Mysql索引优化（一）_索引类型(索引策略)

JAVA记事

2019-11-05

上一篇已经讲了索引的基本类型，这一篇主要介绍下如何选择更高效的索引类型。

独立的列

现在有下面一张学生成绩表

CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  `score` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
  KEY `score` (`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

现在我们要根据学生成绩查询学生姓名，这是一个很简单的查询。select first_name,last_name from student where score=99;这条sql就使用到了索引score。
但是我们通常会看到很多查询不恰当的使用到索引，最后就导致mysql没办法使用到索引。如果查询中的不是独立的，则Mysql不会使用到索引，独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
如select first_name,last_name from student where score+1=100;这个查询是不能使用到索引的。
再如：select first_name from student where TO_DAYS(NOW())-TO_DAYS(created_at)>0;也是不能使用到索引的。

前缀索引

有时候需要索引的列是很长的字符串，如果直接创建索引会使索引变的很大这样就变的比较慢了。改进方式现在能想到就是前面说到的哈希索引，
但是有时候哈希索引是不适合的。这个时候就有了前缀索引：把列开始的部分字符串作为索引，这样可以大大的节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引选择性指：不重复的索引值和数据表总数的比值。索引的选择性越高，那么索引的查询效率越高。唯一索引的选择性最高为1，性能也是最好的。
对于很长的VARCHAR，TEXT这样的列，如果要作为索引的话，那么必须使用前缀索引。
那么怎么选择合适的前缀索引呢。
诀窍在于要选择足够长的前缀以保证比较高的索引选择性，同时又不能太长，因为索引越短，索引空间越小。
如：一张订单表，要为联系人手机号做前缀索引，这个适合需要分析多少长度的前缀索引,可以查询每个长度的

SELECT COUNT(DISTINCT LEFT(phone,3))/COUNT(*) AS pre3,
COUNT(DISTINCT LEFT(phone,4))/COUNT(*) AS pre4,
COUNT(DISTINCT LEFT(phone,5))/COUNT(*) AS pre5,
COUNT(DISTINCT LEFT(phone,6))/COUNT(*) AS pre6,
COUNT(DISTINCT LEFT(phone,7))/COUNT(*) AS pre7,
COUNT(DISTINCT LEFT(phone,8))/COUNT(*) AS pre8  
FROM orders;

+--------+--------+--------+--------+--------+--------+
| pre3   | pre4   | pre5   | pre6   | pre7   | pre8   |
+--------+--------+--------+--------+--------+--------+
| 0.0026 | 0.0216 | 0.1397 | 0.3274 | 0.4533 | 0.4533 |
+--------+--------+--------+--------+--------+--------+
1 row in set (0.10 sec)

可以看到在长度为7的时候，选择性的提升已经很小了。这个时候，我们就可以考虑取7这个值了。当然这里只是一个举例，在实际场景中，手机号的长度完全可以直接作为普通索引的。
前缀索引是比较小而且快，但是mysql不能用前缀索引作为group by 和order by，也不能做覆盖扫描（所以查询必须回表）。

多列索引

对于初学者，常见的错误就是为每个查询列都加一个索引，或者按照错误的顺序创建的多列索引。网上有多说“把where 条件中的列都加上索引就好了”，这种说法是错误的。很多时候这样的索引并不会提高效率。如一个订单表，状态有8种，如果为订单状态加上索引。那么在数据量少的情况下可能会提高效率，但是当数据量大的时候反而会影响效率。
如有下表：

CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  `created_at` timestamp NOT NULL,
  `score` int(3) NOT NULL DEFAULT '0',
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `score` (`score`),
  KEY `first_name` (`first_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8

现在表中有600万数据。现在我们统计分数的出现次数
SELECT COUNT(*) AS num,score FROM student GROUP BY score ORDER BY num DESC LIMIT 10;
得到结果

+-------+-------+
| num   | score |
+-------+-------+
| 68607 |    13 |
| 68557 |    44 |
| 68551 |    67 |
| 68527 |    64 |
| 68490 |    35 |
| 68490 |     5 |
| 68457 |    17 |
| 68422 |    50 |
| 68415 |    95 |
| 68409 |    11 |
+-------+-------+
10 rows in set (2.35 sec)

发现分数为13的有6万8千个。这个时候我们查询score为13,first_name开始为O的，
SELECT score,first_name FROM student WHERE first_name LIKE '0%' AND score=13;
可以得到结果1173 rows in set (0.76 sec);
分析得到

mysql> explain SELECT score,first_name FROM student WHERE first_name LIKE '0%' AND score=13 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ref
possible_keys: score,first_name
          key: score
      key_len: 4
          ref: const
         rows: 135128
     filtered: 3.61
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

这个时候可以看到其实是只用到索引score的，扫描了13万行数据，这个时候我们是单独为两个字段加的索引。

但是如果我们创建一个(score,first_name)的多列索引呢。
得到结果1173 rows in set (0.00 sec);可以看到多列索引的速度明显比单独的索引要很多.
分析

mysql> explain SELECT * FROM student WHERE first_name LIKE '0%' AND score=13 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: range
possible_keys: score_first_name
          key: score_first_name
      key_len: 66
          ref: NULL
         rows: 1173
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

可以看到这个时候用到多列索引，扫描行数，明显的减少了，时间也快了很多。所以说选择合适的多列索引

选择合适的索引顺序

在创建多列索引的时候我们经常需要考虑的就是如何去选择合适的索引顺序，而不是说哪个查询条件在前面就选择哪个顺序。而是应该根据实际情况来分析考虑，在可能是顺序下还应该满足排序分组等需求。比如说查询
SELECT score,first_name FROM student WHERE first_name LIKE '0Z%' AND score=13;
这个查询是应该创建一个(score,first_name)的索引，还是应该将索引顺序颠倒一下呢。我们可以查询一下两个列的分布情况，最后根据分析结果来确认索引的顺序。

mysql> SELECT SUM(score='13'),SUM(first_name LIKE '0Z%') FROM student;
+-----------------+----------------------------+
| SUM(score='13') | SUM(first_name LIKE '0Z%') |
+-----------------+----------------------------+
|           68607 |                       3499 |
+-----------------+----------------------------+
1 row in set (2.59 sec)

根据前面在索引选择性的描述，我们应该将first_name放到前面。那我们在来看看这个情况下 score的索引选择性。

mysql> SELECT SUM(score='13') FROM student WHERE first_name LIKE '0Z%';
+-----------------+
| SUM(score='13') |
+-----------------+
|              39 |
+-----------------+

可以看到这把first_name 放到前面是比较符合索引选择性的规则的。但是也不是所有的场景都是符合这种情况的。所以还是需要具体分析。综合出比较有利的设计方案。不然反而可能造成一些不必要的麻烦。