索引优化(优化场景、失效场景、优化手法)

索引可优化的场景

下图是该文章中例题使用表的结构和总行数

 

mysql> desc usertb;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| uname       | varchar(20)         | YES  |     | NULL    |                |
| ucreatetime | datetime            | YES  |     | NULL    |                |
| age         | int(11)             | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

mysql> select max(id) from usertb;
+----------+
| max(id)  |
+----------+
| 10000000 |
+----------+

1、索引可以优化where条件，当使用where条件取5%~20%的数据时，我们希望在执行计划中row尽可能的小

mysql> explain select * from usertb where id=100;
+----+-------------+--------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | usertb | NULL       | const | id            | id   | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------+---------+-------+------+----------+-------+

2、索引可以优化表连接，当连接列上有索引时，优化器可以选择驱动表和被驱动表和连接顺序

3、索引可以优化order by,尤其是对于order by limit分页查询。当where条件过滤的行数较少，需要对大量数据进行排序时，在连接列上建索引，会将索引列跟主键列取出来，根据索引列进行排序，因此在order by索引列时，性能很好

//uname列上没有建立索引

mysql> explain select * from usertb order by uname limit 1,10;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9726850 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+


//uname列上建立索引
mysql> explain select * from usertb order by uname limit 1,10;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | usertb | NULL       | index | NULL          | i_umane | 63      | NULL |   11 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+

问题：当用户一直刷下一页时，可能就会出现全表扫描，此时这种情况可以用表连接的方式改写

//uname列有索引
mysql> explain select * from usertb order by uname limit 1000000,10;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9726850 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+

//用表连接的方式改写
mysql> explain select * from usertb b
    -> join (select id from usertb order by uname limit 1000000,10) a
    -> on a.id=b.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 1000010 |   100.00 | NULL        |
|  1 | PRIMARY     | b          | NULL       | eq_ref | id            | id      | 8       | a.id |       1 |   100.00 | NULL        |
|  2 | DERIVED     | usertb     | NULL       | index  | NULL          | i_uname | 63      | NULL | 1000010 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+

问题：如何求出总页数？

对于id列是自增长列，假设一页数据为10行，因此
mysql> select (max(id))/10 from usertb;
+--------------+
| (max(id))/10 |
+--------------+
| 1000000.0000 |
+--------------+

4、索引可以优化group by

5、覆盖索引可以避免回表取数据，即使访问大量数据，也比全表扫描要好的多

6、索引可以优化gap锁，对于insert来说，表上要有主键，不然会产生gap锁。对于delete、update来说，where条件要有高效索引。

7、对于主键索引来说，无论取超过20%，甚至80%，走主键索引效果都很好

 

索引失效的场景

1、SQL语句没有where条件或者where条件列没有索引

//无索引情况
mysql> explain select * from usertb1 where uname='用户1';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | usertb1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9973 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

2、SQL语句有where条件，并且where条件列上有索引，但是取的数据超过30%，会走全表扫描

mysql> explain select * from usertb where uname like '用户1';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | i_uname       | NULL | NULL    | NULL | 9726850 |    20.63 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

3、对于小表来说，优化器会认为走全表扫描较好

4、对于多列索引来说，where条件没有出现前导列，或者列之间连接条件是or

5、<>、!=索引会失效，一般认为=号取的数据较少

//uname列有索引
mysql> explain select * from usertb where uname <> '用户1';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | i_uname       | NULL | NULL    | NULL | 9726850 |    70.63 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

6、where like '%abc';一般会走全表扫描

mysql> explain select * from usertb where id like '%1';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9726850 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

7、is null一般会走索引，即使表中数据很多，这是数据库的一个bug

mysql> explain select * from usertb where uname is null;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | usertb | NULL       | ref  | i_uname       | i_uname | 63      | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+

索引优化手法

1、explain语句看执行计划，看是否type列类型是all走全表扫描、如果是表连接的话，看驱动返回的行数是否过多、被驱动表是否没有走主键或者高效索引，访问的成本是全表扫描

2、对SQL进行结构分析，主要看where条件、join连接条件

3、show table status like ‘tbl_name’\G，看表的行数，如果是大表一定要走全表扫面

4、show index from tbl_name;看列的索引情况以及列的选择性，主要看前导列

5、select count(distinct tbl_name) from (select * from tbl_name limit 19000,10);看列的选择性