mysql实战45讲（09-14）笔记

仅仅是笔记，用于自己理解

 

09  普通索引与唯一索引

查询过程
对于普通索引，查找到满足条件的第一个记录，需要查找下一个记录，直到碰到第一个不满足条件的记录。
对于唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录，就会停止继续检索。
其实这两种的性能差距几乎没有，
innodb的数据是按数据页为单位来读写的，数据页默认16kb，
更新过程
概念：change buffer
当需要更新一个数据页时，如果数据页在内存中就直接更新，如果不在，在不影响数据一致性的前提下，会先将更新缓存在change buffer中，
change buffer也会持久化数据
将change buffer中的操作应用到猿数据页，得到最新的结果的过程称为merge，访问会触发，定期出发，关闭数据库也会触发merge
只有普通索引可以使用change buffer，通过innodb_change_buffer_max_size来设置
更新数据分两种情况：
第一种是数据页在内存中，唯一索引需要先找到再判断有没有冲突，再插入，普通索引只要找到就行
第二种是数据页不在内存中，唯一索引需要先将数据页读入内存中再进行判断，再插入，普通索引则是将更新记录在change buffer，就结束了
因为唯一索引需要先读，增加了i/o操作，所以更新性能上，普通会比唯一好。优先普通。
change buffer什么场景不能使用：
当更新了一次操作时，需要立马查询访问这个数据页时，会触发merge，导致i/o操作，change buffer反而会不好
change buffer与redo log
更新：
当数据页在内存中，就更新内存，当数据页没有在内存中，就添加change buffer，然后将上述动作记入redo log中
查询：
当数据页在内存中，直接内存返回，与系统表空间和redo log无关
当数据页不在内存中，需要先把数据页读到内存中，然后应用change buffer的操作日志生成一个正确的版本返回。
所以：redo log 主要节省的是随机写磁盘的i/o操作，而change buffer主要节省的是随机读磁盘的操作。
总结：不考虑业务的情况下，优先普通。
merge 的执行流程是这样的：
从磁盘读入数据页到内存（老版本的数据页）；
从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

 

10 选错索引

优化器逻辑
而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
判断行数：
基数：mysql会根据统计信息估算记录数，这个统计信息就是索引的区分度，一个索引上不同的值越多，区分度越好，而索引上不同值的个数，称为基数，基数越大，区分度越好。
基数通过采样统计获取，当变更行数超过1/m的时候，采样统计会触发重新索引统计。

当扫描行数不准确的时候，可以通过analyze table t 命令重新统计索引信息。
当索引选择异常了怎么处理：
1，sql里使用force index 强行选择一个索引
select * from t force index(a) where a between 10000 and 20000;
2，修改sql，指导优化器选择期望的索引。
3，可以的话，新建一个索引，或者删掉无用的索引。
问题中的知识点：
先删后加，加了事务导致索引选择错误，是因为事务开启了一致性读，保证可重复读，
删除是标记删除，所以会很快，加则不是会很慢。
问题：加了事务索引选择错，没加则对
答案：
1.为什么没有session A,session B扫描的行数是1W
由于mysql是使用标记删除来删除记录的,并不从索引和数据文件中真正的删除。
如果delete和insert中间的间隔相对较小,purge线程还没有来得及清理该记录。
如果主键相同的情况下,新插入的insert会沿用之前删除的delete的记录的空间。
由于相同的数据量以及表大小,所以导致了统计信息没有变化
2.为什么开启了session A,session B扫描行数变成3W
由于session A开启了一致性读,目的为了保证session A的可重复读,insert只能
另起炉灶,不能占用delete的空间。所以出现的情况就是delete虽然删除了,但是
未释放空间,insert又增加了空间。导致统计信息有误

11 给字符串字段加索引

11.1 使用前缀索引
mysql> alter table SUser add index index1(email);（没有使用前缀）
mysql> alter table SUser add index index2(email(6));（使用前缀6位）
使用前缀索引，定义好长度，就可以做到即节省空间，又不用额外增加太多的查询成本。
使用多长的前缀呢？
建立索引关注的是区分度，区分度越高越好，因为区分度越高，意味着重复的键值越少，因此，我们可以通过统计索引上有多少不同的值来判断要使用多长的前缀。
计算索引列上有多少不同的量：mysql> select count(distinct email) as L from SUser;
然后大致计算不同长度的数量：
mysql> select 
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;
这样就可以选择适当的长度。
//
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是一个考虑因素。
覆盖索引：就是当你查询的字段刚好是索引的字段，这样查询就不需要回表查询了，因为索引里已经有你需要的列的数据了，提升了性能，这样我们成为覆盖索引。
11.2 其他方式
11.2.1 使用倒序存储
就是存储身份证号的时候把它倒过来存(reverse函数)，每次查询再倒回来，因为身份证后6位区分度比较高
mysql> select field_list from t where id_card = reverse('input_id_card_string');
11.2.2 使用hash字段
再创建一个整数字段，用来保存身份证的校验码(用加密方式或者hash值得到)，用这个字段建立索引，

