美文网首页
步步递进SQL调优的基本操作

步步递进SQL调优的基本操作

作者: 技术架构小白白 | 来源:发表于2021-12-13 16:15 被阅读0次

前言

本文平铺直叙，步步深入，先讲解数据库相关理论知识，再讲到索引，详细介绍explain关键字，最后通过实战教程，讲解sql调优的基本逻辑以及常见的索引失效场景。本文提到的数据库均为Mysql。

1、什么是数据库

作为软件系统开发者，几乎天天都会跟数据库打交道，不知道大家是否有认真思考过，到底什么是数据库。在生活中，保存粮食的仓库我们叫粮库，保存汽油的仓库叫粮库，那么以此类推，保存数据的仓库就叫做仓库。电脑上我们把照片放到同一个文件夹下，那么这个文件夹就是一个照片数据库；把文档资料放到一个文件夹，那么这个文件夹也是一个数据库。

在计算机编程中，数据库的定义和生活中有一定的区别。同样是数据的集合这没有变，但是多了一些条件限定，每一种类型数据集合里面的数据都有固定的内容结构。

  • 数据库中的数据都有一定规律结构，相同类型的数据放在一起，不同类型的数据之间相互隔离
  • 数据库由统一的规则来读写，有SQL语言专门用来读写数据库，一般都是用程序来读写数据库的内容。

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。（摘抄自百度百科）

面试题：如果让你开发一个数据库，该如何进行设计？

2、数据库设计三大范式

2.1、第一范式

数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。
比如用户地址信息，如果仅仅作为属性展示，无相关业务可以仅用一个地址字段进行保存，但如果涉及用户归属省市区查询统计等，则需要再拆分。

2.2、第二范式

确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如订单表（订单号、数量、商品名称、商品介绍、购买人id）把订单和商品详细信息都放在了同一张表中，应该拆分为两个表，商品信息保存在商品表中，订单表只存放订单信息。

2.3、第三范式

表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）。
示例同范式2，如果已经有了订单表和商品表，那订单表设计字段就只需要包含商品id，不需要额外的商品名称、介绍等字段。
范式2与范式3的本质区别：在于有没有分出两张表。范式2是说一张表中包含了多种不同实体的属性，那么必须要分成多张表，比如分成了订单和商品表。范式3是要求已经分好了多张表的话，一张表中只能有另一张表的ID，而不能有其他任何信息（其他任何信息，一律用主键在另一张表中查询）。

必须先满足范式1才能满足范式2，必须同时满足范式1和2才能满足范式3。

3、什么是索引

索引本质是一种可以高效获取数据的数据结构。
数据库维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

4、索引分类

主键索引：也简称主键，它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长，不允许有空值。
普通（单值）索引：即一个索引只包含单个列，一个表可以有多个普通索引。
唯一索引：索引列的值必须唯一，但允许有空值。
组合（复合）索引：即一个索引包含多个列。
全文索引：主要用来查找文本中的关键字。

聚簇索引：也叫聚集索引，它实际上并不是一种单独的索引类型，而是一种数据存储方式，将数据存储与索引放到了一块，找到索引也就找到了数据。一个表仅有一个聚簇索引。聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
非聚簇索引：也叫辅助索引、二级索引，它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。

Innodb的主键是聚簇索引、MyISAM都是非聚簇索引。

5、索引结构

二叉树：每个节点最多有两个子节点，如果是插入有顺序的数据，可能蜕化成链表。
平衡二叉树：基于二叉树容易蜕化成链表的缺点，进行优化，任意节点的左右子树高度差绝对值不超过1 。
红黑树：属于平衡二叉树的一种，引入了红黑色节点。

B树：与二叉树的区别是多叉，又叫多路自平衡查找树。

B+树：B树的升级版，B+树非叶子节点不存储数据，所有数据均保存在叶子节点中，每个叶子节点指向相邻的叶子节点，便于区间查找。

面试题：用InnoDB引擎使用B+树作为Mysql的索引结构有什么好处。
目的是减少磁盘IO次数，加快查询效率，具体体现在下面两点：

  • B+树，非叶子节点不存数据，节点数据小，每次磁盘IO的时候，数据就多，相同区域，B+树有更多的key 。
  • B+数，子节点链表，磁盘读取预读原理，多读数据，可以减少磁盘IO，同时可以快速的进行范围查询。

6、explain关键字

explain信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

6.1、id

