Mysql各种情景的实例

最新推荐文章于 2021-01-28 03:34:59 发布

weixin_33787529

最新推荐文章于 2021-01-28 03:34:59 发布

阅读量187

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/WEguo/blog/1572524

版权

为什么80%的码农都做不了架构师？>>>

create table Trade_Appay(
id INT NOT NULL AUTO_INCREMENT,

name varchar(10) NOT NULL,
deleted INT NOT NULL,--是否删除，这个字段是预留字段，其实真实项目中是物理删除，字段都为0
checked INT NOT NULL,--是否审核，这个字段在实际应用中多数都是0（已审核），很少数为1。
product_id INT NOT NULL,--产品主键，购买产品的id，我们这里假设有40个产品(0~39)。
SEANO INT NOT NULL,--销售机构，在哪个销售机构购买的产品，我们假设有5个销售机构(0~4)
PRIMARY KEY ( id )
);

好了，我们这是我们的销售申请表，上面已经给出了我们实际场景，我们造1100w条数据。

1、选择性是否重要

有时候我们确实遇到过这种问题那就是有一些字段确实是全是一个数值，在后面查询条件我们也加上了它，实际上我们需要给他加索引吗？加索引会使查询更快吗？

为了不影响测试结果，我们建立Trade_Appay1，数据全部一样，并为其加了联合索引INDEX index1(deleted,checked,product_id,SEANO)

根据最左前缀，我们加上了deleted的索引，我们看看加了索引和没有加索引的区别

当字段值全为统一值加索引

没加deleted的索引

执行语句时间为：8.54s.

加了deleted的索引

时间为：56.54s.

可以看出，当使用索引的情况下，竟然比全表扫描还要慢。这与回表有一定的关系，但是否是全部的关系，我并不能给出完整全面的答案，这个结果也是出乎我的意料之外。

当统一值之后的列是否会有影响

当我们发现我们使用deleted这个统一列的时候，后面的列索引是否还有效果。checked前面说过，只有很少部分为1的值。我们来看一下效果。

没有索引时

有索引时

所以说，在用deleted中使用了deleted字段的时候，查找checked使用索引效果还是很明显的。

选择性对比

我们根据product_id，seano_id的选择行进行对比，trade_appay2(product_id，seano_id),trade_appay3(seano_id，product_id)进行了对比。

index(product_id，seano_id)

index(seano_id，product_id)

我们可以看到，当选择性高的放在前面，在数据量高的情况下，效果要比选择性低的要好。

2、索引列是否越多越好

原理上说，索引列并不是越多越好，因为索引也需要存储，越多的列就会需要更多的存储空间，当表数据发生变化时，索引也会进行更新，那么更新索引的代价就会很高。

无用的更多列会影响查询

我们在表trade_appay1的时候，我们用到了的时候，我们用到了index1(deleted,checked,product_id,SEANO)，那么我们建立表trade_appay4，index1(deleted,checked,product_id)来进行一个实验，在实验中我们假设seano是一个无用的列。

场景：查询表中未删除、已审核、产品、id为25的申请数据，查询的数据中包含并根据未删除、已审核、产品、id进行排序。

可以看到，使用了索引，但是在排序的时候使用的是外部索引排序（using filesort），执行时间为。

可以看到，使用了索引，而且没有使用外部索引排序，使用的是内部，执行时间为。

为什么我们索引里面没有id这列，却显示了使用了覆盖索引(using index)?

因为mysql建立索引时，会在索引的最后加上主键，所以这两个查询显示的字段均在索引里，所以都会是覆盖索引。但是表1的索引为(deleted,checked,product_id,SEANO)，然而SEANO在order by中并没有用上，之后的id也就无法使用了，所以就需要使用外部索引来进行排序。当建立索引时，也一定要考虑order by和group by。

3、where 条件是否会影响索引排序

我们还是以表4做实验(deleted,checked,product_id)

1、where条件中只存在索引列。

我们之前说过，当where条件中有索引的前缀列，order by 后可以不用加此列。我们在where 条件中使用了deleted，checked列，order by根据product_id，id进行排序，我们发现使用了覆盖索引，而且没有使用外部索引。耗费时间为：（这里为什么这么满，解释一下，因为deleted列里全是0，而checked里110w条数据只有10条是1，其他都是0，所以checked=0的时候几乎相当于全表扫描）

2、where条件中使用了索引外的条件

我们发现，当where条件中使用了索引外的条件，没有使用覆盖索引，所用时间为

3、where条件中有索引列的其他列

我们在这里使用了(deleted,checked,product_id，seano)索引，当where条件有seano条件，但是没有product_id条件，发现排序是覆盖索引，并且没有使用外部索引，所用时间为。

4、刚才1中给出的结论，我们在排序中去掉了3中的seano，情况如下

在where条件中有seano，没有product_id的情况下，依旧是覆盖索引，并且没有使用外部排序。

5、在where条件中缺少索引列，并在where条件中无此索引排序。

我们可以看到，此时where条件中缺少product_id，排序中也没有product_id，结果是使用了覆盖索引，但是排序却是使用的外部排序。

所以说索引如何建立，跟where条件、排序、group by有这密切的关系。

4、between 和 in 在索引中的区别和比较

select deleted,checked,product_id,seano,id FROM trade_appay1 where deleted=0 and checked=0 and product_id in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);

select deleted,checked,product_id,seano,id FROM trade_appay1 where deleted=0 and checked=0 and product_id BETWEEN 0 and 20 ;

select deleted,checked,product_id,seano,id FROM trade_appay1 where deleted=0 and checked=0 and product_id in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and SEANO=1;

select deleted,checked,product_id,seano,id FROM trade_appay1 where deleted=0 and checked=0 and product_id BETWEEN 0 and 20 and SEANO=1;

上面这些语句我们发现，的确在使用between and 组合的时候会比in效率高，没什么问题。但是两者虽然type都是range，但是有本质上的不同，使用in可以在后面继续使用索引，而between and 不可以，所以这就是为什么后面加上seano条件后，使用in比使用between and 快一秒的原因。

转载于:https://my.oschina.net/WEguo/blog/1572524