您的位置: 首页  >  文章  >  MySql数据库索引、最左规则之联合索引的测试

MySql数据库索引、最左规则之联合索引的测试

分类: 文章 • 2023-04-03 12:56:32

****想要学会数据库索引，首先要了解联合索引之最左原则，什么是最左原则呢？
联合索引的命名规格：
表名_自定义索引名
首先最左原则要满足四种条件：
1，需要添加索引的字段，需要在where 条件中
2， 数量少的字段不需要添加索引
3，若where条件中是or关系，或者是（>, < ,between , like），比如：
建立索引：tab_index(a,b,c)
explain select *from user where a=3 and b>40 and c=5时候， 后面那个c是没有索引效果的，被b>40给终止掉。但是，当explain select *from user where a=3 and c=5 and b>40时候，a b c 都是有效果的。
MySql数据库索引、最左规则之联合索引的测试
MySql数据库索引、最左规则之联合索引的测试
数据库创建索引：
1，使用alter table 创建普通索引 ,unique,primary key索引
alter table table_name add index index_name (列名)
2，使用create index 可以对表增加普通索引或者unique索引
create index index_name on table_name(列名)
3 ，查看表中索引
show index from table_name

什么情况下不建立索引或者少建立索引：****
1，表记录少的时候
2，经常进行插入。、删除等操作时候
3，数据重复并且分布平均的字段
4，经常和主字段一起进行查询但是主字段索引值比较多的字段

先给一个问题：
数据库表T有A，B，C三个字段，对其建立联合索引uniq(A,B,C)，请问如下查询哪些会用走索引？

  1. SELECT * FROM T WHERE A=a AND B=b AND C=c; //走索引
  2. SELECT * FROM T WHERE A=a AND B=b; //走索引
  3. SELECT * FROM T WHERE A=a AND C=c; //不走索引 ，全表扫描
  4. SELECT * FROM T WHERE B=b AND C=c; //不走索引 全表扫描

大家都知道联合索引有最左原则。也就是说，如果联合索引的第一个列没有在WHERE语句中，或者所查询的列其中并没有在索引中被建立。那么，这个联合索引是无效的。
比如，上面的问题，这个索引可以被用于搜索如下所示的数据列组合：
A，B，C
A，B
A
其他无效果
MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照B或C来搜索，就不会使用到这个索引。
如果你搜索给定的A和C的组合，该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的A从而缩小搜索的范围

具体每个字段什么意思 看链接：https://www.cnblogs.com/yycc/p/7338894.html
MySql数据库索引、最左规则之联合索引的测试

下面进行联合测试： 点击链接
https://www.cnblogs.com/xiaoit/p/4430300.html
提供数据库数据：

INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2012-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2012-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2012-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2012-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2012-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2012-05-19 00:00:00’, ‘39’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2012-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2012-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2012-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2012-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2012-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2012-05-19 00:00:00’, ‘39’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2015-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2012-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2012-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2012-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2013-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2012-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2011-05-19 00:00:00’, ‘39’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2010-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2010-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2010-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2012-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2011-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2012-05-19 00:00:00’, ‘39’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2012-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2012-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2012-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2014-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2011-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2012-05-19 00:00:00’, ‘39’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2012-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2012-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2012-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2012-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2012-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2012-05-19 00:00:00’, ‘39’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2012-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2012-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2012-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2012-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2012-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2012-05-19 00:00:00’, ‘39’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2015-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2012-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2012-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2012-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2013-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2012-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2011-05-19 00:00:00’, ‘39’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2010-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2010-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2010-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2012-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2011-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2012-05-19 00:00:00’, ‘39’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘1’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘2’, ‘2012-05-13 00:00:00’, ‘23’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘3’, ‘2012-05-13 00:00:00’, ‘31’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘4’, ‘2012-05-13 00:00:00’, ‘32’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘5’, ‘2012-05-13 00:00:00’, ‘33’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘6’, ‘2012-06-13 00:00:00’, ‘34’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘7’, ‘2014-07-13 00:00:00’, ‘35’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘8’, ‘2012-08-13 00:00:00’, ‘36’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘9’, ‘2011-09-13 00:00:00’, ‘37’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘10’, ‘2012-05-17 00:00:00’, ‘38’);
INSERT INTO mybatis1.tab_index (id, dte, age) VALUES (‘11’, ‘2012-05-19 00:00:00’, ‘39’);

相关推荐