多慢才算慢，大部分业务场景 range 范围查询避免不了，针对索引的优化也是达到 range 级别是个平衡点，所以根据根据上文索引查询时间，慢查询时间比较合理的数值：

vi /etc/my.cnf.d/mysql-server.cnf 在[mysqld] 项目下添加如下配置 slow_query_log = 1slow-query-log-file = /var/lib/mysql/mysql_slow.loglong_query_time = 0.01log-queries-not-using-indexes = true

systemctl restart mysqld

执行时间最慢的 10 条的 sql：mysqldumpslow -s t -t 10 /var/lib/mysql/mysql_slow.log

找到慢 sql，怎么优化？explain：分析 sql 索引使用情况 详见 EXPLAIN 详解 show profiles：SET profiling = 1; 分析 sql 执行过程（实际工作中用的不多）

COUNT(*)查询

COUNT(常量) 和 COUNT(*)表示的是直接查询符合条件的数据库表的行数，会统计 NULL 的行 COUNT(列名)表示的是查询符合条件的列的值不为 NULL 的行数

COUNT(*)优化：选择最小的二级索引来进行 count 的查询优化，如果没有二级索引才会选择聚簇索引

优化 LIMIT 分页

LIMIT M,N 性能问题：全表扫描，Limit 原理是从结果集的 M 位置处取出 N 条输出,其余抛弃 1、利用二级索引快速分页 ids = select id from articles order by created_at desc limit 10000,10select * from articles where id in (ids)缺点：LIMIT M,N 问题已经存在，只是减缓，1000 万数据 1-2 秒 2、利用数据的首尾记录有由全索引扫描转为范围扫描 15 文章 21 2020-01-01 19:00:0012 文章 20 2020-01-01 18:00:0011 文章 18 2020-01-01 16:00:00

9 文章 17 2020-01-01 16:00:008 文章 15 2020-01-01 15:00:006 文章 14 2020-01-01 14:00:00

4 文章 13 2020-01-01 14:00:003 文章 12 2020-01-01 13:00:001 文章 11 2020-01-01 00:00:00 注意：11、9 和 6、4 时间相同

第一页：select * from articles order by created_at desc, id desc limit 3 第二页：select * from articles where created_at <= '2020-01-01 16:00:00' and (created_at < '2020-01-01 16:00:00' or id < 11) order by created_at desc, id desc limit 3

上一页：select * from articles where created_at >= '2020-01-01 16:00:00' and (created_at > '2020-01-01 16:00:00' or id > 9) order by created_at desc, id desc limit 3 当前第二页：select * from articles where created_at <= '2020-01-01 16:00:00' and (created_at < '2020-01-01 16:00:00' or id < 11) order by created_at desc, id desc limit 3 下一页：select * from articles where created_at <= '2020-01-01 14:00:00' and (created_at < '2020-01-01 14:00:00' or id < 6) order by created_at desc, id desc limit 3

缺点：只能上一页、下一页，没有页码

索引选择性是什么？

索引的选择性（Selectivity），指的是不重复的索引值（也叫基数，Cardinality）和表记录数（#T）的比值。选择性是索引筛选能力的一个指标。索引的取值范围是 0-1 ，当选择性越大，越接近 1，索引价值也就越大。索引选择性（Index Selectivity）= 基数（Cardinality）/ 总行数（#T）SQL = SELECT COUNT(DISTINCT(字段))/COUNT(*) AS Selectivity FROM 表名;

索引选择性与前缀索引

select count(distinct left(prefix_index, 1))/count() as sel1, count(distinct left(prefix_index, 2))/count() as sel2, count(distinct left(prefix_index, 3))/count() as sel3, count(distinct left(prefix_index, 4))/count() as sel4 from indexs

单列索引 VS 组合索引?（高并发倾向建立组合索引）当执行查询时，MySQL 只能使用一个索引。如果有三个单列的索引，MySQL 会试图选择一个限制最严格的索引。即使是限制最严格的单列索引，它的限制能力也肯定远远低于这三个列上的多列索引。

索引下推技术：

没有索引下推查询过程：存储引擎层在索引中把符合 union_index_a=4366964 的一条数据找出来，再回表查这一行记录的全部数据，再返回给 server 层判断 where 条件，把符合 union_index_c=1562544 的数据再过滤出来；索引下推查询过程：存储引擎层在索引中把符合 union_index_a=4366964 的一条数据找出来，判断数据是否符合 union_index_c=1562544 条件，不符合则过滤，再回表查这一行记录的全部数据，再返回给 server 层判断 where 条件；索引下推技术：减少回表次数，减少存储引擎层和 server 层的数据传输

代码优化

SQL 预编译(Prepared)