SELECT的查询序列号，表示语句的执行顺序。

  • id相同时，执行顺序由上至下。
  • 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
  • id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

6.2、select_type

查询类型，主要用于区别普通查询、联合查询、子查询等复杂的查询

  • SIMPLE。简单SELECT，不使用UNION或子查询等。
  • PRIMARY。当存在子查询时，最外层的select被标记为PRIMARY。
  • UNION。UNION中的第二个或后面的SELECT语句。
  • UNION RESULT。UNION的结果，union语句中第二个select开始后面所有select。
  • SUBQUERY。在SELECT或者WHERE列表中包含了子查询。
  • DERIVED。派生表的SELECT，FROM子句的子查询。

6.3、table

标识SQL操作属于哪张表。显示这一步所访问数据库中表名称，有时不是真实的表名字，可能是别名，也可能是第几步执行的结果简称。

6.4、type

标识对表的访问方式，常用的类型有（从上到下，性能从差到好）：

  • ALL：Full Table Scan， 全表扫描，MySQL将遍历全表以找到匹配的行。
  • index：Full Index Scan，遍历索引，index与ALL区别为index类型只遍历索引树。
  • range：只检索给定范围的行，使用一个索引来选择行。
  • ref：非主键非唯一索引等值扫描。
  • eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。
  • const：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
  • system：system是const类型的特例，当查询的表只有一行的情况下，使用system
  • NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

6.5、possible_keys

表示可能应用在这张表中的索引，一个或者多个，但不一定被查询实际使用。

6.6、key

表示实际使用到的索引，必然包含在possible_keys中。
如果为NULL，则没有建立索引或者索引失效。查询中若使用了覆盖索引（查询的数据列只用从索引中就能够取得，不必再读取数据化，换句话说：查询列要被所建的索引覆盖），则该索引仅出现在key列表。

6.7、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。不损失精确性的情况下，长度越短越好

常用计算说明（utf8编码格式）
varchar(10)且字段允许NULL，key_len = 10*3+2+1
varchar(10) 且字段不允许NULL，key_len = 10*3+2
int字段允许NULL，key_len = 4+1
int字段不允许NULL，key_len = 4
+2表示可变长字段，+1表示可以为空。

6.8、ref

表示哪些列被使用了，如果可能的话，最好是一个常数（const），哪些列或常量被用于查找索引列上的值。


6.9、rows

根据表的统计信息和索引的选用情况，大概估算所需要查询的行数。

6.10、Extra

表示不适合在其他列显示，但是也非常重要的额外信息，挑选几个常见结果说明如下。

  • Using filesort。表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序。
    Mysql无法利用索引完成的排序操作称为文件排序
  • Using tempporary。表示在对MySQL查询结果进行排序时，使用了临时表，这样的查询效率是比外部排序更低的，常见于order by和group by。
  • Using index 表示使用了索引，很好。
    相应的select操作中使用了覆盖索引，避免访问表的数据行，效率不错。
    如果同时出现Useing where，表明索引被用来执行索引键值的查找。
    如果没有同时出现Useing where，表明索引用来读取数据而非执行查找动作。
  • Useing where。表明使用了where过滤条件。
  • Using join buffer。表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
  • impossible where。where筛选条件没能筛选出任何东西，比如恒为false。

需特别注意以下几点：

  • explain不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况。
  • explain不考虑各种Cache
  • explain不能显示MySQL在执行查询时所作的优化工作
  • 部分统计信息是估算的，并非精确值
  • explain只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

7、最左前缀原则（最左匹配原则）

可以对某一列建立索引，还可以对多列建立一个复合索引，对复合索引存在一个最左前缀匹配原则的概念。

  • 对于复合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引。的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。
  • = 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。
  • 如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

8、SQL优化实战

建表语句：
CREATE TABLE `user001` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `idcard` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

插入测试数据：
INSERT INTO `user001`(`name`, `age`, `mobile`, `idcard`) VALUES ('张三', 15, '18200000000', 'idcard1');
INSERT INTO `user001`(`name`, `age`, `mobile`, `idcard`) VALUES ('李四', 27, '15900000000', 'idcard2');
INSERT INTO `user001`(`name`, `age`, `mobile`, `idcard`) VALUES ('王五', 29, '13712345678', 'idcard3');

创建复合索引：
alter table `user001` add index idx_nameAgeMobile(`name`, `age`, `mobile`);

1)、未使用任何的where条件。

explain SELECT * from user001;

