优化MySQL数据库中的查询语句详解

很多时候基于php+MySQL建立的网站所出现的系统性能瓶颈往往是出在MySQL上，而MySQL中用的最多的语句就是查询语句，因此，针对MySQL数据库查询语句的优化就显得至关重要！本文就此问题做出详细分析如下：

1、判断是否向MySQL数据库请求了不需要的数据，如下列情况：

（1）、查询不需要的数据，例如你需要10条数据，但是你选出了100条数据加了limit做限制。
（2）、多表关联时返回全部列
（3）、总是取出全部列select*......取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还为服务器带来额外的I/O、内存、和cpu的消耗
（4）、重复查询相同的数据例如，在用户评论的地方需要查询用户的头像的URL，那么用户多次评论的时候将这个数据缓存起来，需要的时候从缓存取出，这样性能会更好。

2、mysql是否在扫描额外的记录

最简单衡量查询开销的三个指标如下：响应时间、扫描的行数、返回的行数

响应时间：服务时间和排队时间。服务时间是指数据库处理这个查询真正花费的时间。排队时间是指服务器因为等待某些资源而没有真正执行的查询。

扫描的行数和返回的行数：理想情况下扫描的行数和返回的行数应该是相同的。

一般MYSQL能够使用如下三种方式应用where条件记录，从好到坏依次为：

（1）、在索引中使用where条件来过滤不匹配的记录，在存储索引层完成。

（2）、使用索引覆盖扫描来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，在mysql服务器层完成，但无需在回表查询记录。

（3）、从数据表中返回数据，然后过滤不满足条件的记录，在mysql服务器层完成，需要先从数据表读出记录然后过滤

如果发现查询需要扫描大量的数据但返回少数的行，那么通常可以尝试下面的技巧：

（1）、使用索引覆盖扫描，把所有需要的列都放到索引中，这样存储引擎无须返回表获取对应行就可以返回结果了。

（2）、改变库表结构，使用单独的汇总表。

（3）、重写这个复杂的查询

3、重构查询的方式

(1)、一个复杂查询还是多个简单查询：

Mysql内部每秒能够扫描内存中上百万条数据，相比之下，mysql响应数据给客户端就慢得多，在其他条件都相同的时候，使用尽可能少的查询当然是好的，但有时候将一个大查询分解为多个小查询都是很有必要的。

(2)、切分查询：

删除旧数据是一个很好的例子，在定期清除大量数据时，如果用一个大的语句一次性完成的话，则可能一次锁住很多数据，占满整个事物日志。耗尽系统资源，阻塞很多小的但很重要的查询。

Mysql>deletefrommessageswherecreated<DATE_SUB(NOW(),INTERVAL3MONTH);

改写：

Rows_affected=0;
Do{
Rows_affected=do_query(
“deletefrommessageswherecreated<DATE_SUB(NOW(),INTERVAL3MONTH)”;
)
}

(3)、分解关联查询：

可以让缓存的效率更高，在应用程序中可以方便的缓存单条数据
就查询分解后，执行单个查询可以减少锁的竞争
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和高扩展
查询本身效率也会更高。
可以减少冗余数据的查询，在应用层做关联查询，意味着对于某条数据应用只需要查询一次，而在数据库中做查询，可能需要重复的访问一部分数据。

适合场景：

①当应用程序能够方便的缓存单个查询结果的时候;
②当可以将数据分布到不同的mysql服务器上的时候;
③当能够使用IN()的方式代替关联查询的时候;
④当查询中使用一个数据表的时候。

赞 (0)

