MYSQL单列索引和组合索引分析

概念：

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
创建语句如下：

/*==============================================================*/
/* Index: application_id                                        */
/*==============================================================*/
create index application_id on crm_course_time_list
(
   application_id
);

   
   

   
   
1
2
3
4
5
6
7

组合索引：即一个索包含多个列。
创建语句如下：

create index union_index on crm_course_time_list
(
   application_id,
   begin_time,
   week_item
);

   
   

   
   
1
2
3
4
5
6

怎么选择：

• 如果我们的查询where条件只有一个，我们完全可以用单列索引，这样的查询速度较快，索引也比较瘦身。
• 如果我们的业务场景是需要经常查询多个组合列，不要试图分别基于单个列建立多个单列索引（因为虽然有多个单列索引，但是MySQL只能用到其中的那个它认为似乎最有效率的单列索引）。这是因为当SQL语句所查询的列，全部都出现在复合索引中时，此时由于只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多。
  我们先创建一个简单的表，举例说明下：

/*==============================================================*/
/* Table: crm_course_time_list                                  */
/*==============================================================*/
create table crm_course_time_list
(
   dr  tinyint(2) NULL DEFAULT 1 COMMENT '是否删除 1:正常 2:删除' ,
   use_status  tinyint(2) NULL DEFAULT 1 COMMENT '使用状态 1:正常 2:封存' ,
   create_date  datetime NULL DEFAULT NULL COMMENT '创建时间' ,
   create_user  int NULL DEFAULT NULL COMMENT '创建人' ,
   update_date datetime NULL DEFAULT NULL COMMENT '修改时间' ,
   update_user  int NULL DEFAULT NULL COMMENT '修改人' ,
   id                   char(32) not null comment '主键',
   application_id       char(32) comment '关联申请记录',
   begin_time           char(5) comment '申请开放的时段的开始时间',
   end_time             char(5) comment '申请开放的时段的结束时间',
   week_item            tinyint comment '申请开放的时段的星期',
   primary key (id)
);

   
   

   
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

上面表中的week_item是星期好，begin_time是开始时间，end_time是结束时间，如果现在表中存在10000条记录。

1. 我们现在要找出星期五（按照日期的习惯周五=6）的所有数据，where条件如下：

where week_item = 6

   
   

   
   
1

如果我们不加索引的情况下，那么MySQL会扫描10000条记录取去找相应的记录。
我们在week_item上增加单列索引的话，MySQL将通过这个索引迅速把搜索范围限制到那些week_item = 6的记录，然后再在这个“中间结果集”上进行其他条件的搜索，因为我们这里没有其他的搜索条件，所以直接就返回结果集，那么此时的查询效率是非常快的。

2. 更换搜索条件：我们现在要找出星期5，并且开始时间是09:00,结束时间是10:00的结果集，where条件如下：

where week_item = 6 and begin_time = '09:00' and end_time = '10:00'

   
   

   
   
1

我们现在week_item上存在索引，所以MySQL将通过这个索引迅速把搜索范围限制到那些week_item = 6的记录，然后再在这个“中间结果集”上进行其他条件的搜索，它首先排除那些begin_time 不等于“09:00”的记录，然后排除那些end_time 不等于“10:00”的记录。当记录满足所有搜索条件之后，MySQL就返回最终的搜索结果。
由于建立了week_item列的索引，与执行表的完全扫描相比，MySQL的效率提高了很多，但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。所以对剩下的结果集还是需要进行全部扫描比较，那么如果此时很多人说我们可以在begin_time和 end_time上再增加单列索引，那么这就涉及到我们上面说的了：因为虽然有多个单列索引，但是MySQL只能用到其中的那个它认为似乎最有效率的单列索引，所以其实这个时候只会用到一个索引。
为了提高搜索效率，我们需要考虑运用多列索引。如果为week_item 、begin_time 和end_time 这三个列创建一个多列索引，MySQL只需一次检索就能够找出正确的结果!下面是创建这个多列索引的SQL命令：

