Mysql学习一

MySQL分为Server层和存储引擎层两部分

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL大多数核心功能，以及所有内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。

连接器

客户端通过连接器和数据库连接。连接分为长连接和短连接。长连接是指连接成功后，如果客户端有持续的请求，则一直使用同一个连接，短连接则是每次执行完很少几次连接后就断开连接，下一次查询再重新建立一个。

建立间接的过程通常比较复杂，尽量使用长连接。

但是全部使用长连接后，MySQL内存涨的特别快，这是因为 MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。如果长连接积累下来，可能导致内存占用太大。

有两个方案解决这个问题：

1. 定期断开连接
2. MySQL5.7+，执行mysql_reset_connection来重新初始化连接资源，将连接恢复到刚刚创建完连接到状态。

执行器

你会在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。

innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这样可以保证MySQL异常重启之后数据不丢失。

sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这样可以保证MySQL异常重启之后binlog不丢失。

索引

索引对于数据库，就像书本的目录一样，方便我们快速找到书中某一个知识点。索引的出现时为了提高查询效率。

索引常见的模型：

1. 哈希表：哈希表是key-value存储数据的解构。适合像 memcached 一样的等值查询。
2. 有序数组：有序列表，通过二分法查找对应数据。适用于静态存储索引，就是不会再修改的数据，不然中间插入数据，效率很低
3. 搜索树：二叉树，N 叉树

基于非主键索引的查询需要多扫描一颗索引树， 因此我们在应用中应尽量使用主键查询。

1. select * from t where id=500，即为主键查询方式，则只需要id这棵B+树；
2. select * from t where k=5，即普通索引查询方式，则需要先搜索K 索引树，得到id的值为500，再到id索引树中搜索一次。这个过程成为回表。

自增主键

插入主键的时候可以不指定id的值，系统会获取当前id 最大值加1作为下一条记录的 id。这样每增加一条新记录都是追加操作，都不涉及挪动其它记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

除了考虑性能外，我们还可以从存储空间的角度来看。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以从性能和存储空间方便考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段做主键呢？

1. 只有一个索引
2. 该索引必须是唯一索引

就是典型的 KV 场景。由于没有其他索引，就不用考虑其它索引的叶子节点的大小问题。

重建字段k 的索引

alter table t drop index k;
alter table t add index(k);

如果重建主键索引：

alter table t drop primary key;
alter table t add primary key(id);

select * from t where k between 3 and 5需要执行几次树的搜索操作，会扫描多少行？

1. 在k索引树上找到k=3的记录，取得id=300;
2. 再到id索引树查到id=300对应的R3;
3. 在 k 索引树上取下一个值k=5,取得id=500;
4. 再回到id索引树查到id=500对应的R4;
5. 在k索引树上取下一个值k=6，不满足条件，循环结束

回到主键索引树搜索到过程称为回表，这个查询过程读了 k 索引树3条记录，回表了两次。

由于查询所需结果的数据只在主键索引上有，所以不得不回表。

2019-01-19 10:04