mysql联合索引分析测试
16lz
2021-01-22
小数据量(2W)条,走联合索引大概快10倍左右。
#联合索引
#和查询的顺序无关!MYSQL会自动重新排列
小数据量,效果明显。
#联合索引在单个查询和联合查询时速度比较如下:
#单个查询结果:2692条,用时0.117s
SELECT * FROM vlc_caomei_state
WHERE
city = '济南市'
AND
id < 1500000
order by region
#联合查询结果:2692条,用时0.017s
SELECT * FROM vlc_caomei_state
WHERE
city = '济南市'
AND
region='山东省'
AND
id < 1500000
order by region
大数据量查询,效果不明显,速度基本相同
#单个查询结果: 12w条,用时26 s
SELECT * FROM vlc_caomei_state
WHERE
city = '济南市'
order by region
#联合查询结果:12w 条,用时21 s
SELECT * FROM vlc_caomei_state
WHERE
city = '济南市'
AND
region='山东省'
order by region
#limit效果
mysql> explain SELECT * FROM vlc_caomei_state
-> WHERE
-> city = '济南市'
-> order by region
-> limit 0,10;
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | vlc_caomei_state | index | NULL | regions | 306 | NULL | 10 | Using where |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT * FROM vlc_caomei_state
-> WHERE
-> city = '济南市'
-> AND
-> region='山东省'
-> order by region
-> limit 0,10;
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
| 1 | SIMPLE | vlc_caomei_state | ref | regions | regions | 306 | const,const | 263458 | Using index condition |
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
1 row in set (0.07 sec)
mysql> explain SELECT * FROM vlc_caomei_state
-> WHERE
-> city = '济南市'
-> order by region ;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-----------------------------+
| 1 | SIMPLE | vlc_caomei_state | ALL | NULL | NULL | NULL | NULL | 3918061 | Using where; Using filesort |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)
mysql> explain SELECT * FROM vlc_caomei_state
-> WHERE
-> city = '济南市'
-> AND
-> region='山东省'
-> order by region ;
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
| 1 | SIMPLE | vlc_caomei_state | ref | regions | regions | 306 | const,const | 263458 | Using index condition |
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
1 row in set (0.42 sec)
总结,大数据量,索引不明显。
http://bbs.csdn.net/topics/390747126
更多相关文章
- 字体图标的引入和通过媒体查询改变导航样式
- HTML样式和常用选择器
- 字体图标的引用和自定义样式/媒体查询的使用
- 数据库的CURD操作、PDO本质与原理的学习
- CSS之伪类选择器和简单盒子简单案例
- 伪类选择器与盒模型常用属性
- 伪类选择器-结构伪类、根据位置选择匹配
- 7.4——常用标签与应用场景之表格与单元格
- css伪类选择器和盒模型