浅聊 MySQL索引覆盖

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并 且需要返回的列，在该索引中已经全部能够找到 。

延寿网站建设公司创新互联,延寿网站设计制作，有大型网站制作公司丰富经验。已为延寿数千家提供企业网站建设服务。企业网站搭建\外贸网站建设要多少钱，请找那个售后服务好的延寿做网站的公司定做！

现在有一张用户表tb_user;

索引情况：

接下来，我们来看一组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——关于索引的总结

首先说说索引的 优点 ：最大的好处无疑就是提高查询效率。有的索引还能保证数据的唯一性，比如唯一索引。

而它的 坏处 也很明显：索引也是文件，我们在创建索引时，也会创建额外的文件，所以会占用一些硬盘空间。其次，索引也需要维护，我们在增加删除数据的时候，索引也需要去变化维护。当一个表的索引多了以后，资源消耗是很大的，所以必须结合实际业务再去确定给哪些列加索引。

再说说索引的基本结构。一说到这里肯定会脱口而出：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 可以为每一张表设置 存储引擎 这里我们只说 InnoDB 存储引擎.

由于实际情况，数据页只能按照一棵 B+树 进行排序， 因此每张表只能拥有一个 聚集索引（即 主键）。

栗子：

每个叶子节点的索引行中包含了一个书签（bookmark）. 该书签是用来告诉 InnoDB存储引擎哪里可以找到该索引对应的数据行或者说 行数据！ 由于InnoDB存储引擎表， 是按照主键来构建的， 所以 ，该书签内其实包含或者说指向了 数据行所对应的聚集索引键

也就是说 辅助索引的 叶结点保存了 指向对应数据的 聚集索引， 可以通过该聚集索引 找到对应的数据行

辅助索引的存在并不影响数据在聚集索引中的组织，因为每张表上可以有多个辅助索引。

当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引(聚集索引)的主键，然后再通过聚集索引找到一个完整的数据行。

例如：

聚集索引辅助索引关系:

： 又叫做组合索引 ， 辅助索引的一种 ， 和普通创建索引的方式一样，不同的是 可以同时添加多列来作为索引项;

从本质上来说，联合索引也是一课B+树

个人理解： 所谓最左原则， 是因为 存储引擎构建组合索引时 是根据最左边的那一列索引项进行排序的 ，所以使用组合索引，必须满足 条件中必须存在 最左边那一列的索引项，这样 才可以找到对应的索引，继而 去寻找对应的数据

： 又叫做 索引覆盖，InnoDB中支持覆盖索引，即 从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

比如 这里没有根据最左原则使用组合索引，但是 优化器依然进行选择

共勉，欢迎指导谢谢~

mysql 索引

二级索引？？

mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

聚簇索引主要是为了方便存储。。所以二级索引应该都是对聚簇索引的索引。

下面是Mysql Manual上的原话，也可能我理解有误。

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. If you define a PRIMARY KEY on your table, the index of the primary key is the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records.)

In InnoDB, the records in non-clustered indexes (also called secondary indexes) contain the primary key value for the row. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

MySql是怎么使用的索引，在哪些情况下会使用

MySql为以下这些操作使用索引：

1、为了快速查找匹配WHERE条件的行。

2、为了从考虑的条件中消除行。如果在多个索引之间选择一个，正常情况下，MySql使用找到行的最小数量的那个索引。

3、如果表有一个multiple-column索引，任何一个索引的最左前缀可以通过使用优化器来查找行。例如，如果你有一个 three-column索引在(col1, col2, col3)，你能搜索索引在(col1), (col1, col2),和 (col1, col2, col3)。

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