ITPub博客

首页 > 数据库 > MySQL > MySQL多列索引实验

MySQL多列索引实验

原创 MySQL 作者:StevenBeijing 时间:2018-06-20 15:50:22 0 删除 编辑

    针对此问题进行测试:
假设某个表有一个联合索引(c1,c2,c3,c4)一下___只能使用该联合索引的c1,c2,c3部分
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=x and c5=x order by c2,c3
E where c1=x and c2=x and c5=? order by c2,c
1、创建测试表

点击( 此处 )折叠或打开

  1. mysql > show create table t1 \G

  2. * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *

  3.         Table : t1

  4. Create Table : CREATE TABLE `t1` (

  5.    `c1` mediumint ( 8 ) unsigned NOT NULL DEFAULT '0' ,

  6.    `c2` smallint ( 5 ) unsigned NOT NULL DEFAULT '0' ,

  7.    `c3` int ( 10 ) unsigned NOT NULL DEFAULT '0' ,

  8.    `c4` int ( 10 ) unsigned NOT NULL DEFAULT '0' ,

  9.    `c5` mediumint ( 8 ) unsigned NOT NULL ,

  10.    `c6` varchar ( 2 ) DEFAULT NULL ,

  11.   KEY `idx_t1_c1_c2_c3_c4` ( `c1` , `c2` , `c3` , `c4` )

  12. ) ENGINE = InnoDB DEFAULT CHARSET = utf8

2、选项A执行计划

点击( 此处 )折叠或打开

  1. mysql > explain select * from t1 where c1 = 100 and c2 = 2 and c4 > 1000 and c3 = 1419401948 \G

  2. * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *

  3.            id: 1

  4.   select_type: SIMPLE

  5.          table : t1

  6.    partitions: NULL

  7.          type: range

  8. possible_keys: idx_t1_c1_c2_c3_c4

  9.           key: idx_t1_c1_c2_c3_c4

  10.       key_len: 13

  11.           ref: NULL

  12.          rows: 1

  13.      filtered: 100 . 00

  14.         Extra: Using index condition

3、选项B执行计划

点击( 此处 )折叠或打开

  1. mysql > explain select * from t1 where c1 = 100 and c2 = 2 and c4 = 1419317673 order by c3 \G

  2. * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *

  3.            id: 1

  4.   select_type: SIMPLE

  5.          table : t1

  6.    partitions: NULL

  7.          type: ref

  8. possible_keys: idx_t1_c1_c2_c3_c4

  9.           key: idx_t1_c1_c2_c3_c4

  10.       key_len: 5

  11.           ref: const , const

  12.          rows: 1

  13.      filtered: 10 . 00

  14.         Extra: Using index condition

4、选项C执行计划

点击( 此处 )折叠或打开

  1. mysql > explain select * from t1 where c1 = 100 and c4 = 1419317673 group by c3 , c2 \G

  2. * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *

  3.            id: 1

  4.   select_type: SIMPLE

  5.          table : t1

  6.    partitions: NULL

  7.          type: ref

  8. possible_keys: idx_t1_c1_c2_c3_c4

  9.           key: idx_t1_c1_c2_c3_c4

  10.       key_len: 3

  11.           ref: const

  12.          rows: 1

  13.      filtered: 10 . 00

  14.         Extra: Using index condition ; Using temporary ; Using filesort



5、选项D执行计划

点击( 此处 )折叠或打开

  1. mysql > explain select * from t1 where c1 = 100 and c5 = 2 order by c2 , c3 \G

  2. * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *

  3.            id: 1

  4.   select_type: SIMPLE

  5.          table : t1

  6.    partitions: NULL

  7.          type: ref

  8. possible_keys: idx_t1_c1_c2_c3_c4

  9.           key: idx_t1_c1_c2_c3_c4

  10.       key_len: 3

  11.           ref: const

  12.          rows: 1

  13.      filtered: 10 . 00

  14.         Extra: Using index condition ; Using where

6、选项E执行计划

点击( 此处 )折叠或打开

  1. mysql > explain select * from t1 where c1 = 1000 and c2 = 200 and c5 = 2 order by c2 , c3 \G

  2. * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *

  3.            id: 1

  4.   select_type: SIMPLE

  5.          table : t1

  6.    partitions: NULL

  7.          type: ref

  8. possible_keys: idx_t1_c1_c2_c3_c4

  9.           key: idx_t1_c1_c2_c3_c4

  10.       key_len: 5

  11.           ref: const , const

  12.          rows: 1

  13.      filtered: 10 . 00

  14.         Extra: Using index condition ; Using where


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30135314/viewspace-2156380/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-02-10

  • 博文量
    200
  • 访问量
    196222