mysql索引的使用（避免索引失效）

MySQL索引

使用索引的效率

索引使数据库中很重要的概念，通过索引往往可以解决大多数sql优化的问题

下面对比一下使用索引和不使用索引对查询速率的影响

以100万条记录为例

不使用索引

使用索引查询同一条记录

可见，通过索引可以有效的提高查询效率。

查看索引的使用频率

show status like 'Handler_read%';	 -- 查看当前会话索引使用情况

show global status like 'Handler_read%';	-- 查看全局索引使用情况

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。

Handler_read_rnd：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

避免索引失效

为了发挥索引的优势，应尽量避免索引失效。
已知创建复合索引（name status address）

1). 全值匹配 ，对索引中所有列都指定具体值。该情况下，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;

2). 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。(想象爬楼梯)

匹配最左前缀法则，走索引：

违法最左前缀法则 ， 索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

3). 范围查询条件之后的字段，索引失效

根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。

4). 不要在索引列上进行运算操作， 索引将失效。

5). 字符串不加单引号，造成索引失效。

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6). 尽量使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

如果查询列，超出索引列，也会降低性能。(索引列没有password，所以需要回表查询 )

TIP : 
	
using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据

using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;	

8). 以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

解决方案 ：通过覆盖索引来解决

9). 如果MySQL评估使用索引比全表更慢，则不使用索引。

创建address单值索引

明明单独创建了address索引，但是explain查看没有使用索引，这种情况跟表中数据有关，查看表数据知道，12条数据，11条是’北京市’，使用索引查找效率不如直接全表扫描来的快，所以在执行SQL语句的时候，MySQL放弃使用索引，而使用全表扫描。

10). is NULL ， is NOT NULL 有时索引失效。

MySQL底层会自动判断，如果全表扫描快，则直接使用全表扫描，不走索引。如果表中该索引列数据绝大多数是非空值，则使用is not null的时候走索引，使用is null的时候不走索引（还不如全表扫描快），全表扫描；反之亦然。

11). in 走索引， not in 索引失效。

12). 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引 。

创建复合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引 ： 
	name
	name + status
	name + status + address

创建单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

使用单列索引的时候，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。