SQL设计与优化
基本原理
MYSQL架构
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 >复制代码
1. FROM
2. ON
3. JOIN
4. WHERE <where_condition>
5. GROUP BY
6. HAVING
7. SELECT
8. DISTINCT <select_list
>
9. ORDER BY <order_by_condition>
10. LIMIT <limit_number>
索引
索引是存储引擎用于快速找到记录的一种数据结构，索引优化时对查询性能优化最有效的手段，索引能够轻易将查询性能提高几个数量级，当表数据越来越大，索引对性能愈发重要，当数据量较少且负载较低是，不恰当的索引对性能影响不明显，但当数据量逐渐增大时，性能则急剧下降。
索引作用：
- 随机IO转化为顺序IO
- 减少服务器需要扫描的数据，减少IO
- 减少内存计算（比较、排序、临时表）
索引类型
MYSQL索引简介.xmind(88.23 kB)
聚簇索引
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
复制代码
- 分析查询性能利器
响应时间 2. 扫描行数 3. 返回行数
参考
- 《高性能MYSQL》- 3.创建高性能索引 4.查询性能优化
