MySQL高级(4) 索引的使用

---

MySQL高级(4) 索引的使用

索引的使用

几种常见的插入优化方式

• 一个语句插入多行, 插入语句时, 尽量在values后面添加多行数据, 而不是一个一个的insert

• 因为在索引在插入时会变慢, 所以在插入大量数据时, 可以把整个表所有的索引(除了主键)都删除,然后再插入数据

• 减少提交提交次数(使用事务), MySQL默认插入一次提交一次,但是插入大量数据时, 可以关闭自动提交, 在所有数据插入完成之后再提交一次

• 使用多线程, 框架等

查询所有的索引

整个MySQL所有的索引都存储在 information_schema 库中的STATISTICS表中

单表查询使用索引注意事项

全值匹配

即如果可以的话, 尽量在所有的过滤条件(where,having)上添加索引

注意

不用在意索引的顺序和查询的顺序是否一致, 即便是不一致, MySQL优化器会自动调整查询的顺序, 所以相同字段复合索引只需要建立一个就可以了

例如以 idx_姓名_班级_学号 建立索引, 查询顺序可以是

select 姓名_班级_学号
select 班级_姓名_学号
select 学号_姓名_班级
.....

最佳左前缀法则

符合索引的匹配规律是按照顺序从左到右依次匹配, 如果匹配成功就成功, 如果某个字段匹配失败, 那么后面所有的字段都不再匹配索引

举例 1 如果我们建立了一个复合索引 idx_姓名_班级_学号

假设这里有一个过滤条件

where 姓名='XXX' and 班级='XXX' and 学号='XXX'
或者
where 班级='XXX' and 学号='XXX' and 姓名='XXX'

结合最佳左前缀法则, 第一个匹配姓名索引匹配成功, 然后第二个再匹配班级索引匹配成功, 根据上面的全值匹配, 这里where后面的字段顺序可以不同, 但是必须存在符合索引中的字段才能够正确匹配

举例 2 同上有一个相同的索引

过滤条件

where 姓名='XXX' and 学号='XXX'
或者
where 学号='XXX' and 姓名='XXX'

第一个姓名匹配成功, 第二个班级没有匹配, 按照最佳左前缀法则, 后面的字段将不再匹配索引, 所以这个查询只匹配了一个姓名索引

如果使用函数, 会使索引失效

举例 在emp表中查询姓王的人员信息

select * from emp where emp.name like '王';
select * from emp where left(emp.name,3) '王';

在不建立索引的情况下, 上面2条语句执行速度是差不多的, 但是如果建立了 name 的索引, 第一条执行速度会比第2条快很多, 通过explain指令可以看出第一条语句使用了索引, 而第2条语句没有使用索引

注意

如果使用like, 要保证首字母确定, 如果首字母不确定(即 where name like '%豆豆'), 这种情况下不会使用索引

复合索引中范围查询右边的字段, 索引会失效

举例 现在有一个索引idx_姓名_工资_年龄, 有以下查询语句

select * from emp where name='XXX' and salary>5000 and age>30;

这条语句第一字段name会根据索引查询, 但是第二个字段是工资, 而SQL语句中工资的查询是一个范围, 所以复合索引中工资后面的年龄索引会失效, 所以这条语句只走了姓名和工资2个索引

注意

1. 结合这个例子, 我们在创建索引时, 尽量把可能会用到范围查询的字段放在后面

2. like也是一种范围查询, 但是如果like确定了前缀, 可以使用索引

不等于(<>)索引失效

如果查询语句中使用了不等于(<>), 那么索引会失效, 所以在实际工作中尽量避免使用不等于

if null 会使用索引, if not null 不会使用索引

跨类型的查询不会使用索引

举例 在emp表中, name字段为varchar值

select * from emp where name=1234;

因为MySQL会自动转换int和varchar的值, 所以这条语句并没有出错, 但是因为做了类型转换, 所以即便是建立了 name 索引, 这里也不会使用索引

注意

根据这里说明, 在写java代码的时候, bean对象里的属性的类型和字段的类型一定要一一对应, 否则可能会出现类型转换, 从而导致无法使用索引

总结

• 对于单键索引, 尽量选择针对当前查询过滤性更好的索引, 例如手机号, 身份证好
• 复合索引, 过滤性比较好的字段放在前面
• 尽量保证索引字段尽可能多的用到
• 某个索引字段如果可能会使用到范围查询, 尽量放在后面

关联查询使用索引注意事项

