步骤

1. 慢查询工具分析项目中执行时间过长的sql
2. SQL本编写优化

操作

慢查询

慢查询，全名是慢查询日志，是mysql提供的一种日志记录，用来记录在执行sql语句时，响应时间超过阈值的语句。
阈值“long_query_time”，在sql语句执行的时候超过这个值时，就会将语句记录到日志中，long_query_tmie默认时间是10s。
mysql中慢查询日志默认是未开启的，开启慢查询有两种方式：1、修改my.ini文件；2、sql语句设置

开启慢查询

1、修改my.ini文件

slow-query-log=1  # 1是开启，0是关闭
slow_query_log_file="slow.log"  # 日志文件，及保存位置，默认保存在/data/下
long_query_time=10  # 执行时间阈值，超过这个时间就会被写入到慢查询日志中

2、SQL语句配置

#设置慢查询保存日志名称
set global slow_query_log_file='slow.log';
#查看慢查询超时时间
show variables like '%long_query_time%';  
# 开启慢查询
set global slow_query_log=1

设置完了之后可以通过sql语句查看慢查询状态

# 查看慢查询状态
SHOW VARIABLES LIKE 'slow_query%';
#查看慢查询超时时间
show variables like '%long_query_time%';  

上面两种方法都能开启慢查询，修改my.ini文件需要重启mysql服务；直接使用SQL语句配置不用重启，但是如果mysql服务重启了那么配置的将会失效

查看慢查询日志文件

在mysql\bin目录下提供了一个工具，可以快速的查看这个日志文件

mysqldumpslow 命令
注意：这个命令在windows下是“mysqldumpslow.pl”，需使用“perl”命令执行，这个命令直接在“https://strawberryperl.com/”下载安装即可

mysqldumpslow命令后面可以追加一下的一些参数
-v verbose # 详细的
-d debug # 调试
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default # 排序

            al: average lock time	# 按平均锁定时间排序
            ar: average rows sent 	# 按平均返回行排序
            at: average query time 	# 按平均查询时间排序
             c: count 		# 按执行次数排序
             l: lock time 		# 按锁定时间排序
             r: rows sent 		# 按返回行排序
             t: query time  	# 按执行时间排序

-r reverse the sort order (largest last instead of first) # 排序反转
-t NUM just show the top n queries # 只显示指定条数
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names # 名称中必须包含指定的“num”
-g PATTERN grep: only consider stmts that include this string # 只显示包含指定字符串的内容
-h HOSTNAME hostname of db server for -slow.log filename (can be wildcard), default is '', i.e. match all # 显示指定服务器主机名的内容
-i NAME name of server instance (if using mysql.server startup script) # 显示包含指定服务器实例的名称
-l don't subtract lock time from total time

mysqldumpslow -s t -t 10 F:\mysql\data\slow.log

执行结果：count: 次数 Time=执行一次耗费时间 (总耗费时间) Lock=锁定时间 (总锁定时间) Rows=返回行数 (总返回行数) 执行角色 执行主机

