sql 深入优化

最新推荐文章于 2020-05-11 18:07:22 发布
最新推荐文章于 2020-05-11 18:07:22 发布
阅读量192 收藏
点赞数
分类专栏： 【MySQL】
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiamaocheng/article/details/104880811
版权
很多时候我们业务系统实现分页功能可能会用如下sql实现
mysql> select * from employees limit 10000,10;
+-------+----------+-------+----------+---------------------+
| id    | name     | age   | position | hire_time           |
+-------+----------+-------+----------+---------------------+
| 17052 | aaa10001 | 10001 | dev      | 2020-03-15 16:23:43 |
| 17053 | aaa10002 | 10002 | dev      | 2020-03-15 16:23:43 |
| 17054 | aaa10003 | 10003 | dev      | 2020-03-15 16:23:43 |
| 17055 | aaa10004 | 10004 | dev      | 2020-03-15 16:23:43 |
| 17056 | aaa10005 | 10005 | dev      | 2020-03-15 16:23:43 |
| 17057 | aaa10006 | 10006 | dev      | 2020-03-15 16:23:43 |
| 17058 | aaa10007 | 10007 | dev      | 2020-03-15 16:23:43 |
| 17059 | aaa10008 | 10008 | dev      | 2020-03-15 16:23:43 |
| 17060 | aaa10009 | 10009 | dev      | 2020-03-15 16:23:43 |
| 17061 | aaa10010 | 10010 | dev      | 2020-03-15 16:23:43 |
+-------+----------+-------+----------+---------------------+
10 rows in set
表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010
条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率
是非常低的。
1、根据自增且连续的主键排序的分页查询
首先来看一个根据自增且连续主键排序的分页查询的例子：
mysql>  select * from employees limit 2000,5;
+------+---------+------+----------+---------------------+
| id   | name    | age  | position | hire_time           |
+------+---------+------+----------+---------------------+
| 9052 | aaa2001 | 2001 | dev      | 2020-03-15 16:17:32 |
| 9053 | aaa2002 | 2002 | dev      | 2020-03-15 16:17:32 |
| 9054 | aaa2003 | 2003 | dev      | 2020-03-15 16:17:32 |
| 9055 | aaa2004 | 2004 | dev      | 2020-03-15 16:17:32 |
| 9056 | aaa2005 | 2005 | dev      | 2020-03-15 16:17:33 |
+------+---------+------+----------+---------------------+
5 rows in set
该 SQL 表示查询从第 2000开始的五行数据，没添加单独 order by，表示通过主键排序 。我们再看表 employees ，因
为主键是自增并且连续的，所以可以改写成按照主键去查询从第 2000开始的五行数据，如下：
mysql>  select * from employees where id > 2000 limit 5;
+------+------+-----+----------+---------------------+
| id   | name | age | position | hire_time           |
+------+------+-----+----------+---------------------+
| 7052 | aaa1 |   1 | dev      | 2020-03-15 16:16:03 |
| 7053 | aaa2 |   2 | dev      | 2020-03-15 16:16:03 |
| 7054 | aaa3 |   3 | dev      | 2020-03-15 16:16:03 |
| 7055 | aaa4 |   4 | dev      | 2020-03-15 16:16:03 |
| 7056 | aaa5 |   5 | dev      | 2020-03-15 16:16:03 |
+------+------+-----+----------+---------------------+
5 rows in set
查询的结果是一致的。我们再对比一下执行计划：
mysql>  expl
ain select * from employees where id > 2000 limit 5;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 7786 |      100 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set
mysql>  explain select * from employees limit 2
000,5;
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15572 |      100 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set
mysql> 
 
