mysql中关于关联索引的问题——对a,b,c三个字段建立联合索引，那么查询时使用其中的2个作为查询条件，是否还会走索引？
情况描述：在MySQL的user表中，对a,b,c三个字段建立联合索引，那么查询时使用其中的2个作为查询条件，是否还会走索引？
根据查询字段的位置不同来决定，如查询a, a,b a,b,c a,c 都可以走索引的，其他条件的查询不能走索引。
组合索引 有“最左前缀”原则。就是只从最左面的开始组合，并不是所有只要含有这三列存在的字段的查询都会用到该组合索引。
验证过程如下所示：
首先，在SQLyog中建立一个user表，如下图所示；
对中间3个字段(user_name,user_age,user_password)进行联合索引 index_user_join
查询情况如下所示：
1.同时查询这3个字段作为条件的SQL，索引情况及SQL语句如下所示：
SELECT *FROM t_user WHERE user_name='zs' AND user_age=20 AND user_password='123456';
其使用索引情况如下所示：
从执行结果上可以看到是从走索引进行查询的
2.使用user_age和user_password作为查询条件进行查询，索引及SQL语句如下所示:
3.使用user_name和user_password作为查询条件进行查询，索引及SQL语句如下所示:
4.使用user_name作为查询条件进行查询，索引及SQL语句如下所示:
5.使用user_age作为查询条件进行查询，索引及SQL语句如下所示:
6.使用user_password作为查询条件进行查询，索引及SQL语句如下所示:
以上是针对普通的字段建立联合索引的测试情况及截图，欢迎小伙伴们来补充~

主键是数据库表的一个重要属性，建立主键可以避免表中存在完全相同的记录，也就是说主键在一张表中的记录值是唯一的。    建立主键有两种方法：一种是在数据库提供的GUI环境中建立，另一种是通过SQL语句执行建立，下面分别介绍。      1.在数据库提供的GUI环境中建立(以SQL7为例)。    输入表信息后按Ctrl键同时选中多行，然后点上面的主键按钮就行了。    2.通过SQL语句执行建立。又分两种，一是在建表语句中直接写，二是建表之后更改表结构。    在建表语句中直接写：      Create Table 表名 (字段名1 Int Not Null,
字段名2 nvarchar(13) Not Null Primary Key (字段名1, 字段名2),
字段名3…………
字段名N………… )    建表之后更改表结构：     CREATE TABLE 表名 (字段名1 Int Not Null,
字段名2 nvarchar(13) Not Null
字段名3…………
字段名N…………)
GO    ALTER TABLE 表名 WITH NOCHECK ADD
CONSTRAINT [PK_表名] PRIMARY KEY  NONCLUSTERED
(
[字段名1],
[字段名2]
)
GO
可以参考一下，相关的资料网上很多。
取消
评论

