Mysql实战45讲小总结

自增id用完怎么办？

1.表定义自增id
表定义自增id达到上限的逻辑：申请下一个id时，得到的值不变
2.InnoDB自增row_id
InnoDB最大值为2^48-1，当达到上限时，会从0开始继续循环

一条select语句的执行过程

客户端通过连接器连接到数据库，这个时候客户端执行select语句，执行过程如下：
Mysql拿到select会先查询缓存，之前是否执行过这条语句，之前执行过的以key-value存储到缓存中；
如果未在缓存中查询到，分析器进行"词法分析"，同时语法分析器对MYSQL的语句判断是否满足MYSQL语法；
优化器会判断在表中有多个索引时，判断用那个索引，有多表关联时，决定用各个表的连接顺序；
执行器开始执行语句；

日志系统的SQL更新语句执行

redo log（重做日志）：有了redo log，那么当发生异常，重启，之前提交的记录不会丢失（crash-safe）。理解为：一条记录需要更新时，先将记录写在日志上，并更新内存；InnoDB空闲时，将记录更新到磁盘中。
binlog（归档日志）：Server层日志。
不同点：

• redo log是InnoDB独有日志，binlog是server层的日志
• redo log是物理日志，binlog是逻辑日志，记录的是用户对数据库更新的SQL语句信息
• redo log是循环读写的，binlog是追加写入，不会覆盖前面的日志

日志系统的SQL更新执行流程：update T set c=c+1 where ID=2

1. 执行器找引擎寻找这行数据，如果数据页在内存中，直接返回，如果没找到，会去磁盘中去找然后放入内存；
2. 执行器拿到数据之后，做+1操作，并调用引擎写入这行数据
3. 引擎将数据更新到内存中，同时将数据操作更新记录到redo log，redo log处于prepare状态，然后告知执行器执行完成了，随时可以提交事务；
4. 执行器生成这个操作的binlog，将binlog写入磁盘；
5. 执行器调用引擎的提交事务接口，引擎将刚写入的redo log改成提交（commit）状态，更新完成；

事务隔离：为什么你改了我还看不见

隔离性与隔离级别：
读未提交：一个事务还未提交时，他做的变更就能被其他事务看到；
读提交：一个事务了提交了，他做的变更才能被其他事务看到；
可重复读：一个事务执行过程中看到的数据，与初始时候的数据是一样的；
串行化：对于同一行数据，读加读锁，写加写锁；

深入浅出索引

索引的出现是为提高数据查询效率；
索引模型：

• 哈希表
• 有序数据
• 搜索树

InnoDB索引模型：B+树
索引类型分为主键索引和非主键索引。

• 主键索引：叶子节点存整行数据
• 非主键索引：叶子节点存键值，二级索引

回表：回到主键索引树搜索的过程

如何索引优化，避免回表：覆盖索引 、最左前缀原则、索引下推

全局锁与表锁：给表加个字段怎么有这么多障碍？

全局锁：对整个数据库实例加锁，Flush tables with read lock(FTWRL),使用场景：全库逻辑备份
如果需要全库只读：为啥不使用set global readonly=true？

1. 在有些系统中，readonly被用来做其他逻辑，比如用来判断一个库是主库还是从库，因此，修改global的影响更大。
2. 在异常处理机制上，执行FTWRL之后，一旦客户端发生异常之后，Mysql会自动释放这个锁，整个库回到正常的状态；若修改的是readonly的值，一旦发生异常，数据库会一直保持readonly的状态，风险比较高

表级锁：
表锁：lock tables…read/write，一般在数据库不支持行锁时才会被用到
元数据锁：不需要显式使用，在访问一个表时就会被加上

如何安全的给小表加字段？
在alter table语句里面设定等待时间如果在这个指定时间里面能够拿到MDL锁最好，拿不到也不要阻塞后面的业务语句，先放弃，之后开发人员或者DNA通过重试这个命令重复这个过程。

行锁功过：怎么减少行锁对性能的影响

行锁：行锁是针对数据表中行记录的锁
两阶段协议：InnoDB事务中，行锁是在需要的时候加上的，并不是不需要了就释放，而是要等到事务结束时才释放
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁往后放；
死锁和死锁检测
如何解决由热点行更新导致的性能问题？
问题的症结在于：死锁的检测要耗费大量资源
解决思路1：如果能够确定不会发生死锁，可以临时将死锁检测关掉
解决思路2：控制并发度，要做到数据库服务端，如果由中间件，加在中间件，对于相同行的更新，在进入引擎之前排队。这样可以尽量避免大量的死锁检测了。

事务到底是隔离的还是不隔离的

视图：
1.view，是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view ，而它的查询方法与表一样；
2.InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现
“快照”在MVCC里是怎么工作的？
InnoDB里面每个事务有一个唯一的事务ID，叫做transaction id。它在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务id，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
也就是说，数据表中的一行记录，其实可能有多个版本，每个版本有自己的row trx_id。
InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID，“活跃”指的是，启动了但还没提交。
数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位，这个视图和高水位，就组成了当前事务的一致性视图（read-view）。
事务查询语句的读数据流程每次需要与高水位和低水位判断，判断可见或不可见，这样执行的话，虽然期间这一行数据被修改过，但是事务A不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。
但是根据代码逻辑转移，用于人肉分析可见性是很麻烦，若去掉数字对比，只用时间先后顺序来判断，分析起来要轻松很多。
**更新逻辑：**更新数据都是先读后写的，而这个读，只能读当前值，称为“当前读”。
可重复读的核心就是一致性读；而事务更新数据的时候，只能用当前读，如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
读提交的逻辑和可重复读的逻辑类似，他们最主要的区别是：
在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
在读提交隔离级别下，每个语句执行前都会重新算出一个新的视图；