create index union_index on crm_course_time_list
(
   week_item，
   begin_time,
   end_time 
);

   
   

   
   
1
2
3
4
5
6

由于索引文件以B+树格式保存，MySQL能够立即转到合适的week_item，然后再转到合适的begin_time，最后转到合适的end_time 。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录!
那么，如果在week_item、begin_time、end_time 这三个列上分别创建单列索引，效果是否和创建一个week_item、begin_time、end_time
的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于week_item、begin_time、end_time
这三个列上的多列索引。

谨防最左前缀索引失效问题

继续考虑前面的例子，现在我们有一个week_item、begin_time、end_time 列上的多列索引，我们称这个索引为union_index 。它相当于我们创建了(week_item，begin_time，end_time )、(week_item，begin_time)以及(week_item)这些列组合上的索引。为什么没有 (begin_time,end_time )等这样的组合索引呢？这是因为 mysql 组合索引"最左前缀"（Leftmost Prefixing）的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。
举几个简单的列子：

select * from crm_course_time_list where week_item = 6 and begin_time = '09:00' and end_time = '10:00'
select * from crm_course_time_list where week_item = 6 and begin_time = '09:00' 
select * from crm_course_time_list where week_item = 6 

   
   

   
   
1
2
3

这些是会用到组合索引的。

select * from crm_course_time_list where  begin_time = '09:00' and end_time = '10:00'
select * from crm_course_time_list where  begin_time = '09:00' 
select * from crm_course_time_list where end_time = '10:00'

   
   

   
   
1
2
3

这些是不会用到组合索引的，会直接全表扫描

select * from crm_course_time_list where  begin_time = '09:00' or end_time = '10:00'

   
   

   
   
1

and换成or索引无效

select * from crm_course_time_list where begin_time = '09:00' and  week_item = 6 

   
   

   
   
1

调整查询顺序，索引有效

结论：

所以我们在查询的时候，如果联合索引的第一个列不在where条件语句中，索引是不会用到的，where条件也要尽量根据联合索引的顺序来，如果不按照顺序来，索引也同样会用到，但是在执行前，SQL优化器也会将条件调整为联合索引的顺序，既然可以直接避免这种情况，就没必要再让SQL优化器去处理，毕竟处理也是有开销的

建立索引的时机：

MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引，所以对于什么情况下需要建立索引就有讲究了。
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引

SELECT * FROM crm_course_time_list WHERE begin_time like '09%' 

   
   

   
   
1

这个时候索引有效

SELECT * FROM crm_course_time_list WHERE begin_time like '%12' 

   
   

   
   
1

注意事项：

• 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
• 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
• MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
• 一般情况下不鼓励使用like操作
• 不要在列上进行运算，会导致索引失效
• 不使用NOT IN和<>操作

                                </div>
            <link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-e44c3c0e64.css" rel="stylesheet">
                </div><div id="article_content" class="article_content clearfix" data-report-click="{&quot;mod&quot;:&quot;popu_307&quot;,&quot;dest&quot;:&quot;https://blog.csdn.net/zhuwei_clark/article/details/84944830&quot;}">
                                <link rel="stylesheet" href="https://csdnimg.cn/release/phoenix/template/css/ck_htmledit_views-3019150162.css">
                                    <div id="content_views" class="markdown_views prism-atom-one-dark">
                <!-- flowchart 箭头图标 勿删 -->
                <svg xmlns="http://www.w3.org/2000/svg" style="display: none;">
                    <path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path>
                </svg>
                                        <h2><a name="t0"></a><a id="_0"></a><strong>概念：</strong></h2>

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
创建语句如下：

/*==============================================================*/
/* Index: application_id                                        */
/*==============================================================*/
create index application_id on crm_course_time_list
(
   application_id
);

 
 

 
 
1
2
3
4
5
6
7

组合索引：即一个索包含多个列。
创建语句如下：

create index union_index on crm_course_time_list
(
   application_id,
   begin_time,
   week_item
);