MySQL高级知识点 | 八、索引优化和order by优化

最新推荐文章于 2024-02-20 10:22:19 发布

火龙果儿

最新推荐文章于 2024-02-20 10:22:19 发布

阅读量454

点赞数

分类专栏： mysql 文章标签：数据库索引 mysql

本文链接：https://blog.csdn.net/qq_43762027/article/details/104250872

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

索引优化

SQL性能下降的原因

查询语句写的不好
索引失效
关联查询太多
服务器调优及各个参数的的设置（缓冲、线程数等等）

常见的JOIN查询

1、SQL的执行顺序

手写的顺序：

真正机器执行的顺序：

2、七种join查询

最后两种语法mysql不支持，但是我们可以用union来联合其他的查询结果来拼凑出最终结果。

索引

1、什么是索引？

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

也可以简单理解为“排好序的快速查找数据结构”

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此所以往往以索引文件的形式存储在磁盘上。

2、索引的优势和劣势

优势

提高了数据检索的效率，降低了数据库的IO成本
降低了数据排序的成本，降低了CPU的消耗

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大的提高了查询速度，但同时也会降低更新表的速度，因为进行更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

3、索引的分类

单值索引：即一个索引只包含单个列，一个表可以有多个单值索引
唯一索引：索引列的值必须唯一但允许有空值
复合索引：即一个索引包含多个列

4、基本语法

创建

CREATE [UNIQUE] INDEX indexName ON table_name(columnname(length))

ALTER table_name ADD [UNIQUE] INDEX indexName ON (columnname(length))

如果时CHAR、VARCHAR类型，length可以小于字段实际长度，如果是BLOB和TEXT类型，必须指定length
删除

DROP INDEX [indexName] ON table_name
查看

SHOW INDEX FROM table_name

5、哪些情况需要创建索引？

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不合适创建索引
Where条件里用不到的字段不创建索引
在高并发下倾向创建组合索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组的字段

6、哪些情况不需要创建索引？

表记录太少
经常增删改的表
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

ORDER BY 优化

在使用order by时，经常出现Using filesort，因此对于此类sql语句需尽力优化，使其尽量使用Using index。

0.准备

#1.创建test表。

drop table if exists test;
create table test(
    id int primary key auto_increment,
    c1 varchar(10),
    c2 varchar(10),
    c3 varchar(10),
    c4 varchar(10),
    c5 varchar(10)
) ENGINE=INNODB default CHARSET=utf8;

insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

#2.创建索引。

1.根据Case分析order by的使用情况

Case 1：

分析：

①在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。

②使用c1进行排序，出现了Using filesort。

③解决方法：使用覆盖索引。

Case 1.1：

分析：

排序时按照索引的顺序，所以不会出现Using filesort。

Case 1.2：

分析：

出现了Using filesort。原因：排序用的c2，与索引的创建顺序不一致，对比Case1.1可知，排序时少了c1（带头大哥），因此出现Using filesort。

Case 1.3：

分析：

出现了Using filesort。因为排序索引列与索引创建的顺序相反，从而产生了重排，也就出现了Using filesort。

Case 2：

分析：

直接使用c2进行排序，出现Using filesort，因为不是从最左列索引开始排序的（没有带头大哥）。

Case 2.1：

分析：

排序使用了索引顺序（带头大哥在），因此不会出现Using filesort。

Case 2.2：

分析：

虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。

总结：

①MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

②order by满足两种情况会使用Using index。

order by语句使用索引最左前列。
使用where子句与order by子句条件列组合满足索引最左前列。

③尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。

④如果order by的条件不在索引列上，就会产生Using filesort。

#1.filesort有两种排序算法：双路排序和单路排序。

双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。

如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。

单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。

#2.单路排序出现的问题。

当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算。

解决方式：

a.增加sort_buffer_size参数的设置。

b.增大max_length_for_sort_data参数的设置。

⑤提升order by速度的方式：

在使用order by时，不要用select *，只查询所需的字段。

因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作。

尝试提高sort_buffer_size。
尝试提高max_length_for_sort_data。

⑥附上一张从视频中截取出来的总结图。

⑦group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了。