MySQL索引-使用原则

索引使用

验证索引效率

执行SELECT * FROM tb_sku WHERE sn = ‘1000000000003145001’\G;

未建立索引时：1 row in set (20.78 sec)

创建索引：CREATE index idx_sku_sn on tb_sku(sn); 构建花费了很长时间

创建索引后的效果：1 row in set (0.01 sec)

由此可见索引大大提高了查询效率

索引使用原则

最左前缀法则：

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则是指查询从索引的最左列开始，并不跳过索引中的列。

如果跳过某一列，索引将部分失效（后面的字段索引失效）。

profession、age、status创建联合索引 idx_user_pro_age_sta

explain select * from tb_user where profession ="软件工程’ and age =31 and status =‘0’； 联合索引有效

explain select * from tb_user where profession ="软件工程’ and age =31； 联合索引有效

explain select * from tb_user where profession='软件工程’； 联合索引有效

explain select * from tb._user where age = 31 and status = ‘0’; 联合索引失效

explain select * from tb_user where status =‘0’； 联合索引失效

注：索引是否生效与字段放置的位置无关

范围查询：联合索引中，出现范围查询(>、<)，范围查询右侧的列索引失效

索引列运算：索引列上进行运算操作，索引将失效

字符串不加引号：字符串类型字段使用时，不加引号，索引将失效

模糊查询：如果仅仅是尾部模糊查询，索引不会失效。如果是头部模糊查询，索引失效。

or连接的条件：用or分割开的条件，如果or前的条件中的列由索引，而后边的列中没有索引，那么索引失效。

数据分布影响：如果MySQL评估使用索引比全表扫描慢，那么不使用索引

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(id user_pro) where profession = ‘软件工程’;

force index[强制]:

explain select * from tb _user force index(iax_user_pro) where profession = ‘软件工程’ ;

覆盖索引

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

通俗的来说，如果需要查询的字段存在于索引当中，那么通过索引查询之后不需要回表查询数据，但使用SELECT *的话，多余的字

段在索引中没有，还需要回表查询数据

前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘I0，

影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法

CREATE index idx_xxxx on table_name(column(n));

前缀长度

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

SELECT COUNT(distinct email)/count(*) from tb_user;

SELECT COUNT(distinct substring(email,1,5))/count(*) from tb_user;

单列索引和联合索引

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

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

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

注：创建索引时要注意创建索引的顺序

索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。