一、SQL

1、创建表

CREATE TABLE IF NOT EXISTS  staffs(
id  INT PRIMARY KEY AUTO_INCREMENT,
`name`  VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age`   INT NOT NULL DEFAULT 0 COMMENT '年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) charset utf8 COMMENT '员工记录表'

2、添加数据

INSERT INTO staffs(NAME, age, pos, add_time) VALUES('z3', 22, 'manager', NOW())
INSERT INTO staffs(NAME, age, pos, add_time) VALUES('July', 23, 'dev', NOW())
INSERT INTO staffs(NAME, age, pos, add_time) VALUES('2000', 24, 'dev', NOW())

3、创建索引

ALTER TABLE staffs ADD INDEX idx_staffs_name_age_pos(`name`, age, pos)

复合索引.png

二、全值匹配

2.1、全值匹配 — 1个

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = 'July'

全值匹配 — 1个.png

• 这个索引效果很好

2.2、全值匹配 — 2个

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = 'July'
AND
age = 25

全值匹配 — 2个.png

2.3、全值匹配 — 3个

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = 'July'
AND
age = 25
AND
pos = 'dev'

全值匹配 — 3个.png

三、复合索引顺序从左至右

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

3.1、跳过索引第一个（name）

EXPLAIN
SELECT
*
FROM
staffs
WHERE
-- `name` = 'July'
-- AND
age = 25
AND
pos = 'dev'

跳过索引第一个.png

EXPLAIN
SELECT
*
FROM
staffs
WHERE
pos = 'dev'

image.png

• 没有索引命中

3.2、跳过索引第2个（age）造成索引中断

EXPLAIN
SELECT
*
FROM
staffs
WHERE
pos = 'dev'
AND
`name` = 'July'

跳过索引第2个（age）.png

• 有一个索引命中

四、不在索引列上做任何操作

不在索引列上做任何操作计算、函数、自动或手动类型转换，会导致索引失效而转向全表扫描

4.1、自动转化导致索引失效

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = '2000'
AND
age = 24
AND
pos = 'dev'

索引正常.png

字符串必须要加 ' '，2000：数值类型—>字符串 造成索引失效

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = 2000
AND
age = 24
AND
pos = 'dev'

自动转化导致索引失效.png

五、存储引擎不能使用索引中范围条件右边的列

5.1、使用name索引

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = 'Raven'

name索引.png

5.2、使用name和age索引

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = 'Raven'
AND
age = 23

name和age索引.png

5.3、全索引

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = 'Raven'
AND
age = 23
AND
pos = 'manager'

全索引.png

5.4、索引链中断

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = 'Raven'
AND
age > 11
AND
pos = 'manager'

image.png

六、尽量使用覆盖索引

只访问索引的查询（索引列和查询列一致），减少select *

EXPLAIN
SELECT
*
FROM
staffs
WHERE
`name` = 'Raven'
AND
age = 25
AND
pos = 'manager'

select *.png

EXPLAIN
SELECT
`name`, age, pos
FROM
staffs
WHERE
`name` = 'Raven'
AND
age = 25
AND
pos = 'manager'

image.png

七、MySQL在使用不等于（!=）或者 < 或者 > 的时候无法使用索引会导致全表扫描

7.1、使用 =

EXPLAIN
SELECT
`name`, age, pos
FROM
staffs
WHERE
`name` = 'Raven'

使用 = .png

7.2、使用不等号（!=）

EXPLAIN
SELECT
`name`, age, pos
FROM
staffs
WHERE
`name` != 'Raven'

!=.png

八、is null is not null 无法使用索引

8.1、is null

EXPLAIN
SELECT
*
FROM
staffs
WHERE
name IS NULL

IS NULL.png

8.2、is not null

EXPLAIN
SELECT
*
FROM
staffs
WHERE
name IS NOT NULL

is not null.png

九、like 以通配符开头（%abc）会使索引失效会变成全表扫描

9.1、左右都有%

EXPLAIN
SELECT
*
FROM
staffs
WHERE
name LIKE '%ju%'

左右都有%.png

9.2、左侧有%右侧%

EXPLAIN
SELECT
*
FROM
staffs
WHERE
name LIKE '%ju'

左侧有%右侧%.png

9.3、只有右侧有%

EXPLAIN
SELECT
*
FROM
staffs
WHERE
name LIKE 'ju%'

只有右侧有%.png

十、字符串不加单引号索引失效

EXPLAIN
SELECT
*
FROM
staffs
WHERE
name = '2000'

image.png

• 字符串不加单引号

EXPLAIN
SELECT
*
FROM
staffs
WHERE
name = 2000

字符串不加单引号.png

十一、使用 or 连接时，索引失效

EXPLAIN
SELECT
*
FROM
staffs
WHERE
name = '2000' OR name = 'July'

image.png

小结

小结.png