MySQL整理-CSDN博客

本文链接：https://blog.csdn.net/X18160013/article/details/132502442

Mysql引擎 MyISAM与InnoDB的区别

MyISAM：是MySQL 5.5及之前版本的默认引擎，InnoDB：MySQL 5.5后成为默认索引

数据存储的方式不同：MyISAM中的数据和索引是分开存储的（.MYD数据文件，.MYI索引文件），而InnoDB是在同一个文件里面(ibd文件)；
对于事务的支持不同：MyISAM不支持事务，而InnoDB支持事务；
对于锁的支持不同：MyISAM只支持表锁，而InnoDB可以根据不同的情况，支持行锁，表锁，采用MVCC来支持高并发；
MyISAM不支持外键，InnoDB支持外键；
MyISAM支持全文索引，InnoDB不支持全文索引；
MyISAM不支持崩溃后的安全恢复，InnoDB支持崩溃后的安全恢复。

如果需要支持事务，那必须要选择InnoDB。
如果大部分的表操作都是查询，可以选择MyISAM
如果系统崩溃导致数据难以恢复，且成本高，不要选择MyISAM

范式和反范式的优缺点？

名称	优点	缺点
范式	范式化的表减少了数据冗余，数据表更新操作快、占用存储空间少。	查询时通常需要多表关联查询，更难进行索引优化
反范式	反范式的过程就是通过冗余数据来提高查询性能，可以减少表关联和更好进行索引优化	存在大量冗余数据，并且数据的维护成本更高

数据库建表为什么用索引？用索引为什么快？

索引，是一种能够帮助Mysql高效从磁盘上检索数据的一种数据结构。在InnoDB引擎中，采用了B+树的结构来实现索引和数据的存储。

B+树索引在进行范围查找的时候，只需要找到起始节点，然后基于叶子节点的链表结构往下读取即可，查询效率较高。通过唯一索引约束，可以保证数据表中每一行数据的唯一性。

简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

索引的优缺点

优点：

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

缺点：

数据的增加、修改、删除，需要涉及到索引的维护，当数据量较大的情况下，索引的维护会带来较大的性能开销。
一个表中允许存在一个聚簇索引和多个非聚簇索引，但是索引数不能创建太多，否则造成的索引维护成本过高。
创建索引的时候，如果字段的重复数据过多，创建索引反而会带来性能降低。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

索引有哪几种类型？

物理结构上:

聚簇索引：索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引；
非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。

应用上:

主键索引: 数据列不允许重复，不允许为 NULL，一个表只能有一个主键。
唯一索引: 数据列不允许重复，允许为 NULL 值，一个表允许多个列创建唯一索引。
普通索引: 基本的索引类型，没有什么限制，允许在定义索引的列中插入重复值和 NULL 值。
联合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并，遵守最左前缀匹配规则；
覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。

聚集(聚簇)索引与非聚集索引的区别

聚集(聚簇)索引就是基于主键创建的索引，除了主键索引以外的其他索引，称为非聚集索引，也叫做二级索引。

在InnoDB引擎里面，聚集索引就是按照每张表的主键来构建一颗B+树，然后叶子节点里面存储了这个表的每一行数据记录。

聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式。

也意味着每个表里面必须要有一个主键，如果没有主键，InnoDB会默认选择或者添加一个隐藏列作为主键索引来存储这个表的数据行。一般情况是建议使用自增id作为主键，这样的话id本身具有连续性使得对应的数据也会按照顺序存储在磁盘上，写入性能和检索性能都很高。否则，如果使用uuid这种随机id，那么在频繁插入数据的时候，就会导致随机磁盘IO，从而导致性能较低。

InnoDB里面只能存在一个聚集索引，如果是基于非聚集索引来查询一条完整的记录，最终还是需要访问主键索引来检索。

Mysql为什么使用B+树作为索引结构?

二叉查找树，在二叉树的基础上增加了一个规则，左子树的所有节点的值都小于它的根节点，右子树的所有子节点都大于它的根节点。

B树是一种多路平衡衡查找树，在二叉查找树增加一个规则，它的左右两个子树的高度差的绝对值不超过1；
用B树存储结构来存储大量数据，它的整个高度会相比二叉树来说，会矮很多。而对于数据库而言，所有的数据都将会保存到磁盘上，而磁盘I/O的效率又比较低，特别是在随机磁盘I/O的情况下效率更低。所以 高度决定了磁盘I/O的次数，磁盘I/O次数越少，对于性能的提升就越大。

B+树的所有数据都存储在叶子节点，非叶子节点只存储索引，叶子节点中的数据使用双向链表的方式进行关联。

从磁盘I/O效率方面来看：B+树的非叶子节点不存储数据，所以树的每一层就能够存储更多的索引数量，也就是说，B+树在层高相同的情况下，比B树的存储数据量更多，间接会减少磁盘I/O的次数。
从范围查询效率方面来看：在MySQL中，范围查询是一个比较常用的操作，而B+树的所有存储在叶子节点的数据使用了双向链表来关联，所以B+树在查询的时候只需查两个节点进行遍历就行，而B树需要获取所有节点，因此，B+树在范围查询上效率更高。
从全表扫描方面来看：因为，B+树的叶子节点存储所有数据，所以B+树的全局扫描能力更强一些，因为它只需要扫描叶子节点。而B树需要遍历整个树。
从自增ID方面来看：基于B+树的这样一种数据结构，如果采用自增的整型数据作为主键，还能更好的避免增加数据的时候，带来叶子节点分裂导致的大量运算的问题。

如何判断 SQL 是否走了索引？

使用 EXPLAIN 只需在查询语句开头增加 EXPLAIN 这个关键字即。
EXPLAIN 的参数：
id：select查询中的序号，它决定了表的执行顺序；id相同：从上到下；id不同：序号大的先执行。
select_type：查询的类型；
table：引用的表的名称；
type：访问类型ALL：全表扫描、index: index与ALL区别为index类型只遍历索引树、range:只检索给定范围的行，使用一个索引来选择行；
key：显示了SQL实际使用索引；
rows：找到所需的行而要读取（扫描）的行数，可能不精确；