一, SQL 查询优化(重要)
1.1 获取有性能问题 SQL 的三种方式
1. 通过用户反馈获取存在性能问题的 SQL;
2. 通过慢查日志获取存在性能问题的 SQL;
3. 实时获取存在性能问题的 SQL;
1.1.2 慢查日志分析工具
相关配置参数:
- slow_query_log # 启动停止记录慢查日志, 慢查询日志默认是没有开启的可以在配置文件中开启(on)
- slow_query_log_file # 指定慢查日志的存储路径及文件, 日志存储和数据从存储应该分开存储
- long_query_time # 指定记录慢查询日志 SQL 执行时间的阀值默认值为 10 秒通常, 对于一个繁忙的系统来说, 改为 0.001 秒 (1 毫秒) 比较合适
- log_queries_not_using_indexes #是否记录未使用索引的 SQL
常用工具: mysqldumpslow 和 pt-query-digest
pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord slow-MySQL.log
1.1.3 实时获取有性能问题的 SQL(推荐)
SELECT id,user,host,DB,command,time,state,info FROM information_schema.processlist WHERE TIME>=60
查询当前服务器执行超过 60s 的 SQL, 可以通过脚本周期性的来执行这条 SQL, 就能查出有问题的 SQL.
1.2 SQL 的解析预处理及生成执行计划(重要)
1.2.1 查询过程描述(重点!!!)
通过上图可以清晰的了解到 MySQL 查询执行的大致过程:
1. 发送 SQL 语句.
2. 查询缓存, 如果命中缓存直接返回结果.
3.SQL 解析, 预处理, 再由优化器生成对应的查询执行计划.
4. 执行查询, 调用存储引擎 API 获取数据.
5. 返回结果.
1.2.2 查询缓存对性能的影响(建议关闭缓存)
第一阶段:
相关配置参数:
query_cache_type # 设置查询缓存是否可用 query_cache_size # 设置查询缓存的内存大小 query_cache_limit # 设置查询缓存可用的存储最大值(加上 sql_no_cache 可以提高效率) query_cache_wlock_invalidate # 设置数据表被锁后是否返回缓存中的数据 query_cache_min_res_unit # 设置查询缓存分配的内存块的最小单
缓存查找是利用对大小写敏感的哈希查找来实现的, Hash 查找只能进行全值查找(sql 完全一致),
如果缓存命中, 检查用户权限, 如果权限允许, 直接返回, 查询不被解析, 也不会生成查询计划.
在一个读写比较频繁的系统中, 建议关闭缓存, 因为缓存更新会加锁. 将 query_cache_type 设置为 off,query_cache_size 设置为 0.
1.2.3 第二阶段: MySQL 依照执行计划和存储引擎进行交互
这个阶段包括了多个子过程:
一条查询可以有多种查询方式, 查询优化器会对每一种查询方式的 (存储引擎) 统计信息进行比较, 找到成本最低的查询方式, 这也就是索引不能太多的原因.
1.3 会造成 MySQL 生成错误的执行计划的原因
1, 统计信息不准确
2, 成本估算与实际的执行计划成本不同
3, 给出的最优执行计划与估计的不同
4,MySQL 不考虑并发查询
5, 会基于固定规则生成执行计划
6,MySQL 不考虑不受其控制的成本, 如存储过程, 用户自定义函数
1.4 MySQL 优化器可优化的 SQL 类型
查询优化器: 对查询进行优化并查询 MySQL 认为的成本最低的执行计划.
为了生成最优的执行计划, 查询优化器会对一些查询进行改写
可以优化的 sql 类型
1, 重新定义表的关联顺序;
2, 将外连接转换为内连接;
3, 使用等价变换规则;
4, 优化 count(),min(),max();
5, 将一个表达式转换为常数;
6, 子查询优化;
image.PNG
7, 提前终止查询, 如发现一个不成立条件(如 where id = -1), 立即返回一个空结果;
8, 对 in()条件进行优化;
1.5 查询处理各个阶段所需要的时间
1.5.1 使用 profile(目前已经不推荐使用了)
set profiling = 1; #启动 profile, 这是一个 session 级的配制执行查询 show profiles; # 查询每一个查询所消耗的总时间的信息 show profiles for query N; # 查询的每个阶段所消耗的时间
1.5.2 performance_schema 是 5.5 引入的一个性能分析引擎(5.5 版本时期开销比较大)
启动监控和历史记录表: use performance_schema
update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%'; update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';
1.6 特定 SQL 的查询优化
1.6.1 大表的数据修改
1.6.2 大表的结构修改
1. 利用主从复制, 先对从服务器进入修改, 然后主从切换
2.(推荐)
添加一个新表(修改后的结构), 老表数据导入新表, 老表建立触发器, 修改数据同步到新表,
老表加一个排它锁(重命名), 新表重命名, 删除老表.
修改语句这个样子:
alter table sbtest4 modify c varchar(150) not null default ''
利用工具修改:
1.6.3 优化 not in 和 <> 查询
子查询改写为关联查询:
二, 分库分表
2.1 分库分表的几种方式
分担读负载 可通过 一主多从, 升级硬件来解决.
2.1.1 把一个实例中的多个数据库拆分到不同实例(集群)
拆分简单, 不允许跨库. 但并不能减少写负载
2.1.2 把一个库中的表分离到不同的数据库中
该方式只能在一定时间内减少写压力.
以上两种方式只能暂时解决读写性能问题.
2.1.3 数据库分片
对一个库中的相关表进行水平拆分到不同实例的数据库中
2.1.3.1 如何选择分区键
1. 分区键要能尽可能避免跨分区查询的发生
2. 分区键要尽可能使各个分区中的数据平均
2.1.3.2 分片中如何生成全局唯一 ID
扩展: 表的垂直拆分和水平拆分
随着业务的发展, 数据库成为了整个系统性能的一个瓶颈, 这时候就需要对数据库进行优化, 但是单单是优化只能提高有限的一点性能, 这时候要想解决问题需要的是从数据库架构层面去思考问题. 数据库的架构是一个很大的课题, 里面最实用的有两个, 一个是数据库拆分, 一个是读写分离. 今天就来谈谈数据库的两种拆分方式.
一, 垂直拆分
垂直拆分很简单, 就是根据不同的业务来划分不同的数据库. 比如一个电商系统根据业务可以分成商品表, 会员表, 订单表. 原先, 这些表都是放在同一个数据库服务器上, 现在需要垂直拆分数据库, 就是将商品表单独放在一个数据库中, 会员表单独放在一个数据库中, 订单表单独放在一个数据库中, 这样就解决了表与表之间的 io 竞争.
二, 水平拆分
垂直拆分比较简单, 水平拆分就比较复杂了, 要考虑很多东西. 垂直拆分根据业务来拆分, 或者说的直白点就是根据表名来拆分, 而水平拆分是根据表里面的字段来拆分(记住是根据字段来拆分, 而不是拆分字段, 拆分后的每一张表的表结构都是一样). 比如要拆分用户表, 可以根据用户的注册时间这一字段来拆分整个表, 2016 年注册的用户放在用户表 1 中, 2017 年注册的用户放在用户表 2 中, 2018 年注册的用户放在用户表 3 中. 这就是水平拆分, 看似很简单, 实际上要考虑的东西是很多的. 就比如上述的例子, 我们用时间来拆分, 就会有局限性. 一个好产品上线后, 在开始的时候用户数量都是很少的, 都需要一定时间的沉淀, 才会有一个用户数量的爆发期. 如果用时间来拆分, 就会出现一种情况, 就是用户表 1 的规模很小, 而用户表 2 的规模却很大, 是用户表 1 的好几倍, 而用户表三可能是用户表 1 的好几十倍. 这样的话, 拆分水平拆分的意义就不大了. 一般用户表都是用户 id 来拆分的, 具体还要结合实际业务去分析. 所以, 水平拆分是一件很复杂的事情, 大家在进行水平拆分的时候一定要考虑到方方面面, 这样才能设计出优秀的数据库架构方案.
来源: http://www.jianshu.com/p/999537f158b1