MySQL常考知识点

作者：逍遥Sean
简介：一个主修Java的Web网站\游戏服务器后端开发者
主页：https://blog.csdn.net/Ureliable
觉得博主文章不错的话，可以三连支持一下~ 如有需要我的支持，请私信或评论留言！

索引的基本原理

索引⽤来快速地寻找那些具有特定值的记录。如果没有索引，⼀般来说执⾏查询时遍历整张表。
索引的原理：就是把⽆序的数据变成有序的查询

1. 把创建了索引的列的内容进⾏排序
2. 对排序结果⽣成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从⽽拿到具体数据

索引设计的原则

查询更快、占⽤空间更⼩

1. 适合索引的列是出现在where⼦句中的列，或者连接⼦句中指定的列
2. 基数较⼩的表，索引效果较差，没有必要在此列建⽴索引
3. 使⽤短索引，如果对⻓字符串列进⾏索引，应该指定⼀个前缀⻓度，这样能够节省⼤量索引空间，如果搜索词超过索引前缀⻓度，则使⽤索引排除不匹配的⾏，然后检查其余⾏是否可能匹配。
4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进⾏更新甚⾄重构，索引列越多，这个时间就会越⻓。所以只保持需要的索引有利于查询即可。
5. 定义有外键的数据列⼀定要建⽴索引。
6. 更新频繁字段不适合创建索引
7. 若是不能有效区分数据的列不适合做索引列(如性别，男⼥未知，最多也就三种，区分度实在太低)
8. 尽量的扩展索引，不要新建索引。⽐如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
9. 对于那些查询中很少涉及的列，重复值⽐较多的列不要建⽴索引。
10. 对于定义为text、image和bit的数据类型的列不要建⽴索引。

事务的基本特性和隔离级别

事务基本特性ACID分别是：
原⼦性： 指的是⼀个事务中的操作要么全部成功，要么全部失败。
⼀致性： 指的是数据库总是从⼀个⼀致性的状态转换到另外⼀个⼀致性的状态。⽐如A转账给B 100块钱，假设A只有90块，⽀付之前我们数据库⾥的数据都是符合约束的,但是如果事务执⾏成功了,我们的数据库数据就破坏约束了,因此事务不能成功,这⾥我们说事务提供了⼀致性的保证
隔离性： 指的是⼀个事务的修改在最终提交前，对其他事务是不可⻅的。
持久性： 指的是⼀旦事务提交，所做的修改就会永久保存到数据库中。

隔离性有4个隔离级别，分别是：

1. read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。⽤户本来应该读取到id=1的⽤户age应该是10，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。
2. read commit 读已提交，两次读取结果不⼀致，叫做不可重复读。不可重复读解决了脏读的问题，他只会读取已经提交的事务。⽤户开启事务读取id=1⽤户，查询到age=10，再次读取发现结果=20，在同⼀个事务⾥同⼀个查询读取到不同的结果叫做不可重复读。
3. repeatable read 可重复复读，这是mysql的默认级别，就是每次读取结果都⼀样，但是有可能产⽣幻读。
4. serializable 串⾏，⼀般是不会使⽤的，他会给每⼀⾏读取的数据加锁，会导致⼤量超时和锁竞争
   的问题。

什么是MVCC

MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使⽤READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执⾏普通的SEELCT操作时访问记录的版本链的过程。可以使不同事务的读-写、写-读操作并发执⾏，从⽽提升系统性能。
READ COMMITTD、REPEATABLE READ这两个隔离级别的⼀个很⼤不同就是：⽣成ReadView的时机不同，READ COMMITTD在每⼀次进⾏普通SELECT操作前都会⽣成⼀个ReadView，⽽REPEATABLEREAD只在第⼀次进⾏普通SELECT操作前⽣成⼀个ReadView，之后的查询操作都重复使⽤这个ReadView就好了。

简述MyISAM和InnoDB的区别

MyISAM：

• 不⽀持事务，但是每次查询都是原⼦的；
• ⽀持表级锁，即每次操作是对整个表加锁；
• 存储表的总⾏数；
• ⼀个MYISAM表有三个⽂件：索引⽂件、表结构⽂件、数据⽂件；
• 采⽤⾮聚集索引，索引⽂件的数据域存储指向数据⽂件的指针。辅索引与主索引基本⼀致，但是辅索引不⽤保证唯⼀性。

