Mysql进阶SQL优化

SQL优化在开发场景中必不可少的技能之一，它能最大限度的提升SQL查询性能，如果随意使用也会出现不可预料的结局。

1、为什么要优化SQL

我们先说说不优化SQL造成什么现象。常见问题是响应时间长，用户体验感低。数据库频繁争抢锁，浪费性能。CPU过载，资源消耗高等问题。为了避免这些问题，不能盲目的添加资源，尽可能做到物尽其用。现实生活中不管对于ToC还是ToB，对外核心点：体验感永远是排在第一位，不管系统好坏，一旦体验感不行，都会丧失客户，这也是我们需要去优化SQL的原因之一。

优化SQL旨在保证提高数据库性能，提升应用程序响应速度，提升体验感。

2、怎么优化SQL

2.1、开启慢查询

a）查看Mysql是否开启慢查询，一般默认没有开启

## 查看慢查询开关命令
show variables like 'slow_query_log';

b）开启慢查询日志

1、在Mysql安装目录中找到my.ini文件

2、添加以下参数：

## 开启慢查询
slow_query_log = 1
## 存放目录
slow_query_log_file = D:\developDoc\slowQueryLog\slowlog.log
## 执行时间超过多少秒（单位秒）就会被记录到慢查询日志中
long_query_time = 1

3、重启Mysql服务。

## windows环境
1、services.msc
2、查询Mysql服务。
3、点击重启即可

## linux环境
systemctl restart mysqld;

4、查询是否开启与存放路径。

b）查询语句（根据自身需求书写SQL）

c）查看慢查询日志

2.2、Profiling耗时分析（非必须）

后续补充。

2.3、语句分析

语句分析有两种命令方式：explain（常用），desc（一般用于查看表结构）；除了关键字外不一样，其他的都一样。

2.3.1、Explain/Desc

我们先看看explain执行语句长什么样子。在分析他们之间的关系与含义。

图中可以看到 id， select_type，table，partitions，type，possible_keys，key，key_len，ref，rows，filtered，extra 这些字段，下面将分开讲述这些字段。

SQL分析中必须查看 type、possible_keys、key、extra 四列，ken_len 其次，其他的可以根据需求查看。

2.3.1.1、id

id：序号值，旨在控制执行顺序，值越大越先执行，值相同从上到下执行。

单行时，不用关注id数值，多行顺序值，需要关注执行顺序来分析SQL信息。

例如：

查询中国银行中有多少用户，并提取用户信息。

select
	*
from
	d_user du
where
	du.user_id in (
	select
		dba.user_id
	from
		d_bank_account dba
	where
		dba.user_id = du.user_id
		and dba.bank_id = (
		select
			db.bank_id
		from
			d_bank db
		where
			db.bank_name = '中国银行')
) and du.id < 3;

先看看原始SQL后（先心里有一个执行顺序），在配合explain中的序号和别名，看是否一致。

中国银行的储蓄用户，第一步：先查询中国银行id。第二部：在查询中国银行id对于的用户id。第三步：根据用户id，查询用户信息。根据步骤可以看出执行顺序：db，dba，du。explain执行顺序：3，2，1。可以看出结论与我们分析的一致，结论成立。

2.3.1.2、select_type

select_type：搜索类型，常见类型 SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION等。

具体主要类型分析：

Simple（简单类型）
- 简单的 SELECT 查询，不包含子查询或 UNION。
PRIMARY（最外层查询）
- 最外层查询，如果查询中包含任何复杂的子部分，最外层查询则被标记为 PRIMARY
SUBQUERY（子查询）
- 在 SELECT 或 WHERE 列表中包含了子查询。
DERIVED
- 在 FROM 列表中包含的子查询被标记为 DERIVED（衍生），MySQL 会递归执行这些子查询，把结果放在临时表里。
- 后续补充
UNION
- 如果第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为 DERIVED。
- 后续补充
UNION RESULT
- 从 UNION 表获取结果的 SELECT
- 后续补充
DEPENDENT SUBQUERY
- 在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层。
- 后续补充
UNCACHEABLE SUBQUERY
- 无法被缓存的子查询。
- 后续补充

2.3.1.3、table

执行SQL所属表。

2.3.1.4、partitions

匹配的分区信息。如果查询是基于分区表的话，会显示查询将访问的分区

2.3.1.5、type（类型）

访问类型，如 ALL（全表扫描）、index（全索引扫描）、range（范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描），const（常量），system（系统）等。

性能从大到小 const > eq_ref > ref > range > index > all。如果Sql类型是 ALL 就必须优化，index 尽可能优化到range。

2.3.1.6、possible_keys

SQL可能使用的索引，包含零个或多个。

2.3.1.7、key

SQL使用的索引。

2.3.1.8、key_len

SQL只用的索引长度。

2.3.1.9、ref

显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值。

2.3.1.10、rows

SQL执行影响行数，不是很准确。

2.3.1.11、filtered

查询的表行占表的百分比。

2.3.1.12、extra

拓展信息，也是SQL分析最重要的列。

3、常见SQL问题

3.1、未走索引，全表检索

explain 中 type 显示 ALL 时是没有走到索引的，具体可查看 key 列。

3.2、分组未走索引

group by 中的字段没有建立索引，造成extra列显示 Using temporary 信息。

3.2、排序未走索引

order by 中字段没有走索引。extra列显示 Using filesort 信息

