MySQL索引

文章目录

1. 1. 索引优化
   1. 1.1. 索引失效案例
   2. 1.2. 小总结
   3. 1.3. 练习题
2. 2. InnoDB 聚簇索引和非聚簇索引
   1. 2.1. 聚簇索引如何加快查询速度
   2. 2.2. 非聚簇索引和聚簇索引的关系
   3. 2.3. 聚簇索引和非聚簇索引的区别
   4. 2.4. 参考

索引优化

索引失效案例

1. 全值匹配
2. 最佳左前缀法则。如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
3. 不在索引列上做任何操作（计算、函数、「自动或者手动」类型转换），会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引（只访问索引的查询「索引列和查询列一致」），减少 select *
6. MySQL 在使用 != 或者 <> 的时候无法使用索引会导致全表扫描
7. is null，is not null 无法使用索引
8. like 以通配符开头 (‘%abc…’) MySQL 索引失效会变成全表扫描的操作
9. 字符串不加单引号索引失效
10. 少用 or，用它来连接时会索引失效

小总结

假设 index(a, b, c)

Where 语句	索引是否被使用
where a = 3	Y，使用到 a
where a = 3 and b = 5	Y，使用到 a，b
where a = 3 and b = 5 and c = 4	Y，使用到 a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到 a，但是 c 不可以，b 中间断了
where a = 3 and b > 4 and c = 5	使用到 a 和 b，c 不能用在范围之后，b 断了
where a = 3 and b like ‘kk%’ and c = 4	Y，使用到 a，b，c
where a = 3 and b like ‘%kk’ and c = 4	Y，只用到 a
where a = 3 and b like ‘%kk%’ and c = 4	Y，只用到 a
where a = 3 and b like ‘k%kk%’ and c = 4	Y，只用到 a，b，c

练习题

#  创建表
create table test03(
 id int primary key not null auto_increment,
 c1 char(10),
 c2 char(10),
 c3 char(10),
 c4 char(10),
 c5 char(10)
);

# 插入数据
insert into test03(c1,c2,c3,c4,c5)values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5)values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5)values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5)values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5)values('e1','e2','e3','e4','e5');

# 创建索引
create index idx_test03_c1234 on test03(c1,c2,c3,c4);

问题：我们创建了复合索引idx_test03_c1234 ，根据以下SQL分析索引使用情况？

1. explain select * from test03 where c1=’a1’ and c2=’a2’ and c3=’a3’ and c4=’a4’;
   索引全部使用

2. explain select * from test03 where c1=’a1’ and c2=’a2’ and c4=’a4’ and c3=’a3’;
   索引全部使用

3. explain select * from test03 where c1=’a1’ and c2=’a2’ and c3>’a3’ and c4=’a4’;
   索引 c1c2c3 使用，c4 未使用

4. explain select * from test03 where c1=’a1’ and c2=’a2’ and c4>’a4’ and c3=’a3’;
   索引全部使用

5. explain select * from test03 where c1=’a1’ and c2=’a2’ and c4=’a4’ order by c3;
   索引 c1c2 使用，c3 作用在排序而不是查找，c4 不使用

6. explain select * from test03 where c1=’a1’ and c2=’a2’ order by c3;
   索引 c1c2 使用 c3 作用在排序而不是查找，和 5 题一样

7. explain select * from test03 where c1=’a1’ and c2=’a2’ order by c4;
   索引 c1c2 使用 ，但出现 Using filesort

8. explain select * from test03 where c1=’a1’ and c5=’a5’ order by c2,c3;
   索引 c1 使用，但是 c2，c3用于排序，无 filesort

explain select * from test03 where c1=’a1’ and c5=’a5’ order by c3,c2;
索引 c1 使用，出现 filesort，我们建立索引 1234，它没有按照顺序来，3 2 颠倒了

9. explain select * from test03 where c1=’a1’ and c2=’a2’ order by c2,c3;
   索引 c1c2 使用

