mysql性能分析工具

阳光穿透心脏的1/2处 2023-09-25 10:47 172阅读 0赞

### mysql如何分析查询性能 ###

如何提高MySQLLimit查询的性能?在MySQL数据库操作中，我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。其实我们可以使用Limit关键字来避免全表扫描的情况

本回答由网友推荐

![mysql性能分析工具\_mysql性能][mysql_mysql]

### SQL Server 与 MySQL 性能相差多大 ###

一、sqlserver

易用性、适合分布式组织的可伸缩性、用于决策支持的数据仓库功能、与许多其他服务器软件紧密关联的集成性、良好的性价比等；

为数据管理与分析带来了灵活性，允许单位在快速变化的环境中从容响应，从而获得竞争优势。从数据管理和分析角度看，将原始数据转化为商业智能和充分利用Web带来的机会非常重要。作为一个完备的数据库和数据分析包，SQLServer为快速开发新一代企业级商业应用程序、为企业赢得核心竞争优势打开了胜利之门。作为重要的基准测试可伸缩性和速度奖的记录保持者，SQLServer是一个具备完全Web支持的数据库产品，提供了对可扩展标记语言(XML)的核心支持以及在Internet上和防火墙外进行查询的能力；

开放性：SQLServer只能windows上运行没有丝毫开放性操作系统系统稳定对数据库十分重要Windows9X系列产品偏重于桌面应用NTserver只适合小型企业而且windows平台靠性安全性和伸缩性非常有限象unix样久经考验尤其处理大数据库；

伸缩性并行性：SQLserver并行实施和共存模型并成熟难处理日益增多用户数和数据卷伸缩性有限；

安全性：没有获得任何安全证书。

性能：SQLServer多用户时性能佳；

客户端支持及应用模式：客户端支持及应用模式。只支持C/S模式，SQLServerC/S结构只支持windows客户用ADO、DAO、OLEDB、ODBC连接；

使用风险：SQLserver完全重写代码经历了长期测试断延迟许多功能需要时间来证明并十分兼容；

二、MySql

体积小、速度快、总体拥有成本低，开源；

支持多种操作系统；

是开源数据库，提供的接口支持多种语言连接操作

MySql的核心程序采用完全的多线程编程。线程是轻量级的进程，它可以灵活地为用户提供服务，而不过多的系统资源。用多线程和C语言实现的MySql能很容易充分利用CPU；

MySql有一个非常灵活而且安全的权限和口令系统。当客户与MySql服务器连接时，他们之间所有的口令传送被加密，而且MySql支持主机认证；

支持ODBCforWindows，支持所有的ODBC2.5函数和其他许多函数，可以用Access连接MySql服务器，使得应用被扩展；

支持大型的数据库，可以方便地支持上千万条记录的数据库。作为一个开放源代码的数据库，可以针对不同的应用进行相应的修改。

拥有一个非常快速而且稳定的基于线程的内存分配系统，可以持续使用面不必担心其稳定性；

MySQL同时提供高度多样性，能够提供很多不同的使用者介面，包括命令行客户端操作，网页浏览器，以及各式各样的程序语言介面，例如C+，Perl，Java，PHP，以及Python。你可以使用事先包装好的客户端，或者干脆自己写一个合适的应用程序。MySQL可用于Unix，Windows，以及OS/2等平台，因此它可以用在个人电脑或者是服务器上；

不支持热备份；

MySQL最大的缺点是其安全系统，主要是复杂而非标准，另外只有到调用mysqladmin来重读用户权限时才发生改变；

没有一种存储过程(StoredProcedure)语言，这是对习惯于企业级数据库的程序员的最大限制；

MySQL的价格随平台和安装方式变化。Linux的MySQL如果由用户自己或系统管理员而不是第三方安装则是免费的，第三方案则必须付许可费。Unix或Linux自行安装免费、Unix或Linux第三方安装收费；

### 如何优化MySQL数据库性能？ ###

使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。

SELECT Name, Phone FROM client

UNION

SELECT Name, BirthDate FROM author

UNION

SELECT Name, Supplier FROM product

4、事务

尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。

![mysql性能分析工具\_mysql性能\_02][mysql_mysql_02]

### MySQL性能如何优化？ ###

六：结语

这是活动目录学习系列的其中一篇，现在越发感觉活动目录的强大与高深，我会继续学习，争取给大家分享有用的东西。过年这段时间由于线上数据库经常压力过大导致响应非常缓慢甚至死机，咬咬牙下大决心来解决效率不高的问题!

首先是由于公司秉承快速开发原则，频繁上线，导致每次忽视了性能问题!日积月累，所以导致系统越来越慢，所以如果你的系统查询语句本来就优化的很好了可能参考意义不大!

提取慢查询日志文件，应该在你的DataDir目录下面

通过程序处理慢查询文件，将文件格式的慢查询导入到数据库中：

1 mysql> desc slow\_query;

2 +---------------+-------------+------+-----+---------+-------+

4 +---------------+-------------+------+-----+---------+-------+

6 | user | varchar(64) | NO | | | |