2)、使用到where条件，但未使用索引字段。

explain SELECT * from user001 where idcard= 'idcard1';

3)、使用到索引字段。

explain select * from user001 where id = 1;
explain select * from user001 where name = '张三';
explain select * from user001 where id = 1 and name = '张三';
explain select * from user001 where name = '张三' and age = 15;
explain select * from user001 where name = '张三' and mobile = '18200000000';
explain select * from user001 where name = '张三' and idcard = 'idcard1';
explain select * from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select * from user001 where mobile = '18200000000' and age = 15 and name = '张三';
explain select * from user001 where name = '张三' and age > 15;
explain select * from user001 where name = '张三' and age > 15 and mobile = '18200000000';
explain select * from user001 where name = '张三' and age > 15 and idcard = 'idcard1';
explain select * from user001 where name = '张三' and age = 15 and mobile like '18%';

3)、使用到索引字段，但未遵循最左前缀匹配原则。

explain select * from user001 where age = 15;
explain select * from user001 where mobile= '15900000000';
explain select * from user001 where age = 15 and mobile= '15900000000';

4)、使用到索引字段，但在该字段上进行了操作（计算、函数、自动或者手动类型转换）。

explain select * from user001 where name = '张三';
explain select * from user001 where left(name,2) = '张三';
explain select * from user001 where left(name,2) = '张三';
explain select * from user001 where name = '张三' and (age + 1) > 15;
explain select * from user001 where name = 2000;
explain select * from user001 where name = '2000';

5)、尽量使用覆盖索引。

explain select * from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select name,age,mobile from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select name,age,mobile from user001 where name = '张三' and age > 15 and mobile = '18200000000';
explain select name from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select name,mobile from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select age,mobile from user001 where name = '张三' and age = 15 and mobile = '18200000000';

6)、is null、is not null无法使用索引。

explain select * from user001 where name is null;
explain select * from user001 where name is not null;

7)、like以通配符开头（%abc..)会使索引失效。

explain select * from user001 where name like '%张三%';
explain select * from user001 where name like '%张三';
explain select * from user001 where name like '张三%';
explain select * from user001 where name like '张三%' and age = 15;
explain select * from user001 where name like '%张三' and age = 15;
explain select * from user001 where name like '张%三%' and age = 15;

面试题：如何解决like '%xx%'导致索引失效？

使用覆盖索引
explain select name from user001 where name like '%张三%';

explain select id from user001 where name like '%张三%';
explain select id,name from user001 where name like '%张三%';

8)、order by。

explain select * from user001 where name = '张三' and mobile = '18200000000' order by age;
explain select * from user001 where name = '张三' order by age;
explain select * from user001 where name = '张三' order by mobile;
explain select * from user001 where name = '张三' order by idcard;
explain select * from user001 where name = '张三' order by age,mobile;
explain select * from user001 where name = '张三' order by mobile,age;
explain select * from user001 where name = '张三' and age = 15 order by age,mobile;
explain select * from user001 where name = '张三' and age = 15 order by mobile,age;

order by关键字优化（主要是避免文件排序）

  • 尽量使用Index排序，避免使用FileSort方式排序。Mysql支持两种方式排序FileSort和Index，Index效率高，扫描索引本身完成排序。
    以下两种情形会使用Index方式排序：order by语句使用索引最左前列；使用where字子句与order by子句条件列组合满足索引最左前缀原则。
  • 尽可能在索引列上完成排序操作，遵照索引最左前缀原则。

explain select * from user001 where name = '张三' and age > 10 order by age;
explain select * from user001 where name = '张三' and age > 10 order by age,mobile;
explain select * from user001 where name = '张三' and age > 10 order by mobile;
explain select * from user001 where name = '张三' and age > 10 order by mobile,age;

  • 如果不在索引列上，FileSort有两种算法，双路排序、单路排序。
    MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。先根据相应的条件取出相应的排序字段和行指针，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段。
    单路排序是从磁盘中读取查询需要的所有列，按照order by列在sort buffer(排序缓存) 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是会使用更多的空间。因为把每一行的数据都保存在内存中，可能取出数据总大小超过sort buffer容量，导致每次只能取出sort buffer容量大小数据进行排序（创建tmp文件，多路合并），从而多次进行磁盘I/O。
    解决方案：
    避免select *，只写需要的字段。
    增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。

