Mysql学习、总结、面试题

一、学习篇

1.1 学习笔记

个人学习笔记

1.2 书籍

这里推荐两本书：

1、深入浅出MySql：这是一本MySql入门的基础书籍。
2、高性能Mysql:这 是一本MySql进阶的书籍

百度云盘链接
链接：https://pan.baidu.com/s/1gfkv3Dunpxjt13Ldbi1d4g
提取码：1314

1.3 一些认为不错的文章

美团大佬谈MySql

二、常见面试题

MySQL 索引的数据结构
为什么使用 B+ 树，与其他索引相比有什么优点
各种索引之间的区别
B+ 树在进行范围查找时怎么处理
MySQL 索引叶子节点存放的是什么
联合索引（复合索引）的底层实现
MySQL 如何锁住一行数据
SELECT 语句能加互斥锁吗
多个事务同时对一行数据进行 SELECT FOR UPDATE 会阻塞还是异常
MySQL 使用的版本和执行引擎
MySQL 不同执行引擎的区别
MySQL 的事务隔离级别
MySQL 的可重复读是怎么实现的
MySQL 是否会出现幻读
MySQL 的 gap 锁
MySQL 的主从同步原理
分库分表的实现方案
分布式唯一 ID 方案
如何优化慢查询
explain 中每个字段的意思
explain 中的 type 字段有哪些常见的值
explain 中你通常关注哪些字段，为什么

三、数据库优化

