索引的使用及设计规则

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

explain select * from tb_user where phone='17799990015'; explain select * from tb_user where phone=17799990015;

类型不一致时，索引不生效，查看一下执行计划：

查看联合索引的情况；字符串不加引号的情况下，索引将会失效。

explain select * from tb_user where profession='软件工程' and age=31 and status='0'; explain select * from tb_user where profession='软件工程' and age=31 and status=0;

模糊查询，如果是尾部进行模糊匹配，索引不会失效；如果是头部进行模糊匹配，索引失效。

explain select * from tb_user where profession like '软件%'; explain select * from tb_user where profession like '%工程';

or连接的条件

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

explain select * from tb_user where id=10 or age=23; explain select * from tb_user where phone='17799990017' or age=23;

数据分布影响：

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

explain select * from tb_user where phone >='17799990020';

explain select * from tb_user where phone >='17799990000';

explain select * from tb_user where phone >='17799990010';

explain select * from tb_user where phone >='17799990013';

表中有这些索引；

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

use index:

explain select * from tb_user use index(idx_user_pro) where profession='软件工程';

ignore index:

explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';

force index:

explain select * from tb_user force index(idx_user_pro) where profession='软件工程';

show index from tb_user; explain select * from tb_user where profession='软件工程'; // mysql数据库使用的是联合索引

索引使用（覆盖索引）

尽量使用覆盖索引（查询使用 索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。

explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0';

explain select id, profession, age, status from tb_user where profession = '软件工程' and age = 31 and status = '0';

explain select id, profession, age, status, name from tb_user where profession = '软件工程' and age = 31 and status = '0';

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

using index： 查找使用了索引，但是查找的列都在索引列里，不需要回表查询。

前缀索引：

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

前缀索引会有sub part；显示所使用字段的前缀长度。

explain select * from tb_user where email='daqiao666@sina.com';

单列索引与联合索引：

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

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

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议使用联合索引。

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

（7）索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。

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

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

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

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

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

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