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: ALL
possible_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_ref
possible_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: ALL
possible_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几秒，几乎可以忽略不计。