MYSQL 联合索引 | 网游世界

好好学习，天天向上；知道 MYSQL 联合索引概念，但实际应用起来却一头雾水。
感谢，面试我的朋友，给我指点。

本文按 https://www.jianshu.com/p/499cf5795de5 复刻而来，MYSQL 版本为 5.6.17-log。

0、前期准备

本地 MYSQL版本为 5.6.17-log。

mysql创建一张表，表名：yjl_test_index

id列为主键，int类型，自增
a,b,c,d,e 全部是int（11）
为（a,b,c）添加一个联合索引 index_abc，索引方法为 BTREE

CREATE TABLE `yjl_test_index` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) unsigned DEFAULT '0',
  `b` int(11) unsigned DEFAULT '0',
  `c` int(11) unsigned DEFAULT '0',
  `d` int(11) unsigned DEFAULT '0',
  `e` int(11) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_abc` (`a`,`b`,`c`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1、写入测试数据

以下为 TP3.2 代码：

$model = M('test_index');
$ins = [];
$max = 100*10000; // 100 万条记录
for ($i = 1; $i<1000000; $i++) {
    $data = [
        'a' => mt_rand(0, 1000),
        'b' => mt_rand(0, 10000),
        'c' => mt_rand(0, 100000),
        'd' => mt_rand(0, 1000),
        'e' => mt_rand(0, 1000),
        ];
    $ins[] = $data;


    if ($i % 10000 == 0) {
        $model->addAll($ins);
        $ins = [];
    }
}

if (!empty($ins)) $model->addAll($ins);

2、索引测试

AND AND 只要用到了最左侧a列，和顺序无关都会使用索引

#使用索引
EXPLAIN SELECT * from yjl_test_index where a = 1 AND b = 2 AND c = 3; 
#使用索引
EXPLAIN SELECT * from yjl_test_index where c = 1 AND b = 2 AND a = 3; 
#使用索引
EXPLAIN SELECT * from yjl_test_index where a = 1 AND b = 2;
#使用索引
EXPLAIN SELECT * from yjl_test_index where a = 1 AND c = 3;
#使用索引
EXPLAIN SELECT * from yjl_test_index where c = 1 AND a = 2;

不包含最左侧的 a 的不使用索引

# 未使用索引
EXPLAIN SELECT * from yjl_test_index where c = 3;
# 未使用索引
EXPLAIN SELECT * from yjl_test_index where b = 2; 
# 未使用索引
EXPLAIN SELECT * from yjl_test_index where b = 2 AND c = 3;
# 未使用索引
EXPLAIN SELECT * from yjl_test_index where c = 1 AND b = 2;

OR 不使用索引

# 未使用索引
EXPLAIN SELECT * from yjl_test_index where a = 1 AND b = 2 OR c = 3;
# 未使用索引
EXPLAIN SELECT * from yjl_test_index where a = 1 OR b = 2 AND c = 3;
# 未使用索引
EXPLAIN SELECT * from yjl_test_index where a = 1 OR b = 2 OR c = 3;

最左侧的‘a’列被大于，小于，不等于比较的，不使用索引

# 未使用索引
EXPLAIN SELECT * from yjl_test_index where a > 1 AND b = 2 AND c = 3;
# 未使用索引
EXPLAIN SELECT * from yjl_test_index where a > 1;
# 未使用索引
EXPLAIN SELECT * from yjl_test_index where a <> 1 AND b = 2 AND c = 3;

特殊情况，因为最左原则的 a 字段，库中最大值为 1000，小于 155 时，也走索引

# 使用索引
EXPLAIN SELECT * from yjl_test_index where a < 155 AND b=2 AND c=3;
# 不使用索引
EXPLAIN SELECT * from yjl_test_index where a < 200 AND b=2 AND c=3;
# 不使用索引
EXPLAIN SELECT * from yjl_test_index where a > 155;

引自：
mysql 联合索引复合索引（abc）如何索引命中规则实测

相关文章：