MYSQL基础

Created in March 01, 2021

2021   ·   Backend MYSQL

索引

MySQL底层是B+树，放入数据的时候，会进行一个排序，排序后会以指针把数据连接成链表的形式，同时mysql进一步优化，利用B+树进行存储，对数据进行一页一页存储，一个页的大小为16kb，按照B+树的数据结构来讲，一个三层的B+树可以存储10亿左右，如果我们基于主键器查询，最多只需要进行一次磁盘IO

原理

把无序的数据变成有序的查询

InnoDB索引分类

  • 主键索引 设定为主键会自动奖励索引，为聚簇索引，主键索引索引列值不能为空

  • 单值索引 即一个索引只包含单个列

  • 唯一索引 索引列的值必须唯一，允许有空值

  • 复合索引 一个索引包含多个列
    • 需要符合最左前缀原则匹配

    最左优先，在检索数据时从联合索引最左边开始匹配。mysql查询为了更好使用索引，会自动调整查询字段顺序，前提是需要包含全部复合索引值

B+树

是B树的变形，基于B树和叶子节点顺序访问指针进行实现

包含2种类型节点：

  • 内部节点(只存储索引)
  • 叶子节点(存储数据)

每个叶子节点都错又相邻子节点的指针，内部节点的key按照从小到大排序

优点

  1. 由于内部节点只存放key，因此，一次读取，可以在内存页种获取更多的键，有利于更快缩小查找范围
  2. 叶子节点由一条链相连，当需要一次遍历全部数据的时候，只需要通过O(logn)找到最小节点，然后通过O(n)依次遍历即可

B树与B+树比较

  1. B树，key和value都可以放在内部节点和叶子节点；B+树，内部节点都是key，叶子节点存放key和value(B树每个节点都有指针，即增加了磁盘IO次数)
  2. B+叶子节点有一条链项链，遍历效率更高

为什么索引用B+树，不用B树或者红黑树

  • 不用B树
    1. 数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操做
    2. B+树叶子节点构成链表，更利用范围查找和排序。而B树进行范围查找和排序则要对树进行递归遍历

  • 不用红黑树 树的查询时间跟树的高度有关，B+树是一棵多路搜索树可以降低树的高度，提高查找效率

  • 不用哈希表 哈希表对于范围查找和排序效率低，但对于单个数据的查询效率很高。

hash索引

存储引擎对所有的索隐列计算出一个哈希码，将哈希码存储在索引中，同时哈希表中保存每个数据行的指针。这样，对于此种索引查找速度是非常快的。出现哈希值碰撞的话，索引会以链表的形式存放多个记录指针到同一个哈希条目中。采用哈希算法，把键值换算成新的哈希值，查找只需要一次哈希算法即可以定位到相应的位置

优点

如果查询单条记录，哈希索引查询性能最快

缺点

无法利用索引完成范围查询，且无序，大量重复键值的情况下，哈希索引的效率比较低，因为存在哈希碰撞

MySQL索引

B+树索引(大部分mysql存储引擎的默认索引类型)

  • 不用全表扫描，只需要对树进行搜索，所以查找速度比较快
  • 有序性，所以可以分组和排序
  • 聚簇索引，数据存储和索引放在一起，索引结构的叶子节点保存了行数据

    主键索引一定是聚簇索引；聚簇索引不一定是主键索引。会把已加载的数据加载到磁盘中。主键建议不要使用uuid，不适合拍下，导致索引树的复杂度变大

  • 非聚簇索引，将数据和索引分开存储，索引结构的叶子节点指向数据对应的位置

    辅助索引叶子结点存的是key值，访问数据总是需要二次查找，即回表查找；先找到主键id值，再去聚簇索引中找到对应的整行数据

hash索引

数据结构与哈希表一样，通过hash算法将数据库字段数据转成定长的hash值

  • 快速精准查询，不支持范围查询
  • 适合场景：等值查询，比如redis等中间件

