MySQL查询优化

查询优化，不涉及修改应用、数据库配置、架构等

1、发现慢查询SQL

1.1、增加mysql慢查询配置

vim /etc/my.cnf

[mysqld]
# 开启慢查询功能
slow_query_log = ON
# 慢查询日志
slow_query_log_file = /usr/local/mysql/data/izwz9f6ny09ico7zccpy51z-slow.log
# 超过2秒的SQL即为慢查询
long_query_time = 2
1
2
3
4
5
6
7

1.2、数据库连接池使用druid

	com.alibaba
	druid-spring-boot-starter
	1.2.6


# 超过2秒即为慢查询，可通过ip:port/driud/index.html查看
spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=2000
1
2
3
4
5
6
7
8

1.3、切面耗时统计

2、优化实战

2.1、表结构

前言：表数据皆为随机生成，身份证号、手机号、姓名、微信号等若有相同，纯属巧合。生成的数据仅用于测试说明问题，不必理会合理性。

2.1.1、【用户表】t_user

数据量：150w

2.1.2、【用户详情】t_user_detail

说明：体现和表t_user的一对一关系，数据量：150w

2.1.3、【收货地址】t_delivery_address

说明：体现和表t_user的一对多关系，数据量：150w

2.1.4、【就读院校】t_school

说明：体现和表t_user的多对多关系，关系由2.1.5表t_user_school_rel维护，数据量：100

2.1.5、【用户与就读院校关系表】t_user_school_rel

数据量：150w

2.2、优化查询的建议

2.2.1、列出所需的列名，不要使用select * from table

Q1: 查询1万个江苏省连云港市海州区出生的用户

SQL1：select * from t_user where id_card_no like '320706%' limit 10000;
SQL1 耗时：8.028s

SQL2: select id, name, id_card_no, phone from t_user where id_card_no like '320706%' limit 10000;
SQL2 耗时：4.539s

<--------------------------------------------------------------------------------------------------------------------->

Q2：查询以“求大学”为后缀毕业的用户
SQL3：select * from t_user u inner join (select * from t_user_detail where school like '%求大学') detail on u.id = detail.user_id;
SQL3耗时：30.035s

SQL4：select * from t_user u inner join (select user_id from t_user_detail where school like '%求大学') detail on u.id = detail.user_id
SQL4耗时：16.422s
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

一般列表查询，不会返回1万条数据，但是在联表查询时，会形成中间表或者子表，这些中间表或字表的数据量有时比较大，select少一个字段，就会有明显效果。

2.2.2、用联表代替in操作

当in的数据量较大时（in中的数据量占总数据量30%左右），可能会导致索引失效，故使用联表的方式，仍可能存在优化空间，尽量使用inner join代替left/right join。

Q2：查询以“求大学”为后缀毕业的用户
SQL5：select * from t_user where id in (select user_id from t_user_detail where school like '%求大学') limit 1000;
SQL5耗时：1.426s

SQL6：select * from t_user u inner join (select user_id from t_user_detail where school like '%求大学') detail on u.id = detail.user_id limit 1000;
SQL3耗时：0.246s
1
2
3
4
5
6

2.2.3、合理使用in和exists

1> 使用in：外表大，内表小

2> 使用exists：外表小，内表大

Q3：查询被用户使用且有效的院校详情
SQL7:select SQL_NO_CACHE * from t_school s where EXISTS (select 1 from t_user_detail d where s.name = d.school)
SQL7耗时：0.408s

SQL8：select SQL_NO_CACHE * from t_school where name in (select school from t_user_detail)
SQL8耗时：5.247s

此处t_user_detail（150w条数据）为内表，t_school（100条数据）为外表

<--------------------------------------------------------------------------------------------------------------------->

SQL9:select distinct school from t_user_detail d where exists (select 1 from t_school s where s.name = d.school)
SQL9耗时：36.618s

SQL10:select distinct school from t_user_detail where school in (select name from t_school)
SQL10耗时：15.949s

