目录
思考sql优化的几个地方,我把他做了个分类,方便理解
-
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:字段优化
覆盖索引尽量用
-
#未覆盖索引
-
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,表示所有索引都使用到了
2.最佳左前缀法则
情况2:从phone开始查询,type=All,key=null,未使用索引
情况3:从name开始,type=ref,使用了索引
3.范围条件放最后
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.不等于要甚用
-
#有使用到索引
-
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 索引失效会变成全表扫描的操作
-
#索引有效
-
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 =
'雷欧';
使用union,使用了索引