索引优化

  • 独立的列 不能是表达式的一部分，也不能是函数的参数

  • 多列索引 在需要使用多个列作为查询条件的时候，使用多列索隐比使用多个单列索引性能更好

  • 索引列的顺序
    1. 将选择行最强的索引放在最前面
    2. 索引的选择性：不重复的索引值和记录总数的比值
  • 前缀索引
    1. 对于varchar等列，必须使用前缀索引
  • 覆盖索引
    1. 索引包含所有需要查询的字段

索引失效

  1. like关键字，匹配字符串第一个字符为’%*‘,索引不会被使用
  2. 符合索引，只有查询到复合索引中的一个字段(最左原则)，才会使用索引
  3. 查询语句使用or关键字，前后都是索引；如果其中一个不是索引，不使用索引
  4. 反向判断也无法使用索引，使用不等于
  5. 索引进行函数操作，计算等

索引 explain参数(查看sql语句的执行计划)

  1. id 是一组数字，代表多个表之间的查询顺序
    • id相同，顺序由上至下
    • id不同，如果是子查询，id越大优先级越高，越先查询
  2. select_type
    • simple：不包含子查询或者union
    • primary：如果有自查询，最外层会被标记为primary
    • subquery：在select或者where包含自查询，子查询会被标记为subquery
    • derived：from中包含的子查询会被标记为衍生查询，会把查询结果放到一个临时表中
    • union：2个select之间如果用union链接，第二个select会被标记union，结果会被标记为union result
    • union result

  3. table 表名称

  4. type(代表使用的索引类型)【重要】 顺序从好到差依次排序
  • system
  • const：使用唯一索引或者主键
  • eq_ref：命中主键或者非空唯一索引
  • ref：普通非唯一索引
  • range：索引上的范围查询
  • index：需要扫描索引的全部数据
  • all：全表扫描

  1. possible_keys 此次查询中设计字段上如果存在索引，则会被列出来。表示可能会用到的索引，但并不是实际上一定会用到的索引

  2. key 此次查询种实际用到的索引

  3. key_len 索引种使用的字节数

事务

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

事务四大特性(ACID)

  • 原子性(Atomocity)：确保动作要么全部完成，要么完全不起作用
  • 一致性(Consistency)：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的
  • 隔离性(Isolation)：并发访问数据库，一个用户的事务不会被其他事务所干扰，各并发事务之间的数据库是独立的
  • 持久性(Durability)：一个事务被提交之后。它对数据库中的数据改变是持久的，即使数据库发生故障也不应该对其有任何影响

并发事务带来的问题

  • 脏读：读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，未提交前，事务A读取了X，但是B却回滚了，导致事务A读取了脏数据形成了脏读。

    当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据

  • 丢失修改：一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据，导致第一个事务修改的结果丢失。例如：事务A读取某表的数据X=20，事务B也读取X=20，事务A修改X=X-1，事务B也修改X=X-1，最终结果X=19，事务A的修改丢失。

  • 不可重复读：事务A读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变，然后事务A再次读取发现数据不匹配。

    前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配

  • 幻读：事务A首先搜索到N条数据，然后事务B改变了这N条数据新增或者修改了M条数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读

    当前事务第一次读取到的数据比后来读取的数据量少或者多

不可重复读与幻读区别：前者针对的是update，后者针对的是insert与delete

事务隔离级别有哪些？MySQL默认隔离级别？

SQL定义了四个隔离级别

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

MySQL InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ(可重复读)。可以完全保证事务的隔离性要求。InnoDB存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

衍生问题：

MVCC只在COMMITTED READ(读提交)和 REPEATABLE READ(可重复读)两种隔离级别

TCC、2PC、3PC

  • 2PC(两段式提交)
    1. 提交事务请求：执行完后不会commit，向事务协调者报告是否可以处理本次事务
    2. 执行事务提交：事务协调者收到所有参与者反馈后，则通知参与者执行commit，否则回滚
  • 3PC(三段式提交)
    1. CanCommit：询问是否可以提交事务
    2. PreCommit：预提交
    3. doCommit：正式提交

