189 8069 5689

MySQL多列索引怎么用

这篇文章将为大家详细讲解有关MySQL多列索引怎么用，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

网站建设哪家好，找成都创新互联！专注于网页设计、网站建设、微信开发、成都微信小程序、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了电白免费建站欢迎大家使用！

    针对此问题进行测试：
假设某个表有一个联合索引（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

关于“MySQL多列索引怎么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。


分享名称：MySQL多列索引怎么用
本文地址：http://cdxtjz.com/article/iiioss.html

其他资讯