MySQL进阶(一)——索引
视频学习来源:MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷;
作者:木子六日;
MySQL版本:5.7.33;
索引介绍
# 介绍
# 索引:排好序的快速查找数据结构
/*
若无特别指明,通常意义上的索引都是指B树索引
聚集索引、复合索引、前缀索引、唯一索引等默认都是使用B+树索引
还有哈希索引
*/
# 索引的优势
/*
1.加快查找速度;
2.加快排序速度;
*/
# 索引的劣势
/*
1.索引也要占用磁盘空间的;
2.索引的确加快了排序和查询的速度,但是写操作就慢了,因为除了更新表本身外,还得更新索引;
*/
# 索引的分类
/*
1.单值索引:一个索引只包含一个列,一张表可以有多个单值索引(最好不要超过5个);
2.唯一索引:索引列的值唯一,可以为null;
3.复合索引:一个索引包含多个列;
*/
# 索引的使用场景
/*
1.主键自动创建索引;
2.频繁作为查询条件的字段;
3.与其他表关联的字段,外键要建立索引;
4.频繁更新的字段不适合建立索引;
5.where里面用不到的字段别建索引;
6.单键/组合索引的选择问题;
7.需要排序的字段要建立索引;
8.需要分组或统计的字段要建立索引;
*/
索引语法
# 索引的语法
# 创建索引
CREATE INDEX employees_salary ON employees (salary);
ALTER TABLE employees ADD INDEX employees_salary (salary);
# 删除索引
DROP INDEX employees_salary ON employees;
# 查看索引
SHOW INDEX
FROM
employees;
explain
# EXPLAIN
# EXPLAIN能干下面这些事:
/*
1.表的读取顺序;
2.数据读取操作的操作类型;
3.那些索引可以使用;
4.那些索引被实际使用了;
5.表之间的引用;
6.每张表有多少行被优化器查询;
*/
# 使用方法很简单:explain+你写的sql
EXPLAIN SELECT
*
FROM
employees;
# explain的字段分析
/*
id字段:
id相同:
表的加载顺序为统一层级,实际加载顺序为explain结果从上到下加载;
id不同:
id越大越被优先加载;
select_type字段:
simple:简单查询,不含子查询或union;
primary:查询包含子查询时的最外层查询;
subquery:子查询;
derived:临时表的查询(from后面做查询)
union:union之后的select(第二个select);
union result:合并两个union之后结果;
type字段:
性能最好到最差:system->const->eq_ref->ref->range->index->all;
system:单表一行记录,平时基本不会出现,可以忽略;
const:索引一次就找到,出现根据常量值查询主键索引或唯一索引的情况(where id = 1);
eq_ref:查询主键或唯一索引(where a.id = b.id);
ref:查询普通索引(where date = '2021/4/15',date是普通索引[必须是等号]);
range:查询普通索引(where salary BETWEEN 1000 AND 2000,salary是普通索引,<,>,in等也是range);
index:通过索引进行全表扫描,比all快,不过也是全表扫描;
all:不走索引的全表扫描;
possible_keys字段:
可能会用到的索引(不一定真的用到);
key字段:
真实用到的索引;
key_len字段:
表示索引使用到的总长度,如果查询结果相同,那么key_len越小越好;
ref字段:
用于匹配索引的字段,比如where id = 123,那么ref就是const;
rows:
查询到内容所需行数;
extra字段:
额外信息
using filesort:无法利用索引完成的排序(不好);
using temporary:使用了临时表(很不好);
using index:使用了覆盖索引[直接从索引中找到,无需从数据行中查找](很好);
using where:使用了where筛选;
using join buffer:使用了连接缓存;
impossible where:where后面的永为假;
*/
EXPLAIN SELECT
employee_id
FROM
employees
ORDER BY
salary;
SHOW INDEX
FROM
employees;
索引优化
# 索引优化
# 单表优化
EXPLAIN SELECT
id,
author_id
FROM
article
WHERE
category_id = 1
AND comments > 1
ORDER BY
views DESC
LIMIT 1;
# type是all,而且extra里有using filesort,效率不行;
# 先建下索引
CREATE INDEX idx_article_ccv ON article (category_id, comments, views);
SHOW INDEX
FROM
article;
# 再来一次
EXPLAIN SELECT
id,
author_id
FROM
article
WHERE
category_id = 1
AND comments > 1
ORDER BY
views DESC
LIMIT 1;
# type已经变成range了,但是extra里还是有using filesort
# 我们来试一下下面这条语句
EXPLAIN SELECT
id,
author_id
FROM
article
WHERE
category_id = 1
AND comments = 1
ORDER BY
views DESC
LIMIT 1;
# 这时候可以看到using filesort已经没了,为什么?
# 因为category_id和comments已经确定了 views直接走索引就行;
# 而之前comments没有确定,而索引是一个复合索引,得按顺序来走;
# 但是这样写并不能解决我们的需求。
DROP INDEX idx_article_ccv ON article;
SHOW INDEX
FROM
article;
# 我们这样来建索引
CREATE INDEX idx_article_cv ON article (category_id, views);
SHOW INDEX
FROM
article;
# 再来执行一遍
EXPLAIN SELECT
id,
author_id
FROM
article
WHERE
category_id = 1
AND comments > 1
ORDER BY
views DESC
LIMIT 1;
# 这下就大功告成了,type变为ref,也没有文件排序了
# 两表优化
EXPLAIN SELECT
*
FROM
class
LEFT JOIN book ON class.card = book.card;
# 我们是把索引加到主表还是从表呢?
# 直接公布答案:从表,因为主表无论如何都要加载全部
CREATE INDEX idx_book_card ON book (card);
# 再看一次
EXPLAIN SELECT
*
FROM
class
LEFT JOIN book ON class.card = book.card;
索引失效
# 索引失效
SELECT
*
FROM
staffs;
SHOW INDEX
FROM
staffs;
-- 对于这个复合索引,以下三种查询是完美走完索引的
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` = 'July';
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` = 'July'
AND age = 23;
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` = 'July'
AND age = 23
AND pos = 'dev';
# 只要第一个name不在筛选条件内,就是全表扫描
EXPLAIN SELECT
*
FROM
staffs
WHERE
age = 23;
EXPLAIN SELECT
*
FROM
staffs
WHERE
age = 23
AND pos = 'dev';
# 那么下面这个查询呢?
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` = 'July'
AND pos = 'dev';
# 虽然type还是ref,但是我们看到key_len还是74,说明只走了name的那一块索引,pos的筛选并没有走到索引;
# 带头大哥不能死
# 中间索引不能断
# 对索引字段进行计算、使用函数、自动或手动类型转换都会使索引失效
EXPLAIN SELECT
*
FROM
staffs
WHERE
trim(`name`) = 'July';
# 举个类型转化导致索引失效的例子
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` = 2000;
# 尽量使用覆盖索引(查询字段就是索引字段,这样extra中会有using index)
EXPLAIN SELECT
`name`,
age,
pos
FROM
staffs
WHERE
`name` = 'July'
AND age = 23
AND pos = 'dev';
# 不等于操作会直接使得索引失效
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` != 'July';
# is null 或者is not null也会使索引失效
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` IS NULL;
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` IS NOT NULL;
# 使用like时,只有%写在右边的情况索引不失效,其余情况皆失效
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` LIKE 'J%';
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` LIKE '%J';
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` LIKE '%J%';
# 那假设我的业务需求就是要'%%'这样呢?
# 可以通过覆盖索引解决这个问题。
EXPLAIN SELECT
pos
FROM
staffs
WHERE
`name` LIKE '%J%';
# 尽量少用or,因为or也会导致索引失效
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` = 'July'
OR `name` = '2000';