Count: 1  Time=5919.94s (5919s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  CALL `proc2`()

Count: 1  Time=4.00s (3s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  SELECT SLEEP(N)

SQL编写优化

本次操作的表结构

CREATE TABLE `bath_center`  (
  `ky_date` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开业时间',
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `1v1_vip_total` int(11) NOT NULL COMMENT '一级会员',
  `1v2_vip_total` int(11) NOT NULL COMMENT '一级会员',
  `black_vip_total` int(11) NOT NULL COMMENT '黑卡会员：老板朋友',
  `in_money` int(11) NOT NULL COMMENT '收入',
  `out_money` int(11) NOT NULL COMMENT '支出',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 353878 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '洗浴中心表' ROW_FORMAT = Compact;
--------------------------------------------------------------------------------------------
CREATE TABLE `bath_center_1v`  (
  `bc_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '洗浴中心ID',
  `center_1v` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '等级',
  PRIMARY KEY (`bc_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 383434 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '级别表' ROW_FORMAT = Compact;

sql语句

-- 关联子查询
-- 耗时1.483s
SELECT a.ky_date, a.id, a.1v1_vip_total,(
	SELECT b.center_1v
	FROM bath_center_1v b
	WHERE a.id = b.bc_id
)center_1v
FROM bath_center a
ORDER BY a.id, a.1v1_vip_total, a.ky_date;

-- 表连接
-- 耗时0.777s
SELECT a.ky_date,a.id,a.1v1_vip_total,b.center_1v
FROM bath_center a, bath_center_1v b
WHERE a.id = b.bc_id
ORDER BY a.id, b.center_1v,a.1v1_vip_total,a.black_vip_total

执行结果

总结

通过上面两条查询语句可以看出，关联子查询的速度要比表连接的速度多了很多；可以总结出，在数据量大的时候尽量不要写子查询，因为子查询会建立一张临时表，在查询完了之后，还有将这张临时表删除，所有子查询的速度一定会受到一定影响；使用表连接查询（JOIN）不要创建临时表，所以速度一定比子查询块。
但是表的数据量小，这个两者的差距就可以忽视了

其他优化

1、SELECT 后面使用具体字段代替“*”

2、避免在 where后面对字段进行null判断， 这样会放弃使用索引进行全部扫描：select id from test where num is null;
解决：可以将字段上的null默认设置为0，这样在查询时就会走索引：select id from test where num = 0;

3、避免在where后面使用“！=”或者“<>”操作符，会进行全表扫描

4、避免在where后面使用“or”来连接条件，会进行全表扫描：select id from test where num=10 or num=30
解决：可以使“union”关键字，比如
select id from test where num = 10;
union all
select id from where num = 30;
对于union优先选择 union all

5、尽量避免使用“IN”和“NOT IN”，容易导致全表扫描：select id from test where num in (1,2,3);
如果是连续的数值，能用between . and .：select id from test where num between 1 and 3;

6、慎用模糊查询，在使用“like”时要在两边加上"%"避免索引失效：select id from t where name like '%abc%';

7、避免在where子句中对字段进行运算操作，会导致全表扫描：select id from t where num / 2 = 100;
解决：在“=”右边进行运算操作无影响：select id from t where num = 100 * 2;

8、避免在where子句中对字段进行函数操作，会导致全部扫描：select id from t where substring(name,1,3)='abc'; --name以abc开头的id
解决：select id from t where name like 'abc%'; --name以abc开头的id

反范式设计优化

数据库

数据库设计有三大范式
1、数据表中的每一项都是不可分割的基本数据项；简单来说就是要确保每列的原子性（不可再拆分）

2、确保表中每一列都和主键相关，而不能只与主键的某一部分相关；就是在一个表中只能保存一种数据，不能把各种数据保存在同一表中

3、确保表中每一项都和组件直接关联，而不能间接关联

数据库三大范式的目的是为了减少冗余，当对查询很不友好，因为会多很多表；数据量相同的情况下，单表查询的速度永远比多表查询块。所以我们在进行表设计的时候，可以适当的违反一下第三范式，合理的冗余设计可以减少“JOIN”的查询；冗余字段的设计应遵循两点：1、不能频繁修改；2、不是varchar超长字段，不是text字段

例如：商品类目名称，使用频繁，且名称基本不会改变，那么就可以在相关联的表中冗余存储类目名称，避免关联查询。
用户信息表中的性别，也是设置了之后基本不会改变。

SQL执行计划：EXPLAIN

在SQL语句前面加上：EXPLAIN就可以查看SQL的执行计划；

例如：

EXPLAIN
SELECT ky_date,id,1v1_vip_total,in_money,out_money
FROM bath_center
WHERE ky_date>='2018-01-01' AND ky_date<='2020-08-01'
ORDER BY id, 1v1_vip_total,black_vip_total

无索引的执行计划：

No	参数	解释
1	id	select查询的序号，包含一组数字，表示查询语句执行的顺序 如果id相同，则从上往下执行；id不同，数值越大越优先执行
2	select_type	表示查询类型：用于区别普通查询、联合查询、子查询等等复杂查询的 1、simple：表示简单查询，不包含子查询或UNION 2、primary：查询中若包含了任何复杂查询的子部分，最外层查询则被标记为primary 3、subQuery：在任何SELECT或WHERE子句中包含了子查询 4、derived：在FROM子句中包含了子查询，子查询被标记成DERIVED 5、UNION：若第二个SELECT出现在UNION之后，将被标记为UNION；
3	table	当前执行的表
4	type	显示查询使用了哪种类型，type中包含了如下几种类型： 1、ALL：性能最差，表示全部扫描 2、index：遍历变量索引树，index也是读全表，与all不同的是，index从索引中读取，all从磁盘中读取 3、range：只检索给定范围的行 4、ref：非唯一索引扫描，返回匹配某个单独值的所有行 5、eq_ref：唯一索引扫描，对于某个键值，在表中只有一条记录与之匹配。常见于主键索引 6、const：表示通过索引一次就找到了 system：表中只有一行记录 7、NULL：表锁不用访问表 性能从好到差：NULL > system > eq_ref > ref > range > index > all
5	possible_keys	显示可能用到这张表上的索引，0个，一个或多个，查询涉及的字段若存在索引，则该索引将被列出，但不一定被使用
6	key	实际使用到的索引
7	key_len	表示索引中使用的字节数
8	ref	表示索引被那一列使用了
9	rows	根据索引既表信息估算出找到需要的记录大概读取的行数，数越小越好
10	extra	包含不适合在其它列中显示但十分重要的额外信息 Using filesort：表示mysql会对世界使用一个外部的索引排序，而不是按照表内的索引进行排序读取。mysql中无法利用索引完成的排序被成为文件排序 Using temporary：表示使用了临时表保存中间结果，Mysql对查询结果排序时使用临时表。常见于order by和group by Using index：表示相应的select操作中使用了覆盖索引 Using where：表示使用了where Using join buffer：表示使用了连接缓存 impossible where： select tables optimized away： distinct：优化distinc操作，在找到第一匹配的元组后既停止找同样值的操作

重要：项目中SQL优化最好到"range"或"ref"级别

---