mysql索引测试案例

蔚落 2022-03-18 13:28 91阅读 0赞

author：skate  
time:2013/04/10

**mysql索引测试案例**

实验条件：

mysql> show create table users\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: users  
Create Table: CREATE TABLE \`users\` (  
  \`id\` int(11) NOT NULL,  
  \`name\` varchar(45) DEFAULT NULL,  
  \`c1\` text,  
  \`c2\` int(6) unsigned zerofill DEFAULT NULL,  
  PRIMARY KEY (\`id\`),  
  UNIQUE KEY \`c2\_UNIQUE\` (\`c2\`),  
  KEY \`idx\_users\` (\`c1\`(10))  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
1 row in set (0.00 sec)

ERROR:  
No query specified

mysql> show create table user\_action\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: user\_action  
Create Table: CREATE TABLE \`user\_action\` (  
  \`user\_id\` int(11) NOT NULL,  
  \`action\` varchar(45) DEFAULT NULL,  
  \`name\` varchar(100) DEFAULT NULL,  
  PRIMARY KEY (\`user\_id\`),  
  UNIQUE KEY \`idx\_userid\` (\`user\_id\`),  
  KEY \`idx\_action\` (\`action\`),  
  KEY \`idx\_useraction\_action\_name\` (\`action\`,\`name\`(10))  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
1 row in set (0.00 sec)

ERROR:  
No query specified

mysql> select \* from users;  
\+----+--------+------+--------+  
| id | name   | c1   | c2     |  
\+----+--------+------+--------+  
|  1 | libk   | NULL |   NULL |  
|  2 | zyfon  | NULL |   NULL |  
|  3 | daodao | NULL |   NULL |  
|  4 | 3333   | NULL | 000002 |  
|  5 | 444    | NULL |   NULL |  
\+----+--------+------+--------+  
5 rows in set (0.00 sec)

mysql> select \* from user\_action;  
\+---------+--------+---------------+  
| user\_id | action | name          |  
\+---------+--------+---------------+  
|       1 | jump   | aaaaaaaaaaaaa |  
|       2 | run    | bbbbbbbbbbb   |  
|       4 | swim   | cccccc        |  
|       6 | kick   | dd            |  
|      15 | jump1  | fff           |  
\+---------+--------+---------------+  
5 rows in set (0.00 sec)

mysql>

从type=ALL可以知道，使用了全表扫描

mysql选择执行路径使用的是和oracle类似的基于cost，只不过mysql没有oracle做的那么强悍

从上面的type=ref和“Using where”，可以看出，mysql先ref的方式找到索引，再通过索引回表找到数据

**说明：**  
ref：当通过键值读数据时，键不是UNIQUE或PRIMARY KEY（换句话说，如果不能基于关键字选择单个行的话），则使用ref  
Using where：提示mysql用where过滤结果集

从上面可以看到“Using where; Using index”，说明是通过全索引扫描获得数据并过滤结果集，不用回表取数据，使用了覆盖索引扫描

上面这个例子中，也使用了覆盖索引扫描，但idx\_action所以里没有user\_id字段，这是什么原因？因为idx\_action是辅助索引，它要通过主键才能找到数据，而user\_id是主键索引。所以也不用回表就可以得到所需数据。所以要想使用覆盖索引，就不要用"select \*"

上面添加了一个字段a1，然后把a1放到select列表里，再看执行计划，发现只有“Using where”,说明需要回表取数据了。

从上面可以看到type=index和“Using index”,说明只扫描了索引，使用的覆盖索引扫描，没有回表就把排序好的数据取出来

这个type=ALL和“Using filesort”可以知道使用了全表扫描，然后对结果集排序

上一个sql排序因为没有使用索引，所以需要排序（Using filesort）,而这个是使用了主键，为什么还需要排序，因为需要的顺序和主键的顺序不一样。

说明  
mysql的排序有两种：index和filesort，index效率高，它指MySQL扫描索引本身而完成排序。FileSort方式效率较低，因为没有利用索引取到数据，需要mysql通过将取得的数据在内存中进行排序然后再将数据返回给客户。

mysql中filesort 的实现算法实际上是有两种的：  
  1) 在mysql4.1版本之前只有一种排序算法，首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer中进行排序，然后再回表取需要

的数据,这里需要二次回表，速度会慢些  
   
  2) 从mysql4.1开始,改进了第一种排序算法，一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。目的是为了减少第一次算法中需要二次回表的IO操作，将两

次变成了一次，但相应也会耗用更多的sort buffer空间。

mysql4.1开始以后所有版本同时支持两种算法，mysql主要通过比较我们所设定的系统参数max\_length\_for\_sort\_data 的大小和“select语句**所取出**的字段类型大小总和”，来判定需要使用哪一种排序算法。如果max\_length\_for\_sort\_data 更大，则使用第二种优化后的算法，否则使用第一种算法。如果希望ORDER BY操作的效率尽可能的高，一定要注意max\_length\_for\_sort\_data 参数的设置。

这个sql使用了索引的排序，然后回表取数据得到结果集，没有对查询结果排序

这个sql也使用了索引“idx\_action”，为什么就需要对结果集排序呢？因为a1即不再索引idx\_action里，也不是主键；所以需要对结果集排序，要使用filesort，可以通过增大max\_length\_for\_sort\_data来优化filesort。或者就避免filesort

对索引列同时使用了ASC和DESC时，需要使用filesort

虽然也对索引列同时使用了ASC和DESC，但是通过where语句（主键）将order by中索引列转为常量，则直接根据索引顺序回表读数据

where语句与order by语句使用了不同的索引也是需要排序的，where使用idx\_action，order by使用了idx\_useraction\_action\_name

**总上面使用filesort的情况：**  
 1) 查询的行数过多，优化器认为要全表扫描，且没有使用覆盖索引  
 2) 对索引列同时使用了ASC和DESC时，但是通过where语句（主键）将order by中索引列转为常量例外  
 3) where语句与order by语句使用了不同的索引也是需要排序的  
 4) order by子句中加入了非索引列,且非索引列不在where子句中  
 5) ORDER BY语句中索引列使用了表达式

\------end------