显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。
但是，这条 改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图试验
所示（先删除一条前面的记录，然后再测试原 SQL 和优化后的 SQL）：
如果主键不连续，不能使用上面描述的优化方法。
另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满
足以下两个条件：
主键自增且连续
结果是按照主键排序的
 
 
 
 
2、根据非主键字段排序的分页查询
mysql> select * from employees ORDER BY name limit 9000
,5;
+-------+---------+------+----------+---------------------+
| id    | name    | age  | position | hire_time           |
+-------+---------+------+----------+---------------------+
| 11129 | aaa4078 | 4078 | dev      | 2020-03-15 16:19:35 |
| 11130 | aaa4079 | 4079 | dev      | 2020-03-15 16:19:35 |
|  7459 | aaa408  |  408 | dev      | 2020-03-15 16:16:24 |
| 11131 | aaa4080 | 4080 | dev      | 2020-03-15 16:19:35 |
| 11132 | aaa4081 | 4081 | dev      | 2020-03-15 16:19:35 |
+-------+---------+------+----------+---------------------+
5 rows in set
mysql> explain
 select * from employees ORDER BY name limit 9000,5;
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15572 |      100 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set
mysql> 
发现并没有使用 name 字段的索引（key 字段对应的值为 null），具体原因上节课讲过： 扫描整个索引并查找到没索引
的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引 。
 
 
其实关键是 让排序时返回的字段尽可能少 ，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL
改写如下
mysql> select * from employees e inner join (select id from employees order by name limit 9000,5) ed on e.id = ed.id;
+-------+---------+------+----------+---------------------+-------+
| id    | name    | age  | position | hire_time           | id    |
+-------+---------+------+----------+---------------------+-------+
| 11129 | aaa4078 | 4078 | dev      | 2020-03-15 16:19:35 | 11129 |
| 11130 | aaa4079 | 4079 | dev      | 2020-03-15 16:19:35 | 11130 |
|  7459 | aaa408  |  408 | dev      | 2020-03-15 16:16:24 |  7459 |
| 11131 | aaa4080 | 4080 | dev      | 2020-03-15 16:19:35 | 11131 |
| 11132 | aaa4081 | 4081 | dev      | 2020-03-15 16:19:35 | 11132 |
+-------+---------+------+----------+---------------------+-------+
5 rows in set

mysql> explain select * from employees e inner join (select id from employees order by name limit 9000,5) ed on e.id = ed.id;e
+----+-------------+------------+------------+--------+---------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL                  | NULL    | NULL  | 9005 |      100 | NULL        |
|  1 | PRIMARY     | e          | NULL       | eq_ref | PRIMARY       | PRIMARY               | 4       | ed.id |    1 |      100 | NULL        |
|  2 | DERIVED     | employees  | NULL       | index  | NULL          | idx_name_age_position | 140     | NULL  | 9005 |      100 | Using index |
+----+-------------+------------+------------+--------+---------------+-----------------------+---------+-------+------+----------+-------------+
3 rows in set

  原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

 

Join关联查询优化

mysql的表关联常见有两种算法
Nested-Loop Join 算法
Block Nested-Loop Join 算法
1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表（称为 驱动表 ）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（ 被驱动
表 ）里取出满足条件的行，然后取出两张表的结果合集。
mysql> EXPLAIN select*from t1 inner join t2 on t1.a= t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | idx_a         | NULL | NULL    | NULL |   100 |      100 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_a         | NULL | NULL    | NULL | 10180 |      100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set
 
驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优
化器一般会优先选择小表做驱动表。 所以使用 inner join 时，排在前面的表并不一定就是驱动表。
一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算
法是 NLJ。 用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。
 
 
in和exsits优化
原则： 小表驱动大表 ，即小的数据集驱动大的数据集
in： 当B表的数据集小于A表的数据集时，in优于exists
  select * from A where id in ( select id from B )
  # 等价于：
  for ( select id from B ){
  select * from A where A . id = B . id
  }
exists： 当A表的数据集小于B表的数据集时，exists优于in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
  select * from A where exists ( select 1 from B where B . id = A . id )
  # 等价于 :