Mysql 建立联合索引时，我这边一般是基数小的排在前面，基数大的排在后面。『如果不对，欢迎指正，谢谢大家！』
今天后辈问起联合索引为何是技术小的排在前面？基数的排在后面？
想了很久，都不知怎么解释？
如果是你，你要怎么向后辈解释联合索引的字段顺序规则呢？
Tip: 网上找了很久，也没找到联合索引字段顺序说明的优质文章。。。
回答
联合索引的顺序，难道不是哪个查询条件最多用就放在前面的吗？
比如商品有三个分类A，B，C，类似「界门纲目科属种」那样，越左类别越大。还有一个是商品来源D，不一定会用于查询条件中。
举个例子，A代表衣服，B代表衣服下的长袖，C代表衣服下的红色的长袖。
如果这三个字段是商品的三个属性，并且要建立索引的话，必然是A,B,C的顺序，并且中间不应该有比如D字段(商品来源)
因为假设你的索引是这样建立的D,A,B,C，那么当你的查询条件中没有D字段的话，那么这个索引将不会被使用到。
假设2：索引是这样建立的A,B,D,C，当查询语句为：
select * from goods where A='衣服' and B='长袖' and C='红色';
虽然会使用到这个索引，但是这个索引只用到了A,B，因为D字段不作为查询条件，所以C字段也没有使用到。
这就是最左匹配原则的一种。
再多的，可以再去了解一下数据库索引的数据结构，以及其他最左匹配原则，这里列一下。
假设有这样一个索引——(a,b,c)，针对下面的5个关于最左原则的解释，做出举例。
1.必须用到索引的第一个字段，如select * from table where b=’b’ and c=’c’，不会使用到索引。
2.对于索引的第一个字段，用like时左边必须是固定值，通配符只能出现在右边，如select * from table where a like ‘%a%’不会使用到索引，而select * from table where a like ‘a%’则会使用到索引。
3.如果在字段前加了函数或在字段前嵌入了表达式，则不会使用索引。如select * from table where trim(a)>1不会使用索引，select * from table where a+1>1也不会使用索引，而select * from table where a>2会使用到索引。
4.索引的使用与where语句中字段出现的顺序无关，只与是否使用到索引相关字段有关。如select * from table where b=’b’ and a=’a’也会使用索引。
5.使用到索引第一个字段的不等于表达式，即使其他字段不是索引字段也会使用到索引。如select * from table where a=’a’ and ddd=’ddd’会使用到索引。
上述引用文字来源于丁奇专栏《MySQL实战45讲》

目录

1.什么是覆盖索引？

2.什么是联合索引？

2.1 联合索引的顺序要注意什么？

3. 索引下推优化？

总结：

最左原则在like中也可以加速检索。

Q: 这个问题蛮有意思的，在什么时候建立了联合索引(a,b)但是却需要单独建立一个冗余索引a呢？

Q： 一个字段只有0/1怎么加快搜索？

Q: 加入索引的缺点？

Q:如果知道了a还是b那个查询其来块，那么使用强制索引。

Q：创建表的时候创建索引

Q: 为什么有时候需要重建索引？

Q:重建索引的语句？

Q:MRR是什么？

Q:使用between有时候比in所有的范围要快！！

Q:ICP索引下推的一个实例问题？

前言：

索引的建立很重要，对于联合索引的顺序的考究，还需要更多的实战才能去体会。

1.什么是覆盖索引？

在去索引表搜索的时候，想要查询的信息就在二级索引树上面，

这个就叫做覆盖索引。

可以不用回表查询，可以提高查询性能

2.什么是联合索引？

联合索引就是多个条件列 组合的索引

因为索引是B+树，那么最早找的最左面的索引，来确定节点。

然后在去根据下一个条件b 确定下一个方向，

所以是一步一步的锁定的~

2.1 联合索引的顺序要注意什么？

注意的点是空间上的问题，字段的大小问题。

注意原则：联合索引范围查询的话，尽量放在最后面。(范围查询如果放在最前面性能会很差)

索引最左列最好还是使用选择性高的列！！特征点明显的

都是等值查询的话，那么无所谓索引顺序也可以进行命中。

(但是有了索引的下推，这个顺序也不需要在意咯~)

比如范围查询select * from table where a=xx,b>xx;

那么如果是(a,b)效率会很高。但是如果是(b,a)就相当于只有范围用到了索引，a是没法用到索引的必须回表查询。

 

3. 索引下推优化？

在mysql5.6之前，如果 建立了联合索引（name, city）

select * from tuser where name like '朱%' and sex =1 and city = "beijing";

那么根据前缀索引会检索出来姓朱的找到第一个符合条件的索引对应的数据页，然后去数据页里面遍历，找到每一个的索引带的主键的值，回表遍历去。

但是在5.6之后，出现了索引下推。（index condition pushdown）

对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

嗯其实就是在查询的时候，判断联合索引中的city，符不符合beijing

然后不符合就不回表查询了~

这就是重要的索引下推~

