SQL设计与优化-CSDN博客

SQL设计与优化

基本原理

MYSQL架构

整个MYSQL架构分为两层，上层是MTSQLD被称为 SQL Layer，下层是各种各样的对上提供接口的存储引擎，被称为 Storage Engine Layer

SQL Layer

TODO

Storage Engine Layer

主要引擎

引擎	介绍	使用场景
InnoDB	采用MVVC支持高并发，实现四个标准隔离级别，默认是REPEATABLE READ，表基于聚簇索引建立，支持行级锁	5.5+版本默认存储引擎，事务型存储引擎。能够符合MYSQL大多数使用场景
MyISAM	不支持事务和行级锁，崩溃后无法修复，	5.5以下版本默认引擎
Memory	支持hash索引，标记所，并发写入性能低，内存表，重启丢失	用于查找映射表缓存周期性聚合数据结果保存数据分析产生中间数据

SQL执行流程

以查询语句为例

客户端发送一条查询给服务器
服务器先检查缓存，如果命中缓存，检查是否有权限，有权限返回存在缓存中的结果，否则进入下一个阶段
服务器执行SQL解析，预处理，再由优化器生成对应的执行计划
MYSQL根据优化器生成的执行计划，调用存储引擎的API执行查询
将结果返回给客户端

SQL解析顺序

SELECTDISTINCT< select_list >FROM< left_table >< join_type >JOIN< right_table >ON< join_condition >WHERE< where_condition >GROUPBY< group_by_list >HAVING< having_condition >ORDERBY< order_by_condition >LIMIT< limit_number >复制代码

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list >
ORDER BY <order_by_condition>
LIMIT <limit_number>

索引

索引是存储引擎用于快速找到记录的一种数据结构，索引优化时对查询性能优化最有效的手段，索引能够轻易将查询性能提高几个数量级，当表数据越来越大，索引对性能愈发重要，当数据量较少且负载较低是，不恰当的索引对性能影响不明显，但当数据量逐渐增大时，性能则急剧下降。

索引作用：

随机IO转化为顺序IO
减少服务器需要扫描的数据，减少IO
减少内存计算（比较、排序、临时表）

索引类型

MYSQL索引简介.xmind(88.23 kB)

聚簇索引

聚簇索引是一种数据存储方式，InnoDB聚簇索引实际上在同一个结构保存了 B-Tree索引和数据行，当表有聚簇索引时，它的数据实际存储在索引的叶子页上，InnoDB是将通过主键聚簇数据（主键列），如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引。

优点：

数据访问更快，聚簇索引和数据保存在B+Tree中，因此查找更快
覆盖索引扫描直接使用页节点中的主键值
相邻键值数据存放在一起
提高I/O密集型应用的性能，假设将数据全部放内存上，则访问顺序没有那么重要，聚簇索引就没有优势

缺点：

插入数据严重依赖插入顺序,如果不是按照主键顺序插入顺序，加载完成后使用OPTIMIZE TABLE重新组织下表
更新聚簇列的代价很高，导致"页分裂"
更新插入都可能导致"页分裂"
可能导致全表扫描变慢，行稀疏，页分裂导致存储不连续的时候
二级索引会比较大
二级索引访问需要两次索引查找

聚簇索引和非聚簇索引对比

MYISAM按照数据插入顺序存储在磁盘上，内部维护一个行号，叶子节点=索引列值+ 行（内部行号+数据）

InnoDB 主键索引（聚簇索引）每一个叶子节点都包含主键值，事务ID，用于事务和MVCC的回滚指针和其他剩余列

二级索引叶子节点存储的是主键值

设计规范

优化法则

SQL优化实践关键点在于减少磁盘I/O,减少网络传输，降低CPU和内存开销和加机器

表结构设计

建议：

更小通让更好，够用就好
适当冗余字段，不要使用多表join查询
选择合适的数据类型
使用信息统计表

索引设计

规则：

查询条件不包含索引最左列，无法使用索引
对于范围查询，只能利用索引最左列
对于order by A语句，在A上建立索引，可以避免排序
对于group by A 语句，在A上建立索引，可以避免排序
对于多列排序，需要所有列所有列顺序方向一致，才能利用索引

建议：

选择过滤性高的字段建立索引 distinct(col)/count(*)
JOIN 查询中连接字段建立索引避免全表扫描
尽量使用覆盖索引，无需访问表，避免随机IO
利用前缀索引 name varchar(128)，index(name(16))

注意：MYSQL我发使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖查询，有排序、分组场景不适合使用前缀索引

避免建重复索引，提高索引使用率 (a),(a,b),(a,b,c)

SQL写法

建议：

建议读写都采用主键索引
尽量利用索引排序，避免产生临时表 order by col1,col2 index(col1,col2)
避免对查询字段进行（类型转换，计算） WHERE id*2》4 --》 id>2
避免使用select * ，指明使用列 select a,b,c
避免使用全木湖查询 LIKE '%xxx%' 避免前缀 LIKE '%xxx' 不使用索引，LIKE 'xxx%'可以使用索引
多SQL综合考虑，保证核心SQL

实践

高效分页

LIMIT 分页在MYSQL中实际上存储引擎层查出所有数据，然后在MYSQL服务端层进行切割丢弃不需要部分返回切割后结果，分页过深存在性能问题。解决方式：利用InnoDB聚簇索引（索引+数据存放在索引文件上）的特性加速分页

select col1,col2,col3 from(select id from table_a
            <where_condition>orderby id limit300000,10) a
leftjoin table_a b on a.id=b.id
复制代码

分析查询性能利器

可以通过执行计划分析SQL的执行情况，主要关注点有三个重要指标 1. 响应时间 2. 扫描行数 3. 返回行数

参考

《高性能MYSQL》- 3.创建高性能索引 4.查询性能优化