总结：对于可重复读，查询只承认在事务启动前就已经提交完成的额数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；

思考题：如何删除表的前10000行；
第一种（直接执行delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟；
第三种（即：在20个连接中同时执行delete from T limit 500），会认为造成锁冲突；
第二种：在一个连接中循环执行20次delete from T limit 500，最好；

普通索引和唯一索引，应该怎样解决？

查询过程：
比如，查询语句为：select id fromT where k=5

• 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录；
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索；

这个不同带来的性能差距是微乎其微的；
InnoDB是以数据页来读数据的，也就是说，当读一条记录时，并不是将这条记录本身从磁盘读出来，而是以页为单位，将其整体读入内存；

因为引擎是按页读写的，所以，找到k=5数据的那一页时，这页一定已经在内存当中了，所以对于普通索引来说，只是会多一次查询与判断下一条记录。

更新语句：
Change Buffer: 当需要更新一个数据页时，如果数据页在内存中就直接更新，如果不在的话，在数据一致性的保证下，InnoDB将这些操作缓存在change buffer中，这样就不需要从磁盘中读取数据了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer 中与这个页有关的操作；
将change buffer上的操作应用到原数据，得到最新结果的过程叫merge，除了访问数据页会触发merge，后台也会定期merge，数据库正常关闭也会触发merge；

什么条件下可以使用change buffer？
只有普通索引可以使用；

因此具体更新过程（如果数据页不再内存中）：

• 唯一索引会将数据页从磁盘调入内存中，判断到没有冲突，插入这个值；
• 普通索引将更新操作放入change buffer；

change buffer的使用场景：常见账单类、日志类的业务模型；写多读少的业务类型；

redo log 与change buffer：red log主要节省的是随机写磁盘的IO消耗，change buffer主要节省的是读磁盘的IO消耗；

MySQL为什么有时候会选错索引？

选错索引的例子：
建表，a，b字段建上索引：

CREATE TABLE `t` (
	`id` int(11) NOT NULL,
	`a` int(11) DEFAULT NULL,
	`b` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `a` (`a`),
	KEY `b` (`b`)
) ENGINE=InnoDB；

执行一个存储过程，插入10万数据：

delimiter ;;
create procedure idata()
begin
	declare i int;
	set i=1;
	while(i<=100000)do
		insert into t values(i, i, i);
		set i=i+1;
	end while;
end;;
delimiter ;
call idata();

并执行下列查询语句：

mysql> select * from t where a between 10000 and 20000;

这里很显然，直接使用了a的索引；
经过下一步操作：

查询链接：https://blog.csdn.net/ykun089/article/details/106261162
session的概念理解，到底什么是session：
Mysql是“单进程多线程”模型，N个线程对接N个客户端，这些线程就是“session”，由于数据的存储是独一份的，所以这些线程在操作数时就会出现线程间的竞争关系，锁便应运而生。
实际情况下，session一般对于一个tcp链接，即一个connection，但一个connection同时代理多个session也是允许的，这些session的业务请求会在connection中排队被发往服务器；

这里sessionA开启事务，随后，sessionB把数据删除，再次调用存储过程，插入10万数据，这个时候sessionB的查询语句不会在选择索引a了

优化器逻辑
选择索引是优化器的工作；
优化器选择索引的目的，是找到一个最优的执行方案，用最小的代价去执行语句；
影响这个代价的因素有很多，比如扫描行数、是否使用临时表，是否排序等因素；
扫描行数是怎么判断的？
Mysql并不能精确的知道满足条件的记录有多少条，而只能通过统计信息来估算记录数；
这个统计信息就是索引的区分度，一个索引上不同的值越多，这个索引的区分度越高，而一个索引上的不同的值的个数，我们称之为“基数”，这个“基数”越大，区分度越大；
Mysql如何得到索引的基数？
“采样统计”：InnoDB选择N个数据页，统计这些页面上的不同值，得到一个平均值，再乘以这个页面的页面数，就是索引的基数；
Mysql中有两种存储索引统计的方法：设置innodb_status_persistent的值来选择：

• 设置为on的时候，表示统计信息会持久化存储，默认N=20，M=10（变更数据行数超过1/M的时候，会自动触发做一次索引统计）；
• 设置为off的时候，表示统计只会存储到内存中，默认N=8，M16；

索引选择异常与处理：

• 采用force index来强制选择一个索引
• 考虑修改语句，引导mysql使用我们期望的索引
• 新建一个更适合的索引，或删掉无用的索引

如果某次写入使用change buffer机制，之后主机异常重启，是否会丢失change buffer和数据？
虽然是只更新内存，但是在事务提交的时候，把change buffer的操作也记录到
redo log中，崩溃恢复的时候，change buffer也能找回来；
merge的过程是否会写到磁盘？
1.从磁盘读数据页到内存中；
2.从change buffer中查找所有相关的记录，依次记录，得到新版数据页；
3.写redo log，redo log包含数据页和change buffer 的变更；

怎么给字符串字段加索引？