MySQL高性能索引策略实践

有时候创建索引是很有必要的,能否让Mysql使用现有的索引引也是要遵循相应的规则的,本文将在一张62W条数据的单表中,测试索引的使用和调优。

  1. 测试表vote_record
#表vote_record
CREATE TABLE `vote_record` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用户Id',
  `vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票数',
  `group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户',
  `status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-已删除',
  `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`user_id`) USING HASH COMMENT '用户ID哈希索引',
  KEY `index_vote_num` (`vote_num`)
) ENGINE=InnoDB AUTO_INCREMENT=631364 DEFAULT CHARSET=utf8 COMMENT='投票记录表';
  1. 索引触发规则之:独立的列(独立的列是指索引列不能是表达式的一部分,也不能是函数参数)
#表达式的一部分
mysql> SELECT * FROM vote_record where vote_num + 1 = 100;
#[SQL] SELECT * FROM vote_record where vote_num + 1 = 100;
#受影响的行: 0
#时间: 6.319s

mysql> SELECT * FROM vote_record where vote_num = 99;
#[SQL] 
#SELECT * FROM vote_record where vote_num = 99;
#受影响的行: 0
#时间: 1.987s
  1. 用Explain分析是否用到了索引:扫描了全表,没有用到索引
EXPLAIN SELECT * FROM vote_record where vote_num + 1 = 100;

| id | selected_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| --------------| --------------| ------------| -----| --------------| -----| --------| -------| -------| ------------|
| 1 | SIMPLE | vote_record | ALL | NULL | NULL | NULL | NULL | 629320 | Using where |

EXPLAIN SELECT * FROM vote_record where vote_num = 99;

| id | selected_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| --------------| --------------| ------------| -----| ---------------| ---------------| --------| -------| -------| ------------|
| 1 | SIMPLE | vote_record | ref | index_vote_num | index_vote_num | 4 | const | 58 | Using where |

#含有函数表达式的例子
mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10

  1. 索引触发规则之:前缀索引(有时候需要索引很长的字符列,这会让索引变得大且慢,比如哈希索引,为了满足特殊的场景,通常可以采取索引开始的字符串,节约索引空间。但这样会降低选择性,这就需要我们要选择合适的长度作为前缀索引)

    • 所谓选择性高,是指查询可以过滤掉更多无效的行数
    • 计算完整列的选择性(即精度), 为了方便测试我们新增列salt
    mysql> SELECT COUNT(DISTINCT salt)/COUNT(*) AS percent FROM `vote_record`
    

    结果:
    |percent|
    |--|
    |0.4943|

    • 计算不同长度的选择性占比
    mysql> SELECT COUNT(DISTINCT LEFT(salt,4))/COUNT(*) AS percent4, COUNT(DISTINCT LEFT(salt,5))/COUNT(*) AS percent5, COUNT(DISTINCT LEFT(salt,6))/COUNT(*) AS percent6, COUNT(DISTINCT LEFT(salt,7))/COUNT(*) AS percent7, COUNT(DISTINCT LEFT(salt,8))/COUNT(*) AS percent8, COUNT(DISTINCT LEFT(salt,9))/COUNT(*) AS percent9 FROM `vote_record`
    

    结果:
    |percent4|percent5|percent6|percent7|percent8|percent9|
    |--|--|--|--|--|--|
    |0.2504|0.4525|0.4888|0.4936|0.4942|0.4943|

    • 由此计算出取前缀长度为7,选择性比较高
    • 创建前缀索引
    ALTER TABLE `vote_record` ADD KEY (salt(7))
    
    • 另外,前缀索引不能做ORDER BY和GROUP BY使用,也无法覆盖扫描
  2. 索引触发规则之:多列索引(即由多个列联合创建的索引,常见的错误是为多个列单独创建索引,这样大部分情况下并不能提高查询效率)

    • 选择合适的索引列顺序
      • 在一个B-TREE结构的多列索引中,索引列的顺序首先按照最左原则排序,其次是第二列,以此类推等等,所以索引可以按照升序或降序进行全表扫描。
      • 当不考虑排序和分组时,将选择性比较高的列放在前面是很好的。比如:
      mysql> SELECT * FROM `vote_record` WHERE `group_id` = 1 AND `status` = 0;
      
      事先不知道哪个列选择性高,可以先查询下各自的总数:
      mysql> SELECT SUM(`group_id` = 1), SUM(`status` = 0) FROM `vote_record`;
      
      结果:
      |SUM(group_id = 1)|SUM(status = 0)|
      |--|--|
      |210711|50001|
      所以优化写法如下:
      mysql> SELECT * FROM `vote_record` WHERE `status` = 0 AND `group_id` = 1;
      
      • 是创建多列索引还是单个索引呢?对比下:

        ① 单个索引情况下

            EXPLAIN SELECT * FROM `vote_record` WHERE `status` = 0 AND `group_id` = 1;
        

        |...|key|rows|..|
        |--|--|--|--|
        |...|index_status|102182|..|

        ② 多列索引index_s_g(status,group_id):

        mysql> ALTER TABLE `vote_record` ADD KEY `index_s_g`(`status`, `group_id`)
        mysql> EXPLAIN SELECT * FROM `vote_record` WHERE `status` = 0 AND `group_id` = 1;
        

        |...|key|rows|..|
        |--|--|--|--|
        |...|index_s_g|33528|..|

      • 考虑分组和排序情况下,多列索引表现更好

(未完待续2017年2月6日17:21:06)

标签: mysql, 原创

添加新评论