MySQL调优之索引在甚么情况下会失效详解
[var]
MySQL中提高性能的一个最有效的方式是对数据表设计公道的索引。索引提供了高效访问数据的方法,并且加快查询的速度,因此索引对查询的速度有着相当重要的影响。
- 使用索引可以快速地定位表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。
- 如果查询时没有使用索引,查询语句就会扫描表中的所有记录。在数据量大的情况下,这样查询的速度回很慢。
大多数情况下都(默许)采取B+树来构建索引。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。
其实,用不用索引,终究都是优化器说了算。优化器是基于甚么的斟酌?基于cost开消(CostBaseOptimizer),它不是基于规则(Rule-BasedOptimizer),也不是基于语义,只是根据数值大小。另外,SQL语句会不会使用索引,跟数据库版本、数据量、数据选择度都有关系。
本文我们尝试总结索引失效的一些场景。我们会准备class和student两个表,class插入一万条数据,student插入50万条数据。环境是MySQL8.0,InnoDB。
[var]
系统中常常出现的SQL语句以下:
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = ‘abcd’;
建立索引前履行:
受影响的行: 0
时间: 0.308s
建立索引(age):
建立索引后履行:
受影响的行: 0
时间: 0.113s
继续创建索引(age,classId):
建立索引后履行:
受影响的行: 0
时间: 0.007s
继续创建索引(age,classId,NAME):
建立索引后履行:
受影响的行: 0
时间: 0.000s # 其实必定不是0,只是更小了
从履行计划可以看到,MySQL会帮我们选择最多包括查询列的联合索引。
[var]
在MySQL建立联合索引时会遵照最好左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左侧开始匹配。
举例:age、name可以用到索引。
虽然可以正常使用,但是只有部份被使用到了。而且MySQL优化器斟酌的索引是idx_age,而非idx_age_classid_name。
举例2:
可以看到,没有age开头 ,完全没有用到索引。
举例3:索引idx_age_classid_name还可以否正常使用?
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE classid=4 AND student.age=30 AND student.name = ‘abcd’;
如果索引了多列,要遵照最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
我们删掉索引idx_age 、idx_age_classid 再次履行查询age and name,没有中间的classid。
DROP INDEX idx_age_classid ON student;
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.name = ‘abcd’;
这里key_len=5,说明只用到了联合索引的一部份–age用到了索引。由于其中间环节 classid不存在, 故而不能完全使用联合索引。
结论 : MySQL可以为多个字段创建索引,一个索引可以包括16个字段。对多列索引,过滤条件要使用索引一定要依照索引建立时的顺序,顺次满足,一旦跳过某个字段,索引后面的字段都没法被使用。如果查询条件中没有使用这些字段中第一个字段时,多列(或联合)索引不会被使用。
对=值查询,如果where中条件查询没有依照联合索引字段顺序编写,MySQL优化器会进行调优以使其满足联合索引字段顺序。
[var]
对一个使用InnoDB存储引擎的表来讲,在我们没有显示的创建索引时,表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的,数据页和记录又是依照记录主键值从小到大的顺序进行排序。所以如果我们插入的记录的主键值是顺次增大的话,那我们每插满一个数据页就换到下一个数据页继续插。
而如果我们插入的主键值忽大忽小的话,就比较麻烦了。假定某个数据页存储的记录已满了,它存储的主键值在1~100之间:
如果此时再插入一条主键值为9的记录,那它插入的位置就以下图:
可这个数据页已满了,再插进来咋办呢?我们需要把当前页面分裂成两个页面,把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着甚么?意味着性能消耗! 所以如果我们想进来避免这样无谓的性能消耗,最好让插入的记录的主键值顺次递增,这样就不会产生这样的性能消耗了。
所以我们建议:让主键具有AUTO_INCREMENT,让存储引擎自己为表生成主键,而不是我们手动插入,比如person_info表:
id int unsigned not null auto_increment,
name varchar(100) not null,
birthday date not null,
phone_numnber char(11) not null,
country varchar(100) not null,
primary key (id),
key idx_name_bd_ph_num(name(10),birthday,phone_number)
)
我们自定义的主键列id具有AUTO_INCREMENT
属性,在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小,顺序写入,减少页分裂。
[var]
以下两条SQL,哪一个更好呢?实际上是第一条,能够使用到索引,第二条有了函数计算。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = ‘abc’;
我们创建索引(NAME):
查看第一条SQL的履行计划:
查看第二条SQL的履行计划:
对照履行计划可以看到,第一条SQL使用到了索引,第二条SQL的type=all表示全表扫描。说明函数计算或致使索引失效。
我们再看一下数学计算:
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
如上图所示,SQL中有数学计算,履行计划中 type=all表示没有使用索引进行了全表扫描。我们再看下面这个SQL,很明显其会使用到索引。这就说明数学计算会致使索引失效。
最后我们再看一下类型转换
字符串类型一定不要忘记单引号,否则索引失效。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;
对照两者的履行计划可知,类型转换会致使索引失效。
[var]
首先删除表student的索引:
alter table student drop index idx_age;
alter table student drop index idx_age_classid;
查看当前索引:show index from student;
对以下SQL,索引idx_age_classid_name还可以够正常使用吗?
WHERE student.age=30 AND student.classId > 20 AND student.name = ‘abc’ ;
履行计划以下所示,key_len=10,说明只有age和classid用到了索引。
这时候候即便交换次序,也是没成心义的,以下所示:
WHERE student.age=30 AND student.name = ‘abc’ AND student.classId>20;
那末如何使其能够使用到索引呢?以下所示创建索引(age,NAME,classId)。
这时候再履行上面SQL,可以看到充分用到了联合索引。
对 下面这个SQL,履行计划是一样的。查询优化器对and条件会进行顺序的调剂,以满足联合索引的顺序。
WHERE student.age=30 AND student.classId > 20 AND student.name = ‘abc’ ;
总结
- 范围右侧的列不能使用索引。比如 < 、<=、 >、 >=、 between。
- 这个右侧指的是联合索引字段的右侧,至于SQL where中的and条件,查询优化器是可以进行调剂的。
- 创建的联合索引中,务必把范围触及到的字段写在最后。
[var]
为name字段创建索引:
进行等值判断,正常使用索引:
对不等判断,查看索引会不会失效:
可以看到,两条SQL均为使用到索引。
[var]
is null可以触发索引
is not null没法使用索引
结论: 最好在设计数据表的时候就将字段设置为not null
束缚,比如你可以将int类型的字段,默许值设置为0
.将字符类型的默许值设置为空字符('')
。同理,在查询中使用 not like
也没法使用索引,致使全表扫描。
[var]
在使用like关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”
,索引就不会起作用。只有"%"
不在第一个位置,索引才会起作用。
使用到索引
没有用到索引
[var]
在where子句中,如果在or前的条件列进行了索引,而在or后的条件列没有进行索引,那末索引会失效。也就是说,OR 前后的两个条件中的列都是索引列时,查询中才会使用到索引。
由于OR的含义就是两个只要满足一个便可,因此只有一个条件列进行了索引是没成心义的。只要有条件列没有进行索引,就会进行全表扫描,因此索引的条件列也会失效。
# 删除索引
alter table student drop index idx_age_classid_name;
alter table student drop index idx_age_name_cid;
alter table student drop index idx_sno;
alter table student drop index idx_name;
#创建索引
CREATE INDEX idx_age ON student(age);
这时候我们查询语句使用OR关键字的情况(age有索引,classid没有索引)
可以看到,是没有使用到索引的。如果我们为classid创建索引呢?
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
可以看到,其使用到了索引,type=index_merge
。简单来讲,index_merge就是对age和classid分别进行了扫描,然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描。
[var]
统一使用utf8mb4(5.5.3版本以上支持)兼容性更好,统一字符集可以免由于字符集转换产生的乱码。
区别的字符集进行比较前需要进行转换会造成索引失效。
一般性建议:
- 对单列索引,尽可能选择针对当前query过滤性更好的索引;
- 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好;
- 在选择组合索引的时候,尽可能选择能够包括当前query中的where子句中更多字段的索引;
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽可能把这个字段放在索引次序的最后面。
[var]
到此这篇关于MySQL调优之索引在甚么情况下会失效的文章就介绍到这了,更多相关MySQL索引失效内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持!
文章来源:丸子建站
文章标题:MySQL调优之索引在甚么情况下会失效详解
https://www.wanzijz.com/view/47181.html