3PC比2PC做了哪些改进

  1. 引入超时机制。同时在每个阶段协调者和参与者中都引入超时机制。
  2. 在第一阶段和第二阶段中插入一个准备阶段。保证了在最后提交阶段之前各参与节点的状态是一致的。
  • TCC(Try-Confirm-Cancel)
    1. try：是在业务逻辑阶段把数据操作更新到中间表并记录操作痕迹。
    2. confirm:是把所有中间步骤更新到原表操作。
    3. cancel：是回滚。

innodb如何实现事务（以update为例）

  1. 在收到一个update语句的时候，会根据条件找到数据库所在的页，并将该页面缓存在buffer pool中
  2. 执行update语句，修改buffer pool中的数据，也就是内存的数据
  3. 针对update语句生成一个redolog对象，存入到logbuffer中
  4. 针对update语句生成undolog日志，用于事务回滚
  5. 如果事务提交，则把redolog对象持久化，后续如果则将修改的数据页也持久化到磁盘中
  6. 如果事务回滚，利用undolog进行日志回滚

事务的实现就是如何实现ACID特性
事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性是通过事务日志实现

事务日志包括重做日志redo与回滚日志undo

  • redo实现持久化和原子性，恢复提交事物修改的页操作，记录页的物理修改操作
  • undo实现一致性，回滚记录到特定版本

锁机制和InnoDB锁算法

MyISAM和InnoDB存储引擎使用的锁：

  • MyISAM采用表级锁(table-level locking)。
  • InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

  • 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
  • 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁

锁的分类

  • 共享锁：被称为读锁，当用户进行数据读取的时候，对数据加上共享锁。让多个线程同时获取一个锁。
  • 排他锁：被称为写锁，当用户进行数据写入时候，对数据加上排他锁。即某一时刻只能被一个线程占有，其他线程必须等待锁被释放才能获取到锁

锁的状态

  • 意向共享锁：当一个事务试图对整个表进行加共享锁之前，首先需要这个表的意向共享锁
  • 意向排他锁：当一个事务试图对整个表进行加排他锁之前，首先需要这个表的意向排他锁

分库分表之后，ID主键如何处理？

  • UUID ：不适合作为主键，因为太长了，并且无序不可读，查询效率低。
  • 数据库自增ID：两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的id有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈
  • 利用redis生成id： 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
  • 雪花算法

数据库SQL慢查询的原因

  1. 大多数情况下很正常，偶尔很慢，则有如下原因
    • 数据库在刷新脏页，例如redo log写满了需要同步到磁盘
    • 执行的时候，遇到锁，如表锁、行锁。
  2. SQL语句一直执行的很慢
    • 没有用上索引：如字段没有索引；由于对字段进行运算、函数操作导致无法用到索引
    • 数据库选错了索引

查询性能优化

数据库优化经验

  1. 尽量避免使用外键（影响增删改的性能）
  2. sql尽量全部大写，以为数据库会先把sql编译成大写再执行
  3. 可以不需要创建大量索引，索引会加快速度，但是也会消耗磁盘空间

优化查询语句

  1. 尽量避免全表扫描，先考虑where或者order by涉及的列上创建索引
  2. 避免使用select * ，确定列查询
  3. 返回必要的行，并使用limit限制返回的行数
  4. 重复查询的数据可以考虑使用redis
  5. 避免使用is null，可以用is not null，会导致放弃索引走全表扫描
  6. in 与not in 尽量少用

调优

  1. 排除缓存干扰
  2. 执行explain计划
  3. 建立索引尽量选择where/orderby join等条件后的字段作为索引列，这些索引列也要排个序，符合最左匹配原则
  4. 这种多列的索引列需要建立联合索引而不是单个索引
  5. sql写的时候不要将索引列放到表达式中，比如反向判断notnull，not in这种关键词会使得索引失效
  6. 如果设计索引没有生效，可以考虑下是不是mysql其他原因影响，可以通过force index强制走索引
  7. 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表

MySQL常见问题



Enjoy Reading This Article?

Here are some more articles you might like to read next:

  • 2379. Minimum Recolors to Get K Consecutive Black Blocks
  • 2471. Minimum Number of Operations to Sort a Binary Tree by Level
  • 1387. Sort Integers by The Power Value
  • 2090. K Radius Subarray Averages
  • 2545. Sort the Students by Their Kth Score