什么是索引?为什么要建立索引?
索引用于快速找出在某个列中有一特定值的行,不使用索引,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已经设置了索引,但就是不能命中。
解决过程
然后我将上面的语句分为两个语句。首先将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。(不查不知道,发现一个数据库中,很多表的字符集都不相同)
修改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分析一下,可以看到都用上了索引。
然后运行,查询了0.112秒。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对的支持。
更新日志
- 张心杰2011-打开心杰[EMI百代][WAV+CUE]
- 《暗喻幻想》IGN 9分:出色地完善了Atlus的RPG公式
- 苏格兰雨林发现罕见僵尸真菌 与《最后生还者》设定相似
- 曝多款《战神》重制版游戏正在开发:或于明年3月官宣
- 周葆元.1994-总有一天你会懂【飞碟】【WAV+CUE】
- 群星.1992-妖兽都市电影原声帶【滚石】【WAV+CUE】
- 林忆莲.1996-回忆总是美好的超级精丫飞碟】【WAV+CUE】
- 好舒服宝宝《好舒服宝宝音乐, Vol.5》[FLAC/分轨][71.21MB]
- 宝藏男孩《超越期待》[320K/MP3][88.88MB]
- 宝藏男孩《超越期待》[FLAC/分轨][247.62MB]
- 陈随意2009-爱你一世人[美华][WAV+CUE]
- 风信子1992-以为你在等我这样的人[歌林][WAV+CUE]
- 高胜美高胜云.2002-山地思情篇【皇星全音】【WAV+CUE】
- 逼迫中国选手弃权事件爆发后 万代官号发水豚君仰望天空
- 国外玩家恶搞奎爷去中国杀神:遇到孙悟空揍二郎神