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编写的原则：把辨识度高的重复率低的写在左边。