索引可优化的场景
下图是该文章中例题使用表的结构和总行数
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);看列的选择性