MySQL数据库：SQL优化与索引优化

一、索引优化：

1、like语句的前导模糊查询不使用索引：

select * from doc where title like '%XX'； --不能使用索引

select * from doc where title like 'XX%'； --非前导模糊查询，可以使用索引

2、负向条件查询不能使用索引：

负向条件有：!=、<>、not in、not exists、not like等

例如下面SQL语句：（假设status的取值为0、1、2、3、4）

select * from doc where status != 1 and status != 2; --不能使用索引

select * from doc where status in (0,3,4); --优化为in查询，可以使用索引

3、范围条件右边的列不能使用索引（范围列可以用到索引）：

范围条件有：<、<=、>、>=、between等。

索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引(emp_no、title、from_date)，那么下面的SQL中emp_no可以用到索引，而title和from_date则使用不到索引。

select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

4、在索引列做任何操作（计算、函数、表达式）会导致索引失效而转向全表扫描：

• select * from doc where YEAR(create_time) <= '2016'; -- 不能使用索引
• select * from doc where create_time<= '2016-01-01'; -- 可以使用索引
• select * from order where date < = CURDATE()； -- 不能使用索引
• select * from order where date < = '2018-01-2412:00:00'; -- 可以使用索引
• select id from t where substring(name,1,3)=’abc’ -- 不能使用索引
• select id from t where name like ‘abc%’ -- 可以使用索引
• select id from t where num/2=100 -- 不能使用索引
• select id from t where num=100*2 -- 可以使用索引

5、where 子句中索引列使用参数，也会导致索引失效：

因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num -- 不能使用索引

select id from t with(index(索引名)) where num=@num --可以改为强制查询使用索引：

6、强制类型转换会导致全表扫描：

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

如果phone字段是varchar类型，则下面的SQL不能命中索引，因为内部发生的类型转换。

select * from user where phone=13800001234; -- 不能使用索引

select * from user where phone='13800001234'; -- 可以使用索引

7、is null, is not null 在无法使用索引，不过在mysql的高版本已经做了优化，允许使用索引

select id from t where num is null; -- mysql低版本不能使用索引

select id from t where num=0; -- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询

8、使用组合索引时，要符合最左前缀原则：

组合索引的字段数不允许超过5个。如果在a,b,c三个字段上建立联合索引 index(a,b,c)，那么他会自动建立a、(a,b)、(a,b,c) 三组索引。

（1）建立联合索引的时候，区分度最高的字段在最左边：

（2）存在等号和非等号混合判断条件时，在建立索引时，把等号条件的列前置，如where a > ? and b= ?，那么即使a的区分度更高，也必须把b放在索引的最前列。

（3）最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致，但还是建议where 条件的顺序和联合索引一致。

（4）假如index(a,b,c),where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用。

9、利用覆盖索引来进行查询操作，避免回表，减少select * 的使用：

覆盖索引：被查询列要被所建的索引覆盖，被查询列的数据能从索引中直接取得，不用通过行定位符再到row上获取，加速查询速度。

例如登录业务需求，SQL语句如下。

Select uid, login_time from user where login_name=? and passwd=?

可以建立(login_name, passwd,login_time)的联合索引，由于login_time已经建立在索引中了，被查询的uid和login_time就不用去row上获取数据了，从而加速查询。

10、利用索引下推减少回表的次数：

索引下推是Mysql5.6版本推出的功能，用于优化查询。

• 不使用索引下推的情况下，在使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务层，服务层然后判断数据是否符合条件 。
• 使用索引下推的情况下，如果存在某些被索引的列的判断条件时，MySQL服务层将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务层传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务层。

所以，索引下推就是存储引擎查询数据时，根据查询条件过滤掉一些记录，减少回表的次数，也可以减少MySQL服务层从存储引擎接收数据量。

11、使用前缀索引：