总结：

最左原则在like中也可以加速检索。

like查询的时候，比如：你要查的是所有名字第一个字是“朱”的人，你的 SQL 语句的条件是"where name like ‘朱%’"那么如果给这个字段加上索引也是可以用到的。因为快速定位到这个索引，然后去数据页去遍历，知道不满足条件的为止。

索引的最左前缀原则，可以加速检索。这个最左原则可以是联合索引的最左的N个字段，也可以是索引的最左M个字符。

Q: 这个问题蛮有意思的，在什么时候建立了联合索引(a,b)但是却需要单独建立一个冗余索引a呢？

这个问题根据高性能mysql中说的，是因为加上了联合索引b导致了条件只有a的时候，查询速度变慢很多。

我猜可能是因为B的站的字节数很大，导致了索引树很大，所以哪怕是冗余，也需要解决这个问题。

Q： 一个字段只有0/1怎么加快搜索？

一般都不会对这种字段建立索引。

但是呢，如果0和1这种分布很不均匀的情况，比如0 有100条，但是1有1w条，我给这个字段建立索引就会快很多。

这样的话，就可以直接定位到0所在的数据页。如果像男女一半对一半，那么就索引没什么用。

Q：查询条件的先后顺序有很大的作用么？


看大佬们的回答，是看MYSQL版本的。

从某个版本之后，条件顺序对索引的选取没有影响了。

查询优化器会优化执行计划。

 

因为是执行sql语句的时候，mysql是有查询优化器的。

查询优化器会将sql 进行优化，选择最优的查询计划来执行


Q: 加入索引的缺点？

增加占用的空间，降低插入的速度。

Q:如果知道了a还是b那个查询其来块，那么使用强制索引。

如果经常有这种查询， 建议建个a 和 b的联合索引，是最有效的优化。

不建联合索引，两列又都有索引时，mysql会有采取自己的算法去选区最快点索引，

如果你很清楚，从a还是b查起来更快， 可以使用强制索引FORCE INDEX (FIELD1)

多用 explain去观察， 优化sql

TIPS:如果数据库的字段长度很固定，使用char类型不要使用varchar 会速度快一点~

Q：创建表的时候创建索引

使用key关键字~

PRIMARY KEY `a`, KEY `c` (`c`)

这个c就是索引咯~

例如：


CREATE TABLE 表名(字段名 数据类型 [完整性约束条件], [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名](字段名1 [(长度)] [ASC | DESC]) );


Q: 为什么有时候需要重建索引？

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。

Q:有的表数据需要删除，但是索引数据确还在。这时候使用语句alter table T engine=InnoDB

重新建表才能重建索引，并不能通过清除数据释放。

Q:重建索引的语句？

alter table T drop index k;alter table T add index(k);

Q:MRR是什么？

MRR 索引多范围查找，https://www.cnblogs.com/xibuhaohao/p/10796113.html

如果基表很大，数据没有被缓存，在二级索引上使用范围扫描读取行可能会导致大量的随机磁盘访问。使用Multi-Range Read新特性，mysql可以减少对磁盘的随机读的次数：首先，mysql只是扫描索引，收集相关行的keys；然后，将收集到的keys进行排序；最后通过有序的主键去访问基表。

Q:使用between有时候比in所有的范围要快！！

1.select * from T where k in(1,2,3,4,5)

2.select * from T where k between 1 and 5

第一个语句要搜索树5次，第二个搜索一次。

Q:ICP索引下推的一个实例问题？

“a > 5 and a < 10 and b='123'”在ICP作用下的执行过程是什么样子的？

   a) 把 a>5 and b='123'传入引擎，

   b)引擎找到第一个a>5的行（这里是快速定位），如果发现b<>'123',找下一个，直到满足b='123',

  c)把找到的行返回给server层， server层根据a是否小于10决定要不要取下一个

也就是server层把a>5执行一次，a>6执行一次。。。。