跳至主要內容

MySQL

Vingkin...大约 24 分钟

说说对MySQL索引的理解

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有的索引类型。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关。MyISAM和InnoDB的存储引擎只支持BTREE索引，MEMORY存储引擎支持HASH和BTREE索引。

优点：

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

缺点：

  1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
  2. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸
  3. 当对表中的数据进行增删改操作的时候，索引也要进行动态维护，这样降低了数据的维护速度

为了减少IO，索引树会一次性加载吗

  1. 数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，超过几个G
  2. 当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的就是逐一加载每个磁盘页（数据页），因为数据页对应着索引树的节点

B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO（为什么索引使用B+树）

InnoDB存储引擎中页的大小为16 KB，一般表的逐渐类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4-8个字节，也就是说一个页（B+tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值，换算出一页大概能存储1000条数据，深度为3可以存储1000*1000*1000=10亿条数据。。。

为什么B+树比B树更适合做索引

  1. B+树的磁盘读写代价更低

    B+树的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对B树更小。如果把所有同一内部节点的关键字存放在同一块盘中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

  2. B+树的查询效率更加稳定

    由于非叶节点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引。所以任何关键字的查找必须走一条从根节点到叶子节点的路。所有关键字查询的路径长度相同，导致每一数据的查询效率相当。而B-树非叶子节点也存储数据，可能需要中序遍历。

  3. 在范围查询上，B+树的效率也比B树高

    B+树的关键字都出现在叶子节点中，叶子节点之间会有指针，数据又是递增的，这使得范围查找可以通过指针连接查找。而在B树中则需要通过中序遍历才能完成范围查询。

InnoDB为什么不建议用过长的字段作为主键

所有二级索引都引用主键索引，过长的主键索引会令二级索引变得过大

InnoDB为什么使用自增主键是一个很好的选择

InnoDB数据文件本身是一颗B+树，非单调的主键会造成在插入新纪录时，数据文件为了维持B+树的特性而频繁的分裂调整（页分裂）

Hash结构效率高，那为什么还要使用B+树索引呢

  1. Hash索引仅能满足 = , <> , IN 查询。如果进行范围查询，哈希索引的时间复杂度会退化为O(n)。而树型的依然能够保持O(log2N)
  2. Hash索引数据存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序。
  3. 对于联合索引的情况，Hash值是将联合索引键合并起来一起计算的，无法对单独的一个键或者几个索引键进行判断。
  4. 对于等值查询来说，通常Hash索引的效率更高，不过当索引列的重复值过多，由于Hash冲突效率就会降低。

索引的分类

  1. 从功能逻辑上说，索引主要有4种，分别是普通索引、唯一索引、主键索引和全文索引
  2. 从物理实现方式：聚簇索引和非聚簇索引
  3. 作用字段个数：单列索引和联合索引

哪些情况适合创建索引

  1. 字段的数值有唯一性的限制

    业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
    说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的。

  2. 频繁作为 WHERE 查询条件的字段

    某个字段在 SELECT 语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。
    尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

  3. 经常 GROUP BY 和 ORDER BY 的列

    索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。
    如果待排序的列有多个，那么可以在这些列上建立 组合索引 。

  4. UPDATE、DELETE 的 WHERE 条件列

    对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。
    如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

  5. DISTINCT 字段需要创建索引

    有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

    索引列按递增的顺序 进行排序。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

  6. 多表 JOIN 连接操作时，创建索引注意事项

    • 首先， 连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
    • 其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
    • 最后， 对用于连接的字段创建索引，并且该字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

  7. 使用列数据类型小的字段创建索引

  8. 使用字符串前缀创建索引

    • 【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
    • 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。

  9. 区分度（散列性）高的适合作为索引

    数据相似性大的就不适合建立索引，如：男女性别

  10. 使用最频繁的字段放到联合索引左侧

    这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

  11. 在多个字段需要创建索引的情况下，联合索引优于单值

哪些情况不适合创建索引

  1. 在where中使用不到的字段

  2. 数据量小的表

  3. 有大量重复数据的列

  4. 避免经常更新的表创建过多的索引

  5. 不建议使用无序的值作为索引

    例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

  6. 删除不再使用或很少使用的索引

  7. 不要定义冗余或重复的索引。

索引下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。

示例：

alert table tb_people add index `zip_last_first` (`zipcode`, `lastname`, `firstname`)

select * from people
where zipcode = '000001'
and lastname like '%张%'
and address like '%北京市%'

上述添加了一个联合索引。select语句只有zipcode命中了联合索引，lastname因为%开头并不能命中，但是因为联合索引中有lastname，所以会先在联合索引中通过lastname进行过滤，然后再进行回表操作。这就是索引下推。

EXISTS和IN的区分

如何选择需要看表的大小。小表驱动大表。

比如

select * from A where cc in (select cc from B)
select * from A where exists (select cc from B where B.cc = A.cc)

当A小于B时，用EXISTS。因为EXISTS的实现，相当于外表循环，

for i in A
    for j in B
        if j.cc == i.cc then...

