mysql数据库查询优化,该从哪几个方面进行优化?

当mysql数据库中数据越来越多的时候,或许我们会发现查询的效率越来越低了,可能低到令人发指的地步,仅仅查询几条数据就要要十几秒甚至几十秒钟,这是十分致命的,如果用户访问你的网站,十几秒过去了数据都加载不出来,那么造成的用户流失是不可想象的。

于是乎,我们就要分析是什么造成了数据库查询效率低下。
一般来讲,造成这种结果有下面这几个原因:
1,sql语句与索引
2,表结构
3,储存引擎
4,网卡流量
5,服务器硬件
6,磁盘IO

本文中我们主要针对sql与索引进行优化,因为这是成本最低,效果最好的优化方案(当然,如果钱多,升级升级硬件比什么优化都好使)

1,使用表连接代替子查询,首先表连接不会再内存中建立临时表,其次,例如在where中使用子查询,会增加数据查询的次数。

2,在编写sql语句的时候,一定要注意函数、运算符或关键字的用法,因为可能会导致索引失效,导致全表扫描,使查询效率大打折扣。具体可以参考该链接中的文章,有比较详细的解读,我这里就不重复了https://www.cnblogs.com/jett010/p/4826655.html

3,在搜索字符型字段时,我们有时会使用LIKE关键字和通配符,这种做法虽然简单,但却也是以牺牲系统性能为代价的,能不用尽量不用,如果要用,就在有索引的字段上用。

4,避免在查询中让MySQL进行自动类型转换,因为转换过程也会使索引变得不起作用,最好是在相同类型的字段间进行比较的操作。

5,使用union来代替手动创建临时表,它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效

6,进行锁定表的操作,事务肯定是mysql中不可缺少的,这是数据安全的基本保障,但是事务机制是对数据库的锁定,这会造成高并发情况下,用户的访问响应延迟,带来不好的用户体验,所以我们可以手动将锁定范围缩小,进行表锁定

LOCK TABLE inventory WRITE SELECT quantity  FROM   inventory   WHERE Item='book';

...

UPDATE   inventory   SET   Quantity=11   WHERE  Item='book';UNLOCKTABLES

7,索引优化,索引应该是mysql提高查询速度最常用的一种办法了,我们应该在哪些字段建立索引呢?
因为场景不同要求也不同,这时候可以查看慢查询日志,选取合适字段建立索引,一般情况下,索引使用原则:
经常被查询的高频字段需要建立索引;
不要在大字段中创建索引,例如text类型的字段;
使用索引之后,查询数量量占总表数据量的3%-5%是最能发挥索引效果的一个范围;
在联合索引中,一定要遵循左前缀元组,否则索引失效。
想知道自己写的sql语句是否用到了索引,可以用explain关键字查看一下

8,切分sql语句,将逻辑复杂,又长关联又多的sql语句拆分成多个单个查询的小的sql语句,这样子既可以让缓存的效率更高,又可以减少锁表锁行的时间

最后借用一句话总结一下sql语句的优化:
首先定位应用程序内哪些sql语句会比较慢,我们可以通过慢查询日志,show profiles ,show processlist, 然后通过explain来查询这条或这些条sql语句为什么会慢,然后进行一系列的优化。
优化,首先从索引着手,然后从数据访问,长难查询语句,以及特定类型的查询语句来进行优化。

课程大纲: 第1课 数据库与关系代数 综述数据库、关系代数、查询优化技术 综述数据库调优技术 预计时间1小时 第2课 数据库查询优化技术总揽 综述查询优化技术范围,包括查询重用、查询重写规则、查询算法优化、并行查询优化等 综述逻辑查询优化,包括子查询优化、视图重写、等价谓词重写、条件化简、连接消除、非SPJ的优化等 综述逻辑物理优化,包括单表扫描算法、两表连接算法、多表连接算法、基于代价的算法等 初步理解MySQL查询执行计划。 预计时间1小时 第3课 查询优化技术理论与MySQL实践(一)------子查询优化(一) 第4课 查询优化技术理论与MySQL实践(二)------子查询优化(二) 从理论看,子查询包括的内容和范围,建立清晰的概念 从实践看,MySQL的子查询优化技术的内容和范围,明确掌握子查询优化手段 预计时间2小时,每小时一个课程段(子查询是SQL查询优化的重点内容,务必掌握好) 第5课 查询优化技术理论与MySQL实践(三)------视图重写与等价谓词重写 什么是视图重写?哪些类型的视图可以被优化MySQL是怎么优化视图的?从而明白在MySQL中怎么写与视图相关的查询语句才能有好的效果? 什么是等价谓词重写?MySQL中怎么写WHERE子句有利于提高查询效率? 预计时间1小时 第6课 查询优化技术理论与MySQL实践(四)------条件化简 什么是条件化简?MySQL中对什么样的条件自动进行优化?如何写出可利用索引的条件语句? 预计时间1小时 第7课 查询优化技术理论与MySQL实践(五)------外连接消除、嵌套连接消除与连接消除 连接方式有些什么类型?不同类型的连接又是怎么优化的?外连接优化的条件是什么?MySQL中怎么写出可优化的连接语句?MySQL是否支持嵌套连接消除?MySQL是否支持连接消除?MySQL中书写SQL连接查询语句时的优化技巧。 预计时间1小时 第8课 查询优化技术理论与MySQL实践(六)------数据库的约束规则与语义优化 数据库的参照完整性(CHECKt NULL等)。什么是语义优化MySQL是否支持语义优化?怎么利用语义优化的思路人工进行SQL语句的优化? 预计时间1小时 第9课 查询优化技术理论与MySQL实践(七)------非SPJ的优化 什么是非SPJ优化? 从理论看,GROUP BY、ORDER BY、LIMIT、DISTINCT等怎么被优化MySQL中:GROUP BY是怎么优化的?ORDER BY是怎么被优化?LIMIT是怎么被优化?DISTINCT是怎么被优化? 非SPJ优化与索引的关系。 预计时间1小时 第10课 MySQL物理查询优化技术概述 从理论看,物理查询优化技术的范围。 从MySQL实践看,怎么利用物理查询优化技术对SQL查询语句调优? 本节预计会承接第9课的部分内容。 预计时间1小时 第11课 MySQL索引的利用、优化MySQL索引的角度出发,看各种SQL查询语句的优化怎么进行?(以前都是从语句的角度看怎么优化,现在站在索引的角度去总结SQL查询语句的优化) 预计时间1小时 第12课 表扫描与连接算法与MySQL多表连接优化实践 MySQL的单表扫描算法。MySQL的两表连接算法。MySQL的多表连接算法。 MySQL的多表连接的优化技巧。 预计时间1小时 第13课 查询优化的综合实例(一)------TPCH实践(一) 第14课 查询优化的综合实例(一)------TPCH实践(二) 以TPC-H国际标准的22条查询语句为实例,综合前面课程的内容,把所学的知识用于实践,进行综合的实战演练。 预计时间2小时(每个课时为1个小时) 第15课 关系代数对于数据库查询优化的指导意义------查询优化技术总结 再次回到理论,从理论的高度总结关系代数理论与MySQL查询优化实践的关系。真正认识、掌握MySQL查询优化技术,大步流星步入查询优化的高手之列。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值