德州力点科技 网站建设,邢台网络问政,八宝山网站建设,百度竞价代运营公司大家好#xff0c;我是考哥。
今天给大家带来MySQL索引相关核心知识。对MySQL索引的理解甚至比你掌握SQL优化还重要#xff0c;索引是优化SQL的前提和基础#xff0c;我们一步步来先打好地基。
当MySQL表数据量不大时#xff0c;缺少索引对查询性能的影响不会太大#x…大家好我是考哥。
今天给大家带来MySQL索引相关核心知识。对MySQL索引的理解甚至比你掌握SQL优化还重要索引是优化SQL的前提和基础我们一步步来先打好地基。
当MySQL表数据量不大时缺少索引对查询性能的影响不会太大可能都是0.0几秒但当表数据量逐日递增时建立一个合适且优雅的索引就至关重要了。 文章目录 1. 索引类型1.1 B-Tree索引1.2 B-Tree值的存储1.3 哈希索引1.4 聚簇索引 2. 索引效率2.1 Explain关键字2.2 索引失效 1. 索引类型 面试官索引有什么用 大家可以把你最近最爱的一本书类比成一个MySQL数据库你要快速翻到你昨天看到的精彩部分是不是要先看下书的目录索引要翻到第几章、第几页。
数据库最主要的就是数据存储其次就是提供复杂查询服务而索引就是MySQL作为快速找到记录的一种数据结构。索引类型有多种像常见的B树索引、哈希索引这些都需要我们去掌握。
不要和我说你看书都用书签或者靠手感就能翻出来昨天看到的地方。
我们对比下不采用索引和采用索引的差异。
目前我本机数据库的article表有10w条数据表结构如下。
CREATE TABLE article (id int(10) NOT NULL AUTO_INCREMENT,author_id int(10) NULL DEFAULT NULL,category_id int(10) NOT NULL DEFAULT 0,views int(10) NULL DEFAULT NULL,comments int(10) NULL DEFAULT NULL,title varbinary(255) NULL DEFAULT NULL,content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,PRIMARY KEY (id, category_id) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 1001 CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT Compact;没建立索引前使用explain关键字分析查询SQL。type显示ALL也就是该SQL执行时对MySQL进行的是全表扫描。
explain select id from article where category_id 1 order by views desc;-------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 102279 | Using where; Using filesort |
-------------------------------------------------------------------------------------------------------建立索引后。
create index idx_ca_vi on article(category_id,views);type显示为ref同时Extra列显示Using where; Using indexUsing index代表该SQL执行时使用了索引而Using index代表了在MySQL服务端再进行了一次views字段的排序。
---------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------
| 1 | SIMPLE | article | ref | idx_ca_vi | idx_ca_vi | 4 | const | 51139 | Using where; Using index |
---------------------------------------------------------------------------------------------1.1 B-Tree索引 面试官B树索引说一下 在杂乱无章的一堆数字里我要你快速找到唯一的一个数字66大家要怎么做
两种选择你在一堆数字里一个个地找就如MySQL全表扫描。或者把所有数都按大小顺序进行排列找到第66个位置的数字。
我们假设建立的是主键索引MySQL索引会根据主键id建立起一棵B-Tree。B-Tree类似于二叉搜索树同样具有快速查找特定值的功能。
1但在结构方面B-Tree又不同于二叉搜索树它是多子树的。即每一个节点可以有两棵以上的子树。
2在值的存储方面B-Tree所有的值都存储在叶子节点。并且每一个叶子节点可以存储多个元素这一点也与二叉搜索树不同。两个人想要去湖里打水一个人拿着手大的碗一个人拿着一个水桶拿水桶的不会比拿碗的装的少。每个叶子节点存储的元素多每次磁盘访问就可以获得更多的数据从而减少查询的I/O操作。
面试官经常会问你这个问题叶子节点是什么数据结构。实际上叶子节点之间用指针链接形成了一串双向链表。这个留到下文解释。
3另外大家很容易漏掉一个重要的知识点。如果是二级索引建立的B-Tree每个叶子节点的值保存的是对应行数据的主键。那一级索引叶子节点保存什么呢一级索引也就是主键索引下文我会告诉大家。 1.2 B-Tree值的存储 面试官你说值都存储在叶子节点那有什么好处 数据库数据都存储在叶子节点会使得非叶子节点层数更少。从外表来看很明显整棵B-Tree的层数变少B-Tree高度变得矮胖。
B-Tree变得矮胖有什么作用举个爬楼梯的例子B-Tee的每一层级就像一层楼。相信大家租房都不想租高楼每次回去都要爬那么多层楼梯膝盖怎么受得了呢。
B-Tree每一层的搜索可能就代表了一次磁盘I/O操作B-Tree的层数变少意味着I/O读取的次数就变少查询的效率也会因此提高。
另外企业业务在查询上更多的是范围查询你对网页的每一次翻页操作都是对MySQL数据的一次范围查询。B-Tree的元素都存储叶子节点同时形成双向链表结构很适合范围查询这种复杂查询操作。
1.3 哈希索引 面试官知道为什么主流数据库引擎不采用哈希索引吗 上文其实已经有涉及到业务上一般都是范围查询而哈希索引由于其底层数据结构不能够支持任何范围查询。这也难怪主流数据库引擎不青睐它。
但其实哈希索引也有它的闪光灯哈希索引会为所有的索引列计算一个哈希码。同时在哈希表中保存哈希码和指向每个数据行的指针这种结构对精确匹配查询的效率极高。
MEMORY数据库引擎底层采用的就是哈希索引。
1.4 聚簇索引 面试官聚簇索引和二级索引有什么关联 读到这里我回答下上文还没回答大家的问题。
首先聚簇索引和主键索引是等同的也有一个一般都不提的名称一级索引。
而B-Tree的二级索引指的是非主键索引它的叶子节点保存的只是行的主键值所以需要另外通过主键来找到行数据。
聚簇索引通过主键来建树它的叶子节点包含了行的全部数据。
这就把两者相关联起来了通过二级索引查找行需要先在二级索引建立的B-Tree上找到主键的值接着再从聚簇索引建立的B-Tree找到行数据。
2. 索引效率
2.1 Explain关键字 面试官那我一条SQL我怎么知道它有没使用到索引 面试官看你简历写了掌握MySQL那这道问题就是必考题。
检查是否使用索引可以利用Explain关键字来分析它会模拟执行sql语句查询出sql语句执行的相关信息如哪些索引可以被命中、哪些索引实际被命中。
我说下Explain查询结果的几个关键字段。 type cost通过索引一次查询ref使用到索引range: 使用到索引all全表扫描 Extra using filesort使用外部文件排序发生在无法使用索引的情况下 using indexwhere查询的列被索引覆盖直接通过索引就可以查询到数据 using wherewhere查询的列没有全部被索引覆盖 using join buffer使用了连接缓存 possible_key 表示可以使用的索引 key 表示实际使用的索引
如果简历你写了精通MySQL那问的可就没这么简单。我可以问你在工作中紧急处理了哪些数据库重大事故优化了哪些业务慢SQL、是怎么优化的、为什么这么做。
2.2 索引失效 面试官有没索引失效的情况呢 索引失效一般是这个SQL查询破坏了使用B-Tree查询的条件。也有一种可能出现如果表数据膨胀得太快即使建立索引你查询起来也会有索引失效的错觉这个问题就要另外讨论了。 如果在where子句中使用not in、!和操作会使索引失效而导致进行全表扫描。 对索引列进行数学函数处理的话索引会失效。 索引是字符串类型查询值没有添加单引号’那索引会失效。因为值类型与索引列类型。不一致MySQL不会使用索引而是把索引列数据进行类型转换后进行查询。 对索引列进行模糊查询%要放在最右侧否则索引会失效。SELECT * FROM user WHERE name LIKE n% 在组合索引中如果前一个索引使用范围查询后面的索引也会失效。
大家在实际工作切忌乱加索引此切忌非切记。每加一次索引MySQL都要多去维护一棵新的B-Tree。增加太多索引数据查询效率会变得低下。
本文收录在我开源的《Java学习面试指南》中目前已经更新有近200道面试官常考的面试题涵盖了Java系列、Redis系列、MySQL系列、多线程系列、Kafka系列、JVM系列、ZooKeeper系列等等。GitHub地址https://github.com/hdgaadd/JavaGetOffer相信你看了一定会有所收获。 创作不易不妨点赞、收藏、关注支持一下各位的支持就是我创作的最大动力❤️
