正文
mysql辅助索引怎么走 mysql主索引和辅助索引
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
MySQL——关于索引的总结
首先说说索引的 优点 :最大的好处无疑就是提高查询效率。有的索引还能保证数据的唯一性,比如唯一索引。
而它的 坏处 也很明显:索引也是文件,我们在创建索引时,也会创建额外的文件,所以会占用一些硬盘空间。其次,索引也需要维护,我们在增加删除数据的时候,索引也需要去变化维护。当一个表的索引多了以后,资源消耗是很大的,所以必须结合实际业务再去确定给哪些列加索引。
再说说索引的基本结构。一说到这里肯定会脱口而出:B+树!了解B+树前先要了解二叉查找树和二叉平衡树。 二叉查找树 :左节点比父节点小,右节点比父节点大,所以二叉查找树的中序遍历就是树的各个节点从小到大的排序。 二叉平衡树 :左右子树高度差不能大于1。B+树就是结合了它们的特点,当然,不一定是二叉树。
为什么要有二叉查找树的特点?? 因为查找效率快,二分查找在这种结构下,查找效率是很快的。 那为什么要有平衡树的特点呢? 试想,如果不维护一颗树的平衡性,当插入一些数据后,树的形态有可能变得很极端,比如左子树一个数据没有,而全在右子树上,这种情况下,二分查找和遍历有什么区别呢?而就是因为这些特点需要去维护,所以就有了上面提到的缺点,当索引很多后,反而增加了系统的负担。
接着说B+树。 它的结构如下 :
可以发现,叶子节点其实是一个 双向循环链表 ,这种结构的好处就是,在范围查询的时候,我只用找到一个数据,就可以直接返回剩余的数据了。比如找小于30的,只用找到30,其余的直接通过叶子节点间的指针就可以找到。再说说其他特点: 数据只存在于叶子节点 。当叶子节点满了,如果再添加数据,就会拆分叶子节点,父节点就多了个子节点。如果父节点的位置也满了,就会扩充高度,就是拆分父节点,如25 50 75拆分成:25为左子树,75为右子树,50变成新的头节点,此时B+树的高度变成了3。它们的扩充的规律如下表,Leaf Page是叶子节点,index Page是非叶子节点。
再说说B树 ,B树相比较B+树,它所有节点都存放数据,所以在查找数据时,B树有可能没到达叶子节点就结束了。再者,B树的叶子节点间不存在指针。
最后说说Hash索引 ,相较于B+树,Hash索引最大的优点就是查找数据快。但是Hash索引最大的问题就是不支持范围查询。试想,如果查询小于30的数据,hash函数是根据数据的值找到其对应的位置,谁又知道小于30的有哪几个数据。而B+树正好相反,范围查询是它的强项。
附录: Hash到底是啥?? 哈希中文名散列,哈希只是它的音译。 为啥都说Hash快?? 首先有一块哈希表(散列表),它的数据结构是个数组,一个任意长度的数据通过hash函数都可以变成一个固定长度的数据,叫hash值。然后通过hash值确定在数组中的位置,相同数据的hash值是相同的,所以我们存储一个数据以后,只需O(1)的时间复杂度就可以找到数据。 那hash函数又是啥?? 算术运算或位运算,很多应用里都有hash函数,但实际运算过程大不一样。这是Java里String的hashCode方法:
publicint hashCode() {
}
还有一个问题,hash函数计算出来的hash值有可能存在碰撞,即两个不同的数据可能存在相同的hash值,在MySQL或其他的应用中,如Java的HashMap等,如果存在碰撞就会以当前数组位置为头节点,转变成一个链表。
说到这里也清楚了为啥Java中引用类型要同时重写hashCode和equals了。两个对象,实例就算一模一样,它们的hash值也不相等, 为啥不相等?? 默认的Object的hashCode方法会根据对象来计算hash值的,实例相同,但它们还是两个不同的对象啊,所以我们重写hashCode时,最简单的方法就是调用Object的hashCode方法,然后传入该引用类型的属性,让hashCode方法只根据这几个属性来计算,那么实例相同的话,它们的hash值也会相等。等hashCode比较完后,如果相等再比较实例内容,也就是equals,确保不是hash碰撞。
索引的分类
如果我们指定了一个主键,那么这个主键就是主键索引。如果我们没有指定,Mysql就会自动找一个非空的唯一索引当主键。如果没有这种字段,Mysql就会创建一个大小为6字节的自增主键。如果有多个非空的唯一索引,那么就让第一个定义为唯一索引的字段当主键,注意,是第一个定义,而不是建表时出现在前面的。
对于辅助索引来说,它们的B+树结构稍微有点特殊,它们的叶子节点存储的是主键,而不是整个数据。所以在大部分情况下,使用辅助索引查找数据,需要二次查找。但并不是所有情况都需要二次查找。比如查找的数据正好就是当前索引字段的值,那么直接返回就行。这里提一句,B+树的key就是对应索引字段的内容。
而辅助索引又有一些分类:唯一索引:不能出现重复的值,也算一种约束。普通索引:可以重复、可以为空,一般就是查询时用到。前缀索引:只适用于字符串类型数据,对字符串前几个字符创建索引。全文索引:作用是检测大文本数据中某个关键字,这也是搜索引擎的一种技术。
注意,聚集索引、非聚集索引和前面几个索引的分类并不是一个层面上的。上面的几个分类是从索引的作用来分析的。聚集、非聚集索引是从索引文件上区分的。主键索引就属于聚集索引,即索引和数据存放在一起,叶子节点存放的就是数据。数据表的.idb文件就是存放该表的索引和数据。
辅助索引属于非聚集索引,说到这也就明白了。索引和数据不存放在一起的就是非聚集索引。在MYISAM引擎中,数据表的.MYI文件包含了表的索引, 该表的 叶子节点存储索引和索引对应数据的指针,指向.MYD文件的数据。
索引的几点使用经验
经常被查询的字段;经常作为条件查询的字段;经常用于外键连接或普通的连表查询时进行相等比较字段;不为null的字段;如果是多条件查询,最好创建联合索引,因为联合索引只有一个索引文件。
经常被更新的字段、不经常被查询的字段、存在相同功能的字段
浅聊 MySQL索引覆盖
尽量使用覆盖索引mysql辅助索引怎么走,减少select *。 那么什么是覆盖索引呢? 覆盖索引是指 查询使用了索引mysql辅助索引怎么走,并 且需要返回的列,在该索引中已经全部能够找到 。
现在有一张用户表tb_user;
索引情况:
接下来,mysql辅助索引怎么走我们来看一组SQL的执行计划,看看执行计划的差别,然后再来具体做一个解析。
Using where; Using Index:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需 要回表查询数据
Using index condition:查找使用了索引,但是需要回表查询数据
因为,在tb_user表中有一个联合索引 idx_user_pro_age_sta,该索引关联了三个字段 profession、age、status,而这个索引也是一个二级索引,所以叶子节点下面挂的是这一行的主 键id。 所以当我们查询返回的数据在 id、profession、age、status 之中,则直接走二级索引 直接返回数据了。 如果超出这个范围,就需要拿到主键id,再去扫描聚集索引,再获取额外的数据了,这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值,很容易就会造成回表 查询(除非是根据主键查询,此时只会扫描聚集索引)。
为了大家更清楚的理解,什么是覆盖索引,什么是回表查询,我们一起再来看下面的这组SQL的执行过 程。
id是主键,是一个聚集索引。 name字段建立了普通索引,是一个二级索引(辅助索引)。
B. 执行SQL : select * from tb_user where id = 2;
根据id查询,直接走聚集索引查询,一次索引扫描,直接返回数据,性能高。
C. 执行SQL:selet id,name from tb_user where name = 'Arm';
虽然是根据name字段查询,查询二级索引,但是由于查询返回在字段为 id,name,在name的二级索 引中,这两个值都是可以直接获取到的,因为覆盖索引,所以不需要回表查询,性能高。
D. 执行SQL:selet id,name,gender from tb_user where name = 'Arm';
由于在name的二级索引中,不包含gender,所以,需要两次索引扫描,也就是需要回表查询,性能相 对较差一点。
MySql 索引(聚集索引,辅助索引,联合索引,覆盖索引..)
引入一个面试问题:
看完以下以后再回顾,会发现迎刃而解
Mysql 可以为每一张表设置 存储引擎 这里我们只说 InnoDB 存储引擎.
由于实际情况,数据页只能按照一棵 B+树 进行排序, 因此每张表只能拥有一个 聚集索引(即 主键)。
栗子:
每个叶子节点的索引行中包含了一个书签(bookmark). 该书签是用来告诉 InnoDB存储引擎哪里可以找到该索引对应的数据行或者说 行数据! 由于InnoDB存储引擎表, 是按照主键来构建的, 所以 ,该书签内其实包含或者说指向了 数据行所对应的聚集索引键
也就是说 辅助索引的 叶结点保存了 指向对应数据的 聚集索引, 可以通过该聚集索引 找到对应的数据行
辅助索引的存在并不影响数据在聚集索引中的组织,因为每张表上可以有多个辅助索引。
当通过辅助索引来寻找数据时,InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引(聚集索引)的主键,然后再通过聚集索引找到一个完整的数据行。
例如:
聚集索引辅助索引关系:
: 又叫做组合索引 , 辅助索引的一种 , 和普通创建索引的方式一样,不同的是 可以同时添加多列来作为索引项;
从本质上来说,联合索引也是一课B+树
个人理解: 所谓最左原则, 是因为 存储引擎构建组合索引时 是根据最左边的那一列索引项进行排序的 ,所以使用组合索引,必须满足 条件中必须存在 最左边那一列的索引项,这样 才可以找到对应的索引,继而 去寻找对应的数据
: 又叫做 索引覆盖,InnoDB中支持覆盖索引,即 从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。
比如 这里没有根据最左原则使用组合索引,但是 优化器依然进行选择
共勉,欢迎指导谢谢~
Mysql-多表查询as索引
方法
(1) 根据需求找到关联表
(2)找到关联条件
重点的表tables
* 需求1:统计world库下有几个表
需求2:统计所有库下表的个数
需求3:统计每个库的总数据大小
--单表占空间:AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
什么使索引:索引其实就是一种算法
BTree
HASH
Rtree
Fulltext
辅助索引
辅助索引只提取索引列作为叶子节点
聚集索引
聚集索引提取整行数据作为叶子节点
1、辅助索引和聚集索引最大的区别就在于叶子节点,枝节点和根节点原理相同
2、辅助索引会记录主键值,一般情况(除等值查询),最终都会通过聚集索引(主键)来找到需要的数据
第二种
一般经常用来查询的列作为索引
索引可以有多个,但是索引名不可重名
第一种:单列索引
第二种:前缀索引
前缀索引只能应用到字符串列,数字列不能用前缀索引
联合索引说明:如果在一个表内对A、B、C三个列创建联合索引那么创建索引将按照如下情况创建索引表:
A
AB
ABC
======================================================
(1)查询列无索引
(2)语句不符合走走索引条件
(3)需要查看全表
即把有索引的列全便利一遍
、 、 =、 =、 like、 between and 在范围扫描中,这些会受到B+tree索引叶子节点上额外的优化,因为这些是连续取值的
or、in 这两个不是连续的取值,所以不能受到B+tree索引的额外优化,使用时相当于Btree索引
!=、 not in 只有在主键列才走索引也是range级别
(1)、 、 =、 =、 like、 between and
(2)or、in
(3)!=、 not in
多表连接查询,非驱动表连接条件是主键或唯一键
一般多表查询的时,最左侧的表为驱动表,右侧的为非驱动表,下边的例子中country标为非驱动表
7.1.1 数字类型
7.1.2 字符串类型:
字符集
中文
gbk 2字节
utf8 3字节
utf8mb4 4字节
utf8mb4 为例:
举例(1)联合索引等值查询
举例(2) 联合索引中有不等值查询
如果Extra列出现Using temporary、Using filesort,两项内容,那么考虑以下语句的问题。
group by
order by
distinct
join on
union
关于mysql辅助索引怎么走和mysql主索引和辅助索引的介绍到此就结束了,不知道你从中找到你需要的信息了吗 ?如果你还想了解更多这方面的信息,记得收藏关注本站。