mysql 索引

2022-02-23 08:41:35

数据库索引

1. 索引是什么？

  • 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)。它包含着对数据表上所有的记录的引用指针
  • 索引是一种数据结构。数据库索引是数据库管理中一种排序的数据结构，它在数据库中的作用是用来协助查询以及更新数据库中的数据。索引的实现通常使用B树及其变种B+树

2. 索引有哪些优缺点？

  • 优点
    1. 建立索引提高了查询速度
    2. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
  • 缺点
    1. 时间方面：创建和维护索引也需要时间。 具体来说：在对数据表进行 增/删/改的时候也需要更新索引，增加了时间消耗
    2. 空间方面：索引也要占用物理空间

3. MySQL有哪几种索引类型？

  • 从存储结构上划分

    1. B-tree B+树
    2. Hash索引
    3. full-index 索引
    4. R-tree索引

  • 从应用层次来分

    1. 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
    2. 唯一索引：索引列的值必须唯一，但允许有空值
    3. 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

  • 划分

    1. 聚集索引：所有行数都会按照主键索引进行排序
    2. 非聚集索引：给普通字段加上索引
    3. 联合索引：由好几个字段组成的索引

4. 说一说索引的底层实现？

  • Hash索引： 使用的hash表
    mysql 索引

  • B-Tree索引（MySQL使用B+Tree）
    B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

  • B+树
    B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B+树的性质
1. B+树的非叶子结点不存储数据信息，存储的是索引。
2. B+树的叶子节点存储数据信息，及指向含这些关键字记录的指针
3. B+ 树中，数据对象的插入和删除仅在叶节点上进行。

5. 为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

  • 为什么不用B-tree

    1. B+树磁盘IO代价更小。因为B+ tree的内部节点不存储关键字的信息，而B树存储。所以在磁盘的一块存储节点的区域可存储B+树的内部节点更多，对应的关键字就更多，从而IO次数就会更少

    2. 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

  • 为什么不用hash

    1. Hash冲突
    2. hash适合等值查询，例如> < = in() 不适合范围查询
    3. 虽然可以快速定位，但是没有顺序，IO复杂度高；
    4. 因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序 ；

  • 为什么不用红黑树
    随着数据量的增大，因为红黑树也是一棵二叉树。树的高度增加，时间复杂度大

  • 为什么不用二叉排序树
    树结构不均匀，查询复杂度大

6. 讲一讲聚簇索引与非聚簇索引？

  1. 聚簇索引：B+树中叶子结点存储整行数据的叫做主键索引，也叫作聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。
  2. 非聚簇索引：索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

区别
1. 非聚簇索引的叶子节点存储的是主键的值而不是整个数据
2. 对于InnoDB， 查找数据需要通过主键。 所以通过非聚簇索引先获得 主键值， 然后通过主键查找出数据，称为回表
3. 通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可

note:MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

7. 非聚簇索引一定会回表查询吗？

  • 不一定。 比如覆盖索引，查询的列 正好是 后面根据索引的列 eg: 对name建立索引 select name from table where name = "djh"

覆盖索引
参考blog
https://www.cnblogs.com/happyflyingpig/p/7662881.html

8. 联合索引是什么？为什么需要注意联合索引中的顺序？

  1. MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

  2. 具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

9. 讲一讲MySQL的最左前缀原则?

  • 最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
  • eg:建立联合索引(name, age, idcard) 先根据name进行查找， name相同然后根据 age进行查找， 以此类推。
  • mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
	select * from table_name where name = "djh" and age > 12 and idcard = 123456;

eg:上面这个sql语句中 idcard就没有用到如果按照(name, age, idcard)建立索引。但是如果按照(name, idcard, age)就可以命中索引
  • =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器
    会帮你优化成索引可以识别的形式

10. 讲一讲前缀索引？

  1. 如果索引是很长的字符列，那么索引就会变得很大。存储索引的内节点就会变大，从而在一页上的内节点就会变少，最终就导致一页上面可以读出的关键字数少，可能引起磁盘I/O次数多。
  2. 前缀索引正是为了解决这个问题，只截取某列的前j个字符作为索引。
  3. 如何选择合适的前缀
    • 索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
    • 计算完整列的选择性，并使其前缀的选择性接近于完整列的选择性
      参考blog
      https://www.cnblogs.com/balfish/p/9003794.html 
select count(distinct city) / count(*) from city_demo;
  1. 前缀索引的缺点
    mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

11. 了解索引下推吗？

  • 索引下推（index condition pushdown ）简称ICP。在Mysql5.6的版本上推出，用于优化查询。
  1. 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
  2. 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
  3. 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
select * from table_name where name = "陈%" and age = 20;
  • 没使用ICP,就会忽略掉age = 20。根据name查出主键，进一步回表查询
    ！avatar
  • 使用ICP,不会忽略掉age，回表次数就会减少
    mysql 索引

参考blog
https://zhuanlan.zhihu.com/p/121084592

12. 怎么查看MySQL语句有没有用到索引？

通过explain，如以下例子：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no=‘10001‘ AND title=‘Senior Engineer‘ AND from_date=‘1986-06-26‘;

13. 为什么官方建议使用自增长主键作为索引？

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

插入连续数据
mysql 索引

插入非连续数据
mysql 索引

14. 如何创建索引？

  1. 创建表的时候
CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);
  1. ALTER TABLE
ALTER TABLE table_name ADD INDEX index_name (column_list);
  1. 使用CREATE INDEX
CREATE INDEX index_name ON table_name (column_list);

15. 创建索引时需要注意什么？

  1. 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
  2. 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
  3. 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

16. 建索引的原则有哪些？

  1. 最左前缀原则
  2. in和=可以乱序。eg a = 10 and b = 20 and c = 100 可以交换顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
  3. 选择离散度的列作为索引
  4. 索引列不能参与计算
  5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

17. 使用索引查询一定能提高查询的性能吗？

通常通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

基于一个范围的检索，一般查询返回结果集小于表中记录数的30%。
基于非唯一性索引的检索。

18. 什么情况下不走索引（索引失效）？

  1. 使用!= 或者 <> 导致索引失效
  2. 类型不一致导致的索引失效
  3. 使用函数导致索引失效
SELECT * FROM `user` WHERE DATE(create_time) = ‘2020-09-03‘;
  1. 运算符导致的索引失效
SELECT * FROM `user` WHERE age - 1 = 20;
  1. OR引起的索引失效
SELECT * FROM `user` WHERE `name` = ‘张三‘ OR height = ‘175‘;

如果 or前后连接的是一个索引字段那么索引不会失效

  1. 模糊搜索导致的索引失效
SELECT * FROM `user` WHERE `name` LIKE ‘%冰‘;

% 放在匹配字段前索引就会失效

  1. NOT IN、NOT EXISTS导致索引失效

参考文章
https://www.nowcoder.com/discuss/639644?channel=-1&source_id=profile_follow_post_nctrack
https://www.cnblogs.com/bypp/p/7755307.html （对于B+树的索引查询）

mysql 索引

上一篇：MySQL高级之死锁分析


下一篇：MySQL数据库中文乱码