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确定了前缀, 可以使用索引

不等于(<>)索引失效

如果查询语句中使用了不等于(<>), 那么索引会失