短索引不仅可以提高查询性能而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销，但缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引。比如有一个varchar(255)的列，如果该列在前10个或20个字符内，可以做到既使前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。为了减少key_len，可以考虑创建前缀索引，即指定一个前缀长度，可以使用count(distinct leftIndex(列名,索引长度))/count(*)来计算前缀索引的区分度（计算前缀索引的区分度在文章第三部分会介绍）。

12、order by、group by后面的列如果有索引，可以利用索引的有序性可以消除排序带来的CPU开销。

（1）order by最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。例如对于语句where a= ? and b= ? order by c，可以建立联合索引(a,b,c)。

（2）如果索引中有范围查找，那么索引有序性无法利用，如WHERE a > 10 ORDER BY b; 索引(a,b)无法排序。

（3）如果是前缀索引，是不能消除排序的

（4）order by排序字段顺序，即asc/desc升降要跟索引保持一致，充分利用索引的有序性来消除排序带来的CPU开销

12、进行join联表查询的字段需要建立索引，join最好不要超过三个表，需要join的字段，数据类型必须一致：

多表关联查询时，保证被关联的字段需要有索引。left join是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用right join。

13、单表索引建议控制在5个以内。

索引不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，同时也会暂用空间。一个表的索引数较好不要超过5个。

14、SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

ref：使用普通的索引

range：对索引进行范围检索。

当type=index时，索引物理文件全扫，速度非常慢。

15、业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引，防止脏数据产生：

不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然。

16、更新十分频繁、数据区分度不高的列不宜建立索引：

数据更新会变更B+树，在更新频繁的字段建立索引会大大降低数据库性能。类似于“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。一般区分度在80%以上的时候就可以建立索引，区分度可以使用count(distinct(列名))/count(*)来计算。

二、SQL语句优化：

1、减少请求的数据量：

（1）只返回必要的列，用具体的字段列表代替 select * 语句

MySQL数据库是按照行的方式存储，而数据存取操作都是以一个页大小进行IO操作的，每个IO单元中存储了多行，每行都是存储了该行的所有字段。所以无论取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。但是如果查询的字段都在索引中，也就是覆盖索引，那么可以直接从索引中获取对应的内容直接返回，不需要进行回表，减少IO操作。除此之外，当存在 order by 操作的时候，select 子句中的字段多少会在很大程度上影响到我们的排序效率。

（2）只返回必要的行，使用 Limit 语句来限制返回的数据。如果不使用 Limit 的话，MySQL将会一行一行的将全部结果按照顺序查找，最后返回结果，借助 Limit 可以实现当找到指定行数时，直接返回查询结果，提高效率

2、优化深度分页的场景：利用延迟关联或者子查询

对于 limit m, n 的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。因为MySQL并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

延迟关联示例如下，先快速定位需要获取的id段，然后再关联：

# 延迟关联：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据

#覆盖索引：select的数据列只用从索引中就能够得到，不用回表查询

select a.* from 表1 a,(select id from 表1 where 条件 limit 100000,20) b where a.id=b.id；

但对于深度分页的情况，最好还是将上次遍历到的最末尾的数据ID传给数据库，然后直接定位到该ID处 再 往后面遍历数据

3、分解大连接查询：

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• （1）减少锁竞争；
• （2）让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• （3）分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• （4）在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
• （5）查询本身效率也可能会有所提升。比如使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

4、避免使用select的内联子查询：

在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。

5、尽量使用Join代替子查询：

由于MySQL的优化器对于子查询的处理能力比较弱，所以不建议使用子查询，可以改写成Inner Join，之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表

select 
    b.member_id,b.member_type, a.create_time,a.device_model 
from 
    member_operation_log a 
inner join 
    (select member_id,member_type from member_base_info where `status` = 1) as b 
on 
    a.member_id = b.member_id;

6、多张大数据量的表进行JOIN连接查询，最好先过滤在JOIN:

在多个表进行 join 连接查询的时候，最好先在一个表上先过滤好数据，然后再用过滤好的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间

7、避免在使用or来连接查询条件：

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

8、union、in、or 都能够命中索引，但推荐使用 in：

（1）union：能够命中索引，并且MySQL耗费的CPU最少

