MySQL 45讲

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

如果这个“字段”是定义not null，一行行的从记录读取这个字段，判断不能为null，按行累加

如果这个“字段”是定义允许null，那么执行的时候，判断有可能是null，还要把值取出来再判断一下，不是null才累加

如果这个“字段”是定义not null，一行行的从记录读取这个字段，判断不能为null按行累加

如果这个“字段”是定义允许null，执行的时候判断有可能是null，还要把值取出来再判断一下，不是null才累加

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

效率排序：count(*) 优于或等于 count(1)  优于 count(主键id) 优于 count(字段) 

建议尽量使用count(*)

如果是，则提交事务

否则，回滚事务

补充：mysql.ini配置属性
sort_buffer_size

前提：如果MySQL认为排序的单行长度太大会怎么做？

修改  SET max_length_for_sort_data = 16
max_length_for_sort_data是MySQL中专门控制用于排序的行数据的长度的一个参数

city、name、age 这三个字段的定义总长度是36，现在最大只能为16而已，所以超过这个值，M有SQL需要下面做法

同样执行select city,name,age from t where city='杭州' order by name limit 1000 流程会改编为：
1. 初始化sort_buffer，确定放入两个字段，即name和id；
2. 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；
3. 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
4. 从索引city取下一个记录的主键id；
5. 重复步骤3、4直到不满足city='杭州’条件为止，也就是图中的ID_Y；
6. 对sort_buffer中的数据按照字段name进行排序；
7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

区别：对比上面全字段排序查找当中，rowid排序中,rowid排序多访问了一次表t的主键索引，就是步骤7

如果内存够买就多利用内存，尽量减少磁盘访问

通过创建where条件、order条件、结果字段的联合索引可以提交order效率。“覆盖索引
select city,name,age from t where city=‘杭州’ order by name limit 1000 ;
alter table t add index city_user_age(city, name, age);

个人总结

1. 创建一个临时表。这个临时表使用的是memory引擎，表里有两个字段，第一个字段是double类型，为了后面描述方便，记为字段R，第二个字段是varchar(64)类型，记为字段W。并且，这个表没有建索引。
2. 从words表中，按主键顺序取出所有的word值。对于每一个word值，调用rand()函数生成一个大于0小于1的随机小数，并把这个随机小数和word分别存入临时表的R和W字段中，到此，扫描行数是10000。
3. 现在临时表有10000行数据了，接下来你要在这个没有索引的内存临时表上，按照字段R排序。
4. 初始化 sort_buffer。sort_buffer中有两个字段，一个是double类型，另一个是整型。
5. 从内存临时表中一行一行地取出R值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加10000，变成了20000。
6. 在sort_buffer中根据R的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
7. 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出word值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了20003。

InnoDB引擎下答案：一个长度为6字节的rowid来作为主键
rowid ：每个引擎用来唯一标识数据行的信息
1 对于有主键的InnoDB表来说，这个rowid就是主键ID
2 对于没有主键的InnoDB表来说，这个rowid就是由系统生成的
3 MEMORY引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个rowid其实就是数组的下标

order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。

优先队列算法的触发条件：
1. 所需的有序行数 * 每一行的大小 < sort_buffer_size 的大小
2. 执行计划的OPTIMIZER_TRACE结果中，filesort_priority_queue_optimization这个部分的chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的number_of_tmp_files是0。

优先队列排序法步骤展示:
1. 对于这10000个准备排序的(R,rowid)，先取前三行，构造成一个堆 （对数据结构印象模糊的同学，可以先设想成这是一个由三个元素组成的数组）
2. 取下一个行(R’,rowid’)，跟当前堆里面最大的R比较，如果R’小于R，把这个(R,rowid)从堆中去掉，换成(R’,rowid’)
3. 重复第2步，直到第10000个(R’,rowid’)完成比较

鉴于rand() 函数会导致临时表排序，在海量数据下SQL的执行效率极低，所以采用函数的方法来生成随机的结果

1. 统一字符集，这样就没有字符集转换的问题了
比如： alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

2. 修改SQL语句，显示的转换范围小的字符集（如果数据量比较大，或者业务上暂时不能做这个DDL可以使用）
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

另外一种查询被堵住的情况，在表t上，执行下面的SQL语句：select * from information_schema.processlist where id=1;  出现上面情况
* 出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句

1. MySQL里对表做flush操作的用法，一般有以下两个：
   flush tables t with read lock;  // 定表t的话，代表的是只关闭表t
   flush tables with read lock;    // 表示关闭MySQL里所有打开的表
2. 结论：
在session A中，我故意每行都调用一次sleep(1)，这样这个语句默认要执行10万秒，在这期间表t一直是被session A“打开”着。然后，session B的flush tables t命令再要去关闭表t，就需要等session A的查询结束。这样，session C要再次查询的话，就会被flush 命令堵住了。

总结： 多个事务id访问同一个table，刚好事务A调用过程中，事务B在flush，导致事务C被flush给卡住

查询是占着这个写锁，入到事务A在update某个id值，刚好事务B在select同一个id值，且使用下列的语句：
select * from t where id=xxx lock in share mode; 
直接因为有写锁保护，不能读取最新值

查看占用写锁的方法[MySQL 5.7版本]，命令如下：
select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

使用慢查询指定
1. set long_query_time=0
2. 查看慢查询日志分析结果

行锁跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作

间隙锁之间都不存在冲突关系

1. 先处理掉那些占着连接但是不工作的线程

2. 减少连接过程的消耗

