SQL的生命周期
- [客户端]->[服务端]建立session
- [客户端]发送sql
- [服务端]解析并生成执行计划
- [服务端]执行执行计划
- [服务端]读取数据到内存并进行逻辑处理
- [服务端]通过session发送结果
- 关闭连接
超大分页方案
sql层面
select * from table where id in (select id from table where age > 20 limit 1000000,10)
因索引覆盖虽load了1000000数据但查询速度与数据量可接受
需求层面
规避类似需求
只允许逐页查看或者按照给定的路线走,这样可预测,可缓存以及防止ID泄漏且连续被人恶意攻击.
慢查询日志
开启
show variables like ‘slov_query_log’
set GLOBAL slow_query_log = on
生成datadir/xxx-slow.log临界时间
show VARIABLES like 'long_query_time' //单位秒
set long_query_time=0.5
InnoDB主键设计
- 因聚簇索引特性，必须指定主键
如果没有指定唯一键，会生成一个隐式的主键 - ID最好趋势增长
- 不推荐UUID及其他类似无规律ID
大量的索引数据插入，数据移动，导致产生很多的内存碎片，进而造成插入性能的下降
优化查询语句的数据访问
- 明确指定返回的数据列
- 使用limit规避 查询不需要的数据行
- 多表关联指定列名
- 应用内缓存数据规避重复查询相同的数据
- 存在扫描额外的记录
使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的
行，可以使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。 - 改变数据库和表的结构，修改数据表范式
- 重写SQL语句，让优化器可以以更优的方式执行查询。
优化复杂的查询语句
- 一个复杂查询可以尝试分解为多个简单查询
- 分解关联查询，让缓存的效率更高。
- 执行单个查询可以减少锁的竞争。
- 在应用层做关联更容易对数据库进行拆分。
- 较少冗余记录的查询。
优化特定类型的查询语句
- 统计行数使用count(*)更佳
- 增加汇总表
- 使用缓存
- 可以考虑使用explain查询近似值，用近似值替代count(*)
- UNION ALL的效率高于UNION
优化关联查询及子查询
- 确定ON或者USING子句中是否有索引。
- 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索 引。
- 关联查询中使用标识列分组的效率更高
- 用关联查询替代子查询
- 子查询如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。
优化WHERE子句避免全表扫描
- 首先应考虑在 where 及 order by 涉 及的列上建立索引。
- 避免在 where 子句中对字段进行 null 值判断
- 避免在 where 子句中使用!=或<>操作符或not in
- 模糊查询使用左前缀匹配
- 避免在 where 子句中对字段进行表达式或函数操作
select id from t where num/2=100‐‐应改为:select id from t where num=100*2
select id from t where substring(name,1,3)=’abc’‐‐name以abc开头的id应改为: select id from t where name like ‘abc%’
数据库优化目标
减少系统瓶颈，减少资源占用，增加系统的反应速度。
数据库结构优化
- 大表拆小表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形 成新表。 - 增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。
将需要通过联合查询的数据插入到中间表中，然后将原来的联 合查询改为对中间表的查询。 - 增加冗余字段
合理的加入冗余字段可以减少关联查询提高查询速度
注意：冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数 据不一致的问题。
近千万数据单表优化
- 严格限定数据的查询范围
- 读/写分离
- 应用级别热点数据缓存
- 分库分表
分库分表
垂直拆分
把一张列比较多的表拆分为多张表
优点：
可以使得行数据变小，在查询时减少读取的Block数，减少 I/O次数。此外，垂直分区可以简化表的结构，易于维护
缺点：
主键会出现冗余，可能需要关联查询
水平拆分
拆分原因：
单表树高超过3层时（行数约超过200万行时），查询效率就会变慢
摘自 https://mp.weixin.qq.com/s/BWlkrHiB-uP6fDnsxtKU0Q
B+ 树的存放总记录数 = [根节点指针数 ^ (树高 - 1)] *单个叶子节点记录行数。
例：ID为bigint,数据行 1K 左右的数据
根节点指针数 = innodb_page_size< 16384 > / (bigint<8> + 指针<6>) = 1170
单个叶子节点记录行数 = 16K/1K=16
高度2可存数据 = 1170 * 16 = 18720
高度3可存数据 = 1170 ^ 2 * 16 = 21902400
拆分原理：
保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同 的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。
适用场景:
- 表中的数据本身就有独立性，例如表中分表记录各 个地区的数据或者不同时期的数据，特别是有些数据常 用，有些不常用。
- 需要把数据存放在多个介质上。
缺点:
- 给应用增加复杂度，通常查询时需要多个表名，查 询所有数据都需UNION操作
- 在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数
- 会带 来逻辑、部署、运维的各种复杂度
- 分片事务难以解 决 ，跨界点Join性能较差，逻辑复杂
常见方案:
- 客户端代理:分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 Apache ShardingSphere 、TDDL是两种比较常用的实现。
- 中间件代理:在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 Mycat、Atlas、Apache ShardingSphere、DDB
分库分表后面临的问题
- 分布式事务
如果由应用程 序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。 - 跨库join
在第一次 查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。 - 跨节点的count,order by,group by以及聚合函数问题
分别在各个 节点上得到结果后在应用程序端进行合并 - 数据迁移，容量规划，扩容等问题
- 生成ID问题；改用Snowflake生成ID
- 跨分片的排序分页
需要在不同的分 片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排 序，最后再返回给用户
