MySQL优化

这是我给学生培训是整理的，现在拿出来分享给大家啊

一. 基本架构

1. 服务层：处理客户端和服务端链接，安全验证
2. 核心层：查询分析，优化，缓存，内置函数； 内建视图，存储过程，触发器
3. 存储引擎层：数据的存储、提取

<img src="http://kevins.pro/blog/mysql_optimization/mysql_optimization_com.jpg" width="100%"/>

遇到问题后针对相应模块进行优化

二. 版本选择

（被收购后创始人开发了MariaDB，完全兼容MySQL，使用XtraDB引擎，代替MySQL的InnoDB）

• 企业版：收费
• 社区版：开源，用的人多
• Percona Server：新特性多
• MariaDB：国内用的不多

三. 配置文件详解

/etc/my.cof

1. max_connections MySQL所允许的的同时会话数 Error: Too many connections
2. max_connect_errors 最大错误（连接类）允许数，会引发FLUSH HOSTS重启服务
3. key_buffer_size 关键词缓冲区大小，缓存MyISAM索引块，决定索引处理速度，读取索引处理速度
4. max_allowed_packet 设置最大包，限制server结束数据块大小，避免超长SQL执行，Error: 信息包过大 关闭连接。 如果客户端遇到丢失与MySQL连接的报错，检查这个选项。
5. thread_***_size 服务器线程缓存
6. thread_concurrency 默认为CPU核数X2，设置错误的话影响MySQL利用多核处理器性能
7. sort_buffer_size 每个连接需要使用buffer时分配的内存大小，不是越大越好。例：1000个连接，一个1MB，会占用1GB内存，200WX1MB=20GB
8. join_buffer_size join表使用的缓存大小，一般默认，不重设
9. query_***_size 查询缓存大小，再查询时返回缓存，缓存期间表必须没有被更改，否则缓存失效，多写入操作的话设置大了会影响写入效率
10. read_buffer_size MyISAM引擎下全表扫描的缓冲大小。如果无法添加索引时还要全表扫描，增大这个值进行优化
11. read_rnd_buffer_size 从排序好的数据中读取行时，行数据从缓冲区读取的大小，会提升order by性能 注意：MySQL会为每个客户端申请这个缓冲区，并发过大时，设置过大影响内存开销
12. myisam_sort_buffer_size MyISAM表发生变化时，重新排序所需的缓存
13. innodb_buffer_pool_size InnoDB 使用缓存保存索引，保存原始数据的缓存大小，可以有效减少读取数据所需的磁盘IO
14. innodb_log_file_size 数据日志文件大小，大的值可以提高性能，但增加了恢复故障数据库的时间（恢复故障数据库时需要读取数据日志文件，当日志过大会导致时间过长）
15. innodb_log_buffer_size日志文件缓存，增大该文件可以提高性能，但增大了忽然宕机后损失数据的风险（日志文件在缓存中，还没来得及存进硬盘就断电了）
16. innodb_flush_log_at_trx_commit 执行事务的时候，会往InnoDB存储引擎的日志缓存插入事务日志，写数据前先写日志（预写日志方式）设置为0，实时写入；当设置为1时，缓存实时写入磁盘；2时，缓存实时写入文件，每秒文件实时写入磁盘
17. innodb_lock_wait_timeout 被回滚前（当一个事务被撤销时），一个InnoDB事务，应该等待一个锁被批准多久，当InnoDB无法检测死锁时，这个值就有用了

总结：buffur_size大了，可以提高性能，但是占用相应的内存。多实践。

四. 软件优化

1. 选择合适的引擎

• MyISAM 索引顺序访问方法，支持全文索引，非事务安全，不支持外键，会加表级锁

三个文件：
    FRM 存放表结构
    MYD 存放数据
    MYI 存放索引

• InnoDB 事务型存储引擎，加行锁，支持回滚，崩溃恢复，ACID事务控制，表和索引放在一个表空间里头，表空间多个文件。

例：
    update tableset age=3 where name like "%jeff%";
    //会锁表

2. 正确使用索引

• 给合适的列表建立索引，给where子句，连接子句建立索引，而不是select选择列表
• 索引值应该不相同，唯一值时效果最好，大量重复效果很差
• 使用短索引，指定前缀长度char(50)的前20，30值唯一例：文件名；索引缓存一定（小）时，存的索引多，消耗IO更小，能提高查找速度
• 最左前缀n列索引，最左列的值匹配，更快。
• like查询，索引会失效，尽量少用like。百万、千万数据时，用like Sphinx开源方案结合MySQL
• 不能滥用索引

    1.索引占用空间
    2.更新数据，索引必须更新，时间长，尽量不要用在长期不用的字段上建立索引
    3.SQL执行一个查询语句，增加查询优化的时间

3. 避免使用SELECT *

• 返回结果过多，降低查询的速度
• 过多的返回结果，会增大服务器返回给APP端的数据传输量。例：网络传输速度面，弱网络环境下，容易造成请求失效

4. 字段尽量设置为NOT NULL

"" 和 NULL

{"name":"myf"} {"name":""} {"hobby":空array}

NULL占空间
    例：安卓需要判断""还是NULL
    Java和OC都是强类型，会造成APP闪退

五. 硬件优化

1. Linux内核用内存开缓存存放数据

