Mysql - 索引优化相关

一些常见问题：

• 字段加索引，你是否在自己的项目中用过呢？你觉得什么样的字段适合加索引？
• mysql怎么创建索引？
• 那你觉得，字段加了索引，查找的时候一定会走索引吗？
• 刚才你的索引失效的例子，都是因为人为没有写好 sql 导致的，那如果排除人为的情况，sql 正确书写，那就一定会走索引吗？
• 如果我想要强制走某个索引，能实现吗？可以怎么做？
• 如果一条 sql 执行的很慢，我们可以怎么来排查原因？
• 刚才说到了模糊匹配失效，为什么使用模糊匹配会失效，你能给我解释一下底层原理吗？

---

1. 字段加索引，你在什么业务场景用过呢？你觉得什么样的字段适合加索引？

比如电商支付下的订单表。其中订单号，下单时间是经常需要使用到的字段，这时候就可以给这些字段加索引，用来提高查询速度。

加索引的目的就是为了加快查询速度，即尽量少的访问无关行记录，准确快速地定位到想要的行记录。但同时，给字段添加索引意味会有内存开销。所以在给字段加索引前我们要考虑这样做是否会得不偿失。

一般情况下，给一些频繁需要使用的字段，和一些区别度高的字段加索引是比较合适，其次还要考虑字段的长度，过长会占用空间，过短会导致区分度减低，比如身份证号只取前 6 位情况

2. mysql怎么创建索引？

创建普通索引或联合索引 

CREATE INDEX 索引名 ON 表名(字段名，...)

对于唯一索引，则多了一个 UINIQUE 关键字

CREATE UNIQUE INDEX 索引名 ON 表名(字段名，...)

如若想删除索引，则可以使用如下语句

DROP INDEX 索引名 ON 表名(字段名，...)

如果想创建主键索引，或创建索引时同时想修改表结构，推荐使用 ALTER TABLE

比如创建主键索引

ALTER TABLE 表名 ADD PRIMARY KEY 索引名(字段名)

3. 那你觉得，字段加了索引，查找的时候一定会走索引吗？

不一定。

如果 sql 语句中对索引字段进行了函数操作，就会导致索引失效，系统则不会走索引，比如这个语句

select * from t where id + 1 = 100;

亦或者 LIKE 查询把通配符 % 放在开头.......等等

4. 刚才你的索引失效的例子，都是因为人为没有写好 sql 导致的，那如果排除人为的情况，sql 正确书写，那就一定会走索引吗？

不一定。

走不走索引，走哪条索引都是由优化器决定的。优化器选择索引的目的，是为了找到一个最优的执行方案，并用最小的代价执行。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着磁盘访问数据的次数越少，消耗的 CPU 资源越少

那这个扫描行数是怎么判断的呢？

Mysql 中执行语句前，并不能精确地知道满足条件的语句有多少，而只能根据统计信息来估算记录数。这个统计信息就是索引的 ”区分度“。显然，一个索引上不同的值越多，则这个索引的区分度越好。而索引上不同值的个数，我们称之为 “基数”。也就是说，这个基数越大，索引的区分度越好

Mysql 当然不可能把索引树中的所有节点取出来计算基数，所有一般用的是采样统计。大致过程，先计算出一个索引树的所有叶子节点 M，再从中随机抽取几个节点计算需要扫描行数的平均值 N，最后用平均值 N 乘与所有的叶子节点数量 M，即为一个索引的基数

优化器会根据索引的基数和其他开销，来选择一个最优的执行方案。所以，如果优化器觉得走全表扫描开销比走索引还小的话，则会放弃走索引，而是走全表扫描

5. 如果我想要强制走某个索引，能实现吗？可以怎么做？

可以的。

如果是要强制走某个索引的话，可以通过 FORCE INDEX(索引名）来实现

比如表 t 中有 a，b两个字段，其都有索引。看一下这个语句

select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

虽然走索引 a 要扫描的行数比 b 少，但因为要对 b 排序，所以优化器可能觉得走 b 索引更好，就选择走 b 索引了

如果想强制使用 a 索引的话，则可以这样写

select * from t force index (a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

当然，还有第二种方法，我们可以考虑修改语句，引导 Mysql 使用我们期望的索引，比如，

select * from t force index (a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;

这样逻辑的语义还是一样的，但因为也会对 a 排序，所以优化器就会选择走扫描行数更少的 a 索引了

因为选错索引的情况还是较少出现的，所以第一种方法在开发过程中很少使用，而是等线上出问题时再去修改 sql 语句，所以 force index，对于生产系统来说，敏捷性不够

当然还可以通过新建一个更合适的索引，以供优化器选择，或者直接删除走错的索引

6. 如果一条 sql 执行的很慢，我们可以怎么来排查原因？

对于 sql 语句执行很慢的情况，我们可以分为两种情况：一种是正常速度执行的，突然很慢；还有一种是一直都很慢
第一种情况，可能就是在 sql 执行过程中，遇到了正在刷脏的情况，或者是在等待锁，我们可以通过 SHOW PROCESSLIST 指令来查看当前线程的状态，来判断是否真的在等待锁

第二种情况就是 sql 一直都很慢

这种情况就要考虑是否是自己的 sql 语句书写有问题了，查看语句是否有函数调用导致索引失效，或者根本就没有添加索引。我们可以通过下面这个语句来查看字段是否建立索引

SHOW CREATE TABLE 表名;

亦或者 sql 语句本身没有什么问题，而是 Mysql 统计基数出现差错，使用错了索引，我们可以通过 explain 来查看语句的执行计划，再通过 FORCE INDEX 强制使用索引来进行对比，从而排查速度慢的原因

7. 刚才说到了模糊匹配失效，为什么使用模糊匹配会失效，你能给我解释一下底层原理吗？

因为 Mysql 索引结构是 B+ 树，B+树存储节点是有序的，遵循 ”最左前缀匹配原则“。

如果使用的后缀匹配 Like '%str'，或通配符匹配 Like '%str%'，B+树都无法确认起始点，只能走全表扫描了。

而且模糊匹配的通配符会导致优化器对索引键进行隐式的函数计算，而索引不支持函数计算后的匹配。如：LIKE ‘%str%’，等效于对每个值执行 IF(name LIKE '%str%')，需要逐行计算

---

诚恳欢迎大家提出意见Orz

...... (待续未完