IN的逻辑

for i in B
    for j in A
        if j.cc == i.cc then...

COUNT(*), COUNT(1)和COUNT(具体字段)

  1. COUNT(*)和COUNT(1)都是对结果进行COUNT，COUNT(*)和COUNT(1)本质上并没有区别。
  2. 如果采用的是MyISAM存储引擎，统计数据表的行数只需要O(1)的复杂度，这是因为每张MyISAM的数据表都有一个meta信息存储了row_count的值，而一致性则由表级锁来保证。如果是InnoDB，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，维护一个row_count变量，因此需要采用扫描全表，O(n)的复杂度。
  3. 在InnoDB中如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引。对于COUNT(*)和COUNT(1)来说，他们不需要查找具体的行，只是统计行数，系统会自动采用占用空间小的二级索引来进行统计。如果有多个二级索引，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

关于SELECT(*)

在表查询中，建议明确字段，不要使用 *作为查询的字段列表，推荐受用具体字段查询。

原因：

  1. mysql在解析的过程中，会通过查询数据字典将 * 按序转换成所有列名，这会大大消耗资源和时间。
  2. 无法使用覆盖索引

多使用COMMIT

只要有可能，在程序中尽量多使用COMMIT，这样程序的性能能够得到提高，需求也会因为COMMIT所释放的资源而减少。

COMMIT所释放的资源：

  1. 回滚段上用于恢复数据的信息
  2. 被程序语句获得的锁
  3. redo / undo log buffer中的空间
  4. 管理上述3种资源种的内部花费

主键如何设计

自增ID的问题

  1. 可靠性不高：存在自增ID回溯的问题，这个问题直到8.0才解决
  2. 安全性不高：对外暴露的接口容易暴露信息。比如/User/1
  3. 性能差：自增ID的性能较差，需要在数据库服务器端生成
  4. 交互多：业务需要二外执行一次类似last_insert_id()的函数才能知道插入的子增值。
  5. 局部唯一性：分布式问题

推荐的主键设计

对于核心业务，主键的设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。推荐使用UUID。

UUID的特点：

全局唯一，占用36字节，数据无序，插入性能差。

UUID如下图所示：

改造UUID

若将时间高低位互换，则时间就是单调递增的了。MySQL8.0可以更换时间低位和时间高位的存储方式，这样UUID就是有序的UUID了。

谈谈你对MVCC的了解

MVCC只存在读已提交和可重复读的情况下，两种情况下生成ReadView的机制不一样。

在读已提交中，每次读操作都会生成一个ReadView所以会出现不可重复读的情况。对于可重复读，在一次事务中，只有第一次读操作会生成ReadView，所以不会出现可重复读，也不会出现幻读。

MySQL事务隔离级别和MVCC - 掘金 (juejin.cn)open in new window

InnoDB的默认隔离级别是REPEATABLE READ，RR解决了脏读、不可重复读和幻读的问题。注意不是到了串行化才解决了幻读， RR通过MVCC快照读的思想就已经解决了幻读的问题。（这点有待商榷） MVCC的意思是多版本并发控制。它最大的优点就是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要基于以下奇数以及数据结构：

  1. 隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id，指向undo log的指针等。
  2. 基于undo log的版本链：每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。
  3. ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本，则需要根据ReadView来确定。所谓ReadView，是指事务（记作事务A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务id与trx_sys快照比较，从而判断数据对该ReadView是否可见，即对事务A是否可见。

WHERE和HAVING的区别

WHERE是一个约束声明，使用WHERE约束来自数据库的数据，WHERE是在结果返回之前起作用的，WHERE中不能使用聚合函数

HAVING是一个过滤声明，是在查询结果返回集以后对查询结果进行过滤的操作，在HAVING中可以使用聚合函数。另一方面，HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段。

从性能上来说，HAVING子句中如果使用了分组字段作为过滤条件，应该替换成WHERE子句。因为WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好。

数据库索引失效了怎么办

可以采用一下几种方式，来避免索引失效

  1. 使用联合索引时，要遵循“最左前缀”原则
  2. 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描
  3. 尽量使用覆盖索引，减少select *使用能减少回表的次数
  4. MySQL在使用不等于的时候无法使用索引会导致全表扫描
  5. LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描
  6. 字符串不加单引号会导致索引失效（发生了索引列的隐式转换）
  7. 少用or，用or会导致索引失效

事务的四大特性以及如何实现

  1. 原子性：原子性指整个数据库事务是不可分割的工作单位。只有事务中的数据库操作都执行成功，整个事务才算执行成功。事务中任何一个SQL语句执行失败，那么之前已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。
  2. 一致性：一致性是指事务将数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
  3. 隔离性：事务与事务之间的操作时互相隔离互不干扰的。
  4. 持久性：事务一旦提交，其结果就是永久的，即使发生宕机等故障，数据库也能将数据恢复。

原子性实现原理

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的SQL语句。InnoDB实现回滚靠的是undo log，当事务对数据库进行修改时，InnoDB会生成对应的undo log。如果事务执行失败或者调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

