注：原始资料来自享学课堂，自己加上整理和思考
目录
思考sql优化的几个地方，我把他做了个分类，方便理解
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';
未使用覆盖索引
使用完全覆盖索引
使用包含覆盖索引
加上主键还是覆盖索引
二、优化点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，表示所有索引都使用到了
当建立了索引列后，能在
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有影响
修改为非空
那么为not null，此时导致索引失效
-
EXPLAIN
select *
from student
where
name
is
null;
-
EXPLAIN
select *
from student
where
name
is
not
null;
改为可以为空
查询为空，索引起作用了
查询非空索引失效
解决方法：
使用覆盖索引（覆盖索引解千愁）
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 =
11;
-
EXPLAIN
select *
from student
where
name =
'泰罗';
使用覆盖索引解决
三、优化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未使用到索引
使用union，使用了索引
解决方式：覆盖索引
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
