004 mysql索引应用

step1 索引使用场景

一、需创建索引的场景：

1. 主键自动建立唯一索引；
2. 频繁作为查询条件的字段应该创建为索引；
3. 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引；
4. 查询中排序的字段，应该创建索引， B + tree 有顺序，其插入就是以小到大， 取出的时候依据排序的字段查出就不用在排序了；
5. 覆盖索引，应用覆盖缩影不需要回表,通过组合索引来创建覆盖索引；
6. 统计或者分组字段，应该创建索引；

二、不需要创建索引：

1. 表记录少，不需要创建，索引有存储的开销；
2. 频繁更新，不创建，索引要维护；
3. 查询字段使用频率不高；

step2 组合索引

        由多个字段组成的索引，即为组合索引，使用顺序就是创建的顺序；

ALTER TABLE 'table_name' ADD INDEX index_name(col1,col2,col3）

 

在一棵树上有多个字段，省空间(如果建立单索引，就需要建立三棵树)、容易形成覆盖索引；

组合索引的使用需要遵循最左前缀原则：

• 前缀索引

  like 常量% 使用索引，like %常量不使用索引；

• 最左前缀

  从左向右匹配直到遇到范围查询 ‘< > between’，遇到后索引就断掉；

示例：

#查看t1表目前只有一个主键索引
mysql>show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comx_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+--------------+
| t1    |          0 | PRIMARY  |            1 | id          | A         |           1 |     NULL | NULL   |      | BTREE      |              |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+--------------+
1 row in set (0.00 sec)

#表结构
mysql> desc t1;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| a     | int(11) | YES  |     | NULL    |                |
| b     | int(11) | YES  |     | NULL    |                |
| c     | int(11) | YES  |     | NULL    |                |
| d     | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

#创建组合索引idx_a_b_c_d
mysql> alter table t1 add index idx_a_b_c_d(a,b,c,d);
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查看组合索引
mysql> show index from t1;
+-------+------------+-------------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | id | A |
1 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx_a_b_c_d | 1 | a | A |
1 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | idx_a_b_c_d | 2 | b | A |
1 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | idx_a_b_c_d | 3 | c | A |
1 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | idx_a_b_c_d | 4 | d | A |
1 | NULL | NULL | YES | BTREE | | |
+-------+------------+-------------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

idx_a_b_c_d组合索引的形态：

 

#应用explain+sql语句查看索引应用情况
mysql> explain select * from t1 where a=1 and b=1 and c=1 and d=1 ;
+----+-------------+-------+------+---------------+-------------+---------+-----
--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-----
--------------------+------+-------------+
| 1 | SIMPLE | t1 | ref | idx_a_b_c_d | idx_a_b_c_d | 20 |
const,const,const,const | 1 | Using index |
+----+-------------+-------+------+---------------+-------------+---------+-----
--------------------+------+-------------+
1 row in set (0.00 sec)
​

 

 

type = ref 代表应用索引；

key_len=20 索引长度为20(组合索引有abcd四个组成，所以每个为5);

ref=const,const,const,const 共四个；

#举例索引断掉c>1
mysql> explain select * from t1 where a=1 and b=1 and c>1 and d=1 ;
+----+-------------+-------+------+---------------+-------------+---------+-----
--------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-----
--------+------+--------------------------+
| 1 | SIMPLE | t1 | ref | idx_a_b_c_d | idx_a_b_c_d | 10 |
const,const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-----
--------+------+--------------------------+
1 row in set (0.00 sec)
​

当执行语句遇到c>1时：

type = ref 代表应用索引；

key_len=10 索引长度为10，即只应用了a和b；

ref=const,const 应用了两个索引；

#调整顺序
mysql> explain select * from t1 where a=1 and b=1 and d=1 and c>1 ;
+----+-------------+-------+------+---------------+-------------+---------+-----
--------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-----
--------+------+--------------------------+
| 1 | SIMPLE | t1 | ref | idx_a_b_c_d | idx_a_b_c_d | 10 |
const,const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-----
--------+------+--------------------------+
1 row in set (0.00 sec)

执行结果一致：

注：索引的执行不是按照where条件的顺序，而是依据索引建立顺序来执行，并且查询优化器会将语句调整为“ select * from t1 where a=1 and b=1 and c>1 and d=1 ; ”在执行语句的时候；

#如果业务需求要求尽可能多的使用索引，即把d用上，那么就要改变索引顺序
#删除索引
mysql> drop index idx_a_b_c_d on t1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#创建新的组合索引，改变顺序
mysql> alter table t1 add index idx_a_b_c_d(a,b,d,c);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t1 where a=1 and b=1 and d=1 and c>1 ;
+----+-------------+-------+------+---------------+-------------+---------+-----
--------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-----
--------------+------+--------------------------+
| 1 | SIMPLE | t1 | ref | idx_a_b_c_d | idx_a_b_c_d | 15 |
const,const,const | 1 | Using where; Using index |+----+-------------+-------+------+---------------+-------------+---------+-----
--------------+------+--------------------------+
1 row in set (0.00 sec)

        当原组合索引sql查询语句执行慢的时候(即应用的索引被打断)，可以通过改变索引顺序来优化(即尽可能多的使用索引)；

索引被“< > between”打断的原因在于B+TREE必须匹配值，遇到范围检索B+TREE无法匹配就失效了；