Mysql索引基础

电玩女神 2022-05-10 09:10 200阅读 0赞

### 什么是索引? ###

索引是存储引擎用于快速找到记录的一种数据结构. 索引是在存储引擎层而不是服务器层实现的, 所以不同储存引擎的索引的工作方式是不一样的, 这里主要介绍应用最多的InnoDB存储引擎的B+Tree索引.

**索引的基本语法**

(1)创建索引

CREATE [UNIQUE] INDEX 索引名 ON 表名 (column_list)
    
    ALTER TABLE 表名 ADD [UNIQUE] INDEX 索引名 ON (column_list)

(2)删除索引

DROP INDEX 索引名 ON 表名

(3)查看索引

SHOW INDEX FROM 表名

**后面实例需要用到的表结构**

CREATE TABLE `user_info` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL DEFAULT '',
      `age` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name_index` (`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
    
    CREATE TABLE `order_info` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `product_name` varchar(50) NOT NULL DEFAULT '',
      `productor` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `user_product_detail_index` (`user_id`,`product_name`,`productor`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

### B+Tree索引 ###

在InnoDB存储引擎中, 数据管理的最小单位为页, 默认是16KB. B+Tree索引的一个节点大小等于一个页的大小. 其中非叶子节点存储键值和指针信息, 键值为索引值, 指针指向比键值大的节点. 叶子节点存储键值、数据信息以及一个指向下一个叶子节点的指针. B+Tree对索引列是按顺序储存的(默认按键值升序), 并且每一个叶子节点到根节点的距离相同, 下图是一个主键索引的结构:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70]

B+Tree支持两种查找运算, 一种是从根节点开始进行二分查找. 另一种是区间查找, 例如, 如果要查询键值为从15到49的所有数据记录, 当找到15后, 只需顺着叶子节点的指针, 顺序遍历就可以一次性访问到所有数据节点, 极大提高了区间查询效率.

InnoDB存储引擎的B+Tree索引又分为聚集索引(Clustered Index)和辅助索引(Secondary Index)两种. 每张表只能有一个聚集索引, 其余全是辅助索引. 聚集索引的定义如下:

*  如果定义了主键, InnoDB会自动使用主键来创建聚集索引.
 *  如果没有定义主键, InnoDB会选择一个唯一的非空索引(UNIQUE+字段不能为空), 将其作为聚集索引.
 *  如果既没有定义主键, 又没有唯一的非空索引, InnodDB会生成一个长度为6字节的隐藏主键.

上面的"主键索引"示例图即为聚集索引, 聚集索引按照每张表的主键构造一颗B+Tree, 叶子节点中存放的即为整张表的记录数据. 辅助索引与聚集索引的区别在于, 辅助索引的叶子节点并不存储行记录, 而是存储对应行记录的主键. 这意味着当使用辅助索引查询数据时, 需要进行两次索引查找, 第一次通过辅助索引查找到对应的主键值, 第二次根据主键值在聚集索引中查找到对应的行. 辅助索引结构如下图所示:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 1]

### 索引性能分析 ###

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化. EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 EXPLAIN 就可以了, 例如:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 2]

**EXPLAIN输出格式**

(1) id

select标识符, 表示select查询的执行顺序, id值越大优先级越高, 越先被执行. id相同, 执行顺序由上至下.

(2)select\_type

查询类型, 主要用于区分普通查询, 联合查询, 子查询等复杂查询. 常见类型如下:

<table> 
 <thead> 
  <tr> 
   <th>select_type</th> 
   <th>Meaning</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>表示此查询不包含 UNION 查询或子查询</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>表示此查询是最外层的查询</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>子查询中的第一个 SELECT</td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>在FROM列表中包含的子查询被标记为DERIVED, MySQL会递归执行这些子查询, 把结果放在临时表里</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>表示此查询是 UNION 后的查询</td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>从UNION表获取结果的SELECT</td> 
  </tr> 
 </tbody> 
</table>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 3]

(3)table

表示查询的表或衍生表.

(4)type

表示查询访问数据的方式(或者说MySQL在表中找到所需行的方式). 下面按照从最佳类型到最差类型的顺序给出常见的连接类型:

*  system  
    查询的表是仅有一行记录的系统表, 这是const连接类型的一个特例.
 *  const  
    针对主键或唯一索引的等值查询, 最多只返回一行数据. ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 4]
 *  eq\_ref  
    连表查询时使用主键或者唯一索引列作为关联条件.![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 5]
 *  ref  
    连表查询时使用普通的非唯一索引列作为关联条件.
 *  range  
    使用任意类型的索引来查询给定范围的行.如 like, in, >, < , !=, between等.![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 6]
 *  index  
    Full Index Scan, index与ALL区别为index类型只遍历索引树. 这通常比ALL快, 因为索引文件通常比数据文件小.![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 7]
 *  all  
    Full Table Scan, 将遍历全表以找到匹配的行.![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 8]

(5)possible\_keys

可能使用的索引.

(6)key

实际使用到的索引.

(7)key\_len

实际使用到的索引的总长度. 通过key\_len值可以确定MySQL实际使用了一个复合索引的几个字段.

(8)ref

哪些列或常量被用于查找索引列上的值.

(9)rows

显示在查询时必须检查的行数.

(10)Extra

MySQL解决查询的额外信息, 包括:

*  Using filesort  
    无法利用索引完成排序操作, 必须额外进行一次排序.![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 9]
 *  Using temporary  
    表示需要创建临时表来存储结果集, 常见于连表排序和分组查询.![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 10]
 *  Using index  
    “覆盖索引扫描”,只需从辅助索引中就可以得到查询记录, 而不需要查询聚集索引. 覆盖索引即一个索引包含所有需要查询的字段.
 *  Using index condition  
    需要查询聚集索引.
 *  Using where  
    全表扫描或者有一部分过滤条件没有命中索引. 需要与type结合来看, 如果type=all, 则为全表扫描, 否则就是有一部分过滤条件没有命中索引.
 *  Using join buffer  
    在获取连接条件时没有使用索引, 并且需要连接缓冲区来存储中间结果. 如果出现了这个值, 那应该注意, 根据查询的具体情况可能需要添加索引来改进能.
 *  Impossible where  
    where子句的值总是false, 不能用来获取任何元组.
 *  Distinct  
    在distinct 查询中, 如果找到了第一个匹配的元素, 就停止查找同样值的操作.

### 索引分类 ###

**单列索引**

单列索引即一个索引只包含单个列. MySQL有两种特殊的单列索引, 分别是唯一索引(UNIQUE字段修饰的列)和主键索引(被PRIMARY KEY修饰的列).

MySQL5.0后引入了一种叫"索引合并"的策略, 一定程度上可以使用表上的多个单列索引来定位指定的行.

单列索引不能是表达式的一部分, 也不能是函数的参数, 否则将失效. 如下面两种情况都将导致索引失效:

(1)作为表达式的一部分  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 11]

(2)作为函数的参数

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 12]

--------------------

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 13]

**多列索引**

多列索引即一个索引包含多个列.

索引可以按照升序或者降序进行扫描, 以满足精确符合列顺序的ORDER BY, GROUP BY 和 DISTINCT 等子句的查询需求. 多列索引要注意索引列的顺序, 因为B-Tree在排序时先按照最左列排序, 其次是第二列往后, 如果跳过了一列将会导致索引失效.

(1)正常使用索引  
![在这里插入图片描述][2019032818342069.png]

--------------------

![在这里插入图片描述][20190328183427919.png]

(2)索引失效

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 14]

--------------------

![在这里插入图片描述][20190328183501285.png]

(3)注意一种情况, 当多列索引前面的列已经确定时可以跳过.

![在这里插入图片描述][20190328183520140.png]

### 索引优化 ###

(1)尽量使用全值匹配, 即多列索引各列都用到.  
![在这里插入图片描述][20190328183538659.png]

(2)尽量使用覆盖索引, 即一个索引包含所有需要查询的字段. 这样查询只需从辅助索引中就可以得到查询记录, 而不需要查询聚集索引.  
![在这里插入图片描述][2019032818355713.png]

(3)使用索引扫描来做排序.

(4)不要在索引上做任何操作(计算, 函数, 类型转换等), 否则会导致索引失效而转向全表扫描.

(5)满足最左前缀法则, 即对于多列索引, 要从左往右进行匹配, 且不能跳过一列.

