mysql索引的使用与优化
作者:binmysql支持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 : 告诉优化器,不查询缓存,并且不将结果存入缓存