此处t_user_detail（150w条数据）为外表，t_school（100条数据）为内表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.2.4、尽量使用union all代替union

union会去除重复行，union all不会，而有些集合，已知是不重复的，就可以使用union all，以提高效率。

注：union并不是很有效的方式

1、每个被合并的SQL集合，是串行执行的

SQL11：select sleep(2) from dual union all select sleep(4) from dual;
SQL11耗时：6.247s
1
2

2、当对应的列类型不一致，但又可以转换时，MySQL会隐式转换

sex是tinyint，name是varchar，从结果看，是将sex转成了name所属的类型，如果SQL还要对和并后的结果做条件查询，由于存在隐式转换时，索引将会失效

SQL12:
select sex from t_user where id = '0025699572a44bcd89eeb98f7cd0c1bc'
union all
select name from t_user where id = '002550a6309f4dbd88268d8207e78a76'

返回结果：
sex
0
刁瑗
说明：如果集合的对应的列名不一致，返回结果将使用第一行的列名
1
2
3
4
5
6
7
8
9
10

2.2.5、分页优化

由于3.1中的表数据都只有150万，分页还看不出明显的差异，此处生成了15000000数据量的订单表t_order

Q4：订单分页查询，pageNum=14000000,pageSize=10
SQL13: select * from t_order limit 14000000, 10
SQL13耗时：2.738s

SQL14：select * from t_order where id >= (select id from t_order limit 14000000, 1) limit 10
SQL14耗时：2.066s

SQL15：select o1.* from t_order o1 inner join (select id from t_order limit 14000000, 10) o2 on o1.id = o2.id
SQL15耗时：2.036s

说明：由于查询条件比较简单，且有索引，所以需要较大的数据量才能看出一些的差异，从结果上看，SQL14和SQL15的效率比SQL13高，如果id是自增类型，且没有删除数据，还存在更加简单和高效的方式
1
2
3
4
5
6
7
8
9
10
11

2.2.6、尽量使用distinct代替group by

distinct和group by都可以去重，但是在没有索引时，distinct效率比group by高些，原因是distinct不需要进行排序

Q5: 查询用户有多少种性别
SQL16：select count(distinct sex) from t_user
SQL16耗时：0.976s

SQL17: select count(1) from (select sex from t_user group by sex) a
SQL17耗时：1.152s
1
2
3
4
5
6

2.2.7、如果确定只有一条数据，添加限制limit 1

如果添加了limit 1，只要找到了满足条件的数据，就不会再往后寻找。如果条件是唯一索引，可能没有差异，如果有索引，但索引不唯一，差异可能不明显，但如果没有索引，就会存在较为明显的差异。

Q6：查询身份证号为530600200807144236的用户详情(身份证号无索引)
SQL18: select * from t_user where id_card_no = '530600200807144236'
SQL18耗时：1.355s

SQL19: select * from t_user where id_card_no = '530600200807144236' limit 1
SQL19耗时：0.382s

注：如果身份证号没有创建唯一索引，当存在相同身份证号的用户信息时，不加limit 1时，会返回一个对象集合，而mybatis使用一个对接接收，也会抛出异常
1
2
3
4
5
6
7
8

说明：暂时想到这么多，若有新增，后续再补充

2.3、索引的优化查询

2.2中的优化建议，都没有涉及到索引，而要高效查询，索引必不可少

2.3.1、索引是什么

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库的表结构。如果数据库是一本字典，那字典前面，按照拼音查字，按照笔画查字，就是字典的索引。如果数据库是通讯录，那通讯录的A,B,C…Z就是索引。如果数据库是一本书，那目录就是索引。总之，索引就是帮助快速查询的，但并不是所有的索引，都会提高查询效率，所以如何创建索引，也很重要。

2.3.2、常见索引类型

1、按照唯一性分

1.1、主键索引

具有唯一性，可以是多列组合而成，也可以是单独的某一列，索引和数据是放在一起的，也称聚簇索引。

1.2、唯一索引

