MySQL优化之索引优化(二)

58 阅读2分钟

开启掘金成长之旅!这是我参与「掘金日新计划 · 12 月更文挑战」的第10天,点击查看活动详情

前言

上篇我们学习了MySQL中的数据库优化之索引优化。有兴趣的小伙伴可以阅读(MySQL优化之索引优化(一))。
下面我们继续学习MySQL中的数据库优化之索引优化。

索引优化

计算,函数的索引失效

创建索引

CREATE INDEX idx_name ON student(NAME);

第一种索引失效

SELECT SQL_NO_CACHE *
FROM student
WHERE student.name LIKE `abc%`;

使用索引的话,查询到结果只需0.01s。但是如果使用函数的话:

SELECT SQL_NO_CACHE *
FROM student
WHERE LEFT(student.name, 3) = `abc%`;

查询到同样的结果,需要用到3.5s,查询效率较之前低很多。运行EXPLAIN查看。

EXPLAIN SELECT SQL_NO_CACHE *
FROM student
WHERE LEFT(student.name, 3) = `abc%`;

结果中type字段结果是ALL,表示没有使用到索引,所以查询时间长。

类型转换导致的索引失效

如name字段设置了索引。

EXPLAIN SELECT SQL_NO_CACHE *
FROM student
WHERE name = 7;

结果中type字段结果是ALL。

EXPLAIN SELECT SQL_NO_CACHE *
FROM student
WHERE name = `7`;

结果中type字段结果是ref。说明索引生效了。name=7发生了类型转换,索引失效了(这里存在隐式的类型转换)。

范围条件右边的列索引失效

EXPLAIN SELECT SQL_NO_CACHE *
FROM student
WHERE student.age = 7
AND studeng.classId > 10
AND student.name = `opq`;

这里的范围条件studeng.classId > 10后面的student.name = opq会失效。要想索引能生效,需要把范围条件放在最后进行查询。

不等于索引失效

当SQL语句中有!=或者<>会出现索引失效的问题,尝试改写为等于,或采用覆盖索引。

is null可以使用索引,is not null无法使用索引

like以通配符%开头,索引会失效

页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。

OR前后存在非索引的列,索引失效

让OR的前后条件都具备所以,如果缺少一个就会出现索引失效。

数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

今天先学习到这里,明天继续。