MySQL学习Day25——数据库其他调优策略

一、数据库调优的措施:

1.调优的目标:

(1)尽可能节省系统资源，以便系统可以提供更大负荷的服务

(2)合理的结构设计和参数调整，以提高用户操作的响应速度

(3)减少系统的瓶颈，提高MySQL数据库整体的性能;

2.如何定位调优:用户的反馈、日志分析、服务器资源使用监控、数据库内部状况监控

3.调优的维度和步骤:

(1)选择合适的DBMS:DBMS的选择关系到了后面的整个设计过程，所以第一步就是要选择适合的DBMS

(2)优化表设计:表的结构要尽量遵循三范式原则，如果查询的次数比较多可以采用反范式化进行优化，表字段的数据类型应该合理。

(3)优化逻辑查询:子查询优化、等价谓词重写、视图重写、条件优化、连接消除和嵌套连接消除等

(4)优化物理查询:合理使用表的索引

(5)使用Redis或者Memcached作为缓存

(6)库级优化:a.读写分离;b.数据分片

二、优化MySQL服务器:

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

(1)配置较大的内存:足够大的内存是提高MySQL数据库的性能方法之一，内存的速度比磁盘IO快很多，可以通过增加系统的缓冲区容量使得数据在内存中停留的时间更长，以减少磁盘的IO。

(2)配置高速磁盘系统

(3)合理分布磁盘IO:把磁盘IO分散在多个设备上，以减少资源竞争，提高并行操作能力

(4)配置多处理器:MySQL是多线程的数据库，多处理器可以同时执行多个线程。

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

(1)innodb_buffer_pool_size:表示InnoDB类型的表和索引的最大缓存。它不仅仅缓存索引数据，还会缓存表的数据。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。

(2)key_buffer_size:表示索引缓冲区的大小，索引缓冲区是所有的线程共亭。增加索引缓冲区可以得到更好处理的索引(对所有读和多重写)。key_buffer_size的大小取决于内存的大小。如果key_buffer_size太大，就会导致操作系统繁换页，也会降低系统性能。

(3)table_cache:表示同时打开表的个数，table_cache值越大，能够同时打开的表的个数越多。物理内存越大，但是该值越大会影响操作系统的性能。

(4)query_cache_size:表示查询缓冲区的大小。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，就要增加Query_cache_size的值；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓存;MySQL8.0之后失效。该参数需要和query_cache_type配合使用。

(5)query_cache_type:当query_cache_type的值是0时，所有的查询都不使用查询缓存区。当该参数的值为1时，所有的查询都将使用查询缓存区，当该参数的值为2时，只有在查询语句中使用SQL_CACHE关键字查询才使用查询缓存区。使用查询缓存区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。

(6)sort_buffer_size:表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。

(7)join_buffer_size:表示联合査询操作所能使用的缓冲区大小，和sort_buffer_size一样该参数对应的分配内存也是每个连接独享。

(8)read_buffer_size:表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。当线程从表中连续读取记录时需要用到这个缓冲区。

(9)innodb_flush_log_at_trx_commit:表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于InnoDB引擎非常重要。该参数有3个值,分别为0、1和2，默认值为1。

a.innodb_flush_log_at_trx_commit的值为0:表示每秒1次的频率将数据写入日志文件并将日志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

b.innodb_flush_log_at_trx_commit值为1:表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘进行同步。该模式是最安全的，但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入(flush)硬盘。

c.innodb_flush_log_at_trx_commit值为2:表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该模式速度较快。也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

(10)innodb_log_buffer_size:是InnoDB存储引擎的事务日志所使用的缓冲区的大小。为了提高性能也是先将信息写入Innodb Log Buffer中，当满足innodb_flush_log_tnx_commit参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件(或者同步到磁盘)中。

(12)max_connections:表示允许连接到MySQL数据库的最大数量，默认值是151，连接数 不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。

(13)back_log:用于控制MySQL监听TCP牆口时设置的积压请求栈大小,如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log将不被授予连接资源。

(14)thread_cache_size:线程池缓存线程数量的大小，当客户端断开连接后将当前线程缓存起来，当在接到新的连接请求时快速响应无需创建新的线程。

(15)wait_timeout:指定一个请求的最大连接时间

(16)interactive_timeout:表示服务器在关闭连接前等待行动的秒数

三、优化数据库的结构:

1.拆分表(冷热数据的分离):

拆分表的思路是把1个包含很多字段的表拆分成2个或者多个相对较小的表。这样做的原因是这些表中某些字段的操作频率很高(热数据)，经常要进行查询或者更新操作，而另外一些字段的使用频率却很低(冷数据 )。冷热数据分离可以减小表的宽度。如果放在一个表里面，每次查询都要读取大记录，会消耗较多的资源。

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节。表越宽，把表装载进内存缓冲池时所占用的内存也就越大，也会消耗更多的IO。冷热数据分离的目的是:减少磁盘IO，保证执数据的内存缓存命中率;更有效的利用缓存，避免读入无用的冷数据，

