MYSQL-索引使用（四）

这一节我们讲一下索引的应用，主要从3个方面讲解，分别是创建索引，索引使用，以及相应的查询优化。1、创建高效索引1.1 主键索引规范

建议使用int/bitint类型自增id作为主键，避免使用uuid等无序数据作为主键。有序主键能保证顺序io提升性能，无序主键是随机io，会导致聚簇索引的插入变成完成随机和频繁页分裂。

1.2 使用前缀索引

如果对很长的字符列建立索引，会让索引变得大且慢。我们可以索引开始的部分字符，这样能大大节省索引空间，提高索引效率。例如，表T的字段name长度为50，可以只对其前5个字符建立索引

alter table T add index IX_NAME (name(5))

前缀索引的长度选择对索引的性能有很大影响，索引的选择性（不重复索引占总记录的总数）越高，查询效率越高。我们需要做权衡，在选择足够长前缀以保证高选择性的同时又不能太长。

需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引。

针对BLOB,TEXT,长度很长的VARCHAR类型，必须选择前缀索引，因为MySQL不允许使用列的完整长度，不是必要的情况下，不建议选择这些列建立索引。

一个小tips：

遇到前缀区分度不够好的情况下，怎么办呢？比如，我们国家的身份证号有18位，其中前6位是地址码，所以同一个县的人身份证号前6位一般是相同的。如果维护的是一个县的公民信息系统，对身份证号做长度为6的前缀索引区分度会很低，但索引长度选取越占用磁盘空间越大，相同数据页能放下的索引值就越少，搜索效率也就越低。

有两种方法能在达到相同的查询效率的同时占用更小的空间：

第一种方式是使用倒序存储。我们可以将身份证号倒过来存储，每次查询的时候这么写

select * from T where id_card = reverse('input_id_card')

由于身份证号后6位没有地址码这样的重复逻辑，所以能够提供足够的区分度。

第二种方式是使用hash字段。我们可以在表上再创建一个整数字段，用来保存身份证的校验码，同时在这个字段上创建索引

alter table T add id_card_crc int unsigned, add index(id_card_crc)

每次插入新记录的时候，都用crc32()这个函数得到身份证校验码填到这个字段。由于校验码可能存在冲突，所以查询语句where部分要判断id_card的值是否相同

select * from T where id_card_crc = crc('input_id_card') and id_card='input_id_card'

这样，索引的长度就变成了4个字节，比原来小了很多。

1.3 选择合适索引列顺序

在多列的B+树索引中，索引会按照最左列进行排序，其次是第二列，因此索引的顺序对于查询是至关重要的，将选择性更高的字段放到索引的前面，可以更快地过滤出需要的行。唯一索引区分度最高，选择性为1，性能最好。

下面的查询

select * from T where age = 20 and name = '张三'

如何选择索引的顺序？我们可以预先计算下哪个列的选择性更高

select count(distinct age)/count(*) as age_selectivity, count(distinct name)/count(*) as name_selectivity from T

根据计算结果，选择值更大的列作为索引列的第一项

1.4 建立覆盖索引

通常大家会根据查询的where条件建立索引，设计优秀的索引应该考虑到整个查询。在前面介绍索引结构时提到过，InnoDB使用辅助索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引。例如对于如下查询

select name from T where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table T add index IX_CITY_NAME (city, name)

覆盖索引可以极大地提高性能，因为只需要扫描索引，这种方式能带来很多好处：

1.5 利用索引扫描做排序

MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢。因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行。例如

代码块

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from demo where date = '2010-01-01' order by staff_id,customer_id

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

1.6 避免创建冗余索引

冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，比如有一个索引(A,B)，再创建索引(A)就是冗余索引，因为索引(A)是索引(A,B)的前缀索引。冗余索引经常发生在为表添加新索引时，例如表中已经有了索引(A)，有人可能会新增加一个索引(A,B)，而不是对索引(A)进行扩展，或者将索引(A)扩展成(A,id)，其中id是主键，但是对于InnDB来说主键列已经包含在二级索引中了，所以这个也是冗余的。

