mysql笔记

1 基本

1.1 引擎

关系性数据库，MySQL5.5以前的默认引擎是MyISAM，5.5版本之后是InnoDB。

区别	MyISAM	InnoDB
是否支持行级锁	只有表级锁	支持行级锁和表级锁，默认行级锁
是否支持事务	不支持事务	支持事务
是否支持外键	不支持	支持
是否支持数据库异常崩溃后的安全恢复	不支持	支持，恢复的过程依赖与redo log(重做日志)

1.2 MySQL日志

https://zhuanlan.zhihu.com/p/190886874

MySQL日志主要包括错误日记，查询日志，慢查询日志，事务日志，二进制日志。

• binlog(二进制日志)，主要用于记录数据库执行的写入行操作，以二进制的形式保存在磁盘中。binlog是MySQL的逻辑日志，使用任何存储引擎都会记录binlog日志。binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。binlog使用场景，主要使用场景有2个，分别是主从复制和数据恢复，使用mysqlbinlog工具进行数据恢复。

• redo log(事务日志)，包括两部分，一个是内存中的日志缓冲（redo log buffer），另一个是磁盘上的日志文件（redo log file）。mysql每执行一条DML，都先将记录写入redo log buffer，后续某个时间点再一次性写入redo log file。在计算机操作系统中，用户空间下的缓冲区数据一般无法直接写入磁盘的，中间必须经过操作系统内核空间（kernel space）缓冲区（OS Buffer），再通过系统调用fsync()将其刷新到redo log file中。可以通过配置innodb_flush_log_at_trx_commit的值设置写入的时机。

• undo log(事务日志)，数据库事务四大特性中有一个原子性，原子性底层就是通过undo log来实现的，undo log主要记录数据的逻辑变化，比如一条insert语句，对应一条delete的undo log，对于每个update语句，对于一条相反的update的undo log，这样发生错误时，就能回滚到事务之前的数据状态。

1.3 事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

https://snailclimb.gitee.io/javaguide/#/docs/database/%E4%BA%8B%E5%8A%A1%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB(%E5%9B%BE%E6%96%87%E8%AF%A6%E8%A7%A3)

2. 索引

https://blog.csdn.net/kimowinter/article/details/108145801

https://mp.weixin.qq.com/s?__biz=Mzg2NjE5NDQyOA==&mid=2247483790&idx=1&sn=bf573b66517bed97ac63c3869ee6cb8a&source=41#wechat_redirect

https://juejin.cn/post/6844903645125820424

2.1什么是索引？

索引的作用相当于目录的作用，可以通过目录快速定位到所要查找的内容。索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，使用索引可以不用扫描全表定位某行的数据，而是通过索引表找到该行数据对应的物理地址然后访问相应的数据。

优点：加快数据的检索速度。

缺点：创建索引和维护索引需要耗费很多时间，对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，降低SQL执行效率；占用物理存储空间。

2.2 索引类型

• 主键索引

  建表的时候指定了主键，就会创建主键索引，不允许为null。如果没有显式的指定表的主键，InnoDB会自动检查表中是否有唯一索引，如果有，则选择该字段为默认的主键，否则InnoDB会自动创建一个6Byte的自增主键。

• 二级索引（辅助索引）

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

  唯一索引，普通索引，前缀索引等属于二级索引。

  • 唯一索引，不能出现重复的值，允许为null，建立唯一索引的目的大部分时候是为了属性列数据的唯一性，而不是为了效率。
  • 普通索引，由关键字key或index定义的索引，唯一任务是加快对数据的访问速度。
  • 前缀索引，前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引。前缀索引使用https://blog.csdn.net/shanjairui/article/details/100015216
  • 全文索引，用于检索大文本数据中的关键字信息。https://blog.csdn.net/mrzhouxiaofei/article/details/79940958

2.3 聚集索引和非聚集索引

• 聚集索引

  聚集索引是指索引结构和数据一起存放的索引，主键索引属于聚集索引。

  对于InnoDB引擎，该表的索引的每个非叶子节点存储索引，叶子节点存储索引与索引对应的数据。

优点：聚集索引查询非常快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也是有序的，定位到索引的节点相当于定位到了数据。

缺点：但是聚集索引依赖于有序的数据，如果不是有序的，那么需要在插入的时候排序，如果数据不是整形，插入或查找的时候速度肯定慢；同时更新代价大，索引列的数据被修改，对应的索引也会被修改。

• 非聚集索引

  非聚集索引指索引结构和数据分开存放的索引，二级索引属于非聚集索引。

  非聚集索引的叶子节点不一定存放数据的指针，因为二级索引的叶子节点存储的数据是主键。

  优点：更新代价要比聚集索引小，因为非聚集索引的叶子节点是不存放数据的。

  缺点：非聚集索引也依赖于有序的数据；可能会二次查询，当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

2.4 覆盖索引

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

# 如id是主键索引，要查的数据就是id
select id from user where id = 1;

2.5 索引创建

1. 最左前缀原则，尽量把查询最频繁的那个字段作为最左(第一个)字段

   create index idx_name_pass on user(username,password)
   

2. 被频繁查询的字段

3. 被作为条件查询的字段

4. 被频繁用于连接的字段

3. 索引的数据结构

