1. SQL 执行顺序

1.1 Mysql 逻辑架构图

• 

1.1.1 查询SQL的执行步骤

连接器 -> 查询缓存 -> 分析器（语法词法分析） -> 优化器 -> 执行器

ps: 查询缓存，在有更新语句时，跟该表相关的缓存都会清空；
    所以大部分情况下，不要设置查询缓存（可通过设置参数query_cache_type = DEMAND禁止使用缓存）；
    也可以通过SQL_CACHE 显示指定缓存
    mysql> select SQL_CACHE * from T where ID=10；
    MySql 8.0以后，查询缓存模快已经被去掉

1.1.2 更新SQL的执行步骤

• 第一步后查询SQL的执行步骤一样
• 唯一要注意的是：在一个表上有更新的时候，跟这个表有关的查询缓存会失效
• 与查询流程不一样的是，更新流程还涉及两个重要的日志模块
• redo log（重做日志）和 binlog（归档日志）

具体内容可以见该文档第7章

1.2 所有的查询语句都是从from开始执行的，在执行过程中每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入

• 1.from
• 2.on
• 3.join
• 4.where
• 5.group by (开始使用select中的别名，后面的语句都可以使用)
• 6.avg，sum…
• 7.having
• 8.select
• 9.distinct
• 10.order by limit

1.3 where 和 on 中使用逻辑表达式的区别

• on 是建立关联关系，where是对关联关系进行筛选
• on 中被表达式过滤掉的值还可以通过join将其找回，where是最终过滤

2. Mysql 的 SQL 优化

2.1 慢sql的常见原因

2.1.1 无索引或者索引失效

2.1.2 锁等待

• InnoDB 存储引擎：支持行锁和表锁
• MyISAM 存储引擎：只支持表锁
• 基于表锁的数据库操作，会导致 SQL 阻塞等待，从而影响执行速度
• 在一些更新操作（insert\update\delete）大于或等于读操作的情况下，MySQL 不建议使用 MyISAM 存储引擎
• 行锁相对表锁而言，粒度更细，并发能力更强，但有个缺点，就是死锁

2.1.2.1 行锁升级为表锁的情况

• MySQL 认为如果对一张表使用大量行锁，会导致事务执行效率下降，从而可能造成其它事务长时间锁等待和更多的锁冲突问题发生，致使性能严重下降，所以 MySQL 会将行锁升级为表锁
• 行锁是基于索引加的锁，如果我们在更新操作时，条件索引失效，那么行锁也会升级为表锁

2.1.3 不恰当的SQL语句

• 在大数据表中使用 LIMIT M,N 分页查询
• 对非索引字段进行排序
• 在MyISAM 存储引擎上 使用 SELECT * 或者 SELECT COUNT(*)

2.2 优化sql语句的步骤

2.2.1 通过 EXPLAIN 分析 SQL 执行计划

• 

2.2.1.1 id

• 每个执行计划的id，如果是一个联合查询，会有多个id

2.2.1.2 select_type

• 表示SELECT 查询类型。
• SIMPLE (普通查询，即没有联合查询和子查询)
• PRIMARY (主查询)
• UNION (UNION中后面的查询)
• SUBQUERY (子查询)等

2.2.1.3 table

• 当前执行计划查询的表，如果给表起了别名，则显示别名

2.2.1.3 partitions

• 访问分区表信息（分库分表时才有用）

2.2.1.4 type

• 表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标
• 结果值：system > const > eq_ref > ref > range > index > ALL

2.2.1.4.1 system/const

• 表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据
• 

2.2.1.4.2 eq_ref

• 使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件
• 

2.2.1.4.3 ref

• 非唯一索引扫描，还可见于唯一索引最左原则匹配扫描
• 

2.2.1.4.4 range

• 索引范围扫描，比如，<，>，between 等操作
• 

2.2.1.4.5 index

• 索引全表扫描，此时遍历整个索引树
• 

2.2.1.4.6 ALL

• 表示全表扫描，需要遍历全表来找到对应的行
• 

2.2.1.5 possible_keys

• 可能使用到的索引

2.2.1.6 key

• 实际使用到的索引

2.2.1.7 key_len

• 当前使用的索引的长度

2.2.1.8 ref

• 关联 id 等信息

2.2.1.8 rows

• 查找到记录所扫描的行数

2.2.1.9 filtered

• 查找到所需记录占总扫描记录数的比例

2.2.1.10 Extra

• 额外的信息
• Using Index 代表使用了索引
• Using where 代表使用了where 过滤条件

2.2.2 通过 Show Profile 分析 SQL 执行性能

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n] //n 代表查询的id
[LIMIT row_count [OFFSET offset]]

type参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示CPU的相关开销信息 
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数) 
| SWAPS：显示swap交换次数的相关开销信息

• EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况
• Profile 则 深入到 MySQL 内核中，从执行线程的状态和时间来分析
• MySQL 是在 5.0.37 版本之后才支持 Show Profile 功能，可通过 select @@have_profiling 查询
• 新版本的Mysql 都是默认开启 Profile 功能的
• Show Profiles 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15 条记录，我们可以重新设置 profiling_history_size 增大该存储记录，最大值为 100
• 
• 获取到 Query_ID 之后，我们再通过 Show Profile for Query ID 语句，就能够查看到对应 Query_ID 的 SQL 语句在执行过程中线程的每个状态所消耗的时间了
• 

2.2.3 show processlist 查看当前sql状态,显示用户正在运行的线程