12 sql偶尔会慢（抖一下）
当内存数据页跟磁盘数据页内容不一致的时候，我们称内存页为脏页，内存数据写入到磁盘后，内存与磁盘保持一致了，就是干净页了。
偶尔会抖一下，可能就是在刷脏页。
什么时候会刷脏页呢？
第一种：就是redo log写满了，需要刷新
第二种：内存不足了，需要先刷新一部分数据。
第三种：在系统空闲的时候
第四种：mysql正常关闭的时候。
影响
第一种，当redo log 满了，整个系统就不能接受更新了，更新操作都必需堵住，
第二种，是常态，innodb用缓冲池管理内存，innodb的策略是尽量使用内存。
innodb刷脏页的控制策略。
innodb_io_capacity：建议设置成磁盘的IOPS，
测试IOPS：
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 
设置脏页的速度考虑因素
脏页比例和redo log写盘速度
innodb_max_dirty_pages_pct是脏页比例上限，默认75%，根据这个比例算出m
innodb写入日志每一次都有一个序号，当前序号和checkpoint的序号的差为n,然后再计算(计算复杂)，只要n越大，算出来的值越大。
然后，根据m,n的值，取较大的那个为r，然后innodb_io_capacity乘以r就是控制的速度。

所以innodb_io_capacity的值尽量不要接近75%。
其中，脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具体的命令参考下面的代码：
mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为（刷脏页的时候发现边上的数据页也是脏的会顺带一起刷掉）的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。现在默认为0了
问题总结
将脏页flush到磁盘上是直接将脏页数据覆盖到对应磁盘上的数据。
redo log是怎么记录对应脏页是否已经flush了？如果断电了重启导致内存丢失，前面几章说通过redo log进行数据恢复那redo log又怎么去释放空间？
不用记，重启了就从checkpoint 的位置往后扫。 如果已经之前刷过盘的, 不会重复应用redi log。

13 为什么表数据删掉一半，表文件还是那么大
13.1 表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table 控制的：
这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。推荐设置成on
13.2 数据删除流程
删除记录：删除一条记录(di=400)只是标记为删除，如果后面再插入一条id在300-600之间的记录时，可能会复用这个位置，但是当id=800时那就不会复用这个位置了，所以删除不会减少磁盘文件大小，只是标记为删除，后续可以复用。而且是复用符合范围的是记录
删除数据页：也是一样，会被复用，不同的是整个页标记删除后，这个页位置可以被任何位置的页复用。同时如果相邻的两个页利用率很低，就会合并成一个页，空出来的页就会标记成可复用。
进一步，没有被复用到的位置就成为空洞了，这样慢慢的文件会越来越大，但是数据量其实不大，不止删除，插入也会造成空洞(原因就不记了，简单来说就是，当一个页满了，其实有空洞，但是插入的记录不在这个空洞位置范围内，就会新建一个页，页分裂，这样原来那个页就会产生空洞)。
13.3 怎么解决呢？
解决方案是重建表：新建一个一摸一样的表，然后把数据按照主键递增转移到新表中，这样新表都是紧凑的。
可以使用alter table A engine=InnoDB命令。
这个命令5.6之前，执行的时候是不允许更新表的，5.6之后可以在更新表的时候进行重建表。流程是：
Online DDL 重建表的流程：
1）建立一个临时文件，扫描表 A 主键的所有数据页；
2）用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3）生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
4）临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
5）用临时文件替换表 A 的数据文件
使用 optimize table、analyze table 和 alter table 这三种方式重建表的区别。这里，我顺便再简单和你解释一下。
从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是上面图 4 的流程了；
analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。
13.4 问题；
4.1 不影响增删改，就是 Online；相对 Server层没有新建临时表，就是 inplace
4.2 主键索引只要是递增，不需要连续，性能是一样的。
4.3 如果将 alter 操作显式的放到事务里 ，事务不提交 ， 另一个事务查询的时候会查询到alter 操作后的表结构 ， 比如新增了一个字段。这个是什么原因 ，是否打破了 mvcc 的定义呢?
答：alter table 语句会默认提交前面的事务，然后自己独立执行

14 count(*) 很慢
14.1 count(*) 的实现方式
1）MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
2）而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
这里需要注意的是，我们在这篇文章里讨论的是没有过滤条件的 count(*)，如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。
MyISAM 表虽然 count(*) 很快，但是不支持事务；
show table status 命令虽然返回很快，但是不准确；
InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题
14.2 解决办法
在数据库保存计数：单独建一张表用于记录计数表
个人觉得：这种办法也不适用，因为如果加了where条件，这种就不好用了，因为条件不同，count的值也不同的。
14.3 count(*),count(1),count(字段)，count(id) 比较
对于 count(主键 id) 来说，
InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
对于 count(1) 来说，
InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
对于 count(字段) 来说：
如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
count(*) 是例外，
并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。看到这里，你一定会说，优化器就不能自己判断一下吗，主键 id 肯定非空啊，为什么不能按照 count(*) 来处理，多么简单的优化啊。当然，MySQL 专门针对这个语句进行优化，也不是不可以。但是这种需要专门优化的情况太多了，而且 MySQL 已经优化过 count(*) 了，你直接使用这种用法就可以了
所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。
问题：
我们用了事务来确保计数准确。由于事务可以保证中间结果不被别的事务读到，因此修改计数值和插入新记录的顺序是不影响逻辑结果的。但是，从并发系统性能的角度考虑，你觉得在这个事务序列里，应该先插入操作记录，还是应该先更新计数表呢？
答：从并发系统性能的角度考虑，应该先插入操作记录，再更新计数表。
知识点在《行锁功过：怎么减少行锁对性能的影响？》
因为更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少了事务之间的锁等待，提升了并发度。