mysql-Innodb存储引擎-联合索引

联合索引

联合索引是什么？

MySQL可以使用多个字段同时建立一个索引,叫做联合索引。

当我们的where查询存在多个条件查询的时候，我们需要对查询的列创建组合索引。

什么时候用单列索引，什么时候使用联合索引

我一个表 students 表，有3个字段 ，id,name,age 我要查询 通过 name 和age，在这两个字段 是创建联合索引？还是分别在name和age上创建单列索引呢？ 多个字段查询什么情况下用联合索引 什么时候分别创建单列索引呢？

1，首先要确定优化的目标，在什么样的业务场景下，表的大小等等。如果表比较小的话，可能都不需要加索引。
2，哪些字段可以建索引，一般都where、order by 或者 group by 后面的字段。
3，记录修改的时候需要维护索引，所以会有开销，要衡量建了索引之后的得与失。

学生表，可以认为name的重复度比较小，而age的重复度比较大，对于单列索引来说，比较适合建在重读度低的列上。

对于select * from students where name='张三’and age=18; 题主所说的两种情况
A. name 和 age 各自单独建立索引。
一般来说mysql会选择其中一个索引，name的可能性比较大，因为mysq会统计每个索引上的重复度，选用低重复度的字段。另外一个age的索引就不会用到，但还有维护索引的开销，所以age的索引不需要创建。

B. name和age的联合索引
这种索引的切合度最好，mysql会直接选用这个索引。但相对单独的name索引来说，维护的成本要大一些，并且索引数据占用的存储空间也要更大一些。

回过来看，有必要使用联合索引吗？

建议此时使用单列索引。学校里可能会有重名的人，但比较少。用name就可以比较精准的找到记录，即使有重复的也比较少。

什么情况下使用联合索引比较好呢？

举一个例子，大学选认课老师，需要创建一个关系对应表，有2个字段,student_id 和 teacher_id，想要查询某个老师和某个学生是否存在师生关系。
一个学生会选几十个老师，一个老师会带几百个学生
如果只为student_id建立索引的情况下，经过索引会选出几十条记录，然后在内存中where一下，去除其余的老师。
相反如果只为teacher_id建立索引，经过索引会选出几百条记录，然后在内存中where一下，去除其余的学生。
两种情况都不是最优的，这个时候使用联合索引最合适，通过索引直接找到对应记录。

使用联合索引的好处

减少开销：假如对col1、col2、col3创建组合索引，相当于创建了（col1）、（col1，col2）、（col1，col2，col3）3个索引
覆盖索引：假如查询SELECT col1, col2, col3 FROM 表名，由于查询的字段存在索引页中，那么可以从索引中直接获取，而不需要回表查询

效率高：对col1、col2、col3三列分别创建索引，MySQL只会选择辨识度高的一列作为索引。假设有100w的数据，一个索引筛选出10%的数据，那么可以筛选出10w的数据；对于组合索引而言，可以筛选出100w*10%*10%*10%=1000条数据

最左匹配原则

假设我们创建（col1，col2，col3）这样的一个组合索引，那么相当于对col1列进行排序，也就是我们创建组合索引，以最左边的为准，只要查询条件中带有最左边的列，那么查询就会使用到索引。相当于创建了（col1）、（col1，col2）、（col1，col2，col3）3个索引。

最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询就会停止匹配。

下面进行假设和举例：

假设建立联合索引(X，Y，Z)

1. 全值匹配时：
select * from table1 where X = 1 and Y = 2 and Z = 3 
select * from table1 where X = 1 and Y = 2 and Z = 3 
select * from table1 where X = 1 and Y = 2 and Z = 3 

上面的查询用到了索引，因为有Mysql查询优化器，优化器会调整sql语句的顺序，使之达到最高的执行效率。所以where语句中的条件的顺序不影响索引的使用。

2. 匹配索引中最左边的列时：
select * from table1 where X = 1 
select * from table1 where X = 1 and Y = 2  
select * from table1 where X = 1 and Y = 2 and Z = 3
上述查询从最左边开始连续屁屁额，使用了索引

3. 没有最左边字段的查询时：
select * from table1 where Y = 2 
select * from table1 where Z = 3 
select * from table1 where Y = 2 and Z = 3  
上述查询没有最左列，所以使用的是全表扫描。

4. 不连续时：

select * from table1 where X = 1 and Z = 3 
只使用了X列的索引，后续没有使用索引。

5. 匹配范围值：

select * from table1 where  X > 1 and X < 3 and Y > 1;
进行范围查找时，只有对联合索引最左列进行范围查询才能使用索引。例如上述sql，在1<X<3的范围内，X的值是有序的，所以可以使用索引，但是在1<X<3范围内，Y的值是无序的，不能使用索引。

1、应该将最常用作限制条件的列放在最左边。（where、order by 或者 group by 后面的字段重复使用比较多的）

2、mysql查询每次只能使用一个索引。

3，索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4，使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5，排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

排序使用索引提高效率，减少文件排序

1. order by顺序和索引顺序一致，且所有列一致升序或降序
2. 当多表连接时，order by所有列使用第一个表，此时同样遵循最左前缀法则
3. order by字段是索引最左连续字段
4. 如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

正例：

where a=? and b=? order by c; 索引：a_b_c

反例：

索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b无法排序。

Order by 满足两种情况，会使用Index方式排序

1. order by语句使用索引最左前列（没有where子句），并且排序方式要相同比如都是升序
2. 使用where子句与order by子句条件列组合满足索引最左前列，并且where子句中不能使用范围查询。

6，like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

7，不要在列上进行运算

8，不使用NOT IN操作

NOT IN操作不会使用索引将进行全表扫描。NOT IN可以用NOT EXISTS代替

9、or子句两边无法均利用索引，通常是全表扫描

优化原则：

1. or子句全部相同，则改为in

示例：

select * from t1 where a=1 or a=3;

1. or子句具有公共子序列前缀的，请在or公共部分建立索引

示例：

select * from t1 where (a=1 and b=2) or (a=3 and c=4);

1. 若无公共，则建议改为union all，并为每部分建立索引

示例

select * from t1 where a=1 or b=2;

转换

select * from t1 where a=1 union all select * from t1 where b=2;

10、【强制】禁止出现隐式转换，保持SQL中变量类型与字段类型一致

示例：

条件语句中数据类型以传入参数为准。user_id定义为int型，在程序中使用user_id=’1’，就会发生隐式转换，将user_id转换成varchar类型，导致无法使用索引，因此应使用user_id=1。

11、高偏移量的limit

示例：

select * from film where length>200 limit 2000,30;

主键:film_id

【改进语句】

select * from film,(select film_id from film where length>200 limit 2000,30)  temp where film.film_id=temp.film_id;