MySQL总结二

十一
如果索引的键比较大不仅浪费空间而且没页的索引键也会变少导致查询时需要更多的页
前缀索引：可以对字符串索引规定索引的长度
优点：节省空间
缺点：索引覆盖失效，必须回表查询索引字段的完整信息
可以采取倒序存储再创建前缀这样可以提高区分度较少冗余
也可以建立hash字段来节省存储空间，但是2种方案都会失去范围查询的能力。
十二
刷脏页的条件：
一：redo log写满

需要将cp推进到cp撇才可以继续进行写入操作。
二：内存不够用了
当需要淘汰脏页时需要将脏页flush到磁盘。
MySQL通过buffer pool来管理内存，有几种数据页
1：干净页
2：脏页
3：未使用
当读取数据未在内存的时候需要申请新的页，根据LRU如果淘汰脏页的话需要flush，此时如果需要淘汰多个脏页会导致速度下降，在淘汰的时候会连带将附件的脏页一起flush，可以通过innodb_flush_neighbors来进行控制。
三：空闲时候
四：MySQL正常关闭时
一些参数：
Innodb_buffer_pool_pages_total：总页数
Innodb_buffer_pool_pages_dirty ：脏页数量

十三
对于innodb来说，表结构定义在.frm这个文件，数据放在.idb文件中，通过innodb_file_per_table可以将数据放到共享表空间，但是执行drop table无法回收。对于删除单行的时候，即便放在了每张表放到了单个文件中，但是空间还是没有回收。

delete操作只会在索引页的数据标记可复用，如果整个页被删除那么整页都可以复用，索引页会产生空洞。对于insert来说也会产生空洞，因为当一个页满后，再次插入（非递增，随机插入）会导致页分裂，从而每个页都会有多余的空间，这也会导致空洞。

重建表可以解决空洞问题。
将被重建的表重新按照主键递增放入到新表中。
上述操作可以使用：alter table A engine=InnoDB
在5.5之前不支持在线DDL，5.6后可以进行DDL。
主要流程都是建立临时表，将被重建表写入临时表，之后将临时表替换被重建表。5.6后会引入一个row log日志文件记录在写入临时表期间增量数据，在写入后会应用到临时表中，之后替换被重建表。
DDL操作会获取DML锁，5.6后会将获取到MDL锁退化成读锁，防止其他DDL，但是期间可以进行DML。
optimize table：analyze table + alter table
analyze table：会获取MDL锁，只是重新统计表信息
alter table：重构索引树。
十四
count(*):
因为innodb支持MVCC所以每个回话看到的统计结果不同，故必须遍历索引树。它会挑一棵树小的来遍历。
因为慢所以咱们需要自己计数：
Redis：
在redis中统计新插入的数量，当然如果需要查询最新几笔数据会导致数据不一致。

会话A先插入数据，期间B就会先看到记录所以统计的数量并没有加一。

会话A 先统计加一，期间统计虽然是最新的但是查询的记录不是最新的。

在数据库统计计数：
由于事务的存在所以逻辑上都是正确的。

count(字段) < count(主键) < count(1) = count(*)
count(字段)：如果该字段可以为null，需要将数据去除判断不为null，返回给server层，server层累加
count(主键)：遍历索引，判断主键不为null，返回主键，server层累加
count(1): 遍历索引，每行数据返回1，server层累加
count(*): 交给mysql进行优化。

十五

时刻A：在此时服务挂了，那么恢复的时候直接回滚，binlog没有改记录可以正常归档
时刻B：在此时服务挂了，那么恢复的时候会查阅redolog
如果redolog完整且已有commit标识直接提交事务
如果redolog完整只有prepare没有commit：进一步查看binlog如果不完整那么回滚事务反之提交事务。

binlog的完整性：

statement格式的binlog，最后会有COMMIT；
row格式的binlog，最后会有一个XID event。

而且binlog会有个checksum来检查整个binlog是否完整。

重启恢复逻辑：
redolog和binlog有相同的xid字段。
在崩溃后，会扫描redolog，检查是否有commit标识，有的话直接提交，只有prepare的话会拿着xid会查找binlog，进一步判断。只要写入了binlog那么就会同步到其他从库，所以检查完redolog需要检查binlog才能保证数据一致性。

