mysql慢查询优化

一、慢查询记录

1.开启记录未使用索引语句的日志功能：

SHOW VARIABLES LIKE 'log_queries%'
SET GLOBAL log_queries_not_using_indexes=ON

2.配置超时查询时间：（这里设置为0.05s，生产要根据实际情况设置）

SHOW VARIABLES LIKE 'long_query_time'
SET GLOBAL long_query_time = 0.05

3.开启慢查询日志记录：

SET GLOBAL slow_query_log=ON;

4.查看慢查询的信息，可以查看到慢查询日志里面

SHOW VARIABLES LIKE 'slow%'

执行一条语句

select * from order_info  inner join order_detail  on order_info.`order_id`=order_detail.`order_id` 

耗时0.07s被记录到慢查询日志中

select * from order_info  inner join order_detail  on order_info.`order_id`=order_detail.`order_id` 
# Time: 2019-07-07T01:44:43.301646Z
# User@Host: root[root] @ localhost [::1]  Id:  2049
# Query_time: 0.002417  Lock_time: 0.000511 Rows_sent: 16  Rows_examined: 99
SET timestamp=1562463883;
select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 111 group by state order by `duration (summed) in sec` desc;

从日志中我们可以挑一些语句（比如查询次数过多且时间占用较长，io大的sql，没有用到索引的sql）出来进行分析

用explain语句进行分析

订单表