• root用户，可以看到全部线程运行情况
• 普通的activiti用户只能看到自己的，如果想看到所有的，单独给其赋予 PROCESS 权限
• show processlist 显示的信息时来自information_schema.processlist 表
• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vCJwoRfd-1631411603678)(https://note.youdao.com/yws/api/personal/file/E8BD2F8B403846C9A445D6DF843F66A1?method=download&shareKey=e50190036d741c45f390fea293738210)]

2.2.3.1 id

• 线程Id，这个id 是 information_schema.processlist 表的主键

2.2.3.2 User

• 指启动这个线程的用户

2.2.3.3 Host

• 记录了发送请求的客户端的 IP 和 端口号。
• 通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求

2.2.3.4 DB

• 当前执行的命令是在哪一个数据库上
• 如果没有指定数据库，则该值为 NULL

2.2.3.5 Command

• 是指此刻该线程正在执行的命令
• 一般就是休眠（sleep），查询（query），连接（connect）
• 通常代表资源未释放，如果是通过连接池，sleep状态应该恒定在一定数量范围内
• 例子： 因客户端数据输出时（特别是输出到用户终端）未及时关闭数据库连接，导致因网络连接速度产生大量sleep连接，在网速出现异常时，数据库too many connections挂死
• 例子解读： 数据查询和执行通常只需要不到0.01秒，而网络输出通常需要1秒左右甚至更长，原本数据连接在0.01秒即可释放，但是因为客户端程序未执行close操作，直接输出结果，那么在结果未展现在用户桌面前，该数据库连接一直维持在sleep状态！

2.2.3.5.1 Binlog Dump

• 主节点正在将二进制日志 ，同步到从节点

2.2.3.5.2 Change User

• 正在执行一个 change-user 的操作

2.2.3.5.3 Close Stmt

• 正在关闭一个Prepared Statement 对象

2.2.3.5.4 Connect

• 一个从节点连上了主节点

2.2.3.5.5 Connect Out

• 一个从节点正在连主节点

2.2.3.5.6 Create DB

• 正在执行一个create-database 的操作

2.2.3.5.7 Daemon

• 服务器内部线程，而不是来自客户端的链接

2.2.3.5.8 Debug

• 线程正在生成调试信息

2.2.3.5.9 Delayed Insert

• 该线程是一个延迟插入的处理程序

2.2.3.5.10 Drop DB

• 正在执行一个 drop-database 的操作

2.2.3.5.11 Execute

• 正在执行一个 Prepared Statement

2.2.3.5.12 Fetch

• 正在从Prepared Statement 中获取执行结果

2.2.3.5.13 Field List

• 正在获取表的列信息

2.2.3.5.14 Init DB

• 该线程正在选取一个默认的数据库

2.2.3.5.15 Kill

• 正在执行 kill 语句，杀死指定线程

2.2.3.5.16 Long Data

• 正在从Prepared Statement 中检索 long data

2.2.3.5.17 Ping

• 正在处理 server-ping 的请求

2.2.3.5.18 Prepare

• 该线程正在准备一个 Prepared Statement

2.2.3.5.19 ProcessList

• 该线程正在生成服务器线程相关信息

2.2.3.5.20 Query

• 该线程正在执行一个语句

2.2.3.5.21 Quit

• 该线程正在退出

2.2.3.5.22 Refresh

• 该线程正在刷表，日志或缓存；或者在重置状态变量，或者在复制服务器信息

2.2.3.5.23 Register Slave

• 正在注册从节点

2.2.3.5.24 Reset Stmt

• 正在重置 prepared statement

2.2.3.5.25 Set Option

• 正在设置或重置客户端的 statement-execution 选项

2.2.3.5.26 Shutdown

• 正在关闭服务器

2.2.3.5.27 Sleep

• 正在等待客户端向它发送执行语句

2.2.3.5.28 Statistics

• 该线程正在生成 server-status 信息

2.2.3.5.29 Table Dump

• 正在发送表的内容到从服务器

2.2.3.6 Time

• 表示线程处于当前状态的时间，单位是秒

2.2.3.7 State

• 当前连接的sql语句的状态，和 Command 对应
• state只是语句执行中的某一个状态
• 一个sql语句，以查询为例，可能需要经过 copying to tmp table，Sorting result，Sending data 等状态才可以完成

2.2.3.7.1 Checking table

• 正在检查数据表（这是自动的）

2.2.3.7.2 Closing tables

• 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。
• 这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中

2.2.3.7.3 Connect Out

• 从服务器正在连接主服务器

2.2.3.7.4 Copying to tmp table on disk

• 由于临时结果集大于 tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存
• 索引及现有结构无法涵盖查询条件，才会建立一个临时表来满足查询要求，产生巨大的恐怖的i/o压力
• 通常与连表查询有关，建议逐渐习惯不使用连表查询

2.2.3.7.5 Creating tmp table

• 正在创建临时表以存放部分查询结果

2.2.3.7.6 deleting from main table

• 服务器正在执行多表删除中的第一部分，刚删除第一个表

2.2.3.7.7 deleting from reference tables

• 服务器正在执行多表删除中的第二部分，正在删除其他表的记录

2.2.3.7.8 Flushing tables

• 正在执行 FLUSH TABLES，等待其他线程关闭数据表

2.2.3.7.9 Killed

• 发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求
• MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉
• 如果该线程被其他线程锁住了，那么kill请求会在锁释放时马上生效

2.2.3.7.10 Locked

• 被其他查询锁住了 （表锁）
• 有更新操作锁定
• Innodb 引擎 可以很好的减少 Locked的出现，但切记，更新操作一定要正确使用索引，不然行锁会升级到表锁

2.2.3.7.11 Sending data

• 正在处理 SELECT 查询的记录，同时正在把结果发送给客户端
• 这是从物理磁盘获取数据的进程，如果你的影响结果集较多，那么就需要从不同的磁盘碎片去抽取数据
• 一般而言，如果sending data连接过多，通常是某查询的影响结果集过大，也就是查询的索引项不够优化

2.2.3.7.12 Sorting for group

• 正在为 GROUP BY 做排序

2.2.3.7.13 Sorting for group

• 正在为 ORDER BY 做排序
• 和Sending data类似，结果集过大，排序条件没有索引化，需要在内存里排序，甚至需要创建临时结构排序

2.2.3.7.14 Opening tables

• 正尝试打开一个表
• 这个过程应该会很快，除非受到其他因素的干扰。例如，在执行 ALTER TABLE 或 LOCK TABLE 语句行完以前，数据表无法被其他线程打开

2.2.3.7.15 Removing duplicates

• 正在执行一个 SELECT DISTINCT 方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。
• 因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端

2.2.3.7.16 Reopen table

• 已经释放锁，关闭数据表，正尝试重新打开数据表
• 获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁

2.2.3.7.17 Repair by sorting

• 修复指令正在排序以创建索引

2.2.3.7.18 Repair with keycache

• 修复指令正在利用索引缓存一个一个地创建新索引
• 它会比 Repair by sorting 慢些

2.2.3.7.19 Searching rows for update

• 正在将符合条件的记录找出来以备更新
• 它必须在 UPDATE 要修改相关的记录之前就完成了

2.2.3.7.20 Sleeping

• 正在等待客户端发送新请求

2.2.3.7.21 System lock

• 正在等待取得一个外部的系统锁
• 如果当前没有运行多个 mysqld 服务器同时请求同一个表，那么可以通过增加 –skip-external-locking参数来禁止外部系统锁。

2.2.3.7.22 Upgrading lock

• INSERT DELAYED 正在尝试取得一个锁表以插入新记录

2.2.3.7.23 Updating

• 正在搜索匹配的记录，并且修改它们

2.2.3.7.24 User Lock

• 正在等待 GET_LOCK()

2.2.3.7.25 Waiting for tables

• 该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构
• 然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表
• 以下几种情况下会产生这个通知：
• FLUSH TABLES tbl_name
• ALTER TABLE
• RENAME TABLE
• REPAIR TABLE
• ANALYZE TABLE
• OPTIMIZE TABLE

2.2.3.7.26 waiting for handler insert

• NSERT DELAYED 已经处理完了所有待处理的插入操作，正在等待新的请求

2.2.3.7.27 Waiting for net, reading from net, writing to net

• 如大量出现，迅速检查数据库到前端的网络连接状态和流量

2.2.3.7 Info

• 一般记录的是线程执行的语句（sql）。默认只显示前100个字符
• 也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist

2.2.3.8 相关案例

• Mysql里面执行 show processlist 出现很多 Sleep 状态的线程 由于程序设计的Bug,导致目前这个项目使用的数据库中有很多Sleep状态的线程。找了很多解决办法,还没发现最终有效的解决方案，临时解决方案如下:

#编写shell文件,如killSleepProcess.sh

# 每隔120 秒 执行一次 done
[plain] while : do n=`mysqladmin -uroot -p*** processlist | grep -i sleep | wc -l` date=`date +%Y%m%d/[%H:%M:%S]` echo $n if [ "$n" -gt 10 ] then for i in `mysqladmin -uroot -p*** processlist | grep -i sleep | awk '{print $2}'` do mysqladmin -uroot -p*** kill $i done echo "sleep is too many i killed it" >> /root/tmp/sleep.log echo "$date : $n" >> /root/tmp/sleep.log fi sleep 120 

• 按客户端 IP 分组，看哪个客户端的链接数最多

select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;

• 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

• 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀

select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

2.3 常用的sql优化

2.3.1 优化分页查询

• 通常我们是使用 LIMIT M,N + 合适的 order by 来实现分页查询
• 这种分页在没有索引的情况下，需要做大量的文件排序操作，性能很差
• 在有索引的情况下，刚开始查询效率比较理想，但越往后，分页查询性能就越差（偏移量 M 在分页越靠后的时候，值就越大，数据库检索的数据也就越多）
• 例如 LIMIT 10000,10 这样的查询，数据库需要查询 10010 条记录，最后返回 10 条记录。也就是说将会有 10000 条记录被查询出来没有被使用到

2.3.1.1 利用子查询优化分页查询

• 用一个id值来作为where条件，这也是为什么大部分表都要有个自增长的主键id的原因

# limit m,n -- 大概0.018s
select * from `demo`.`order` order by order_no limit 10000, 20; 

# 子查询，遍历索引的范围跟limit m,n差不多，主查询还扫描了更多的行
# 但执行时间却减少了 -- -- 大概0.004s
# 这是因为查询返回的数据只要20行了，而以前的是要10020行
select * from ``order` where id >= (select id from `order` order by order_no limit 10000, 1)  limit 20;

2.3.2 SELECT COUNT(*) – 主要针对不同存储引擎

• COUNT() 是一个聚合函数，主要用来统计行数
• COUNT(列) 统计某一列的数量（不统计NULL值的行）
• COUNT(1) 和 COUNT(*) 没有明显区别，在拥有主键的情况下，他们都是利用主键列实现了行数的统计
• 没带任何条件，COUNT(*)，MyISAM 的查询速度要明显快于 InnoDB（这是因为 MyISAM 存储引擎记录的是整个表的行数(MyISAM 引擎不需要支持事务)，在 COUNT(*) 查询操作时无需遍历表计算，直接获取该值即可。而在 InnoDB 存储引擎中就需要扫描表来统计具体的行数）*
• 带了 WHERE 条件的化，MyISAM 和 InnoDB 没有区别 ，都需要通过扫描表来进行行数统计
• 效率：count(*) = count(1) > count(primary key) > count(column)

2.3.3 尽量少使用 SELECT *

• InnoDB 在默认创建主键时会创建主键索引，而主键索引属于聚簇索引，即在存储数据时，索引是基于 B + 树构成的，具体的行数据则存储在叶子节点。
• MyISAM 默认创建的主键索引、二级索引以及 InnoDB 的二级索引都属于非聚簇索引，即在存储数据时，索引是基于 B + 树构成的，而叶子节点存储的是主键值

## 一张order表 存在 order_no、status 两列组成的组合索引
## 此时需要根据订单号查询一张订单表的 status
select * from order where order_no='xxx’; //先查组合索引，通过组合索引获取到主键Id，再通过主键id去主键索引中获取对应行所有列的值

select order_no,status from order where order_no='xxx’; //只会查询组合索引，通过组合索引获取到对应的 order_no 和 status 的值

2.3.4 where 子句优化

2.3.4.1 在 where 及 order by 涉及的列上建立索引

2.3.4.2 尽量避免使用 != 和 <> 、not

• 此时mysql 存储引擎会放弃索引而进行全表扫描

2.3.4.3 尽量避免在 where 子句中对字段进行 null 值判断

• 此时mysql 存储引擎会放弃索引而进行全表扫描
• 可以在字段上设置默认值，确保表中列没有null值

2.3.4.4 尽量避免在 where 子句中使用 or 来连接条件

• 此时mysql 存储引擎会放弃索引而进行全表扫描
• 用 union/union all 来替代

select id from t where num=10 or num=20 

可替换为：

select id from t where num=10 
union all 
select id from t where num=20 

2.3.4.5 尽量避免使用 %前缀、%前缀% 模糊查询

• 这种查询会导致索引失效而进行全表扫描，但可以使用 LIKE “name%”
• 可以使用全文检索解决

//对要查询的字段，建立全文索引
ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_column` (`column`);

//使用全文索引sql示例
select id,name from `table_name` where match(`column`) against("具体值" in boolean mode);

2.3.4.6 尽量避免使用 in 或者 not in 结合子查询语句，应该是具体值（但也不宜过多），否则会导致索引失效

• 对于连续值，能用 between 就不要用 in
• 可用 exsits 代替 in

select * from 表A where id in (select id from 表B)
上面SQL语句相当于
select * from 表A where exists(select * from 表B where 表B.id=表A.id)

• 区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键）
• exists，那么以外层表为驱动表，先被访问；适合外表小而内表大的情况
• IN，那么先执行子查询，以内表为驱动表；适合外表大而内表小的情况
• 关于 not in 和 not exists，推荐使用 not exists，不仅仅是效率问题，not in可能存在逻辑问题
• 如何高效的写出一个替代not exists的SQL语句？

原SQL语句：
select colname … from A表 where a.id not in (select b.id from B表)

高效的SQL语句：
select colname … from A表 Left join B表 on a.id = b.id where b.id is null

2.3.4.7 尽量避免在 where 子句中使用参数

• 会导致索引失效，因为sql只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。

select id from t where num=@num 
可以改为强制查询使用索引： 
select id from t with(index(索引名)) where num=@num 

2.3.4.8 尽量避免在 where 子句 对字段进行表达式操作

• 会导致索引失效

select id from t where num/2=100 
应改为: 
select id from t where num=100*2 

2.3.4.9 尽量避免在 where子句 中对字段进行函数操作

• 会导致索引失效

select id from t where substring(name,1,3)='abc'--name以abc开头的id 
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id 
应改为: 
select id from t where name like 'abc%' 
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' 

2.3.4.10 避免隐式类型转换

• where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型

2.3.5 尽量用union all代替union

• union 和 union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟

2.3.6 不使用ORDER BY RAND() – 随机排序

• RAND() 函数效率极低，因为这样会导致数据列被多次扫描

select id from `dynamic` order by rand() limit 1000;
上面的SQL语句，可优化为：
select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;

2.3.7 对于组合索引来说，要遵守最左前缀法则

• A、B、C 三个字段的组合索引，有效的只有 A,A-B,A-C,A-B-C

2.3.8 必要时可以使用 force index 来强制查询走某个索引

• 有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引

SELECT columnName... from tableA  FORCE INDEX(`索引名`)

2.3.9 索引并不是越多越好

• 索引固然可以提高相应的 select 的效率
• 但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引
• 一个表的索引数最好不要超过6个
• 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引 （走索引要看需要查询的数据是否少于全表的30%）

2.3.10 尽量使用数字型字段

• 若只含数值信息的字段尽量不要设计为字符型（字符型会降低查询和连接的性能，并会增加存储开销）
• 存储引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

2.3.11 尽可能的使用 varchar/nvarchar 代替 char/nchar

• 变长字段存储空间小，可以节省存储空间
• 对于查询来说，在一个相对较小的字段内搜索效率显然要高些

2.3.12 注意范围查询语句

• 对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效

联合索引：idx = cA,CB
select * from table where cA > 10 and cB = 'XXX';
//此时列B cB是没有用到索引的

• Memory 引擎 范围查询，将会使索引失效

2.3.13 JOIN优化

• 只要是关联查询，首先都是进行笛卡尔积，然后根据连接条件和where条件进行条件过滤
• LEFT JOIN 左表为驱动表 （保留左表满足条件的数据）
• RIGHT JOIN 右表为驱动表 （保留右表满足条件的数据）
• INNER JOIN MySQL会自动找出那个数据少的表作用驱动表

2.3.13.1 MySQL中没有full join，可以用以下方式来解决

select * from A left join B on B.name = A.name where B.name is null union all select * from B;

2.3.13.2 尽量使用inner join，避免left join

• 参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表

2.3.13.3 合理利用索引

• 被驱动表的索引字段作为on的限制字段

2.3.13.4 利用小表去驱动大表

• 如果能够减少驱动表的话，姐可以减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数

2.3.13.5 巧用 STRAIGHT_JOIN

• STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。
• 在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join，也就是需要优化的语句原本是inner join的，并且是mysql 自己选定的驱动表不符合预期

# t1 的 FilterID 字段设置了索引，t1和t2的 CommonID 也建立了索引
select t1.* from Table1 t1 INNER JOIN Table2 t2 on t1.CommonID = t2.CommonID
where t1.FilterID = 1
// 此时由于Mysql自动选择的驱动表为 Table2 即 Table2 驱动 Table1，导致FilterID索引失效，查询效率降低
// 这是可以用 STRAIGHT_JOIN 来替换，强制是驱动表为 Table1
select t1.* from Table1 t1 STRAIGHT_JOIN Table2 t2 on t1.CommonID = t2.CommonID
where t1.FilterID = 1

2.4 慢查询记录配置

2.4.1 开启慢查询记录配置

set global slow_query_log='ON'; //开启慢SQL日志
set global slow_query_log_file='/var/lib/mysql/test-slow.log';//记录日志地址
set global long_query_time=1;//最大执行时间

2.4.2 慢 SQL 的功能查询

Show variables like 'slow_query%'; //是否开启了记录慢 SQL 的功能
Show variables like 'long_query_time'; //最大的执行时间

3. Mysql的索引

3.1 索引存储结构

• 
• 在创建表时，无论使用 InnoDB 还是 MyISAM 存储引擎，默认都会创建一个主键索引，而创建的主键索引默认使用的是 B+Tree 索引
• InnoDB 默认创建的主键索引是聚簇索引（Clustered Index），其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。
• MyISAM 默认创建的主键索引是非聚簇索引（Secondary Index）

3.1.1 聚簇索引（Clustered Index）

• 聚簇索引中的叶子节点则记录了主键值、事务 id、用于事务和 MVCC 的回流指针以及所有的剩余列(行数据)

3.1.2 非聚簇索引/辅助索引（Secondary Index）

• 索引中每一个叶子节点仅仅记录的是每行数据的物理地址，即行指针

3.1.3 回表

• 用辅助索引进行查询，则会先检索辅助索引中的 B+ 树的 对应的值，找到对应的叶子节点，获取主键值，然后再通过聚簇索引中的 B+ 树检索到对应的叶子节点，然后获取整行数据
• 就是相检索了两个索引，相当于当前索引查询出来的，是另一个索引的值，然后通过另一个索引才能查询到想要的数据

3.2 索引优化

3.2.1 覆盖索引优化查询 （避免回表）

• 覆盖索引： 从辅助索引中查询得到记录，而不需要通过聚簇索引查询获得，即不需要回表，且减少大量I/O操作
• 常说的 组合索引 就是一种覆盖索引，因为组合索引的特点就是：如果组合索引中有这些数据了，就不回再次检索主键索引了，避免了回表
• 统计数量时，如果存在辅助索引，则会通过查询辅助索引来统计行数，减少 I/O 操作

• 使用了idx_order 这个辅助索引来统计行数

3.2.2 自增字段做主键优化查询

• InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+ 树的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的
• 使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为不需要重新移动数据，因此这种插入数据的方法效率非常高
• 如果使用非自增主键，每次插入新数据，都需要移动其他数据。甚至会出现页分裂（从一个页面复制数据到另一个页面）
• 页分裂，还可能造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率

3.2.3 前缀索引优化

• 前缀索引：使用某个字段中字符串的前几个字符建立索引
• 索引文件是存储在磁盘中的，而磁盘中最小分配单元是页，通常一个页的默认大小为 16KB，假设我们建立的索引的每个索引值大小为 2KB，则在一个页中，我们只能记录 8 个索引值
• 减小索引字段大小，可以增加一个页中存储的索引项，有效提高索引的查询速度
• order by 无法使用前缀索引
• 无法把前缀索引用作覆盖索引。

3.2.3 防止索引失效

• 可以查看上面的sql优化 相关的where子句优化和其他优化里面导致索引失效的

4. 数据库事务

• 是数据库系统执行过程中的一个逻辑处理单元，保证一个数据库操作要么成功，要么失败
• MyISAM 引擎不支持事务

4.1 ACID 特性

4.1.1 A (Atomicity) 原子性

• 是指一个事务操作不可分割，要么成功，要么失败，不能有一半成功一半失败的情况
• Mysql 通过回滚机制，实现原子性

4.1.2 C (Consistency) 一致性

• 是指这些数据在事务执行完成这个时间点之前，读到的一定是更新前的数据，之后读到的一定是更新后的数据，不应该存在一个时刻，让用户读到更新过程中的数据
• Mysql 通过 undo log（事务回滚日志） + MVCC 保证一致性 事务开始和结束的过程不会其它事务看到 为了并发可以适当破坏一致性

4.1.3 I (Isolation) 隔离性/独立性

• 是指一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对正在进行的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
• Mysql 通过 不同的事务隔离级别来实现隔离性

4.1.4 D (Durability) 持久性

• 是指一个事务一旦完成提交，后续的其他操作和故障都不会对事务的结果产生任何影响
• Mysql 通过 binlog + redo log 两阶段提交，实现持久性

4.2 InnoDB 的锁机制

4.2.1 表锁

• 开销小，加锁快（直接锁住整个表，不用考虑其他条件）
• 不会发生死锁现象，锁粒度大
• 发生锁冲突的概率高，并发度低
• 适用于以查询为主，只有小量按索引条件更新数据的应用
• 共享读锁，独占写锁
• 对表进行读，不会阻塞其他线程的读操作，但会阻塞其他线程的写操作
• 对表进行写，会阻塞其他线程的读和写操作
• Mysql 在执行查询语句（SELECT）前，会自动给涉及到的表加读锁
• Mysql 在执行更新操作（UPDATE、DELETE、INSERT）前，会自动给涉及到的表加写锁

4.2.2 行锁

• 开销大，加锁慢（需要过滤查询条件）
• 会发生死锁现象，锁粒度小
• 发生锁冲突的概率低，并发度高
• 适用于有大量按索引条件并发更新少量数据，同时又有并发查询的应用
• 行锁是通过给索引项加锁实现的，只用通过索引条件查询数据，行锁才会起作用，否则适用表锁

4.2.2.1 共享锁 （S）

• 允许一个事务读数据，不允许修改数据
• 阻止其他事务获得相同行数据集的排它锁，只能获取共享锁

select ... from tableName where ... lock in share mode;

4.2.2.2 排他锁 （X）

• 修改数据时加的锁，可以读取和修改数据
• 阻止其他事务获得相同行数据集的排它锁 和 共享锁

select ... from tableName where ... for update;

4.2.3 引申

4.2.3.1 悲观锁

• 在整个数据处理过程中，将数据处于锁定状态
• 依靠数据库排它锁实现 （for update）
• 影响程序的并发性，使的数据库性能开销增大

4.2.3.2 乐观锁

• 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测
• 通过在数据库表中，增加一个int 类型的 版本号（version） 字段来实现
• 每次更新时，将当前版本号和前一次取出来的版本号进行对比，如果相同，则进行更新，版本号+1；如果不同，则不给予更新

4.2.3.3 MVCC （多版本并发控制）实现高性能事务

• 对普通的 Select 不加锁，如果读取的数据正在执行 Delete 或 Update 操作，这时读取操作不会等待排他锁的释放，而是直接利用 MVCC 读取该行的数据快照
• 数据快照是指在该行的之前版本的数据，而数据快照的版本是基于 undo 实现的，undo 是用来做事务回滚的，记录了回滚的不同版本的行记录
• MVCC 避免了对数据重复加锁的过程，大大提高了读操作的性能

4.2.2.3.1 InnoDB 下 MVCC 具体实现

• 乐观锁机制
• 在每行记录后面保存两个隐藏的列，分别保存行的创建时间和删除时间（存储的并不是实际的时间值，而是系统版本号（可以理解为事务id））
• 每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号，就是该事务id
• 数据库 可重复读 就是用 MVCC 实现的
• 参考文档-轻松理解MYSQL MVCC 实现机制

4.2.2.3.1.1 SELECT

• InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的
• 行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除

4.2.2.3.1.2 DELETE

• 为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识

4.2.2.3.1.2 UPDATE

• InnoDB 执行UPDATE，实际上是新插入了一行记录
• 并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间

4.3 并发事务带来的问题

4.3.1 数据丢失

• 

4.3.1.1 解决方式

• 通过悲观锁/乐观锁解决

4.3.2 脏读

• 

4.3.2.1 解决方式

• 将事务的隔离级别 设为已提交读（Read Committed）或者 可重复读 （Repeattable Read）或者可序列化

4.3.3 不可重复读

• 

4.3.3.1 解决方式

• 将事务的隔离级别 设为可重复读 （Repeattable Read）

4.3.4 幻读

• 
• 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（仅专指“新插入的行”）
• 在innoDB 可重复读 隔离级别下，只有当前读会存在幻读；快照读（非阻塞读，伪 MVCC）不会

4.3.4.1 解决方式

• 将事务的隔离级别 设为可序列化（Serializable）
• 利用锁间隙算法来防止幻读
• InnoDB的伪MVCC机制 + next-key的gap锁（即间隙锁） – 只在RR隔离级别下才会生效
• 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系

4.3.4.2 当前读

• 加了锁的增删改查就是当前读
• 意思是当前操作的是最新记录，其他的并发事务不能修改当前记录，对当前记录加锁
• 当前读（查询）：select…lock in share mode，select…for update
• 当前读（增、删、改）：update、insert、delete

4.3.4.2 快照读

• 不加锁的非阻塞读，也就是select操作
• 在Serializable隔离级别下，快照度会退化为当前读
• 在RC隔离界别下，当前读和快照读读取的都是同一版本
• 在RR隔离界别下，当前读读到的是最新版本数据，而快照度可能读取到的是历史版本数据
• 如果在增、删、改完成之后，再去查询快照读，则此时读取到的是最新版本的数据。
• 如果在增、删、改之前进行了快照读，在增、删、改之后继续快照读，则读到的就是旧版本数据

4.4 Mysql的事务隔离级别

4.4.1 未提交读（Read Uncommitted）

• 在事务A读取数据时，事务B读取数据加了共享锁，修改数据时，加了排他锁
• 会导致脏读、不可重复读、幻读

4.4.2 已提交读（Read Committed）

• 在事务 A 读取数据时增加了共享锁，一旦读取，立即释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。
• 也就是说，事务 A 在读取数据时，事务 B 只能读取数据，不能修改。当事务 A 读取到数据后，事务 B 才能修改
• 避免了脏读，但仍会存在不可重复读、幻读

4.4.3 可重复读 （Repeattable Read） – Mysql的默认事务隔离级别

• 在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁
• 也就是说，事务 A 在没有结束事务时，事务 B 只能读取数据，不能修改。当事务 A 结束事务，事务 B 才能修改
• 避免了脏读、不可重复读，但仍会存在幻读
• 在InnoDB下，可重复读 的 MVCC 可以解决幻读

4.4.4 可序列化（Serializable）

• 在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了表级排他锁，直到事务结束才释放锁
• 避免了脏读、不可重复读和幻读
• 隔离级别越来越高的同时，并发性会越来越低

4.5 优化高并发事务

4.5.1 结合业务场景，使用低级别事务隔离

4.5.2 避免行锁升级表锁

• 行锁一定要基于索引来使用

4.5.3 控制事务的大小，减少锁定的资源量和锁定时间长度 （锁粒度）

• 锁粒度要细
• 例如：
  
• 虽然这些操作在同一个事务，但锁的申请在不同时间，只有当其他操作都执行完，才会释放所有锁
• 因为扣除库存是更新操作，属于行锁，这将会影响到其他操作该数据的事务，所以我们应该尽量避免长时间地持有该锁，尽快释放该锁
• 所以执行顺序1 才是最优的 （尽量减少了获取/更新库存这个锁持有的时间）

4.6 Mysql的死锁

• Mysql死锁的概率并不大
• 死锁的四个必要条件：
• 1.互斥
• 2.占有且等待
• 3.不可强占用
• 4.循环等待

4.6.1 Mysql死锁产生的原因

• 只有行锁才会产生死锁现象
• 两个事务相互等待对方释放锁
• 主要发生场景：
• 加排它锁的 select + insert，具体见 4.6.1.3
• 使用不同类别索引的 update，具体见 4.6.2.4

4.6.1.1 行锁的具体实现算法

• 只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock
• 在 Select、Update、Delete，除了基于唯一索引的查询之外，其他索引查询都会获取 gap lock 或者 next-key lock

4.6.1.1.1 record lock

• 专门对索引项加锁

4.6.1.1.2 gap lock

• 对索引项之间的间隙加锁

4.6.1.1.3 next-key lock

• 是前面两种的组合，对索引项以其之间的间隙加锁

4.6.1.2 行锁的兼容性

• 

4.6.1.3 具体案例

• 利用数据库来校验幂等性
• 首先将Mysql的事务提交改为手动提交，默认为自动提交

// 查看自动提交事务是否开启
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

// 将自动提交设置为0，即改为手动提交
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

// 表结构
CREATE TABLE `order_record`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` int(11) DEFAULT NULL,
  `status` int(4) DEFAULT NULL,
  `create_date` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_order_status`(`order_no`,`status`) USING BTREE
) ENGINE = InnoDB

• 
• 图中的Select 操作，由于 order_no 列为非唯一索引，而且Mysql默认事务隔离级别为RR（可重复读），所以Select操作的加锁类型为 gap lock，gap范围为（4，+∞）
• 由行锁的兼容性可知，Select 获取的 gap lock 不会导致阻塞，但当执行插入Sql时，会在插入间隙上再次获取 插入意向锁（insert intension lock），其实也是一种 gap lock，但它与gap lock是冲突的，所以当其它事务持有该间隙的 gap lock 时，需要等待其它事务释放 gap lock 之后，才能获取到插入意向锁
• 以上事务 A 和事务 B 都持有间隙 (4,+∞）的 gap lock，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁

4.6.1.4 加锁的规则

• 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间
• 原则 2：查找过程中访问到的对象才会加锁
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

4.6.2 如何避免死锁

4.6.2.1 设置事务等待的超时时间

• 当一个事务的等待时间超过设置的某一阈值，就对这个事务进行回滚，另一个事务可以继续执行
• 这种方法简单有效，在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的
• MySQL默认开启了死锁检测机制，当检测到死锁后会选择一个最小(锁定资源最少得事务)的事务进行回滚
• Innodb提供了wait-for graph算法来主动进行死锁检测，我们可以通过innodb_deadlock_detect = on 打开死锁检测

4.6.2.2 设置唯一索引

• 将相关列设置为唯一索引
• 这种方式唯一的缺点就是当遇到重复创时会抛出异常

4.6.2.3 在允许幻读和不可重复读的情况下，尽量使用 RC (已提交读） 事务隔离级别，可以避免 gap lock 导致的死锁问题

4.6.2.4 更新表时，尽量使用主键更新

• 如果两个更新事务使用了不同的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待
• 由于本身两个事务是互斥，也就构成了以上死锁的四个必要条件了
• 例子：
  
• 出现死锁的原因（前提，都是更新的同一行数据）：
  

4.6.2.5 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率

4.6.2.6 用其他方式去实现幂等校验

• redis
• zookeeper

5. 分区、分表分库

• 当单表遇到性能瓶颈时，优先考虑分区，当分区也没有解决时，再考虑分表分库
• 单表的性能瓶颈：5000W行（比较追求性能的到500W行就要开始考虑性能了） 或者 100G以上
• 单库的性能瓶颈：数据在2T以上

5.1 分区

• 分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分
• 就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理
• 分区对应用来说是完全透明的，不影响应用的业务逻辑
• 分区是一种逻辑上的水平分表，在物理层面还是一张表。分区是MySQL的一个插件Plugin功能，将一张大表的数据在数据库底层分成多个分区文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd)

5.1.1 数据库文件格式

5.1.1.1 MyISAM

• .frm: 表结构
• .MYD: 表数据
• .MYI：索引文件

5.1.1.2 InnoDB

• .frm: 表结构
• .ibd: 表数据 + 索引

5.1.2 分区优势

• 和单个磁盘或者文件系统分区相比，可以存储更多数据
• 优化查询。在 where 子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及 sum() 和 count() 这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果
• 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
• 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量

5.1.3 分区类型

-- 查看mysql版本
select version();

-- 查看分区插件是否激活 partition active
show plugins;

-- 对于低版本的MySQL，如果InnoDB引擎要想分区成功，需要在my.conf中设置innodb_file_per_table=1 设置成独立表空间
-- 独立表空间：每张表都有对应的.ibd文件
innodb_file_per_table=1

5.1.3.1 Range 分区

• 最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。
• 最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型

5.1.3.1.1 语法

create table <table> (
	// 字段
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段) (
  partition <分区名称> values less than (Value),
  partition <分区名称> values less than (Value),
  ...
  partition <分区名称> values less than maxvalue
);

-- 也可以在创建表之后进行分区
alter table <table> partition by RANGE(id) (
	PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN MAXVALUE 
);

• range: 表示按范围分区
• 分区字段：表示要按照哪个字段进行分区，可以是一个字段名，也可以是多某个字段进行表达式运算，如：year(create_time)，使用range最终的值必须是数字
• 分区名称：要保证不同，也可以采用P0,P1,P2这样的名称
• less than：表示小于
• Value： 表示要小于某个具体的值，如 less than（10），那么分区字段小于10的都会被分到这个分区
• maxvalue：表示一个最大的值

5.1.3.1.2 range columns

• columns分区支持一个或者多个字段作为分区键，不支持表达式作为分区键

create talbe rc3 (
	a int,
	b int
)
partition by range columns(a, b) (
	partition p01 values less than (0, 10),
	partition p02 values less than (10, 10),
	partition p03 values less than (10, 20),
	partition p04 values less than (10, 35),
	partition p05 values less than (10, maxvalue),
	partition p06 values less than (maxvalue, maxvalue),
);

insert into rc3(a, b) values(1, 10);

select (1, 10) < (10, 10) from dual;

-- 根据结果存放到p02分区上了
select
	partition_name,
	partition_expression,
	partition_description,
	table_rows
from information_schema.partitions
where table_schema = schema() and table_name = 'rc3';	

• range columns分区键的比较(元组的比较)其实就是多列排序，先根据a字段排序再根据b字段排
• 根据排序结果来分区存放数据，和range单字段的分区排序的规则实际上是一样的

5.1.3.2 List 分区

• LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合
• 适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列

5.1.3.2.1 语法

create table <table> (
	// 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) (
  partition <分区名称> values IN (Value1，Value2, Value3),
  ...
  partition <分区名称> values IN (Value4, Value5),
);

5.1.3.2.2 list columns

• 类似与 range columns

5.1.3.3 Hash 分区

• 主要用来分散热点读，确保数据在预先确定个数的分区中可能的平均分布
• 基于指定的分区个数，对分区键进行Hash算法，将数据分配到不同的分区
• HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数
• hash分区只需要指定要分区的字段和要分成几个分区

5.1.3.3.1 语法

-- 常规Hash
create table <table> (
	// 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY HASH(expr) //常规Hash分区，取模运算，MOD(expr,num)
PARTITIONS <num>;

-- 线性Hash
create table <table> (
	// 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY LINEAR HASH(expr) //线性Hash分区，分区函数是一个线性的2的幂的运算法则
PARTITIONS <num>;

• expr：是一个字段值或者基于某列Hash后返回的一个整数，可以是mysql中有效的任何函数或者其它表达式，只要他们能返回一个整数
• num：表示分区数量
• 常规Hash：数据平均分布在每个分区；但分区维护不方便，需要重新计算并重新进行分区
• 线性Hash：分区维护(增加、删除、合并、拆分分区)时，mysql能够处理的更加迅速；缺点是，各个分区的数据分布不均衡。

5.1.3.4 Key分区

• 和Hash分区类似
• 与Hash分区的不同点：
• KEY分区允许多列，而HASH分区只允许一列
• 分区的字段可以是非int类型,如字符串、日期等类型
• 如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列
• KEY分区对象必须为列，而不能是基于列的表达式
• KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值

5.1.3.4.1 语法

create table <table> (
	// 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY key(expr)
//PARTITION BY key() -- 不指定expr默认首选主键作为分区键，在没有主键的情况下会选择非空唯一键作为分区键
//partition by linear key(expr)
PARTITIONS <num>;

5.1.3.5 子分区

• 是分区表中对每个分区的再次分割，又被称为复合分区
• 支持对range和list进行子分区，子分区即可以使用hash分区也可以使用key分区
• 适用于保存非常大量的数据记录

5.1.3.5.1 语法案例

-- 根据年进行分区
-- 再根据天数分区
-- 3个range分区(p0,p1,p2)又被进一步分成2个子分区，实际上整个分区被分成了 3 x 2 = 6个分区
create table ts (
	id int, 
	purchased date
) 
partition by range(year(purchased))
subpartition by hash(to_days(purchased)) subpartitions 2 
(
	partition p0 values less than (1990),
	partition p0 values less than (2000),
	partition p0 values less than maxvalue
);

5.1.4 管理分区

5.1.4.1 添加分区

-- range添加新分区
alter table <table> add partition(partition p4 values less than MAXVALUE);

-- list添加新分区
alter table <table> add partition(partition p4 values in (25,26,28));

5.1.4.2 拆分分区

alter table access_log reorganize partition p4 into(
    -> partition s0 values less than(to_days('20190104')),
    -> partition s1 values less than(to_days('20190105'))
    -> );

5.1.4.3 删除分区

-- 删除list或者range分区(同时删除分区对应的数据)
alter table <table> drop partition <分区名称>;

5.1.4.4 合并分区

alter table access_log reorganize partition s0,s1 into ( 
    partition p4 values less than (to_days('20190105')) 
);

-- range合并分区
ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);

-- list合并分区
ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));

5.1.5 注意事项

• 1.MySQL分区中如果存在主键或唯一键，则分区列必须是这些键，不能是其他键（否则判断主键或唯一时，需要扫描所有分区）
• 2.分区字段最好不要为 NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL,虽然分区时会对 NULL 特殊处理（把null值当做零值或者一个最小值），但很有可能处理的不符合你的意愿
• 3.最大分区数目不能超过1024
• 4.不支持外键
• 5. 最好是对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列。（key分区支持对非整型列进行分区）
• 6.分区表不影响自增列

5.1.6 分区时的常见错误

5.1.6.1 A PRIMARY KEY must include all columns in the table's partitioning function

• 分区的字段必须是要包含在主键当中
• 这样的话判断主键是否唯一就可以在单个分区内部完成，否则就需要跨所有的分区

5.1.6.2 MAXVALUE can only be used in last partition definition

• MAXVALUE只能用在最后一个分区
• Range表分区后最好不要带MAXVALUE分区，否则无法增加分区。或者就只能重新分区了

5.1.6.3 Table has no partition for value 737425

• 这是因为分区的范围没有包含所有可能的记录的值

5.1.7 为什么一些公司 或者 DBA 要禁用分区？

• MySQL的表分区实现偏底层，定制不灵活且性能不是很好，维护成本高
• 性能不好的表现：
• 范围（range）分区，服务器需要扫描所有的分区定义的列表来确定具体的分区
• 表分区在操作数据过滤之前，是需要打开并锁住所有底层表的，这个过程是在分区过滤之前发生的，非常耗性能

5.2 分表分库

• 能不分表分库就不要分表分库
• 一旦分表，我们可能会涉及到多表的分页查询、多表的 JOIN 查询，从而增加业务的复杂度
• 一旦分库了，除了跨库分页查询、跨库 JOIN 查询，还会存在跨库事务的问题。这些问题无疑会增加我们系统开发的复杂度。

5.2.1 垂直切分

5.2.1.1 垂直分库

• 主要是指 根据业务来分库
• 不同的业务使用不同的数据库

5.2.1.2 垂直分表

• 指根据一张表中的字段，将一张表划分为两张表
• 划分规则：将一些不经常使用的字段拆分到另一张表中

5.2.2 水平切分

5.2.1.1 水平分表

• 将表中的某一列作为切分的条件，按照某种规则（Range 或 Hash 取模）来切分为更小的表

5.2.1.2 水平分库

• 水平分表只是在一个库中，如果存在连接数、I/O 读写以及网络吞吐等瓶颈
• 就要将水平切分的表分布到不同机器的数据库中

5.2.3 根据切分，数据库的分类

5.2.3.1 单库单表

• 平时业务开发时，优先考虑单库单表
• 如果数据量比较大，且热点数据比较集中，历史数据访问很少，可以考虑表分区

5.2.3.2 单库多表

• 如果访问热点数据分散，基本上所有的数据都会访问到，我们可以考虑单库多表

5.2.3.3 多库多表

• 如果并发量比较高、海量数据以及每日新增数据量巨大，我们可以考虑多库多表

5.2.4 拆分案例

• 有一张订单表（order） 和 订单详情表（order_detail），每天的数据增长量在60W单，平时还会有一些促销类活动，订单增长量在千万单。
• 为了提高系统的并发能力，考虑将order 和 order_detail做分库分表
• 因为用户一般查询的是最近的订单信息，所以热点数据比较集中，我们还可以考虑用表分区来优化单表查询
• 通常order的分库分表要么基于订单号 Hash 取模实现，要么根据用户 ID Hash 取模实现。订单号 Hash 取模的好处是数据能均匀分布到各个表中，而缺陷则是一个用户查询所有订单时，需要去多个表中查询
• 由于订单表用户查询比较多，此时我们应该考虑使用用户 ID 字段做 Hash 取模，对订单表进行水平分表。如果需要考虑高并发时的订单处理能力，我们可以考虑基于用户 ID 字段 Hash 取模实现分库分表

5.2.5 分库分表的中间件

• sharding-jdbc
• mycat

5.3 分库分表后遇到的挑战/问题

5.3.1 分布式事务问题

• 场景：在提交订单时，除了创建订单之外，还需要扣除相应的库存。
• 而订单表和库存表由于垂直分库，位于不同库中，这时需要分布式事务来保证提交订单时事务的完整性
• 们解决分布式事务有两种通用的方式：两阶事务提交（2PC）以及补偿事务提交（TCC）
• 一些中间件已经帮我们封装好了这两种方式的实现，例如 Spring 实现的 JTA，目前阿里开源的分布式事务中间件 Fescar（现已改名为Seata），就很好地实现了与 Dubbo 的兼容

5.3.1.1 两阶段事务提交 （2PC）

5.3.1.2 补偿事务提交 （TCC）

5.3.2 跨节点 JOIN 查询问题

• 用户在查询订单时，我们往往需要通过表连接获取到商品信息，而商品信息表可能在另外一个库中，这就涉及到了跨库 JOIN 查询
• 一般通过 冗余表 或 冗余字段 来优化跨库 JOIN 查询

5.3.3 跨节点分页查询问题

• 当用户在订单列表中查询所有订单时，可以通过用户 ID 的 Hash 值来快速查询到订单信息；而运营人员在后台对订单表进行查询时，则是通过订单付款时间来进行查询的，这些数据都分布在不同的库以及表中，此时就存在一个跨节点分页查询的问题了
• 通常建议使用两套数据来解决跨节点分页查询问题
• 一套是基于分库分表的用户单条或多条查询数据
• 一套则是基于Elasticsearch、Solr 存储的订单数据（为了不影响业务性能，一般使用异步消息来实现ES、Solr 订单数据的新增和修改）

5.3.4 全局主键 ID 问题

• 在分库分表后，需要单独设计全局主键，避免不同表和库中的主键重复问题

5.3.4.1 UUID实现全局ID

• 是最方便快捷的方式；随机生成一个 32 位 16 进制数字，这种方式可以保证一个 UUID 的唯一性，水平扩展能力以及性能都比较高
• 使用 UUID 最大的缺陷就是：它是一个比较长的字符串，连续性差，如果作为主键使用，性能相对来说会比较差

5.3.4.2 基于Redis分布式锁实现一个递增的主键ID

• 可以保证主键是一个整数且有一定的连续性
• 但分布式锁存在一定的性能消耗

5.3.4.3 基于雪花算法，生成全局唯一 ID

• Twitter 开源的分布式 ID 生产算法——snowflake
• 分别截取时间、机器标识、顺序计数的位数组成一个 long 类型的主键 ID
• 这种算法可以满足每秒上万个全局 ID 生成
• 不仅性能好，而且低延时
• 是一个64位的二进制正整数，然后转换成10进制的数
• 
• 1位标识符：始终是0，由于long基本类型在Java中是带符号的，最高位是符号位，正数是0，负数是1，所以id一般是正数，最高位是0。
• 41位时间戳：41位时间截不是存储当前时间的时间截，而是存储时间截的差值（当前时间截 - 开始时间截 )得到的值，这里的的开始时间截，一般是我们的id生成器开始使用的时间，由我们程序来指定的。
• 10位机器标识码：可以部署在1024个节点，如果机器分机房（IDC）部署，这10位可以由 5位机房ID + 5位机器ID 组成。
• 12位序列：毫秒内的计数，12位的计数顺序号支持每个节点每毫秒(同一机器，同一时间截)产生4096个ID序号

5.3.4.3.1 优点

• 简单高效，生成速度快
• 时间戳在高位，自增序列在低位，整个ID是趋势递增的，按照时间有序递增
• 灵活度高，可以根据业务需求，调整bit位的划分，满足不同的需求

5.3.4.3.2 缺点

• 依赖机器的时钟，如果服务器时钟回拨，会导致重复ID生成
• 在分布式环境上，每个服务器的时钟不可能完全同步，有时会出现不是全局递增的情况

5.3.5 扩容问题

• 一旦动态增加表了，就会涉及到数据迁移问题
• 最开始设计表数据量时，尽量使用 2 的倍数来设置表数量。
• 当我们需要扩容时，也同样按照 2 的倍数来扩容，这种方式可以减少数据的迁移量
• 通过一致性哈希来解决扩容问题

6. 表设计优化

• 如果表字段非常多（可以拆表，将共用的字段拆为另一张表（共用表），goods,goods_sku），易变动，不方便统一的字段，建议使用redis等Nosql来存储
• 高并发查询时，可以将热点数据，提前放入缓存，用缓存代替数据库操作，提高并发性能。比如：将促销活动的库存更新到缓存中，通过缓存来查询商品的实时库存，并且通过分布式锁来实现库存扣减、锁定库存；通过异步消息去更新数据库中的库存
• 数据增加比较快的表，要提前进行分库分表的设计。比如：提前用全局唯一ID方案来生成主键ID
• 对一些复杂的业务查询，要增加冗余字段，避免多余的 JOIN 查询

