Skip to content

Latest commit

 

History

History
167 lines (117 loc) · 11 KB

索引.md

File metadata and controls

167 lines (117 loc) · 11 KB
Raw

这一块，最好能知道怎么优化

面试官：索引介绍一下

索引类型

我：ok，先说一下索引类型：

  • FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
  • HASH：由于HASH的唯一及类似键值对的形式，很适合作为索引。 HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
  • BTREE：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
  • RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。 相对于BTREE，RTREE的优势在于范围查找。

索引种类

再说一下索引种类：

  • 普通索引：仅加速查询
  • 唯一索引：加速查询 + 列值唯一（可以有null）
  • 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
  • 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
  • 全文索引：对文本的内容进行分词，进行搜索
  • 索引合并：使用多个单列索引组合搜索
  • 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
  • 聚簇索引：表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)

索引结构

其次说索引结构：

MyISAM：

  1. MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，同样使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址
  2. 在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复
  3. MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录

InnoDB：

  1. InnoDB的数据文件本身就是索引文件，这棵树的叶节点data域保存了完整的数据记录（聚集索引）
  2. InnoDB的辅助索引data域存储相应记录主键的值而不是地址
  3. 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

补充一下为什么InnoDB索引是B+：

  • Hash索引：Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描
  • 二叉查找树：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表。
  • 平衡二叉树：通过旋转解决了平衡的问题，但是旋转操作效率太低。
  • 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了 AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多。
  • B+树：在B树的基础上，B+树相对于B树能够有更多的分支，使得这棵树更加矮胖，查询时做的IO操作次数也更少；此外将叶节点使用指针连接成链表，范围查询更加高效。B+树的非叶子节点不保存数据，只保存子树的临界值（最大或者最小），所以同样大小的节点。
  • B+树的高度

补充B树：

B树（英语: B-tree）是一种自平衡的树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。B树，概括来说是一个一般化的二叉查找树（binary search tree），可以拥有最多2个子节点。与自平衡二叉查找树不同，B树适用于读写相对大的数据块的存储系统，例如磁盘。

  • 关键字集合分布在整颗树中；
  • 任何一个关键字出现且只出现在一个结点中；
  • 搜索有可能在非叶子结点结束；
  • 其搜索性能等价于在关键字全集内做一次二分查找；

索引失效

查询什么时候不走索引：

  1. 模糊查询 %like
  2. 索引列参与计算,使用了函数
  3. 非最左前缀顺序
  4. where对null判断
  5. where不等于
  6. or操作有至少一个字段没有索引
  7. 需要回表的查询结果集过大（超过配置的范围）
  8. 将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描

索引最左

索引最左原则：

举例子： 如果索引列分别为A，B，C，顺序也是A，B，C：

  • 那么查询的时候，如果查询【A】【A，B】 【A，B，C】，那么可以通过索引查询
  • 如果查询的时候，采用【A，C】，那么C这个虽然是索引，但是由于中间缺失了B，因此C这个索引是用不到的，只能用到A索引
  • 如果查询的时候，采用【B】 【B，C】 【C】，由于没有用到第一列索引，不是最左前缀，那么后面的索引也是用不到了
  • 如果查询的时候，采用范围查询，并且是最左前缀，也就是第一列索引，那么可以用到索引，但是范围后面的列无法用到索引（比如，a>= 3 and b = 4 and c = 5; A走索引，bc不走）（比如，a = 3 and b >= 4 and c = 5; a和b走，c不走）

组合索引的底层其实按照第一个索引排序，从排序里面查第二个索引，以此类推。如果第一个索引失效，或者没有经过第一个索引，后面没发在前面的基础上查询。

为什么使用索引

  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 帮助服务器避免排序和临时表。
  • 将随机IO变为顺序IO。
  • 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

但是使用索引要看一条准则--- 那就是读写比例，我们知道索引的缺点：

  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
  • 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立簇索引，那么需要的空间就会更大。
  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

你想，如果某个场景，发送10条请求，9条写，1条读。 加索引岂不是在浪费效率和空间？

explain

面试官：聊聊explain

我：好的，不过这一块内容好多，我只说几个关键的吧

  1. id : 表示SQL执行的顺序的标识,SQL从大到小的执行
  2. select_type：表示查询中每个select子句的类型
  3. table：显示这一行的数据是关于哪张表的，有时不是真实的表名字
  4. type：表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
  5. possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
  6. Key：key列显示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。
  7. key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
  8. ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  9. rows： 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，理论上行数越少，查询性能越好
  10. Extra：该列包含MySQL解决查询的详细信息

慢查询优化

面试官：慢查询优化

我：我试试

打开慢查询日志

  1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
  2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
  3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
  4. order by limit 形式的sql语句让排序的表优先查（这里要注意如果数据量大，要注意了）
  5. 了解业务方使用场景
  6. 加索引时参照建索引的几大原则
  7. 观察结果，不符合预期继续从0分析

咱们知道，使用limit分页查询，offset越大，性能越差，比如：

-- 以真实的生产环境的6万条数据的一张表为例，比较一下优化前后的查询耗时：
-- 传统limit，文件扫描
select * from table order by id limit 50000,2;
受影响的行: 0
时间:  0.171s

-- 子查询方式，索引扫描
select * from table
where id >= (select id from table order by id limit 50000 , 1)
limit 2;
受影响的行: 0
时间: 0.035s

-- JOIN分页方式
select * from table as t1
join (select id from table order by id limit 50000, 1) as t2
where t1.id <= t2.id order by t1.id limit 2;
受影响的行: 0
时间: 0.036s

原因：因为 MySQL 并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。比如上面的(50000，2)，每次取2条，还要经过回表，发现不是想要的，舍弃。那肯定非常耗时间，而通过子查询通过id索引，只查询id，使用到了innodb的索引覆盖, 在内存缓冲区中进行检索,没有回表查询. 然后再用id >= 条件,进一步的缩小查询范围.这样就大大提高了效率。

而MyISAM，是直接索引是分离的，通过索引文件查到的数据记录地址，不需要回表，直接对应数据记录，效率也很高。