mysql索引测试案例

最新推荐文章于 2024-09-01 14:33:45 发布

skate

最新推荐文章于 2024-09-01 14:33:45 发布

阅读量3.7k

点赞数

分类专栏： Mysql Optimization

本文链接：https://blog.csdn.net/wyzxg/article/details/8784582

版权

Mysql Optimization 专栏收录该内容

12 篇文章 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------