SQL查询优化总结

中国梅西呼和浩特分西
164 阅读13分钟

接上文，了解完SQL的基本函数之后，接下来想分享一下自己日常总结出来的Sql优化查询总结。想让查询高效执行，个人认为有两点，首先，合理理解需求，使用合适的逻辑和函数，高效执行查询语句。然后第二点就是在大数据量的情况下，合理使用索引，优化查询逻辑，利用索引进行高效查询。今天就要给大家分享一下索引的原理，查询中如何合理使用索引等

一、索引

1.1:索引的分类

MySQL索引可以按照不同的分类方式进行分类，以下是几种常见的分类方法：

  1. 根据索引的数据结构分类：

    • B-Tree索引（常用于存储引擎MyISAM和InnoDB）
    • Hash索引（仅适用于Memory存储引擎
    • Full-text索引（用于全文搜索）

  2. 根据索引的字段数分类：

    • 单列索引（单个字段上的索引）
    • 组合索引（多个字段的索引）

  3. 根据索引的功能分类：

    • 主键索引（唯一标识表中的每行数据，一个表只能有一个主键）
    • 外键索引（用于保证数据的完整性和一致性，一个表中的字段是另一个表的主键）
    • 普通索引（用于加速查询）
    • 唯一索引（确保索引列的值是唯一的，可以包含NULL值）
    • 全文索引（用于全文搜索，只有MyISAM存储引擎支持）

1.2:索引的创建方式

-- 创建普通索引 
CREATE INDEX index_name ON table_name(column_name);

-- 创建唯一索引 
CREATE UNIQUE INDEX index_name ON table_name(column_name);

-- 创建主键索引 
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 创建组合索引 
CREATE INDEX index_name ON table_name(column1, column2, ...);

-- 创建全文索引 CREATE FULLTEXT INDEX index_name ON table_name(column_name);

1.3:索引的数据结构

1.3.1 B-Tree

B树(多路搜索树,并不是二叉的)是一种常见的数据结构,使用B-tree结构可以显著的显著的减少定位记录时所经历的中间过程,从而加快存取速度

特性

  • 每个节点最多有m个孩子(m代表阶数)
  • 除了根节点和叶子节点歪,其他每个节点至少有Ceil(m/2)个孩子
  • 如果根节点不是叶子节点,则至少有两个孩子
  • 所有叶子节点都在同一层,也不包含其他关键字信息
  • 非叶子节点的关键字个数,ki(i=1,…n)为关键字,且关键字升序排序(k[i]<k[i+1])
  • 非叶子节点的关键字个数 = 指向儿子的指针个数-1
  • 非叶子节点的指针:P[1],P[2],…P[n];其中P[1]只想关键字小于K[1]的子树,P[M]指向关键字大于K[M-1]
  • 的子树,其它的P[i]指向关键字属于(K[i-1],K[i])的子树

image.png 从上图结构中可以看出在B-tree中每个节点储存了键值和数据两部分,同属在连接左右节点时通过指针进行连接

并且可以看到在B-Tree中每个节点到叶子结点的高度都是相同的,因为对于B树来说查询效率是稳定的

每个节点就是对应的键值,这样在获取时,只要找到键值就可以获取到对应的数据,不需要再进行定位数据的位置,加快了速度

查询数据时先通过键值定位到所在的磁盘块,在磁盘快中查询对应的数据

B树的搜索,从根节点开始,对节点内的关键字(有序)序列进行二分查找如果命中则结束,否则进入查询关键字所属范围的儿子节点;重复,直到所对应的儿子指针为空,或者已经是叶子节点

image.png

创建过程

image.png

三阶B树

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

以上图示为，B树创建不同的阶流程

1.3.2:B+Tree

B+树是在B树的基础上的一种优化,是一种更适合储存的数据结构

image.png 特性:

  • 非叶子节点只存储自己的关键字信息

  • 数据记录都存放在叶子节点中

  • 所有叶子节点之间有一个链指针

创建过程:

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

三阶B+树创建流程如上图，分步解释如下

image.png

此处为叶子节点,只是储存key,不储存value

