前言
你真的会数据库调优吗？
不要光搁着索引薅了，B+树都被薅秃了
这篇文章总结了在面试中被忽略的知识点，看了这篇文章能让你对数据库调优有更深的理解！
目录
一、SQL优化与索引优化
1.使用 EXPLAIN 分析 SQL
explain select * from xxx_table;
打印结果：
（1）select_type
查询类型
SIMPLE：简单查询，查询中不包含子查询和UNION
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY：在SELECT或WHERE列表中包含了子查询
DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，将结果放在临时表中。
UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在form子句的子查询中，外层select将被标记为DERIVED
UNION RESULT：从UNION表获取结果的select
（2）table
该条查询是来自哪张表
（3）type
访问类型
const/system：单表中最多有一条匹配行，查询起来非常迅速，所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理
const：表示通过索引一次就找到了，const用于比较primary key或unique索引，常见于主键或唯一索引扫描
eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，一般出现在多表连接时使用primary key或者unique index作为关联条件。
ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行。
range：索引范围扫描。key列表示使用了哪个索引。这种范围扫描索引扫描要比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引，常用于<、<=、>、>=、between等操作。
index：索引全扫描，通常比ALL快，因为索引文件比数据文件要小，虽然都是全表扫描，但是index是从索引读取的，ALL是从硬盘读取的。
ALL：全表扫描
（4）possible_keys
显示可能引用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
（5）key
实际在这次查询中使用的索引，如果为NULL则表示这次没有使用索引
（6）key_len
索引字段的最大可能长度，根据表定义计算而得
（7）ref
使用哪一列 or 常数与 key 一起从表中选择行
（8）rows
扫描行的数量
（9）Extra
包含不适用在其他列中显示，但对执行计划非常重要的额外信息，主要有以下几种：
Using Where 表示进行了回表查询
Using Index 表示索引覆盖，不会回表查询
Using Index Condition 表示进行了ICP优化
Using Flesort 表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果
2.创建、使用索引的原则
（1）理解查询需求
在创建索引之前，了解应用程序的查询需求是非常重要的。索引应该根据常见的查询和应用程序性能目标来设计。
例如：
①、单表查询，在高频查询字段中添加索引；
②、多表查询，在表关联字段上创建索引；
③、索引失效的条件：
索引列数据类型不匹配；
对索引列进行计算或者是使用函数；
where语句中使用了IS NULL或者IS NOT NULL；
like查询是以%开头；
使用OR时，有一个列没有索引，那么其它列的索引将不起作用；
不要使用 !=，<>；尽量避免上述情况！
（2）选择适当的索引类型
MySQL支持多种类型的索引，包括B-tree索引、哈希索引、全文索引等。选择适当的索引类型可以提高查询效率和性能。
（3）考虑使用复合索引
复合索引是在多个列上创建的索引，可以同时满足多个查询条件。当创建复合索引时，请按照查询中列的顺序定义索引列。这样可以最大程度地提高索引的效率。
（4）避免冗余索引
在创建索引时，要避免冗余索引。冗余索引会导致额外的磁盘空间占用和更新性能损失。尽可能使用覆盖索引来避免全表扫描。
（5）定期维护索引
定期运行MySQL的ANALYZE TABLE命令来更新表的统计信息，以便优化器做出更好的执行计划。
（6）监控和调整索引
定期监控表的性能指标，如查询响应时间和吞吐量。如果发现索引效率低下，可以调整或重新设计索引以优化性能。
3.不要使用Select *
使用Select * 会导致：
①、不必要的磁盘I/O、导致网络延时
②、回表查询（一般我们数据库的底层存储的数据结构使用的是B+树来存储，在我们创建索引的时候，在底层也会为我们的索引创建一个B+树，这个B+树的结构一般是这样的
B+树只有底层会存储数据，并且底层数据结构为链表，这样也能优化查询速度，使用索引为什么会查询快，是因为我们查询数据的时候会直接找到我们的那颗索引B+树，但是这颗B+树并不会存储所有的字段，我有一张user表，如果使用Select *，会我们先查询索引B+树，但是这颗树中存在的数据，并不能满足我们的要求，结果就是拿到索引B+树的主键id，去主键索引树中查询完整的记录）
③、拖慢关联表查询速度
我们先了解几个概念，驱动表、被驱动表和join buffer，驱动表也就是我们的主表，被驱动表就是我们需要关联的表，这个join buffer就是我们存储我们驱动表过滤之后的结果集，在每次查询，我们会将被驱动表加载到内存中与join buffer里过滤出来的结果集进行匹配，由于匹配都是在内存中，可以显著减少被驱动表的I/O代价；但是join buffer它的大小是有限的啊！如果我们使用Select *，会将很多不常用的字段加入到join buffer里，数据量一大这个join buffer就和没有差不多了。
4.避免使用%做前缀查询
也就是不要使用select xxx,xxx from table_name where xxx like '%.com';，这样会导致索引失效
5.程序中不要在for循环里写访问数据的操作
我们先了解一个概念，就是缓冲池（Buffer pool）；
MySQL缓冲池是MySQL数据库中的一个缓存区域，用于存储经常使用的数据和查询结果。工作原理是将磁盘中的数据缓存到内存中，当需要查询数据时，优先从内存（MySQL缓冲池）中读取数据。如果内存中不存在需要的数据，再从磁盘中读取数据并缓存到内存（MySQL缓冲池）中如果不断的与数据库交互，MySQL缓冲池里的数据就会不断刷新，导致因为一个功能导致整套系统与数据交互都变慢。
6.delete和update导致碎片化(删除数据尽量使用truncate)
大量的update操作也会导致文件碎片化。当执行delete操作删除大量数据时，数据被标记为已删除，但并没有从磁盘空间中物理删除，因此会形成留白空间。如果没有对碎片进行整理，那么磁盘空间就会被长期占用。
解决方法：
①、先查看表碎片
SHOW TABLE STATUS LIKE 'table_name';
Data_free就是表碎片大小
②、回收碎片（会锁表，尽量在程序中设置定时任务处理）：
MyISAM回收碎片：
optimize table table_name;
InnoDB存储引擎回收碎片：
alter table 表名 engine=InnoDB;
7.避免字段隐式转换
查询条件数据类型必须与字段类型相匹配，不然会导致索引失效
8.union all 与 union
尽量使用union all 替代 union，因为后者在执行union操作之前会进行一个distinct的操作，数据量越大速度越慢
9.排序字段和分组字段加索引
因为排序字段和分组字段会在你的查询范围中，这两个字段加上索引还会减少回表的操作
10.in包含的值不宜太多
因为Mysql对于in的优化，会在内存中对in中的数据进行排序，也会消耗性能
11.in和exists
如果是in，会先执行in中的子查询，如果是exists会先执行外层查询；如果是子表数据量小，优先使用in；如果主表数量小，优先使用exists；因为数据量小的一方去与另一方关联，交互次数会更少，节省资源
12.开启慢查询日志
-- 开启全局慢查询日志
SET global slow_query_log = ON;
-- 指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中，一般默认值为10s
SET long_query_time = 10;
-- 设置慢查询日志文件名
SET global slow_query_log_file = 'database_name-slow-query.log';
-- 记录未使用索引的SQL
SET global log_queries_not_using_indexes = ON;
注：如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响
二、表结构优化
设计冗余字段
某个功能需要两张表的数据，但是将这两张表关联起来需要你连接5张表甚至更多，为了减少关联查询，我们可以在表中合理的设置冗余字段，达到我们优化查询速度的目的
设计聚合表
对于某些实时性要求不高的功能（报表、日志、数据统计等），我们可以采用空间置换时间的方式，去设计一个聚合表，在程序中采用定时任务将数据整合分析后存储到聚合表，方便后续加速关键数据的获取
避免使用触发器
触发器可以在数据发生变化时自动执行相关的操作，但是会增加额外的计算开销和复杂度，所有对数据的操作尽量在程序中就完成
设计数据类型
选择合适的数据类型可以提高数据的精度和存储效率。例如，使用整数代替浮点数可以节省存储空间，同时避免精度损失。
分表
水平拆分
比如我们的日志表，光一天的流水都能达到百万级，像这种我们就可以按月拆分日志表，减少每张表的数据量，在程序中处理查询逻辑，优化查询效率
垂直拆分
针对字段过多的表，我们就可以将不常用的字段与常用的字段拆分，分成两张表
三、参数优化
1.Buffer Pool优化
前面我们已经提过一嘴Buffer Pool了，现在我们详细的看看它是怎么优化的，首先在数据库启动的时候，就会根据配置的Buffer Pool区域大小向操作系统申请内存，申请完后就会按照默认缓存页的16KB和描述数据800字节的大小将Buffer Pool划分为一个一个的缓存页和对应的描述数据，只是此时缓存页和描述数据都是空的，只有当对数据进行操作、查询的时候，才会将数据从磁盘加载到Buffer Pool中来。
查看Buffer Pool大小
show variables like '%innodb_buffer_pool_size%';;
一般针对MySQL的专用服务器，我们可以修改为总内存的 60%~80%
在my.cnf 或 my.ini进行修改，innodb_buffer_pool_size = 750M
当然，在Buffer Pool里的数据，是需要更新的，但是每查一次数据就会把这次查询的数据更新到Buffer Pool中吗？
这显然是不现实的，MySQL通过某种算法，去筛选数据存储到Buffer Pool中的，这个算法就是LRU(least recently used-最近最少使用算法)，通过这个算法，我们就会把不常用的数据淘汰出去，常用的放到Buffer Pool中，达到我们快速查询的目的
LRU的工作原理：
①、从磁盘加载数据页到缓存页时，将缓存页的描述数据块放到LRU链表的头部；
②、如果某个缓存页的描述数据在尾部节点，只要后续对该缓存页进行了查询或者更新，都会将这个缓存页挪到LRU链表头部，也就是最新访问的缓存页一定在LRU头部；
③、当Buffer Pool中没有空闲缓存页了，就直接从LRU链表找到最尾部的缓存页进行刷盘即可，它一定是最近最少被访问的缓存页
*但是这里有一个缺点，吃不住全表扫描和Select 的狂轰乱炸
优化过后的LRU：
①、LRU链表分为热区域和冷区域；
②、数据页第一次被加载到缓存页的时候，缓存页会被放到冷区域的头节点；
③、如果这条数据在innodb_old_blocks_time内又再一次被访问，我们可以把它加入热区域，innodb_old_blocks_time（默认为1000，单位毫秒）；
④、进入热区域后，不会马上移动到头部，而是在后3/4区域先呆着，如果还被频繁的访问，才会移动到头部，减少了节点的频繁移动！
查看Buffer Pool相关参数
show variables like '%innodb_buffer_pool_%';
针对并发量较高的情况，我们还可以给数据库设置多个Buffer Pool
上面就是设置8个Buffer Pool，总共10个G的空间
2.日志优化
（1）redo log（重做日志）
防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log来恢复，实现事务持久性，当脏页写入磁盘后，redo log释放
innodb_log_file_size 一般设置为 innodb_buffer_pool_size / 4
redo log策略：
show variables like '%innodb_flush_log_at_trx_commit%';
0：每隔 1 秒写日志文件和刷盘操作（写日志文件 LogBuffer 、写缓存、刷缓存 、磁盘），最多丢失 1 秒数据；
1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 I/O 操作；
2：事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作；
对于实时性要求不高的数据，一般会设置成0或2
（2）binlog（同步日志、二进制日志）
记录着执行过的事务中的sql语句，在主库与从库的数据同步发挥着巨大的作用，一般选择开启
show variables like '%binlog%';
查看连接数据库的进程
show full processlist;
系统其它相关参数
SHOW VARIABLES;
Variable_name |Value |
----------------------------------------|----------------------------|
auto_increment_increment |1 |
auto_increment_offset |1 |
autocommit |ON |
automatic_sp_privileges |ON |
back_log |170 |
basedir |/opt |
big_tables |OFF |
bind_address |* |
binlog_cache_size |32768 |
binlog_checksum |CRC32 |
binlog_direct_non_transactional_updates |OFF |
binlog_error_action |IGNORE_ERROR |
binlog_format |STATEMENT |
binlog_gtid_simple_recovery |OFF |
binlog_max_flush_queue_time |0 |
binlog_order_commits |ON |
binlog_row_image |FULL |
binlog_rows_query_log_events |OFF |
binlog_stmt_cache_size |32768 |
binlogging_impossible_mode |IGNORE_ERROR |
block_encryption_mode |aes-128-ecb |
bulk_insert_buffer_size |8388608 |
character_set_client |utf8mb4 |
character_set_connection |utf8mb4 |
character_set_database |utf8 |
character_set_filesystem |binary |
character_set_results | |
character_set_server |utf8 |
character_set_system |utf8 |
character_sets_dir |/opt/share/mysql/charsets/ |
collation_connection |utf8mb4_general_ci |
collation_database |utf8_general_ci |
collation_server |utf8_general_ci |
completion_type |NO_CHAIN |
concurrent_insert |AUTO |
connect_timeout |10 |
core_file |OFF |
datadir |/home/data/mysql/ |
date_format |%Y-%m-%d |
datetime_format |%Y-%m-%d %H:%i:%s |
default_storage_engine |InnoDB |
default_tmp_storage_engine |InnoDB |
default_week_format |0 |
delay_key_write |ON |
delayed_insert_limit |100 |
delayed_insert_timeout |300 |
delayed_queue_size |1000 |
disconnect_on_expired_password |ON |
div_precision_increment |4 |
end_markers_in_json |OFF |
enforce_gtid_consistency |OFF |
eq_range_index_dive_limit |10 |
error_count |0 |
event_scheduler |OFF |
expire_logs_days |0 |
...
这里面的参数太多了，我们就挑几个来讲讲
thread_concurrency（并发线程数）
-- 一般设置为CPU数 * 2
SHOW VARIABLES like '%thread_concurrency%';
max_user_connection（最大连接数）
默认为0（无上限）
wait_timeout（闲置连接时间）
根据情况而定
四、硬件优化
简单粗暴
磁盘扩容，内存扩容
五、主从
这个相对简单，只需要制定好策略，读写分离，也能提升数据库的性能
推荐文章：主从配置
六、好用小工具
1.database-export
能支持最新的数据库版本，可以导出支持office2007版本以上的docx格式的文档
也可以导出excel，支持xlsx，也可以直接在浏览器预览
项目地址：https://github.com/PomZWJ/database-export/
输入数据库的相关信息，即可导出excel文档或在线浏览
总结
下次碰到面试官，别上来就光优化索引和分库主从了，先把单机性能拉满才是关键！
3802
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