CREATE TABLE `order_info` (
  `order_id` varchar(64) NOT NULL,
  `buyer_name` varchar(64) NOT NULL,
  `create_date` varchar(64) NOT NULL,
  `total_amount` float NOT NULL,
  `status` int(2) DEFAULT '0' COMMENT '1完结，0处理中，-1撤销,-2已失效',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

详情表

CREATE TABLE `order_detail` (
  `order_detail_id` varchar(64) NOT NULL,
  `order_id` varchar(64) NOT NULL,
  `product_count` int(11) NOT NULL,
  `product_name` varchar(64) NOT NULL,
  `product_price` float NOT NULL,
  `product_id` varchar(64) NOT NULL,
  `product_img` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_detail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

比如分析订单详情和订单表的语句、

select_type:

simple	简单的sql语句
primary	查询中最外层的select
union	union操作中，查询中处于内层的select（内层的select语句与外层的select语句没有依赖关系）
dependent union	union操作中，查询中处于内层的select（内层的select语句与外层的select语句有依赖关系）
union result	union操作的结果，id值通常为null
subquery	子查询中首个select（如果有多个子查询存在）
dependent subquery	子查询中首个select，但依赖于外层的表（如果有多个子查询存在）,会比较消耗性能
deriver	被驱动的select子查询（子查询位于from子句）
materialized	被物化的子查询，即内容放到一个临时表中
uncachable subquery	对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
uncacheable union	UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

table:该行数据对应的表

type:显示使用了何种类型的链接，从好到坏依次是（如果出现了all、index也可能需要优化）

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

possible_keys:指出了mysq可l能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

key：显示mysql实际使用的键

key_len:实际使用的键长度，如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好。

ref:显示索引的哪一列被使用了，如果可能是的话最好是一个常数

rows：查询所需检查的行数

extra:

Using filesort（需要用到文件排序）和Using temporary（临时表）这两个要注意优化

更多详细extra介绍

二、聚合函数

对于经常统计的字段如max、count、min等等，可以为他建立索引

如查询订单总金额最大的订单信息（这里的订单主表有1w条信息）

EXPLAIN SELECT * FROM order_info WHERE total_amount = (SELECT MAX(total_amount) FROM order_info)

用时0.052s

然后为total_amount字段建立索引

CREATE INDEX idx_total_amount ON order_info(total_amount)

select table optimized away表示通过索引来查找，此时执行时间为0.005s

再看多一个例子，根据日期分组，找出每天的订单总金额

EXPLAIN SELECT create_date,SUM(total_amount) FROM order_info  GROUP BY create_date

发现出现了文件排序和临时表的情况，下面为create_date、total_amount建立索引

CREATE INDEX idx_d_t ON order_info(total_amount,create_date)

发现只是增多一个索引，此时执行时间为0.039(这个是把原来的total_amount索引去掉的结果，有覆盖索引会降低效率)

再创建一个索引，把group by字段放在最左，

CREATE INDEX idx_t_d ON order_info(create_date,total_amount)

此时强制使用新建的索引

EXPLAIN SELECT create_date,SUM(total_amount) FROM order_info FORCE INDEX(idx_t_d)  GROUP BY create_date

执行时间为0.024s

而create_date和total_amount都作为查询条件时候，则适用于把离散度较高的字段放在联合索引的第一位，如

SELECT create_date,total_amount FROM order_info WHERE total_amount > 100 AND create_date > '2019-06-09'

此时会走create_date,total_amount这个索引。根据不同的语句建立不同的索引

三、子查询优化

一般的做法是把in查询改为join链接查询

比如查找卖出的商品名字和id,是否打折、描述等等信息，

SELECT discount,description,id,NAME FROM product WHERE id IN (SELECT DISTINCT(product_id) FROM order_detail )

用时0.232s

此时把他改为join查询

EXPLAIN SELECT discount,description,id,NAME FROM product LEFT JOIN order_detail ON order_detail.`product_id`=product.id 

由于是一对多的关系，一个商品对应多个订单详情，所以会查出来有很多条记录，虽然花费时间为0.013s，但不符合预期，

于是想到在前面加distinct关键词

EXPLAIN SELECT DISTINCT product.discount,product.description,product.id,product.name FROM product LEFT JOIN order_detail ON order_detail.`product_id`=product.`id` 

发现执行时间更慢了，0.699s

那groupby呢？执行时间为0.493s

EXPLAIN SELECT  product.discount,product.description,product.id,product.name FROM product LEFT JOIN order_detail ON order_detail.`product_id`=product.`id` GROUP BY product.id

发现是想错了，应该链接一个先查出来的结果

 SELECT  product.discount,product.description,product.id,product.name FROM product LEFT JOIN (SELECT DISTINCT(product_id) AS i FROM order_detail)c ON c.i=product.`id`

执行用时0.112s，可以看到还有个临时表，并且遍历行数比较多，这时候为order_detail里的product_id建一个索引

 CREATE INDEX idx_product_name ON order_detail(product_id)

最后用时0.004s

除此之外，还有exists语句可以使用，在商品数量不多的情况下，即外表数据远小于内表数据时候，也可以尝试用exists,他在没有建立索引的情况下用时达到0.005s

 SELECT product.discount,product.description,product.id,product.name FROM product WHERE EXISTS (SELECT * FROM order_detail WHERE product_id = id)

一般的来讲，把子查询改为join链接是比较常用的，但是当in里面的数目比较小的话，效率可能比join更高，还有exists语句，具体要结合实际使用，灵活变通。

另外一个例子：查出每个用户的email、账号及其订单总数

SELECT user.`account`,user.`email`,COUNT(*) FROM USER LEFT JOIN order_info ON order_info.`buyer_name`=user.`account` GROUP BY user.`account`

用时0.026s

这种groupby的查询可以先把groupby查询的条件过滤出来，再进行连接

 EXPLAIN SELECT user.`account`,user.`email`,c.total FROM USER LEFT JOIN (SELECT COUNT(*) AS total,order_info.`buyer_name` FROM order_info GROUP BY order_info.`buyer_name`)c ON c.buyer_name=user.`account`

即可以进行语句的改写或建立合适索引

四、limit优化

limit语句是比较常用的，用于分页技术，但使用时也有注意的点，因为limit一般是和order by一起使用的，所以可以从order by里面入手

比如对于没有建索引的日期查询，可以看出他是全表扫描的

 EXPLAIN SELECT * FROM order_info ORDER BY create_date LIMIT 10,5

添加一个日期索引

 CREATE INDEX idx_create_date ON order_info(create_date)

此时看出只扫描了15行，但是如果有很多条数据，比如分页到一百万，此时需要建立一个有序的索引（也可以是主键），并且限定其范围。这样就可以限定只扫描那几行

EXPLAIN SELECT order_info.`create_date`,order_info.`buyer_name` FROM order_info  WHERE sort_index > 10000 AND sort_index<=10005 ORDER BY sort_index  LIMIT 1,5

另外也有一种更常见的优化方案，可以先把主键查出来，再对这个临时表进行join查询，减少回表的次数

SELECT * FROM table where xxxx LIMIT 358420, 17921;


select * from table temp
inner join (SELECT ID FROM table where xxx  LIMIT 358420, 17921 ) f
where f.id=temp.id

五、字段优化

比如说日期，订单表的日期类型为字符类型，在统计每天的订单总金额时，要对日期进行转换才能方便计算

EXPLAIN SELECT COUNT(*),DATE(create_date) d,SUM(total_amount) FROM order_info GROUP BY create_date HAVING d >=  DATE_SUB(CURRENT_DATE(),INTERVAL 60 DAY) ORDER BY d ASC

执行时间为0.087

此时把create_date换为datetime类型，就可以把转换函数去掉

EXPLAIN SELECT COUNT(*),create_date d,SUM(total_amount) FROM order_info  GROUP BY create_date HAVING d >=  DATE_SUB(CURRENT_DATE(),INTERVAL 60 DAY) ORDER BY d ASC

其他的字段类型设置比如涉及金融计算等精准度要求高的的，用decimal

字段的长度尽量少

主键尽量用数字类型

字段尽可能设置为not null

尽量避免text，非用不可可以考虑分表，并且分表后也如果有业务需要可以考虑建立全文索引fulltext。

ip地址可以使用bigint存储，在插入时要进行INET_ATON转为bigint类型，查询时使用INET_NTOA进行转换为字符类型

SELECT INET_ATON('192.168.0.2')
  SELECT INET_NTOA('3232235522')

时间戳也可以用int存储，在插入时用UNIX_TIMESTAMP进行转换，查询时用FROM_UNIXTIME进行转换

 SELECT FROM_UNIXTIME('1562557980')
  SELECT UNIX_TIMESTAMP('2019-07-08 11:53:00')

六、索引优化

一列上最好不要有重复的索引，查看重复索引的语句如下，在数据库information_scheme中执行：

SELECT a.table_schema AS '数据库',a.table_name AS '表'
,a.index_name AS '索引1',b.index_name AS '索引2' FROM
statistics a JOIN statistics b ON a.table_schema=b.table_schema AND 
a.table_name=b.table_name AND a.seq_in_index=b.seq_in_index AND 
a.column_name=b.column_name WHERE a.seq_in_index=1 AND a.index_name <> b.index_name

去除重复多余的索引，外键设置尽量避免，关联关系在应用程序上建立即可，保证关联的键上面有索引即可，一个表最好不要超过5个索引，必要时可用force index来指定走哪个索引进行查询,经常变动的字段不要设置索引，因为每次变化都要重新调整b+树结构

七、其他

遵循范式：保持数据完整性

反范式：用空间换时间

垂直拆分表（宽度）：

把不常用的字段单独存放在一个表中。

把大字段（text、blob）独立放到一个表中。

把经常一起使用的字段放到一起。

水平拆分表（数据量）：

根据主键hash、范围、时间粒度分表等等

最左匹配原则

mysql会一直向右匹配直到遇到范围查询（>、<、between、like)就停止匹配，如a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)顺序的索引（会创建子搜索结构），d是用不了索引的（用的是a,b,c），如果建立(a,b,d,c）的索引则可以用到，a,b,d的顺序可以任意调整。=和in可以乱序，mysql的查询优化器会把索引优化成可识别的形式