索引优化无非就是两点:
- 把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在执行时,当匹配到第一个值时就会自动停止扫描,因此上述这个方案只适用于普通索引字段、或表中的普通字段。