相关推荐

  • my.ini优化mysql数据库性能的十个参数(推荐)

    今天刚好需要配置mysql 5.5.45,因为数据库量挺大的,所以必须优化,要不mysql真的不快. (1).max_connections: 允许的同时客户的数量.增加该值增加 mysqld 要求的文件描述符的数量.这个数字应该增加,否则,你将经常看到 too many connections 错误. 默认数值是100,我把它改为1024 . (2).record_buffer: 每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区.如果你做很多顺序扫描,你可能想要增加该值.默认

  • mysql中优化和修复数据库工具mysqlcheck详细介绍

    一.mysqlcheck简介 mysqlcheck客户端可以检查和修复MyISAM表.它还可以优化和分析表. mysqlcheck的功能类似myisamchk,但其工作不同.主要差别是当mysqld服务器在运行时必须使用mysqlcheck,而myisamchk应用于服务器没有运行时.使用mysqlcheck的好处是不需要停止服务器来检查或修复表.使用myisamchk修复失败是不可逆的. Mysqlcheck为用户提供了一种方便的使用SQL语句CHECK TABLE.REPAIR TABLE.

  • 详解MySQL数据库优化的八种方式(经典必看)

    引言: 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂. 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿到自己的总结文集中,积累优质文章,提升个人能力,希望对大家今后开发中也有帮助 1.选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快.因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小. 例如,在定义邮政编码这个字段时,如果将其设置为CHAR(

  • mysql数据库sql优化原则(经验总结)

    一.前提 这里的原则只是针对MySQL数据库,其他的数据库某些是殊途同归,某些还是存在差异.我总结的也是MySQL普遍的规则,对于某些特殊情况得特殊对待.在构造SQL语句的时候要养成良好的习惯. 二.原则总结 原则1.仅列出需要查询的字段,这对速度不会明显的影响,主要是考虑节省应用程序服务器的内存. 原来语句: select * from admin 优化为: select admin_id,admin_name,admin_password from admin 原则2.尽量避免在列上做运算,

  • MySQL数据库优化详解

    mysql表复制 复制表结构+复制表数据 mysql> create table t3 like t1; mysql> insert into t3 select * from t1; mysql索引 ALTER TABLE用来创建普通索引.UNIQUE索引或PRIMARY KEY索引 ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list)

  • 优化Mysql数据库的8个方法

    1.创建索引对于查询占主要的应用来说,索引显得尤为重要.很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致.如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降.但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引.2.复合索引比如有一条语句是这样的:select * from users

  • 优化MySQL数据库中的查询语句详解

    很多时候基于php+MySQL建立的网站所出现的系统性能瓶颈往往是出在MySQL上,而MySQL中用的最多的语句就是查询语句,因此,针对MySQL数据库查询语句的优化就显得至关重要!本文就此问题做出详细分析如下: 1.判断是否向MySQL数据库请求了不需要的数据,如下列情况: (1).查询不需要的数据,例如你需要10条数据,但是你选出了100条数据加了limit做限制. (2).多表关联时返回全部列 (3).总是取出全部列select*......取出全部列,会让优化器无法完成索引覆盖扫描这类优

  • MySQL数据库学习之查询操作详解

    目录 1.示例表内容 2.简单查询 3.给列起别名 4.列运算 5.条件查询 1.示例表内容 dept表: +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS

  • MYSQL数据库中cmd命令操作详解

    CMD命令 cmd是command的缩写.即命令提示符(CMD),是在OS / 2 , Windows CE与Windows NT平台为基础的操作系统(包括Windows 2000和XP中, Vista中,和Server 2003 )下的"MS-DOS 方式".中文版Windows XP 中的命令提示符进一步提高了与DOS 下操作命令的兼容性,用户可以在命令提示符直接输入中文调用文件. 今天我们就来看一下数据库的各种命令,以下命令全部是从CMD命令窗口下的命令行输入指令,首先如果如果输

  • Java mysql特殊形式的查询语句详解

    Java mysql特殊形式的查询语句详解

    目录 1.子查询 1.1.SELECT字段名称FROM表名WHERE字段名称=(SELECT字段名称FROM表名); 1.2.内层语句查询的结果可以作为外层语句查询的条件 1.3.由IN引发的子查询 1.4.由比较运算符引出子查询 1.5.由EXISTS引发的子查询 1.6.ANYSOMEALL 1.7.CREATE…SELECT 1.8.INSERT…SELECT 1.9.CREATETABLE表名1LIKE表名2: 2.联合查询 2.1UNION 2.2UNIONALL 3.自身连接查询 3

  • MySQL数据库8——数据库中函数的应用详解

    MySQL数据库8——数据库中函数的应用详解

    数据库中内置函数的使用 该篇主要介绍数据库中内置函数的使用,主要有日期函数,字符串函数,数学函数. (一)日期函数 select current_date();//获得当前日期,年月日 select current_time();//获得当前时间,时分秒 select current_timestamp();//获得当前 年月日 时分秒 select date('2017-10-28');//获得参数日期 select date_add('2017-10-28', interval 10 day

  • MySQL一些常用高级SQL语句详解

    MySQL一些常用高级SQL语句详解

    目录 一.MySQL进阶查询 二.MySQL数据库函数 三.MySQL存储过程 总结 一.MySQL进阶查询 首先先创建两张表 mysql -u root -pXXX #登陆数据库,XXX为密码 create database jiangsu; #新建一个名为jiangsu的数据库 use jiangsu; #使用该数据库 create table location(Region char(20),Store_name char(20)); #创建location表,字段1为Region,数据类

  • MySQL数据库学习之分组函数详解

    目录 1.分组函数 极值 求和 平均值 列数和 2.分组查询 3.小练习 4.大BOSS 1.分组函数 极值 示例表内容见此篇文章 找出最高工资: mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+ 1 row in set (0.00 sec) 找出最低工资: mysql> select min(sal) from emp; +----------+

  • MySQL数据库使用mysqldump导出数据详解

    MySQL数据库使用mysqldump导出数据详解

    mysqldump是mysql用于转存储数据库的客户端程序.它主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建您的数据库所需要的 SQL命令如CREATE DATABASE,CREATE TABLE,INSERT等等.可以用来实现轻量级的快速迁移或恢复数据库.是mysql数据库实现逻辑备份的一种方式. 在日常维护工作当中经常会需要对数据进行导出操作,而mysqldump是导出数据过程中使用非常频繁的一个工具:它自带的功能参数非常多,文章中会列举出一些常用的操作,在文章末尾会将所有

  • Python MySQL数据库基本操作及项目示例详解

    目录 一.数据库基础用法 二.项目:银行管理系统 1.进行初始化操作 2.登录检查,并选择操作 3.加入查询功能 4.加入取钱功能 5.加入存钱功能 一.数据库基础用法 要先配置环境变量,然后cmd安装:pip install pymysql 1.连接MySQL,并创建wzg库 #引入decimal模块 import pymysql #连接数据库 db=pymysql.connect(host='localhost',user='root',password='1234',charset='ut

  • MySQL8数据库安装及SQL语句详解

    目录 MySQL8数据库安装 一.Windows 环境下安装 A.下载 MySQL B.解压并配置MySQL环境变量 C.在解压根目录创建my.ini配置文件 D.安装 MySQL (以下操作必须是管理员身份) E.登录.修改密码 二.Linux 环境下安装 A.下载 MySQL B.把下载的 MySQL 压缩包上传到 Linux 服务器 C.解压mysql-8.0.22.tar.gz D.把解压后的文件移动到 /usr/local 目录下 E.添加MySQL组合用户 (默认会添加,没有添加就手

随机推荐