select * from doc where status=1

union all

select * from doc where status=2;

（2）in：能够命中索引，查询优化耗费的CPU比union all多，但可以忽略不计

select * from doc where status in (1, 2);

（3）or：新版的MySQL能够命中索引，但是如果一个字段有建立索引、一个字段没有建立索引，那么将导致索引失效而进行全表扫描，or 查询优化耗费的CPU比in多

select * from doc where status = 1 or status = 2

对于上面三种关键词：union all 分两步执行，而 in 和 or 只用了一步，效率高一点。用 or 的执行时间比 in 时间长。因为使用 or 条件查询，会先判断一个条件进行筛选，再判断 or 中另外的条件再筛选，而 in 查询直接一次在 in 的集合里筛选，并且or 查询优化耗费的CPU比in多，所以推荐使用in

9、对于连续的数值，能用 between 就不要用 in：

10、小表驱动大表，即小的数据集驱动大的数据集：

in 和 exists 都可以用于子查询，那么 MySQL 中 in 和 exists 有什么区别呢？

• （1）使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。
• （2）in在内表查询或者外表查询过程中都会用到索引；exists仅在内表查询时会用到索引
• （3）一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询的结果集较小，外表较大时，使用in效率更高。
• （4）对于 not in 和 not exists，not exists 效率比 not in 的效率高，与子查询的结果集无关，因为 not in 对于内外表都进行了全表扫描，没有使用到索引。not exists的子查询中可以用到表上的索引。

11、使用union all 替换 union：

当SQL语句需要union两个查询结果集合时，这两个结果集合会以union all的方式被合并，然后再输出最终结果前进行排序。如果用union all替代union，这样排序就不是不要了，效率就会因此得到提高.。需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。

12、优化Group by，使用where子句替换Having子句：

避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。

on、where、having这三个都可以加条件的子句，on是最先执行，where次之，having最后。

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。

低效:SELECT JOB, AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'

高效:SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB

13、尽量使用数字型字段：

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

14、写出统一的SQL语句：

对于以下两句SQL语句，很多人都认为是相同的。不过数据库查询优化器则认为是不同的，虽然只是大小写不同，但必须进行两次解析，生成2个执行计划。所以应该保证相同的查询语句在任何地方都一致，多一个空格都不行。

• select * from dual
• select * From dual

15、使用复合索引须遵守最左前缀原则：

复合索引必须使用到最左边字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

16、当需要全表删除且无需回滚时，使用Truncate替代delete：

drop、truncate、delete的区别：https://www.cdsy.xyz/computer/soft/database/mysql/230617/cd44251.html

17、使用表的别名:

当在SQL语句中连接多个表时, 使用表的别名并把别名前缀用于每个Column上，这样可以减少解析的时间并减少那些由Column歧义引起的语法错误。

18、避免使用耗费资源的操作：

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句，会启动SQL引擎执行耗费资源的排序功能，DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。通常。带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写，如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

19、Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

20、应尽可能的避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。

21、尽量使用表变量来代替临时表。

22、考虑使用“临时表”暂存中间结果。临时表并不是不可使用，适当地使用它们可以使某些查询更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。将临时结果暂存在临时表，后面的查询就在临时表中查询了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。但是，对于一次性事件，较好使用导出表。

23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25、避免频繁创建和删除临时表，以减少系统表资源的消耗。

26、尽量避免使用游标，因为游标的效率较差。与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。

27、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。

28、尽量避免大事务操作，提高系统并发能力。

29、在运行代码中，尽量使用PreparedStatement来查询，不要用Statement。

三、索引的选择性与前缀索引：

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，没必要建索引，让查询做全表扫描就好了。

第二种情况是索引的选择性较低。所谓索引的选择性，是指 不重复的索引值 与 表记录数量 的比值：

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

例如，employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

假设employees表只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引建上：

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

此时再执行一遍按名字查询，比较分析一下与建索引前的结果：

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

性能的提升是显著的，查询速度提高了120多倍。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引