S观察

如何定位调优问题（大中小+4S分析法）

大：服务器资源使用监控：监控服务器的CPU、IO。====>服务器的优化
中：数据库内部状况监控：在数据库的监控中，活动会话(Active Session)监控是一个重要的指标。通过它，可以清楚地了解数据库当前是否处于非常繁忙的状态，是否存在SQL堆积等。
小：我们也可以对事务、锁等待等进行监控，这些都可以帮助我们对数据库的运行状态有更全面的认识。使用慢查询、show profiles、explain看表连接、索引的情况。===>库表/SQL语句的优化
- 4S分析法：Show Status、Slow_Query_Log、Slow Profile、ExplatinSQL，详见24 性能分析工具的使用：4S分析法
手册：日志分析（主要）

补充

	观察（ShowStatus）	行动（Action）
1	是否有周期性	加服务器缓存
2	CPU/IO/Slow慢查询日志 Show Profile Explain SQL	- 索引失效、没有充分利用到索引。索引建立。 - 关联查询太多JOIN(设计缺陷或不得已的需求。SQL优化。 - 服务器调优及各个参数设置（缓冲、线程数等）。调整my.cnf。
3	性能瓶颈	- 读写分离/主从复制 - 分库分表
虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。 - 物理查询优化：通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。 - 逻辑查询优化：通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

| | |

A行动（大中小）

大：服务器的优化

优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈直接决定MySQL数据库的运行速度和效率。针对性能瓶颈提高硬件配置，可以提高MySQL数据库查询、更新的速度。

配置较大的内存。足够大的内存是提高MySQL数据库性能的方法之一。内存的速度比磁盘I/O快得多，可以通过增加系统的缓冲区容量使数据在内存中停留的时间更长，以减少磁盘I/O。
配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。磁盘的I/O能力，也就是它的寻道能力，目前的SCSI高速旋转的是7200转/分钟，这样的速度，一旦访问的用户量上去，磁盘的压力就会过大，如果是每天的网站pv(page view)在150w，这样的一般的配置就无法满足这样的需求了。现在SSD盛行，在SSD上随机访问和顺序访问性能几乎差不多，使用SSD可以减少随机IO带来的性能损耗。
合理分布磁盘IO，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。
配置多处理器，MySQL是多线程的数据库，多处理器可同时执行多个线程。
选择适合的DBMS

如果对事务性处理以及安全性要求高的话，可以选择商业的数据库产品。这些数据库在事务处理和查询性能上都比较强，比如采用SQL Server、Oracle，那么单表存储上亿条数据是没有问题的。如果数据表设计得好，即使不采用分库分表的方式，查询效率也不差。
MySQL的存储引擎可以选择：如果进行事务处理的话可以选择lnnoDB，非事务处理可以选择MylSAM。
NoSQL阵营：键值型数据库、文档型数据库、搜索引擎、列式存储和图形数据库。这些数据库的优缺点和使用场景各有不同，比如列式存储数据库可以大幅度降低系统的IO，适合于分布式文件系统，但如果数据需要频繁地增删改，那么列式存储就不太适用了。

DBMS的选择关系到了后面的整个设计过程，所以第一步就是要选择适合的DBMS。如果已经确定好了DBMS，那么这步可以跳过。

优化MySQL的参数

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。MySQL服务的配置参数都在my . cnf或者my .ini文件的[mysqld]组中。配置完参数以后，需要重新启动MysQL服务才会生效。
如下是性能影响比较大的参数

参数	概念
innodb_buffer_pool_size	这个参数是Mysql数据库最重要的参数之一，表示InnoDB类型的表和索引的最大缓存。它不仅仅缓存索引数据，还会缓存表的数据。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。
key_buffer_size	表示`索引缓冲区的大小`。索引缓冲区是所有的`线程共享`。增加索引缓冲区可以得到更好处理的索引（对所有读和多重写）。当然，这个值不是越大越好，它的大小取决于内存的大小。如果这个值太大，就会导致操作系统频繁换页，也会降低系统性能。对于内存在`4GB`左右的服务器该参数可设置为`256M`或`384M`。
table_cache	表示`同时打开的表的个数`。这个值越大，能够同时打开的表的个数越多。物理内存越大，设置就越大。默认为2402，调到512-1024最佳。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。
sort_buffer_size	表示每个`需要进行排序的线程分配的缓冲区的大小`。增加这个参数的值可以提高`ORDER BY`或`GROUP BY`操作的速度。默认数值是2 097 144字节（约2MB）。对于内存在4GB左右的服务器推荐设置为6-8M，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。
join_buffer_size	表示`联合查询操作所能使用的缓冲区大小`，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。 `join_buffer_size = 8M`：
read_buffer_size	表示`每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）`。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size=n可以临时设置该参数的值。默认为64K，可以设置为4M。
innodb_flush_log_at_trx_commit	表示`何时将缓冲区的数据写入日志文件`，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。默认值为1。 - 值为`0`时，表示`每秒1次`的频率将数据写入日志文件并将日志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。 - 值为`1`时，表示`每次提交事务时`将数据写入日志文件并将日志文件写入磁盘进行同步。该模式是最安全的，但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入（flush）硬盘。 - 值为`2`时，表示`每次提交事务时`将数据写入日志文件，`每隔1秒`将日志文件写入磁盘。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

innodb_log_buffer_size：这是 InnoDB 存储引擎的事务日志所使用的缓冲区。为了提高性能，也是先将信息写入 Innodb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。
max_connections：表示允许连接到MySQL数据库的最大数量，默认值是 151 。如果状态变量connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。
back_log：用于控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ，之后的版本默认为 50 + （max_connections / 5），对于Linux系统推荐设置为小于512的整数，但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的值。
thread_cache_size：线程池缓存线程数量的大小，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。那么为了提高性能可以增大该参数的值。默认为60，可以设置为120。
wait_timeout：指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
interactive_timeout：表示服务器在关闭连接前等待行动的秒数。
~~query_cache_size~~和~~query_cache_type~~：表示查询缓冲区的大小。可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，就要增加Query_cache_size的值；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓存；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。MySQL8.0之后失效。该参数需要和query_cache_type配合使用。

中：库和表的优化

大表优化

限定查询的范围
禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
读写分离
经典的数据库拆分方案，主库负责写，从库负责读，常见的是一主一从模式、双主双重模式
垂直拆分
当数据量级达到千万级以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单—数据库服务器的访问压力。分布式数据库。
水平拆分
当某个表很大时，可以考虑水平拆分。

拆分表：冷热数据分离

拆分表的思路是，把1个包含很多字段的表拆分成多个相对较小的表。这样做的原因是，这些表中某些字段的操作频率很高（热数据），经常要进行查询或客更新操作，而另外一些中段的使用频率却很低（冷数据），冷热数据分离，可以减小表的宽度。如果放在一个表里面，每次查询都要读取大记录，会消耗较多的资源。
冷热数据分离的目的是：减少磁盘lO，保证热数据的内存缓存命中率。更有效的利用缓存，避免读人无用的冷数据。

MySQL限制每个表最多存储4096列，并且每行数据的大小不能超过65535字节，表越宽，把表装载进内存缓冲池时所占用的内存也就越大，也会消耗更多的IO。

增加中间表

对于需要经常联合查询的表，可以建立中询表以提高查询效率。
通过建立中间表，把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询,以此来提高查询效率。

增加冗余字段

根据业务情况合理的增加冗余字段。

优化数据类型

情况1：对整数类型数据进行优化
- 遇到整数类型的字段可以用INT 型。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数据超出取值范围的问题。刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以的。但在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。
- 对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型UNSIGNED来存储。因为无符号相对于有符号，同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255，多出一倍的存储空间。
情况2：既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型

跟文本类型数据相比，大整数往往占用更少的存储空间，因此，在存取和比对的时候，可以占用更少的内存空间。所以，在二者皆可用的情况下，尽量使用整数类型，这样可以提高查询的效率。
如：将IP地址转换成整型数据。

情况3：用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
情况4：使用TIMESTAMP存储时间

TIMESTAMP存储的时间范围1970-01-0100:00:01~2038-01-19-03:14:07。TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。

情况5：避免使用TEXT、BLOB数据类型
情况6：避免使用ENUM类型

修改ENUM值需要使用ALTER语句。ENUM类型的ORDER BY操作效率低，需要额外操作。使用TINYINT来代替ENUM类型。
总之，遇到数据量大的项目时，一定要在充分了解业务需求的前提下，合理优化数据类型，这样才能充分发挥资源的效率，使系统达到最优。

优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况可以分别进行优化。这里我们分为MylSAM引擎和InnoDB存储引擎来讲。

MyISAM引擎的表
- 禁用索引：数据插入完毕后再开启索引。
- 禁用唯一性检查：等到添加完成后再开启。
- 使用批量插入

使用LOAD DATA INFILE 批量导入
- InnoDB引擎的表
禁用唯一性检查：等到添加完成后再开启：set unique_checks=0/1
禁用外键检查：插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复。禁用外键检查的语句如下：SET foreign_key_checks=0;开启语句：SET foreign_key_checks=1;
禁止自动提交：插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交的语句如下：set autocomnit=0;恢复语句：set autocomnit=1;
使用非空约束
在设计字段的时候，如果业务允许，建议尽量使用非空约束。这样做的好处：

进行比较和计算时，省去要对NULL值的字段判断是否为空的开销，提高存储效率。
非空字段也容易创建索引。因为索引NULL列需要额外的空间来保存，所以要占用更多的空间。使用非空约束，就可以节省存储空间(每个字段1个bit) 。

分析表、检查表与优化表
MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布，检查表主要是检查表是否存在错误，优化表主要是消除删除或者更新造成的空间浪费。
详见顶部的文档说明。

小：SQL语句的优化
SQL查询优化，可以分为逻辑查询优化和物理查询优化。
逻辑查询优化
逻辑查询优化就是通过改变SQL语句的内容让SQL执行效率更高效，采用的方式是对SQL语句进行等价变换，对查询进行重写。
SQL的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。
- EXISTS子查询和IN子查询的时候，会根据小表驱动大表的原则选择适合的子查询。
- 在WHERE子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。
- 减少使用order by使用
- 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，可以将子查询更改为join表连接。

详见索引和查询优化一节。
物理查询优化
物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过计算代价模型对各种可能的访问路径进行估算，从而找到执行方式中代价最小的作为执行计划。

补充：MySQL8.0下的新特性

隐藏索引
降序索引

综合优化

使用 Redis 或 Memcached 作为缓存

10 数据库之MySQL

10 综合：数据库分析调优策略

S观察