MySQL 索引的使用

发表于 2020-06-09 阅读次数：评论数：本文字数： 3.4k 阅读时长 ≈ 3 分钟

索引介绍

索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），包含了对数据表里所有记录的引用指针。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，需要确保该索引是应用在 SQL 查询语句的条件 (一般是 WHERE、JOIN 子句的条件)。

索引的类型（四种）

FULLTEXT：即为全文索引，目前只有 MyISAM 引擎支持，其可以在 CREATE TABLE，ALTER TABLE，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引
HASH：由于 HASH 的唯一性及类似键值对的形式，很适合作为索引，HASH 索引可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率。但是，这种高效是有条件的，即只在 “=” 和 “in” 条件下才高效，对于范围查询、排序及组合索引仍然效率不高
BTREE：一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口 Root 开始，依次遍历 Node，获取 Leaf，这是 MySQL 里默认和最常用的索引类型
RTREE：在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎有 MyISAM、BDb、InnoDb、NDb、Archive，相对于 BTREE，RTREE 的优势在于范围查找

索引的种类（五种）

普通索引：仅加速查询（BTREE 类型）
全文索引：对文本的内容进行分词和搜索
唯一索引：加速查询 + 列值唯一（可以有 NULL 值）
主键索引：加速查询 + 列值唯一（不可以有 NULL 值） + 每个表只能有一个主键索引
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（使用多个单列索引组合搜索）

索引的操作

创建索引：

--创建普通索引
CREATE INDEX index_name ON table_name(col_name);

--创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

--创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1, col_name_2);

--创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1, col_name_2);

通过修改表结构创建索引：

1	ALTER TABLE table_name ADD INDEX index_name(col_name);

创建表时直接指定索引：

CREATE TABLE table_name (
    ID INT NOT NULL,
    col_name VARCHAR (16) NOT NULL,
    INDEX index_name(col_name)
);

删除索引：

--直接删除索引
DROP INDEX index_name ON table_name;

--修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

其它相关命令：

--查看表结构
desc table_name;

--查看创建表的SQL
show create table table_name;

--查看索引
show index from table_name;

--查看执行时间
set profiling = 1;
SQL ...
show profiles;

索引使用的代价

索引虽然可以大大提高了查询速度，但同时也会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE 操作；因为更新表时，MySQL 不仅要保存数据，还要更新索引文件
建立索引会占用更多的磁盘空间，这是因为需要分配磁盘空间给索引文件，一般情况这个问题不太严重，但如果在一个大表上创建了多种组合索引，索引文件的体积会膨胀得很快

索引适用的场景

索引创建的时机

一般来说，在 WHERE 和 JOIN 子句中出现的列需要建立索引，但也不完全如此，因为 MySQL 只对 <、<=、=、>、>=、BETWEEN、IN 以及某些时候的 LIKE 才会使用索引。例如下述的 SQL 语句，就需要对 city 和 age 列建立索引，由于 mytable_m 表的 userame 也出现在了 JOIN 子句中，因此也有对它建立索引的必要。

1	SELECT t.Name FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;

特别注意：上面提到只有某些时候的 LIKE 才需建立索引，因为在以通配符 % 开头作查询时，MySQL 不会使用索引；只有以通配符 % 结尾做查询时，MySQL 才会使用到索引。但有一种情况例外，那就是当触发了覆盖索引（select 的数据列只从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖）的情况下，以通配符 % 开头作查询 MySQL 也会使用索引。例如：如果表里面只有 id 和 username 两个字段且都加了索引，那么 select * like '%username' 查询也是会使用索引的，前提是 select 数据列都加了索引。

哪些字段应该创建索引

增删改非常频繁的字段不适合作为索引
查询中与其他表关联的字段，例如外键应该建立索引
WHERE 和 JOIN 子句中，较频繁作为查询条件的字段应该创建索引
查询中排序（order by）、分组（group by）、统计的字段应该建立索引
唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如：性别字段

索引不生效的情况

