MySQL索引优化

发表于 更新于 分类于

索引是数据结构；可以简单理解为书的目录，通过目录找书本章节的内容，顺序结构

索引分类

  • 普通索引：只有一个索引只包含一个列，一个表可以有多个单列索引

  • 唯一索引(Unique)：索引列的值必须唯一，但允许有空值

  • 复合索引：即一个索引包含多个列

  • 聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引（技术上来说就是B+Tree）和数据行。

  • 非聚簇索引：不是聚簇索引就是非聚簇索引

查看索引：

1
show index from 表名;

创建索引：

1
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
1
ALTER TABLE 表名 add UNIQUE 索引名(字段名);

删除索引：

1
DROP INDEX 索引名 ON 表名;

执行计划

执行计划是什么：使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

比如查看一条SQL的执行计划，

1
EXPLAIN SELECT * FROM grade WHERE course = "语文"

会展示如下信息

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE grade ref index_course index_course 69 const 3 Using index condition

id：一个序号值，按顺序从大到依次执行

select_type：选择的类型,可选值有: SIMPLE(简单的),

table：查询的表

type：访问类型,主要包括以下几种：
访问速度如表格显示从上到下排列由慢到快；通过这个属性可以直接判断是否走索引

类型 解释
ALL 全表扫描
index 索引扫描
range 范围扫描
ref 非唯一索引扫描
eq_ref 唯一索引扫描
const 常数引用

possible_keys：可能走的索引

key：SQL实际用到的索引，用到什么索引，key会显示索引名

key_len：表示索引使用的字节数,索引长度，可以衡量索引是否生效，

比如如下SQL会查出key_len为13，

1
explain select * from t1 where col1 = 'ab';

如果如下SQL会查出key_len为26，说明用了两个索引

1
explain select * from t1 where col1 = 'ab' and col2 = 'cd';

ref：表示哪一列用到了索引

rows：扫描行数

Extra：额外的信息说明

优化实战

  1. 全职匹配

  2. 最佳左前缀法则：如果索引是多个，要遵循此原则。指的是查询从索引的最左边开始并且不跳过字段。如果聚合索引是name,age,postion三个字段的聚合索引；那如果通过age和postion查询，那索引会失效，因为name头不在了，无法走索引

  3. 不能再索引列上做任何操作（计算，函数），会跳过索引

  4. 范围条件放最后：存储引擎不能使用索引中范围条件右边的列；
    比如如下SQL只会走name和age两个索引，因为age做了范围搜索之后就不在进入索引

    1
    select * from staffs where name = '张三' and age >15 and postion = '上海'，

  5. 尽量使用覆盖索引：只访问索引的查询，减少“select ”的；
    比如只查询name，age，postion三个参数，
    那就不要用select * from~ 而是 select name,age,postion from staffs where~

  6. 不等于要慎用：mysql在使用不等于（！=或<>）的时候无法使用索引，会导致全表扫描；
    如下无法使用索引

    1
    select * from staffs where name != '张三';

如下使用了上面的覆盖索引原则，这样可以使用索引

1
select name,age,postion from staffs where name != '张三';

  1. Null/Not有影响 : select * from staffs where name is not null; 是否会全表扫描要根据字段来决定；如果查询的字段没有设置为 not null那就不会全表扫描

  2. Like查询要当心：左模糊查询会造成全表扫描，避免使用左模糊查询；但是实际项目无法避免，所以要用到之前的覆盖索引原则

  3. 字符串不加单引号索引失效：如果数据类型是varchar那一定要在查询条件上加上引号；如果不用引号那就会全表扫描

  4. OR改UNION效率高：能用union就不要用or，用or会导致索引失效