假设复合索引为a_b_c(a , b , c)
1、order by能使用索引最左前缀
order by a
order by a,b
order by a,b,c
order by a desc,b desc,c desc
2、如果where使用索引的最左前缀定义为常量，则order by能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c
3、不能使用索引进行排序
order by a asc,b desc,c desc //排序不一样
where d = const order by b,c //丢失a索引
where a = const order by c //丢失b索引
where a = const order by a,d //d不是索引的一部分
where a = in (...) order b,c //对于排序来说，多个相等条件也是范围查询

9)、group by。分组前基本上都需要先排序，可能会有临时表产生

explain select * from user001 where name = '张三' group by age,mobile;
explain select * from user001 where name = '张三' group by mobile,age;

group by关键字优化

  • group by实质是先排序后进行分组，遵照索引的最左前缀原则，基本同order by。
  • 当无法使用索引列时，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。
  • where优先级高于having，能写在where限定的条件就不要去aving限定了。

10)、查询结果数量过大，占据了大表30%以上

explain select * from health_record where institutionId like '01%';
explain select * from health_record where institutionId like '0101%';

总结：

  • 对于单值索引，尽量选择针对当前query过滤性更好的索引。
  • 在选择复合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择复合索引的时候，尽量选择可以包含当前查询中where条件更多字段的索引。
  • 尽可能通过分许统计信息和调整sql的写法来达到选择适合索引的目的。

9、索引失效场景

  • 没有使用查询条件，或者查询条件并未建立在索引字段上。
  • 被索引字段，发生了类型转换。
  • 被索引字段，使用了运算符或者函数（包含!=、<>）。
  • 使用了左模糊匹配。like %xx
  • 复合索引，未遵守最左前缀原则。
  • 查询结果数量过大，占据了大表30%以上。
  • 查询条件中使用or会使索引失效。
  • is null或者is not null。

面试题：如何解决like '%xx%'导致索引失效？------覆盖索引

10、SQL优化步骤

  • 持续观察，时间略微长一点，最好能把系统使用率高低峰谷都涵盖进去，整体分析下系统的响应情况。
  • 在第1步的基础上，结合数据库服务器磁盘是否过载，内存是否用完进行观察。
  • 开启慢查询日志，设置阙值，比如超过5秒的就是慢sql，并将它抓出来。
  • explain进行慢查询sql分析，比如增加索引、优化原有搜索逻辑等。
  • show profile 查询sql在mysql服务器里面的执行细节和生命周期情况。
  • 数据库服务器的参数调优。

相关文章

  • 步步递进SQL调优的基本操作

    前言 本文平铺直叙，步步深入，先讲解数据库相关理论知识，再讲到索引，详细介绍explain关键字，最后通过实战教程...

  • 数据库调优

    SQL调优 这是最常用、每一个技术人员都应该掌握基本的SQL调优手段（包括方法、工具、辅助系统等）。以MySQL为...

  • 【工作】大纲

    整理工作多年涉猎的技术与积累。 评价体系（递进）： ★ 了解基本原理 。 ★★ 用过，知道基本调优。 ★★★ 有项...

  • 浅谈SQL Server 对于内存的管理

    简介 理解SQL Server对于内存的管理是对于SQL Server问题处理和性能调优的基本，本篇文章讲述SQL...

  • MqSQL - 调优手段

    SQL调优 SQL 调优往往是解决数据库问题的第一步，往往投入少部分精力就能获得较大的收益。SQL 调优主要目的是...

  • Oracle SQL调优系列之SQL Monitor Repor

    @[TOC](Oracle SQL调优系列之SQL Monitor Report) 1、SQL Monitor简介...

  • JVM调优

    1 调优层次 性能调优包含多个层次，比如:架构调优、代码调优、JVM调优、数据库调优、操作系统调优等。架构调优和代...

  • Oracle AWR报告生成和性能分析

    [TOC] 一、AWE报告生成步骤 对于SQL调优，局部SQL，我们可以直接使用执行计划等直接调优，而对于整个系统...

  • sql调优

    explain 查看执行计划，关于执行计划的更多内容，可以参考这篇文章; 另外，如果加上extended关键字，可...

  • SQL调优

    一、SQL调优的依据 - 测试 二、优化性能的三架马车 2.1 DDL设计优化 整体原则是根据系统将要进行的查询来...

网友评论

      本文标题：步步递进SQL调优的基本操作

      本文链接：https://www.haomeiwen.com/subject/mlccfrtx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|步步递进SQL调优的基本操作|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！