image.png

此处为叶子节点,不仅储存key还储存value,数据保存的地址(和B树的区别)

这就是为什么索引排序

B+树是B树的一种变形,两者差异在于

1.有n棵子树的节点中含有n个关键字,每个关键字不保存数据,只用来索引,所有数据都保存在叶子节点

2.所有叶子节点中包含了全部的关键信息以及指向含这些关键字信息的指针,且叶子节点本身因关键字的大小自小而大顺序连接

3.素有非终端节点可以看成索引部分,节点中仅含其子树(根节点)中最大或最小的关键字

4.通常在B+树上有两个头指针,一个指向根节点,一个指向关键字最小的叶子节点

5.非叶子节点的字数指针与关键子个数相同

6.非叶子节点的字数指针P[i],指向关键字属于([K[i],K[i+1]])的子树(B树是开区间)

7.为所有叶子节点增加了一个链指针

8.所有关键字都在叶子节点出现

B+树是一个n叉树,每个节点通常有多个孩子,一颗B+树包含根节点,内部节点和叶子节点.根节点可能是一个叶子节点,也可能是一个包含两个或两个以上孩子结点的节点

B+ 树通常用于数据库和操作系统的文件系统中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。

二、查询优化

查询优化，在结合索引数据结构理解，其实我认为就是一句话，最佳左前缀原则

1.对查询进行优化，尽量避免全表扫描，首先应考虑在where以及order by设计的列上建立索引

2.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引，而进行全表扫描

select id from t where num is null 
##可以在num上设置默认值0，确保表中num列没有null值
select id from t where num = 0

3.应尽量避免在where子句中使用!= 或<>操作符，否则将引擎放弃使用索引而进行全表扫描 4.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num = 10 or num = 20
##可以使用拼接查询
select id from t where num = 10 
union all 
select id from t where num = 20

5.in和not in也要慎用，否则会导致全表扫描

select id from t where num in (1,2,3);
##对于连续的数值，能用between就不要用in了
select id from t where num between 1 and 3;

select num from a where nun in (select num from b)
##
select num from a where exists (select l from b where num = a.num)

6.下面的查询也会导致全表扫描，左侧确定，右侧模糊

select id from t where name like '%abc%' ##全表 不符合最佳左前缀
select id from t where name like 'abc%' ##使用索引

7.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

select id from t where num / 2 = 100;
##
select id from t where num = 100 * 2;

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

select id from t where substring (name,1,3) = 'abc'  ##name以abc开头的id
## 使用索引
select id from t where name like 'abc%'

9.不要在where子句中的“=“左边进行函数，算术运算或者其他表达式运算，否则系统将可能无法正确使用索引

 -- where 函数操作 = 值

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序保持一致。

查询中如果想使用索引必须where后面第一个字段为索引内容

11.不要写一些没有意义的查询，如需要生成一个空表结构

select  coll,col2 into #t from where 1 = 0
##
create table #t()

12.并不是所有索引都对查询有效，SQL是根据表中数据来进行优化的，当索引列有大量的数据重复时，SQL查询可能不会去利用索引。如一表中有字段sex，male，female几乎各一半，那么即使在sex上建立了索引，也对查询起不了作用

13.索引并不是越多越好，索引固然可以提高相应的select的效率，但是同时也降低了insert以及update的效率。因为insert或update时可能会重建索引，所以怎样建索引也需要慎重考虑，根据业务需求以及表结构进行判断

14.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

15.尽可能使用varchar代替char，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高一些

16.任何地方都不要使用select * from t，用具体的字段列代替“*”，不要返回用不到的任何字段

这个问题可能开始的时候大家会遇到，等稍微有经验之后，这个这个就不算问题了

17.避免频繁的创建和删除临时表，以减少系统表资源的消耗

临时表并不是不可使用，适当的使用它们可以使某些查询更有效

例：当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表

18.在新建临时表时，如果一次性插入数据量很大，那么可以使用select into 代替 create table，避免造成大量的log，以提高速度。如果数据量不大，为了缓和系统表的资源，应先create table，然后insert