2.增加中间表:

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

3.增加冗余字段:

设计数据库表时应该尽量遵循范式理论，尽可能减少冗余字段，但是合理的增加冗余字段可以提高查询速度。

4.优化数据类型:优先选择符合存储需要的最小数据类型

情况一:遇到整数类型字段可以用INT类型，对于非负数的数据优先使用无符号整型UNSIGNED

情况二:既可以使用文本类型也可以使用整数类型的字段要选择使用整数类型

情况三:避免使用TEXT,BLOB数据类型,如果一定要使用建议分离到单独的扩展表中

情况四:避免使用ENUM类型，使用TINYINT类型代替ENUM

情况五:使用TIMESTAMP存储时间

情况六:用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

5.优化插入记录的速度:

(1)MyISAM引擎的表:a.禁用索引;b.禁用唯一性检查;c.使用批量插入;d.使用LOAD DATA INFILE批量导入

(2)InnoDB引擎的表:a.禁用唯一性检查;b.禁用外键检查;c.禁止自动提交

6.使用非空约束:

在设计字段时，如果业务允许，建议使用非空约束。首先，在进行比较和计算时，省去对NULL值的字段判断是否为空的开销，可以提高存储效率;其次，非空字段也更加容易创建索引，可以节省存储空间。

7.分析表、检查表与优化表:

(1)分析表:ANALYZE TABLE

MySQL中提供了ANALYZE TABLE语句分析表，ANALYZE TABLE语句的基本语法如下:

ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE table_name[,table_name1],...

默认情况下MySQL服务会将 ANALYZE TABLE语句写到binlog中，以便在主从架构中从服务能够同步数据。可以添加参数LOCAL或者NO_WRITE_TO_BINLOG取消将语句写到binlog中。使用 ANALYZE TABLE分析表的过程中数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能作用于视图。

ANALYZE TABLE分析后的统计结果会反应到cardinality的值，该值统计了表中某一键所在的列不重复的值的个数。该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。也就是索引列的cardinality的值与表中数据的总条数差距越大，即使査询的时候使用该索引作为查询条件，存储引擎实际査询的时候使用的概率就越小。

(2)检查表:CHECK TABLE

MySQL中可以使用CHECK TABLE语句来检查表,CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误;CHECK TABLE语句在执行过程中也会给表加上只读锁。对于MyISAM类型的表,CHECK TABLE语句还会更新关键字统计数据。而且CHECKTABLE也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在。该语句的基本语法如下:

CHECK TABLE table_name[,table_name]...[option]...
option = {QUICK|FAST|MEDIUM|EXTENDED|CHANGED}
QUICK:不扫描行，不检查错误的连接
FAST:只检查没有被正确关闭的表
CHANGED:只检查上次检查后被更改的表和没有被正确关闭的表
MEDIUM:扫描行，以验证被制除的连接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点
EXTENDED:对每行的所有关键字进行一个全面的关键字査找。这可以确保表是100%一致的，但是花的时间和较长
option只对MyISAM类型的表有效，对InnoDB类型的表无效。

(3)优化表:OPTIMIZE TABLE

MySQL中使用OPTIMIZE TABLE语句来优化表。但是OPTILMIZE TABLE语句只能优化表中的 VARCHAR、BLOB或TEXT 类型的字段。一个表使用了这些字段的数据类型，若已经删除表的一大部分数据，或者已经对含有可变长度行的表(含有VARCHAR、BLOB或TEXT列的表)进行很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

OPTIMIZE TABLE语句对InnoDB和MyISAM类型的表都有效，该语句在执行过程中也会给表加上只读锁。OPTIMIZE语法的格式如下:

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

四、大表优化:

1.限制查询的范围:禁止不带有任何数据范围条件的查询语句

2.读写分离:经典的数据库拆分方案，主库负责写，从库负责读

(1).一主一从模式:

(2).双主双从模式:

3.垂直拆分: 当数据库量级达到千万级以上时，需要把一个数据库切分成多份，放到不同的数据库服务器上，减少对单一数据库的访问压力。可以使得列数据变少，减少IO次数，简化表的结构，易于维护。但是主键会出现冗余，并会引起JOIN操作。

(1).如果数据库中的数据表过多，可以采用垂直分库的方式将关联的数据表部署在同一个数据库上;

(2).如果数据表中的列过多，可以采用垂直分表的方式将一张数据表拆分成多张数据表，把经常一起使用的列放到一张表里

4.水平拆分: