Mysql实战 学习笔记

9课时：普通索引和唯一索引

1.change buffer 

2.数据页 （到缓冲池申请数据页）

3.内存/硬盘

4.redo log

        InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 redo log（重做日志)

4.2 flush 

        内存里的数据写入磁盘的过程

5.InnoDB

6.merge

7.idata存储过程

8.WAL机制

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

...未完待补充

10课时：索引的选择

选错索引：

1.索引是优化器的工作，优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句

2.页面 x 该索引的的平均值  =  基数 (采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值) 

11课时：给字符串加索引

1.前缀索引，节省空间，但会增加额外的扫描次数

2.使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

3.我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少

4.使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素、

5.主要是依靠业务理解来，确定一个合适的前缀索引长度，

6.第一种：倒序存储

7.第二种： hash 字段（更稳定）

8.reverse 函数

12课时 ：MYsql的卡顿现象
1.“脏页”、“干净页”：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

2.抖一下：

        情景1：redo log 满了。对应的就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写

        情景2：系统内存满了。对应的就是系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。（InnoDB 用缓冲池（buffer pool）管理内存        ）

        情景3：MYsql闲置。对应的就是 MySQL 认为系统“空闲”的时候。

        情景4：MYsql关闭的时候。例如年底结账

3.缓冲池 buffer pool  

4.控制脏页比例

5.innodb_io_capacity （参数，它会告诉 InnoDB 你的磁盘能力）

6.IPOS:一般指硬盘的每秒的读写次数

7.WAL 技术

        其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

13课时 表和存储空间(空间回收)的问题

1. innodb_file_per_table

        OFF 表的数据放在系统共享表空间，也就是跟数据字典放在一起 

        ON  每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中 (5.5.6默认ON)

2. 要放在文件里，及innodb_file_per_table=NO时，我门面drop Table会删除文件夹，释放空间，而在共享中drop 不会释放空间。

3. InnoDB 里的数据都是用 B+ 树的结构组织的

4. 数据页复用 / 记录复用

5.delete命令不能表回收空间，但这些空间在delete后可以复用，像是个'空洞'，但是占着位置。

6.'空洞'，一般在删除或者插入数据造成，所以频繁的删除插入数据会导致存在很多‘空洞’

7.重建表，可以去掉空洞。

8.MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化

9.日志文件（row log） 操作日志

10.alter 语句在启动的时候需要获取 MDL 写锁。

11.Online 和 inplace的区别：
alter table t engine=innodb,ALGORITHM=inplace;

inplace：对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源

12.增加全文索引：
alter table t add FULLTEXT(field_name);、

13.summary:

如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。(PS:我跟你介绍了重建表的两种实现方式，Online DDL 的方式是可以考虑在业务低峰期使用的，而 MySQL 5.5 及之前的版本，这个命令是会阻塞 DML 的，这个你需要特别小心)

14.write pos：写入指针

15. cp :checkPoint 检查点

14课时 count(*)理解

1.MyISAM 引擎 ：会把表行数记录下来

2.InnoDB 引擎：不会把表行数记录下来

3.多版本并发控制（MVCC）

4.InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值

5.普通索引树 / 主键索引树

6.summary

        1)MyISAM 表虽然 count(*) 很快，但是不支持事务

        2)show table status 命令虽然返回很快，但是不准确

        3)nnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题

7.Count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值

8.Redis 缓存计数的方法，不能保证精准性，因为不够事务，且内存不稳定，存在断电的风险。

9.结论：

执行效率：count(字段)<count(主键 id)<count(1)≈count(*)

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

对于count(字段)，存在null的风险

对于count(*) ，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

16课时   order by的流程

1.排序方法：全字段排序、rowid 排序 两种

2.explain中看到Using filesort 表示要排序

3.sort_buffer ：MySQL 会给每个线程分配一块内存用于排序，会选取出数据，放进ort_buffer，在做排序

4.sort_buffer_size：开辟的排序内存大小

5.number_of_tmp_files：排序临时文件数，切分出来的个数

6.内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法

7.内存足够则采用全字段排序，内存不够采用rowid 排序

8.MYSQL的设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问！

9.通过city和name的联合索引，就可以直接取数，而不需要排序，更快速的得到相当于排序后的效果。效率最高！

alter table t add index city_user(city, name) --Using index

17课时 随机展示消息

1.order by rand()

2.Using temporary 需要临时表(explain中的extra)

3.Using filesort 要做排序(explain中的extra)

18课时 sql语句性能

字段条件函数操作、隐式类型转换、    

1.如果对字段做了函数计算，就用不上索引了(即使该字段有索引)，这是 MySQL 的规定

2.对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能

3.select的时候可以遍历主键索引，也可以遍历需要的字段的索引，优化器会，取判断索引的大小，优先选择小的取走遍历。

4.select “10” > 9 返回的是 1，所以你就能确认 MySQL 里的转换规则了：在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。会把本身存储在表的varchar字段signed int再去比较，所以就不会走数搜索功能，导致运行效率很低

5. $L2.字段名.value 的字符集是 utf8mb4。字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集（专门用来兼容四字节的unicode），再做比较。这个设定很好理解，utf8mb4 是 utf8 的超集。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。牺牲空间和效率保证数据准确。\

19课时  select查询阻塞

1.ioutil（IO 利用率）

2.show processlist 命令，看看当前语句处于什么状态。Waiting for table metadata lock 等待锁状态。这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。

3.select blocking_pid from sys.schema_table_lock_waits; --找到持有MDL写锁，得到process id，然后kill掉

4.Waiting for table flush

5.表锁、行锁和一致性读

20课时  幻读

1.略

21课时  锁 （难点，需复习）

1.间隙锁 (可重复读隔离级别下才有效)

2.next-key lock   /   行锁   / 间隙锁

3.加锁规则

4.两个“原则”、两个“优化”和一个“bug”:

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

 bug 1：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

5.lock in share mode

22课时 应对高峰，短时间提高性能（server 层）

1.MySQL 建立连接的过程，成本是很高的。三次握手，登录权限等

2.max_connections 最大连接数参数

3.第一种方法：先处理掉那些占着连接但是不工作的线程。wait_time_out

4.第二种方法：减少连接过程的消耗。重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内

5.什么是慢查询： MySQL默认10秒内没有响应SQL结果,则为慢查询

show variables like 'long_query_time';

6.慢查询性能问题：

1）索引没有设计好；（Online DDL）

2）SQL 语句没写好；（解决query_rewrite）

3）MySQL 选错了索引。（紧急解决force index）

7.gh-ost 

gh-ost 作为一个伪装的备库，可以从主库/备库上拉取 binlog，过滤之后重新应用到主库上去，相当于主库上的增量操作通过 binlog 又应用回主库本身，不过是应用在幽灵表上

8.query_rewrite.flush_rewrite_rules();

9.慢查询日志（slow log） long_query_time = 0开启慢日志写入log中

10.Rows_examined字段

11. 每秒查询率（QPS，Queries-per-second）

12.QPS突增问题，解决方案：当运维规范的情况下，虚拟化、白名单机制、删除账号密码，业务账号分离，重置压力大的语句，都可以解决这个问题

13.长连接：可以连续发送多个包，保持期间，没有数据发送，则需要发送链路检测包，闲置8h后会自动断开

短连接：一次通讯一次连接，发送完数据，就会断开

23课时 保证数据不丢失

1.WAL机制

2.redo log和binlog 保证持久化到磁盘，就能确保MySQL的数据可恢复性

3.binlog写入机制：**

binlog cache(多个) -> wirte(快) ->binlog files(共用1个) -> fyns(IOPS慢) ->disk

4.binlog_cache_size，超了就要暂存在磁盘

5.write 和 fsync 的时机，是由参数 sync_binlog 控制的：

1) sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

2) sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；

3) sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。（风险是如果主机发生异常重启，会丢失最近N个事务的binlog日志）

6.redo log写入机制 **（前提：事务执行会生成redo log）

redo log --> wright -> redo log buffer（属于Mysql）-> FS page cache -> fsync -> hard disk

但不是每次都会write进hard disk

redo log三种状态：

1)存在 redo log buffer 中，物理上是在 MySQL 进程内存中.

2)写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面（FS page cache）

3)持久化到磁盘，对应的是 hard disk.

7.控制redo logo 写入策略,innodb_flush_log_at_trx_commit 参数

1) 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中

2）设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；

3）设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 writ

（InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘）

8.redo log 主动写盘的两种情况：

1）一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘

2.另一种是，并行的事务提交（commit）的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。

9.binlog和redo log的时序关系

redo log -> prepare -> bin log -> redo log commit

10.组提交机制（group commit）

11.日志逻辑序列号（log sequence number，LSN）

12.check point

13.如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，如何提升性能？

1）设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。

2）将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。

3）将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

PS：不建议你把 innodb_flush_log_at_trx_commit 设置成 0,redo log保存在内润，风险很大！