优化sql的一般步骤
1.发现问题 -> 分析执行计划 -> 优化索引 -> 改写sql
- 如以上方法还无法达到满意的效果，就要进行数据库设计的优化，如数据库的垂直切分，水平切分。
这里只讲第1种优化
- 常见问题发现渠道
- 用户主动上报应用性能问题
- 分析慢查询日志发现存在问题的sql
- 数据库实时监控长时间运行的sql
第1种偏被动，日常主要还是靠2，3发现问题
配置mysql慢查询日志
set global slow_query_log = [ ON | OFF ]
慢查询日志开关，mysql默认设置为off
set global slow_query_log_file = /sql_log/slowlog.log
慢查询日志存储位置
set global long_query_time = xx.xxxxxx（单位秒）
时间阈值，执行时间超过设置阈值的都会被记录在慢查询日志
默认为10，设0记录全部sql，对于业务繁忙的需求一般设置为0.1，即100毫秒
set global log_queries_not_using_indexes = [ ON | OFF]
未使用到索引的sql会被记录到慢查询日志
'分析慢查询日志的工具'
官方提供的工具
mysqldumpslow [ opts... ] [logs...]
非官方工具，能提供更多信息，排版更好，更好用
本文用这工具
pt-query-digest [ options ] [ files ] [ dsn ]
pt-query-digest工具安装具体看《2)mysql慢日志分析工具pt-query-digest安装》
//www.greatytc.com/p/15c650a1cd9d
开启慢日志查询
在mysql里面可用
show variables like 'slow_query_log'; -- 查看慢日志查询日志是否开启
SHOW VARIABLES LIKE 'long_query_time';
查看阈值设置时间
show variables like 'slow_query_log_file'; -- 查看慢日志存储路径
运行pt-query-digest 分析慢查询日志
pt-query-digest slowlog.log
exex time：执行时间
lock time：锁时间
rows sent：查询访问的行数
rows_examined：扫描的行数
count：查询执行的次数
query size：查询的字节数
最下面是执行的sql语句
'通常来说，要关注那些执行次数非常多，还有扫描远远大于输出的sql'
'方法2通过对time过滤，找出长时间运行的sql'
SELECT id, 'user','host',DB,command,'time',state,info
FROM information_schema.`PROCESSLIST`
WHERE TIME>=30 -- 单位秒
找到有问题的sql后，要获取sql的执行计划，根据执行计划优化
explainable_stmt：静态语句
FOR CONNECTION connection_id：正在执行的sql
id：表示查询执行的顺序，id相同时由上到下执行，id不同时由大到小执行，若为null，则表示为两个其他查询通过 union 得来的结果
select_type：有4种值
simple: 不包含子查询或union操作的查询
primary: 查询中如果包含任何子查询，那么最外层的查询则被标记为primary
subquery: select列表中的子查询
dependent subquery: 依赖外部结果的子查询
简单理解：若sql只有1层子查询，为primary，若有多层子查询，最外层为primary，最底层为dependent subquery，中间层为subquerytable：输出数据所在的表，3情况
1.如果表格起了别名，就显示别名
2.<union M,N>由id为M，N查询union产生的结果集
3.<derived N> / <subquery N>由ID为N的查询产生的结果partitions：只有在查询分区表时才有意义
对于分区表，显示查询的分区ID
对于非分区表，显示NULL-
查询所使用的类型
type类型 possob;e_keys：可能使用到的索引
keys：实际使用到的索引
key_len：实际使用索引的最大长度。比如1个联合索引总长度为100，但key_len可能不到100，那就说明查询可能没有使用联合索引的所有列，这个值是由表中定义的长度来计算的，并不是实际存储的长度，因此在设计数据库时要用满足数据的最小长度，有利于提高性能
ref：哪些列或常量被用于通过索引来进行数据过
rows：根据统计信息预估扫描的行数（预估值）
filtered：表示返回结果的行数占需读取行数的百分比（预估值）
-
Extra：不适合在其他页所显示的一些信息，常见值如下图
要注意出现using temporary的情况，因为使用到了外部临时表，通常来说性能不太好
示例：
EXPLAIN
SELECT course_id,class_name,level_name,title,study_cnt
FROM imc_course a
JOIN imc_class b ON b.class_id=a.class_id
JOIN imc_level c ON c.level_id=a.level_id
WHERE study_cnt > 3000
EXPLAIN
SELECT a.course_id,a.title
FROM imc_course a
WHERE a.course_id NOT IN (
SELECT a.course_id
FROM imc_chapter b
)
-- 查询出2019年1月1号之后注册的男性会员昵称
EXPLAIN
SELECT user_nick
FROM imc_user
WHERE sex=1 AND reg_time>'2019-01-01';
输出
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- -------- ---------- ------ ------------- ------ ------- ------ ------ -------- -------------
1 SIMPLE imc_user (NULL) ALL idx_sex (NULL) (NULL) (NULL) 2530 3.33 Using where
-- 筛选性
SELECT COUNT(DISTINCT sex)
,COUNT(DISTINCT DATE_FORMAT(reg_time,'%Y-%m-%d'))
,COUNT(*)
,COUNT(DISTINCT sex)/COUNT(*)
,COUNT(DISTINCT DATE_FORMAT(reg_time,'%Y-%m-%d'))/COUNT(*)
FROM imc_user
CREATE INDEX idx_regtime ON imc_user(reg_time)
EXPLAIN
SELECT user_nick
FROM imc_user
WHERE sex=1 AND reg_time>'2019-01-01';
'再次获取sql的执行计划，输出'
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- -------- ---------- ------ ------------------- ----------- ------- ------ ------ -------- ------------------------------------
1 SIMPLE imc_user (NULL) range idx_sex,idx_regtime idx_regtime 5 (NULL) 516 10.00 Using index condition; Using where
'添加索引之后，再次执行执行计划，要比之前没索引的情况好 '
DROP INDEX idx_regtime ON imc_user
-- 给sex添加索引后，并不会使用到索引，同样要扫描2530行数据
-- 说明在筛选性不好的列上建立索引，没有任何作用
CREATE INDEX idx_sex ON imc_user(sex)
DROP INDEX idx_sex ON imc_user
EXPLAIN
SELECT course_id,b.class_name,d.type_name,c.level_name,title,score
FROM imc_course a
JOIN imc_class b ON b.`class_id`=a.`class_id`
JOIN imc_level c ON c.`level_id`=a.`level_id`
JOIN imc_type d ON d.`type_id`=a.`type_id`
WHERE c.`level_name`='高级'
AND b.`class_name`='MySQL'
-- 联合索引，按照可筛选性排序
CREATE INDEX idx_classid_typeid_levelid ON imc_course(class_id,type_id,level_id);
DROP INDEX idx_classid_typeid_levelid ON imc_course;
CREATE INDEX idx_levelname ON imc_level(level_name)
-- 查询出不存在课程的分类名称
EXPLAIN
SELECT class_name
FROM imc_class
WHERE class_id NOT IN (SELECT class_id FROM imc_course )