6.1 表设计相关准则

6.1.1 不允许物理删除 （通过一个字段标记，是否删除就行）

6.1.2 每张表必须要有id、创建时间、创建人、更新时间、更新人 这几个字段

6.1.3 对核心表的操作要有操作历史记录，要知道谁？啥时候？操作了啥

6.2 为什么目前互联网公司不建议使用外键关联，而是建议逻辑上实现各个表之间的关联？

• 使用外键，操作复杂，性能低下。处理数据非常麻烦，update/delete 要求先后顺序，由于外键的约束检测也会影响查询性能。
• 高并发时，使用外键容易造成死锁
• 外键最关键的作用就是保证数据的一致性和完整性。一致性完全可以通过程序在逻辑上去保证，完整性的话，就算有冗余，也是被允许的

7. Mysql 的参数设置

• 数据库主要是用来存取数据的，而存取数据涉及到了磁盘 I/O 的读写操作，所以数据库系统主要的性能瓶颈就是 I/O 读写的瓶颈了
• Mysql 是一个非常灵活，高度可定制化的数据库，可以根据需求来调整参数，定制性能最优的数据库

7.1 Mysql 体系结构

• 

7.1.1 第一层：客户端连接器层

• 包括了数据库连接、授权认证、安全管理等
• 这一层会引入 数据库连接池，为接入的连接请求提高线程处理效率

