首页 / MYSQL / MySQL 联合索引测试
MySQL 联合索引测试
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 联合索引测试,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9600字,纯文字阅读大概需要14分钟。
内容图文
![MySQL 联合索引测试](/upload/InfoBanner/zyjiaocheng/1335/009c6df40c234eb18bf700e3da3016aa.jpg)
搭建测试环境
1:创建表
CREATE TABLE tab_index (id int(5), age int(3), dte datetime);
2:插入测试数据
INSERT INTO tab_index VALUES(1,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(2,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(3,‘2012-05-13‘,31); INSERT INTO tab_index VALUES(4,‘2012-05-13‘,32); INSERT INTO tab_index VALUES(5,‘2012-05-13‘,33); INSERT INTO tab_index VALUES(6,‘2012-06-13‘,34); INSERT INTO tab_index VALUES(7,‘2012-07-13‘,35); INSERT INTO tab_index VALUES(8,‘2012-08-13‘,36); INSERT INTO tab_index VALUES(9,‘2012-09-13‘,37); INSERT INTO tab_index VALUES(10,‘2012-05-17‘,38); INSERT INTO tab_index VALUES(11,‘2012-05-19‘,39); INSERT INTO tab_index VALUES(1,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(2,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(3,‘2012-05-13‘,31); INSERT INTO tab_index VALUES(4,‘2012-05-13‘,32); INSERT INTO tab_index VALUES(5,‘2012-05-13‘,33); INSERT INTO tab_index VALUES(6,‘2012-06-13‘,34); INSERT INTO tab_index VALUES(7,‘2012-07-13‘,35); INSERT INTO tab_index VALUES(8,‘2012-08-13‘,36); INSERT INTO tab_index VALUES(9,‘2012-09-13‘,37); INSERT INTO tab_index VALUES(10,‘2012-05-17‘,38); INSERT INTO tab_index VALUES(11,‘2012-05-19‘,39); INSERT INTO tab_index VALUES(1,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(2,‘2015-05-13‘,23); INSERT INTO tab_index VALUES(3,‘2012-05-13‘,31); INSERT INTO tab_index VALUES(4,‘2012-05-13‘,32); INSERT INTO tab_index VALUES(5,‘2012-05-13‘,33); INSERT INTO tab_index VALUES(6,‘2012-06-13‘,34); INSERT INTO tab_index VALUES(7,‘2013-07-13‘,35); INSERT INTO tab_index VALUES(8,‘2012-08-13‘,36); INSERT INTO tab_index VALUES(9,‘2012-09-13‘,37); INSERT INTO tab_index VALUES(10,‘2012-05-17‘,38); INSERT INTO tab_index VALUES(11,‘2011-05-19‘,39); INSERT INTO tab_index VALUES(1,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(2,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(3,‘2010-05-13‘,31); INSERT INTO tab_index VALUES(4,‘2012-05-13‘,32); INSERT INTO tab_index VALUES(5,‘2010-05-13‘,33); INSERT INTO tab_index VALUES(6,‘2010-06-13‘,34); INSERT INTO tab_index VALUES(7,‘2012-07-13‘,35); INSERT INTO tab_index VALUES(8,‘2012-08-13‘,36); INSERT INTO tab_index VALUES(9,‘2011-09-13‘,37); INSERT INTO tab_index VALUES(10,‘2012-05-17‘,38); INSERT INTO tab_index VALUES(11,‘2012-05-19‘,39); INSERT INTO tab_index VALUES(1,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(2,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(3,‘2012-05-13‘,31); INSERT INTO tab_index VALUES(4,‘2012-05-13‘,32); INSERT INTO tab_index VALUES(5,‘2012-05-13‘,33); INSERT INTO tab_index VALUES(6,‘2012-06-13‘,34); INSERT INTO tab_index VALUES(7,‘2014-07-13‘,35); INSERT INTO tab_index VALUES(8,‘2012-08-13‘,36); INSERT INTO tab_index VALUES(9,‘2011-09-13‘,37); INSERT INTO tab_index VALUES(10,‘2012-05-17‘,38); INSERT INTO tab_index VALUES(11,‘2012-05-19‘,39);
3:创建id和age上的联合索引
CREATE INDEX idx1 ON tab_index(id,age);
4:开始测试
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE id=3; +----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | tab_index | ref | idx1 | idx1 | 5 | const | 5 | Using where | +----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
id=3走索引,age=31不走索引。很容易理解
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE age=31; +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tab_index | ALL | NULL | NULL | NULL | NULL | 55 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE id=3 AND age=31; +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | tab_index | ref | idx1 | idx1 | 10 | const,const | 1 | Using where | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
id=3 AND age=31 和 age=31 AND id=3都走索引了,但是索引长度跟之前不同
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE age=31 AND id=3 ; +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | tab_index | ref | idx1 | idx1 | 10 | const,const | 1 | Using where | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE id=3 AND age=31 AND dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘; +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | tab_index | ref | idx1 | idx1 | 10 | const,const | 1 | Using where | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE id=3 AND dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘ AND age=31; +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | tab_index | ref | idx1 | idx1 | 10 | const,const | 1 | Using where | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘ AND id=3 AND age=31; +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | tab_index | ref | idx1 | idx1 | 10 | const,const | 1 | Using where | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE age=31 AND id=3 AND dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘; +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | tab_index | ref | idx1 | idx1 | 10 | const,const | 1 | Using where | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE age=31 AND dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘ AND id=3 ; +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | tab_index | ref | idx1 | idx1 | 10 | const,const | 1 | Using where | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id,dte,age -> FROM tab_index -> WHERE dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘ AND age=31 AND id=3 ; +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | tab_index | ref | idx1 | idx1 | 10 | const,const | 1 | Using where | +----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
另外其他几种情况,同学们自己尝试下以加深印象。
总结如下:
(id)走索引,且索引长度最短
(id,dte)走索引,且索引长度最短
(id,age)走索引,且索引长度最长
(id,age,dte)走索引,且索引长度最长
(id,dte,age)走索引,且索引长度最长
(dte,id,age)走索引,且索引长度最长
(dte,age,id)走索引,且索引长度最长
(age,dte,id)走索引,且索引长度最长
(age,id,dte)走索引,且索引长度最长
原文:http://www.cnblogs.com/xiaoit/p/4430300.html
内容总结
以上是互联网集市为您收集整理的MySQL 联合索引测试全部内容,希望文章能够帮你解决MySQL 联合索引测试所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。