mysql调优三步曲(慢查询、explain profile)_MySQL
MySQLexplain
bitsCN.commysql调优三步曲(慢查询、explain profile)
在做性能测试中经常会遇到一些sql的问题,其实做性能测试这几年遇到问题最多还是数据库这块,要么就是IO高要么就是cpu高,所以对数据的优化在性能测试过程中占据着很重要的地方,下面我就介绍一些msyql性能调优过程中经常用到的三件利器:
1、慢查询 (分析出现出问题的sql)
2、Explain (显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句)
3、Profile(查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.)
首先我们先讲一讲mysql的慢查询
1,配置开启
Linux:
在mysql配置文件my.cnf中增加
log-slow-queries=/var/lib/mysql/slowquery.log (指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log)
long_query_time=2 (记录超过的时间,默认为10s)
log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
log-long-format (如果设置了,所有没有使用索引的查询也将被记录)
Windows:
在my.ini的[mysqld]添加如下语句:
log-slow-queries =E:/web/mysql/log/mysqlslowquery.log
long_query_time = 2(其他参数如上)
2,查看方式
Linux:
使用mysql自带命令mysqldumpslow查看
常用命令
-s ORDER what to sort by (t, at, l, al, r, aretc), 'at’ is default-t NUM just show the top n queries-g PATTERN grep: only consider stmts that includethis string
eg:
s,是order的顺序,说明写的不够详细,俺用下来,包括看了代码,主要有 c,t,l,r和ac,at,al,ar,分别是按照query次数,时间,lock的时间和返回的记录数来排序,前面加了a的时倒序 -t,是top n的意思,即为返回前面多少条的数据 -g,后边可以写一个正则匹配模式,大小写不敏感的
mysqldumpslow -s c -t 20 host-slow.logmysqldumpslow -s r -t 20 host-slow.log
上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log这个是按照时间返回前10条里面含有左连接的sql语句。
接下来就是explain
使用方法:
执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000 得到如下结果:
显示结果分析:
table | type | possible_keys | key |key_len | ref | rows | Extra
EXPLAIN列的解释:
table
显示这一行的数据是关于哪张表的
type
这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys
显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key
实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
key_len
使用的索引的长度。在不损失精确性的情况下,长度越短越好
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数
rows
MYSQL认为必须检查的用来返回请求数据的行数
Extra
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢
extra列返回的描述的意义
Distinct
一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
Not exists
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,
就不再搜索了
Range checked for each
Record(index map:#)
没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort
看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary
看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
Where used
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题
不同连接类型的解释(按照效率高低的顺序排序)
system
表只有一行:system表。这是const连接类型的特殊情况
const
表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待
eq_ref
在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用
ref
这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range
这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况
index
这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)
ALL
这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免
再者就是profile
我们可以先使用
mysql> SELECT @@profiling;+-------------+| @@profiling |+-------------+| 0 |+-------------+1 row in set (0.00 sec)来查看是否已经启用profile,如果profilng值为0,可以通过mysql> SET profiling = 1;Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@profiling;+-------------+| @@profiling |+-------------+| 1 |+-------------+1 row in set (0.00 sec)
来启用。启用profiling之后,我们执行一条查询语句,比如:
SELECT * FROM res_user ORDER BY modifiedtimeLIMIT 0,1000mysql> show profiles;+----------+------------+-------------------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+-------------------------------------------------------------+| 1| 0.00012200 | SELECT @@profiling || 2| 1.54582000 | SELECT res_id FROM res_user ORDER BY modifiedtime LIMIT 0,3 |+----------+------------+-------------------------------------------------------------+2 rows in set (0.00 sec) 注意:Query_ID表示刚执行的查询语句mysql> show profile for query 2;+--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| starting | 0.000013 || checking query cache for query | 0.000035 || Opening tables | 0.000009 || System lock | 0.000002 || Table lock | 0.000015 || init | 0.000011 || optimizing | 0.000003 || statistics | 0.000006 || preparing | 0.000006 || executing | 0.000001 || Sorting result | 1.545565 || Sending data | 0.000038 || end | 0.000003 || query end | 0.000003 || freeing items | 0.000069 || storing result in query cache | 0.000004 || logging slow query | 0.000001 || logging slow query | 0.000033 || cleaning up | 0.000003 |+--------------------------------+----------+19 rows in set (0.00 sec)
结论:可以看出此条查询语句的执行过程及执行时间,总的时间约为1.545s。这时候我们再执行一次。
mysql> SELECT res_id FROM res_user ORDERBY modifiedtime LIMIT 0,3;+---------+| res_id |+---------+| 1000305 || 1000322 || 1000323 |+---------+3 rows in set (0.00 sec)mysql> show profiles;+----------+------------+-------------------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+-------------------------------------------------------------+| 1 | 0.00012200 | SELECT @@profiling || 2 | 1.54582000 | SELECT res_id FROM res_userORDER BY modifiedtime LIMIT 0,3 || 3 | 0.00006500 | SELECT res_id FROMres_user ORDER BY modifiedtime LIMIT 0,3 |+----------+------------+-------------------------------------------------------------+3 rows in set (0.00 sec)mysql> show profile for query 3;+--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| starting | 0.000013 || checking query cache for query | 0.000005|| checking privileges on cached | 0.000003 || sending cached result to clien | 0.000040|| logging slow query | 0.000002 || cleaning up | 0.000002 |+--------------------------------+----------+6 rows in set (0.00 sec) (注意红色标记的地方)
结论:可以看出此次第二次查询因为前一次的查询生成了cache,所以这次无需从数据库文件中再次读取数据而是直接从缓存中读取,结果查询时间比第一次快多了(第一次查询用了1.5秒而本次用了不到5毫秒)。

熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

Video Face Swap
使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

熱工具

記事本++7.3.1
好用且免費的程式碼編輯器

SublimeText3漢化版
中文版,非常好用

禪工作室 13.0.1
強大的PHP整合開發環境

Dreamweaver CS6
視覺化網頁開發工具

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)

MySQL是一種開源的關係型數據庫管理系統,主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據,以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題,優化建議包括使用索引、優化查詢和分錶分區。

MySQL在數據庫和編程中的地位非常重要,它是一個開源的關係型數據庫管理系統,廣泛應用於各種應用場景。 1)MySQL提供高效的數據存儲、組織和檢索功能,支持Web、移動和企業級系統。 2)它使用客戶端-服務器架構,支持多種存儲引擎和索引優化。 3)基本用法包括創建表和插入數據,高級用法涉及多表JOIN和復雜查詢。 4)常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5)性能優化方法包括合理使用索引、優化查詢和使用緩存,最佳實踐包括使用事務和PreparedStatemen

選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能,支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎,支持事務和查詢優化。 3.易於使用,支持多種操作系統和編程語言。 4.擁有強大的社區支持,提供豐富的資源和解決方案。

Apache 連接數據庫需要以下步驟:安裝數據庫驅動程序。配置 web.xml 文件以創建連接池。創建 JDBC 數據源,指定連接設置。從 Java 代碼中使用 JDBC API 訪問數據庫,包括獲取連接、創建語句、綁定參數、執行查詢或更新以及處理結果。

在 Docker 中啟動 MySQL 的過程包含以下步驟:拉取 MySQL 鏡像創建並啟動容器,設置根用戶密碼並映射端口驗證連接創建數據庫和用戶授予對數據庫的所有權限

MySQL在Web應用中的主要作用是存儲和管理數據。 1.MySQL高效處理用戶信息、產品目錄和交易記錄等數據。 2.通過SQL查詢,開發者能從數據庫提取信息生成動態內容。 3.MySQL基於客戶端-服務器模型工作,確保查詢速度可接受。

Laravel 是一款 PHP 框架,用於輕鬆構建 Web 應用程序。它提供一系列強大的功能,包括:安裝: 使用 Composer 全局安裝 Laravel CLI,並在項目目錄中創建應用程序。路由: 在 routes/web.php 中定義 URL 和處理函數之間的關係。視圖: 在 resources/views 中創建視圖以呈現應用程序的界面。數據庫集成: 提供與 MySQL 等數據庫的開箱即用集成,並使用遷移來創建和修改表。模型和控制器: 模型表示數據庫實體,控制器處理 HTTP 請求。

優雅安裝 MySQL 的關鍵在於添加 MySQL 官方倉庫。具體步驟如下:下載 MySQL 官方 GPG 密鑰,防止釣魚攻擊。添加 MySQL 倉庫文件:rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm更新 yum 倉庫緩存:yum update安裝 MySQL:yum install mysql-server啟動 MySQL 服務:systemctl start mysqld設置開機自啟動