对于多列索引，如果不是使用的第一部分，则不会使用索引
如果 MySQL 估算使用全表扫描要比使用索引快，则不会使用索引
like 查询，即是以 % 开头的查询不会使用索引，除非 select 数据列都加了索引
如果列类型是字符串，那一定要在条件中将数据使用单引号包起来，否则索引不生效
如果条件中有 or，即使其中有部分条件带索引也不会使用。换言之，必须所有列都建有索引才有效

索引使用注意事项

针对普通查询
- 避免使用 select *
- 连表时注意条件类型需一致
- 创建表时尽量时 char 代替 varchar
- count (1) 或 count (列) 代替 count (*)
- 使用表连接（JOIN）来代替子查询（Sub-Queries）
针对索引使用
- 使用组合索引代替多个单列索引（经常使用多个条件查询时）
- 索引散列值（重复多的值）不适合建索引，例如：性别字段不适合建索引
- 索引不会包含有 NULL 值的列，只要列中包含有 NULL 值都将不会被包含在索引中，组合索引中只要有一列含有 NULL 值，那么这一列对于此组合索引就是无效的，因此在数据库设计时不要让字段的默认值为 NULL
- 不要在列上进行运算，例如 select * from users where YEAR(adddate)<2007，将在每个行记录上进行运算，这将导致索引失效而进行全表扫描，因此可以改成 select * from users where adddate<’2007-01-01′
- 尽量使用短索引，对串列进行索引，如果可能应该指定一个前缀长度。例如：如果有一个 CHAR (255) 的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引；短索引不仅可以提高查询速度，还可以节省磁盘空间和 I/O 操作
- MySQL 5.0 之前，SQL 查询只能使用一个索引，因此如果 WHERE 子句中已经使用了索引的话，那么 order by、group by 中的列是不会使用索引的。因此如果数据库默认排序可以符合要求的情况下，不要使用排序操作，同时尽量使用不包含多个列的排序，如果需要最好给这些列创建组合索引

查看索引的使用效果

执行计划

Explain + 查询 SQL，用于显示 SQL 执行信息参数，根据参考信息可以进行 SQL 优化或者判断索引是否生效

mysql-explain-sql

查看索引的使用情况

1	show status like '%Handler_read%';

handler_read_key：这个值越高越好，越高表示使用索引查询到的次数越多
handler_read_rnd_next：这个值越高，说明查询效率低效

补充说明

MySQL 查询只能使用一个索引？

MySQL 5.0 之前，SQL 查询只能使用一个索引，所以要合理使用组合索引，而不是单列索引。与其说是 “数据库查询只能用到一个索引”，倒不如说和全表扫描、只使用一个索引的查询速度比起来，去分析多个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是用一个索引。特别注意：从 MySQL 5.1 开始，引入了索引合并优化技术，对同一个表可以使用多个索引分别进行条件扫描。

1	select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar';

例如上面的语句，当数据库有 N 个索引并且查询中分别都要用上它们的情况下：查询优化器（用于生成执行计划）需要进行 N 次主二叉树查找（这里主二叉树的意思是最外层的索引节点），此时的查找流程大概是：查出第一条 column1 主二叉树等于 1 的值，然后去第二条 column2 主二叉树查出 foo 的值并且当前行的 coumn1 必须等于 1，最后去 column3 主二叉树查找 bar 的值并且 column1 必须等于 1 和 column2 必须等于 foo。如果这样的流程被查询优化器执行一遍，就算不死也半条命了，查询优化器可等不及把以上计划都执行一遍，贪婪算法（最近邻居算法）可不允许这种情况的发生。所以当遇到上面的语句，数据库只要用到第一个筛选列的索引（column1），就会直接去进行表扫描了。所以与其说是数据库只支持一条查询语句只使用一个索引，倒不如说 N 个独立索引同时在一条语句使用的开销比只使用一个索引还要大。最佳推荐是使用 index(column1, column2, column3） 这种组合索引，此组合索引可以把 B+Tree 结构的优势发挥得淋漓尽致。一条主二叉树（column=1），查询到（column=1）节点后基于当前节点进行二级二叉树（column2=foo）的查询，在二级二叉树查询到（column2=foo）后，去三级二叉树（column3=bar）查找，这样查询效率会高跟多。