MySQL - Mysql优化（出自官方文档）；索引优化系列）

短命女 2023-09-25 21:29 20阅读 0赞

#### 官网文档 ####

[MySQL :: MySQL 5.7 Reference Manual :: 8.3 Optimization and Indexes][MySQL _ MySQL 5.7 Reference Manual _ 8.3 Optimization and Indexes]

### Mysql优化（出自官方文档） - 第八篇（索引优化系列） ###

*  [Mysql优化（出自官方文档） - 第八篇（索引优化系列）][Mysql_ -]
    
     *  [Optimization and Indexes][]
     *  [1 Foreign Key Optimization][]
     *  [2 Column Indexes][]
        
         *  [Indexes in the MEMORY Storage Engine][]
     *  [3 Column Indexes && Multiple-Column Indexes][3 Column Indexes _ Multiple-Column Indexes]
     *  [4 Comparison of B-Tree and Hash Indexes][]
        
         *  [B-Tree Index Characteristics][]
         *  [Hash Index Characteristics][]
     *  [5 Use of Index Extensions][]
     *  [6 Invisible Indexes][]
     *  [7 Descending Indexes][]

#### **Optimization and Indexes** ####

正确的创建索引往往会加快查询速度，但是，没有必要的索引往往只会浪费空间，并且增加插入，更新和删除的开销，因为进行这些操作要同时更新索引。

但是，索引并不是万能的，在下面的几个场景中，索引将会显得不是那么有用：

*  小表，或者大表，但是需要请求所有行
 *  当一个请求需要表中的大部分行时，连续读往往效率会比索引高，因为连续读会减少磁盘seek的时间。

#### **1 Foreign Key Optimization** ####

如果经常读取一个表中的多个列，那么，把最少访问的列分出来单独作为一个小表，然后使用外键的方式和主表联系起来，这样子在读取数据的时候就能尽量减少磁盘的I/O读写。

#### **2 Column Indexes** ####

*  **Index Prefixes**
    
    Mysql允许只使用一些字段的一部分来作为索引，这其中包括所有的`TEXT`和`BLOB`字段，如下面的例子：
    
        CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
    
    **需要注意的是，对于`CHAR, VARCHAR, TEXT`类型的字段，长度为*字符个数*的意思，但是对于非字符串字段，如`BINARY, VARBINARY, BLOB`字段，长度为*字节个数*的意思。**
 *  **FULLTEXT Indexes**
    
    该索引适用于[CHAR][], [VARCHAR][CHAR], and [TEXT][]的列，并且全文索引不支持前缀，只支持全列的索引。当查询符合如下特征的时候，全文索引将会非常有用：
    
     *  `FULLTEXT`查询语句值需要`document ID`或者`search rank`（分级？？）
     *  `FULLTEXT`查询语句对查询到的结果进行一个降序排序，并且有一个`LIMIT`语句选取`top N`项，这种情况下为了使用优化必须保证查询语句没有`WHERE`且`ORDER BY`只有一列。
     *  `FULLTEXT`查询只获取`COUNT(*)`的结果，且没有`WHERE`语句，如果需要`WHERE`过滤，请将`WHERE`写为`WHERE MATCH(TEXT) AGAINST('other_text')`，并且没有任何`> 0`的比较操作符。
 *  Indexes in the MEMORY Storage Engine
    
    对于内存存储引擎，默认使用`HASH`索引，但是同样也支持`BTREE`索引。

#### **3 Column Indexes && Multiple-Column Indexes** ####

Mysql支持单独的一列作为索引，也可以使用多列作为索引，当使用多列的时候，使用最左边（leftmost prefix of the index）的列进行查询，可以用到索引，否则，Mysql将无法使用索引，举例如下：

假设一个表含有下面的索引：

INDEX name (last_name,first_name)

对于下面的查询语句都可以使用到name索引：

SELECT * FROM test WHERE last_name='Jones';
    
    SELECT * FROM test
      WHERE last_name='Jones'
      AND (first_name='John' OR first_name='Jon');
      
    SELECT * FROM test
      WHERE last_name='Jones'
      AND first_name >='M' AND first_name < 'N';