主键索引是一种特殊的唯一索引，唯一索引在入库前，都会检查索引数据的唯一性。

1.3、普通索引

基本的索引类型，没有特别特性，可以为空，没有唯一性控制，适用于快速查询

2、按照列数量分

2.1、单列索引

索引由一列组成

2.2、组合索引

索引由多列组成，如a,b,c,d组成索引

3、按照数据和索引的位置分

3.1、聚簇索引

数据和索引存放在一起，命中索引，即可取出数据，不用进行回表查询。mysql默认主键就是聚簇索引，如果没有主键，会找从列中找一个非空唯一的列作为聚簇索引

3.2、非聚簇索引

数据和索引是分开存放的，命中索引后，要到聚簇所在的地方取数据，才能查看整条完整的数据。

说明：索引是经过排序的，有索引的表，在插入数据时，速度会慢一些。3.2.6章节中，如果去重的字段是索引，group by即使要排序，效率和distinct差不多，是因为索引已经排好序了。这也是为什么使用自增的主键，效率是最高的，因为插入时，不需要找寻当前插入数据的位置。

2.3.3、索引的长度

1、各字符集占用空间

latin1:

1 character=1byte, 1汉字=2 character,

也就是说一个字段定义成 varchar(100)，则它可以存储50个汉字或者100个字母。

utf8:

1 character=3bytes, 1汉字=1 character

也就是说一个字段定义成 varchar(100)，则它可以存储100个汉字或者100个字母。

gbk:

1 character=2bytes,1汉字=1 character

也就是说一个字段定义成 varchar(100)，则它可以存储100个汉字或者100个字母。

2、各类型占用空间

2.1、如3.2.5订单表t_order

