mysql 基础(索引)

索引使用规则：
1.最左原则
2.可能会导致索引失效的情况
1）范围查询：联合索引中出现范围查询（>,<）,范围查询右侧的列索引失效，建议使用(>=, <=)

2）索引列运算，索引失效
explain select * from n_user where substring(phone, 10, 2)='15'; # 查询手机号最后两位为15的记录
3）字符串不加引号，索引失效
explain select * from n_user where phone=15612312345
4）模糊查询：如果只是尾部模糊匹配，索引不会失效；头部模糊匹配，索引失效


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

解决：对没有索引的字段加上索引
6）数据分布影响
如果mysql评估使用索引比全表更慢，则不适用索引

3.SQL提示：是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
use index: 使用哪个索引
--explain select * from n_user use index(xxx) where phone=15612312345

ignore index: 不用使用哪个索引
--explain select * from n_user ignore index(xxx) where phone=15612312345

force index: 必须使用哪个索引
--explain select * from n_user force index(xxx) where phone=15612312345

4.覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *
select id from n_user where uid=11;
using index condition:查找使用了索引，但是需要回表查询数据
select id,name from n_user where uid=11;
using where;using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

5.前缀索引
当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：create index idx_xxxx on table_name(column(n)); ## n表示根据字段的前边几个字符创建索引
前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

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

7.索引设计原则

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