MySQL索引

MySQL索引类型
1.普通索引
最基本得索引，任何列都可以建立普通索引
2.主键索引
主键上建立索引，主键保持唯一性，并且不能为null，一般在建表的时候选定。
大家在设计主键的时候一定要是自增的，非常不建议使用UUID作为主键。
为什么？因为UUID是无序的，MySQL在维护聚簇索引的时候都是按照主键的顺序排序的，也就是说每个数据页中的数据一定是按照主键从小到排序的，而且，数据与数据之前是通过单向链表连接的，上一个数据页中的最大的主键的值一定是小于下一个数据页中的最小的主键的值，数据页和数据页之间是通过双向链表来维护的。

如果主键是自增的，MySQL只需要根据主键目录能很快的定位到新增的记录应该插入到哪里，如果主键不是自增的那么每次都需要从头开始比较，然后找到合适的位置，再将记录插入进去，这样真的严重影响效率，所以主键的设计一定要是自增的。
另外唯一索引和主键索引类似，但是唯一索引不一定是自增的，所以维护唯一索引的成本肯定是大于主键索引的。
但是唯一索引的值是唯一的（唯一索引可以有一个值为 NULL），可以更快的通过索引字段来确定一条记录，但是可能需要进行回表查询。
3.复合索引
复合索引也叫组合索引，建立索引的时候使用多个字段，例如同时使用手机号和身份证号建立索引。复合索引使用的复合最左原则。

-- 创建索引的基本语法
CREATE  INDEX indexName 
ON table(column1(length), column2(length));
-- 例子 
CREATE INDEX idx_phone_name 
ON user(phone,name);

SELECT * FROM user_innodb where name = 'Fcant';
SELECT * FROM user_innodb where phone = '15100046637';
SELECT * FROM user_innodb where phone = '15100046637' and name = 'Fcant';
SELECT * FROM user_innodb where name = 'Fcant' and phone = '15100046637';

三条SQL只有 2、3、4能使用到索引 idx_phone_name ，因为条件里面必须包含索引前面的字段才能够进行匹配。而3和4相比where条件的顺序不一样，为什么4可以用到索引呢？是因为MySQL本身就有一层SQL优化，他会根据SQL来识别出来该用哪个索引，我们可以理解为3和4在MySQL眼中是等价的。
4.全文索引
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的**where**语句的参数匹配。fulltext索引配合**match against**操作使用，而不是一般的**where**语句加**like**。
它可以在**create table**，**alter table** ，**create index**使用，不过目前只有char、varchar，text 列上可以创建全文索引。正常情况下我们也不会使用到全文索引，因为这不是MySQL的专长。

MySQL查看索引：

show index from tbl_opr_info

InnoDB的索引

首先看一下InnoDB存储引擎中的索引，InnoDB表的索引按照叶子节点存储的是否为完整表数据分为聚簇索引和二级索引。

全表数据就是存储在聚簇索引中的。

聚簇索引以外的其它索引叫做二级索引。
下面结合实际的例子介绍下这两类索引。

create table workers
 (
     id    int(11)     not null auto_increment comment '员工工号',
     name  varchar(16) not null comment '员工名字',
     sales int(11) default null comment '员工销售业绩',
     primary key (id)
 ) engine InnoDB
   AUTO_INCREMENT = 10
   default charset = utf8;
 insert into workers(id, name, sales) values (1, '江南', 12744);
 insert into workers(id, name, sales) values (3, '今何在', 14082);
 insert into workers(id, name, sales) values (7, '路明非', 14738);
 insert into workers(id, name, sales) values (8, '吕归尘', 7087);
 insert into workers(id, name, sales) values (11, '姬野', 8565);
 insert into workers(id, name, sales) values (15, '凯撒', 8501);
 insert into workers(id, name, sales) values (20, '绘梨衣', 7890);

现在自己的测试数据库中创建一个包含销售员信息的测试表workers
包含id(主键),name,sales三个字段，指定表的存储引擎为InnoDB。
然后插入8条数据，聚簇索引建立在字段id上
聚合索引B+tree示意图：

从图中可以看到，聚簇索引的每个叶子节点存储了一行完整的表数据，叶子节点间采用单向链表按id列递增连接，可以方便的进行顺序检索。
InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个NOT NULL 的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式自增id列并在此列上创建聚簇索引。
接着来看二级索引。
还以刚才的workers表为例
在name字段上添加二级索引index_name