7.1.2 第二层：Server层，主要实现 SQL 的一些基础功能

• 包括 SQL 解析、优化、执行以及缓存等

7.1.3 第三层：存储引擎层，主要负责数据的存取

• Buffer 缓存

7.1.4 第四层：数据存储层

• 主要负责将数据存储在文件系统中，并完成与存储引擎的交互

7.2 SQL的处理

7.2.1 查询语句

• SQL接口接收到请求后，会先检查SQL是否命中 Cache 缓存中的数据
• 如果命中，则直接返回缓存中的结果
• 否则，进入解析器进行解析
• 解析器对 SQL 进行语法以及词法分析
• 优化器会生成多种执行计划方案，并选择最优的方案执行
• 执行器 检查连接用户是否有该表的执行权限
• 没有，返回无权限的错误
• 有，则查看Buffer 中是否存在该缓存，
• 存在则获取锁，查询表数据；
• 否则重新打开表文件，通过接口调用相应的存储引擎处理，这时存储引擎就会进入到存储文件系统中获取相应的数据，并返回结果集

7.2.2 更新语句

• 数据库更新 SQL 的执行流程其实跟查询 SQL 差不多
• 只不过执行更新操作的时候多了记录日志的步骤
• Mysql 会将操作日志记录到 binlog （归档日志） 中，这个步骤所有存储引擎都有
• InnoDB 除了要记录 binlog 之外，还需要多记录一个 redo log（重做日志）
• 在执行更新操作时，首先会查询相关的数据，之后通过执行器执行更新操作，并将执行结果写入到内存中，同时记录更新操作到 redo log 的缓存中，此时 redo log 中的记录状态为 prepare（并写入磁盘），并通知执行器更新完成，随时可以提交事务
• 执行器收到通知后会执行 binlog 的写入操作，此时的 binlog 是记录在缓存中的，写入成功后 binlog 会刷新到磁盘文件中
• 然后会调用引擎的提交事务接口，更新redo记录状态为 commit
• 之后，将内存中的 redo log刷新到磁盘文件中
• 重启恢复是需要检查binlog来确认redo log中处于prepare的事务是否需要commit

