MySQL原理和优化

本文主要摘抄自《MySQL技术内幕（InnoDB存储引擎）第二版》，少部分来自网络博客和自己补充。注意，该书内容是基于MySQL 5.6 及更早的版本，具体的实现方式有可能发生了改变，有些内容我做了注释和补充，但是不保证百分百的完整和正确。

存储引擎

MySQL 5.0 支持的存储引擎包括 MyISAM、 InnoDB、 BDB、 MEMORY、 MERGE、 EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中 InnoDB 和 BDB 提供事务安全表，其他存储引擎都是非事务安全表。

使用show engines命令可以查看支持的存储引擎。我在 MySQL 8.0 中执行命令得到的结果如下：

一般使用的都是 InnoDB 引擎，所以主要还是研究 InnoDB 的特性和原理。

自动增长列

InnoDB 表的自动增长列可以手工插入，但是插入的值如果是空或者 0，则实际插入的将是自动增长后的值。如果插入一个大于当前自增id的数的时候，自增id会变成这个数字，中间的就被跳过了。

可以通过ALTER TABLE *** AUTO_INCREMENT = n;语句强制设置自动增长列的初识值，默认从 1 开始，但是该强制的默认值是保留在内存中的，如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要在数据库启动以后重新设置。

对于 InnoDB 表,自动增长列必须是索引。如果是组合索引,也必须是组合索引的第一列。

外键约束

MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候,要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。其中 RESTRICT 和 NO ACTION 相同，是指限制在子表有关联记录的情况下父表不能更新； CASCADE 表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被 SET NULL。选择后两种方式的时候要谨慎，可能会因为错误的操作导致数据的丢失。

《阿里巴巴Java规范》中强制要求不能使用外键，所有表之间的关联逻辑应该在业务逻辑中实现。

存储方式

在 8.0 里面，InnoDB 把数据字典进行重构，大家应该知道数据字典是什么，就是表结构，你的用户定义，所有的跟 DDL 相关放到数据字典里面去。在 8.0 之前，数据字典有两份，一份是存储在 .frm 文件里，另一份是 InnoDB 的数据表里。在8.0之后 .frm 被干掉了，只保留了 InnoDB 中的数据。8.0 中数据库的 innodb 表全部放至在 datadir 下的 mysql.ibd 中，不再把表结构放在 .frm 文件中，而是存放在元数据表中。

索引

索引概述

所有 MySQL 列类型都可以被索引，对相关列使用索引是提高 SELECT 操作性能的最佳途径。根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎(如 MyISAM、InnoDB、BDB、MEMORY 等)对每个表至少支持 16 个索引，总索引长度至少为 256 字节。大多数存储引擎有更高的限制。

InnoDB存储引擎支持以下几种常见的索引：

• B+ 树索引
• 全文索引
• 哈希索引

B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最有效的索引。B+树索引的构造类似与二叉树，根据键值（Key Value）快速找到数据。

设计索引的原则和技巧

• 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在 WHERE子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。
• 使用惟一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“ M”和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。
• 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。
• 利用最左前缀。在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。另外，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任调整。
• 不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错误的。
• 对于 InnoDB 存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键,则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
• = 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

索引的操作

创建索引的详细定义如下：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

索引的删除语法为：

DROP INDEX index_name ON tbl_name

B+树索引的原理

关于 B 树和 B+ 树的结构，可以参考这篇博文：B树和B+树的插入、删除图文详解

B+ 树相比 B 树，优点包括：

1. B+ 树的层级更少：相较于 B 树 B+ 每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快；
2. B+ 树查询速度更稳定：B+ 所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比 B 树更稳定;
3. B+ 树天然具备排序功能：B+ 树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比 B 树高。
4. B+ 树全节点遍历更快：B+ 树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像 B 树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

而 B 树相对于 B+ 树的优点是，如果经常访问的数据离根节点很近，而 B 树的非叶子节点本身存有关键字其数据的地址，所以这种数据检索的时候会要比 B+ 树快。

聚簇索引 & 非聚簇索引

聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录

InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。

下图形象说明了聚簇索引表(InnoDB)和非聚簇索引(MyISAM)的区别：

1. 对于非聚簇索引表来说（右图），表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用的是 B+ 树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引 + 索引对应的记录的数据。

2. 对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据（包含了主键值），二级索引的叶结点存储行的主键值。使用的是 B+ 树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录（数据内容）。

聚簇索引的优点

1. 当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。

2. 当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次 I/O。

3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点

1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键。

2. 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于 InnoDB 表，我们一般定义主键为不可更新。

3. 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

4. 采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的 I/O 代价。

联合索引

联合索引是指对表上多个列进行索引，创建方法和单个索引一样，不同之处在于有多个索引列。

联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

联合索引的优势：

1. 避免回表

   在执行计划中，table access by index rowid代表是回表动作。如在 user 的 id 列建有索引，select id from user这个不用回表，直接从索引中读取id的值，而select id,name from user中，不能返回除 id 列其他的值，所以必须要回表。如果建有了 id 和 name 列的联合索引，则可以避免回表。

   另外，建立了 id 和 name 的联合索引(id列在在前)，则select id from user可以避免回表，而不用单独建立 id 列的单列索引

2. 两个单列查询返回行较多，同时查返回行较少，联合索引更高效。

   如果 select * from user where id=2 和 select * from user where name='tom' 各自返回的行数比较多，而 select * from user where id=2 and name='tom' 返回的行数比较少，那么这个时候使用联合索引更加高效。

索引优化和最佳实践

先来个偷来的大长图：

一些其他的技巧

• 通常来说简单的把可为 NUL 的列改为 NOT NULL 不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为 NOT NULL。
• 对整数类型指定宽度，比如 INT(11)，没有任何卵用。INT 使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以 INT(1) 和 INT(20) 对于存储和计算是相同的。
• UNSIGNED 表示不允许负值，大致可以使正数的上限提高一倍。
• 大表 ALTER TABLE 非常耗时，MySQL 执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。

一些不走索引的情况：

MySQL 内部优化器会对 SQL 语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引。所以实际上不走索引的情况可能非常多，很难一一列举。以下是一些比较典型的情况。

非独立的列

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。比如：

select * from where id + 1 = 5

我们很容易看出其等价于 id = 4，但是 MySQL 无法自动解析这个表达式，使用函数是同样的道理。

另外在查询条件里面用函数计算的话，也是一样的。

模糊查询

当然不是全部模糊查询都不走索引，如果查询条件前半部分是确定的，就会走索引，即like 'xxx%'的情况。

select * from compare_result where request_id like '%0173f%';#不走索引

不等于

类似where id !=2或者where id <> 2都不会走索引，尽量使用 UNION 关键字进行表述。

范围查询

如果是连续的范围，优先用 between 而不是 in。

最后又偷了一个别人的图：

参考文章：

MySQL索引原理及慢查询优化

MySQL优化/面试，看这一篇就够了