Mysql
本文回答以下问题，文内可能有遗漏、错误或表达不够清晰的地方。
① Mysql(innondb) 有哪几种事务隔离级别？不同事务隔离级别分别会加哪些锁？
② mysql的行锁、表锁、间隙锁、意向锁分别是做什么的？
③ 索引的数据结构？mysql索引为什么用的是b+ tree而不是b tree、红黑树?
④ 说说什么是最左匹配？什么是聚簇索引和非聚簇索引？
⑤ sql查询优化？（explain等命令）如何优化慢查询？
⑥ 分库分表如何选择分表键，分库分表的情况下，查询时一般是如何做排序的？
① Mysql(innondb) 有哪几种事务隔离级别？不同事务隔离级别分别会加哪些锁？
可重复读（repeatable read）隔离级别，通过MVCC快照读和next-key(记录锁+间隙锁)两种模式解决幻读问题。
MVCC机制
Mysql在可重复读隔离级别下保证了事务较高的隔离性，同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。
MySQL的InnoDB引擎实现MVCC的3个基础点
- 隐式字段
- undo log
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用隐藏字段把这些undo日志串联起来形成一个历史记录版本链。
- read-view一致性视图
在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化。
- binlog、undolog、redolog日志
binlog 属于Servie 层，是所有存储引擎都有的；undolog、redolog 是innodb存储引擎特有的
undolog：当事务提交失败，可以用undo日志里的数据恢复Buffer Pool里的缓存数据。MySQL实现回滚操作完全依赖于undo log。
redolog ：当Buffer Pool缓存池里的数据还没来得及刷磁盘，万一数据丢失（数据库宕机），当下次数据库重启的时候，后台IO线程重做、恢复、刷新Buffer Pool里的数据。
② mysql的行锁、表锁、间隙锁、意向锁分别是做什么的？
表锁：每次操作锁住整张表，开销小加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 一般用在整表数据迁移的场景。
行锁：每次操作锁住一行数据，开销大加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
MYISAM只支持表锁，InnoDB还支持行锁；表锁和行锁有各自的读写锁实现。
MyISAM 在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。 （表锁共享锁、表锁排他锁）
InnoDB 在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁在某些情况下可以解决幻读问题。
加意向锁的目的是为了表明某个事务正在锁定一行或者将要锁定一行；为了解决加了行锁后、再加表锁的问题（发生冲突、需要遍历整张表判断是否加了行锁、从而实现表锁快速失败机制）。
③ 索引的数据结构？mysql索引为什么用的是b+ tree而不是b tree、红黑树?
索引数据结构是 B+ 树。
B树非叶子节点也会存储数据，导致树的高度很高，查磁盘次数增加；
B+树非叶子节点不存储数据只存储索引，故而可以放更多的索引，举例：mysql页大小/节点大小为16k，一颗高度为3的B+树能存储的数据为1170117016= 2千万记录。（16KB/8B+6B = 1170、16KB/1KB=16：主键一般是bigint类型占用8B、存储下一层索引的地址大小占用6B、叶子节点按照1KB计算）另外，B+树的叶子节点间用指针相连，从而提高区间访问的性能；
红黑树是一颗平衡二叉树，数据量大的时候，树的深度也很深，如果树的深度有20层，而查找的数据在叶子节点，就要进行20次IO操作，性能低。
④ 说说什么是最左匹配？什么是聚簇索引和非聚簇索引？
最左匹配原则：从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配。
聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。
聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行。
非聚簇索引：将数据进和索引分开存储，索引叶子节点存储的是指向数据行的地址。
在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，也是聚簇索引，在主索引之上创建的索引为辅助索引，是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢，因为辅助索引中的叶子节点存储的是主键。
在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。
⑤ sql查询优化？（explain等命令）如何优化慢查询？
拿到 sql 先看执行计划，先干掉全表扫描（执行计划结果 type 列表示关联类型，ALL最差），如果索引建立的很差，mysql 也会走全表扫描（重建或者建立索引）
如果表是小表，全表扫描不一定是坏事；当表是超大型表几百G那种，即使关联字段建立了索引，因为扫描索引，在内存哈希散列或者排序区放不下了，导致必须借助磁盘排序，非常慢，还不如全表扫描小表，去嵌套循环大表（小的数据集驱动大的数据集）
Order by与Group by优化
执行计划 Extra 列出现：覆盖索引(Using index)、文件排序(Using filesort)等
MySQL支持两种方式的排序，filesort和index，index是MySQL扫描索引本身完成排序，index效率高，filesort效率低。order by语句使用索引最左前列或where与order by组合满足使用索引最左前列，就会使用Using index排序；如果order by的条件不在索引列上，就会产生Using filesort。
索引优化
覆盖索引，索引上的信息足够满足查询请求，不需要回表。（不要使用 select * 语句）
联合索引，符合最左前缀法则。查询从索引的最左前列开始并且不跳过索引中的列，遇到范围查询(>、<、between、like)会停止匹配。范围查询后的条件不能用索引，范围查询字段可能用到索引可能没用到；like KK%相当于常量，%KK和%KK% 相当于范围
索引下推，是联合索引上的优化。在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数（如果索引中包含了该字段信息会直接进行过滤不会再回表比对）。联合索引不仅用到模糊查询字段，也使用到后面所有索引字段过滤，从而减少回表次数
避免索引失效
不在索引列上做任何操作（计算、函数）
不要在小基数字段上建立索引
长字符串我们可以采用前缀索引，但用order by会失效
is not null 和 不等于 不会使用索引
or查询条件每一列都要建索引
⑥ 分库分表如何选择分表键，分库分表的情况下，查询时一般是如何做排序的？
https://blog.csdn.net/abckingaa/article/details/123026402
- 对于单库，冗余一个汇总所有数据表，用于全部数据的排序，但是当数据量大，汇总表将会成为瓶颈。这不是一个很好的方案。
- 无论单库还是多个分库，都由程序读取需要数据并作排序。
网友评论