关于mysql:Mysql-索引设计与优化

什么是索引？

数据库索引是一种数据结构，它以额定的写入和存储空间为代价来进步数据库表上数据检索操作的速度。艰深来说，索引相似于书的目录，依据其中记录的页码能够疾速找到所需的内容。——维基百科

常见索引有哪些？

• 一般索引：最根本的索引，没有任何限度
• 惟一索引：与”一般索引“相似，不同的就是：索引列的值必须是惟一，但容许有空值
• 主键索引：它是一种非凡的索引，不容许有空值
• 全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时占空间
• 组合索引：为了进步多条件查问效率，可建设组合索引，遵循”最左前缀匹配准则“

这里以绝对简单的组合为例，介绍如何优化。

最左前缀匹配准则

首先咱们要晓得什么是最左前缀匹配准则。

最左前缀匹配准则是指在应用 B+Tree 联结索引进行数据检索时，MySQL 优化器会读取谓词（过滤条件） 并依照联结索引字段创立程序始终向右匹配直到遇到范畴查问或非等值查问后进行匹配 ，此字段之后的索引列不会被应用，这时计算 key_len 能够剖析出联结索引理论应用了哪些索引列。

如何计算 key_len

通过 key_len 计算也帮忙咱们理解索引的最左前缀匹配准则。

key_len 示意失去后果集所应用的抉择索引的长度 [字节数]，不包含 order by，也就是说如果 order by 也应用了索引则 key_len 不计算在内。

在计算 key_len 之前，先来复习一下根本数据类型（以 UTF8 编码为例）：

测试数据表如下：

CREATE TABLE `test_table` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NOT NULL,
  `b` int(11) DEFAULT NOT NULL,
  `c` int(11) DEFAULT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `test_table_a_b_c_index` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

命中索引：

mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys          | key                    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | test_table | NULL       | ref  | test_table_a_b_c_index | test_table_a_b_c_index | 12      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

能够看到 key_len = 12，这是如何计算的呢？
因为字符集是 UTF8，一个字段占用四个字节，三个字段就是 4 * 3 = 12 字节。

是否容许为 NULL，如果容许为 NULL，则须要用额定的字节来标记该字段，不同的数据类型所需的字节大小不同。

mysql> ALTER TABLE `test_table` CHANGE `a` `a` INT(11)  NULL;
mysql> ALTER TABLE `test_table` CHANGE `c` `c` INT(11)  NULL;
mysql> ALTER TABLE `test_table` CHANGE `b` `b` INT(11)  NULL;
mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys          | key                    | key_len | ref               | rows | filtered | Extra       |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | test_table | NULL       | ref  | test_table_a_b_c_index | test_table_a_b_c_index | 15      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

能够看到，当字段容许为空时，这时的 key_len 变成了 15 = 4 3 + 1 3（INT 类型为空时，额定占用一个字节）。

索引优化

有了这些基础知识之后，再来依据理论的 SQL 判断索性性能好坏。

还是以下面那张数据表为例，为 a、b、c 三个字段创立联结索引。

通常在查看执行打算时，Extra 列为 Using index 则示意优化器应用了笼罩索引。

• SQL1 能够应用笼罩索引，性能好
• SQL2 能够应用笼罩索引，同时防止排序，性能好
• SQL3 能够应用笼罩索引，然而须要依据 where 字句进行过滤
• SQL4 能够应用局部索引 a，但无奈防止排序，性能差
• SQL5 能够齐全应用笼罩索引，同时能够防止排序，性能好
• SQL6 能够应用笼罩索引，但无奈防止排序，（这是因为 MySQL InnoDB 创立索引时默认 asc 升序，索引无奈主动倒序排序）
• SQL7 能够应用笼罩索引，然而须要依据 where 子句进行过滤（非定值查问）

创立索引标准

• 思考到索引保护的老本，单张表的索引数量不超过 5 个，单个索引中的字段数不超过 5 个
• 不在低基数列上建⽴索引，例如“性别”。在低基数列上创立的索引查问相比全表扫描不肯定有性能劣势，特地是当存在回表老本时。
• 正当创立联结索引，(a,b,c) 相当于 (a)、(a,b)、(a,b,c)。
• 正当应用笼罩索引缩小 IO，防止排序。

参考链接

• Explain 之 key_len 长度计算