Mysql Multiple-Column Indexes(Mysql多重索引)

系统管理员 2023-01-07 10:15 145阅读 0赞

## 前言 ##

复习的灵感来自某天不小心划过跳到了[mysql官方文档][mysql]中，发现官方文档中其实对于`Multiple-Column Indexes` 也就是`多重索引` 中有所提及。

## 多列索引的定义 ##

> MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.  
> .  
> A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.  
> .

翻译过来就是：

MySQL可以将多列索引用于测试索引中所有列的查询，或者仅测试第一列，前两列，前三列等等的查询。 如果在索引定义中以正确的顺序指定列，则单个复合索引可以加快对同一表的几种查询。

多列索引可以被认为是排序数组，其行包含通过串联索引列的值而创建的值。

**总结就是**，mysql多列索引是顺序索引，假设有`index(a,b,c)`，也就是可以仅使用第一个`a`，或者`a+b`，或者`a+b+c` 。

## 顺序索引 ##

`假设`有这么一个常用表，字段为id、last\_name姓、first\_name名

CREATE TABLE test (
        id         INT NOT NULL,
        last_name  CHAR(30) NOT NULL,
        first_name CHAR(30) NOT NULL,
        PRIMARY KEY (id),
        INDEX name (last_name,first_name)
    );

现有`index（last_name、first_name）` ，该索引可用于查询中的where条件，既可以使用`where last_name+first_name`值组合的已知范围内的值。 它也可以用于仅指定`where last_name`值的查询，因为该列是索引的`最左前缀`（根据索引的顺序查询属性）。例如以下索引：

SELECT * FROM test WHERE last_name='Jones';
    
    SELECT * FROM test
      WHERE last_name='Jones' AND first_name='John';
    
    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';

## 拆分|合并索引 ##

SELECT * FROM tbl_name
      WHERE col1=val1 AND col2=val2 and col3=val3;

*  如果col1和col2和col3上存在多列索引，则可以直接获取适当的行，也就是上文说的顺序索引`index(col1,col2,col3)`。注意，index(col3,col2,col1)对该语句是无效的。另外，由于（col2）和（col2，col3）并不是（col1，col2，col3）的最左排序，所以也是无效的。此为`顺序索引`。
 *  如果col1和col2上存在单独的`单列索引`，则优化器将尝试使用`索引合并优化`，或通过确定哪个索引排除更多行来查找`限制性最强的索引`。 并使用该索引来获取行。此为`合并索引`。
 *  如果表具有`多列索引`，那么优化器可以使用索引的任何最左前缀来查找行。 例如有个三列索引 `index（col1，col2，col3）`，则在`（col1）`，`（col1，col2）`和`（col1，col2，col3）`上都有索引搜索功能。此为`拆分索引`。  
    \-如果索引存在于（col1，col2，col3）上，则仅前两个查询使用该索引。 第三和第四查询确实涉及索引列，但是不使用索引来执行查找，因为（col2）和（col2，col3）并不是（col1，col2，col3）的最左前缀。

[mysql]: https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html