MySQL查看SQL语句执行效率和mysql几种性能测试的工具
Explain命令在解決數據庫性能上是第一推薦使用命令，大部分的性能問題可以通過此命令來簡單的解決，Explain可以用來查看 SQL 語句的執行效 果，可以幫助選擇更好的索引和優化查詢語句，寫出更好的優化語句。
Explain語法：explain select … from … [where ...]
例如：explain select * from news;
輸出：
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
下面對各個屬性進行了解：
1、id：這是SELECT的查詢序列號
2、select_type：select_type就是select的類型，可以有以下幾種：
SIMPLE：簡單SELECT(不使用UNION或子查詢等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二個或后面的SELECT語句
DEPENDENT UNION：UNION中的第二個或后面的SELECT語句，取決于外面的查詢
UNION RESULT：UNION的結果。
SUBQUERY：子查詢中的第一個SELECT
DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢
DERIVED：導出表的SELECT(FROM子句的子查詢)
3、table：顯示這一行的數據是關于哪張表的
4、type：這列最重要，顯示了連接使用了哪種類別,有無使用索引，是使用Explain命令分析性能瓶頸的關鍵項之一。
結果值從好到壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般來說，得保證查詢至少達到range級別，最好能達到ref，否則就可能會出現性能問題。
5、possible_keys：列指出MySQL能使用哪個索引在該表中找到行
6、key：顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL
7、key_len：顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。使用的索引的長度。在不損失精確性的情況下，長度越短越好
8、ref：顯示使用哪個列或常數與key一起從表中選擇行。
9、rows：顯示MySQL認為它執行查詢時必須檢查的行數。
10、Extra：包含MySQL解決查詢的詳細信息，也是關鍵參考項之一。
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上
Using where使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index， 這就會發生，或者是查詢有問題
?
?mysql幾種性能測試的工具使用
?
一、mysqlslap
?
? ? 安裝：裝了mysql就有了
? ? 作用：模擬并發測試數據庫性能。
? ? 優點：簡單，容易使用。
? ? 不足：不能指定生成的數據規模，測試過程不清楚針對十萬級還是百萬級數據做的測試，感覺不太適合做綜合測試，比較適合針對既有數據庫，對單個sql進行優化的測試。
? ? 使用方法：
? ??可以使用mysqlslap --help來顯示使用方法：
?
? ? ?Default options are read from the following files in the given order:
?
? ? ?/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
?
? ? ?--engines：代表要測試的引擎，可以有多個，用分隔符隔開。
?
? ? ?--iterations：代表要運行這些測試多少次。
?
? ? ?--auto-generate-sql ：代表用系統自己生成的SQL腳本來測試。
?
? ? ?--auto-generate-sql-load-type： 代表要測試的是讀還是寫還是兩者混合的（read,write,update,mixed）
?
? ? ?--number-of-queries：代表總共要運行多少次查詢。每個客戶運行的查詢數量可以用查詢總數/并發數來計算。
?
? ? --debug-info 代表要額外輸出CPU以及內存的相關信息。
?
? ? --number-int-cols ：創建測試表的 int 型字段數量
?
? ? --auto-generate-sql-add-autoincrement : 代表對生成的表自動添加auto_increment列，從5.1.18版本開始
?
? ? --number-char-cols 創建測試表的 char 型字段數量。
?
? ? --create-schema 測試的schema，MySQL中schema也就是database。
?
? ? --query ?使用自定義腳本執行測試，例如可以調用自定義的一個存儲過程或者sql語句來執行測試。
?
? ??--only-print 如果只想打印看看SQL語句是什么，可以用這個選項。
?
? ? mysqlslap -u root -p --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam --number-of-queries=10
?
或：
? ??指定數據庫和sql語句:
?
? ? mysqlslap -h localhost -P 123456 --concurrency=100 --iterations=1 --create-schema='mysql' --query='select * from user;' --number-of-queries=10 -u root -p
?
? ??要是看到底做了什么可以加上：--only-print
Benchmark
? ? ? ? Average number of seconds to run all queries: 25.225 seconds
? ? ? ? Minimum number of seconds to run all queries: 25.225 seconds
? ? ? ? Maximum number of seconds to run all queries: 25.225 seconds
? ? ? ? Number of clients running queries: 100
? ? ? ? Average number of queries per client: 0
?
? ? 以上表明100個客戶端同時運行要25秒
?
? ? 再如:
?
? ? mysqlslap -uroot -p123456 --concurrency=100 --iterations=1 --engine=myisam --create-schema='haodingdan112' --query='select * ?From order_boxing_transit where id = 10' --number-of-queries=1 --debug-info
?
二、sysbench?
?
安裝：
? ? 可以從http://sourceforge.net/projects/sysbench/ 下載
? ? tar zxf sysbench-0.4.12.tar.gz
? ? cd sysbench-0.4.12
? ? ./autogen.sh
? ? ./configure && make && make install
?
? ? strip /usr/local/bin/sysbench
?
? ? 安裝時候可能會報錯，后來baidu發現個好文 http://blog.csdn.net/icelemon1314/article/details/7004955 怕以后找不到，也貼過來吧
?
? ??1.如果mysql不是默認路徑安裝，那么需要通過指定--with-mysql-includes和--with-mysql-libs參數來加載mysql安裝路徑
?
2.如果報錯：
? ? ../libtool: line 838: X--tag=CC: command not found
? ? ../libtool: line 871: libtool: ignoring unknown tag : command not found
? ? ../libtool: line 838: X--mode=link: command not found
? ? ../libtool: line 1004: *** Warning: inferring the mode of operation is deprecated.: command not found
? ? ../libtool: line 1005: *** Future versions of Libtool will require --mode=MODE be specified.: command not found
? ? ../libtool: line 2231: X-g: command not found
? ? ../libtool: line 2231: X-O2: command not found
? ??那么執行下根目錄的：autogen.sh文件，然后重新configure && make && make install
?
3.如果報錯：
? ? sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory
?
? ? 那么執行下：
? ??ln -s /usr/local/mysql5.5/mysql/lib/libmysqlclient.so.18 /usr/lib64/
?
4.如果執行autogen.sh時，報如下錯誤：
? ? ./autogen.sh: line 3: aclocal: command not found
? ? 那么需要安裝一個軟件：
? ??yum install automake
? ? 然后需要增加一個參數：查找： AC_PROG_LIBTOOL 將其注釋，然后增加AC_PROG_RANLIB?
? ? 作用：模擬并發,可以執行CPU/內存/線程/IO/數據庫等方面的性能測試。數據庫目前支持MySQL/Oracle/PostgreSQL
? ? 優點：可以指定測試數據的規模，可以單獨測試讀、寫的性能，也可以測試讀寫混合的性能。
? ? 不足：測試的時候，由于網絡原因，測試的非常慢，但是最終給的結果卻很好，并發支持很高，所以給我的感覺是并不太準確。當然也可能我沒搞明白原理
?
使用方法：
? ? 準備數據?
? ? sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=400000 --mysql-db=dbtest2 --mysql-user=root --mysql-host=192.168.1.101 --mysql-password=pwd prepare
?
?
執行測試
? ? sysbench --num-threads=100 --max-requests=4000 --test=oltp --mysql-table-engine=innodb --oltp-table-size=400000 --mysql-db=dbtest1 --mysql-user=root --mysql-host=192.168.1.101 --mysql-password=pwd run?
?
? ? sysbench 0.4.12: multi-threaded system evaluation benchmark
? ? No DB drivers specified, using mysql
? ? Running the test with following options:
? ? Number of threads: 100
? ? Doing OLTP test.
? ? Running mixed OLTP test
? ? Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
? ? Using "BEGIN" for starting transactions
? ? Using auto_inc on the id column
? ? Maximum number of requests for OLTP test is limited to 4000
? ? Threads started!
? ? Done.
? ? OLTP test statistics:
? ? queries performed:
? ? read: 56014
? ? write: 20005
? ? other: 8002
? ? total: 84021
? ? transactions: 4001 (259.14 per sec.)
? ? deadlocks: 0 (0.00 per sec.)
? ? read/write requests: 76019 (4923.75 per sec.)
? ? other operations: 8002 (518.29 per sec.)
? ? Test execution summary:
? ? total time: 15.4393s
? ? total number of events: 4001
? ? total time taken by event execution: 1504.7744
? ? per-request statistics:
? ? min: 33.45ms
? ? avg: 376.10ms
? ? max: 861.53ms
? ? approx. 95 percentile: 505.65ms
? ? Threads fairness:
? ? events (avg/stddev): 40.0100/0.67
? ? execution time (avg/stddev): 15.0477/0.22
?
?
三、tpcc-mysql?
?
? ? 安裝：
? ? export C_INCLUDE_PATH=/usr/include/mysql
? ? export PATH=/usr/bin:$PATH
? ? export LD_LIBRARY_PATH=/usr/lib/mysql
? ? cd /tmp/tpcc/src
? ? make?
? ??然后就會在 /tmp/tpcc-mysql 下生成 tpcc 命令行工具 tpcc_load 、 tpcc_start
?
? ? 作用：測試mysql數據庫的整體性能
? ? 優點：符合tpcc標準，有標準的方法，模擬真實的交易活動，結果比較可靠。
? ? 不足：不能單獨測試讀或者寫的性能，對于一些以查詢為主或者只寫的應用，就沒有這么大的意義了。
? ? 使用方法：
? ? 加載數據
? ? 創建庫
? ? mysql>create database tpcc10;
? ? 創建表：
? ? shell>mysql tpcc10 < create_table.sql
? ? 添加外鍵：
? ? shell>mysql tpcc10 < add_fkey_idx.sql
?
? ? 加載數據:
?
1、單進程加載：
? ? shell>./tpcc_load 192.168.11.172 tpcc10 root pwd 300
? ? |主機||數據庫||用戶||密碼||warehouse|
?
2、并發加載：（推薦，但需要修改一下）
?
? ? shell>./load.sh tpcc300 300
? ? |數據庫||warehouse|
?
3、測試
? ? ./tpcc_start -h192.168.11.172 -d tpcc -u root -p 'pwd' -w 10 -c 10 -r 10 -l 60 -i 10 -f /mnt/hgfs/mysql/tpcc100_2013522.txt
? ? ***************************************
? ? *** ###easy### TPC-C Load Generator ***
? ? ***************************************
? ? option h with value '192.168.11.172'
? ? option d with value 'tpcc'
? ? option u with value 'root'
? ? option p with value 'pwd'
? ? option w with value '1'
? ? option c with value '100'
? ? option r with value '120'
? ? option l with value '60'
? ? option i with value '10'
? ? option f with value '/mnt/hgfs/mysql/tpcc100_2013522.txt'
? ? <Parameters>
? ? [server]: 192.168.11.172
? ? [port]: 3306
? ? [DBname]: tpcc
? ? [user]: root
? ? [pass]: pwd
? ? [warehouse]: 1
? ? [connection]: 100
? ? [rampup]: 120 (sec.)
? ? [measure]: 60 (sec.)
? ? RAMP-UP TIME.(120 sec.)
? ? MEASURING START.
? ? 10, 245(77):10.923|28.902, 242(0):3.677|10.796, 25(0):1.579|2.198, 24(0):17.451|21.047, 25(4):19.999|33.776
? ? 20, 262(75):9.070|11.917, 263(0):3.407|4.716, 26(0):1.608|1.776, 27(0):11.347|16.408, 26(1):19.166|21.018
? ? 30, 247(90):11.130|14.131, 241(0):2.367|2.654, 24(0):0.960|1.095, 24(0):9.308|16.538, 25(3):19.999|24.874
? ? 40, 237(69):11.840|13.009, 239(1):3.638|7.245, 24(0):0.692|0.773, 23(0):8.756|10.456, 23(1):19.527|20.495
? ? 50, 252(69):10.548|17.925, 256(0):2.652|2.893, 26(0):1.177|3.579, 27(0):14.648|15.018, 25(4):19.999|26.398
? ? 60, 256(78):9.323|11.328, 251(1):3.895|5.380, 25(0):0.785|1.542, 25(0):11.382|15.829, 26(0):18.481|18.855
? ? STOPPING THREADS....................................................................................................
? ? <Raw Results>
? ? [0] sc:1041 lt:458 rt:0 fl:0?
? ? [1] sc:1490 lt:2 rt:0 fl:0?
? ? [2] sc:150 lt:0 rt:0 fl:0?
? ? [3] sc:150 lt:0 rt:0 fl:0?
? ? [4] sc:137 lt:13 rt:0 fl:0?
? ? in 60 sec.
? ? <Raw Results2(sum ver.)>
? ? [0] sc:1041 lt:458 rt:0 fl:0?
? ? [1] sc:1490 lt:2 rt:0 fl:0?
? ? [2] sc:150 lt:0 rt:0 fl:0?
? ? [3] sc:150 lt:0 rt:0 fl:0?
? ? [4] sc:137 lt:13 rt:0 fl:0?
? ? <Constraint Check> (all must be [OK])
? ? [transaction percentage]
? ? Payment: 43.36% (>=43.0%) [OK]
? ? Order-Status: 4.36% (>= 4.0%) [OK]
? ? Delivery: 4.36% (>= 4.0%) [OK]
? ? Stock-Level: 4.36% (>= 4.0%) [OK]
? ? [response time (at least 90% passed)]
? ? New-Order: 69.45% [NG] *
? ? Payment: 99.87% [OK]
? ? Order-Status: 100.00% [OK]
? ? Delivery: 100.00% [OK]
? ? Stock-Level: 91.33% [OK]
? ? <TpmC>
? ? 1499.000 TpmC
? ? 關于Tpcc的概念請參見http://baike.baidu.com/view/2776305.htm?
? ? 這里把測試用例介紹貼一下
? ? TPC-C測試用到的模型是一個大型的商品批發銷售公司，它擁有若干個分布在不同區域的商品倉庫。當業務擴展的時候，公司將添加新的倉庫。
? ? 每個倉庫負責為10個銷售點供貨，其中每個銷售點為3000個客戶提供服務，每個客戶提交的訂單中，平均每個訂單有10項產品，
? ? 所有訂單中約1%的產品在其直接所屬的倉庫中沒有存貨，必須由其他區域的倉庫來供貨。同時，每個倉庫都要維護公司銷售的100000種商品的庫存記錄。
?
四、 The MySQL Benchmark Suite
? ? ? ? 這個測試工具是隨著MySQL綁定發行的，基于Perl語言和其中的兩個模塊：DBI和Benchmark。如果有需要，它支持所有支持DBI驅動的數據庫。可以通過修改bench-init.pl的選項以符合需要。另外提醒的是，它不支持多CPU。
? ? ? ? 進行測試時，執行run-all-tests腳本，具體的命令選項請看README。
五、 MySQL super-smack
? ? ? ?這是一個強大的廣受贊譽的壓力測試工具，支持MySQL和PostgreSQL。
? ? ? ?http://jeremy.zawodny.com/mysql/super-smack/
? ? ? 安裝很簡單，請先仔細閱讀目錄里的指導文件。
? ? ? ?Preparing test data
? ? ? ?做測試時，最好用自己的數據。因為使用真實的數據，使測試變得接近現實和客觀。
? ? ? Configuration
? ? ? smack的文件設置，看起來很簡單。
?
六、MyBench: A Home-Grown Solution
? ? ? MyBench一種基于Perl語言易于擴展的測試工具。
來源:http://blog.csdn.net/chengxuyuanyonghu/article/details/61431386
總結
以上是生活随笔為你收集整理的MySQL查看SQL语句执行效率和mysql几种性能测试的工具的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 易宝支付是什么平台扣款
- 下一篇： 云上如何做冷热数据分离