下面的语句因为没有使用最左边的列进行查询，所以无法使用索引：

SELECT * FROM test WHERE first_name='John';
    
    SELECT * FROM test
      WHERE last_name='Jones' OR first_name='John';

假设有两列`col1`和`col2`，如果索引刚好建在`col1`和`col2`上，那么可以直接使用索引，但是如果`col1`和`col2`是单独的索引（即不是多列索引），那么Mysql会尝试使用 `Index Merge optimization` (see [Section 8.2.1.3, “Index Merge Optimization”][Section 8.2.1.3_ _Index Merge Optimization])技术，或者判断两个索引中哪个的条件是最严苛的，能够排除尽可能的行，在决定使用哪个索引。

#### **4 Comparison of B-Tree and Hash Indexes** ####

*  B-Tree Index Characteristics
    
    B树索引支持 [=][Link 1], [>][Link 2], [>=][Link 3], [<][Link 4], [<=][Link 5], or [BETWEEN][]操作符，也支持`LIKE`操作符，但是必须保证`LIKE`字段不是以通配符开头的。
    
    如果一个索引没有覆盖所有的`AND`，那么Mysql将无法使用该索引，换句话说，为了能够使用到该索引，必须保证一个索引的前缀（最左边部分）出现在每一个`AND`中，举例如下：
    
        ... WHERE index_part1=1 AND index_part2=2 AND other_column=3
        
            /* index = 1 OR index = 2 */
        ... WHERE index=1 OR A=10 AND index=2
        
            /* optimized like "index_part1='hello'" */
        ... WHERE index_part1='hello' AND index_part3=5
        
            /* Can use index on index1 but not on index2 or index3 */
        ... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
    
    下面的语句将无法使用索引：
    
        /* index_part1 is not used */
        ... WHERE index_part2=1 AND index_part3=2
        
            /*  Index is not used in both parts of the WHERE clause  */
        ... WHERE index=1 OR A=10
        
            /* No index spans all rows  */
        ... WHERE index_part1=1 OR index_part2=10
    
    （这里不是很懂？）
    
    需要注意的是：
    
    Mysql并不会任何时候都选择使用索引，有的时候，当需要读取特别多的行时，使用索引的效率可能要低于`table scan`的效率，因为索引会导致磁盘出现更多的寻道消耗，而`table scan`由于是连续读，则能更大效率的利用磁盘I/O
 *  Hash Index Characteristics
    
    `Hash`索引相对比于`B`树索引，可能会出现下面的不同：
    
     *  `Hash`索引只能用于`=`或者`<=>`操作符，不能用于范围查询
     *  优化器无法使用`Hash`索引来加速`ORDER BY`操作
     *  `Mysql`无法评估两个`value`之间有多少行（`BETWEEN`语句）
     *  只有完整的索引才能正常工作，不能像`BTREE`那样，使用`leftmost prefix key`

#### **5 Use of Index Extensions** ####

InnoDB会自动给二级索引增加primary key作为新的索引，这是在内部完成的，用户无法感知，比如下面的表：

CREATE TABLE t1 (
      i1 INT NOT NULL DEFAULT 0,
      i2 INT NOT NULL DEFAULT 0,
      d DATE DEFAULT NULL,
      PRIMARY KEY (i1, i2),
      INDEX k_d (d)
    ) ENGINE = InnoDB;

此时，对于索引k\_d，其内部真正的实现方式为： `(d, i1, i2)`. 即自动将primary key(i1, i2)添加到k\_d后面构成新的二级索引。

这种优化方式，可用于`ref`， `range`和`index_merge`的索引访问场景，`Loose Index Scan`，`join`和`sort`，以及`MIN()/MAX()。`

比如下面的语句：

SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

此时由于内部将`i1`添加到了`k_d`索引，那么这条语句就可以使用到`(d, i1)`这样的索引（`(d, i1)`是索引`(d, i1, i2)`的最左边前缀），由于使用到了更多的列，所以需要扫描的行数将大大减少。