1.数据库性能优化
参考:sql层面：in和exist 减少子查询 存储过程 执行计划 避免 LIKE ‘%parm1’百分号放左边会导致索引失效 避免用select *
数据库层面：索引
应用层面：hibernate二级缓存ehcache
jdbc层面：预编译preparedstatment 及时关闭数据库连接
2.数据库的隔离级别
READ-UNCOMMITTED（未提交读）一个事务B可以读取到另一个事务A尚未提交的变更，对于SELECT语句的执行是以非锁模式进行的。
READ-COMMITTED（已提交读）一个事务B只能够读取另一个事务A已经提交的变更，SELECT的时候无法重复读，即同一个事务中两次执行同样的查询语句，若在第一次与第二次查询之间时间段，其他事务又刚好修改了其查询的数据且提交了，则两次读到的数据不一致
REPEATABLE—READ（可重复读）在一个事务内部，多次对相同记录读取会得到相同的结果(利用快照缓存)，只有当自己提交了之后才能读到其它事务提交的数据
SERIALIZABLE（串行化）一个事务对某条记录的变更完成之前，另一个事务是不能使用（读、写）这条记录的，只允许读-读并发，读写是冲突的
mysql默认的事务处理级别是’REPEATABLE-READ’,也就是可重复读
3.脏读
当前事务可以查看到其他事务未提交的数据。只出现在未READ-UNCOMMITTED中。
4.如何解决脏读？
可以用MVCC机制，因为未提交读的级别下，select直接去主内存里面读，所以可能读到没有提交的数据，而MVCC是读磁盘中的快照(undolog),已提交读级别下直接读最新的快照就可以避免脏读；或者读时加共享锁，修改时加排他锁也可以解决脏读
5.不可重复读
在一个事务的两次查询中数据不一致，即查询相同数据范围时，同一个数据资源莫名改变了(侧重在于更新)，出现在READ-UNCOMMITTED和READ-COMMITTED
6.幻读
在一个事务的两次查询中数据不一致，即使用相同查询语句，第二次莫名多出了之前不存在的数据或不见了一些数据(侧重在于新增和删除)，出现在READ-UNCOMMITTED和READ-COMMITTED和REPEATABLE—READ
7.数据库单表数据量上限?
数据库单表达到500万行或单表容量超过2GB时会出现性能瓶颈推荐分库分表(阿里java开发手册上说的)，因为MySQL 为了提高性能，会将表的索引装载到内存中，InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降
8.mysql引擎？
mysql引擎分为Myiasm（不提供事务和行级锁和外键约束）和innodb(mysql5.5之后是默认的引擎提供了对事务的ACID操作，还提供了行级锁和外键约束)
9.mysql参数优化
innodb_buffer_pool_size=50G 索引缓存 一般为物理内存的60%-70%
innodb_io_capacity=20000每秒后台进程处理IO数据的上限，一般为IO QPS总能力的75%
innodb_page_size=4k 每次刷磁盘的大小
10.limit分页到后面的时候性能差？
原因出在Limit的偏移量offset上，比如limit 100000,10虽然最后只返回10条数据，但是偏移量却高达100000，数据库的操作其实是拿到100010数据，然后返回最后10条
解决思路跳过前面的100000条，通过走覆盖索引的方式(原来是select *from mytbl order by id limit 100000,10)(现在是select from mytbl where id>=
(select id from mytbl order by id limit 100000,1) limit 10)走了id的索引就快了
11.索引树的高度？
一个千万量级，且存储引擎是MyISAM或者InnoDB的表，其索引树的高度在3~5之间，现在我们假设表3000W条记录（因为2^{25=33554432），如果每个节点保存64个索引KEY,那么索引的高度就是（log2}25)/log64≈ 25/6 ≈ 4.17
12.MySQL中有一条SQL比较慢，如何排查？
一. explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描
二. 查看SQL的执行计划， 看有没有走索引。
三. 更深入一些的临时表创建(当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后在这些表运行查询,创建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字,临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间)
四. 回表(数据库根据索引找到了指定的记录所在行后(比如select * 去查了索引之外的字段)，还需要根据rowid再次到数据块里取数据的操作,回表”一般就是指执行计划里显示的“TABLE ACCESS BY INDEX ROWID”,将需要的字段放在索引中去,查询的时候就能避免回表)
五. 索引覆盖(如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表只扫描索引而无需回表的优点：
1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。
当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息)
六. 驱动表(即需要从驱动表中拿出来每条记录，去与被驱动表的所有记录进行匹配探测)
13.DB索引的实现
B树 为了磁盘或其它存储设备而设计的一种多叉（相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树，B树的特点：
（1）所有键值分布在整个树中
（2）任何关键字出现且只出现在一个节点中
（3）搜索有可能在非叶子节点结束
（4）在关键字全集内做一次查找，性能逼近二分查找算法
B+树 是B树的变体，也是一种多路平衡查找树，B+树与B树的不同在于：
（1）所有关键字存储在叶子节点，非叶子节点不存储真正的data
（2）为所有叶子节点增加了一个链指针
14.为什么mysql的索引使用B+树而不是B树呢？
(1）B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。
（2）mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。
1.单一节点存储更多的元素，使得查询的IO次数更少。
2.所有查询都要查找到叶子节点，查询性能稳定。
3.所有叶子节点形成有序链表，便于范围查询。
15.B+树和哈希表的优缺点
1.哈希不冲突的情况下查询O(1)比B+树O(logN)树快
2.哈希表冲突的情况下需要扩容有性能损耗，冲突后查询性能就不稳定了，B+数查询性能稳定
3.哈希表是无序的，只能等值查询不能范围查询，B+数可以范围查询
16.联合索引(a,b,c),走不走a索引,走不走b索引,走不走ab索引
最左前缀原则，index:(a,b,c)走a,ab和abc索引，其实这里说的有一点问题，a,c也走，但是只走a字段索引，不会走c字段。
另外还有一个特殊情况说明下，select * from table where a = ‘1’ and b > ‘2’ and c=‘3’ 这种类型的也只会有a与b走索引，c不会走。
索引是有序的，index1索引在索引文件中的排列是有序的，首先根据a来排序，然后才是根据b来排序，最后是根据c来排序，
像select * from table where a = ‘1’ and b > ‘2’ and c=‘3’ 这种类型的sql语句，在a、b走完索引后，c肯定是无序了，所以c就没法走索引，数据库会觉得还不如全表扫描c字段来的快
注意：联合索引在B+树中是每个节点都包含了联合索引中的所有字段，而不是只包含了第一个字段
17.MVCC多版本并发控制
主要适用于重复读和已提交读隔离级别，使用的是行级锁(行的版本控制)
MVCC的实现，通过保存数据在某个时间点的快照来实现的（基于undolog）。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。
每行数据都存在一个版本，每次数据更新时都更新该版本
18.innodb的MVCC实现
在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号
插入新纪录，把事务号写入创建版本号，删除版本号为空
更新记录，把旧记录标记为删除，删除版本号写入当前事务版本号，然后插入新纪录创建版本号为当前事务版本号，删除版本号为空
删除记录，把记录删除版本号为当前事务版本号
查询记录，删除版本号未指定或大于当前事务版本号，即查询事务开启时确保读取的行未被删除；创建版本号小于或者等于当前事务版本号，即创建是在当前事务启动之前进行的
19.mysql锁的类型
读锁:也叫共享锁、S锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改
写锁:又称排他锁、X锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
行级锁:操作对象是数据表中的一行。是MVCC技术用的比较多的，但在MYISAM用不了，行级锁用mysql的储存引擎实现而不是mysql服务器。但行级锁对系统开销较大，处理高并发较好
表锁:操作对象是数据表。Mysql大多数锁策略都支持（常见mysql innodb），是系统开销最低但并发性最低的一个锁策略。
gap锁:解决RR可重复读隔离级别下出现幻读的问题，就是在这个事务关联的数据行范围里的每个数据间隙之间加锁，防止间隙之间被增删了数据从而引起幻读
20.索引优化
1.更新频繁、数据区分度不高的字段上不宜建立索引(更新频繁的字段建立索引会大大降低数据库性能，区分度可以使用 count(distinct(列名))/count() 来计算，也叫基数，在80%以上的时候就可以建立索引)
2.索引列中不要有null，单列索引不存null值，复合索引不存全为null的值，如果列允许为 null，可能会得到不符合预期的结果集
3.避免使用or来连接条件，应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描
4.使用覆盖索引，可以避免回表
5.联合索引最左前缀原则
21.聚集索引,非聚集索引,聚簇索引,稠密索引,稀疏索引
聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引，聚集索引的顺序就是数据的物理存储顺序
非聚集索引：索引顺序与物理存储顺序不同，非聚集索引必须是稠密索引
聚簇索引:并不是一种单独的索引类型，而是一种数据存储方式,聚簇索引指向的是一个数据块(多行组成的，可以把相关数据保存在一起)，就好像在操场上战队，一个院系一个院系的站在一起，这样要找到一个人，就先找到他的院系，然后在他的院系里找到他就行了，而不是把学校里的所有人都遍历一遍
稠密索引：每个索引键值都对应有一个索引项，一一对应的
稀疏索引：相对于稠密索引，稀疏索引只为某些搜索码值建立索引记录，在搜索时，找到其最大的搜索码值小于或等于所查找记录的搜索码值的索引项，然后从该记录开始向后顺序查询直到找到为止
22.并发update多行死锁问题
并发update情况加锁顺序是如果用到主键索引，会先给主键行(主键索引)加锁，再给非主键索引加锁，如果只用到了非主键索引，会先锁非主键索引，在给主键行(主键索引)加锁。当第一条sql只用了非主键索引的where条件，第二条sql用到了主键索引和非主键索引的where条件，那第一条sql先锁非主键索引在尝试锁主键，而第二条先锁主键在尝试锁非主键索引就可能会死锁
update死锁的解决方法是先获取需要更新的记录的主键，然后再根据主键去逐条更新，逐条更新的for循环外面套一个事务(或者直接用in语句)