mysql sql优化实例
优化前：
pt-query-degist分析结果：
# Query 3: 0.00 QPS, 0.00x concurrency, ID 0xDC6E62FA021C85B5 at byte 628331# This item is included in the report because it matches --limit.# Scores: V/M = 0.19# Time range: 2016-09-24T15:14:24 to 2016-10-08T07:46:24# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 12 50# Exec time 6 623s 10s 16s 12s 15s 2s 11s# Lock time 0 28ms 176us 12ms 553us 568us 2ms 287us# Rows sent 0 162 3 5 3.24 4.96 0.67 2.90# Rows examine 11 776.54k 13.80k 16.19k 15.53k 15.96k 761.60 15.96k# Query size 7 12.74k 261 261 261 261 0 261# String:# Databases wechat_prod# Hosts localhost# Users test# Query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s# 10s+ ################################################################# Tables# SHOW TABLE STATUS FROM `wechat_prod` LIKE 'product'\G# SHOW CREATE TABLE `wechat_prod`.`product`\G# SHOW TABLE STATUS FROM `wechat_prod` LIKE 'sys_members'\G# SHOW CREATE TABLE `wechat_prod`.`sys_members`\G# SHOW TABLE STATUS FROM `wechat_prod` LIKE 'product_sku'\G# SHOW CREATE TABLE `wechat_prod`.`product_sku`\G# EXPLAIN /*!50100 PARTITIONS*/SELECT `p`.`id`, `p`.`title`, `p`.`fare`, `p`.`sales`, `p`.`user_openid`, `u`.`nickname`, `s`.`price` FROM `product` `p` LEFT JOIN `sys_members` `u` ON p.user_openid = u.openid LEFT JOIN `product_sku` `s` ON s.product_id = p.id ORDER BY `wd_sort` LIMIT 3\G
sql 分析
mysql> EXPLAIN /*!50100 PARTITIONS*/ -> SELECT `p`.`id`, `p`.`title`, `p`.`fare`, `p`.`sales`, `p`.`user_openid`, `u`.`nickname`, `s`.`price` FROM `product` `p` LEFT JOIN `sys_members` `u` ON p.user_openid = u.openid -> LEFT JOIN `product_sku` `s` ON s.product_id = p.id ORDER BY `wd_sort` LIMIT 3\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2413 filtered: 100.00 Extra: Using temporary; Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: u partitions: NULL type: eq_refpossible_keys: openid key: openid key_len: 152 ref: wechat_prod.p.user_openid rows: 1 filtered: 100.00 Extra: Using where*************************** 3. row *************************** id: 1 select_type: SIMPLE table: s partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 518 filtered: 100.00 Extra: Using where; Using join buffer (Block Nested Loop)3 rows in set, 2 warnings (0.00 sec)
product和product_sku表都没有使用索引。
其中product表的分析结果为Extra: Using temporary; Using filesort，此结果表示使用了临时文件排序，product_sku表的分析结果为Extra: Using where; Using join buffer (Block Nested Loop)，而此结果表示使用了循环查找，扫描了518行。
product表表结构：
CREATE TABLE `product` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `title` varchar(64) DEFAULT NULL , `description` varchar(1200) DEFAULT '' , `cat_id` smallint(6) DEFAULT '1' , `on_sell` tinyint(4) DEFAULT NULL, `sort` int(8) DEFAULT NULL , `nice` tinyint(4) DEFAULT NULL , `user_openid` varchar(32) DEFAULT NULL , `is_return` tinyint(2) DEFAULT NULL , `fare` tinyint(4) DEFAULT NULL , `content` text COMMENT , `add_time` int(11) DEFAULT NULL , `sales` int(11) DEFAULT '0' , `if_audit` tinyint(1) DEFAULT '1, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3321 DEFAULT CHARSET=utf8
product_sku表表结构：
CREATE TABLE `product_sku` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `product_id` bigint(20) DEFAULT NULL, `name` varchar(64) DEFAULT NULL , `count` int(8) DEFAULT NULL , `price` decimal(10,2) DEFAULT NULL , PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3367 DEFAULT CHARSET=utf8
添加索引
alter table product add index user_openid(user_openid);alter table product_sku add index product_id(product_id);
分析添加索引后的查询情况
mysql> explain SELECT `p`.`id`, `p`.`title`, `p`.`fare`, `p`.`sales`, `p`.`user_openid`, `u`.`nickname`, `s`.`price` FROM `product` `p` LEFT JOIN `sys_members` `u` ON p.user_openid = u.openid LEFT JOIN `product_sku` `s` ON s.product_id = p.id LIMIT 3;+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+-------------+| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 2413 | 100.00 | NULL || 1 | SIMPLE | u | NULL | eq_ref | openid | openid | 152 | wechat_prod.p.user_openid | 1 | 100.00 | Using where || 1 | SIMPLE | s | NULL | ref | product_id | product_id | 9 | wechat_prod.p.id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------------+---------+--------------------------+------+----------+-------------+3 rows in set, 1 warning (0.00 sec)
使用索引后，product_sku表只扫描了1行。
由平均的12s降为0.0几秒，几乎可以忽略不计。