MySQL查看SQL语句执行效率

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 

安装：
    可以从 下载
    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/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//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'
   
    [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....................................................................................................
   
    [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.
   
    [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 
    (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]
   
    1499.000 TpmC
    关于Tpcc的概念请参见
    这里把测试用例介绍贴一下
    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。
       
      安装很简单，请先仔细阅读目录里的指导文件。
       Preparing test data
       做测试时，最好用自己的数据。因为使用真实的数据，使测试变得接近现实和客观。
      Configuration
      smack的文件设置，看起来很简单。
 
六、MyBench: A Home-Grown Solution
      MyBench一种基于Perl语言易于扩展的测试工具。