#### **6 Invisible Indexes** ####

这种索引指的是那种真实存在，但是优化器却不会使用的索引，只能对非`primary key`进行该种设定，其他类型的索引均支持设置为`INVISIBLE`。

例如下面的例子：

CREATE TABLE t1 (
      i INT,
      j INT,
      k INT,
      INDEX i_idx (i) INVISIBLE
    ) ENGINE = InnoDB;
    CREATE INDEX j_idx ON t1 (j) INVISIBLE;
    ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

这种索引一般的用途：测试移除一个索引对于性能的影响，因为这种索引不是真正的将其移除了，只是优化器不再考虑该索引，所以并不会对表造成太大的影响。

注意虽然索引是`Invisible`的，但是这种索引和普通索引一样具有完备的功能，也就是说增删改的时候，Mysql同样会更新`Invisible Index`，同理，如果在一个非常大的表上创建`Invisible Index`时，和普通索引一样，开销也是非常巨大的。

#### **7 Descending Indexes** ####

Mysql支持降序索引，不像以前的版本，索引定义里面的DESC不再被忽略，内部对于索引的存储方式也采取降序的方式；而在此之前，Mysql是采用对index进行逆序扫描的方式，当前采用降序的方式效率比以前要高很多。

使用降序索引主要针对下面的场景：

*  降序索引只支持InnoDB引擎，并且有如下限制：
    
     *  如果一个二级索引有降序key或者说一个主键包含降序key，那么Mysql无法支持修改Buffering
     *  InnoDB的SQL解析器不会使用降序索引，对于full-text查询，这意味着已经被索引的表上面的`FTS_DOC_ID` 列不能被定义为降序索引。
 *  一个含有降序key的索引无法使用MIN/MAX优化，特指那种含有聚合函数但是却不带GROUP BY的语句。
 *  降序索引只支持BTREE，并不支持HASH索引，降序索引无法支持FULLTEXT或者SPATIAL索引

[MySQL _ MySQL 5.7 Reference Manual _ 8.3 Optimization and Indexes]: https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html
[Mysql_ -]: https://www.cnblogs.com/seancheer/p/11382991.html#mysql%E4%BC%98%E5%8C%96%E5%87%BA%E8%87%AA%E5%AE%98%E6%96%B9%E6%96%87%E6%A1%A3---%E7%AC%AC%E5%85%AB%E7%AF%87%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96%E7%B3%BB%E5%88%97
[Optimization and Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#optimization-and-indexes
[1 Foreign Key Optimization]: https://www.cnblogs.com/seancheer/p/11382991.html#1-foreign-key-optimization
[2 Column Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#2-column-indexes
[Indexes in the MEMORY Storage Engine]: https://www.cnblogs.com/seancheer/p/11382991.html#indexes-in-the-memory-storage-engine
[3 Column Indexes _ Multiple-Column Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#3-column-indexes--multiple-column-indexes
[4 Comparison of B-Tree and Hash Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#4-comparison-of-b-tree-and-hash-indexes
[B-Tree Index Characteristics]: https://www.cnblogs.com/seancheer/p/11382991.html#b-tree-index-characteristics
[Hash Index Characteristics]: https://www.cnblogs.com/seancheer/p/11382991.html#hash-index-characteristics
[5 Use of Index Extensions]: https://www.cnblogs.com/seancheer/p/11382991.html#5-use-of-index-extensions
[6 Invisible Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#6-invisible-indexes
[7 Descending Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#7-descending-indexes
[CHAR]: https://dev.mysql.com/doc/refman/8.0/en/char.html
[TEXT]: https://dev.mysql.com/doc/refman/8.0/en/blob.html
[Section 8.2.1.3_ _Index Merge Optimization]: https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html
[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal
[Link 2]: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_greater-than
[Link 3]: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_greater-than-or-equal
[Link 4]: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_less-than
[Link 5]: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_less-than-or-equal
[BETWEEN]: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_between