面试中必问的Mysql性能优化

前言:今天主要给大家剖析Mysql性能优化中最重要的优化方式——索引优化

        主要包括索引的基本使用，索引的失效情况分析，复合索引的使用原则等等

1.什么是索引

索引是用来快速查询满足条件的记录，就像我们的楼层标记几楼，我们就能很快的定位到自己想去的楼层，而不需要从一层一层的往上找到自己想要的楼层，小表中我们通常看不出对性能的影响。但是大数据下，将会产生质变。Mysql中索引原理采用B+树的算法。索引记录会保存在数据库文件中

2.几种常用的索引

主键索引 通常我们创建表的主键ID 就是主键索引，因此根据主键ID查询记录往往数据性能较好

唯一索引 在某个表中记录值唯一存在

普通索引 通过create index 创建 

3. 创建索引方式

create INDEX indx_name on table_name(`clo_name`)

indx_name 是索引名称 table_name 是表名 clo_name 是字段名

 

4. 查看索引

show index from table_name;

查询表 table_name 

比如我们在表 my_test 的 name 字段上加上了所以indx_name 采用上面语句查看索引结构如下:

 

5.使用索引

索引的使用必须就先创建索引了，创建的索引也不是随意创建，创建的原则是我们经常来查询的字段，而不是修改的字段，因为索引的创建也会消耗性能，而修改的话也要修改索引库。

下面我们来测试 500万条数据的单表查询

这个是我的表结构

 

数据录入采用存储结构批量生成

我们查询 学生编号为 student_no=28100 的数据记录

没建立索引时的查询情况为:

 

可以看到我们500万条数据查询花费了3秒多，一般正常情况是几十秒到几百秒就算比较高性能的。达到几秒以上就属于long_sql，这样我们必须进行优化处理。在查询较多的字段 student_no 上加索引。

 

看到我们创建索引都花费了 13.8秒 因为索引要写文件。现在我们再次执行上次的查询语句看看效果。

 

只花费了0.003毫秒，性能提升显而易见。

这就是最简单的索引优化方式。

 

6.用Explain 分析sql语句是否使用到了索引。

在查询语句前加上 explain 就可以查看该语句的索引使用情况

比如：EXPLAIN SELECT * from student where student_no=28100;

 

执行后可以查看效果如下:

由此可以看到使用到了索引。

7.索引失效之like模糊匹配

like模糊匹配导致索引失效

在上表情况下，我们建立 学生名的索引 根据学生名来查询学生记录

固定值查询匹配肯定使用到索引，我们来看 "%" 的模糊查

7.1>前置% 导致索引失效

EXPLAIN SELECT * from student where student_name like '%IuZMN';

 

我们可以看到 key 为null 说明没用使用到索引，执行结果也花费了3秒多。

7.2> 后置% 索引有效

EXPLAIN SELECT * from student where student_name like 'IuZ%';

可以看到 type 变成了 range 此时已经不是全表扫描了

 

7.3> 前后都是 % 

如果是 select * 肯定 没用使用到索引 但是 select student_name 此时使用到了覆盖索引 效果如下:

在索引中我们切记勿使用 is null 字段类型和查询不匹配等等，都会导致索引失效，这边我就不带着大家一一测试了。大家可以自行下去测试。

 

8.索引失效之复合索引(复合索引遵循最左原则)

为了避免受到影响我们删除上面创建的两个索引，来创建一个复合索引:

create index idx_name_no on student(student_name,student_no);

SELECT * from student where student_no=5675;

 

我们发现并没有使用到索引 因为 建立索引的顺序 是 student_name,no

此时我们只查询了最右侧的student_no 但是未使用到 student_name

会导致索引失效

我们在根据student_name 查询

 

 

发现我们使用到了索引。

再看sql :

EXPLAIN SELECT * from student where student_name like 'IuZ%' and student_no=1413819;

 

我们发现key_len 的长度变成了66 比单独查询 student_name 多出了4个单位，因此此时两个字段都使用到了索引。由此可知，最左原则顺序 与sql中的字段顺序无关，而是指建立sql索引的顺序。

group by order 等等同样遵循这样的原则，由于篇幅有限，就不一 一举例了。大家可以自行测试。

9.关联查询索引建立原则

假如有a，b两表 sql如下:

我在a表上建立了索引，用explain分析发现索引无效

我在b表上建立索引 create index idx_bid on b(b_id);

 

发现使用到了索引

right连接不再演示，直接给出结论:

左连接右表关联字段必须加索引，右连接左表必须加索引

 

10.关联查询之小表驱动大表

 

sql子查询中 我们尽量遵循子查询的结果数量尽量较少。(面试经典题)Exist 和 in 在使用时

如果in中的查询数据较少 则使用in 否则使用Exist。

11.总结

1. 建立索引的字段类型和查询必须匹配否则索引失效

2. 索引列不要做任何函数操作，否则索引失效

3. 使用 is null is not null 索引失效  != 时候索引失效

4. 以%开头的模糊查询索引失效

5. or会导致索引失效

6. 复合索引遵循最左原则，左侧索引必须使用到

    

关注公众号 微笑码 回复mysql 获取mysql优化视频