几个概念

这里要先说一下关联查询中的几个概念

• 驱动表

两个表关联查询, 主表就是驱动表

• 被驱动表

除主表之外的表时被驱动表

举例

select * 
from t1 
left join t2
on ...

t1就是驱动表, t2是被驱动表

如果是右关联:

select * 
from t1 
right join t2
on ...

t2就是驱动表, t1是被驱动表

left|right join

举例 假设有2张表

1. 部门表(dept): 其中有 id, name 2个字段
2. 员工表(emp): 其中有 id, deptId

查询语句如下:

explain
select * 
from dept d
left join emp e
on d.id=emp.deptId

• 两个表都没有索引

然后使用explain解析上面的语句, 结果如下(只保留了关键字段), 可以看出表中没有使用到任何索引

id	table	type	key_len	rows	extra
1	d	ALL		9932
1	e	ALL		498576	Using where; Using join buffer

• 被驱动表使用索引

为了加快查询速度, 我们给emp中的depId创建一个索引:

CREATE INDEX idx_deptId ON emp(deptId)

执行explain结果如下, 可以看出在被驱动表中使用了索引

id	table	type	key_len	rows	extra
1	d	ALL		9932
1	e	ref	5	49

• 驱动表使用索引

如果是只是驱动表中使用索引呢, 测试结果跟不使用索引没有区别

id	table	type	key_len	rows	extra
1	d	ALL		9932
1	e	ALL		498576	Using where; Using join buffer

如果将驱动表和被驱动表位置换一下呢?

# 此时emp是驱动表, dept是被驱动表
EXPLAIN
select * 
from emp e
left join dept d
on d.id=e.deptId

执行结果如下, 可以看出, 被驱动表使用了索引

id	table	type	key_len	rows	extra
1	e	ALL		498576	Using where; Using join buffer
1	d	ref	4	1	Using where; Using join buffer

• 两个表都使用索引, 结果跟被驱动表使用索引是一样的

id	table	type	key_len	rows	extra
1	d	ALL		9932
1	e	ref	5	49

如果测试right join, 会发现结果也是一样的, 就是驱动表中的索引失效

结论

通过测试可以看出, 驱动表中即便是有索引, 也会进行全表扫描, 所以在使用关联索引的时候, 只需要给被驱动表的字段添加索引即可

inner join

举例 假设有2张表

1. 部门表(dept): 其中有 id, name 2个字段
2. 员工表(emp): 其中有 id, deptId

查询语句如下:

explain
select * 
from dept d
inner join emp e
on d.id=emp.deptId

• 两个表都没有索引

然后使用explain解析上面的语句, 结果如下(只保留了关键字段), 可以看出表中没有使用到任何索引

id	table	type	key_len	rows	extra
1	d	ALL		9932
1	e	ALL		498576	Using where; Using join buffer

• 使用一个索引

为了加快查询速度, 我们给emp中的depId创建一个索引:

CREATE INDEX idx_deptId ON emp(deptId)

然后使用explain解析上面的语句, 结果如下(只保留了关键字段), 可以看出, emp表使用了索引, 并且扫描行数明显减少

id	table	type	key_len	rows	extra
1	d	ALL		9932
1	e	ref	5	49

如果说, 给驱动表添加索引呢, 结果好像有变化, 跟left|right不同的是, 在inner join中, 驱动表竟然也可以设置索引? 别着急, 继续往下看

id	table	type	key_len	rows	extra
1	e	ALL		498576	Using where; Using join buffer
1	d	ref	4	1

• 两个表都使用索引, 我们会发现, 跟一开始的结果是一样的

id	table	type	key_len	rows	extra
1	d	ALL		9932
1	e	ref	5	49

总结

在inner join中, MySQL可以自动将有索引的表设置为被驱动表, 从而加快查询速度, 但是如果驱动表和被驱动表都使用了索引, 驱动表仍然会进行全表扫描而不会使用索引

关联优化总结

1. 尽量将小表放在驱动表, 因为驱动表会全表搜索
2. 虚拟表(子查询的结果表)不会使用索引, 所以尽量将虚拟表放在驱动表的位置(5.7以后优化了), 如果可以不使用子查询, 就不要使用子查询

排序分组使用索引注意事项

排序条件过滤

我们使用员工表做一个例子, 现在表中没有索引

EXPLAIN
SELECT * 
FROM emp
ORDER BY age,deptId;

执行结果, 在没有索引的情况下, 出现了Using filesort, 这种情况下速度是非常慢的