for ( select * from A ){
  select * from B where B . id = A . id
  }
 
  #A 表与 B 表的 ID 字段应建立索引
 
 
count(*)查询优化
 

mysql> set global query_cache_size=0;
Query OK, 0 rows affected

mysql> set global query_cache_type=0;
Query OK, 0 rows affected

mysql> 
 EXPLAIN select count(1) from employees;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set
mysql> EXPLAIN select count(id) from employees;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set

mysql> EXPLAIN select count(name) from employees;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set

mysql> 
 EXPLAIN select count(*) from employees;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set

四个sql的执行计划一样，说明这四个sql执行效率应该差不多，区别在于根据某个字段count不会统计字段为null值的数
据行
为什么mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高
 
常见优化方法
1、查询mysql自己维护的总行数
对于 myisam存储引擎 的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被
mysql存储在磁盘上，查询不需要计算
对于 innodb存储引擎 的表mysql不会存储表的总记录行数，查询count需要实时计算
 
 
2、show table status
如果只需要知道表总行数的估计值可以用如下sql查询，性能很高

mysql> show table status like 'employees'
    -> ;
+-----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+------------+
| Name      | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment    |
+-----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+------------+
| employees | InnoDB |      10 | Dynamic    | 15572 |            102 |     1589248 |               0 |       540672 |   4194304 |          22630 | 2020-03-14 18:21:24 | 2020-03-15 16:27:23 | NULL       | utf8_general_ci | NULL     |                | 员工记录表 |
+-----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+------------+
1 row in set

mysql> 

3、将总数维护到Redis里
插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难
保证表操作和redis操作的事务一致性
4、增加计数表
插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作
 
 
 
