mysql 索引部分

- 日理万妓 2022-08-31 07:21 190阅读 0赞

索引的概念：数据的唯一标识，书的目录

考虑由你来完成索引，比如我们按主键/id（唯一且非空）的字典序来排序

select \* from emp where name="xxx"

这种查询无法使用索引，因为答案离散

这个时候通常的解决方式为对name也建立一个索引

如果是多条件呢，select \* from emp where name="xxx" and city="xxx",使用联合索引即可

**联合索引**（适用于多对多） 我们把索引关键词设置为（name,city）假设还是字典序排序，name为第一关键词，city为第二关键词对于该索引

select \* from emp where name="xxx" ok

select \* from emp where name="xxx" and city ="xxx" ok

select \* from emp where city="xxx" not ok 无法命中索引

考虑你排好序的索引，答案也是离散的

select \* from emp where city="xxx" and name ="xxx" ok

这样也是可以的，sql会自动吧优先度高的条件放在前面

这就是最左前缀匹配

考虑自己写sql语句建立索引时，a,b,c b,a,c效果是不同的

我们一般基于去重后剩余数量最多的放在最前面这一原则来放置顺序

什么时候使用联合索引呢？

比如选课记录表，每个学生有很多老师，每个老师同样有很多学生，如果现在要查询某个学生是否选了某个老师的课，只根据一个条件的话会返回很多值，需要在内存中判断，如果是联合索引的话可以一次就查到

索引好处：

加快查询速度

坏处：

修改节点时，很可能破坏索引结构，这时索引会重新排序，进行维护

**聚集索引**：索引顺序对应物理地址顺序

数据和索引在一起

好处：查到了就找到数据了

为什么建议用自增主键做索引呢？

主键自增的话，索引写满了一页，自动写下一页，如果不是的话就得在前面的数据中插入，会造成移动数据，增加磁盘io，频繁的移动，分页也会造成大量的内存碎片

**非聚集索引**：不对应，查到的是聚集索引的id，然后要去聚集索引再查一次

回表现象就是非聚集索引的特点

select \* from emp where deptno=“xxx” 这种不行

select id,deptno from emp where deptno="xxx" 这样就能在聚集索引里面查询数据了就只用查一次，该方法被称为索引覆盖 ，所以尽量不要使用select \*的方式

索引下推：select \* from userinfo where name like "ming%" and age=20;

之前的做法是，先靠非聚集索引name查每个满足ming%的id（主键）然后回表找到所有信息，然后过滤掉age=20的，现在的做法是name查ming%的先判断age是否为20，然后用id回表去查

主键和unique约束的字段会自动添加索引

所以根据主键查询效率更高

创建索引

create index emp\_sal\_index on emp(sal);

删除索引

drop index emp\_sal\_index;

还有一个问题，当一个表具有多个索引时，数据库会选择那个来查询呢？

[https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html][https_dev.mysql.com_doc_refman_8.0_en_mysql-indexes.html]

来自官方的解释

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

数据库有两种索引，一种是B+树一种是哈希表(o1查询的那个unordered map）

B+树的插入和查找时间复杂度都是O（logn）的

哈希表的插入和查找时间复杂度都是O（1）的

那既然哈希表时间复杂度优秀为什么还要用B+树

因为哈希表不支持区间查询，B+树的区间查询也是logn的时间复杂度

B+树的特性

1.只有叶子节点保存数据，非叶子节点只保存索引，不保存实际的数据。

2.非叶子节点的子树指针与关键字个数相同

3.非叶子节点子树指针指向关键字大于等于该关键字，小于兄弟关键字的子树

4.为所有的叶子节点增加一个链指针

5.每个节点最多m个儿子，除叶子节点，至少（m+1）/2个儿子，根节点最少2个儿子

为什么使用B+树作为mysql的索引？

B+树能显著减少IO次数，提高效率(树高比较低  
B+树的查询效率更加稳定，因为数据放在叶子节点  
B+树能提高范围查询的效率，只需遍历叶子节点链表即可  
4.增删文件（节点）时，效率更高，因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

哈希表（均摊查询时间复杂度O1)

为什么默认是B+tree而不是哈希表呢明明哈希表更快（范围查询）

为什么不使用B-tree呢？

考虑b-tree的特性，b-树的节点和数据在一起，所以查询时间不稳定，范围查询不方便，全局遍历也不方便

红黑树和AVL的比较

红黑树特点，每个点要么红要么黑，红儿子必须黑，根叶子黑，每条根到叶子路径上的黑色节点数相等（所以最长最短距离不会超过两倍）每次增删改会在三次旋转内回到平衡

AVL平衡二叉树 查询效率稳定，每次增删一个节点时需要updata节点至根以保证平衡，所以查询比红黑树快，增删比红黑树慢

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**谈谈你对mysql索引的理解!**

索引——唯一标识，书的目录，提高检索效率的。这是定义

索引使用的数据结构

**B+树 B+树的优点**，为什么不用Btree 为什么不用二叉树，AVL，红黑树其他博客有提到这些知识点

**哈希表**：哈希冲突如何解决，如何设计一个哈希函数，unordered\_map源码看过么他是怎么实现的？为什么InnoDB和Mysalary不用哈希表（区间查询）

**使用B+树当索引的引擎——InnoDB和Mysalary**

引擎：存储数据的格式

区别：前文有讲，但只是大概的区别，很多地方都不一样的，比如InnoDB的redolog，最大的区别是数据和索引在一起，Mysalary索引数据分开，每次都需要回表，锁粒度不同，支持事务与否，外键完整性InnoDB支持

数据和索引在一起叫做聚簇索引，不在一起叫非聚簇索引，非聚簇索引B+树叶子放的是主键id，还得去聚簇索引处查一次，这个现象叫做回表。

[https_dev.mysql.com_doc_refman_8.0_en_mysql-indexes.html]: https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

mysql 索引部分

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql创建索引，mysql索引优化，mysql索引创建删除

相关 mysql 二进制索引_MySQL索引

相关 mysql索引_MySQL索引

相关 MySQL索引main日志_mysql索引

相关 mysql 索引部分

相关 Mysql技术内幕索引部分

相关 MongoDB 部分索引(Partial Indexes)

相关 Mysql 部分优化

相关 Lucene 索引部分核心~

相关 mysql高级部分