MySQL学习笔记总结

MySQL的存储引擎

比较常用的：MyISAM、InnoDB

• MyISAM：拥有较高的插入查询速度，但不支持事务，使用表级锁，并发性差，灾难恢复性不佳。MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。
• InnoDB：支持ACID事务，支持行级锁和表级锁，默认使用行级锁，支持外键关联，支持热备份，灾难恢复性好。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MyISAM和InnoDB该如何选择

• InnoDB支持事务、外键、MVCC而MyISAM都不支持
• MyISAM适合频繁查询的应用，适合小数据，小并发而InnoDB适合插入和更新操作比较多的应用，大数据、高并发。

MySQL索引

索引是什么

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成一种排序好的数据结构

索引底层数据结构

• B树
• B+树
• Hash、红黑树

在MySQL中，无论是InnoDB还是MyISAM，都使用了B+树作为索引结构

索引的优缺点

优点

• 大大加快了数据的检索速度
• 通过创建唯一性索引，可以保证数据库表中数据的唯一性

缺点

• 创建维护索引需要耗费很多时间。当对表中数据进行增删改的时候，如果数据有索引，那么索引也需要动态修改，会降低SQL执行效率
• 索引需要使用物理文件存储，也会耗费一定空间

使用索引一定能提高查询性能吗

大多数情况下，索引查询都是比全表查询要快的，但如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升

索引的底层数据结构

Hash表

哈希表是键值对的集合，通过键（key）即可快速取出对应的值（value），因此哈希表可以快速检索数据

为何能够通过key快速取出value呢？ 原因在于哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到key对应的index，找到了index也就找到了对应的value。 但是！哈希算法有个Hash冲突的问题。

何为哈希冲突？ 也就是说多个不同的key最后得到的index是相同的。

通常情况下，我们常用的解决办法是链地址法。链地址法就是将哈希冲突的数据存放在链表中。
就比如JDK1.8之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8以后，HashMap为了减少链表过长的时候搜索时间过长，就引入了红黑树。

既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？ 主要是因为Hash索引不支持顺序和范围查询。 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。
试想一种情况:

SELECT * FROM tb1 WHERE id < 500;

在这种范围查询中，优势非常大，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗?这就是 Hash 最大的缺点了。

B树&B+树

B树也称为B-树，全称为多路平衡查找树，B+树是B树的一种变体。B树和B+树中的B是Balance（平衡）的意思。目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B树&B+树两者有何异同

• B树的所有节点既存放键（key）也存放数据（data），而B+树只有叶子节点存放key和data，其他节点只存放key
• B树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点
• B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

为啥使用B+树而不使用B树

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索
• B+树的空间利用率更高，更稳定，范围查询性能更优

索引类型

索引类型分为两大类：主键索引、二级索引
细分的话，分为：

• 主键索引
• 唯一索引
• 普通索引
• 空间索引
• 全文索引

主键索引（Primary Key）

数据表的主键列使用的就是主键索引，一个数据表只能有一个主键，并且主键不能为null，不能重复。

在MySQL的InnoDB的表中，当没有显示指定表的主键时，InnoDB会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择改字段为默认的主键，否则InnoDB会自动创建一个6Byte的自增主键。

二级索引（辅助索引）

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

• 唯一索引（Unique Key）
• 普通索引（Index）
• 前缀索引（Prefix）
• 全文索引（Full Text）

唯一索引

唯一索引也是一种约束，唯一索引的属性列不能出现重复的数据，但是允许数据为null，一张表允许创建多个唯一索引。

普通索引

普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和null。

前缀索引

前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

全文索引

全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。

聚簇索引与非聚簇索引

聚簇索引（聚集索引）

聚簇索引即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB中的主键索引就属于聚簇索引。

聚簇索引的优缺点

优点

• 查询速度非常快：因为整个B+树本身就是一颗多叉平衡树，叶子节点也是有序的，定位到索引的节点，就相当于定位到了数据。相对于非聚簇索引，聚簇索引少了一次读取数据的IO操作
• 对排序查询和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快

缺点

• 依赖于有序的数据
• 更新代价大

非聚簇索引（非聚集索引）

非聚簇索引即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引（辅助索引）就属于非聚簇索引。MySQL的MyISAM引擎，不管主键还是非主键，使用的都是非聚簇索引
非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚簇索引的优缺点

优点

更新代价比聚簇索引要小，因为其叶子节点不存放数据

缺点

• 依赖于有序的数据： 跟聚簇索引一样，非聚簇索引也依赖于有序的数据
• 可能会二次查询（回表）：这个应该是非聚簇索引最大的缺点了，当查到索引对于的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询

覆盖索引和联合索引

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要回表，也就是通过主键再查找一次，
这样会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作。

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询
如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

联合索引

使用表中的多个字段创建索引，就是联合索引，也叫组合索引或复合索引

最左前缀匹配原则

指在使用联合索引时，MySQL会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。

索引失效的情况

• 使用select * 进行查询
• 创建了组合索引但查询条件未遵守最左匹配原则
• 在索引列上进行计算、函数、类型转换等操作
• 以%开头的like查询，比如 like ‘%abc%’
• 查询条件中使用or，并且or的前后条件有一个列没有索引，涉及的索引都不会被使用到
• 对于大数据量的查询，数据库可能选择放弃索引而进行全表扫描，以避免频繁的索引访问

