MySQL学习（14）：索引使用规则

 1. 索引语法

#创建索引
create [unique|fulltext] index 索引名 on 表名 (字段名,...);
#字段可以有多个
#如果不加unique或fulltext，那么将创建一个普通索引
#在InnoDB引擎小，创建的索引默认是B+tree结构

#查看索引
show index from 表名;

#删除索引
drop index 索引名 on 表名;

2.联合索引

2.1创建语法

为th_use表中的3个字段：profession、age、status创建联合索引:

create index idx_user_pro_age_sta on th_user(profession,age,status);

括号内3个字段的顺序是有作用的，不能随意排序。

创建完成后，可以查找到下图的结果

2.2最左前缀法则

联合索引要遵守最左前缀法则

最左前缀法则：查询从索引的最左列开始，并且不能跳过索引中间的列。如果跳过某一列，该列后面的字段的索引会失效。

以下面的表tb_user为例，它有一个联合索引idx_user_pro_age_sta，创建联合索引时，3个字段的顺序从左至右为profession、age、status

（1）搜索语句包含联合索引的每个字段

虽然建立索引时要区分字段的左右顺序，但在搜索语句并不需要特别区分顺序，并列即可

（2）搜索语句缺少右列字段

语句中虽然少了status字段，但因为包含了最左列，且age是左数第二列，顺序没有打乱，所以可以对profession、age字段正常使用索引

（3）搜索语句缺少最左列字段

可以看到，一旦缺少了最左列字段profession，其他2个字段是无法使用索引的

（4）搜索语句缺少中间字段

这种情况会造成缺少列及其右边的字段无法使用索引，而缺失列左边的字段依然可以正常使用索引。

即：profession字段可以使用索引，status字段无法使用索引

2.3范围查询造成右侧列失效

联合索引中，出现范围查询（即>、<），则范围查询右侧的列字段的索引将失效。但如果使用>=、<=则不会出现这个问题。

沿用之前的表为例，下图展示的就是虽然使用了联合索引idx_user_pro_age_sta，但第三个字段status的索引失效了

如果把age>30改为age>=30，则可以避免这一情况。可以看到下图中索引使用的字节数增加了，说明status字段也使用了索引

3.索引失效的5种情况

3.1对建立了索引的字段进行运算操作，索引将失效

对字段的运算操作，最典型的就是函数运算

像下图这样的操作就会导致索引失效

3.2字符串类型的字段没加单引号，索引将失效

对于char、varchar、text这种字符串类型的字段，必须要在搜索语句中对其后的数值加上单引号，才能使用索引。

像下图中的第二个语句，由于没有加单引号，它就不会使用索引，而是进行全表扫描：

3.3模糊匹配

尾部模糊匹配不会导致索引失效，头部模糊匹配会导致索引失效。

下图中，第一条语句可以正常使用索引，第二条语句则无法使用索引：

3.4 or连接若有一边的字段索引失效，则涉及的索引都会失效

下图中第一条语句，如果id建立了索引，age没建立索引，则id的索引也会失效

3.5若mysql评估全表扫描比使用索引快，则会主动放弃使用索引

这种情况不需要人为操作什么，mysql的判断是在命令执行前自动运行的

4.SQL提示

假如一个表中的某些字段既单独添加了索引，又添加了联合索引，那么在搜索这些字段的时候，就会在2中索引之间做选择，如果不进行人为干涉，那么就全都由mysql自行判断。

但使用者也可以通过添加SQL提示，来干预mysql的选择：

（1）建议使用：use index:

explain select * from tb_user use index(idx_user_pro) where profession ='软件工程';
#当有多个索引可选择的时候，建议mysql使用联合索引idx_user_pro
#如果mysql判断使用联合索引消耗过大，那么就不会使用联合索引

（2）强制不使用：ignore index:

explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
#当有多个索引可用时，排除idx_user_pro

（3）强制使用：force index:

explain select * from tb_user force index(idx_user_pro) where profession='软件工程’;
#强制mysql必须使用联合索引idx_user_pro

如果存在多个查询条件，对于查询字段建立索引时，建议建立联合索引，而非单列索引。

5.覆盖索引、回表查询

回表查询指的就是SQL语句使用了二级索引找主键号，再由主键号走聚集索引的过程。因为使用了2次索引，所以造成了消耗。实际操作时，要避免产生回表查询。

覆盖索引指的是，虽然SQL语句使用了二级索引，但查找的东西已经可以在二级索引内找到，不必再走聚集索引。

上图中的语句1是通过id直接走的聚集索引，不涉及回表查询

语句2虽然走了二级索引，但所搜索的目标是id号，这已经在二级索引的叶子结点中存储了，所以就不必走聚集索引了，也就是一种覆盖索引

语句3所搜索的gender字段不在二级索引的叶子结点内，只能走聚集索引，也就是发生了回表查询

所以在使用select语句时，要避免直接使用select *

6.前缀索引

当字段类型为字符串(char，varchar，text)时，有时候会存储很长的字符串，这会让索引变得很大，查询时影响查询效率。

此时可以只将字符串数据的一部分前缀建立索引，提高索引效率。

create index 索引名 on 表名(字段名(n));
#表示根据字段的前n个字符建立索引

*该使用字段的前几位建立索引

这可以根据索引的选择性来决定。选择性是指不重复的索引值和数据表的记录总数的比值。

假设现在要判断email字段的选择性，就可以通过下面的语句进行比较：

select count(distinct substring(email,1,n))/ count(*) from tb user;
#可以把n取不同的数字，如...6、7、8...等进行必交，得到的结果越接近1，选择性越好

*如果搜索到了相同前缀的数据，那么会把每一个都显示出来

7.索引创建原则

（1）针对数据量较大，查询较频繁的表建立索引。

（2）针对常作为查询条件(where)、排序(orderby)、分组(group by)操作的字段建立索引。

（3）尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

（4）如果是字符串类型的字段，字段的长度较长，可以建立前缀索引。

（5）尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

（6）要控制索引的数量，索引越多，维护索引结构的代价也就越大，会影响增删改的效率

（7）如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。