Mysql原理与调优-索引原理及使用

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

本文将以上述结构建立联合索引，所以这个索引的列，是按照先按照name进行排序，然后按照birthday顺序排序，最后按照phonenumber进行排序的。

3.1.2 等值匹配

1. 索引列和where条件后面列相等

where条件后面的列刚好是在索引列

select * from person_info where `name`='ello' AND birthday ='1998-07-01' and phone_number = 12321312321

对于联合索引的等值匹配,这个时候会直接走索引，先匹配name匹配到过后再在b+树种查找birthday ='1998-07-01'然后再查找country = 'china'的内容。等值匹配的顺序是可以变的，不管是他们顺序是什么样的，都会被优化成索引的顺序，先匹配name，再匹配birthday，再匹配phonenumber。

2. 索引列和where条件后面列部分相等且遵循最左匹配原则

where条件后面的列，有name和birthday这两列，是按照索引的建立顺序从左能够匹配上的，这称之为最左匹配原则。

SELECT * FROM person_info WHERE `name`='ello' AND birthday ='1998-07-01' AND conutry = 'China'

这个时候，name和birthday会走二级索引获取到主键id，然后通过主键索引回表后过滤出conutry = 'China'的数据。

3. 索引列和where条件后面列部分相等但不遵循最左匹配原则

这个时候，where条件后面没有根据name进行判断，但是索引建立是name相当，才根据birthday进行排序，所以需要向把name定下来，才能对birthday采用二分查找。所以这种情况不能走索引。

SELECT * FROM person_info WHERE  birthday ='1998-07-01' AND conutry = 'China'

3.1.3 模糊匹配

1. 匹配列前缀

SELECT * FROM person_info WHERE  `name` LIKE 'zhang%'

上面name排在第一列，并且对于字符串建立的索引，可以认为也是一个聚簇索引，每次比较的时候比如'zhang'和'zhba'这两个字符串，也是从左到右一个字符一个字符的进行比较，页应该满足最左匹配原则。所以上述会走索引。

2. 非列前缀模糊匹配

SELECT * FROM person_info WHERE  `name` like '%zhang'

上述sql不满足最左匹配原则，则不走索引。

3.1.4. 范围查询

1. 索引最左列进行范围查询

SELECT * FROM person_info WHERE  `name` > 'aaa' and 'name' < 'nnn'

最左列式有序的，所以直接查询出name='aaa'的数据，然后再叶子节点上通过后向指针遍历即可。所以会走索引。

2. 索引非左列进行范围查询

SELECT * FROM person_info WHERE  phone_number > 1213123 and phone_number < 5555555

左侧列没有固定，所以在全局来看，该列是随机的，不能走索引。

3. 索引列范围查询，并且左侧列等值查询

SELECT * FROM person_info WHERE  `name`='ello' AND birthday ='1998-07-01' and phone_number > 1213123 and phone_number < 5555555

name和birthday等值查询后就已经固定下来，然后再通过phone进行范围查询，可以采用二分查找，会走索引。

3.1.5 排序

1. 按照索引列顺序排序

SELECT * FROM person_info ORDER BY  `name` ASC, birthday ASC

索引会先按照name升序排序，在按照birthday排序，最后按照phone升序排序，刚好满足sql，所以会走索引。

2. 排序列在索引里，但未按照索引顺序

SELECT * FROM person_info ORDER BY  birthday ASC

左侧name未固定，所以birthday对于整棵索引树来说是乱序，不会走索引。

3. 索引列排序，左侧列等值比较

SELECT * FROM person_info WHERE `name`='ello' ORDER BY  birthday ASC

name字段已经被固定了，所以索引树是哪找birthday进行排序的，所以会走索引。

4. ASC和DESC混用

SELECT * FROM person_info ORDER BY  `name` ASC, birthday DESC

这种情况，name可以通过索引进行排序，然后将数据读取到sort buffer中，最后通过文件排序对birthday进行排序。

3.1.5 分组

索引其实天然就带有分组的功能，比如上面的联合索引可以理解为先对name进行分组，然后再对brthday进行分组，最后对phone_number进行分组。

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

所以针对上述sql，也会走索引。

3.2 回表与索引覆盖

3.2.1 回表

1.什么是回表

在二级索引中，如果查询的数据在二级索引中并没有存储，就需要通过二级索引得到主键id，然后通过主键id到主键索引中回表得到完整的记录。