alter table  workers add index  index_name(name);

同样画出了二级索引index_name的B+tree示意图

图中可以看出二级索引的叶子节点并不存储一行完整的表数据，而是存储了聚簇索引所在列的值，也就是
workers表中的id列的值。


这两张示意图中B+tree的度设置为了3 ，这也主要是为了方便演示。
实际的B+tree索引中，树的度通常会大于100。
说了聚簇索引和二级索引 肯定要提到回表查询。
由于二级索引的叶子节点不存储完整的表数据，所以当通过二级索引查询到聚簇索引的列值后，还需要回到聚簇索引也就是表数据本身进一步获取数据。
比如说要在workers表中查询 名叫吕归尘的人

select * from workers where name='吕归尘';

这条sql通过name='吕归尘'的条件
在二级索引index_name中查询到主键id=8 ,接着带着id=8这个条件
进一步回到聚簇索引查询以后才能获取到完整的数据，很显然回表需要额外的B+tree搜索过程，必然增大查询耗时。
需要注意的是通过二级索引查询时，回表不是必须的过程,当Query的所有字段在二级索引中就能找到时，就不需要回表，MySQL称此时的二级索引为覆盖索引或称触发了索引覆盖。

select id,name from workers where name='吕归尘';

这句sql只查询了id，和name,二级索引就已经包含了Query所以需要的所有字段，就无需回表查询。

explain select id,name from workers where name='吕归尘';

使用explain查看此条sql的执行计划

执行计划的Extra字段中出现了Using where;Using index表明查询触发了索引index_name的索引覆盖,且对索引做了where筛选，这里不需要回表。
下面做对比，查询一下没有索引的

explain select id,name,sales from workers where name='吕归尘';

Extra为Using Index Condition 表示会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。

Extra为Using where 只是提醒MySQL将用where子句来过滤结果集。这个一般发生在MySQL服务器，而不是存储引擎层。一般发生在不能走索引扫描的情况下或者走索引扫描，但是有些查询条件不在索引当中的情况下。
这里表明没有触发索引覆盖，进行回表查询。

索引失效的常见场景

1.不满足最左匹配原则
例如给code、age和name这3个字段建好联合索引：idx_code_age_name。
该索引字段的顺序是：

• code
• age
• name

如果在使用联合索引时，没注意最左前缀原则，很有可能导致索引失效。
成功场景：
查询条件原本的顺序是：code、age、name，只有code和name，中间少了age，这种情况也能走code字段上的索引。
code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。
2.使用了select *
从执行结果看，走了全表扫描，没有用到任何索引，查询效率是非常低的。
如果查询的时候，只查真正需要的列，而不查所有列，可以选择需要查出的列。
3.索引上有计算
例如在某个字段上加减乘除等运算。
4.索引上用了函数
5.字段类型不同
比如主键类型为varchar类型，但你在查询的时候使用id=10这样情况查询数据时，索引会失效。
但是注意int类型的字段，你不管加不加引号，他都会走索引。因为MySQL在int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。mysql会把上面列子中的字符串175，转换成数字175，所以仍然能走索引。
6.like左边包含%
两种情况：’1%’和’%1’和’%1%’
‘1%’和’%1%’索引失败，’%1’索引正常
下面用一句话总结一下规律：当like语句中的%，出现在查询条件的左边时，索引会失效。
那么，为什么会出现这种现象呢？
答：其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。
在查目录时，通常会先从左边第一个字母进行匹对，如果相同，再匹对左边第二个字母，如果再相同匹对其他的字母，以此类推。通过这种方式能快速锁定一个具体的目录，或者缩小目录的范围。
7.列对比
两个字段都是int类型，类型是一样的。但如果把两个单独建了索引的列，用来做列对比时索引会失效。
8.使用or关键字
使用or关键字时注意，or关键字前和后都要加索引，不然就会失效。
9.order
在code、age和name这3个字段上，已经建了联合索引：idx_code_age_name。
失效场景：
a.如果order by语句中没有加where或limit关键字，该sql语句将不会走索引。
b.这一个索引的情况正常。但如果对多个索引进行order by，比如order by后面出现两个不同字段的索引，索引会失效。
c.order by 不满足最左匹配原则。
d.不同的排序，比如联合索引中的两个字段，其中一个升序，另外一个倒序，会导致索引失效。
成功场景：
a.满足最左匹配原则
b.配合where一起使用
c.相同的排序，例如联合索引中字段都采取升序或者倒序。