mysql索引(覆盖索引,联合索引,索引下推)

发布时间：2022-09-12 16:55:14 所属栏目：MsSql教程来源：

导读：　　mysql索引(覆盖索引,联合索引,索引下推)

　　什么是索引？

　　当我们使用汉语字典查找某个字时，我们会先通过拼音目录查到那个字所在的页码，然后直接翻到字典的那一页，找到我们要查的字，通过拼音目

　　mysql索引(覆盖索引,联合索引,索引下推)

　　什么是索引？

　　当我们使用汉语字典查找某个字时，我们会先通过拼音目录查到那个字所在的页码，然后直接翻到字典的那一页，找到我们要查的字，通过拼音目录查找比我们拿起字典从头一页一页翻找要快的多，数据库索引也一样，索引就像书的目录，通过索引能极大提高数据查询的效率。

　　索引的实现方式

　　在数据库中，常见的索引实现方式有哈希表、有序数组、搜索树

　　哈希表

　　哈希表是通过键值对（key-value）存储数据的索引实现方式，可以将哈希表想象成是一个数组，将索引通过哈希函数计算得到该行数据在数组中的位置，然后将数据存到数组中，容易发现一个问题，如果两个索引通过哈希函数计算后得到的数组位置相同要怎么办？在这里，数组的每个value都是一个链表，链表上的每个元素都是一个数据，新数据直接添加到链表尾部。

　　所以数据库查询过程为：索引通过哈希函数计算数据所在位置--> 遍历指定位置的链表，找到满足条件的数据。

　　要注意的是，链表上的数据元素不是有序的，每次有新数据加入时，新数据时直接添加到链表尾部，这样做的好处是添加数据时很方便。

　　哈希表不擅长进行区间查询，一般都用于等值查询：

　　有序数组：

　　顾名思义，有序数组是按索引大小将数据保存在一个数组上，因为该数组是有序的，可以通过二分法很容易查到位置，找到第一个位置后，通过向左/向右遍历很容易得到所求区间的数据。因此，无论是等值查询还是区间查询，效率都极高。但缺陷也是显而易见的，当向数组中间n位置插入一条数据时，需将n后面的数据全部往后移动，所以，这种索引一般用于静态存储引擎。

　　搜索树：

　　当使用平衡二叉实现索引时，结构如下图：

　　从图中可发现，每次查询最多需要访问4个节点必能得到所要数据。例如查询user2时，查询过程为：userA-->userC-->userF-->user2。

　　所以查询速度很高，同时，因为搜索树的特性（左子树小于右子树），区间查询也很方便。

　　如果搜索树存于内存中，与多叉树相比，二叉树的搜索速率是最高的，但实际上数据库使用的是n叉树而不是二叉树。

　　innodb的索引模型

　　innodb使用B+树作为索引结构。

　　在B+树中，我们将节点分为叶子结点和非叶子结点，非叶子结点上保存的是索引，而且一个节点可以保存多个索引；数据全部存于叶子结点上,根据叶子结点的内容不同，innodb索引分为主键索引和非主键索引。非主键索引也称为二级索引。

　　主键索引的叶子结点中保存的数据为整行数据，而非主键索引叶子节点保存的是主键的值。

　　非主键索引图;

　　通过主键索引查询数据时，我们只需查找主键索引树便可以获取数据；通过非主键索引查询数据时，我们先通过非主键索引树查找到主键值，然后再在主键索引树搜索一次，这个过程称为回表，也就是说非主键索引查询会比主键查询多搜索一棵树。所以我们应尽可能使用主键查询。

　　索引维护

　　添加新行时，将会在索引表上添加一条记录mssql 索引，如果是索引递增插入时，数据都是追加在当前最大索引之后，不会对树中其他数据造成影响；如果新加入的数据的索引值位于节点的中间，需要挪动部分节点的位置，从而保持索引树的有序性。

　　而且，相邻多个节点是存储在同一个数据页上的，此时，如果是在已经存储满状态的数据页中插入节点，会申请新的数据页，将部分数据挪动到新的数据页，这个过程称为页分裂，页分裂除了会影响性能，还会降低磁盘空间利用率。不规则数据插入时,会造成频繁的页分裂。

　　当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并

　　所以，一般情况下会采用递增主键，使新数据递增插入。

　　使用业务逻辑字段做主键有什么优缺点？

　　综上，从性能和存储空间方面考量，自增主键往往是更合理的选择,当业务场景有且只有一个索引，而且该索引为唯一索引时，此时更适合使用业务逻辑字段作为主键。

