MySQL/面试题:数据库索引

MySQL/面试题:数据库索引

为什么要使用索引

当一张表数据量很小,不加索引,直接全表扫描可能会更快

什么样的信息能称为索引

索引的数据结构

哈希表这种结构适用于只有等值查询的场景 MySQL 有自适应的 hash 索引 有序数组索引只适用于静态存储引擎 大多数的数据库存储却并不使用二叉树,而是使用 B+ 树。其原因是,索引不止存在内存中,还要写到磁盘上。N叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了。数据库技术发展到今天,跳表、LSM树等数据结构也被用于引擎设计中,这里就不再一一展开了。 Hash 索引的缺点:

  • 不支持范围查询
  • 排序操作
  • 不能利用部分索引键查询,联合索引是使用整个索引组合来进行hash的
  • 不能避免表扫描,如果出现hash冲突,需要从bucket中取出实际的数据来坐对比,而B+树就不用。
  • 遇到大量Hash 值相等的情况性能不一定比B+ 树索引高 B+ 树的查询效率更加稳定,磁盘IO次数更少,范围查询和全表扫描友好 在大多数情况下,建议在表中定义一个自增主键,自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。 而在典型的KV场景中,只有一个索引,且该索引必须是唯一索引,则可以不另外定义一个自增主键,而是用业务字段作为主键,由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。

聚集索引和稀疏索引的区别

在InnoDB 中,聚族索引的选取规则如下 - 如果有定义主键,则该主键作为聚族索引 - 若没有定义主键,该表的第一个唯一非空索引作为聚族索引 - 如果不满足上述条件,InnoDB 会使用隐藏的 row_id 字段作为聚族索引 在 MyISAM 中,主键索引和非主键索引的叶子节点存储的都是指向数据地址的指针。在InnoDB 中,主键索引叶子节点存储的数据本身,而非主键索引叶子节点存储的是对应的,,通过非聚集索引查询数据可能需要多一次回表(只是可能,还要看是否是聚集索引等,会用到索引覆盖)。

联合索引最左比配原则

不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。 在建立联合索引的时候,如何安排索引内的字段顺序?第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

SQL 语句的执行顺序

数据库标的设计

除了范式之外,覆盖索引、前缀索引、索引下推

联合索引的技巧

1、覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取整行数据 2、最左前缀:联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符 3、联合索引:根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age= 1 and name=‘张三’可以使用索引,单以name=‘张三’ 不会使用索引,考虑到存储空间的问题,还请根据业务需求,将查找频繁的数据进行靠左创建索引。 4、索引下推:like 'hello%’and age >10 检索,MySQL5.6版本之前,会对匹配的数据进行回表查询。5.6版本后,会先过滤掉age<10的数据,再进行回表查询,减少回表率,提升检索速度

普通索引和change buffer的配合使用,对于数据量大的表的更新优化还是很明显的

redo log 主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的则是随机读磁盘的IO消耗。

如果查询索引+主键,可以利用索引覆盖避免回表

选择普通索引还是唯一索引? 对于查询过程来说: a、普通索引,查到满足条件的第一个记录后,继续查找下一个记录,直到第一个不满足条件的记录 b、唯一索引,由于索引唯一性,查到第一个满足条件的记录后,停止检索 但是,两者的性能差距微乎其微。因为InnoDB根据数据页来读写的。 对于更新过程来说: 概念:change buffer 当需要更新一个数据页,如果数据页在内存中就直接更新,如果不在内存中,在不影响数据一致性的前提下,InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中的与这个页有关的操作。

change buffer是可以持久化的数据。在内存中有拷贝,也会被写入到磁盘上

purge:将change buffer中的操作应用到原数据页上,得到最新结果的过程,称为 purge 访问这个数据页会触发purge,系统有后台线程定期purge,在数据库正常关闭的过程中,也会执行purge

唯一索引的更新不能使用change buffer,因为更新唯一索引时,需要把数据也读取到内存中判断是否有冲突,而更新普通索引时,直接把修改记录到 change buffer 中即可。而且写多读少适合使用 change buffer

change buffer用的是buffer pool里的内存,change buffer的大小,可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候,表示change buffer的大小最多只能占用buffer pool的50%。

将数据从磁盘读入内存涉及随机IO的访问,是数据库里面成本最高的操作之一。 change buffer 因为减少了随机磁盘访问,所以对更新性能的提升很明显。

change buffer使用场景 在一个数据页做purge之前,change buffer记录的变更越多,收益就越大。 对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在change buffer,但之后由于马上要访问这个数据页,会立即触发purge过程。 这样随机访问IO的次数不会减少,反而增加了change buffer的维护代价。所以,对于这种业务模式来说,change buffer反而起到了副作用。

索引的选择和实践: 尽可能使用普通索引。 redo log主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的则是随机读磁盘的IO消耗。

DDL过程如果是Online的,就一定是inplace的;

反过来未必,也就是说inplace的DDL,有可能不是Online的。截止到MySQL 8.0,添加全文索引(FULLTEXT index)和空间索引(SPATIAL index)就属于这种情况。

评论