一般来说，增加新索引将会导致insert、update、delete等操作的速度变慢，大多数情况下都应该尽量扩展已有的索引而不是创建新索引。但有极少情况下出现性能方面的考虑需要冗余索引，比如扩展已有索引而导致其变得过大，从而影响到其他使用该索引的查询。

2、 正确使用索引

即使建立了索引，如果在查询中不注意的话，很容易造成索引失效，导致MySQL走全量扫描。

2.1 最左前缀匹配原则

对于联合索引，MySQL会一直向右匹配，直到遇到范围查询（< 、>、between、like等）就停止匹配。例如表T上有联合索引（a，b，c），只有a、ab、abc类型的查询会走这个索引，特别要注意对这种联合索引的使用

-- 只有a走联合索引
select * from T where a>1 and b=2 and c=3
-- 不会走联合索引
select * from T where b=2 and c=3

2.2 禁止在索引字段上做数学运算或函数运算

select * from T where tagId + 1 = 50;
select * from T where month(updateTime) = 7;

上面两个查询分别对索引列使用了数学运算和函数运算，通过explain查看执行计划，可以发现他们都是走的全表扫描。因为对索引字段做数学运算和函数运算，可能会破坏索引值的有序性，所以优化器会放弃走索引。

说一个很容易踩的坑：隐式类型转换

比如下面这条SQL语句：

select * from Tradelog where tradeid=123456

交易编号tradeid这个字段上有索引，但是explain的结果却显示这条语句会全表扫描。原因在于Tradeid的字符类型是varchar(32)，比较值却是整型，故需要做类型转换。在MySQL中字符串和数字进行比较的话是将字符串转换成数字，对于优化器来说，上面的查询语句相当于

select * from Tradelog where cast(tradeid as signed int)=123456

也就是说，它对索引字段做了函数运算，所以会出现索引失效。

再说个更不容易发现的隐式转换的坑：隐式字符编码转换

两个用tradeid关联的表查询

select * from Tradelog, Tradedetail where Tradelog.tradeid=Tradedetail.tradeid and Tradelog.id=1

Tradelog用tradeid关联tradedetail时，理应会走Tradedetail的tradeid索引快速定位到等值的行，实际上却走了全表扫描。如果仔细检查表结构定义的话，可以发现Tradelog字符集是utf8，Tradedetail的字符集是utf8mb4，由于utf8mb4是utf8的超集，当两个类型的字符串在做比较时，MySQL会先把utf8字符集的字符串转换成utf8mb4再做比较。所以，它也属于对索引字段做函数操作，索引会失效。

2.3 使用like时避免前缀模糊查询'%xxx%'

一般情况下不鼓励使用like，如果要使用的话避免以通配符%和_开头，即like '%xxx%'，它不会走索引，而like 'xxx%'能走索引。若要提高效率，可以考虑使用全文索引。

2.4 尽量避免负向查询

一般情况下，not null、!=、、not in、not like等负向查询不会走索引，但不是绝对的，mysql查询优化器会选择更优的执行计划。对于数据均匀的场景索引会失效，但如果数据严重不均匀索引不一定失效。例如表A里性别有10000条男10条女，当sex!='男'是可以使用索引的，sex='男'反而不会使用索引，如果mysql判断走全表扫描更高效，就不会走索引。但是还是建议大家避免使用负向查询，尽量利用索引。

我在网上搜到的几乎所有文章都直接给结论负向查询会使索引失效，也没有说失效原因，实际上MySQL选不选择使用索引是基于成本来判断的，负向查询大部分情况下走全表扫描成本更低，因此它不选择使用索引，但在某些情况下，走索引反而更高效，此时MySQL就会选择使用索引。所以要多动手实验，俗话说，实践是检验真理的唯一标准，下面来验证负向查询也可以走索引（下面实验是在我们beta环境数据库测试的，MySQL的版本是5.7.18）

CREATE TABLE s (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 VARCHAR(100),
    key3 VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    KEY idx_key2 (key2),
    KEY idx_key3 (key3)
) Engine=InnoDB CHARSET=utf8;

表s中有10000条记录，用explain查看is not null、!=、not in语句的执行计划，可以看到它们实际上都走了索引