(6)范围条件之后的索引全失效(like, in, >, < , !=, between等), LIKE的百分号写最右边.  
![在这里插入图片描述][20190328183628425.png]

![在这里插入图片描述][20190328183636341.png]

--------------------

![在这里插入图片描述][20190328183702795.png]  
![在这里插入图片描述][2019032818371143.png]

(7)is null, is not null ,or容易造成索引失效.  
![在这里插入图片描述][20190328183739679.png]

(8)如果是字符串类型的数据, 不能丢失引号, 否则会进行自动类型转换, 从而导致索引失效.  
![在这里插入图片描述][20190328183748733.png]

### 索引的优缺点 ###

(1)优点

*  提高数据的检索效率.
 *  索引可以帮助服务器避免排序和临时表(ORDER BY, GROUP BY 和 DISTINCT).

(2)缺点

*  索引需要占据磁盘空间.
 *  虽然索引大大提高了查询速度, 但同时也会降低更新表的速度(如INSERT,UPDATE, DELETE), 因为更新表时, MySQL不仅要更新数据, 还要更新索引信息.

### 哪些情况需要创建索引 ###

*  频繁作为查询条件的字段应该创建索引.
 *  查询中与其他表关联的字段, 外键关系创建索引.
 *  查询中排序的字段应该创建索引.
 *  查询中统计或分组的字段应该创建索引, 因为分组操作会默认先进行排序.
 *  频繁更新的字段不适合创建索引.
 *  where条件里用不到的字段不创建索引.
 *  表记录太少不需要创建索引, 如100万以下.
 *  数据重复且分布平均的字段不适合创建索引.

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70]: /images/20220510/9f2ce43288ef4803b5fe43174d0d911d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 1]: /images/20220510/a754aaaf7cd846d4a5ce45b9a95e2b9e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 2]: /images/20220510/8fdca12a02224a4ba5110c8fbb54f185.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 3]: /images/20220510/fd4ea05883fb42ce84b5ef8ca1e39fe6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 4]: /images/20220510/54d9a6d8ac2e4bf5acf611d674e63728.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 5]: /images/20220510/cb46776ea3ae4696a6b06b76d0482e97.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 6]: /images/20220510/6176bb04228143168f6f6ce5126a6cc5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 7]: /images/20220510/e0d19cf8d02242dbb28b88fe50cbb6e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 8]: /images/20220510/a30d463528084e60ab044ca7cd6111b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 9]: /images/20220510/ac80a17267204ecaaffda264427ef5ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 10]: /images/20220510/219447f2f10b4dbcbcceed08277e8b4a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 11]: /images/20220510/9f6c41020a3541b7a436647cb9dd96d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 12]: /images/20220510/ce1c6086e8aa4b7bbb6f67a7a4afa3bf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 13]: /images/20220510/86f9509ee00247d5b40f116b5e1c6027.png
[2019032818342069.png]: /images/20220510/7d96ca48fba04e78b9361759204a586e.png
[20190328183427919.png]: /images/20220510/dd74798ebb0640eb900b38659589c41f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70 14]: /images/20220510/643b2fe676534010b031ce0d5ba64000.png
[20190328183501285.png]: /images/20220510/e176e4918a9c4daeaabd46701fb5f816.png
[20190328183520140.png]: /images/20220510/0f5be060467d4347adb76950e0c2b1ad.png
[20190328183538659.png]: /images/20220510/8e66358196454cf9a40a401059285b0e.png
[2019032818355713.png]: /images/20220510/c22436fff692438988364cc59763f1c0.png
[20190328183628425.png]: /images/20220510/39cb23d677fe4d698c6be332839a19f9.png
[20190328183636341.png]: /images/20220510/2076b43ae6dd4391925c849d9fa7b17b.png
[20190328183702795.png]: /images/20220510/977e4be6beb54e9c877b4fcecf6c3186.png
[2019032818371143.png]: /images/20220510/3c1bb865a4444b8ba4cc927ac0ff75b0.png
[20190328183739679.png]: /images/20220510/32915b3c503341f488764a3368d7a661.png
[20190328183748733.png]: /images/20220510/0f8b6c59b3a94d00bf3adc6423bfabfe.png