12)sql优化

2019-08-21 本文已影响0人哥斯拉啊啊啊哦

优化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安装》
https://www.jianshu.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，中间层为subquery
table：输出数据所在的表，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 )

12)sql优化

配置mysql慢查询日志

猜你喜欢

热点阅读