2.5 避免使用select *

查询时尽量不要使用select *，而是只查出需要的字段mysql表索引，因为select * 无法利用覆盖索引优化，还会为服务器带来额外的IO、内存和cpu的消耗

3 查询优化

除了在索引上优化，我们也能通过对查询用的SQL语句做相应优化，来提升查询性能

3.1 优化COUNT()查询

我们一般用count()函数来统计行数，最简单的就是使用count(*)，它并不会像我们想象的那样扩展所有列，实际上，它会忽略列而直接统计所有的行数，这种误解让我们经常在使用时在括号里指定了一个列却希望统计结果集的行数，还误认为性能更好。实际并非这样，如果要统计行数，最好直接使用count(*)，这样写意义清晰，性能也更好。

通常来说，执行COUNT()都需要扫描大量的行才能获取到精确的数据，因此很难优化，一种有效的优化方法就是利用覆盖索引，避免回表。有时候某些业务场景并不要求完全精确的count值，比如说统计活跃用户数，此时可以用近似值来代替，比如explain得到的优化器估算行数就是一个不错的近似值，因为explain并不会真正去执行查询，成本很低。如果确实不能满足要求，那就需要从架构层面解决了，比如说添加汇总表，或者使用redis这样的外部缓存系统。

3.2 IN列表代替多个OR

在很多数据库中，in等于多个or子句，但是在MySQL中不是这样，它进行了一些优化，如官方文档（#function_in）中对in语句的介绍

意思是MySQL会对in列表的值排序，搜索时通过二分查找来判断是否在列表中。所以in的时间复杂度是O(logn)，而or的时间复杂度是O(n)，in的效率更高。如果or有大量数据，建议使用in。

select * from T where name='a' or name='b' or name='c'
--改为
select * from T where name in ('a','b','c')

3.3 LIMIT分页优化

在进行分页操作时，我们通常会用limit语法，一个非常常见又令人头疼的问题就是，偏移量非常大的时候，例如limit 10000,20，查询速度很慢，因为MySQL会查出10020条数据，然后再抛弃掉前面的10000条数据，代价非常高。一个简单的优化办法就是使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。考虑下面的查询

select id, descrition from film order by title limit 10000,5
--改为
select film.id, film.descrition from film inner join (select id from film order by title limit 10000,5) as a using(id)

这里延迟关联将大大提升查询效率，它能让MySQL扫描尽可能少的页。一种更优的方式是，如果能记录上次取数据的位置，那么下次就直接记录位置开始扫描，这样就可以避免使用offset。例如

select * from t limit 10000,10
--从偏移记录的地方开始
select * from t where id>10000 limit 10

3.4 优化UNION语句

MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引。最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化。此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。

3.5 优化JOIN语句

MySQL的join语句连接表使用的是nested-loop join算法，这个过程类似于嵌套循环，简单来说，就是遍历驱动表（外层表），每读出一行数据，取出连接字段到被驱动表（内层表）里查找满足条件的行，组成结果行。要提升join语句的性能，就要尽可能减少嵌套循环的循环次数。一个显著优化方式是对被驱动表的join字段建立索引，利用索引能快速匹配到对应的行，避免与内层表每一行记录做比较，极大地减少总循环次数。另一个优化点，就是连接时用小结果集驱动大结果集，在索引优化的基础上能进一步减少嵌套循环的次数。如果难以判断哪个是大表，哪个是小表，可以用inner join连接，MySQL会自动选择小表去驱动大表。当然，线上尽量避免join连表查询，因为随着数据量的增加，连表操作往往会导致结果集大增，如果连接字段上也没有索引，查询复杂度会爆炸。

为什么选择小表驱动大表？

假设被驱动表行数是M，每次查找时走连接字段的索引，再走主键索引获取数据，搜索一棵树近似复杂度是log2M，故被驱动表上查一行的复杂度是2*log2M。假设驱动表的行数是N，连接时就需要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次，因此整个执行过程，近似复杂度是N + N*2*log2M。显然，N对扫描行数的影响更大，所以应该让小表来做驱动表。

总结

个人主页网站：

若有收获，就点个赞吧

（编辑：徐州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!