7 | host | varchar(64) | NO | | | |

9 | query\_time | int(11) | NO | | | | 查询所用时间，直接性能指标

10 | lock\_time | int(11) | YES | | 0 | | 等待锁定的时间

11 | rows\_sent | int(11) | YES | | 0 | | 返回的结果行数

12 | rows\_examined | int(11) | YES | | 0 | | 扫描行数(很重要，上万以后就要重点注意了

14 +---------------+-------------+------+-----+---------+-------+

然后发挥您的想象力在这个表中尽力捕捉你想捕捉的，那类型语句压力最大、扫描行数最多、等锁最久……

优化后：

1 mysql> select sum(query\_time)/count(\*),count

2 (\*),sum(query\_time),min(Date),Max(Date) from slow where Date>'2008-02-20 22:50:52' and Date 7233 | 2197 | 12574 | 2008-02-20 22:51:16 | 2008-02-21 17:34:10 |

7 +--------------------------+----------+-----------------+---------------------+---------------------+

8 1 row in set (0。

### MySQL和SQLServer相比，哪个性能更好 ###

MySQL和SQLServer相比，哪个性能更好

主要差别，SQL Server是微软的，只在Windows里能用。MySQL，各种操作系统都能用。要说性能，Sun公司已经把MySQL做到很好了，可以支持大型系统。但是Oracle以后会不会把MySQL做到能够威胁Oracle数据库的程度，就不知道了。主要差别，sql server是微软的，只在windows里能用。mysql，各种操作系统都能用。要说性能，sun公司已经把mysql做到很好了，可以支持大型系统。但是oracle以后会不会把mysql做到能够威胁oracle数据库的程度，就不知道了。

![mysql性能分析工具\_mysql性能\_03][mysql_mysql_03]

### 如何提高MySQL性能 ###

默认情况下，MySQL的清写(purge)操作(用于移除带删除标记的记录)由InnoDB的主线程完成，这可以降低内部资源竞争发生的概率，进而增强MySQL服务伸缩能力。

不过，随着InnoDB内部各式各样的竞争越来越多，这种设置带来的性能优势已几乎不值一提，因此，生产环境中应该通过为innodb\_purge\_threads服务器变量设定为ON将主线程与清写线程分开运行。如何提高mysql limit查询的性能?

在mysql数据库操作中，我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。其实我们可以使用limit关键字来避免全表扫描的情况，从而提高效率。

有个几千万条记录的表 on mysql 5.0.x，现在要读出其中几十万万条左右的记录。常用方法，依次循环：

select \* from mytable where index\_col = xxx limit offset, limit;

经验：如果没有blob/text字段，单行记录比较小，可以把 limit 设大点，会加快速度。

问题：头几万条读取很快，但是速度呈线性下降，同时 mysql server cpu 99% ，速度不可接受。

调用 explain select \* from mytable where index\_col = xxx limit offset, limit;

显示 type = all

在 mysql optimization 的文档写到"all"的解释

a full table scan is done for each combination of rows from the previous tables. this is normally not good if the table is the first table not marked const, and usually very bad in all other cases. normally, you can avoid all by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

看样子对于 all, mysql 就使用比较笨的方法，那就改用 range 方式？ 因为 id 是递增的，也很好修改 sql 。

select \* from mytable where id > offset and id < offset + limit and index\_col = xxx

explain 显示 type = range，结果速度非常理想，返回结果快了几十倍。

limit语法：

select \* from table limit \[offset,\] rows | rows offset offset

limit子句可以被用于强制 select 语句返回指定的记录数。limit接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

为了与 postgresql 兼容，mysql 也支持句法：limit \# offset \#。

mysql> select \* from table limit 5,10; //检索记录行6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1

mysql> select \* from table limit 95,-1; //检索记录行96-last

//如果只给定一个参数，它表示返回最大的记录行数目，换句话说，limit n 等价于 limit 0,n

mysql> select \* from table limit 5; //检索前5个记录行

mysql的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。同样是取10条数据，下面两句就不是一个数量级别的。

select \* from table limit 10000,10

select \* from table limit 0,10

文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据，明显要好于直接使用limit。

这里我具体使用数据分两种情况进行测试。

1、offset比较小的时候：

select \* from table limit 10,10

//多次运行，时间保持在0.0004-0.0005之间

select \* from table where vid ＞=(select vid from table order by vid limit 10,1) limit 10

//多次运行，时间保持在0.0005-0.0006之间，主要是0.0006

结论：偏移offset较小的时候，直接使用limit较优。这个显然是子查询的原因。

2、offset大的时候：

select \* from table limit 10000,10

//多次运行，时间保持在0.0187左右

select \* from table where vid ＞=(select vid from table order by vid limit 10000,1) limit 10

//多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。

[mysql_mysql]: https://img1.baidu.com/it/u=4264933115,2558347114&fm=26&fmt=auto
[mysql_mysql_02]: https://img0.baidu.com/it/u=2470941637,805255152&fm=26&fmt=auto
[mysql_mysql_03]: https://img2.baidu.com/it/u=3940394324,684945590&fm=26&fmt=auto