1. prepare阶段 
2. 写binlog 
3. commit
当在2之前崩溃时
重启恢复：发现没有commit，回滚。备份恢复：没有binlog 。一致

当在3之前崩溃
重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

• InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘。当一条记录更新时，InnoDB 会先把记录写入到 redo log buffer 中，并更新内存数据
• WAL 机制的两个优势：
• 1.redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快
• 组提交机制（针对并发而言，因为一组并发到来时，可以只进行一次写磁盘操作（日志逻辑序列号（log sequence number，LSN），在这一组中，最先完成的事务，会根据最大LSN（小于它的操作都会进行fsync），其他请求在内存写了后，直接返回），可以大幅度降低磁盘的 IOPS 消耗

7.2.2.1 二段式提交

• redo prepare; 写 binlog；redo commit
• redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致
• redo log 记录 做了什么改动（比如把某个字段从0改成了1）
• binlog 记录 是怎么修改的（记录sql语句或者 记录更新前后的行）

7.2.2.2 binlog

• 记录更新（包含insert，update，delete）的操作日志
• Mysql的 Sever层实现
• 追加写数据
• 所有存储引擎都有

7.2.2.2.1 binlog的写入机制

• 事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中，并清空 binlog cache
• 
• write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
• fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。
• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

7.2.2.3 redo log

