《App后台开发运维和架构实践》读书笔记 - MySQL

6.1 基本架构

连接/线程处理：连接处理，安全验证；

核心层： 查询分析，优化，缓存，内置函数；视图，存储过程，触发器；

存储引擎层：负责数据的存储与提取；通过存储引擎API访问底层存储，遮蔽了底层不同存储引擎的差异，底层差异化对上层而言是透明的；存储引擎之间互相不通信，只响应上层的查询；

6.3 配置

max_connections: MySQL同时允许会话数的上限；

max_connect_errors: 每个客户端连接的最大错误允许数。如果达到上限，该客户端将被MySQL阻止，直到执行"FLUSH HOSTS"或者服务重启。

key_buffer_size: 关键词缓冲区大小，缓冲MyISAM表的索引快；决定了数据库索引处理的速度；

max_allowed_packet: 限制server接受的数据包大小，避免超长SQL的执行有问题；

thread_cache_size: 服务器端线程缓存。如果缓存空间足够，当客户端线程断开后，连接还会存放在缓存中，当客户端线程重新连接，从缓存中把连接取出并重新使用；

thread_concurrency: 此值最好设为CPU核数的2倍；

sort_buffer_size: 每个连接需要使用buffer时分配的内存大小

join_buffer_size: join表时使用的缓存

query_cache_size: 查询缓存的大小。每次查询结束后，MySQL会把查询结果缓存，当下次收到相同的查询时，若涉及的原表未改变，则直接返回缓存数据；

innodb_log_buffer_size: 日志文件的缓存。增大该值可提高一些性能，但增大了忽然宕机时损失数据的风险

innodb_flush_log_at_trx_commit: 当执行事务时，回往InnoDB存储引擎的日志缓存里插入事务日志；当事务提交时，必须将存储引擎的日志缓冲flush入磁盘，即写数据前必须先写日志，即“预写日志方式”；此参数控制flush频率；

6.4 配置

6.4.1 MyISM和InnoDB存储引擎的选择：

表锁还是行锁；是否事务安全；是否支持外建；

6.4.2 索引技巧

给哪列建立索引；

索引列的值尽可能不相同，才能更有效；

索引列的值如果前几十字节都相同，那太占空间了，会降低缓存使用率，消耗更多磁盘IO;

使用like查询，会使索引实效；解决：结合Sphinx等搜索软件来做；

索引弊端：占磁盘空间；更新数据时必须更新索引，导致数据更新操作速度变慢；

6.4.3 避免使用select *，特别是App端

"select *"从数据库返回结果更多，速度慢；服务器和App间无线网流量消耗增大，不稳定的无线网会导致操作失败几率加大；

6.4.4 空字段尽量别用NULL，而用""代替；因为程序判断字符串NULL如果忘记会导致App闪退！！

数组也同样道理，空的话返回长度为0的数组就好；

6.5 硬件优化

6.5.1 增加物理内存：Linux的buffer和cache越大越好；MySQL的各种查询buffer和cache越大越好；

6.5.2 增加应用缓存：使用Redis（支持数据持久化）或Memcached，减少访问数据库的次数；

6.5.3 使用SSD，随机读写速度超快；不是磁头机械原理，而是闪存电路；

可将顺序写的日志放在机械磁盘；把顺序读些多的表放在机械磁盘，随机读些多的表放在SSD

6.5.4 Facebook的FlashCache，是SSD硬盘和SATA硬盘捆绑为一个虚拟设备，供文件系统使用；其中SSD作为热数据的缓存来使用；

6.6 分库分表

6.6.1 分表：现象：大表的写速度变慢；原因：“表锁”

水平拆分（按列分）；垂直拆分（按行分）

业务语句如何将多个子表视为一个整表（即不加大原SQL语句的复杂程度）：使用MyISAM的MERGE存储引擎；

6.6.2 读写分离：原理：读操作比写操作多

用来执行写操作的做主数据库，用来执行读操作的做从数据库（即复本），主库一有更新就要复制到从库上；

问题：从库延迟；

6.6.3 分库：数据表更大时，一个库放不下了，分别放到多台机器的MySQL上；

软件：MyCat：分库对用户透明化：负载均衡；查询结果merge；

6.7 SQL慢查询分析

有些查询语句执行时间过长，MySQL支持对超长查询的Debug，分析其为什么耗时

6.8 云数据库

- 有SSD;

- 主从热备份；

- 监控完善；

- 弹性扩展