mysql sql 优化案例_mysql sql优化实例

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