　　因为数据修改/删除、页分裂等原因，会导致数据页空间利用率降低，此时，可以考虑重建索引，将数据按顺序插入，提高磁盘空间利用率。但重建主键索引和普通索引会有不同影响，重建普通索引，可以达到提高空间利用率的目的，且不会对其他索引造成影响，但如果重建主键索引就不合理了，会影响所有普通索引，性能影响较大，而且无论是新建/删除主键，都会重建整张表。这时我们可以使用alter table T engine=InnoDB这个语句代替。

　　查看索引利用率

　　查看performance_schema.table_io_waits_summary_by_index_usage表

　　覆盖索引

　　mysql的innodb引擎通过搜索树方式实现索引，索引类型分为主键索引和二级索引（非主键索引），主键索引树中，叶子结点保存着主键即对应行的全部数据；而二级索引树中，叶子结点保存着索引值和主键值，当使用二级索引进行查询时，需要进行回表操作。假如我们现在有如下表结构

　　CREATE TABLE `user_table` (
　　`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
　　`username` varchar(255) NOT NULL,
　　`password` varchar(255) DEFAULT NULL,
　　`age` int(11) unsigned Not NULL,
　　PRIMARY KEY (`id`),
　　key (`username`)
　　) ENGINE=InnoDB DEFAULT CHARSET=utf8
　　执行语句(A) select id from user_table where username = 'lzs'时，因为username索引树的叶子结点上保存有username和id的值，所以通过username索引树查找到id后，我们就已经得到所需的数据了，这时候就不需要再去主键索引上继续查找了。

　　执行语句(B) select password from user_table where username = 'lzs'时，

　　流程如下:

　　由上面可知，当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中，可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数，是常用的性能优化手段。

　　例如上面的语句B是一个高频查询的语句，我们可以建立(username,password)的联合索引，这样，查询的时候就不需要再去回表操作了，可以提高查询效率。当然，添加索引是有维护代价的，所以添加时也要权衡一下。

　　联合索引

　　mysql的b+树索引遵循“最左前缀”原则，继续以上面的例子来说明，为了提高语句B的执行速度，我们添加了一个联合索引（username,password）,特别注意这个联合索引的顺序，如果我们颠倒下顺序改成（password,username),这样查询能使用这个索引吗？答案是不能的！这是最左前缀的第一层含义：联合索引的多个字段中，只有当查询条件为联合索引的一个字段时，查询才能使用该索引。

　　现在，假设我们有一下三种查询情景：

　　以上三种情况下，只有第1种能够使用（username,password）联合索引来加快查询速度。这就是最左前缀的第二层含义：索引可以用于查询条件字段为索引字段，根据字段值最左若干个字符进行的模糊查询。

　　维护索引需要代价，所以有时候我们可以利用“最左前缀”原则减少索引数量，上面的（username,password）索引，也可用于根据username查询age的情况。当然，使用这个索引去查询age的时候是需要进行回表的，当这个需求（根据username查询age）也是高频请求时，我们可以创建（username,password,age）联合索引，这样，我们需要维护的索引数量不变。

　　创建索引时，我们也要考虑空间代价，使用较少的空间来创建索引

　　假设我们现在不需要通过username查询password了，相反，经常需要通过username查询age或通过age查询username,这时候，删掉（username,password）索引后，我们需要创建新的索引，我们有两种选择

　　一般来说，username字段比age字段大的多，所以，我们应选择第一种，索引占用空间较小。

　　索引下推

　　对于user_table表，我们现在有（username,age）联合索引

　　如果现在有一个需求，查出名称中以“张”开头且年龄小于等于10的用户信息，语句C如下："select * from user_table where username like '张%' and age > 10".

　　语句C有两种执行可能：

　　1、根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于10的用户数据。

　

　　2、根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据。

　　明显的，第二种方式需要回表查询的全行数据比较少，这就是mysql的索引下推。mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制

　　SET optimizer_switch = 'index_condition_pushdown=off';
　　注意点：

　　1、innodb引擎的表，索引下推只能用于二级索引。

　　就像之前提到的，innodb的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

　　2、索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。

　　假设表t有联合索引（a,b）,下面语句可以使用索引下推提高效率

　　select * from t where a > 2 and b > 10;

　　关于mysql索引(覆盖索引,联合索引,索引下推)的文章就介绍至此，更多相关mysql索引内容请搜索编程宝库以前的文章，希望以后支持编程宝库！

　　下一节：Mysql在项目中相关使用方法指南(简单操作数据库)MySQL教程

　　数据库的基本概念什么是数据库?数据库（Database）是用来组织、存储和管理数据的仓库。当今世界是一个充满着数据的互联网世界，充斥着大量的数据。数据的来源有很多，比如出行记 ...

（编辑：均轻资讯网_我爱站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

Sql中session阻塞怎样	SQL常见数据类型有什么
Sql Server 视图数据的	SQL SERVER临时表排序