mysql索引的使用与优化

作者：bin

mysql支持4种索引类型，常用等有B-tree和hash索引，

B-tree支持大部分存储类型，memory仅支持hash，hash因为索引方式的问题，只支持比较索引，不支持范围，排序等使用索引

一、使用场景：

1、全值匹配：

使用explain 解析sql语句，得到的type为const，即为全值匹配，例如我们直接使用id进行查询时，就为const；

2、范围查询：

type为range时，即为范围查询，我们使用类似 id > 100 and id < 200时，就会使用范围查询; 3、最左前缀匹配：当type为ref时即为最左前缀索引，值得注意的是例如col 1+ col2 + col3的联合索引，mysql会使用col1 , col2进行索引，而不会用col1 , col3或者col2,col3进行索引 4、仅仅对索引进行查询在查询时，取出的字段都为索引，那么在extar中就显示为using index, type依然为const；示例col1和col2为联合索引：

select col form table_tmp where col = a and col2 = b;

5、匹配列前缀

在使用like进行查询时，如果仅仅使用索引中的第一列，并且包含索引第一列开头部分进行查找,例如：

explain select * from film where title like 'A%' limit 10;

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | film | range | idx_title | idx_title | 767 | NULL | 46 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+

extra 为using where 的意思为需要通过索引回表中去查数据；

但是如果like ‘%A’，显然不是匹配前缀，这时就用不上这个索引了

二、存在索引但不使用索引的场景

1、以%开头的like查询不能够利用B-tree索引，explain中key为null，即没有使用索引例如

like "%Star%"

如果遇到以%开头的查询，很显然没办法用索引来，我们可以使用全文索引(Fulltext)的方式在解决类似的全文检索问题;

在InnoDB结构时，二级索引中都会包含主键id，所以我们可以先通过二级所以找到主键id，然后通过主键id，找到整行值；

select * from (select id from table_tmp name  like '%Start%')  a,  table_tmp b where a.id = b.id ;

tips:这里创建了一个临时表a

2、类型出现隐式的转换

例如索引类型为字符串时：

select * from name = 1; //此时不会使用索引

select * from name = '1' //此时会使用索引

3、复合索引不包含最左边部分

复合索引查询条件不包含最左边部分即不满足最左原则，就不会使用复合索引；

4、使用索引扫描还不如全表扫描快

例如 like “S%”, 返回的记录比较大，mysql就会预估索引扫描可能还不如全表扫描快;

此时我们可以考虑更换一个效率更高的值来促使mysql选择索引扫描;

5、or语句分割条件

如果or语句前面的字段有索引，而or后面的字段没有索引，那么涉及到的索引都不会被使用，因为后面的条件没索引，那么就要全表扫描，既然不可避免全表扫描，那么就会直接进行全表扫描;

6、条件字段函数操作
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

where month(t_modified)=7;

where id + 1 = 1000;

三、日常使用sql的优化

1、大批量数据导入时

如果时MyISAM引擎，并且数据表中已有数据，需要附加更多的数据时，可以先关闭非唯一索引的更新，然后导入数据，再打开非唯一索引的更新：

实现方式：

alter table tbl_tmp disable keys

导入数据….

alter table tbl_tmp enable keys

而在空的表中导入数据时，默认就是先导数据，再创索引;

而InnoDB引擎，储存的顺序时按照主键排序的，所以导入的数据如果已经按主键排序好来，效率会快一些;

2、优化insert语句

如果需要插入多条，尽量使用多个值一起插入，这样可以大大减少客户与数据库之间的连接、关闭等消耗；

如果多个用户需要插入多条数据时，可以使用insert delayed语句提高插入速度，其实数据都放在内存队列中，并没有写入磁盘，但这比每条都插入要快得多；

将索引和数据文件分在不同的磁盘

3、优化order by语句

mysql中有2种排序方式

3.1、根据覆盖索引扫描直接返回有序数据。

这里explain中extra会显示Using index.

3.2、对返回对结果数据进行排序。

这里explain中extra会显示为Using filesort

了解了排序方式，优化目标就清晰了，在不可避免进行排序时，应当优先通过索引直接返回有序对数据，而不是再排序。

对于不可避免使用到filesort查询时，因为是优先在sort_buffer中排序，如果buffer不够大，就会创建临时文件进行归并排序，可以考虑适当调高sort_buffer_size，来尽量使排序在内存中完成。而不是用临时表。

尽量使用指定字段，而不是随处使用select * ，这样可以减少排序区的使用提高sql性能;

4、优化gourp by 语句

如果一个sql已经使用来group by，那么也可以强制使用order by null 来禁止mysql使用filesort排序，来提交效率；

使用索引作为group by字段可以提高效率，验证方法就是在explian中查看未使用use temproray 和use filesort，即生效了

如果数据量大，那么可以用SQL_BIG_RESULT来提示sql优化器直接使用磁盘排序，不使用temproray表。

5、优化嵌套查询

能用一句sql写完的功能不要用2句sql，善用嵌套，可以减少访问次数，同时也可以避免事物或者表锁死。

6、优化or语句

对于or用到的条件字段，都应该有索引，如果没有索引，应该考虑增加索引。

因为只要有一个没有索引，那么所有索引都将失效

值得注意的时，如果or用到的条件时在一个复合索引中，那么也不会被用到;

7、优化分页

先通过索引来分页，然后再通过索引来找到指定行，而不是直接进行分页；

优化前：

select id,name from tbl_tmp order by title limit 100,10;

优化后

select a.id, a.name from tbl_tmp a inner join ( seletct id from tbl_tmp order by title limit 100,10 ) b where b.id = a.id ;

8、通过固定的增长id来优化分页

每次分页时，记录最新的ID，然后：

select * from tbl_tmp where times < 1000 order by times desc limit 10;

这样就将limit n,m 优化为来limit m；

不过这只适用于排序字段不出现大量重复的情况下

四、使用查询优化器提示

mysql提供了sql提示，我们在使用sql语句时，可以提示mysql，如何进行优化

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

ALL : 返回所有匹配的记录行
DISTINCT：移除重复的行
DISTINCTROW：是DISTINCT的同义词
HIGH_PRIORITY：给select以高于update的优先级去执行，仅用于只使用了表锁的存储引擎，例如MyISAM, MEMORY, and MERGE
STRAIGHT_JOIN ：会将from后面的表格使用做链接(left join)进行查询优化，例如使用了inner join，执行器不知道联合查询的表顺序
SQL_SMALL_RESULT：可以用于Group by 和 distinct，提示sql优化器，使用内存进行排序，通常用不到
SQL_BIG_RESULT：可用于Group by 和 distinct，提示sql优化器，使用基于磁盘的临时文件去排序，通常用不到
SQL_BUFFER_RESULT：强制将结果放入临时表中，可以更快的释放对表对锁，并且返回客户端耗时比较长时，可以用于优化
SQL_CACHE：告诉优化器将结果存入缓存
SQL_NO_CACHE ：告诉优化器，不查询缓存，并且不将结果存入缓存