比如

SELECT * FROM person_info WHERE `name`='ello' AND birthday ='1998-07-01' AND phone_number = 12321312321

我们需要根据idx_name_birthday_phone_number 这个索引查询出满足条件的id值，然后根据主键id到主键索引中获取到完整的记录。

这就是我们为什么不建议使用select *的原因，因为这样会加大回表的概率。

2.回表的代价

1.我们从索引中获取主键id的时候，因为索引是按照顺序存储的，所以这个时候基本上可以认为是顺序IO，性能较好。

2.拿到主键id过后，由于主键id可能是乱序的，然后到主键索引中查询，这个时候是随机IO，性能较差。

3.2.2 索引覆盖

我们如果让二级索引刚好包含需要查询的列，这个时候结果就只需要从二级索引中获取，不用回表到主键索引中再次获取，就称之为索引覆盖。

3.3. 怎么建立索引

3.3.1 尽量只为搜索、排序、分组字段建立索引

尽量只为搜索、排序、分组字段建立索引。当然，如果某个sql特别重要，为了减少回表，也可以为sql的将该sql的查询字段冗余到索引中，减少回表。这些都需要结合实际而定。

3.3.2 尽量为基数较大的列建立索引

比如状态类型这种列值只有几个选择的列时，如果要建立索引，由于有大量重复值，这个索引也基本上不会生效。这个时候我们可以考虑从业务角度优化，比如查询是带上时间，建立一个时间和状态的联合索引，增大索引列的基数。

3.3.3 索引列的类型尽量足够小

索引列尽量使用tinyint，int这种类型，这样b+树的每个节点会存储更多内容，减少树的高度。

3.3.4 对于字符串，使用字符串前缀建立索引

字符串比较的时候，是按照一个字符一个字符进行比较的。如果将整个字符串都作为索引的话，首先整棵索引树会特别大，比较的时候，如果索引树比较深，或者用or连接多个字符串模糊匹配的场景，优化器可能会选择全表扫描。

SELECT * FROM person_info WHERE  `name` like 'aaa%' or `name` like 'bbb%' or `name` like 'ccc%' or `name` like 'ddd%'

所以，我们可以截取字符串的部分前缀建立索引，这个时候可以减少索引树的大小。但是这样不能将其用于排序。

3.3.5 不要让索引列出现在表达式中

尽量不要在where条件后面的索引列中，进行表达式运算，这样会导致索引失效。

3.3.5 主键尽量递增

主键递增，可以减少主键索引的页分裂。

3.3.6 减少冗余索引和重复索引

维护冗余索引和重复索引会降低性能。

3.4 is null和使用or会走索引吗？

3.4.1 null值在索引中的处理

Mysql默认null值是索引中的最小值。

3.4.2 扫描区间

对于is null或者or，Mysql的优化器首先会根据写的sql得出一个扫描区间：

比如is null的扫描区间就是[null,null],is not null的扫描区间(null,+∞]，而对于下面这个用sql连接的语句器扫描区间为[11111,22222] U [33333,44444] U [55555,66666]。

SELECT * FROM person_info WHERE   phone_number > 11111 and phone_number < 22222 or   
 phone_number > 33333 AND phone_number < 44444
 or   phone_number > 55555 AND phone_number < 66666

Mysql的优化器会根据索引对扫描区间进行扫描的话估算出执行代价，然后对比全表扫描的代价，判断哪种方式代价更低，然后选择索引或者全表扫描。

网上说的is null不走索引，大概率是因为表的数据量不够或者如果运行字段为空，大概率这个字段会出现大量为空的记录。所以对于null这个值，基数会很小，执行器在计算代价的时候，跟倾向于选择全表扫描。

3.总结

索引是sql优化的最主要的手段，只有掌握原理才能知道哪些场景索引会失效或者生效。对于索引失效或者生效，是由执行器根据走不同的索引或者全表扫描得到执行计划，然后估算出不同的代价比较后选择代价最小的执行计划。执行计划的选择会受很多条件影响，比如数据量，或者索引树的存储排列结构。我们只有根据索引的原理去分析是否走索引，但是因为其他因素影响，可能真正的执行结果和我们预想的不一致。这些都是要结合实际数据场景去分析的。而且优化器选择的执行计划也不一定是最优的，有时通过强制索引来使得某个索引生效，也是一种优化手段。