MySQL索引怎么优化

索引优化无非就是两点：

• 把SQL的写法进行优化，对于无法应用索引，或导致出现大数据量检索的语句，改为精准匹配的语句。
• 对于合适的字段上建立索引，确保经常作为查询条件的字段，可以命中索引去检索数据。

连接查询时尽量不关联太多表

关联太多会导致执行效率变慢

多表查询时一定要以大驱小

用小的数据集去驱动大的数据集，就是先查小表，用小表的结果去大表中检索数据，MySQL的优化器有驱动表的优化，执行多表联查MySQL的关联算法为Nest Loop Join，该算法会依照驱动表的结果集作为循环基础数据，然后通过该结果集中一条条数据，作为过滤条件去下一个表中查询数据，最后合并结果得到最终数据集。

• 指定了连接条件，满足查询条件的小数据表作为驱动表。
• 未指定连接条件，数据总行数少的表作为驱动表。

通常使用in做子查询时，都要确保in的条件位于所有条件的最后面，这样能够在最大程度上减小多表查询的数据匹配量，在下一道工序开始前尽量缩小数据量，为下一道工序尽可能提供更加精准的数据。

不要使用like左模糊和全模糊查询

like关键字以 % 开头会导致索引失效，千万要避免 %xxx、%xxx% 这两种情况出现。

查询时不要对字段做空值判断

会导致索引失效，想为空的字段，可以设计一个0、" "这类空字符代替，一方面要查询空值时可通过查询空字符的方式走索引检索，同时也能避免MyBatis注入对象属性时触发空指针异常。

不要在条件查询 = 前对字段做任何运算

使用函数也不可以，优化器生成执行计划时，发现 = 前涉及逻辑运算就不会继续往下走了。

!=、!<>、not in、not like、or...要慎用

也可能会导致索引失效，可以使用其他语法代替，比如or使用union all代替。

select user_name from zz_users where user_id=1 or user_id=2;
-- 可以替换成：
select user_name from zz_users where user_id=1
union all
select user_name from zz_users where user_id=2;

必要情况可以强制指定索引

优化器面对复杂 SQL 时没有那么智能，有时选择的索引并不是做好的，对索引结构足够熟悉的情况下，可以通过force index指定索引。

select * from zz_users force index(unite_index) where user_name = "熊猫";

避免频繁创建、销毁临时表

临时表是一种数据缓存，对于一些常用的查询结果可以为其建立临时表，后续要查询时可以直接基于临时表来获取数据，MySQL默认会在内存中开辟一块临时表数据的存放空间，走临时表查询数据是直接基于内存的，速度会比走磁盘检索快上很多倍。注意：只有对于经常查询的数据才对其建立临时表，不要盲目的去无限制创建，否则频繁的创建、销毁会对MySQL造成不小的负担。

尽量将大事务拆分为小事务执行

一个事务在执行时，如果包含了写操作，会先获取锁再执行，直到事务结束后MySQL才会释放锁。一个事务比较大时，导致一部分数据锁定周期长，高并发情况下会有大量事务阻塞，最终拖垮整个MySQL系统。

• show status like 'innodb_log_waits'；查看是否有大事务由于redo_log_buffer不足，而在等待写入日志。

大事务也会导致日志写入时出现阻塞，这种情况下会强制触发刷盘机制，大事务的日志需要阻塞到有足够的空间时，才能继续写入日志到缓冲区，这也可能会引起线上出现阻塞。

从业务设计层面减少大量数据返回的情况

一次性返回的数据量过于巨大时，就会引起网络阻塞、内存占用过高、资源开销过大的各类问题出现，如果项目中存在这类业务，一定要拆分掉，比如分批返回给客户端。每次基于上次返回数据的界限，再一次读取一批数据返回给客户端，也就是经典的分页场景，通过分页的思想能够提升单次查询的速度，以及避免大数据量带来的一系列后患问题。

避免深分页的情况出现

select xx,xx,xx from yyy limit 100000,10; 

查询第1w 页数据，一共查询10w 条数据，然后丢弃前10w 条数据返回最后10条数据。

• 解决方案：

查询的结果集存在递增且连续的字段，可以基于有序字段做一步筛选在获取分页数据。这种情况会先按where条件筛选数据后，再获取前10条数据返回。也可通过between做优化。

-- 第一页
select xx,xx,xx from yyy where 有序字段 >= 1 limit 10; 
-- 第二页
select xx,xx,xx from yyy where 有序字段 >= 11 limit 10; 
-- 第N页.....

-- 第10000页
select xx,xx,xx from yyy where 有序字段 >= 100001 limit 10; 
-- 舍弃了limit关键字来实现分页，但这种方式仅适合于基于递增且连续字段分页。
select xx,xx,xx from yyy where 有序字段 between 1000000 and 1000010; 

搜素分页的情况下是无序的，数据可以位于表中的任意行，就算存在有序字段也不会连续。就只能通过在业务上限制深分页的情况。以百度为例，显示大约搜索到了一亿条数据，往后拉就会发现，最大只能显示76页，然后会提示“限于网页篇幅，部分结果未予显示”。这种思想仅局限于业务允许的情况下，以搜索为例，一般用户最多看前面30页，如果还未找到他需要的内容，基本上就会换个更精准的关键词重新搜索。

如果业务必须要求展现所有分页数据，此时又不存在递增的连续字段，要么选择之前哪种很慢的分页方式，要么就直接抛弃所有！每次随机十条数据出来给用户，如果不想重复的话，每次新的分页时，再对随机过的数据加个标识即可。

SQL务必写完整，不要使用缩写

这种隐式的写法在MySQL底层会做一次转换，为完整的写法。考虑极致的优化，将SQL写成完整的语法。

-- 为字段取别名的简单写法
select user_name "姓名" from zz_users;
-- 为字段取别名的完整写法
select user_name as "姓名" from zz_users;

-- 内连表查询的简单写法
select * from 表1,表2... where 表1.字段 = 表2.字段 ...; 
-- 内连表查询的完整写法
select * from 表1 别名1 inner join 表2 别名2 on 别名1.字段 = 别名2.字段;

基于联合索引查询时确保字段的顺序性

基于建立的联合索引查询数据，就必须要按照索引字段的顺序去查询数据，否则可能导致不能完全利用联合索引，要遵循索引最左前缀原则。

客户端的一些操作可批量化完成

xxDao.insertBatch(xxObjs);

/**
 * xxDao.insertBatch(xxObjs)对应的SQL如下：
 * insert into tb_xxx values(......),(......),(......),(......),.....;
**/

会组合成一条SQL发送给MySQL执行，能够在很大程度上节省网络资源的开销，提升批量操作的执行效率。同样适用于修改场景，如果一个业务会出现批量修改的情况时，也切记不要用for循环来调用update语句对应的接口，而是应该再写一个update/replace语句的批量修改接口。

明确仅返回一条数据的语句可以使用limit 1

select * from zz_users where user_name = "竹子";
select * from zz_users where user_name = "竹子" limit 1;

后者大多数情况下会比前者好，加上limit 1关键字后，当程序匹配到一条数据时就会停止扫描，不加的情况下会将所有数据都扫描一次。一般情况下，如果确定了只需要查询一条数据，就可以加上limit 1提升性能。

但在一些极端情况下，性能可能相差不大，比如要查询的数据位于表/索引文件的最后面，那么依旧会全部扫描一次。还有一种情况是基于主键/唯一索引字段查询数据时，这些字段值本身具备唯一性，MySQL在执行时，当匹配到第一个值时就会自动停止扫描，因此上述这个方案只适用于普通索引字段、或表中的普通字段。

参考文档