• innoDB 引擎独有
• redolog是循环写的，不持久保存
• 主要是为了解决 crash-safe （要保证数据库存储要么成功，要么失败，不能有数据丢失）问题而引入的
• crash-safe是崩溃恢复，就是原地满血复活；binlog恢复是制造一个影分身（副本）出来

7.2.2.3.1 redo log的写入机制

• 事务执行过程中，先把日志写到 redo log buffer，事务提交的时候，再把 redo log buffer 写到 redo log 文件中
• innodb_flush_log_at_trx_commit=0 表示每次事务提交时都只是把 redo log 留在 redo log buffer 中
• innodb_flush_log_at_trx_commit=1 表示每次事务提交时都将 redo log 直接持久化到磁盘
• innodb_flush_log_at_trx_commit=2 表示每次事务提交时都只是把 redo log 写到 page cache
• InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

7.3 内存调优

7.3.1 Query Cache

• 缓存的是 SQL 语句和对应的结果集
• 这里的缓存是以查询 SQL 的 Hash 值为 key，返回结果集为 value 的键值对，判断一条 SQL 是否命中缓存，是通过匹配查询 SQL 的 Hash 值来实现的
• Query Cache 虽然可以优化查询操作，但也仅限于不常修改的数据，如果一张表数据经常进行新增、更新和删除操作，则会造成 Query Cache 的失效率非常高，从而导致频繁地清除 Cache 中的数据，给系统增加额外的性能开销

7.3.1.1 优化参数设置

• 
• 可以通过设置合适的 query_cache_min_res_unit 来减少碎片

//这个参数最合适的大小和应用程序查询结果的平均大小直接相关，可以通过以下公式计算所得
（query_cache_size - Qcache_free_memory）/ Qcache_queries_in_cache

// Qcache_free_memory 和 Qcache_queries_in_cache 的值可以通过以下命令查询：
show status like 'Qcache%'

7.3.2 Buffer 缓存

7.3.2.1 MyISAM 存储引擎参数设置调优

• 使用 key buffer 缓存索引块
• 对数据库则没有缓存，是直接存储在磁盘中的
• 可以通过 key_buffer_size 来设置key buffer缓存的大小。而它的大小并不是越大越好。正如我前面所讲的，key buffer 缓存设置过大，实际应用却不大的话，就容易造成内存浪费，而且系统也容易发生 SWAP 页交换。
• 一般建议将服务器内存中可用内存的 1/4 分配给 key buffer

//更准确地评估 key buffer 的设置是否合理，我们还可以通过缓存使用率公式来计算：
(1-(key_blocks_unused*key_cache_block_size))/key_buffer_size

// key_blocks_unused 表示未使用的缓存簇（blocks）数
// key_cache_block_size 表示 key_buffer_size 被分割的区域大小
// key_blocks_unused*key_cache_block_size 则表示剩余的可用缓存空间（一般来说，缓存使用率在 80% 作用比较合适）

7.3.2.2 InnoDB 存储引擎参数设置调优

• InnoDB Buffer Pool（简称 IBP）是 InnoDB 存储引擎的一个缓冲池
• 它不仅存储了表索引块，还存储了表数据

7.3.2.2.1 innodb_buffer_pool_size

• IBP 默认的内存大小是 128M，我们可以通过参数 innodb_buffer_pool_size 来设置 IBP 的大小，IBP 设置得越大，InnoDB 表性能就越好，但不能过大，过大会导致系统SWAP页交互
• MySQL 推荐配置 IBP 的大小为服务器物理内存的 80%

//我们也可以通过计算 InnoDB 缓冲池的命中率来调整 IBP 大小：
(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request)*100

//果我们将 IBP 的大小设置为物理内存的 80% 以后，发现命中率还是很低，此时我们就应该考虑扩充内存来增加 IBP 的大小

7.3.2.2.2 innodb_buffer_pool_instances

• InnoDB 中的 IBP 缓冲池被划分为了多个实例
• 对于具有数千兆字节的缓冲池的系统来说，将缓冲池划分为单独的实例可以减少不同线程读取和写入缓存页面时的争用，从而提高系统的并发性
• 该参数项仅在将 innodb_buffer_pool_size 设置为 1GB 或更大时才会生效。
• **在windows 32 位的操作系统中：**如果 innodb_buffer_pool_size 的大小超过 1.3GB，innodb_buffer_pool_instances 默认大小就为 innodb_buffer_pool_size/128MB；否则，默认为 1。
• 而在其它操作系统中，如果 innodb_buffer_pool_size 大小超过 1GB，innodb_buffer_pool_instances 值就默认为 8；否则，默认为1
• 建议指定 innodb_buffer_pool_instances 的大小，并保证每个缓冲池实例至少有 1GB 内存
• 建议 innodb_buffer_pool_instances 的大小不超过 innodb_read_io_threads + innodb_write_io_threads 之和，建议实例和线程数量比例为 1:1

7.3.2.2.3 innode_read_io_threads/innodb_write_io_threads

• 在默认情况下，MySQL 后台线程包括了主线程、IO 线程、锁线程以及监控线程等
• 读写线程数量值默认为 4，也就是总共有 8 个线程同时在后台运行

( innodb_read_io_threads + innodb_write_io_threads ) = innodb_buffe_pool_instances

我们可以通过以下查询来确定读写比率：
SHOW GLOBAL STATUS LIKE 'Com_select';//读取数量
SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace', 'Com_delete');//写入数量

• 如果程序 读大于写，我们应该考虑将读线程的数量设置得大一些，写线程数量小一些；否则，反之。

7.3.2.2.4 innodb_log_file_size

• InnoDB 中有一个 redo log 文件，InnoDB 用它来存储服务器处理的每个写请求的重做活动
• 执行的每个写入查询都会在日志文件中获得重做条目，以便在发生崩溃时可以恢复更改
• 日志文件大小已经超过我们参数设置的日志文件大小时，InnoDB 会自动切换到另外一个日志文件，由于重做日志是一个循环使用的环，在切换时，就需要将新的日志文件脏页的缓存数据刷新到磁盘中（触发检查点）
• 在大多数情况下，我们将日志文件大小设置为 1GB 就足够了

7.3.2.2.5 innodb_log_buffer_size

• 这个参数决定了 InnoDB 重做日志缓冲池的大小，默认值为 8MB
• 可以通过增大该参数来减少写入磁盘操作，从而提高并发时的事务性能

7.3.2.2.6 innodb_flush_log_at_trx_commit

• 这个参数可以控制重做日志从缓存写入文件刷新到磁盘中的策略，默认值为 1
• 当设置该参数为 0 时，InnoDB 每秒钟就会触发一次缓存日志写入到文件中并刷新到磁盘的操作，这有可能在数据库崩溃后，丢失 1s 的数据
• 当设置该参数为 1 时，则表示每次事务的 redo log 都会直接持久化到磁盘中，这样可以保证 MySQL 异常重启之后数据不会丢失
• 当设置该参数为 2 时，每次事务的 redo log 都会直接写入到文件中，再将文件刷新到磁盘

7.3.2.2.7 其他

• 

7.3.2.3 innodb_buffer_pool_size IBP的内存大小是有限的，InnoDB 是如何讲热点数据保留在内存中，淘汰非热点数据的呢？

• 通过在内存中维护一个链表，并使用改进版的LRU(最近最少使用)算法淘汰非热点数据
• 因为传统的LRU算法，会存在预读失效和buffer_pool 污染的问题
• MySQL新增了一个midpoint insertion startegy策略，就是默认情况下，读取到的新页并不是直接放入的LRU列表的首部，而是LRU列表长度的5/8处 （将LRU 分为新生代和老年代，大致是7：3，新数据总是先加入到新生代，如果在一定时间内有被再次查询，才会进入到老年代。老年代和新生代内部都是LRU算法来淘汰缓存页的）
• buffer_pool 污染：一些不常查询SQL偶尔一次查询就把之前热点数据淘汰的情况