mysql 优化实例之索引创建
优化前：
pt-query-degist分析结果：
# Query 23: 0.00 QPS, 0.00x concurrency, ID 0x78761E301CC7EE47 at byte 394687
# This item is included in the report because it matches --limit.
# Scores: V/M = 3.27
# Time range: 2016-09-29T11:46:22 to 2016-10-01T12:45:02
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 3
# Exec time 0 78s 19s 39s 26s 39s 9s 19s
# Lock time 0 328us 66us 136us 109us 131us 30us 125us
# Rows sent 0 6 1 3 2 2.90 0.78 1.96
# Rows examine 0 6 1 3 2 2.90 0.78 1.96
# Query size 0 348 116 116 116 116 0 116
# 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 'sys_files'\G
# SHOW CREATE TABLE `wechat_prod`.`sys_files`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_type`='wp_goods') AND (`bus_id`='32597')\G
表结构
CREATE TABLE `sys_files` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`url_small` varchar(255) DEFAULT NULL,
`url_big` varchar(255) DEFAULT NULL,
`bus_type` varchar(255) DEFAULT NULL,
`bus_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name` (`bus_type`),
KEY `index_name2` (`bus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207750 DEFAULT CHARSET=utf8
sql执行分析
mysql> explain SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_type`='wp_goods') AND (`bus_id`='32597');
+----+-------------+------------+------------+------+------------------------+-------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+------------------------+-------------+---------+-------+------+----------+------------------------------------+
| 1 | SIMPLE | sys_files | NULL | ref | index_name,index_name2 | index_name2 | 9 | const | 3 | 50.00 | Using index condition; Using where |
+----+-------------+------------+------------+------+------------------------+-------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
根据业务逻辑，索引创建错误：不应该创建两个单独的索引bus_type和bus_id，此处虽然创建了两个索引，但真正用到的索引只是index_name2，索引index_name没有任何作用。会占用空间，并影响写入和更新的性能。
alter table sys_files drop index index_name;
修改后索引使用情况：
mysql> explain SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_type`='wp_goods') AND (`bus_id`='32597');
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+
| 1 | SIMPLE | sys_files | NULL | ref | index_name2 | index_name2 | 9 | const | 3 | 10.00 | Using index condition; Using where |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
sql写法
SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_type`='wp_goods') AND (`bus_id`='32597')
改为
SELECT `id`, `url_small`, `url_big`, `bus_id` FROM `sys_files` WHERE (`bus_id`='32597') AND (`bus_type`='wp_goods')
sql编写的原则：把辨识度高的重复率低的写在左边。