十六

全字段排序：

select city,name,age from t where city='杭州' order by name limit 1000  ;

流程：
1.根据city索引树，找到满足条件的杭州，拿到主键ID。
2.根据主键ID从主键索引中查找city,name,age的字段放入到sort_buffer中
3.重复1,2直到不满足条件为止
4.在sort_buffer中依据name进行快排
5.返回结果集

如果sort_buffer_size超过待排序的数据在内存中进行，否则会创建临时文件，进行归并排序，sort_buffer_size越小创建文件个数越多。max_length_for_sort_data 可以通过改参数调整sort_buffer中行的最大值超过了就使用磁盘。
rowid排序：
1.根据city索引查找满足条件的数据的主键ID
2.根据主键ID去主键索引中找到name的值，放入到sort_buffer中
3.重复1,2直到条件不满足
4.根据排序sort_buffer
5.根据sort_buffer中id，去主键索引中找到city,age返回客户端。

联合索引：设置(city，name)联合索引。
1.根据city索引树找到主键ID，因为其已经按照city，name在构建索引树的时候已经排序好了
2.根据主键ID去主键索引找到age放入结果集
3.重复1,2直到条件不满足

可以配置索引覆盖，此时就无法进行回表了，直接根据city索引树查找满足条件的直接返回记录。

十七
随机取数使用order by random();
步骤：
1.初始化临时表，产生r，w列
2.将原表按主键顺序去除所有的word字段，放入到临时表中，R为随机的小树，W为word字段内容
3.将临时表R去除放入到sort_buffer中，pos对应临时表的角标
4.进行rowid排序，根据pos查找临时表，返回结果集

为什么叫rowid排序：
mysql定位一行数据主要就是通过rowid来定位。
对于有主键id的行rowid就是主键的id
对于没有主键id的行rowid就是系统自动生成的
对于临时表来说rowid就是临时表的角标

如果数据超过临时表（tmp_table_size）的大小，那么使用磁盘排序。mysql5.6引入优先队列排序算法使用堆排序所以对于limit值小的不会超过sort_buffer的大小。

十八
不走索引的条件：
1.mysql条件查询的时候如有函数计算不走索引，但是走全表扫的话优化器也会选择小的索引树。
因为函数计算需要计算才能得到值，会导致失去有序性，由于索引树都是有序的。
下图就是mysql的索引，如果获取所有年份的7月节点，对于MySQL使用month(字段)来计算，但是计算后无法从根节点进行查找。

2.隐式类型转换
当条件中类型不符时会进行隐式转换，此时回到条件中含有函数计算。
select * from tradelog where CAST(tradid AS signed int) = 110717;
3.字符集不同时

十九
查询慢的原因：
1。当对一行数据进行update时都会生成undolog，形成版本链，为了MVCC。当为一致性读时，MySQL会根据undolog计算出当前应该显示那个版本。所以会查询慢，此时使用当前读会查询很快。
2。表锁，行锁，全局锁

二十
幻读：
针对当前读说的，对于RR级别没有幻读现象。幻读是对于同一个事务，查询的后发现前一次查询为出现的行。幻读主要对于新插入的行。

假设的前提下：针对select * from t where d=5 for update;仅对d=5这一行加锁

下面session A只有在提交时候才会写入binlog，所以在binlog中A会覆盖B，C的update值。

假设的前提下：针对select * from t where d=5 for update;对所有行加锁，此时update对所有行的修改都会堵塞故需等待A提交事务后才会进行update，但是无法针对insert，因为只是对所有行进行了加锁。

间隙锁：解决幻读问题，因为幻读针对插入无法锁定

对于： select * from t where d=5 for update 会添加不光光对所有行进行了加锁，而且对间隙也进行了加锁。
间隙锁和行锁结合是next-key lock。间隙锁仅针对插入，在间隙update等操作是不会堵塞的不冲突

总结
前缀索引，redolog刷盘时机，binlog和redolog crash情况分析，释放表空间问题，count函数的优化
orderby原理（全字段排序、rowid排序），不走索引和查询慢等问题，幻读和间隙锁。