漫谈SQL优化

SQL优化

1. 前言

随着业务量的增长，用户数越来越多，随之产生的数据也呈几何级增长，从而慢查询越来越多。SQL优化一方面是找出系统的瓶颈，提高数据库服务器的整体性能，而另一方面需要合理的结构设计和参数调整，以提数据访问的速度，同时还要尽可能的节约系统资源，以便让系统性能最大化。

本文内容主要针对的是关系型数据库，以MySQL为例子

2. 需要准备的知识点

2.1 数据逻辑架构

• 第一层：客户端通过连接服务，将要执行的SQL指令传输过来
• 第二层：服务器解析并优化SQL，生成最终的执行计划并执行
• 第三层：存储引擎，负责数据的储存和提取

2.2 锁

数据库通过锁机制来解决并发场景-共享锁（读锁）和排他锁（写锁）。读锁是不阻塞的，多个客户端可以在同一时刻读取同一个资源。写锁是排他的，并且会阻塞其他的读锁和写锁。简单提下乐观锁和悲观锁。

• 乐观锁：通常用于数据竞争不激烈的场景，多读少写，通过版本号和时间戳实现。
• 悲观锁：通常用于数据竞争激烈的场景，每次操作都会锁定数据，要锁定数据需要一定的锁策略来配合。
• 表锁：锁定整张表，开销最小，但是会加剧锁竞争。
• 行锁：锁定行级别，开销最大，但是可以最大程度的支持并发。

但是MySQL的存储引擎的真实实现不是简单的行级锁，一般都是实现了多版本并发控制（MVCC）。MVCC是行级锁的变种，多数情况下避免了加锁操作，开销更低。MVCC是通过保存数据的某个时间点快照实现的。

2.3 事务

事务保证一组原子性的操作，要么全部成功，要么全部失败。一旦失败，回滚之前的所有操作。

隔离级别控制了一个事务中的修改，哪些在事务内和事务间是可见的。四种常见的隔离级别：

• 未提交读（Read UnCommitted），事务中的修改，即使没提交对其他事务也是可见的。事务可能读取未提交的数据，造成脏读。
• 提交读（Read Committed），一个事务开始时，只能看见已提交的事务所做的修改。事务未提交之前，所做的修改对其他事务是不可见的。也叫不可重复读，同一个事务多次读取同样记录可能不同。
• 可重复读（RepeatTable Read），同一个事务中多次读取同样的记录结果时结果相同。
• 可串行化（Serializable），最高隔离级别，强制事务串行执行。

2.4 常用存储引擎

• InnoDB引擎： 支持事务、行级锁，被用来设计处理大量短期事务，具有高性能和自动崩溃恢复的特性

• **MyISAM引擎：**不支持事务和行级锁，查询性能强，崩溃后无法安全恢复

3. 优化

3.1 优化方法

• 软优化：操作数据库
• 硬优化：是操作服务器硬件及参数设置

3.2 软优化

3.2.1 查询语句优化

用EXPLAIN命令分析一条查询语句的执行信息

EXPLAIN SELECT * FROM `user`

• select_type，有几种值：simple（表示简单的select，没有union和子查询），primary（有子查询，最外面的select查询就是primary），union（union中的第二个或随后的select查询，不依赖外部查询结果），dependent union（union中的第二个或随后的select查询，依赖外部查询结果）
• type，有几种值：system（表仅有一行（=系统表），这是const连接类型的一个特例），const（常量查询）, ref(非唯一索引访问，只有普通索引)，eq_ref（使用唯一索引或组件查询），all（全表查询），index（根据索引查询全表），range（范围查询）
• possible_keys: 表中可能帮助查询的索引
• key，选择使用的索引
• key_len，使用的索引长度
• rows，扫描的行数，越大越不好
• extra，有几种值：Only index（信息从索引中检索出，比扫描表快），where used（使用where限制），Using filesort （可能在内存或磁盘排序），Using temporary（对查询结果排序时使用临时表）