1、即时 SQL 一条 SQL 在 DB 接收到最终执行完毕返回，大致的过程如下：1. 词法和语义解析；2. 优化 SQL 语句，制定执行计划；3. 执行并返回结果；如上，一条 SQL 直接是走流程处理，一次编译，单次运行，此类普通语句被称作 Immediate Statements （即时 SQL）。2、预编译 SQL 但是，绝大多数情况下，某一条 SQL 语句可能会被反复调用执行，或者每次执行的时候只有个别的值不同（比如 select 的 where 子句值不同，update 的 set 子句值不同，insert 的 values 值不同）。每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，在 SQL 语句整个执行过程中，Optimizer 是最耗时的。所谓预编译语句就是将此类 SQL 语句中的值用占位符替代，可以视为将 SQL 语句模板化或者说参数化，一般称这类语句叫 Prepared Statements。

预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程。

此外预编译语句能防止 SQL 注入（思考原因）

大部分编程语言都支持预编译，可以通过 SQL 预编译提高数据库执行效率

使用 ORM 框架避免关联查询，规范 API 接口

拆表优化

MySQL 单表容量在 1 千万效率最好，超过 1 千万需要拆表

水平拆表

单表的数据能保持在一定的量级，有助于性能的提高切分的表结构相同，应用层改造较少，只需要增加路由规则即可

问题：拆开多少张表合适？10 张，50 张，100 张？分析：主要还是看业务增长量，比如用户订单表，一天 10 万订单，100 天数据量达到 1000 万，4 张表可以支撑 1 年，8 张表 2 年，以此类推（分表数量保持 4 的倍数，原因下文再说）

冷热数据分离将热数据剥离开来，减少热数据表的数据量，保证热数据的读写性能，冷数据相对来说访问量少，可以再业务上做针对的优化，如下图京东按时间维度拆分历史订单数据

垂直拆分页溢出 在字段很多的情况下，通过大表拆小表，更便于开发与维护，也能避免跨页问题，MYSQL 底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的开销。

拆分依据：1、将长度较短，访问频率较高的属性尽量放在一个表里，这个表称为主表 2、将字段较长，访问频率较低的属性尽量放在一个表里，这个表称为扩展表如果 1 和 2 都满足，还可以考虑第三点：（3）经常一起访问的属性，也可以放在一个表里

主机优化

主要针对 my.cnf 配置的优化

主要是 dba 的活，专业的事留给专业的人，列举比较重要、好理解的的参数

innodb_buffer_pool_size 参数 SELECT @@innodb_buffer_pool_size/1024/1024/1024;show global variables like 'innodb_buffer_pool_size';用于 innodb 数据和索引的缓存，默认 128M，innodb 最重要的性能参数。建议值：不超过物理内存的 80%（如果数据量小，可以是数据量+10%，数据量 20G，物理内存是 32G，这时候可以设置 buffer pool 为 22G。）innodb_buffer_pool_size=1G

缓冲池大小必须始终等于或者是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。如果将缓冲池大小更改为不等于或等于 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数的值，则缓冲池大小将自动调整为等于或者是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数的值

SELECT @@innodb_buffer_pool_chunk_size/1024/1024/1024; // 查看每个缓存池的大小 show global variables like 'innodb_buffer_pool_chunk_size';show global variables like 'innodb_buffer_pool_instances'; // 缓存池的数量 逻辑 CPU 数量

参数 innodb_dedicated_server=ON 来让 MySQL 自动探测服务器的内存资源，确定 innodb_buffer_pool_size, innodb_log_file_size 和 innodb_flush_method 三个参数的取值

innodb_flush_log_at_trx_commit 参数 show global variables like 'innodb_flush_log_at_trx_commit'; 默认是 1 控制事务的提交方式，控制日志刷新到硬盘的方式 0: 由 mysql 的 main_thread 每秒将存储引擎 log buffer 中的 redo log 写入到 log file，并调用文件系统的 sync 操作，将日志刷新到磁盘。（速度快，不安全）1：每次事务提交时，将存储引擎 log buffer 中的 redo log 写入到 log file，并调用文件系统的 sync 操作，将日志刷新到磁盘。（安全）2：每次事务提交时，将存储引擎 log buffer 中的 redo log 写入到 log file，并由存储引擎的 main_thread 每秒将日志刷新到磁盘。

当 innodb_flush_log_at_trx_commit 设置为 0，mysqld 进程的崩溃会导致上一秒钟所有事务数据的丢失。当 innodb_flush_log_at_trx_commit 设置为 2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。

sync_binlog 参数 show global variables like 'sync_binlog'; 默认是 10：这时候的性能是最好的，但是风险也是最大的。因为一旦系统 crash，在 binlog_cache 中的所有 binlog 信息都会被丢失 1：表示每次事务提交，MySQL 都会把 binlog 刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失 1 个事务的数据，设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多

innodb_flush_log_at_trx_commit=1 和 sync_binlog=1 是最安全的，在 mysqld 服务崩溃或者服务器主机 crash 的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双 1 模式会导致频繁的 io 操作，因此该模式也是最慢的一种方式。

