索引管理

超、凢脫俗 2022-04-23 10:06 167阅读 0赞

# 简介 #

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

# 平衡二叉树、B树、B+树、B\*树 #

一般都在索引用的数据类型就是B+树，这里顺便记一下其他的数据结构。

### 平衡二叉树 ###

平衡二叉树是基于二分法的策略提高数据的查找速度的二叉树的数据结构；平衡二叉树是采用二分法思维把数据按规则组装成一个树形结构的数据，用这个树形结构的数据减少无关数据的检索，大大的提升了数据检索的速度；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70]

### B树(B-tree) ###

B树和平衡二叉树稍有不同的是B树属于多叉树又名平衡多路查找树（查找路径不只两个），数据库索引技术里大量使用者B树和B+树的数据结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 1]

##### B树的查询流程： #####

我要从上图中找到E字母，查找流程如下

1.  获取根节点的关键字进行比较，当前根节点关键字为M，E<M（26个字母顺序），所以往找到指向左边的子节点（二分法规则，左小右大，左边放小于当前节点值的子节点、右边放大于当前节点值的子节点）；
2.  拿到关键字D和G，D<E<G 所以直接找到D和G中间的节点；拿到关键字D和G，D<E<G 所以直接找到D和G中间的节点；
3.  拿到E和F，因为E=E 所以直接返回关键字和指针信息（如果树结构里面没有包含所要查找的节点则返回null）；拿到E和F，因为E=E 所以直接返回关键字和指针信息（如果树结构里面没有包含所要查找的节点则返回null）；

### B+树 ###

B+树是B树的一个升级版，在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点 中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 2]

### B\*树 ###

在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 3]

# 索引创建,删除,查看 #

### 创建索引 ###

CREATE INDEX index_name ON table_name (column_list)

或

ALTER TABLE table_name ADD INDEX index_name (column_list)

### 删除索引 ###

ALTER TABLE table_name DROP INDEX index_name

### 查看索引 ###

SHOW INDEX FROM  table_name ;

### 主键索引创建 ###

*  全表只能有一个主键
 *  唯一
 *  非空
 *  表创建的时候至少要有一个主键索引，最好和业务无关。

1.  建立表时创建

CREATE TABLE `test` (
      `id` int(4) NOT NULL AUTO_INCREMENT,
      `name` char(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

1.  建立表后增加

CREATE TABLE `test1` (
      `id` int(4) NOT NULL,
      `name` char(20) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
    
    --增加自增主键
    alter table test1 change id id int(11) primary key not null auto_increment;

### 普通索引创建 ###

加快查询速度，工作中优化数据库的关键。在合适的列上建立索引，让数据查询更高效。在`where`条件关键字后面的列建立索引才会加快查询速度.

-- 第一种方式
    create index index_name on test(name);
    -- 第二种方式
    alter table test add index index_name(name);

### 唯一索引创建 ###

内容唯一，但不是主键。

alter table test add unique index idx_name(name);

怎么判断某个列的值都是唯一的？  
1、总行数查询  
`select count(*) from test;`  
2、基于某个列去重复之后还剩多少行  
`select count(distinct population) from test;`

# EXPLAIN命令的应用 #

`EXPLAIN`用于获取查询执行计划

mysql> EXPLAIN SELECT * FROM test \G
    *************************** 1. row ***************************
               id: 1       
      select_type: SIMPLE  
            table: test    
       partitions: NULL    
             type: ALL     
    possible_keys: NULL    
              key: NULL    
          key_len: NULL    
              ref: NULL    
             rows: 1       
         filtered: 100.00  
            Extra: NULL
    1 row in set, 1 warning (0.00 sec)

### EXPLAIN输出列 ###

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>该SELECT标识符</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>该SELECT类型</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>匹配的分区</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>连接类型</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>可供选择的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>实际选择的指数</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>所选键的长度</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>列与索引进行比较</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>估计要检查的行</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>按表条件过滤的行的百分比</td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>附加信息</td> 
  </tr> 
 </tbody> 
</table>

*  id  
    SELECT标识符。这是SELECT查询中的序号
 *  select\_type

<table> 
 <thead> 
  <tr> 
   <th>select_type 常见值</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单SELECT（不使用 UNION或子查询）</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>使用了<code>UNION</code>查询</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>第一个<code>SELECT</code>是子查询</td> 
  </tr> 
 </tbody> 
</table>

*  table  
    表名
 *  partitions  
    查询将匹配记录的分区。该值适用NULL于非分区表
 *  type（非常重要）  
    该type列 EXPLAIN输出介绍如何联接表。从最差类型到最佳类型的连接类型：`ALL`,`index`, `range`,`ref`, `eq_ref`, `const`,`system`,`NULL`，一般来说最少也要到达`range`级别
    
     *  `ALL`:全表扫描，性能最差
     *  `index`：Full Index Scan，index与ALL区别为index类型只遍历索引树
     *  `range`：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有`between`或者`where`子句里带有<,>查询。当MySQL使用索引去查找一系列值时，例如`IN()`和`OR`列表，也会显示`range`（范围扫描）,当然性能上面是有差异的。
     *  `ref`:使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行（等值查询）
     *  `eq_ref`:类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用`primary key`或者`unique key`作为关联条件
     *  `const`:该表最多只有一个匹配行，在查询开头读取。因为只有一行，所以优化器的其余部分可以将此行中列的值视为常量。 const表非常快，因为它们只读一次。
     *  `system`:该表只有一行（=系统表）。这是const连接类型的特例 。
     *  `NULL`：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，比如利用主键查找一个不存在的值，直接返回NULL，这样就没有产生磁盘io性能非常好。侧面说明。。。数据库不用的时候，性能最好。
 *  possible\_keys  
    该possible\_keys列指示MySQL可以从中选择查找此表中的行的索引
 *  key  
    该key列指示MySQL实际决定使用的索引
 *  key\_len  
    该key\_len列指示MySQL决定使用的索引的长度
 *  ref  
    该ref列显示将哪些列或常量与列中指定的索引进行比较
 *  rows  
    该rows列指示MySQL认为必须检查以执行查询的行数
 *  filtered  
    该filtered列指示将按表条件过滤的表行的估计百分比
 *  Extra

Using temporary
    Using filesort  --文件排序，索引已经生效还要做排序操作`
    Using join buffer

如果出现以上附加信息：  
请检查`order by` ,`group by` ,`distinct`,`join` 条件列上是否有索引  
如果排序的列有索引但是在`where`子句中没有用到这个索引，那么实际上会产生`Using filesort`，也就是使用了文件排序

> key\_len 越小越好 可以用前缀  
> rows 越小越好 返回行越少越好

参考文档

*  [https://zhuanlan.zhihu.com/p/27700617][https_zhuanlan.zhihu.com_p_27700617]
 *  [https://dev.mysql.com/doc/refman/5.7/en/explain-output.html][https_dev.mysql.com_doc_refman_5.7_en_explain-output.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70]: /images/20220321/eadfa59356f4462092e621c0820d1794.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 1]: /images/20220321/cd9f361abb3e434dafa6ae131382715d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 2]: /images/20220321/effb82135a074b0499764294f2d84ca0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 3]: /images/20220321/cfdf3e62639f44bdbc468da8dbb980a9.png
[https_zhuanlan.zhihu.com_p_27700617]: https://zhuanlan.zhihu.com/p/27700617
[https_dev.mysql.com_doc_refman_5.7_en_explain-output.html]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html