MySQL进阶（三）

关于索引失效那些事......

在执行SQL语句时，有时尽管设置了索引，但是在语句真正执行时却依然是全表扫描，并没有优化性能，这其实与我们所写的SQL语句有很大关系，以下简单列举几个导致索引失效的原因以及例子。

针对接下来的点，使用的例子，我们先简单建个book表，并插入一些数据，建立一个简单的索引。

-- 建立book表
create table if not exists book(
book_id int auto_increment,
book_name varchar(20) not null default '',
cat_id int not null,
book_price decimal(5,2) not null default 0.00,
cost decimal(5,2) not null default 0.00,
primary key(book_id)
)engine=innoDB charset utf8;

-- 插入数据
insert into book(book_name, cat_id, book_price,cost) values 
('追风筝的人', 1, 25.33, 12.56),
('活着', 2, 20.45, 13.55),
('兄弟', 2, 20.36, 15.00),
('许三观卖血记', 2, 30.00, 24.56),
('喂，看见耳朵啦', 5, 24.23, 11.56),
('朝花惜时', 5, 20.56, 16.50),
('组词造句', 4, 40.99, 35.49),
('我会好好的', 3, 18.59, 10.46),
('花千骨', 3, 28.29, 25.36),
('撒野', 3, 45.88, 40.78),
('五三高考', 4, 53.26, 50.92),
('霍乱时期的爱情', 1, 28.33, 24.88),
('1982', 1, 45.28, 39.16);

-- 建立索引
create index idx_BnBpCi on book(book_name, book_price, cat_id);
create index idx_BpCost on book(book_price,cost);
create index idx_BpCat on book(book_price, cat_id);

1、索引的全值匹配。即所需的列与索引列的顺序以及个数完全一致。

2、最佳左前缀法则，即所需的列一定是从索引的左边开始，可以个数少，但不能缺少中间列。

3、不能在索引列上进行操作，包括函数、计算以及转换类型等操作，操作后无法使用索引而变成全表扫描。

4、存储引擎不能使用范围条件右边的列。

5、尽量使用覆盖索引（只访问索引的查询，即查询列和索引列一致）。

6、使用不等于的时候无法使用索引会导致全表扫描。

7、is null， is not null也无法使用索引。

8、like以通配符开头（‘%......’），索引失效会变成全表扫描。

9、字符串不加单引号索引失效，其实这类操作属于强制转型，与上述3原因一致。

10、少用or，用它连接会索引失效。