1、负责跟客户端建立连接、获取权限、维持和管理连接

2、执行mysql -h$ip -P$port -u$user -p 指令

3、客户端长时间无操作会被连接器自动断开。由参数wait_timeout控制。默认8小时。中间没有执行的话就处于空闲状态

4、通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

MySQL拿到【select * from T where ID=10】先到这里查询缓存

补充1 ：将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存

补充2：对于确定要使用查询缓存的语句，可以用SQL_CACHE显式指定：select SQL_CACHE * from T where ID=10；

补充3：MySQL 8.0版本直接将查询缓存的整块功能删掉了（重点）

分析器先会做语法分析

1. 决定使用哪个索引

2. 决定各个表的连接顺序

首先判断用户执行权限，然后根据引擎不同调用不同接口获取数据

慢查询日志中rows_examined字段，表示这个语句扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的

a. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用

b. redo log是物理日志，记录的是“在某个数据页上做了什么修改”
    binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”

c. redo log是循环写的，空间固定会用完
    binlog是可以追加写入的
“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

a. 执行器先找引擎去ID=2的一行。ID是主键，引擎直接用树搜索。如果在内存中则直接返回给执行器，否则需要从磁盘读入内存，然后返回

b. 执行器拿到引擎给的行数据，加1，得到新一行数据，调用引擎接口，写入新数据

c. 引擎更新数据到内存，同时记录到redo log，此时redo log处于prepare状态，然后告知执行器执行完成了，随时可以提交事务

d. 执行器生成binlog，写入磁盘

e. 执行器调用引擎的提交事务接口，引擎把redo log改成提交（commit）状态，更新完成

a. redo log写入被拆成了两个步骤：prepare和commit，这就是“两阶段提交”

b. redo log和binlog都可以用于表示事物的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致

读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。
个人理解--别人改数据的事务尚未提交，我在我的事务中也能读到

读提交（read committed）：一个事务提交之后，他做的变更才会被其他事务看到
个人理解--别人改数据的事务已经提交，我在我的事务中才能读到。

可重复读（repeatable read)：一个事务执行过程中看到的数据，
总是跟这个事务在启动时候看到的数据是一致的。
在可重复读隔离级别下，为提交变更对其他事务也是不可见的。
个人理解--别人改数据的事务已经提交，我在我的事务中也不去读

串行化（serializable）：对同一行记录，读写都会加锁。当出现锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
个人理解--我的事务尚未提交，别人就别想改数据

每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）

查询记录的时候，不同的事务ID会根据MVCC找到相对应符合MySQL规定的事务ID对应的快照值

记录的日志删除，根据系统判断没有回滚日志时候便会删除

尽量不使用长事务：
1.会存在很老的事务视图。占用大量存储空间
2.占用锁资源

显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback

set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到主动执行commit 或 rollback 语句，或者断开连接

建议set autocommit=1，通过显式语句启动事务

是一种以键-值【K-V】存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value

这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎

在等值查询和范围查询场景中的性能就都非常优秀

查找复杂度O(log(N))

有序数组索引只适用于静态存储引擎，在需要更新数据的时候比较麻烦

查找复杂度O(log(N))

更新复杂度O(log(N))

N叉树：多个儿子之间大小保证从左到右递增

实际大多数的数据库采用是N叉树
因为索引不止存在内存中，还要写到磁盘上

为了让一个查询尽量少的度期盼，必须让查询访问尽量少的数据库，所以使用N叉树

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

如果语句是select * from T where ID=500，是主键查询方式只需要搜索ID这棵B+树

如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表

优化：于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询

在5.6版本之前的执行处理，查询的索引只要不是主键索引，每一条均需要会回表，这里需要2次回表，假如符合要求更多就要回表更多

在5.6版本及以后的执行处理：对索引中的字段先做判断过滤不符合的字段，得出结果回表查询对应id，只做一次回表

命令： Flush tables with read lock (FTWRL)
后果：当前事务id只读，其他线程或者写入操作均阻塞

set global readonly=true 让全数据库变成只读

执行方式： lock tables … read/write 锁住这个表
                  用unlock tables主动释放锁

执行方式：访问一个表的时候会被自动加上

作用：保证读写的正确性

情景：事务A内有两个update的操作【换而言之有2个行锁在这个事务内】，事务B同样有同一个id去修改该值
分析结果：事务B需要等待事务A释放资源【等待事务A要commit释放】，事务B才能执行begin的update语句。这个等待的过程会阻塞
总结：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

死锁：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态
解决死锁策略：1. 等待释放，通过innodb_lock_wait_timeout来设置等待时间
                        2. 发起死锁检测，发现死锁后主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。通过innodb_deadlock_detect设置为on，表示开启这个逻辑

a.通过undo log来保存多版本的数据

b.通过一致性视图来保存当前活跃的事务列表，
将两者结合和制定一定的规则来判断当前可读数据

rr机制与rc机制的readview区别

当前读与快照读的不同

当MySQL需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer用的是buffer pool里的内存,change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置

这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%

使用场景

第一种情况：这个记录要更新的目标页在内存中【change buffer内判断】

第二种情况：这个记录要更新的目标页不在内存中【在数据页内】

都不支持范围查询

从占用空间来看：倒序存储方式在主键索引上，不会消耗额外的存储空间，hash方式需要增加一个字段。

在CPU消耗方面：倒序方式每次写和读的时候，需要额外调用一次reverse函数，hash方式需要额外调用一次crc32()函数，reverse消耗资源更小

查询效率：hash字段方式更稳定