MySQL 索引的使用

索引介绍

索引是一种特殊的文件(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 值) + 每个表只能有一个主键索引
  • 组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并(使用多个单列索引组合搜索)

索引的操作

创建索引:

1
2
3
4
5
6
7
8
9
10
11
--创建普通索引
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);

创建表时直接指定索引:

1
2
3
4
5
CREATE TABLE table_name (
ID INT NOT NULL,
col_name VARCHAR (16) NOT NULL,
INDEX index_name(col_name)
);

删除索引:

1
2
3
4
5
--直接删除索引
DROP INDEX index_name ON table_name;

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

其它相关命令:

1
2
3
4
5
6
7
8
9
10
11
12
13
--查看表结构
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 bygroup 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)查找,这样查询效率会高跟多。