欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL中因字段字符集不同导致索引不能命中的解决方法

程序员文章站 2023-08-13 11:01:40
什么是索引?为什么要建立索引? 索引用于快速找出在某个列中有一特定值的行,不使用索引,mysql必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的...

什么是索引?为什么要建立索引?

索引用于快速找出在某个列中有一特定值的行,不使用索引,mysql必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,mysql能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

例如:有一张person表,其中有2w条记录,记录着2w个人的信息。有一个phone的字段记录每个人的电话号码,现在想要查询出电话号码为xxxx的人的信息。

如果没有索引,那么将从表中第一条记录一条条往下遍历,直到找到该条信息为止。

如果有了索引,那么会将该phone字段,通过一定的方法进行存储,好让查询该字段上的信息时,能够快速找到对应的数据,而不必在遍历2w条数据了。其中mysql中的索引的存储类型有两种:btree、hash。 也就是用树或者hash值来存储该字段,要知道其中详细是如何查找的,就需要会算法的知识了。我们现在只需要知道索引的作用,功能是什么就行。

引言

今天写了一个sql,其中涉及的表中的数据量都差不多为50w左右,查询发现用了8s。这个只是测试服上数据,放到正式服上,肯定一运行就挂了。

select
 orders. no,
 guidno,
 orders.createtime,
 sum(orderitem.quantity) as quantity,
 brand. name as brandname,
 member.mobile,
 street as deliveryaddress,
 area
from
 orders
inner join orderitem on orders.guidno = orderitem.orderguidno
inner join brand on brand.id = orders.brandid
inner join member on member.id = 13
inner join memberaddress on member.id = memberaddress.memberid
where
 orders.guidno in (
  select
   orderpayment.orderguidno
  from
   paymentrecord
  left join orderpayment on paymentrecord.`no` = orderpayment.paymentno
  where
   paymentrecord.paymentmethod = 'membercard'
  and paymentrecord.payer = 13
 )
group by
 guidno;

然后就用explain分析了一下,发现orders表没有命中索引,但是查询orders中的guidno已经设置了索引,但就是不能命中。

MySQL中因字段字符集不同导致索引不能命中的解决方法

解决过程

然后我将上面的语句分为两个语句。首先将sql语句修改为:将子查询的数据直接写在了sql中,查询用了0.12s。

select
 orders. no,
 guidno,
 orders.createtime,
 sum(orderitem.quantity) as quantity,
 brand. name as brandname,
 member.mobile,
 street as deliveryaddress,
 area
from
 orders
inner join orderitem on orders.guidno = orderitem.orderguidno
inner join brand on brand.id = orders.brandid
inner join member on member.id = 13
inner join memberaddress on member.id = memberaddress.memberid
where
 orders.guidno in (
  '0a499c5b1a82b6322ae99d107d4da7b8',
  '18a5ee6b1d4e9d76b6346d2f6b836442',
  '327a5ae2bacea714f8b907865f084503',
  'b42b085e794ba14516ce21c13cf38187',
  'fbc978e1602ed342e5567168e73f0602'
 )
group by
 guidno

第二个:单独运行子查询的sql,也才用了0.1s

select
   orderpayment.orderguidno
  from
   paymentrecord
  left join orderpayment on paymentrecord.`no` = orderpayment.paymentno
  where
   paymentrecord.paymentmethod = 'membercard'
  and paymentrecord.payer = 13

这么问题就清晰了,一定是子查询和父查询关联的问题。因为子查询单独很快,父查询用子查询数据直接查的时候也很快,就是在他俩结合的时候很慢。大致能将问题锁定在这两个关联字段orderguidno上。

最后发现 orderpayment 表和 orders 表字符集是不同的。一个表的字符集是:utf8_general_ci,一个是:utf8mb4_general_ci。(不查不知道,发现一个数据库中,很多表的字符集都不相同)

MySQL中因字段字符集不同导致索引不能命中的解决方法

修改orderpayment表的字符集和表中orderguidno的字符集为:utf8_general_ci

alter table orderpayment default character set utf8 collate utf8_general_ci; //修改表的字符集

alter table orderpayment change orderguidno orderguidno varchar(100) character set utf8 collate utf8_general_ci; //修改字段的字符集

然后在用explain分析一下,可以看到都用上了索引。

MySQL中因字段字符集不同导致索引不能命中的解决方法

然后运行,查询了0.112秒。

MySQL中因字段字符集不同导致索引不能命中的解决方法

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对的支持。