原创 如何通过建立索引优化SQL？

优化SQL是一个老生常谈的问题，我们可以从多方面入手对SQL进行优化。今天本文主要从应用层来看，如何通过建立索引优化SQL。索引的数据结构B+Tree有着较高的查询性能，因此建立索引主要是对SQL 的查询性能进行优化。下面我们一起来看看建立普通索引、建立复合索引、最左前缀匹配原则、索引下推、覆盖索引、普通索引等等内容吧~

1、建立普通索引：对经常出现在 where 关键字后面的表字段建立对应的索引。。

2、建立复合索引：如果 where 关键字后面常出现的有几个字段，可以建立对应的 复合索引。要注意可以优化的一点是，将单独出现最多的字段放在前面。例如现在我们有两个字段 a 和 b 经常会同时出现在 where 关键字后面：

select * from t where a = 1 and b = 2;   \* Q1 *\

也有很多 SQL 会单独使用字段 a 作为查询条件：

select * from t where a = 2;   \* Q2 *\

此时，我们可以建立复合索引 index(a,b)。因为不但 Q1 可以利用复合索引，Q2 也可以利用复合索引。

3、最左前缀匹配原则

如果我们使用的是复合索引，应该尽量遵循 最左前缀匹配原则。MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。假如此时我们有一条SQL：

select * from t where a = 1 and b = 2 and c > 3 and d = 4;

那么我们应该建立的复合索引是：index(a,b,d,c) 而不是 index(a,b,c,d)。因为字段 c 是范围查询，当 MySQL 遇到范围查询就停止索引的匹配了。大家也注意到了，其实 a,b,d 在 SQL 的位置是可以任意调整的，优化器会找到对应的复合索引。还要注意一点的是，最左前缀匹配原则不但是复合索引的最左 N 个字段；也可以是单列（字符串类型）索引的最左 M 个字符。例如我们常说的 like 关键字，尽量不要使用全模糊查询，因为这样用不到索引；所以建议是使用右模糊查询：select * from t where name like '李%'（查询所有姓李的同学的信息）。

4、索引下推：很多时候，我们还可以复合索引的 索引下推 来优化 SQL 。例如此时我们有一个复合索引：index(name,age) ，然后有一条 SQL 如下：

select * from user where name like '张%' and age = 10 and sex = 'm';

根据复合索引的最左前缀匹配原则，MySQL 匹配到复合索引 index(name,age) 的 name 时，就停止匹配了；然后接下来的流程就是根据主键回表，判断 age 和 sex 的条件是否同时满足，满足则返回给客户端。

但是由于有索引下推的优化，匹配到 name 时，不会立刻回表；而是先判断复合索引 index(name,age) 中的 age 是否符合条件；符合条件才进行回表接着判断 sex 是否满足，否则会被过滤掉。那么借着 MySQL 5.6 引入的索引下推优化 ，可以做到减少回表的次数。

5、覆盖索引：很多时候，我们还可以覆盖索引来优化SQL。

情况一：SQL 只查询主键作为返回值。主键索引（聚簇索引）的叶子节点是整行数据，而普通索引（二级索引）的叶子节点是主键的值。所以当我们的 SQL 只查询主键值，可以直接获取对应叶子节点的内容，而避免回表。

情况二：SQL 的查询字段就在索引里。复合索引：假如此时我们有一个复合索引 index(name,age) ，有一条 SQL 如下：

select name,age from t where name like '张%';

由于是字段 name 是右模糊查询所以可以走复合索引，然后匹配到 name 时，不需要回表，因为 SQL 只是查询字段 name 和 age，所以直接返回索引值就 ok 了。

6、普通索引

尽量 使用普通索引 而不是唯一索引。首先，普通索引和唯一索引的查询性能其实不会相差很多；当然了，前提是要查询的记录都在同一个数据页中，否则普通索引的性能会慢很多。但是，普通索引的更新操作性能比唯一索引更好；其实很简单，因为普通索引能利用 change buffer 来做更新操作；而唯一索引因为要判断更新的值是否是唯一的，所以每次都需要将磁盘中的数据读取到 buffer pool 中。

7、前缀索引

我们要学会巧妙的使用 前缀索引，避免索引值过大。例如有一个字段是 addr varchar(255)，但是如果一整个建立索引 [ index(addr) ]，会很浪费磁盘空间，所以会选择建立前缀索引 [ index(addr(64)) ]。建立前缀索引，一定要关注字段的区分度。例如像身份证号码这种字段的区分度很低，只要出生地一样，前面好多个字符都是一样的；这样的话，最不理想时，可能会扫描全表。前缀索引避免不了回表，即无法使用覆盖索引这个优化点，因为索引值只是字段的前 n 个字符，需要回表才能判断查询值是否和字段值是一致的。

怎么解决？倒序存储：像身份证这种，后面的几位区分度就非常的高了；我们可以这么查询：

select field_list from t where id_card = reverse('input_id_card_string'

增加 hash 字段并为 hash 字段添加索引。

8、干净的索引列：索引列不能参与计算，要保持索引列“干净”。假设我们给表 student 的字段 birthday 建立了普通索引。下面的 SQL 语句不能利用到索引来提升执行效率：

select * from student where DATE_FORMAT(birthday,'%Y-%m-%d') = '2020-02-02';

我们应该改成下面这样：

select * from student where birthday = STR_TO_DATE('2020-02-02', '%Y-%m-%d');

9、扩展索引

我们应该尽量扩展索引，而不是新增索引，一个表最好不要超过5个索引；一个表的索引越多，会导致更新操作更加耗费性能。

关于建立索引优化SQL的方案就说到这里了，更多精彩内容尽在博学谷资讯栏目！