3.3、索引失效

字段建立索引没有走到。type 值显示 ALL。

4、优化策略

4.0、数据结构

CREATE TABLE `d_user` (
  `id` int NOT NULL COMMENT '主键id',
  `user_id` varchar(50) NOT NULL COMMENT '用户id',
  `user_name` varchar(100) NOT NULL COMMENT '用户名',
  `phone` varchar(100) NOT NULL,
  `age` int NOT NULL,
  `gender` tinyint DEFAULT '0' COMMENT '性别',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `test_not_null` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_qe_user_id` (`user_id`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB COMMENT='用户信息表';


CREATE TABLE `d_bank` (
  `id` int NOT NULL COMMENT '主键id',
  `bank_id` varchar(50) NOT NULL COMMENT '银行id',
  `bank_name` varchar(50) NOT NULL COMMENT '银行名',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='银行表';


CREATE TABLE `d_bank_account` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `account_id` varchar(50) NOT NULL COMMENT '银行卡id',
  `account_no` varchar(50) NOT NULL COMMENT '银行卡编号',
  `account_type` tinyint DEFAULT '0' COMMENT '银行卡类型',
  `bank_id` varchar(50) DEFAULT NULL COMMENT '银行卡id',
  `user_id` varchar(50) DEFAULT NULL COMMENT '持有人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB comment='用户储蓄账号关系表';

4.1、策略方向

1、经常搜索列建立索引。2、最左前缀匹配法则。3、索引覆盖。

我们回顾哈上篇文章的索引：主键索引、唯一索引、普通索引（单列索引、联合索引）、全文索引（一般不用）

建议：尽可能新建联合索引，少新建单列索引，能走主键索引绝不走唯一索引或普通索引（避免回表查询）。

4.2、问题分析

4.2.0、前序信息

表信息	索引信息	索引截图	数据量
d_user	无		999803
d_bank	无		5
d_bank_account	有		2774925

查看索引语法：

## 查询表索引
## table_name 数据表
show index from table_name;

4.2.1、未走索引现象与优化

案例一：查询手机号='13500000215'用户信息

语句：select * from d_user where phone = '13500000215';

数据：可以看出执行耗时1.5秒

explain分析：可以看出 type = ALL 且 key = null 没有走索引。

对phone字段添加索引：

图中可知：type 由 all 优化为了 ref , key 也使用了刚刚新建索引，整个耗时由 1.5秒提升至忽略不计。

案例二：查询user_id=4a163cc6e18341698bc9b3a8ce64ee88用户信息

图中可知：耗时1.5秒左右。

分析：用户id在整个系统中应该唯一，不可能出现所谓了重复问题，所以对user_id字段添加唯一索引。

总结：通过上面两个案例可知，走索引与不走索引的性能差异之大，其中唯一索引性能提升最大，type 等于了 const，这也是 SQL优化中最优解，但是这个类型只有唯一索引与主键索引可以达到，其他的索引最多只能达到eq_ref ，索引优化中常见是 ref 是最优。

4.2.2、走索引缺失效与优化

索引失效常见有如下几种场景：

4.2.2.1、未走最左前缀匹配法则，导致索引失效。

可以看出 age 字段在联合索引中，但是

4.2.2.2、范围查询列之后列，不走索引。

4.2.2.2.1、案例分析

是否失效需要查看联合索引中各个字段的索引长度：

a）先看看全匹配联合索引的索引长度：key_len = 408

b）查看gender索引长度：408 - 106 = 2

c）查看age与user_name索引长度：age ：408 - 402 = 6。user_name : 402

从上面三个步骤来看三个字段索引长度，usename = 402, age = 6, gender = 2

案例一：查看姓名=‘测试用户2’ 且年龄>20 且性别=1；

图中可知：索引长度 = 406 缺少了 gender的索引长度（失效）。

解决方式：范围查询更改范围查询+等值查询。例如： > 更改 >= ， < 更改 <=等。

下图可知：将 > 更改为 >= 后，走全索引。

4.2.2.2.2、结论

范围查询需要将 > ，< 更换为 >=, <=来走索引。

4.2.2.3、全模糊或前缀模糊不走索引。

4.2.2.3.1、案例分析

案例一：查询姓名包含测试用户的储蓄用户。

案例二：查询已123结尾的储蓄用户。

4.2.2.3.2、结论

从上面两个案例来看，对于字符类型的模糊匹配会造成索引失效（不走索引）。

解决方式：采用后缀匹配即 like 'xxx%';

图中可知：后缀模糊匹配与等值匹配都走索引，且索引长度一致，除type不一致外。

4.2.2.4、函数计算索引失效。

4.2.2.4.1、案例分析

案例一：查询用户姓名长度大于7的信息

案例二：查询以前2个字符为前缀匹配

4.2.2.4.2、结论

从上面两个案例来看，对于使用计算函数列无法采用索引。

解决方式：1、创建函数索引（8.0引入）。2、索引覆盖

4.2.2.5、or条件索引失效

4.2.2.5.1、案例分析

案例一：前面条件走索引字段 or 条件字段不是索引字段

案例二：前面条件走索引 or 条件字段索引失效

4.2.2.5.2、结论

从上面两个案例可知：一旦 or中某一条件索引失效，整个SQL的索引都失效。

解决方法：or 前后条件都走索引。

5、总结

本文还未完善，后续会补充多表关联讲解。