3.2.2 优化子查询

在MySQL中，尽量使用JOIN来代替子查询。因为子查询需要嵌套查询，嵌套查询时会建立一张临时表，临时表的建立和删除都会有较大的系统开销，而连接查询不会创建临时表，因此效率比嵌套子查询高。

3.2.3 使用索引

使用索引可以显著地提高数据库查询速度，但是未恰当使用索引往往适得其反

如何创建索引:

• 尽量选择区分度高的列创建索引
• 对于查询频率高的列创建索引
• 尽量使用长度短的列作为索引
• 对于长VARCHAR/TEXT/BLOB使用前缀索引

索引未命中:

• 条件中包含OR / NOT IN / NOT EXIST / 不等于
• 模糊匹配字段以%开头
• 未使用联合索引中的第一个字段
• 数值的字符串列未使用单引号
• 在索引列上使用函数

3.2.4 分解表

• 对于字段较多的表，如果某些字段使用频率较低，此时应当，将其分离出来从而形成新的表
• 对应存储长文本如LONGTEXT，应当放到单独的表中进行存储

3.2.5 中间表

对于将大量连接查询的表可以创建中间表，从而减少在查询时造成的连接耗时

• 中间表复制源表部分数据，并且与源表相“隔离”，在中间表上做统计查询不 会对在线应用产生负面影响
• 中间表上可以灵活的添加索引或增加临时用的新字段，从而达到提高统计查询 效率和辅助统计查询作用

3.2.6 增加冗余字段

在设计数据库时，我们往往依照数据库的范式降低数据间的冗余度，但是冗余字段就像是把双刃剑，在业务需求上,有效的增加数据的冗余度，还可以提高数据的访问效率

• 空间换时间
• 业务快照

3.2.7 分析表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费

ANALYZE TABLE user;

• Op: 表示执行的操作
• Msg_type: 信息类型,有status,info,note,warning,error
• Msg_text: 显示信息

3.2.8 检查表

使用 CHECK关键字, 如CHECK TABLE user [option]

CHECK TABLE `user` FAST;

option 只对MyISAM有效, 共五个参数值:

• QUICK: 不扫描行,不检查错误的连接
• FAST: 只检查没有正确关闭的表
• CHANGED: 只检查上次检查后被更改的表和没被正确关闭的表
• MEDIUM: 扫描行,以验证被删除的连接是有效的,也可以计算各行关键字校验和
• EXTENDED: 最全面的的检查,对每行关键字全面查找

3.2.9 优化表

使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

OPTIMIZE LOCAL TABLE `user`;

LOCAL | NO_WRITE_TO_BINLOG都是表示不写入日志，优化表只对VARCHAR, BLOB和TEXT有效, 通过OPTIMIZE TABLE语句可以消除文件碎片, 在执行过程中会加上只读锁

3.3 硬优化

3.3.1 硬件配置

• 配置多核心和频率高的cpu,多核心可以执行多个线程
• 配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度
• 配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力

3.3.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

• key_buffer_size: 索引缓冲区大小
• table_cache: 能同时打开表的个数
• query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
• sort_buffer_size:排序缓冲区

3.3.3 分库分表 + 读写分离

当数据量达到一定的数量之后，限制数据库存储性能的就不再是数据库层面的优化就能够解决的；这个时候往往采用的是读写分离与分库分表同时也会结合缓存一起使用，而这个时候数据库层面的优化只是基础。读写分离适用于较小一些的数据量；分表适用于中等数据量；而分库与分表一般是结合着用，这就适用于大数据量的存储了。分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求，从而减轻服务器压力

3.3.4 缓存集群

缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

4. 结语

要快速定位和解决慢SQL问题需要对数据库结构和运行机制有足够的了解，在设计数据库时就要考虑到数据量级，创建合适的字段类型和索引，设计合理的表结构可以避免慢SQL

本文由 Clive 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: 2020/03/20 13:51