下载鸥 > 网站下载 > 开发教程 > Mysql数据库

如何写出高性能SQL

235 2020-11-26 15:46:41

收藏

简述

sql的性能高低与网站加载速度成正比,如何写出高性能的sql是我们不得不考虑的问题。
定量遍历次数

在编写一条查询语句的时候,可以依据你要查询数据表的数据总量,估算一下这条查询大致需要遍历多少行数据:

如果遍历行数在百万以内的,只要不是每秒钟都要执行几十上百次的频繁查询,可以认为是安全的。

遍历数据行数在几百万的,查询时间最少也要几秒钟,你就要仔细考虑有没有优化的办法。

遍历行数达到千万量级和以上的,我只能告诉你,这种查询就不应该出现在你的系统中。当然我们这里说的都是在线交易系统,离线分析类系统另说。
 

遍历行数在千万左右,是 MySQL 查询的一个坎儿。MySQL 中单个表数据量,也要尽量控制在一千万条以下,最多不要超过二三千万这个量级。原因也很好理解,对一个千万级别的表执行查询,加上几个 WHERE 条件过滤一下,符合条件的数据最多可能在几十万或者百万量级,这还可以接受。但如果再和其他的表做一个联合查询,遍历的数据量很可能就超过千万级别了。所以,每个表的数据量最好小于千万级别。
 

避免全表索引

绝大多数情况下,我们编写的查询语句,都应该使用索引,避免去遍历整张表,也就是通常说的,避免全表扫描。你在每次开发新功能,需要给数据库增加一个新的查询时,都要评估一下,是不是有索引可以支撑新的查询语句,如果有必要的话,需要新建索引来支持新增的查询。
 

增加索引付出的代价是,会降低数据插入、删除和更新的性能。这个也很好理解,增加了索引,在数据变化的时候,不仅要变更数据表里的数据,还要去变更每个索引。所以,对于更新频繁并且对更新性能要求较高的表,可以尽量少建索引。而对于查询较多更新较少的表,可以根据查询的业务逻辑,适当多建一些索引。
 

分析 SQL 执行计划

在 MySQL 中使用执行计划也非常简单,只要在你的 SQL 语句前面加上 EXPLAIN 关键字,然后执行这个查询语句就可以了。

比如有一个用户表,包含用户 ID、姓名、部门编号和状态这几个字段:

我们希望查询某个二级部门下的所有人,查询条件就是,部门代号以 00028 开头的所有人。下面这两个 SQL,他们的查询结果是一样的,都满足要求,但是,哪个查询性能更好呢?

SELECT * FROM user WHERE left(department_code, 5) = '00028';SELECT * FROM user WHERE department_code LIKE '00028%';
 

我们分别查看一下这两个 SQL 的执行计划:

row 列:

MySQL 预估执行这个 SQL 可能会遍历的数据行数。第一个 SQL 遍历了四千多行,这就是整个 User 表的数据条数;第二个 SQL 只有 8 行,这 8 行其实就是符合条件的 8 条记录。显然第二个 SQL 查询性能要远远好于第一个 SQL。

type 列:

表示这个查询的访问类型。ALL 代表全表扫描,这是最差的情况。range 代表使用了索引,在索引中进行范围查找,因为第二个 SQL 语句的 WHERE 中有一个 LIKE 的查询条件。如果直接命中索引,type 这一列显示的是 index。如果使用了索引,可以在 key 这一列中看到,实际上使用了哪个索引。
 

总结

在开发阶段,衡量一个 SQL 查询语句查询性能的手段是,估计执行 SQL 时需要遍历的数据行数。遍历行数在百万以内,可以认为是安全的 SQL,百万到千万这个量级则需要仔细评估和优化,千万级别以上则是非常危险的。为了减少慢 SQL 的可能性,每个数据表的行数最好控制在千万以内。 索引可以显著减少查询遍历数据的数量,所以提升 SQL 查询性能最有效的方式就是,让查询尽可能多的命中索引,但索引也是一把双刃剑,它在提升查询性能的同时,也会降低数据更新的性能。 对于复杂的查询,最好使用 SQL 执行计划,事先对查询做一个分析。在 SQL 执行计划的结果中,可以看到查询预估的遍历行数,命中了哪些索引。执行计划也可以很好地帮助你优化你的查询语句。

本文地址:https://xzo.com.cn/develop/mysql/554.html

有帮助,很赞!

信息来源:精准像素
信息标签
导出教程 下载word版教程
发表评论 共有条评论
关于Mysql数据库


MySQL是一款安全、跨平台、高效的,并与 PHP、Java 等主流编程语言紧密结合的数据库系统。该数据库系统是由瑞典的 MySQL AB 公司开发、发布并支持,由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的。

推荐Mysql数据库开发教程
什么是SQL注入?如何防注入?
什么是SQL注入?如何防注入?

简言之,SQL是一种结构化查询语言,使我们有能力访问和操作数据库。一些黑客便...

9 373
执行delete命令批量删除了10万条数据,数据库空间为什么没变?
执行delete命令批量删除了10万条数据,数据库空间为什么没变?

今天有客户联系鸥哥,说使用mysql的delete命令删除了10万条数据,但主机还是显...

11 785
怎样让Mysql支持emoji和符号表情?
怎样让Mysql支持emoji和符号表情?

emoji表情在当下已经十分流行,微信名中使用了emoji表情包的也大有人在。我们...

16 648
怎样用phpMyAdmin批量导入Excel内容到MySQL数据库?
怎样用phpMyAdmin批量导入Excel内容到MySQL数据库?

近期,有客户需要将excel数据导入到MySQL数据库,这是一个比较常用的功能,操作起...

12 499
MySQL怎样通过身份证号码计算用户年龄?
MySQL怎样通过身份证号码计算用户年龄?

实名制站点往往需要用到身份证号码,有了身份证号码就需要获取用户年纪或做展...

1 420
MySQL数据库误删除了要怎么恢复?
MySQL数据库误删除了要怎么恢复?

MySQL误删除这种事出现的概率不高,但如果哪天误删除了,总不能真的跑路吧?那么,M...

0 316
MySQL查找字符串FIND_IN_SET()函数介绍
MySQL查找字符串FIND_IN_SET()函数介绍

FIND_IN_SET()是一个MySQL函数,通过使用本函数,我们可以返回指定字符串在以逗...

0 238
textarea带换行存入mysql数据库的方案(php/js)
textarea带换行存入mysql数据库的方案(php/js)

textarea输入框如果带有换行符,在存入数据库时需要将换行符\n转换为<br>,在...

0 376
随机Mysql数据库开发教程
客服QQ:341553759
点击咨询 常见问题 >
官方交流群:90432500
点击加入