• 写文件：文件延迟写入机制，先把文件存放到缓存，达到一定程度写进硬盘
• 读文件：同时读文件到缓存，下次需要相同文件直接从缓存中取，而不是从硬盘取

2. 增加应用缓存

• 本地缓存：数据防盗服务器内存的文件中
• 分布式缓存：Redis, Men*** 读写性能非常高，QPS（每秒查询请求数）每秒达到1W以上；数据持久化用Redis，不持久化两者都可以

3. 用SSD代替机械硬盘

• 日志和数据分开存储，日志顺序读写 - 机械硬盘，数据随机读写 - SSD
• 可以调参数

# 操作系统禁用缓存，直接通过fsync方式将数据刷入机械硬盘
innodb_flush_method = O_DIRECT

# 控制MySQL中一次刷新脏页的数量，SSD io 增强，增大一次输入脏页的数量
innodb_in_capacity = 1000

4. SSD+SATA混合存储，FlashCache: Facebook开源在文件系统和设备驱动之间加了一层缓存，对热数据缓存

六. 架构优化

1. 分表

• 水平拆分：数据分成多个表<br> 拆分后的每张表的表头相同

• 垂直拆分：字段分成多个表

• 插入数据、更新数据、删除数据、查询数据时：<br> MyISAM MERGE存储引擎，多个表合成一个表<br> InnoDB用alter table，变成MyISAM存储引擎，然后MEGRE

• 面试题：MERGE存储引擎将N个表合并，数据库中如何存储：<br> 答： 真实存储为N个表

• 表更大的话就需要分库了

2. 读写分离

• 读是一些机器，写是一些机器，二进制文件的主从复制，延迟解决方案
• 数据库压力大了，可以把读和写拆开，对应主从服务器，主服务器写操作、从服务器是读操作
• 大多数业务是读业务。京东、淘宝大量浏览商品、挑选商品是读操作（多），购买是写操作（少）。

• 主服务器写操作的同时，同步到从服务器，保持数据完整性——主从复制

• 主从复制原理：<br> 基于主服务器的二进制日志（binlog）跟踪所有的对数据库的完整更改实现<br> 要实现主从复制，必须在主服务器上启动二进制日志<br> 主从复制是异步复制，三个线程参与：主服务器一个线程（IO线程）、从服务器两个（IO线程和SQL线程）

• 主从复制过程：<br> a. 从数据库，执行start slave开启主从复制<br> b. 从数据库IO线程会通过主数据库授权的用户请求连接主数据库，并请求主数据库的binlog日志的指定位置，change master命令指定日志文件位置<br> c. 主数据库收到IO请求，负责复制的IO线程跟据请求读取的指定binlog文件返回给从数据库的IO线程，返回的信息除了日志文件，还有本次返回的日志内容在binlog文件名称和位置<br> d. 从数据库获取的内容和位置（binlog），写入到（从数据库）relaylog中继日志的最末端，并将新的binlog文件名和位置记录到master-info文件，方便下次读取主数据库的binlog日志，指定位置，方便定位<br> e. 从数据库SQL线程，实时检测本地relaylog新增内容，解析为SQL语句，执行

• 弊端：延迟

• 主从复制延迟解决方案：<br> a. 定位问题：延迟瓶颈，IO压力大，升级硬件，换成SSD<br> b. 单线程从relaylog执行MySQL语句延迟，换成MySQL5.6以上版本多线程，或者Tungsten第三方并行复制工具<br> c. 都不行，直接分库

3. 分库

• Cobar方案：阿里开源（后续无更新）

• MyCat基于Cobar，MySQL通讯协议，***服务器，无状态，容易部署，负载均衡<br> 原理：<br> 应用服务器传SQL语句，路由解析，转发到不同的后台数据库，结果汇总，返回

• MyCat把逻辑数据库和数据表对应到物理真实的数据库、数据表，遮蔽了物理差异性

• MyCat工作流程：<br> a. 应用服务器向MyCat发送SQL语句select * from user where id in(30, 31, 32)<br> b. MyCat前端通信模块与应用服务器通信，交给SQL解析模块<br> c. SQL解析模块解析完交给SQL路由模块<br> d. SQL路由模块，id取模，余数为0：db1，余数为1：db2……<br> e. 把SQL拆解为select * from user where id in 30……交给SQL执行模块，对应db1 db2 db3<br> f. SQL执行模块通过后端，分别在db1 db2 db3执行语句，返回结构到数据集合合并模块，然后返回给应用服务器

七. SQL慢查询分析、调参数

慢查询：指执行超过一定时间的SQL查询语句记录到慢查询日志，方便开发人员查看日志

1. 找问题：

• long_qeury_time定义慢查询时间
• slow_query_log设置慢查询开关
• slow_query_log_file设置慢查询日志文件路径

2. 设置方法1：

set log_query_time = 1;
set slow_query_log = on;
set slow_query_log_file = '/data/slow.log'

3. 设置方法2：

/etc/my.comf设置参数

4. 分析：

explain命令进行分析，输出结构含义，官方文档

八. 活用存储结构

1. 内容表 id user_id content

2. 索引表（字段）

3. 内容表（kv，放数据）

九. 故障排除案例

1. APP搜索商家，后台数据库load居高不下

• 解决方案：like 查询索引无效导致，使用Sphinx Coreseek开源全文检索