undo log属于逻辑日志，它记录的是SQL执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作。对于insert，回滚时会执行delete。对于delete，回滚时会执行insert。对于update，回滚时则会执行相反的update，把数据改回去。

持久性实现原理

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存（Buffer Pool），Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。当从数据库读取数据时，首先会从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool。当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中。

Buffer Pool的使用大大提高了读写数据的效率，但是也带来了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题。当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作。当事务提交时，对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证数据不在因MySQL宕机而丢失，从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘要快呢?

  • 将Buffer Pool中的数据写入磁盘属于随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO
  • 将Buffer Pool中的数据写入磁盘是以数据页为单位进行的，MySQL默认页为16KB，一个页上一个小修改都要整页写入。而redo log中只包含真正需要写入的部分，无效IO大大减少。

隔离性实现原理

隔离性追求的是并发情形下事务之间互不干扰。

第一方面，两个事务同时写：锁机制保证隔离性

隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁。获得锁之后，事务便可以修改数据。该事务执行期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

按照粒度，锁可以分为表锁、行锁以及其他位于二至之间的锁（页锁）。表锁在操作数据时会锁定整张表，并发性能较差。行锁则之锁定需要操作的数据，并发性能好。但是由于加锁本身也需要消耗资源，因此在锁定数据较多的情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且处于性能考虑，绝大多数情况下使用的是行锁。

第二方面，一个事务读，一个事务写时，MVCC保证隔离性

[0x10. 谈谈你对MVCC的了解](#0x10. 谈谈你对MVCC的了解)

一致性实现原理

一致性是事务追求的最终目标。前面提到的原子性、持久性和隔离性都是为了保证数据库状态的一致性。此外，除数据库层面的保障，一致性的实现也需要在应用层面进行保障。

MySQL的悲观锁和乐观锁

悲观锁

悲观锁是一种思想，对数据被其他事务修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排他性。

乐观锁

乐观锁也是一种思想，对数据被其他事务修改持乐观态度，属于小概率事件，不采用数据库本身的锁机制，而是通过程序来实现（CAS）。在程序上，我们可以使用版本号机制或时间戳机制来实现

  • 乐观锁的版本号机制

    在表中设计一个版本号字段version，第一次读的时候会获取version的取值。然后对数据进行更新或删除操作时会对当前字段的version进行加一操作。如果此时已经有事务对这条数据进行了更改，修改就不会成功。

  • 乐观锁的时间戳机制

    时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。

根据锁的类型分类

需要注意的是对于InnoDB引擎来说，共享锁和排他锁既可以加在表上，也可以加在行上

  1. 共享锁（S Lock）：针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，互相不阻塞
  2. 排他锁（X Lock）：当前写操作没有完成前，他会阻断其他共享锁和排他锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源
XS
X不兼容不兼容
S不兼容兼容

根据锁的粒度进行分类

可以分为表锁，页锁和行锁

表锁

意向锁

如果我们给某一行数据加上了排他锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人给它上过排他锁了。

不这么做的话，想上锁的那个事务需要遍历有没有行锁。

  • 意向共享锁：事务有意向对表中的某些行加共享锁，就会在更大一级的空间加上意向共享锁。
  • 意向排他锁：事务有意向对表中的某些行加排他锁，就会在更大一级的空间加上意向排他锁。
意向共享锁（IS）意向排他锁（IX）
意向共享锁（IS）兼容兼容
意向排他锁（IX）兼容兼容
意向共享锁（IS）意向排他锁（IX）
共享锁（S）兼容不兼容
排他锁（X）不兼容不兼容

自增锁

AUTO-INC锁是当想使用含有AUTO-INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。

元数据锁

当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。不需要显示使用，在访问一个表的时候会被自动加上。

行锁

行锁又称为记录锁，顾名思义就是锁住某一行。需要注意的是，MySQL服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。

记录锁（Record Locks）

可以理解为行级别的共享锁和排他锁

间隙锁（Gap Locks）

锁定一个范围，不包含记录本身（开区间），为了防止插入幻影记录而提出的

临键锁（Next-Key Locks）

Record Locks + Gap Locks

锁定一个范围包含记录本身（闭区间）

InnoDB中行级锁是怎么实现的

InnoDB行级锁是通过给索引上的索引项加锁来实现的。

数据库死锁问题以及解决办法

死锁是指两个或两个以上的事务在执行过程中，因争夺所资源而造成的一种相互等待的现象。若无外力作用，事务都无法推进下去。

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续运行。

除了超时机制，当前数据库还普遍采用wait-for graph（等待图）的方式来进行死锁检测。较之被动的超时方案，这时一种主动的死锁检测方法。InnoDB引擎也采用这种方法。wait-for graph要求数据库保存一下两种信息：

  • 锁的信息链表
  • 事务等待链表

通过上述链表可以构造出一张图，若这个途中存在回路，就代表存在死锁。

0  字
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v2.15.8
Copyright © 2023 Vingkin