19.如果使用到了临时表，在存储过程的最后，务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定

‌DROP、TRUNCATE和DELETE的主要区别在于它们对数据的处理方式、执行速度、是否可以回滚以及是否触发触发器等方面。 ‌

1.  ‌执行速度：

    -   ‌TRUNCATE：比DELETE快，因为它直接删除整个表的数据，而不是一行一行地删除。TRUNCATE操作不会记录每一行的删除操作，因此执行效率高‌。
    -   ‌DELETE：逐行删除数据，并将删除操作记录在日志中，以便进行回滚操作。DELETE操作可以触发触发器‌
    -   ‌DROP：速度最快，因为它不仅删除数据，还会删除表的结构，包括索引等‌

1.  ‌是否可以回滚：

    -   ‌TRUNCATE和‌DROP：一旦执行，无法回滚，数据无法恢复‌
    -   ‌DELETE：可以在事务中回滚，如果操作错误，可以恢复数据‌

2.  ‌是否触发触发器：

    -   ‌DELETE：会触发触发器‌
    -   ‌TRUNCATE和‌DROP：不会触发触发器‌

3.  ‌是否保留表结构：

    -   ‌TRUNCATE和‌DELETE：只删除数据，保留表的结构（定义）
    -   ‌DROP：删除表的结构和数据‌

4.  ‌表和索引空间：

    -   ‌TRUNCATE：删除数据后，表和索引占用的空间会恢复到初始大小‌
    -   ‌DELETE：不会减少表或索引占用的空间
    -   ‌DROP：释放表所占用的所有空间‌

20.应尽量避免大事务操作，提高系统并发能力

这个需要根据业务需求自行决定，根据个人经验，大事务容易出现BUG，并且排查问题比较麻烦。合理安排事务大小，才是王道

21.尽量避免向客户端返回大数据，若数据量过大，应该考虑响应需求是否合理

比如列表数据，设置分页。如果数据量太大，前后端交互，前端渲染都比较麻烦

三、索引命中查询

EXPLAIN select * from table

虽然刚说了不要用*，但是马上破戒了😂。执行结果

image.png

  • select_type : 查询类型，常见的值

    • SIMPLE：简单表，不使用表连接或子查询。

    • PRIMARY : 主查询，外层的查询。

    • UNION 第二个或者后面的查询语句。

    • SUBQUERY : 子查询中的第一个select

  • table ：输出结果的表

  • type : 表示MySql在表中找到所需行的方式，或者叫访问类型。常见的类型：

ALL 、 index 、 range 、 ref 、 eq_ref 、 const,system 、 NULL
从左到右，性能由最差到最好。

type＝ALL　全表扫描

type＝index　索引全扫描，遍历整个索引来查询匹配的行

type＝range　索引范围扫描，常见于　,>=,between,in等操作符

type＝ref　使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行。ref还经常出现在JOIN操作中

type＝eq_ref　类似于ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中有一条记录匹配；简单来说，说是多表连接中使用　主建或唯一健作为关联条件

type＝const/system　单表中最多有一个匹配行。主要用于比较primary key [主键索引]或者unique[唯一]索引,因为数据都是唯一的，所以性能最优。条件使用=。

type＝NULL　不用访问表或者索引，直接就能够得到结果
  • possible_keys : 可能使用的索引列表
  • key : 实现执行使用索引列表
  • key_len : 索引的长度
  • ref : 显示使用哪个列或常数与key一起从表中选择行。
  • row : 执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引
  • Extra: 该列包含MySQL解决查询的详细信息9.1 Not exists

range checked for each record 没有找到合适的索引

校验索引命中情况，可以使用以上查询

结束语：

本章主要内容就是SQL查询的优化总结，借鉴了不少其他大佬的图片，内容，根据自己的经验整理出来的经验。我个人学习过程主要是从B-Tree和B+-Tree的创建过程图片可以对比，然后理解最佳左前缀原则，这样使用索引也会让自己得心应手一些。

最后还是那句话，如果发现有问题，欢迎各位大佬批评和指正😀

avatar
文章
阅读
粉丝