id	table	type	key_len	rows	extra
1	emp	ALL		498576	Using filesort

我们给排序字段添加上索引

CREATE INDEX idx_age_deptId ON emp(age, deptId);

然后查看执行结果, 发现虽然添加了索引, 但是并没有使用索引

id	table	type	key_len	rows	extra
1	emp	ALL		498576	Using filesort

但是如果我们加上过滤条件

EXPLAIN
SELECT * 
FROM emp
WHERE age=50
ORDER BY deptId, age;

再执行发现, 竟然用上索引了!

id	table	type	key_len	rows	extra
1	emp	ref	5	46810	Using index condition

结论

单纯的排序, 不会使用索引, 需要加上过滤条件才会使用索引

多条字段排序

上面的例子也用到了多条字段, 之前说过, 只要索引字段包括了过滤条件的字段(即覆盖索引), MySQL会自动给过滤条件重新排序, 但是在分组和排序中呢?

我们把上面的语句排序字段换一下顺序

EXPLAIN
SELECT * 
FROM emp
WHERE age=50
ORDER BY age, deptId;

执行结果好像并没有什么问题, 这说明如果索引是2个字段, 可以改变排序字段的顺序

id	table	type	key_len	rows	extra
1	emp	ref	5	46810	Using index condition

如果说多个字段呢, 这里我们再加一个

EXPLAIN
SELECT * 
FROM emp
WHERE age=50
ORDER BY age, deptId, empno;

然后创建索引

CREATE INDEX idx_age_deptId_empno ON emp(age, deptId, empno);

执行结果, 意料之中, 用到了索引,

id	table	type	key_len	rows	extra
1	emp	ref	5	46810	Using index condition

但是如果打破顺序呢?

比如:

EXPLAIN
SELECT * 
FROM emp
WHERE age=50
ORDER BY age, empno; #这里少中间的depId

# 或者

EXPLAIN
SELECT * 
FROM emp
WHERE age=50
ORDER BY age, empno, deptId;

执行结果, 确实也用到了索引, 但是注意extra字段, 还是进行了Using filesort,

id	table	type	key_len	rows	extra
1	emp	ref	5	46810	Using index condition; Using filesort

说明

1. 这是因为按照最佳左前缀原则, 第一个可以理解, 因为中间的depId字段没有, 只匹配了 age 字段的索引
2. 第二个按理说MySQL可以自动更换顺序, 但是注意, 自动更换字段顺序是在不改变查询结果的情况下, 但是order by后面的字段顺序改变, 查询结果就改变了, 所以这里不会自动改变顺序

排序顺序

刚刚我们测试的都是按照默认(全部升序)

接下来我们改变一下排序顺序(全部降序)

EXPLAIN
SELECT * 
FROM emp
WHERE age=50
ORDER BY age DESC, deptId DESC, empno DESC;

结果如下, 说明如果全部都是降序, 也会使用索引

id	table	type	key_len	rows	extra
1	emp	ref	5	46810	Using where

但是如果部分降序呢?

• 只有第一个字段降序

# 只有第一个字段降序
EXPLAIN
SELECT * 
FROM emp
WHERE age=50
ORDER BY age DESC, deptId, empno;

结果仍旧是使用索引

id	table	type	key_len	rows	extra
1	emp	ref	5	46810	Using where

• 只有第二个字段降序

# 只有第二个字段降序
EXPLAIN
SELECT * 
FROM emp
WHERE age=50
ORDER BY age, deptId DESC, empno;

可以看到, 如果只有第二个字段降序, 会出现 Using filesort

id	table	type	key_len	rows	extra
1	emp	ref	5	46810	Using index condition; Using filesort

• 最后一个字段降序

# 最后一个字段降序
EXPLAIN
SELECT * 
FROM emp
WHERE age=50
ORDER BY age, deptId, empno DESC;

也是不会使用索引

id	table	type	key_len	rows	extra
1	emp	ref	5	46810	Using index condition; Using filesort

结论

我们可以发现, 只有第一个可以随意的改变顺序, 除此之外, 想要使用索引排序, 要么全部升序, 要么全部降序

分组

分组和排序几乎是一样的, 唯一的区别就是即便不使用条件过滤, 分组过程中也会使用索引

使用覆盖索引

尽量不要使用select *, 而是尽量把查询的字段写出来(虽然上面的示例都使用了select *), 避免使用select *可以提高索引的使用率