mysql进阶-索引及其优化

一 关于索引

1. 索引概述

• 索引是存储引擎用于快速找到记录的一种数据结构
• 索引的优点：
  减少扫描的数据量，加速查询
  减少或完全消除数据库的排序操作（ORDER BY），因为索引是有序的
  将服务器的随机 IO 变为顺序 IO，例如，想要查询 salary 处于 1500 ~ 2100 的员工，就可以按照索引顺序查询
• 索引的缺点：
  索引会占据额外的存储空间（毕竟它是数据结构），包括磁盘和内存
  由于对数据需要排序，自然会影响到数据更新（插入、更新、删除）的速度

2. 索引分类

• 普通索引：针对于单个列创建的索引，之所以说它普通是因为它对列值没有什么限制，允许被索引的列包含重复的值
• 唯一索引：正如它的关键字一样，它要求列值是唯一的，这个索引保证了数据记录的唯一性
• 主键索引：它是一种特殊的唯一索引，在一张表中只能定义一个（但不是必须）主键索引
• 联合索引：也被称为复合索引，它是将多个列值绑定在一起作为索引

3. 创建高性能的索引方式

聚簇索引

聚簇索引是 InnoDB 中一个最为特殊的概念，它实际上并不是一种索引类型，而是一种存储数据的方式，且是将索引和数据存储在一起。InnoDB 规定一个表只能有一个聚簇索引，且会使用主键来创建。
对于聚簇索引来说，索引即数据，所以，如果以主键去查询数据，那么只需要一次索引查找即可。
对于非聚簇索引而言，实际存储的是记录主键，所以，还需要根据主键再做一次查询才可以获取到数据，这也就是我们通常所说的 “非主键的二次查询

联合索引

联合索引，它的应用场景是多列 WHERE 查询。例如，对于 worker 表，我们需要做这样的查询：

联合索引了，它的应用场景是多列 WHERE 查询。例如，对于 worker 表，我们需要做这样的查询：

SELECT * FROM worker WHERE type = 'B' AND salary > 2000;
此时，我们不要去单独的创建 type 和 salary 索引，而应该创建 (type, salary) 的联合索引（可以思考下，联合索引的 B+ 树存储是怎样的呢 ？），语句如下：

mysql> ALTER TABLE worker ADD INDEX type_salary_idx(`type`, `salary`);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
worker 表添加了联合索引之后，再去查询时就会使用到 type_salary_idx（索引的名称，可以随意）。同时，联合索引会遵循最左前缀匹配原则，也就是常说的 “最左优先原则”。它的表现形式是在检索数据时从联合索引的最左边开始匹配。举个例子：我现在给 worker 添加如下索引：

mysql> ALTER TABLE worker ADD INDEX type_salary_name_idx(`type`, `salary`, `name`);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
可以认为，MySQL 一共创建了三个索引：(type)，(type, salary)，(type, salary, name)。那么，你应该也明白了之前创建的 type_salary_idx 索引是多余的。联合索引是一个比较有意思的话题，你需要充分理解它的 “最左原则”，这会让你避免创建冗余的索引。另外，你还需要知道一些索引失效的条件：

在索引列上执行计算、函数、类型转换等操作
使用不等于（!= 或 <>）
使用 IS NULL，IS NOT NULL
LIKE 以通配符（%）开头，例如：%tom
除了基本的原则之外，MySQL 还会对查询语句做优化处理，它会重新编排 SQL 中的条件匹配顺序，例如如下的两条查询语句：

SELECT * FROM worker WHERE type = 'B' AND salary > 2000;
SELECT * FROM worker WHERE salary > 2000 AND type = 'B';
也许你会认为第二条 SQL 语句不能使用到索引，但事实却不是这样的，它与第一条查询语句是相同的，MySQL 优化器会按照 SQL 的语义重新编排列顺序，目的就是能够使用索引优化查询。

前缀索引

• 当表中的数据列是字符型，且大多数长度都比较长时，就可以考虑使用列值的一部分前缀作为索引，这也就被称作是前缀索引
• 如何选择比较合适的索引长度，在 MySQL 中专业的叫法是 “索引选择性”（Index Selectivity）。索引选择性是一个数值，它等于 不重复的值 / 所有的值，取值范围介于0到1。1就是代表这一列唯一，没有重复的值。且这个值是可以计算得到的
• 值越接近1索引性能越好

覆盖索引

当一个索引包含需要查询的所有字段时，就称之为覆盖索引
由于索引中已经包含了当前需要查询的两列，就不需要再回数据库查询了，也就是说索引覆盖了数据。通常情况下，索引数据的量级会比真实数据的量级小很多，覆盖索引能够大大的减少从磁盘加载的数据量，也是比较高级的优化手段。

二 查看索引信息

2.1 查看表索引信息

第一种方式： SHOW INDEX FROM WORKER （查看索引字段信息）
第二种方式： SHOW CREATE TABLE worker\G（查看索引建表语句）

2.2 查看索引占据的空间大小

在information_schema.TABLES中查看对应表的总索引大小

SELECT CONCAT(ROUND(SUM(index_length) / (1024*1024), 2), ' MB') FROM information_schema.TABLES;