MySQL--索引使用

索引使用

创建索引：create index [index_name] on [ table_name(feild)];

查看索引：show index from [table_name];

使用规则

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳过了某一列，索引将部分失效(后面的字段索引失效)

例如：一个表的联合索引顺序为：name、age、sex，如果要用到这个索引，要保证最左边的列(age)存在，如果跳过了中间的列(name)，后面的索引(sex)将会失效。

生效：

select * from table where name='xxx' and age='xxx' and sex='xxx';

select * from table where name='xxx' and sex='xxx' and age='xxx';

select * from table where name='xxx' and sex='xxx';

select * from table where name='xxx';

不生效：

select * from table where age='xxx' and sex='xxx'; （最左边的列age不存在）

select * from table where sex='xxx'; 

范围查询

联合索引中，出现范围查询（<，>），范围查询右侧的列索引失效

select * from table where name='xxx' and age>30 and sex='male'； (sex索引无效)

select * from table where name='xxx' and age>=30 and sex='male'； (索引有效)

索引列运算

不要在所有列上进行运算操作，索引将失效

字符串不加引号

字符串不加引号，索引将失效

模糊查询

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

or连接的条件

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

 数据分布影响

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

 SQL提示

        如果一个字段，被包含在一个联合索引和自己的索引中(例如上面的联合索引和新建一个name自己的一个索引)，MySQL会自动选择联合索引。

        SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index: 建议使用
select * from table use index(idx_name) where name='老王';
ignore index: 忽略使用
select * from table ignore index(idx_name) where name='老王';
force index: 强制使用
select * from table force index(idx_name) where name='老王';

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少使用select *。例如 select name,age,sex

    explain的extra字段中：

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

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

前缀索引

当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

        语法

-- n表示提起column字段前n个字符作为索引
create index idx_xxx on table_name(column(n));   

       前缀长度选择

可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的巨鹿总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

-- 字符串前五位 与 总数的比值
select count(distinct substring(email,1,5)) / count(*) from user;

单列索引和联合索引

单列索引：一个索引只包含一个列

联合索引：一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑正对于查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

1. 针对于数据来那个较大，且查询比较平凡的表建立索引。

2. 针对于长作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。

3. 尽量选择区分度较高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4. 如果是字符串类型的字段，字符串长度较长，可以针对于字符串的特点，建立前缀索引。

5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，结婚存储空间，避免回表，提高查询效率。

6. 要控制索引的数量，索引不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改查的效率。

7. 如果索引列不能存储NULL值，亲在创建表时候使用not null 约束它。当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询