InnoDb：

• ⽀持ACID的事务，⽀持事务的四种隔离级别；
• ⽀持⾏级锁及外键约束：因此可以⽀持写并发；
• 不存储总⾏数；
• ⼀个InnoDb引擎存储在⼀个⽂件空间（共享表空间，表⼤⼩不受操作系统控制，⼀个表可能分布在多个⽂件⾥），也有可能为多个（设置为独⽴表空，表⼤⼩受操作系统⽂件⼤⼩限制，⼀般为2G），受操作系统⽂件⼤⼩的限制；
• 主键索引采⽤聚集索引（索引的数据域存储数据⽂件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使⽤⾃增主键，防⽌插⼊数据时，为维持B+树结构，⽂件的⼤调整。

Explain语句结果中各个字段分表表示什么

列名	描述
id	查询语句中每出现⼀个SELECT关键字，MySQL就会为它分配⼀个唯⼀的id值，某些⼦查询会被优化为join查询，那么出现的id会⼀样
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的查询⽅式（全表扫描、索引）
possible_keys	可能⽤到的索引
key	实际上使⽤的索引
key_len	实际使⽤到的索引⻓度
ref	当使⽤索引列等值查询时，与索引列进⾏等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分⽐
Extra	⼀些额外的信息，⽐如排序等

索引覆盖是什么

索引覆盖就是⼀个SQL在执⾏时，可以利⽤索引来快速查找，并且此SQL所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL⾛完索引后不⽤回表了，所需要的字段都在当前索引的叶⼦节点上存在，可以直接作为结果返回了

最左前缀原则是什么

当⼀个SQL想要利⽤索引是，就⼀定要提供该索引所对应的字段中最左边的字段，也就是排在最前⾯的字段，⽐如针对a,b,c三个字段建⽴了⼀个联合索引，那么在写⼀个sql时就⼀定要提供a字段的条件，这样才能⽤到联合索引，这是由于在建⽴a,b,c三个字段的联合索引时，底层的B+树是按照a,b,c三个字段从左往右去⽐较⼤⼩进⾏排序的，所以如果想要利⽤B+树进⾏快速查找也得符合这个规则

B树和B+树的区别，为什么Mysql使⽤B+树

B树的特点：

1. 节点排序
2. ⼀个节点了可以存多个元素，多个元素也排序了
   B+树的特点：
3. 拥有B树的特点
4. 叶⼦节点之间有指针
5. ⾮叶⼦节点上的元素在叶⼦节点上都冗余了，也就是叶⼦节点中存储了所有的元素，并且排好顺序

Mysql索引使⽤的是B+树，因为索引是⽤来加快查询的，⽽B+树通过对数据进⾏排序所以是可以提⾼查询速度的，然后通过⼀个节点中可以存储多个元素，从⽽可以使得B+树的⾼度不会太⾼，在Mysql中⼀个Innodb⻚就是⼀个B+树节点，⼀个Innodb⻚默认16kb，所以⼀般情况下⼀颗两层的B+树可以存2000万⾏左右的数据，然后通过利⽤B+树叶⼦节点存储了所有数据并且进⾏了排序，并且叶⼦节点之间有指针，可以很好的⽀持全表扫描，范围查找等SQL语句。

Mysql锁有哪些，如何理解

按锁粒度分类：

1. ⾏锁：锁某⾏数据，锁粒度最⼩，并发度⾼
2. 表锁：锁整张表，锁粒度最⼤，并发度低
3. 间隙锁：锁的是⼀个区间

还可以分为：

1. 共享锁：也就是读锁，⼀个事务给某⾏数据加了读锁，其他事务也可以读，但是不能写
2. 排它锁：也就是写锁，⼀个事务给某⾏数据加了写锁，其他事务不能读，也不能写

还可以分为：

1. 乐观锁：并不会真正的去锁某⾏记录，⽽是通过⼀个版本号来实现的
2. 悲观锁：上⾯所的⾏锁、表锁等都是悲观锁

在事务的隔离级别实现中，就需要利⽤锁来解决幻读

Mysql慢查询该如何优化？

1. 检查是否⾛了索引，如果没有则优化SQL利⽤索引
2. 检查所利⽤的索引，是否是最优索引
3. 检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
4. 检查表中数据是否过多，是否应该进⾏分库分表了
5. 检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源