https://www.jianshu.com/p/79a14f59da13

https://www.jianshu.com/p/1775b4ff123a

https://www.jianshu.com/p/f3009ea37dec

3.1 MySQL的基本存储结构页

https://snailclimb.gitee.io/javaguide/#/docs/database/MySQL%20Index

各个数据页组成是一个双向链表，每个数据页是一个单向链表，所有我们一般查询（没有索引）的时候，会先遍历一个双向链表找到所在的页，然后在页中再遍历一个单项链表，时间复杂度是O(n)，在数据量非常大的时候很慢。

使用索引后，可以通过目录很快定位到对应的页上，索引结构是一颗B+树，查找速度是O(logn)。

3.2 哈希索引

哈希索引底层结构就是哈希表，在大多数需求为单条查询时，可以选择哈希索引，查询性能最快。

缺点：没办法利用哈希排序；不支持最左匹配原则；在有大量重复键值的情况下，哈希索引的效率也是极低的；不支持范围查询。

3.3 全文索引

用于检索大文本数据中的关键字信息。https://blog.csdn.net/mrzhouxiaofei/article/details/79940958

3.4 B-Tree索引

B-tree索引的数据结构是一个平衡多叉树，

3.5 B+Tree索引

B+Tree是B-Tree的的一个变种，MySQL普遍使用B+Tree实现其索引结构。区别是B+树只有达到叶子节点才命中。

4. 锁

https://juejin.cn/post/6844903645125820424

对于update、delete、insert语句，InnoDB会自动给相关的数据集加排他锁。

MyISAM在执行查询语句前，会自动给相关数据集加读锁，在更新数据时会自动加写锁。

表锁	行锁
开销小，加锁快；不会出现死锁；发生锁冲突的概率大，并发度最低	开销大，加锁慢；会出现死锁；发生锁冲突的概率小，并发度高

InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表级锁，InnoDB的行锁是基于索引的。

4.1 表锁

表写锁，表读锁，在表写锁和表读锁下，读读不阻塞，读写阻塞，写写阻塞。

读锁和写锁是互斥的，读写操作是串行的。在MySQL中写锁优先于读锁。

4.2 行锁

• 共享锁（S锁）（读锁）：允许一个事务读一行，阻止其他事务获得相同数据集的排他锁。多个客户可以同时读取同一个资源，但不允许其他客户修改。事务打算给数据行加共享锁，必须先取得该表的意向共享锁（IS锁）。
• 排他锁（X锁）（写锁）：允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享读锁和排他写锁。写锁会阻塞其他的写锁和读锁。事务打算给数据行加排他锁，必须先取得该表的意向排他锁（IX锁）。

4.3 InnoDB行锁的算法

https://zhuanlan.zhihu.com/p/66676020

• Record Lock：单个行记录上的范围；
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，包括记录本身。

例如一个索引有10,11,13,20这四个值。InnoDB可以根据需要使用Record Lock将10，11，13，20四个索引锁住，也可以使用Gap Lock将(-∞,10)，(10,11)，(11,13)，(13,20)，(20, +∞)五个范围区间锁住。Next-Key Locking类似于上述两种锁的结合，它可以锁住的区间有为(-∞,10]，(10,11]，(11,13]，(13,20]，(20, +∞)，可以看出它即锁定了一个范围，也会锁定记录本身。

1. 在不通过索引条件查询时，InnoDB会锁定表中的所有记录；

通过索引查询，锁的只是本身。

2. InnoDB通过索引来实现行锁，而不是通过锁住记录；

3. InnoDB的索引机制，数据库操作主键索引时，InnoDB会锁住主键索引；使用非主键索引时InnoDB会先锁住非主键索引，然后再锁定主键索引。

4. 当查询的索引是唯一索引时，InnoDB引擎会将Next-key Lock降级为Record Lock，只锁住本身；

5. InnoDB使用Next-key Lock来避免幻读。

   默认隔离级别REPEATABLE-READ下，InnoDB中行锁默认使用的算法是Next-key Lock，只有当查询的索引是唯一索引或主键时，InnoDB会对Next-ket Lock降级为Record Lock。当查询的索引为辅助索引时，InnoDB会使用Next-key Lock进行加锁。InnoDB对于辅助索引有特殊的处理，不仅会锁住辅助索引值所在的范围，还会将其下一键值加上Gap Lock。

4.4 乐观锁与悲观锁

https://zhuanlan.zhihu.com/p/31537871

悲观锁，在修改数据之前把数据锁住，然后再对数据读写，在释放锁之前任何人都不能对其操作，直到锁释放。

# 用于表的悲观锁
# 手动加悲观锁：读锁
LOCK tables test_db read
# 写锁
LOCK tables test_db WRITE
# 释放锁
UNLOCK TABLES;

乐观锁，操作数据时不会对操作加锁，只有提交数据的时候检验是否冲突，一般实现方式通过加版本号然后通过版本号对比。

5. 连接查询

内连接，inner join，查询结果集为左右表都存在的值。

左外连接，left join，查询结果集为左表的所有行，右表没有匹配行的话用null表示。

右外连接，right join，查询结果集为右表的所有行，左表没有匹配行的话用null表示。

完全外连接，full join，查询结果集为左右表的所有行，某行没有在另外一个表中匹配的话用null表示。