"双 1 模式"适合数据安全性要求非常高，而且磁盘 IO 写能力足够支持业务，比如订单、交易、充值、支付消费系统。双 1 模式下，当磁盘 IO 无法满足业务需求时，推荐的做法是 innodb_flush_log_at_trx_commit=2，sync_binlog=N (N 为 500 或 1000)

这就是 MySQL 著名的"双 1 模式"

思考：主从复制，在半同步下并且不允许退化为异步复制的情况下使用 innodb_flush_log_at_trx_commit=2，sync_binlog=N 的方案是不是可以保证性能又能保证数据不丢失？靠谱不？

主从分离（针对读的优化）

扩展阅读：

基于 biglog 日志点复制当发生故障，需要主从切换，需要找到 binlog 和 position 点，然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。基于 GTID 的复制（MySQL>=5.7 推荐使用）GTID 全局事务 ID（Global Transaction ID），是一个已提交事务的编号，并且是一个全局唯一的编号。代替了基于 binlog 和 position 号的主从复制同步方式

MySQL 的三种复制方式 1、asynchronous 异步复制 2、semisynchronous 半同步复制 3、lossless replication 无损复制 增强版的半同步复制

半同步复制 与异步复制不同的是，采用半同步复制机制时，Slave 会向 Master 发送一个 ACK 确认消息，同时 Master 的 commit 操作也会被阻塞，只有收

【一线大厂Java面试题解析+核心总结学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开：qq.cn.hn/FTf 免费领取

到了 Slave 的确认消息之后，Master 才会把数据的修改结果返回给客户端。

binlog 格式：1、row level: 仅保存记录被修改细节，不记录 sql 语句上下文相关信息优点 (默认)2、statement level: 每一条会修改数据的 sql 都会记录在 binlog 中 3、mixed level: 以上两种 level 的混合使用经过前面的对比，可以发现 row level 和 statement level 各有优势，如能根据 sql 语句取舍可能会有更好地性能和效果；Mixed level 便是以上两种 leve 的结合。

并行复制：（解决主从复制数据延迟问题）库间并发库间并发的理论依据是这样的 ---- 一个实例内可能会有多个库(schema)，不同的库之间没有什么依赖关系，所以在 slave 那边为每一个库(schema)单独起一个 SQL 线程，这样就能通过多线程并行复制的方式来提高主从复制的效率。这个理论听起来没问题，但是事实上一个实例也就一个业务库，所以这种库间并发就没什么作用了；也就是说这个方式的适用场景比较少，针对这个不足直到"组提交"才解决！组提交组提交的理论依据是这样的 --- 如果多个事务他们能在同一时间内提交，这个就间接说明了这个几个事务锁上是没有冲突的，也是就说他们各自持有不同的锁，互不影响；逻辑上把几个事务看一个组，在 slave 以"组"为单位分配给 SQL 线程执行，这样多个 SQL 线程就可以并行跑了；而且不在以库为并行的粒度，效果上要比"库间并发"要好一些。WriteSetWriteSet 是站在"组提交"的基础之间建立起来的，在在 master 上做的自适应打包分组

mysql8.0 主从同步：1、binlog format：row level2、基于 GTID 的复制 3、无损复制 4、WriteSet 并行复制

show global variables like 'query_cache_type';主：query_cache_type=OFFmysql8.0 已经废弃查询缓存这个功能

写库拆分（针对写的优化）

误区：主主复制提高不了写速度，只能解决主单点问题，还没见过这种架构应用在实际工作中

二叉树分库分表主要解决扩容数据库服务器时避免按行进行数据拆分，以 2 的倍数扩容数据库主机数量，先按库拆分，再按表拆分，每次只需要转移 1/2 的数据；常规拆数据过程：

二叉树分库分表拆数据过程：

按 8 个库 16 张表的规则分 order 表

// 用户数据库、表映射关系

$userId=用户id;//一个数据库实例$conn = 数据库连接;$conns[0]=$conn;

// 两个数据库实例$conn1=数据库连接1;$conn2 = 数据库连接 2;$conns[0]=$conn1;$conns[1]=$conn2;

$dbConn,$dbName, $tableName=getConn($userId, $conns)

func getConn($userId,$conns) {$dbCount=8;$tableCount = 16;$dbHostCount=len($conns);$dbIndex=$userId % $dbCount$dbHostCount;$tableIndex=$userId / 10 % $tableCount+1;$dbConn = $conns[$dbIndex - 1]$dbName="d{b}_{"}+$dbIndex$tableName="orde{r}_{"}+$tableIndexreturn $dbConn,$dbName, $tableName}

总结：单表插入主要是建数据速度有瓶颈，多表插入的时候主要是磁盘 IO 有瓶颈，多表保持 3-5 张比较合适

网上有文章说分到最后可以一库一表，通过我们分表压测的结果来判断，一库一表发挥不出 mysql 全部的性能，一库四表一个小集群比较合适，8 个库 16 张表的话，最后可以分到 32 个小集群，每个集群 2 万的插入 QPS，32*2 = 64 万插入 QPS；