MySQL常见优化方案是什么

魁首哥

作者

MySQL常见优化方案是什么

MySQL常见优化方案是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

sql优化的几个地方

select [字段优化1]：主要是覆盖索引
from []
where [条件优化2]
union [联合查询优化3]
新建表格

CREATETABLE`student`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',`name`varchar(50)DEFAULTNULLCOMMENT'姓名',`age`int(11)DEFAULTNULLCOMMENT'年龄',`phone`varchar(12)DEFAULTNULL,`create_time`datetimeDEFAULTNULLCOMMENT'创建时间',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=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)

altertablestudenaddindexname_age_phone(name,age,phone);

添加数据

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

一、优化点1：字段优化

覆盖索引尽量用

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

#未覆盖索引EXPLAINSELECT*FROMstudentWHERENAME='泰罗'andage=1000andphone='15717177664';#覆盖了索引EXPLAINSELECTname,age,phoneFROMstudentWHERENAME='泰罗'andage=1000andphone='15717177664';#包含了索引EXPLAINSELECTnameFROMstudentWHERENAME='泰罗'andage=1000andphone='15717177664';#加上主键也还是覆盖索引EXPLAINSELECTid,name,age,phoneFROMstudentWHERENAME='泰罗'andage=1000andphone='15717177664';

未使用覆盖索引

使用完全覆盖索引

使用包含覆盖索引

加上主键还是覆盖索引

二、优化点2：where优化

1.尽量全值匹配

EXPLAINSELECT*FROMstudentWHERENAME='赛文';EXPLAINSELECT*FROMstudentWHERENAME='雷欧'ANDage=1200;EXPLAINSELECT*FROMstudentWHERENAME='泰罗'ANDage=800ANDphone='15714447664';

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

当建立了索引列后，能在 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组合索引

EXPLAINSELECT*FROMstudentWHERENAME='泰罗'ANDage=1000ANDphone='15717177664';

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

EXPLAINSELECT*FROMstudentWHERENAME='泰罗'ANDage>800ANDphone='15717177664';

key_len=name(153)+age(5)

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

EXPLAINSELECT*FROMstudentWHERENAME='泰罗';EXPLAINSELECT*FROMstudentWHEREleft(NAME,1)='泰罗';

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

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

5.不等于要甚用

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

#有使用到索引EXPLAINSELECT*FROMstudentWHERENAME='泰罗';#不等于查询，未使用到索引EXPLAINSELECT*FROMstudentWHERENAME!='泰罗';EXPLAINSELECT*FROMstudentWHERENAME<>'泰罗';#如果定要需要使用不等于,请用覆盖索引EXPLAINSELECTname,age,phoneFROMstudentWHERENAME!='泰罗';EXPLAINSELECTname,age,phoneFROMstudentWHERENAME<>'泰罗';

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

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

6.Null/Not null有影响

修改为非空

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

EXPLAINselect*fromstudentwherenameisnull;EXPLAINselect*fromstudentwherenameisnotnull;

改为可以为空

查询为空，索引起作用了

查询非空索引失效

解决方法：

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

7、Like 查询要当心 like

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

#like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作#索引有效EXPLAINselect*fromstudentwherename='泰罗';#索引失效EXPLAINselect*fromstudentwherenamelike'%泰罗%';#索引失效EXPLAINselect*fromstudentwherenamelike'%泰罗';#索引有效EXPLAINselect*fromstudentwherenamelike'泰罗%';解决方式：覆盖索引EXPLAINselectname,age,phonefromstudentwherenamelike'%泰罗%';

使用覆盖索引能够解决

8.字符类型加引号

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

使用覆盖索引解决

三、优化3

1.OR 改 UNION 效率高

未使用索引EXPLAINselect*fromstudentwherename='泰罗'orname='雷欧';使用索引EXPLAINselect*fromstudentwherename='泰罗'UNIONselect*fromstudentwherename='雷欧';解决方式：覆盖索引EXPLAINselectname,agefromstudentwherename='泰罗'orname='雷欧';

使用or未使用到索引