MySQL索引优化

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

索引分类

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

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

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

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

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

查看索引:

show index from 表名;

创建索引:

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

删除索引:

DROP INDEX 索引名 ON 表名;

执行计划

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

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

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,

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

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

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做了范围搜索之后就不在进入索引

select * from staffs where name = '张三' and age >15 and postion = '上海',
  1. 尽量使用覆盖索引:只访问索引的查询,减少“select ”的
    比如只查询name,age,postion三个参数,
    那就不要用select * from~ 而是 select name,age,postion from staffs where~

  2. 不等于要慎用:mysql在使用不等于(!=或<>)的时候无法使用索引,会导致全表扫描;
    如下无法使用索引

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

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

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会导致索引失效

  5. 使用复合索引:如果查询条件中用到了很多查询条件
    如下sql如果表中name,age,postion三个字段分别都建立了普通索引,大概率只走其中一个索引

select name,age,postion from staffs where name = '张三' and age='18' and postion = '上海';

发表评论

邮箱地址不会被公开。 必填项已用*标注