执于代码
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
SQL优化 SQL优化软件 SQL优化工具
12-14
首先，让我们深入理解SQL优化的重要性。SQL（结构化查询语言）是用于与关系型数据库交互的语言。当SQL语句执行效率低下时，可能导致数据库响应时间延长，服务器负载增加，甚至影响整个应用程序的性能。因此，SQL优化...
mysql show table status 的替代方案：获得更多更具体的表信息
cbbbc
10-12 708
mysql提供的show table status like ‘%xxx’可以方便的查看某些表的信息，但是使用这个语句没法对结果集进行排序，不如我想看数据库中那个表中的数据量最大就办不到了？ 其实可以通过访问information_schema数据库获得show table status的相关信息 比如: SELECT * FROM information_schema.tables WHE
MySQL 常用脚本之查看数据库、表结构、约束、索引等信息
Tony.Dong的专栏
05-11 3357
各种 MySQL 常用脚本，查看 MySQL 数据库模式 show databases 以及数据表的结构 show tables，包括字段定义、主键、外键、唯一等约束和索引信息，如何查看表和索引占用的磁盘空间等。
mysql 表字节查看,字节大小,mysql show table status详解
ghostyusheng 's blog
06-22 4365
参考官方文档解释（mysql 8.0）Name表名Engine存储引擎Version这个字段已经没用Row_format行格式 (Fixed, Dynamic, Compressed, Redundant, Compact). 对于MyISAM 表, (Dynamic 指的是 myisamchk -dvv 打包的？）.Rows对于 MYISAM  存的是精确的长度，对于其他引擎，比如InnoDB ...
show tables，数据表显示部分
热门推荐
就是你的博客
02-09 1万+
当用 show tables时，由于数据表过多，导致数据表名称无法显示全部 数据库:test 记录部分显示： select table_name from information_schema.tables where table_schema='test' order by table_name limit 0,30 ; 由于我每次只需要查询某一类数据表： select tabl
SQL 优化
09-11
为了更好地理解 SQL 优化，我们需要深入浅出地理解索引结构。 索引结构是 SQL 优化的基础，索引结构可以分为聚集索引和非聚集索引两种。聚集索引是一种特殊的目录，实际上是字典的正文部分本身是按照一定规则排列的...
基于案例学习SQL优化
10-06
在“第1周”的学习中，可能会首先介绍SQL优化的基础概念，包括SQL的工作原理和性能评估指标，然后逐步深入到具体的优化技术和实践案例。通过这些内容的学习，学员将能够更好地理解和应对实际工作中的SQL性能问题，...
sql优化书籍大全
03-21
本书籍集合了丰富的SQL优化知识，旨在帮助读者深入理解并掌握MySQL SQL优化技巧。 首先，我们要明白SQL优化的基本原则：减少查询次数、减小数据量、合理设计索引以及优化查询语句结构。这四个原则贯穿于整个SQL优化...
oracle-sql优化
最新发布
08-03
综上所述，Oracle SQL优化是一项涉及广泛的技术任务，需要对SQL语句、Oracle数据库结构和优化器有深入理解，通过一系列调整策略和方法，以提升系统性能和响应时间。在整个过程中，开发人员不仅要关注功能实现，还要...
sql.rar_sql的优化
09-24
在数据库管理领域，SQL（Structured Query Language）是用于管理和处理关系型数据库的强大工具。SQL的优化对于提升数据库...通过深入学习和实践这些优化策略，我们可以显著提升SQL查询性能，从而优化整个数据库系统。
SQLServer 优化SQL语句 in 和not in的替代方案
09-11
在SQL Server中，优化SQL语句是提升数据库性能的关键环节。在处理大量数据时，尤其需要注意避免使用效率较低的操作符，如`IN`和`NOT IN`。这些操作符虽然在编写时提供了简洁和易读性，但在执行效率上往往不如其他...
SQL.zip_sql优化
09-23
本文将深入探讨一些SQL语句的常用优化方法，以提升数据库查询的性能。 一、索引优化 1. **创建合适类型的索引**：B树索引、哈希索引、全文索引等，根据查询需求选择合适的索引类型。 2. **避免全表扫描**：合理利用...
SQL性能优化示例
03-09
在IT领域，数据库性能优化是...以上只是SQL性能优化的一部分知识点，实际应用中还需要结合具体环境和业务场景进行深入研究。"SQL Server性能优化及管理艺术"这个压缩包很可能包含更多具体的实例和技巧，值得深入学习。
ORACLESQL性能优化.pptx
04-24
只有通过深入学习 SQL 语法及各种 SQL 调优技巧，熟悉 SQL 语法、掌握各种内嵌函数、分析函数的用法，才能编写高效的 SQL。 SQL 优化是一个系统工程，需要从多方面来考虑，包括应用程序级调优、实例级调优和操作...
MySQL- SHOW TABLE STATUS命令
洛冰翼的专栏
05-17 1284
mysql日志路径: ps aux|grep mysqld /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/app/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/log
mysql show status 详解
weixin_34129696的博客
11-03 98
2019独角兽企业重金招聘Python工程师标准>>> ...
美化mysql SHOW TABLE STATUS的输出
xhiaa的专栏
07-31 1172
新建存储过程   DELIMITER $$ DROP PROCEDURE IF EXISTS `client`.`sp_status` $$ CREATE PROCEDURE `client`.`sp_status`(dbname VARCHAR(50)) BEGIN -- Obtaining tables and views ( SELECT TABLE_
MySQL运行状态show status中文详解
weixin_34353714的博客
06-08 1015
要查看MySQL运行状态，要优化MySQL运行效率都少不了要运行show status查看各种状态，下面是参考官方文档及网上资料整理出来的中文详细解释： 如有问题，欢迎指正 状态名 作用域 详细解释 Aborted_clients ...
深入理解SQL调整与Oracle优化
本文档深入探讨了SQL语句的调整和优化，主要针对ORACLE数据库系统，但也适用于其他如SQL Server等数据库平台。文档分为多个章节，旨在通过逐步讲解，帮助读者理解SQL优化的全过程。 首先，性能调整是一个团队合作的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

执于代码

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值