10. explain select * from test03 where c1=’a1’ and c2=’a2’ and c5=’a5’ order by c2,c3;
    索引 c1c2 使用，但是 c2、c3 用于排序，无 filesort

explain select * from test03 where c1=’a1’ and c2=’a2’ and c5=’a5’ order by c3,c2;
本例有常量 c2 的情况，和 8.2 对比，无filesort

11. explain select * from test03 where c1=’a1’ and c4=’a4’ order by c2,c3;
    索引 c1 使用

12. explain select * from test03 where c1=’a1’ and c4=’a4’ order by c3,c2;
    索引 c1 使用, 出现了 Using where; Using temporary; Using filesort

InnoDB 聚簇索引和非聚簇索引

每个 InnoDB 表都有一个称为 「 聚簇索引 」 的特殊索引，通常情况下，这个聚簇索引就是 「 主键 」 ( primary key ) 。Innodb 使用它存储表中每一行的数据。

如果想要从查询、插入和其它数据库操作中获得最佳性能，那么我们就必须了解 InnoDB 如何使用 「 聚簇索引 」 来优化每个表的最常见检索和 DML 操作方式

• 当我们在一个 Innodb 表上定义了一个主键，InnoDB 会默认的使用它作为聚簇索引。

  使用 InnoDB 存储引擎时，建议为每个表都添加一个主键。如果该表没有一个逻辑唯一且非空列或列集合，那么可以添加一个带有 AUTO_INCREMENT 约束的自增列作为主键，InnoDB 会自动填充该列。

• 如果某个 InnoDB 表并没有定义主键。那么 InnoDB 会查找第一个 「 唯一索引 」( UNIQUE Index ) ，因为唯一索引的所有键 ( key ) 都是 NOT ，因此可以用来作为聚簇索引。

• 如果某个 InnoDB 表既没有定义主键，也没有一个合适的唯一索引。InnoDB 会在内部生成一个名为 GEN_CLUST_INDEX 的隐式的聚簇索引

  该聚簇索引的键 ( key ) 会包含一个自动为行生成的 ID 值 ( 行号 ) 。

  该表中的所有行会按 InnoDB 分配给此类表中的行的 ID 排序。

  行 ID 是一个 6 字节的字段，在插入新行时会单调自增。

  因此，可以认为物理上的行保存顺序就是该行 ID 排序的排序顺序

聚簇索引如何加快查询速度

通过聚簇索引访问行很快，因为索引搜索直接指向包含所有行数据页 ( data page )。

如果表很大，与那种索引页与数据页分离的 MyISAM 存储引擎相比， 聚簇索引体系结构通常可以节省磁盘 I/O 操作。

非聚簇索引和聚簇索引的关系

非聚簇索引，通常也称之为 「 二级索引 」 ( Secondary Indexes ) 或 「 辅助索引 」 ，一般是指聚簇索引之外的所有其它的索引。

在 InnoDB 中，每个辅助索引中的每条记录都会包含该行的主键列 ( 也就是聚簇索引的键 ) ，以及为辅助索引指定的列。InnoDB 使用此主键值来搜索聚簇索引中的行。

如果主键很长，那么辅助索引就会占用更多空间，因此使用短主键是有利的，也是我们所推荐的。

聚簇索引和非聚簇索引的区别

1. 首先，我们要认识到聚簇索引和非聚簇索引的划分依据是什么？

   答案就是 InnoDB 会使用聚簇索引来保存数据，而非聚簇索引的目的仅仅是加快查询速度

2. 在第一点认知基础上，我们就可以知道

   • 聚簇索引是唯一的，一个 InnoDB 表只有一个聚簇索引，而且一定会有一个聚簇索引，如果不存在，Innodb 存储引擎会自动添加一个
   • 非聚簇所以可以有多个，而且只能由用户自己添加，InnoDB 默认并不会创建任何非聚簇索引。

3. 非聚簇索引中一定包含了聚簇索引的列值，但反过来却不存在。

   因此，使用非聚簇索引查询数据一定会用到聚簇索引，但反过来却不存在。

参考

http://cmsblogs.com/?p=5463