MySQL常见的几种优化方案

转
 2022-09-02
©版权
原文地址：https://blog.csdn.net/qq_22701869/article/details/119651504

select [字段 优化1 ]：主要是覆盖索引

from []

where [条件 优化2 ]

union [联合查询 优化3 ]

新建表格

    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(50) DEFAULT NULL COMMENT '姓名',
      `age` int(11) DEFAULT NULL COMMENT '年龄',
      `phone` varchar(12) DEFAULT NULL,
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加索引，添加索引之后

key_len：根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。

key_len计算方式简单介绍

latin1占用1个字节，gbk占用2个字节，utf8占用3个字节

不允许为空：

varchar(10)：10*3

char(10)：10*3+2

int：4

允许为空：

varchar(10)：10*3+1

char(10)：10*3+2+1

int：4+1

使用完全索引 key_len=name(50*3+2+1=153)+age(4+1)+phone(12*3+2+1=39)

    alter table studen add index name_age_phone(name, age, phone);

添加数据

    insert into student(name,age,phone,create_time) values('赛文',1000,'15717177664',now());
    insert into student(name,age,phone,create_time) values('雷欧',1200,'15733337664',now());
    insert into student(name,age,phone,create_time) values('泰罗',800,'15714447664',now());

一、优化点1：字段优化

覆盖索引尽量用

简单解释解释，索引是哪几个列，就查询哪几个列：

覆盖索引的原因：索引是高效找到行的一个方法，但是一般数据库也能使用 索引 找到一个列的数据，因此它 不必读取整个行 。毕竟索引叶子节点存储了它们索引的数据;

当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引 包含了 (或 覆盖了 )满足查询结果的数据就叫做覆盖索引

注意： 有索引尽量不要使用select *

    #未覆盖索引
    EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
    #覆盖了索引
    EXPLAIN SELECT name,age,phone FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
    #包含了索引
    EXPLAIN SELECT name FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
    #加上主键也还是覆盖索引
    EXPLAIN SELECT id, name,age,phone FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';

未使用覆盖索引

202209022256249841.png

使用完全覆盖索引

202209022256256552.png

使用包含覆盖索引

202209022256263193.png 加上主键还是覆盖索引

202209022256269724.png

二、优化点2：where优化

1.尽量全值匹配

    EXPLAIN SELECT * FROM student WHERE NAME = '赛文';
    EXPLAIN SELECT * FROM student WHERE NAME = '雷欧' AND age = 1200;
    EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' AND age = 800 AND phone = '15714447664';

执行结果，三个都用到了索引，但是key_len是不同的，key_len=197，表示所有索引都使用到了

202209022256275555.png

当建立了索引列后，能在 wherel 条件中使用索引的尽量所用。

2.最佳左前缀法则

最左前缀法则： 指的是查询从索引的最左前列开始并且不跳过索引中的列。

我们定义的索引顺序是 name_age_phone ，所以查询的时候也应该从name开始，然后age，然后phone

情况1 ：从age、phone开始查询，tpye=All，key = null，没使用索引

202209022256283506.png

情况2 ：从phone开始查询，type=All,key=null，未使用索引

202209022256288577.png

情况3 ：从name开始，type=ref，使用了索引

202209022256294998.png

3.范围条件放最后

没有使用范围查询，key_len=197，使用到了name+age+phone组合索引

    EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' AND age = 1000 AND phone = '15717177664';

202209022256300409.png

使用了范围查询，key_len从197变为158，即除了name和age，phone索引失效了

    EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' AND age > 800 AND phone = '15717177664';

key_len=name(153)+age(5)

2022090222563069110.png

4.不在索引列上做任何操作

    EXPLAIN SELECT * FROM student WHERE NAME = '泰罗';
    EXPLAIN SELECT * FROM student WHERE left(NAME,1) = '泰罗';

不做计算,key_len有值，key_len=153，有使用name索引

2022090222563120211.png

做了截取结算，type=All,key_len=null，未使用索引

2022090222563187712.png

5.不等于要甚用

mysql 在使用不等于 (!= 或者 <>) 的时候无法使用索引会导致全表扫描

    #有使用到索引
    EXPLAIN SELECT * FROM student WHERE NAME = '泰罗';
    #不等于查询，未使用到索引
    EXPLAIN SELECT * FROM student WHERE NAME != '泰罗';
    EXPLAIN SELECT * FROM student WHERE NAME <> '泰罗';
    
    #如果定要需要使用不等于,请用覆盖索引
    EXPLAIN SELECT name,age,phone FROM student WHERE NAME != '泰罗';
    EXPLAIN SELECT name,age,phone FROM student WHERE NAME <> '泰罗';

使用不等于查询，跳过索引

2022090222563241313.png

使用不等于查询，同时使用覆盖索引，此时可以使用到索引

2022090222563305214.png

6.Null/Not null有影响

修改为非空

2022090222563383415.png

那么为not null，此时导致索引失效

    EXPLAIN select * from student where name is null;
    EXPLAIN select * from student where name is not null;

2022090222563436416.png

2022090222563492417.png

改为可以为空

2022090222563549318.png

查询为空，索引起作用了

2022090222563600419.png

查询非空索引失效

2022090222563662120.png

解决方法：

使用覆盖索引（覆盖索引解千愁）

2022090222563730421.png

7、Like 查询要当心

like 以通配符开头 ('%abc...')mysql 索引失效会变成全表扫描的操作

    #like 以通配符开头('%abc...')mysql 索引失效会变成全表扫描的操作
    #索引有效
    EXPLAIN select * from student where name ='泰罗';
    #索引失效
    EXPLAIN select * from student where name like '%泰罗%';
    #索引失效
    EXPLAIN select * from student where name like '%泰罗';
    #索引有效
    EXPLAIN select * from student where name like '泰罗%';
    
    解决方式：覆盖索引
    EXPLAIN select name,age,phone from student where name like '%泰罗%';

2022090222563786122.png

2022090222563866923.png

2022090222563929424.png

2022090222563993825.png

使用覆盖索引能够解决

2022090222564071426.png

8.字符类型加引号

字符串不加单引号索引失效（这个看着有点鸡肋了，一般查询字符串都会加上引号）

    #不加引号导致索引失效
    EXPLAIN select * from student where name =11;
    EXPLAIN select * from student where name ='泰罗';

2022090222564135827.png

使用覆盖索引解决

2022090222564191728.png

三、优化3

1.OR 改 UNION 效率高

    未使用索引
    EXPLAIN select * from student where name='泰罗' or name = '雷欧';
    
    使用索引
    EXPLAIN
    select * from student where name='泰罗'
    UNION
    select * from student where name = '雷欧';
    
    解决方式：覆盖索引
    EXPLAIN select name,age from student where name='泰罗' or name = '雷欧';

使用or未使用到索引

2022090222564245229.png

使用union，使用了索引

2022090222564320630.png

解决方式：覆盖索引

2022090222564388331.png