MySQL-----索引

一、索引建立的原则

单列索引：单列索引即由一列属性组成的索引。

联合索引(多列索引)：联合索引即由多列属性组成索引。

最左前缀原则：如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

eg：我设置(name,age)为联合索引，则

select * from user where name=xx and age=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where age=xx ; // 无法命中索引 

注意：查询的时候如果两个条件都用上了，但是顺序不同，如 age = xx and name = xxx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。

二、哪些操作会引起索引失效

（1）> <范围查询

mysql 会一直向右匹配直到遇到索引搜索键使用>、<就停止匹配。一旦权重最高的索引搜索键使用>、<范围查询，那么其它>、<搜索键都无法用作索引。即索引最多使用一个>、<的范围列，因此如果查询条件中有两个>、<范围列则无法全用到索引。

（2）like %xx

搜索键值以通配符%开头（如: like '%abc'），则索引失效，直接全表扫描；若只是以%结尾，则不影响索引构建。

（3）对索引列进行函数或表达式运算

如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。 

eg:  select * from users where length(name)>4;

（4）数据类型不一致(隐式类型转换导致索引失效)

如果列是字符串类型，传入条件是必须用引号引起来，不然报错或索引失效。

eg:  select * from users where name = 28;

（5）or条件索引问题

or 条件列除了同时是主键的时候，索引才会生效。其他情况下的，无论条件列是什么，索引都失效。

（6）!=问题

普通索引使用 != 索引失效，主键索引没影响。
where语句中索引列使用了负向查询，可能会导致索引失效。
负向查询包括：NOT、!=、<>、NOT IN、NOT LIKE等。

（7）联合索引违背最左匹配原则

联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效。

（8）order by问题

order by 对主键索引排序会用到索引，其他的索引失效

三、如何判断索引生效

（1）使用`EXPLAIN`函数：

    `EXPLAIN` + sql语句，可以显示MySQL如何使用索引来处理SELECT语句。例如：
     EXPLAIN select  *  from  student;

`EXPLAIN`会显示很多列，其中重要的列包括：

• `table`：显示这一行的数据是关于哪张表的。
• `type`：显示连接使用了何种类型，从最好到最差的连接类型为：const、eq_reg、ref、range、index和ALL。
• `possible_keys`：显示可能应用在这张表中的索引。如果为空，说明没有可能的索引。
• `key`：实际使用的索引。如果为NULL，则没有使用索引。
• `key_len`：使用的索引的长度。
• `ref`：显示索引的哪一列被使用了。
• `rows`：MySQL认为必须检查的用来返回请求数据的行数。
• `Extra`：关于MySQL如何解析查询的额外信息。

（2）查看`Extra`列：

`Extra`列会提供关于查询优化的额外信息。例如：
 `Using index`：表示列数据是从仅仅使用了索引中的信息而没有读取实际的表返回的。
 `Using filesort`：表示查询需要进行额外的步骤来发现如何对返回的行排序，这通常意味着没有使用索引。

（3）检查`possible_keys`和`key`列：

 `possible_keys`列显示可能应用在这张表中的索引，如果为空，则没有可能的索引。
 `key`列显示实际使用的索引。如果为NULL，则没有使用索引。

（4）使用时间检测：

 可以通过设置`profiling=1`，然后执行SQL语句，最后查看`show profiles`来检测查询的执行时间。如果使用了索引，通常会看到更快的执行时间。

（5）注意索引失效的情况：如第二点

通过以上方法，可以有效地判断索引是否生效，并进行相应的优化。