blog.csdn.net/early_or_la… blog.csdn.net/Allenzyg/ar…
09 普通索引和唯一索引
原理
- InnoDB的数据是按数据页为单位来读写的。
- 当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。
- 在InnoDB中，每个数据页的大小默认是16KB zhuanlan.zhihu.com/p/379092178
change buffer
- 数据页不在内存中的话会将更新操作缓存在change buffer中，等下次查询需要访问这个数据页的时候，将数据页读入内存，执行change buffer与这个页有关的操作。
- 将change buffer中的操作应用到原始数据页过程叫merge
zhuanlan.zhihu.com/p/106297543
读过程
引擎按页读写
- 对于唯一索引找到记录的时候它所在的数据就在内存里
- 对于普通索引要多做的那一次查找和判断下一次记录的操作
- 对于cpu来说可以忽略不计
更新过程
- 对于唯一索引，需要将数据页读入内存，判断没有冲突，插入
- 对于普通索引（不在内存），将更新记录在change buffer
- 将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。changebufeer因此减少随机磁盘访问，所以对更新性能是会很明显的。
change buffer使用场景
- 写多读少的业务来说，页面写完以后马上被访问到的概率比较小，此时change buffer使用效果最好
总结
- 两类索引查询能力删没差别，主要考虑对更新性能的影响，建议选择普通索引。
10 MySQL为什么有时候会选错索引？
优化器的逻辑
- 扫描行数越少，意味着访问磁盘数据的次数更少，消耗cpu的资源越少
- 结合是否使用临时表，是否排序等因素进行综合判断
扫描行数的判断
show index 查看索引基数
优化器预估两个语句行数是多少
优化器使用索引a，每次索引a拿到一个值，都会回表，这个代价也会算进去，如果扫描10w行，是直接在主索引扫描，没有代价。
优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。
delete 语句删掉了所有的数据，然后再通过 call idata() 插入了 10 万行数据，看上去是覆盖了原来的 10 万行。但是，session A 开启了事务并没有提交，所以之前插入的 10 万行数据是不能删除的。
这样，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。这样，索引 a 上的数据其实就有两份。
主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是 show table status 的值。
慢日志查询 show log 使用force index(a) 让优化器强制使用索引 a
我们平常不断地删除历史数据和新增数据的场景，MySQL竟然会选错索引。在实践中，如果你发现explain的结果预估的rows值跟实际情况差距很大，可以使用analyze table t命令，用来重新统计索引信息。
如果MySQL索引选错
- 使用force index 强行选择一个索引。
- 修改语句，引导MySQL使用我们期望的索引。
- 新建一个更合适的索引，删掉误用的索引。
11 怎么给字符串字段加索引？
mysql> alter table SUser add index index1(email); mysql> alter table SUser add index index2(email(6));
直接创建索引（比较占用空间 : 索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。）
- 在索引index1树满足索引值取得ID2，到主键上查找主键值是ID2的行判断正确加入结果集，取index1索引树上查到的位置的下一条记录发现不满足，循环结束。
前缀索引（节省空间，增加查询扫描次数，不能使用覆盖索引）
- 先在index2索引树查找取得ID1，到主键上查到主键值是ID1的行判断email值正确加入结果集。跟普通索引思想差不多，但是前缀索引会导致语句读数据的次数变多。
前缀索引定义好长度，可以做到省空间，又不用额外增加太多的查询成本。
例子 身份证
- 使用倒叙存储（前缀索引，绕过字符串本身前缀的区分度不够的问题）
mysql> select field_list from t where id_card = reverse('input_id_card_string');
- 使用hash字段（查询稳定，额外的存储，不支持范围扫描）
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc); mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
reverse函数额外消耗的CPU会比较小，hash字段的查询效率相对更稳定，查询的平均函数接近1，倒叙使用前缀的方式会增加行数。
12 为什么我的MySQL会抖一下？
InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作。
脏页
- 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。
- 内存数据写入到磁盘后，内存和磁盘上的数据页内容就一致了，称为干净页。
情况
- redo log写满了，要flush脏页，所有更新必须堵住。
- 内存不够用，要先将脏页写到磁盘。InnoDB用缓冲池buffer pool管理内存。
内存页有三种状态：
- 还没有使用的
- 使用了并且是干净页
- 使用了并且是脏页
当要读入的数据页没有在内存时候，就必须要到缓冲池中申请一个数据页。
这时候需要淘汰。如果淘汰的是干净页很OK。如果是脏页，就必须先刷到磁盘变成干净后才能复用。
脏页的影响
- 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
- 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。
避免
- 要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。其中，脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具体的命令参考下面的代码：
- 在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。
13 为什么数据表删掉一半，表文件大小不变？
参数 innodb_file_per_table
- off 表的数据存放在系统共享表空间（即使表删掉了，空间也不会回收的）
- on 每个InnoDB表数据存储在一个以.ibd为后缀的文件中
我们用delete命令把整个表的数据删除，所有的数据页都会被标记可复用，磁盘上，文件不会变小。
如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能会造成索引的数据页分裂。
经过大量增删改查的表，都是可能存在空洞的。而重建表就可以达到这样的目的。
重建表
alter table A engine = InnoDB命令来重建表。
alter table t egine=innodb，algorithm=inplace
MySQL5.6版本开始引入Online DDL，这样的话在重建表的过程允许对表A做增删改查操作。
analyze table t 对表的索引信息座重新统计，没有修改数据，过程加了MDL读锁 optmize table t = recreate + analyze
14 count(*)这么慢，我该怎么办？
InnoDB优点
- 事务支持：redolog持久性，undolog原子性，MVCC+锁隔离性
- 并发能力：行锁
- 数据安全：持久到磁盘
InnoDB默认的隔离级别是 可重复读，在代码上就是通过多版本并发控制，MVCC来实现的。每一行记录都要判断自己是否对这个会话可见。InnoDB只好把数据一行一行地读出依次判断。
按效率排序的话
count(字段)<count(主键id)<count(1)~count(*)
count(主键 id) （涉及解析解析数据行）
- InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层，server层拿到id后，判断是不可能为空的，就按行累加。
count(1)
- InnoDB引擎遍历整张表，但不取值。server鞥对于返回的每一行，放一个数字1进去，判断是不可能为空的，按行累加。
count(字段)
- 如果这个字段是定义not null，一行行地从记录里面读出这个字段，判断不能为null，按行累加
- 如果这个字段定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来在判断一下，不是null才累加
count(*)
- 并不会把全部字段取出来，而是专门做了优化，不取值。
把计数值放在MySQL中，解决了一致性视图的问题。 InnoDB引擎支持事务，利用好事务的原子性和隔离性，简化在业务开发时的逻辑。
从并发系统性能的角度考虑，应该先插入操作记录，再更新计数表。 因为更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少了事务之间的锁等待，提升了并发度。
15.日志索引相关问题
两阶段提交示意图
时刻A，MySQL异常
这个时候 事务会滚，binlog还没写，也不会传到备库。
时刻B，MySQL异常
redo log还没提交。 如果redo log里面的事务是完整的，也就是已经有了commit表示，则直接提交。 如果redo log里面的事务只有完整的prepare
- binlog存在，则提交事务
- binlog不存在，回滚事务
MySQL怎么知道binlog是完整的
statement 格式的 binlog，最后会有 commit； row 格式的 binlog，最后会有一个 xid event。
redo log 和 binlog是怎么关联起来的？
他们有一个共同的数字字段 xid 崩溃恢复的时候，会按顺序扫描redo log
为什么还要两阶段提交呢？
对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。 而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。
16."order by"是怎么工作的？
select city,name,age from t where city='杭州' order by name limit 1000 ；key是city
using filesort 表示的就是需要排序；MySQL会给每个线程分配一块内存用于排序称为sort_buffer。
MySQL之所以需要生成临时表并且在临时表上做排序操作，其原因是原来的数据都是无序的。 并不是所有的order by语句都需要排序操作。
如何优化？-- using fiersort
创建一个city和name的联合索引
alter table t add index city_user(city, name);
联合索引本身有序
进一步优化 --> using index
alter table t add index city_user_age(city, name, age);
extra字段多了using index 表示使用了覆盖索引，性能会快很多。
问题
如何实现一个数据库端不需要排序的方案？
有分页需求，要显示第101页，limit 10000,100，实现方法怎么办？
17.如何正确地显示随机消息
英语学习app首页有一个随机显示单词的功能
内存临时表
mysql> select word from words order by rand() limit 3;
extra 字段显示 using temporary 表示的是需要使用临时表；using filesort表示的是需要执行排序操作
order by rand 使用了内存临时表，内存临时表排序的时候使用 rowid排序方法。
rowid：每个引擎用来唯一标识数据行的信息。有主键，rowid就是主键id。没有主键由rowid系统生成的。
随机排序方法
mysql> select max(id),min(id) into @M,@N from t ; set @X= floor((@M-@N+1)*rand() + @N); select * from t where id >= @X limit 1;
18.为什么这些SQL语句逻辑相同，性能却差异巨大？
案例一：对字段做了函数计算，就用不上索引了
mysql> select count() from tradelog where month(t_modified)=7;
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器决定放弃走树搜索给你。
explain说明语句扫描了整个索引的所有值
优化方式
mysql> select count() from tradelog where
-> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
-> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
-> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');
案例二：隐式类型转换
mysql> select * from tradelog where tradeid=110717; tradeid字段类型是varchar(32) 而 输入的参数是整型。 对于优化器来说 = mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
案例三：隐式字符编码转换
交易日志表tradelog 和交易详情表 trade_detail表
mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /语句Q1/
原因是两个表的字符集不同，一个是utf8另一个是uft8mb4
优化1：
把trade_detail表上的tradeid字段的字符集也改成utf8mb4。
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
优化2:
遇到业务数据量大的时候不能做ddl的时候
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
19.为什么我只查一行的语句，也执行这么慢？
第一类：查询长时间不返回
可能是表被锁住 使用 show processlist命令
等mdl锁，查询系统表并kill掉
等flush
等行锁
查询谁占用这个写锁
第二类：查询慢
select * from t where id=1
select * from t where id=1 lock in share mode
session B更新完100万次，生成了100万哥回滚日志。
带lock in share mode的SQL语句，是当前读，直接读到1000001这个结果，速度快。
第一个与是一致性读，需要从1000001开始，依次undo log。
20.幻读是什么，幻读有什么问题？
幻读
在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。 幻读仅专指“新插入的行”。
幻读产生的问题
语义被破坏 数据和日志的逻辑不一致性
如何解决
为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。 间隙锁和行锁合称 next-key lock。 间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，其实是影响了并发度的。
