Mysql优化相关
Mysql性能优化，包括主从复制、读写分离、亿级数据处理等…
Mysql主从复制,读写分离
常见的数据库集群架构如何
一主多从,主从同步,读写分离
如上图：
- 一个主库提供写服务
- 多个从库提供读服务，可以增加从库提升读性能
- 主从之间同步数据
画外音：任何方案不要忘了本心，加从库的本心，是提升读性能。
为什么会出现不一致
主从同步有时延，这个时延期间读从库，可能读到不一致的数据。
如上图：
- 服务发起了一个写请求
- 服务又发起了一个读请求，此时同步未完成，读到一个不一致的脏数据
- 数据库主从同步最后才完成
画外音：任何数据冗余，必将引发一致性问题。
如何避免这种主从延时导致的不一致
方案一：忽略
任何脱离业务的架构设计都是耍流氓，绝大部分业务，例如：百度搜索，淘宝订单，QQ消息，58帖子都允许短时间不一致。
画外音：如果业务能接受，最推崇此法。
如果业务能够接受，别把系统架构搞得太复杂。
方案二：强制读主
如上图：
- 使用一个高可用主库提供数据库服务
- 读和写都落到主库上
- 采用缓存来提升系统读性能
这是很常见的微服务架构，可以避免数据库主从一致性问题。
方案三：选择性读主
强制读主过于粗暴，毕竟只有少量写请求，很短时间，可能读取到脏数据。
有没有可能实现，只有这一段时间，可能读到从库脏数据的读请求读主，平时读从呢
可以利用一个缓存记录必须读主的数据。
如上图，当写请求发生时：
- 写主库
- 将哪个库，哪个表，哪个主键三个信息拼装一个key设置到cache里，这条记录的超时时间，设置为“主从同步时延”
画外音：key的格式为“db:table:PK”，假设主从延时为1s，这个key的cache超时时间也为1s。*
如上图，当读请求发生时：
这是要读哪个库，哪个表，哪个主键的数据呢，也将这三个信息拼装一个key，到cache里去查询，如果，
- cache里有这个key，说明1s内刚发生过写请求，数据库主从同步可能还没有完成，此时就应该去主库查询
- cache里没有这个key，说明最近没有发生过写请求，此时就可以去从库查询
以此，保证读到的一定不是不一致的脏数据。
总结
数据库主库和从库不一致，常见有这么几种优化方案：
- 业务可以接受，系统不优化
- 强制读主，高可用主库，用缓存提高读性能
- 在cache里记录哪些记录发生过写请求，来路由读主还是读从
来源： https://blog.csdn.net/dingqinghu/article/details/80991923
Mysql亿级数据处理
第一阶段
- 一定要正bai确设计索引
- 一定要避免duSQL语句全表扫描，所以SQL一定要走zhi索引（如：一切的 > < != 等等之类的dao写法都会导致全表扫描）
- 一定要避免 limit 10000000,20 这样的查询
- 一定要避免 LEFT JOIN 之类的查询，不把这样的逻辑处理交给数据库
- 每个表索引不要建太多，大数据时会增加数据库的写入压力
第二阶段：
- 采用分表技术（大表分小表）
- 垂直分表：将部分字段分离出来，设计成分表，根据主表的主键关联
- 水平分表：将相同字段表中的记录按照某种Hash算法进行拆分多个分表
- 采用mysql分区技术（必须5.1版以上，此技术完全能够对抗Oracle），与水平分表有点类似，但是它是在逻辑层进行的水平分表
第三阶段（服务器方面）：
- 采用memcached之类的内存对象缓存系统，减少数据库读取操作
- 采用主从数据库设计，分离数据库的读写压力
- 采用Squid之类的代理服务器和Web缓存服务器技术
explain分析慢查询
可以通过 explain、show profile 和 trace 等诊断工具来分析慢查询
Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等
。可以帮我们选择更好地索引和写出更优的 SQL 。
使用方法：在查询语句前面加上 explain 运行就可以了。
mysql> explain select * from t1 where b=100;
Explain 的结果各字段解释如下：
加粗的列为需要重点关注的项。
| 列名 | 解释 |
|---|---|
| id | 查询编号 |
| select_type | 查询类型：显示本行是简单还是复杂查询 |
| table | 涉及到的表 |
| partitions | 匹配的分区：查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表，该值为 NULL。 |
| type | 本次查询的表连接类型 |
| possible_keys | 可能选择的索引 |
| key | 实际选择的索引 |
| key_len | 被选择的索引长度：一般用于判断联合索引有多少列被选择了 |
| ref | 与索引比较的列 |
| rows | 预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确 |
| filtered | 按条件筛选的行的百分比 |
| Extra | 附加信息 |
select_type
| select_type 的值 | 解释 |
|---|---|
| SIMPLE | 简单查询(不使用关联查询或子查询) |
| PRIMARY | 如果包含关联查询或者子查询，则最外层的查询部分标记为primary |
| UNION | 联合查询中第二个及后面的查询 |
| DEPENDENT UNION | 满足依赖外部的关联查询中第二个及以后的查询 |
| UNION RESULT | 联合查询的结果 |
| SUBQUERY | 子查询中的第一个查询 |
| DEPENDENT SUBQUERY | 子查询中的第一个查询，并且依赖外部查询 |
| DERIVED | 用到派生表的查询 |
| MATERIALIZED | 被物化的子查询 |
| UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存，必须重新评估外层查询的每一行 |
| UNCACHEABLE UNION | 关联查询第二个或后面的语句属于不可缓存的子查询 |
type
| type的值 | 解释 |
|---|---|
| system | 查询对象表只有一行数据,且只能用于 MyISAM 和 Memory 引擎的表，这是最好的情况 |
| const | 基于主键或唯一索引查询，最多返回一条结果 |
| eq_ref | 表连接时基于主键或非 NULL 的唯一索引完成扫描 |
| ref | 基于普通索引的等值查询，或者表间等值连接 |
| fulltext | 全文检索 |
| ref_or_null | 表连接类型是 ref，但进行扫描的索引列中可能包含 NULL 值 |
| index_merge | 利用多个索引 |
| unique_subquery | 子查询中使用唯一索引 |
| index_subquery | 子查询中使用普通索引 |
| range | 利用索引进行范围查询 |
| index | 全索引扫描 |
| ALL | 全表扫描 |
表3-type 各项值解释
Extra
| Extra 常见的值 | 解释 | 例子 |
|---|---|---|
| Using filesort | 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序 | explain select * from t1 order by create_time; |
| Using temporary | 需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时 | explain select * from t1 group by create_time; |
| Using index | 使用覆盖索引 | explain select a from t1 where a=111; |
| Using where | 使用 where 语句来处理结果 | explain select * from t1 where create_time=‘2019-06-18 14:38:24’; |
| Impossible WHERE | 对 where 子句判断的结果总是 false 而不能选择任何数据 | explain select * from t1 where 1<0; |
| Using join buffer (Block Nested Loop) | 关联查询中，被驱动表的关联字段没索引 | explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time); |
| Using index condition | 先条件过滤索引，再查数据 | explain select * from t1 where a >900 and a like “%9”; |
| Select tables optimized away | 使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是 | explain select max(a) from t1; |
表4-Extra 常见值解释及举例
如何定位慢sql
定位慢sql
在实际工作中,发现日志中某个功能需要很久才能返回结果,去分析是不是由于慢查询所导致的.
定位慢sql两种方式:
查看慢查询日志确定已经执行完的慢查询
show processlist
通过慢查询日志
如果需要定位慢查询,一般的方法是通过慢查询日志来查询的,Mysql的慢查询日志用来记录Mysql中响应时间超过参数long_query_time(
单位/秒,默认10) 设置的值并且扫描记录数不小于min_examined_row_limit(默认值0)的语句, 能够帮我们找到执行完的慢sql,方便我们对慢sql进行优化.
默认情况下不会记录查询时间不超过
long_query_time但是不使用索引的语句, 可以通过配置log_queries_not_using_indexes= on
让不使用索引的sql都被记录到慢查询日志中(即使查询没超过long_query_time配置的值)
如果需要使用慢查询日志,一般分为四步:
- 开启慢查询 :
set global slow_query_log = on - 设置慢查询阈值:
set global long_query_time = 1 - 确定慢查询日志路径 :
show global variables like "datadir"(慢查询的日志路径默认是Mysql的数据目录) - 确认慢查询日志的文件名:
show global variables like "show_query_log_file"
通过show processlist
有的时候慢查询正在执行,已经导致数据库负载偏高了,而由于慢查询还没执行完, 因此慢查询日志还看不到任何语句,此时可以使用show
processlist 命令判断正在执行的慢查询. show processlist 显示哪些线程正在运行.如果有PROCESS权限,则可以看到所有的线程.否则,只能看到当前会话的线程.mysql> show processlist\G
time : 表示执行的时间 ,可以通过执行时间来判断是否为慢sql
info : 表示sql语句