一.索引：
索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。
对于索引，会保存在额外的文件中
1.1. 创建一个索引:
mysql> create index ix_class on tb3(class_id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
1.2. 删除一个索引:
mysql> drop index ix_class on tb3;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
二 . 索引种类
1.普通索引 --- 加速查找
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.02 sec)
mysql> use db1;
Database changed
mysql> create table t11(
-> nid int not null auto_increment primary key,
-> name varchar(255),
-> emile varchar(255))engine=innodb default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> create index ix_name on t11(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t11;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t11 | 0 | PRIMARY | 1 | nid | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| t11 | 1 | ix_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
2.唯一索引 --- 加速查找，约束列数据不能重复，可以为null
create unique index ix_name on t11(name);
3.主键索引 --- 加速查找，约束列数据不能重复，不能为null
mysql> create table tb3(
-> nid int not null auto_increment,
-> class_id varchar(255),
-> name varchar(255),
-> unique ix_name (name),
-> primary key(nid))engine=innodb default charset='utf8';
Query OK, 0 rows affected, 1 warning (0.03 sec)
4.组合索引 --- 多列可以创建一个索引文件
<1>普通组合索引: 无约束
<2>联合组合索引: 有约束，两列数据同时不相同时才可以插入，否则报错
组合索引遵循最左匹配原则
三.覆盖索引 与 合并索引
1.覆盖索引
如果情况应用上索引，不用去数据表中操作 ---- 覆盖索引
即只需要在索引表中就能获取数据
而: select * from t1 where nid = 1;
(1)要先去索引中找
(2)再去数据表中找
2.合并索引
nid name(单独索引) email(单独索引)
select name from t1 where name='star';
select name from t1 where email='486075@qq.com';
select name from t1 where name='xiaozhang' or email='486075@qq.com';
判断业务需求，选择合并索引 还是 组合索引
三.mysql执行计划
mysql> select * from tb1;
+-----+------------+
| nid | class |
+-----+------------+
| 1 | 三年级二班 |
| 2 | 五年级 |
+-----+------------+
1. explain sql语句
1.1. ALL --- 全表扫描，对于数据表从头到尾找一遍
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 26 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
如果type值是ALL --- 表示要对数据表进行全部扫描
1.2 index --- 全索引扫描，对索引从头到尾找一遍
mysql> explain select class from tb1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb1 | NULL | index | NULL | ix_class | 1022 | NULL | 2 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
如果type值是index --- 表示要对索引表进行全部扫描
ALL 和 index 这两种的执行效率不高
1.1.limit
如果加上limit：
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select sname from student where sname='沙比';
select sname from student where sname='沙比' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描
1.3.range --- 对索引列进行范围查找
对索引列进行范围查找，要注意，如果条件为 > != 的情况下，不能应用到索引
1.3.1
mysql> explain select sname from student where sname<'张';
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | student | NULL | range | ix_sn | ix_sn | 98 | NULL | 11 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
1.4 index_merge --- 合并索引，使用多个单列索引搜索
mysql> explain select * from student where sid=5 or sname='刘二';
+----+-------------+---------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
| 1 | SIMPLE | student | NULL | index_merge | PRIMARY,ix_sn | PRIMARY,ix_sn | 4,98 | NULL | 2 | 100.00 | Using union(PRIMARY,ix_sn); Using where |
+----+-------------+---------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)
1.5 ref ---根据索引查找一个或多个值
eg:
mysql> explain select * from student where sname='star';
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | ix_sn | ix_sn | 98 | const | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
1.6 eq_ref --- 连接时使用primary key 或 unique类型
eg:
mysql> explain select student.class_id,class.caption from student left join class on student.sid=class.cid where student.class_id=1 and class.caption='队长';
+----+-------------+---------+------------+--------+------------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+------------------+---------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | PRIMARY | NULL | NULL | NULL | 8 | 12.50 | Using where |
| 1 | SIMPLE | student | NULL | eq_ref | PRIMARY,fk_class | PRIMARY | 4 | db1.class.cid | 1 | 34.62 | Using where |
+----+-------------+---------+------------+--------+------------------+---------+---------+---------------+------+----------+-------------+
1.7 const ---表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,
const表很快,因为它们只读取一次。
mysql> explain select sid from student where sid=8;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1.8 system ---系统
表仅有一行(为系统表)-----这是const联接类型的一个特例。
possible_keys ------可能使用的索引
key ----真实使用的
key_len --------mysql中使用索引字节长度
四.正确使用索引
1.用like的时候，% 在前不走索引
mysql> explain select * from student where sname like '%三';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 26 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from student where sname like '三%';
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | ix_sn | ix_sn | 98 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
2.sql语句中加函数不走索引
mysql> explain select * from student where substring(sname,1,4)='star';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 26 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
3.数据类型不一致，也会造成不走索引
mysql> explain select * from student where sname='star';
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | ix_sn | ix_sn | 98 | const | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from student where sname=555;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | ix_sn | NULL | NULL | NULL | 26 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
4. 条件是 != 与 > ，走不走索引
4.1-----如果是普通索引，!=的话，不走索引
eg:
mysql> explain select * from student where sname!='star';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | ix_sn | NULL | NULL | NULL | 26 | 96.15 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
4.2
mysql> desc tb3;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| nid | int | NO | PRI | NULL | auto_increment |
| class_id | varchar(255) | YES | | NULL | |
| name | varchar(255) | YES | UNI | NULL | |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
4.2.1 -----如果是唯一索引，!=的话，走索引
eg:
mysql> explain select * from tb3 where name!='star';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb3 | NULL | range | ix_name | ix_name | 768 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
4.2.2 -----如果是主键索引，!=的话，走索引
mysql> explain select * from tb3 where nid!=8;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb3 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
4.2.3 ----索引是整数类型还是会走索引
4.2.4 -----如果是主键索引，> 的话，走索引
eg:
mysql> explain select * from tb3 where nid>8;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb3 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
result:如果是主键索引，> 的话，走索引
4.2.5 -----如果是唯一索引，> 的话，走索引
eg:
mysql> explain select name from tb3 where name>'star';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tb3 | NULL | index | ix_name | ix_name | 768 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
result: 如果是唯一索引，> 的话，走索引
4.2.6.1 ----索引是整数类型，> 还是会走索引
# 给表tb3 添加一列 num ,且num为整型类型
mysql> alter table tb3 add num int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb3;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| nid | int | NO | PRI | NULL | auto_increment |
| class_id | varchar(255) | YES | | NULL | |
| name | varchar(255) | YES | UNI | NULL | |
| num | int | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
给列 num 添加索引
mysql> create index ix_num on tb3(num);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
eg:
mysql> explain select num from tb3 where num>15;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tb3 | NULL | index | ix_num | ix_num | 5 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
result: 索引是整数类型，> 还是会走索引
4.2.6.2 ---- ----索引是整数类型，!=还是会走索引
mysql> explain select num from tb3 where num!=15;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tb3 | NULL | index | ix_num | ix_num | 5 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
4 rows in set (0.00 sec)
result:索引是整数类型，!=还是会走索引
4.2.7 -----如果是普通索引，>的话，不走索引
如下，给tb3表新添加一列 parient 数据类型为 varchar
mysql> alter table tb3 add parient varchar(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from tb3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb3 | 0 | PRIMARY | 1 | nid | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| tb3 | 0 | ix_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb3 | 1 | ix_num | 1 | num | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)
mysql> desc tb3;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| nid | int | NO | PRI | NULL | auto_increment |
| class_id | varchar(255) | YES | | NULL | |
| name | varchar(255) | YES | UNI | NULL | |
| num | int | YES | MUL | NULL | |
| parient | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
parient 为普通列
mysql> explain select parient from tb3 where parient>'star';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb3 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
result: 如果是普通索引，> 的话，不走索引,type 为ALL
5.条件里有order by 走不走所引
mysql> explain select * from tb3 order by name desc;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | tb3 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
结论:不走索引
mysql> explain select name from tb3 order by name desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | tb3 | NULL | index | NULL | ix_name | 768 | NULL | 1 | 100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
结论:走索引
对于主键nid，*
mysql> explain select * from tb3 order by nid desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------+
| 1 | SIMPLE | tb3 | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Backward index scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)
结论:走索引
6.如果是三个列组成联合索引
--给tb3添加两列 child mom
mysql> desc tb3;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| nid | int | NO | PRI | NULL | auto_increment |
| class_id | varchar(255) | YES | | NULL | |
| name | varchar(255) | YES | UNI | NULL | |
| num | int | YES | MUL | NULL | |
| parient | varchar(255) | YES | | NULL | |
| child | varchar(255) | YES | | NULL | |
| mom | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
给 parient child mom 创建组合索引:
如下:
mysql> create index ix_pa_ch_mo on tb3(parient,child,mom);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb3;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| nid | int | NO | PRI | NULL | auto_increment |
| class_id | varchar(255) | YES | | NULL | |
| name | varchar(255) | YES | UNI | NULL | |
| num | int | YES | MUL | NULL | |
| parient | varchar(255) | YES | MUL | NULL | |
| child | varchar(255) | YES | | NULL | |
| mom | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
question:查找组合索引后面两个列时，是不是走索引
mysql> explain select child,mom from tb3 where child='star' and mom='mother';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tb3 | NULL | index | ix_pa_ch_mo | ix_pa_ch_mo | 2304 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
结论: 查找组合索引后面两个列时，走索引 <type ---->indx>