CREATE TABLE t_order (
id bigint(20) NOT NULL DEFAULT ‘0’,
user_id bigint(20) DEFAULT NULL,
price decimal(10,2) DEFAULT NULL,
sign_time varchar(20) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

通过explain查看key_len的长度为8

2.2、如3.1用户表t_user

CREATE TABLE t_user (
id varchar(32) COLLATE utf8_bin NOT NULL COMMENT ‘用户标识’,
name varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT ‘姓名’,
nick_name varchar(40) COLLATE utf8_bin DEFAULT NULL COMMENT ‘昵称’,
id_card_no varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT ‘身份证号’,
sex tinyint(1) DEFAULT NULL COMMENT ‘性别’,
phone varchar(15) COLLATE utf8_bin DEFAULT NULL COMMENT ‘手机号’,
email varchar(40) COLLATE utf8_bin DEFAULT NULL COMMENT ‘邮件’,
wechat varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT ‘微信号’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘用户’

通过explain查看key_len的长度为：32 * 3 + 2 = 98；

utf-8一个字符占用3个字节，长度可变+2，如果是普通索引，可以为空+1。

故索引长度 = 字符集占用空间*列长度 + 是否为空 + 是否可变

结论：

1、数值类型的索引，比字符串更节省空间，且在插入数据时，需要根据主键的值找到合适的位置，又数值类型比较，快于字符串类型，所以选择数值类型作为主键，优于字符串类型。

2、字段也尽量选择占用空间小的，这样相同的空间，可以存放更多的索引和数据。如果你不知道如何优化列类型，可借助PROCEDURE ANALYSE()帮你完成。如下图所示，最后一列就是mysql建议的列类型。从建议可以看出，如果没有空值，就建议显示声明，长度不变的，尽量声明为不可变的，这样可以节省空间。

2.3.4、创建索引的建议

1、尽可能创建唯一索引

唯一索引的搜索，快于普通索引，但插入数据时的效率稍慢于普通索引

Q7: 根据用户敖武武的身份证号查询用户详情
SQL20:select * from t_user where id_card_no = '320706195205031112'
SQL20耗时：1.388s

-- 将身份证号设置为普通索引
alter table t_user add index(id_card_no)
SQL20耗时：0.387s

-- 由于入库时，没有将身份证号设置为唯一，导致存在一些重复的，删除重复数据，并将普通索引改为唯一索引, 数据量1497292
alter table t_user add unique(id_card_no)
SQL20耗时：0.243s
1
2
3
4
5
6
7
8
9
10
11

2、尽量不要在重复值较多的列创建索引

如性别，只有男，女，未知等少数值，并不适合创建索引

3、在联表字段添加索引

在数据量较大时，索引的效果会非常显著

Q8：根据用户敖武武的id查询毕业院校详情
SQL21：select * from t_user u left join t_user_school_rel rel on u.id = rel.user_id left join t_school s on rel.school_id = s.id where u.id = '0000bc017fc14cda9bd3ea0bd8f733ab'
SQL21耗时：0.974s

-- 添加联表字段索引
alter table t_user_school_rel add index user_school_idx (user_id, school_id);
SQL21耗时：0.241s
1
2
3
4
5
6
7

4、单表索引数量建议不超过5个

5、尽量创建组合索引

6、在一些特定位置的列创建索引

6.1、出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列创建索引

6.2、在 ORDER BY、GROUP BY、DISTINCT 中的列创建索引

并不要将符合 6.1 和 6.2 中的字段的列都建立一个索引， 通常将两处的列建立联合索引效果更好。如查询列表时，需要按照创建时间排序，则可以将该字段添加为索引。

2.4、索引失效的场景

2.4.1、存在隐私或显示转换

1、隐私转换，当列类型和值的类型不一致时，容易发生隐私转换，如id_card_no是字符串类型，220281197710263190是数值类型，此时会导致隐私转换
select * from t_user where id_card_no = 220281197710263190;
2、显示转换，将索引字段进行运算后再参与比较，这种运算包括函数
select * from t_user where id_card_no + "" = '220281197710263190';
select * from t_user where id_card_no = '220281197710263190' + '';
注意：在联表查询时，on中条件的列类型不一致，但是执行没有报错，也是发生了隐私转换，索引也会失效
1
2
3
4
5
6

2.4.2、不符合最左原则

单列索引和组合索引，都有从左匹配的特性。最左匹配原则：一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配

1、单列索引

单列索引就好比在字典中根据拼音查字，中：zhong，如果第一个字母z不知道，那可能是chong，既然无法确定，只能全部都找一遍，就这样索引失效了。

2、组合索引

组合索引就好比在字典中根据拼音查词语，泪水，如果不知道第一个字泪，那词语可能是眼泪，既然不确定，也只好全部都找一遍。

这种不确定性，可以是%，也可以是_，%表示多个未知的，_表示一个未知的
-- 索引失效
select * from t_user where id_card_no like '_20000197307088297';

-- 索引生效
select * from t_user where id_card_no like '12000019730708%'
1
2
3
4
5
6

2.4.3、!= 、<> 、is not null

select * from t_user where id_card_no <> ‘120000197307088297’ 会导致索引失效

2.4.4、in数据量较大、not in回表

以下sql可通过explain + sql查看是否走索引

-- 【走索引】in的数据量较少时，走索引
select * from t_user where id in ('00006f5053ff4dd1aaa6ea812f7cccc0')

-- 【不走索引】in的数据量较大时，不走索引，大约多余总数据量的30%
select * from t_user where id in (select user_id from t_user_detail)

-- 【走索引】根据id查出数据后，因为id是聚簇索引，故数据和索引是放在一起的，不需要回表查询，not in会走索引
select * from t_user where id not in ('00006f5053ff4dd1aaa6ea812f7cccc0')

-- 【不走索引】根据id_card_no查出数据，因为id_card_no是非聚簇索引，需要回表，才能查出详情，not in不走索引
select * from t_user where id_card_no not in ('320706195909232131')

-- 【走索引】根据id_card_no查出数据，select的列只有id_card_no，，即使id_card_no是非聚簇索引，是不需要回表查询的，not in走索引
select id_card_no from t_user where id_card_no not in ('320706195909232131');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.4.5、or连接的条件存在不是索引的列

-- 【走索引】 id_card_no和id都是索引，索引生效
select * from t_user where id_card_no= '320706195909232131' or id = '000063437bc140928d7eae38b8f3a1b2';

-- 【不走索引】name不是索引，索引失效
select name from t_user where id_card_no= '320706195909232131' or name = '张三';
1
2
3
4
5

3、相关名词

3.1、索引覆盖和回表查询

了解回表查询之前，先了解聚簇索引和非聚簇索引的结构。假设表格t_test有如下数据

1、如下图所示为聚簇索引，特点是数据和索引，放在一起，命中索引，即可取出对应的数据

2、如下图所示为非聚簇索引，特点是只有索引，没有整行数据，索引的叶子节点，存放的是数据的主键。

3、回表查询

-- 查询column2='ww'的详情
select * from t_test where column2 = 'ww';

1、根据非聚簇索引column2，找到column2='ww'所在数据的非聚簇索引，即column1=9
2、由于返回的是”*“,代表返回所有的列，即column1、column2、column3、column4，但在非聚簇索引的叶子节点，只有column1
3、所以通过column1=9,再去聚簇索引中找到column1=9的数据，从而查询出整条数据"column1=9、column2=ww、column3=f、column4=B"
说明：第三步也称回表查询，所以为了提高效率，应该尽量避免回表查询。单列索引在非聚簇索引的叶子节点存放的数据只有一列，如果是组合索引，就是多列，很容易避免回表查询，所以返回需要的列，代替”*“也很关键。
1
2
3
4
5
6
7

4、索引覆盖

select column2 from t_test where column2 not in ('ww');
由3可知，非聚簇索引的叶子节点，存放的数据就是column2，返回的列也只有column2，这种返回结果已经存在于索引上的情况，就是索引覆盖
1
2

3.2、索引下推

SQL1: select * from t_user where name = '钟义新' and phone = '13708817332';
SQL1耗时：1.358s

SQL2: select * from t_user where name like '钟%' and phone = '13708817332';
SQL2耗时：1.358s

-- 创建组合索引
create index name_phone on t_user (name, phone);

-- 会索引下推
SQL3: select * from t_user where name like '钟%' and phone = '13702220393';
SQL3耗时：0.390s
SQL4: select SQL_NO_CACHE * from t_user where name like '钟%' and phone like '1370222039%';
SQL4耗时: 0.388s

-- 不会索引下推
SQL5:select SQL_NO_CACHE * from t_user where name = '钟义新' and phone = '13702220393'
SQL5耗时：0.389s
SQL6: select SQL_NO_CACHE * from t_user where name = '钟义新' and phone = '13702220393';
SQL6耗时：0.389s
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

索引下推，只会出现在组合索引中，且只有存在失效的组合索引列。

eg: select * from t_user where name like ‘钟%’ and phone = ‘13702220393’

select * from t_user where name like ‘钟%’ 由于name是范围查询，根据最左匹配原则（3.4.2章节），phone列查询将不会生效。条件查询有多条记录。

在5.6版本之前，拿到所有符合条件的数据，进行回表查询，然后将结果返回到server层，server层再根据phone的条件进行过滤。

在5.6版本之后，由于索引下推，server层将phone的过滤条件，交给了存储引擎层判断，这样可以过滤掉一些不符合条件的数据，从而减少回表查询的数量。存储引擎层可以对下推的索引进行判断，前提是组合索引中存在phone值，不然无法拿到phone值进行判断/过滤。

3.3、前缀索引

索引不一定要具体的某一个列，也可以是某个列的一部分，如身份证号的前6位，即可快速查找到同县人。

aler table t_user add index county(id_card_no(6));

3.4、驱动表

连接查询时，原则是小表驱动大表。

左连接，驱动表为左边的表，右连接，驱动表为右边的表，故如果有条件，应该先通过条件过滤，缩小驱动表的数据范围。

内连接时，mysql会自动选择小表为驱动表。驱动表可通过explain查看，id相同时，上面为驱动表，id不同时，id大的为驱动表。