MySQL常见的几种优化方案

Mysql 常见优化分类：

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 计算简单介绍

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

      1. 不允许为空：

　　　　　　  varchar(10)：10*3

　　　　　　  char(10)：10*3+2

　　　　　　  int：4

　　        2.允许为空：

 　　　　　　  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());

 

一、字段优化
覆盖索引尽量用，简单解释解释，索引是哪几个列，就查询哪几个列；
覆盖索引的原因：索引是高效找到行的一个方法，但是一般数据库也能使用 索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;
当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引
注意：有索引尽量不要使用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';

二、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';

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

2. 最左匹配原则
   

   最左前缀法则：指的是查询从索引的最左前列开始并且不跳过索引中的列。
   我们定义的索引顺序是 name_age_phone ，所以查询的时候也应该从name开始，然后age，然后phone。
   情况1：从age、phone开始查询，tpye=All，key = null，没使用索引
   情况2：从phone开始查询，type=All,key=null，未使用索引
   情况3：从name开始，type=ref，使用了索引

3. 范围条件放最后
   没有使用范围查询，key_len=197，使用到了name+age+phone组合索引
   

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

   使用了范围查询，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)

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

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

   不做计算,key_len有值，key_len=153，有使用name索引
   做了截取结算，type=All,key_len=null，未使用索引

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 <> '泰罗';

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

6. null 和 not null 的影响
   第一种：name 字段修改为非空，此时导致索引失效，查询为空或非空索引都会失效哦🤔
   

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

   第二种：name 字段修改为空，查询为空，索引起作用了；查询非空索引失效
   解决方案：使用覆盖索引 （select name , age ,phone）

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 '%泰罗%';

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

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

   解决方案：使用覆盖索引哦

9. 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 = '雷欧';