MySQL索引使用原则

1、最左前缀法则：

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

2、范围查询

      联合索引中，出现范围查询（<,>）,范围查询右侧的列索引失效（业务允许的情况下尽量使用>=，加个”=”）。

3、索引列运算

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

4、字符串不加引号

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

5、模糊查询

             如果仅仅是尾部模糊匹配，索引不会失效（like 'xxx%'），如果是头部模糊匹配，索引将失效（like '%xxx'）。

6、Or连接的条件

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

7、数据分布影响

     如果MySQL评估使用索引比全表更慢，则不使用索引。（满足条件的数据大概超过一半，就不走索引）

8、索引使用

• SQL提示：

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

                 use index（建议使用）:select * from table use index(xxx) where ...

                 ignore index（避免使用）:select * from table ignoreindex(xxx) where ... 

                 force index（强制使用）: select * from table forceindex(xxx) where ...

• 覆盖索引：

    尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *，select * 极易出现回表查询，扫描两次索引，从二级索引中查一次，从聚集索引中查一次;

• EXTRA信息：

       

• 前缀索引：

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

      语法：Create index idx_xxx on table_name(column(n));对某一列的前n个字符创建索引

1. 前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（不是单条，是所有的不重复值，基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1（没有重复的索引值），这是最好的索引选择性，性能也是最好的。

            通过以下语句可以判断：

    

• 单列索引和联合索引

1. 单列索引：即一个索引只包含单个列。
2. 联合索引：即一个索引包含了多个列。
3. 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
4. 多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

• 联合索引的存储情况：

  

• 创建联合索引时，索引列的顺序有影响。

9、索引原则：

         三个点：什么表需要建索引；什么字段需要建立索引；建立什么索引；

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