什么是事务&事务基本特性是啥

事务指的是满足ACID特性的一组操作，可以通过Commit提交一个事务，也可以使用Rollback进行回滚

事务基本特性ACID？

• A（atomicity）原子性：事务是一个不可分割的整体，事务的所有操作要么全部成功，要么全部回滚；
• C（consistency）一致性：指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如：A转账给B，100块钱，假如中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改就不会被保存到数据库
• I（isolation）隔离性：指的是一个事务的修改在最终提交前，对其他事务是不可见的
• D（durability）持久性：指的是一旦事务提交，所做的修改就会永久保存到数据库中

脏读&&不可重复读&&幻读（虚读）

脏读

脏读：事务A读取到事务B未提交的数据，然后事务B回滚，那么事务A读取到的数据就是脏数据

比如我给你转100万，但我还没提交，这时你查询自己账户多了100万，然后我发现转错人了，回滚了事务，然后你100万没了，在这个过程中，你查询到我未提交的事务数据（100万）就是脏读

不可重复读

不可重复读：事务A进行多次读取操作，事务B在事务A多次读取的过程中，执行更新操作并提交，导致事务A读取到的数据先后不一致。

T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

幻读（虚读）

事务A首先根据条件得到N条数据，事务B增添了M条符合事务A搜索条件的数据，导致事务A再次搜素发现有N+M条数据，就产生了幻读。

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

不可重复读和幻读有什么区别

• 不可重复读的重点是内容修改或记录减少
• 幻读的重点在于新增

事务的隔离级别

• 读未提交（READ-UNCOMMITTED）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
• 读已提交（READ-COMMITTED）：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
• 可重复读（REPEATABLE-READ）：对同一字段的多次读取结果是一致的，除非数据被自身事务所修改，可以阻止脏读和不可重复读
• 可串行化（SERIALIZABLE）:最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

MySQL锁

表级锁和行级锁

• 表级锁：粒度大，针对非索引字段加的锁，资源消耗少，加锁快，不会出现死锁，但高并发下效率极低。
• 行级锁：粒度小，针对索引字段加的锁，只针对当前操作的行记录加锁，并发性高，但开销也大，会出现死锁。

MySQL的日志

• 错误日志（error log）：对MySQL的启动、运行、关闭过程进行记录。
• 二进制日志（binary log，binlog）：主要记录的是更改数据库数据的SQL语句。
• 一般查询日志（general query log）：已建立连接的客户端发送给MySQL服务器的所有SQL记录，因为SQL的量比较大，默认是不开启的，也不建议开启。
• 慢查询日志（slow query log）：执行时间超过 long_query_time秒钟的查询，解决SQL慢查询问题的时候会用到。
• 事务日志（redo log 和 undo log）：redo log是重做日志；undo log是回滚日志。
• 中继日志（relay log）：relay log是复制过程中产生的日志，很多方面都跟binary log差不多。不过，relay log针对的是主从复制的从库。
• DDL日志（metadata log）：DDL语句执行的元数据操作。

binlog（二进制日志）

binlog是什么

binlog（binary log即二进制日志文件）：主要记录了对MySQL数据库执行了更改的所有操作（数据库执行的所有DDL和DML语句），包括表结构变更（CREATE、ALTER、DROP TABLE…）、表数据修改（INSERT、UPDATE、DELETE…），但不包括SELECT、SHOW这类不会对数据库更改的操作。

即使表结构变更和表数据修改操作并未对数据库造成更改，依然会被记录进binlog。

binlog的格式有哪几种

• statement模式：每一条会修改数据的sql都会被记录在binlog中，如inserts、updates，deletes
• row模式：

binlog和redo log的区别

• binlog主要用于数据库还原，属于数据级别的数据恢复，主从复制是binlog最常用的一个应用场景。redo log主要用于保证事务的持久性，属于事务级别的数据恢复。
• redo log属于InnoDB引擎特有的，binlog属于所有存储引擎共有的，因为binlog是MySQL的Server层实现的。
• redo log属于物理日志，主要记录的事某个页的修改。binlog属于逻辑日志，主要记录的是数据库执行的所有DDL和DML语句。

MVCC

MVCC（Multi-Version Concurrency Control）是一种数据库事务并发控制机制，用于实现多个事务的并发执行而不产生冲突。MVCC通过维护数据的多个版本，为每个事务创建一个可见性视图，从而实现事务的隔离性和高并发性

三大范式

• 第一范式：每个列都不可以再拆分。
• 第二范式：非主键列完全依赖于主键。
• 第三范式：非主键列只依赖于主键，不依赖于其他非主键。

DDL和DML

• DDL是数据定义语言，有：create、drop、alter、truncate
• DML是数据操作语言，有：insert、update、delete、select等

SQL优化的经验

• 使用索引：在经常查询的列上创建索引
• 避免全表扫描
• 选择合适的数据类型：有助于减少磁盘和内存占用，提高性能
• 避免索引失效的情况
• 避免不必要的子查询：可以使用连接操作来替代。子查询可能会导致性能下降
• 适当使用UNION和UNION ALL
• 合理的分页查询
• 定期维护和优化
• 避免在循环中执行SQL
• 适当使用数据库连接池
• 充分利用缓存