Home >> MySQL >> mysql-优化

mysql-优化

对于数据库来说,无论是oracle还是mysql,优化是一个永远不变的主题。(原文链接 http://ddbiz.com/?p=931)

本系列成文是由另一篇笔记引起的。鉴于数据库的调优是一个目的性很明确但是长路漫漫的事情,因此本系列将陆陆续续进行更新。

另:本系列是参考 mysql reference manual 来进行的(可以从这里或者这里)。
特别说明:本系列是针对MyISAM查询进行的优化,并不适用于MyISAM的并发写以及InnoDB数据引擎。

目录
第一篇 序章
第二篇 连接优化
第三篇 索引优化
第四篇 查询优化
第五篇 到实战中去

涉及本系列的测试环境包括:

MySQL: 5.5.0 CE, OS: 2.6.18-274.7.1.el5.x86_64 (CentOS 5.4)
以及
MySQL: 5.1.47 CE, OS: 2.6.32-71.29.1.el6.x86_64 (CentOS 6.1)
以及
MySQL: 5.5.19 CE, OS: windows 2008 r2 sp1 (x64)

准备工作
在动手优化数据库服务器前,先明确两个要点:

  1. 尽量使用内存,而不是交换分区.
    如尽量使用linux 下的 /tmp ,以及在情况允许的时候,把尽可能多的内存分配给mysql。因此,数据库服务器的内存,越大越好!
  2. 用 –skip-external-locking 运行mysql服务
    通过 show variables like ‘%external%’; 可以查看当前的设置:

    mysql> show variables like ‘%external%’;
    +———————–+——-+
    | Variable_name | Value |
    +———————–+——-+
    | external_user | |
    | skip_external_locking | ON |
    +———————–+——-+
    2 rows in set (0.00 sec)

    在单一的服务器运行环境中(只非集群方式),–skip-external-locking 应该是默认的开启选项。

  3. 手动启动mysql数据库服务
    因为要实时调整mysql的启动参数,可以使用 mysqld_safe (linux) 或 mysqld (windows)的命令行方式,启动命令类似于

    (linux)/usr/bin/mysqld_safe –skip-external-locking

    (windows)mysqld –skip-external-locking

    命令行下启动的服务进程,可以通过 mysqladmin -uroot -pyourpasswd shutdown来终止运行.

  4. 测试自己的数据库服务器
    mysql 的benchmark可以对服务器的性能作出测试。可以通过
    yum install mysql-bench 进行安装。如果数据库的安装包不是通过 yun安装的,比如你装的是 MySQL-5.5….(我安装时centos6的库中mysql还只有5.1),可能需要手动安装perl-DBD::MySQL驱动。下载和安装方式可以参考这里
    下面是我的一个数据库服务器的测试

    perl run-all-tests –server=mysql –user=$MYSQLUSER –password=$MYSQLPASSWD
    Benchmark DBD suite: 2.15
    Date of test: 2012-02-27 16:40:23
    Running tests on: Linux 2.6.32-71.29.1.el6.x86_64 x86_64
    Arguments:
    Comments:
    Limits from:
    Server version: MySQL 5.5.20
    Optimization: None
    Hardware:

    alter-table:
    Total time: 82 wallclock secs ( 0.07 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.09 CPU)
    ATIS: Total time: 167 wallclock secs ( 3.88 usr 0.25 sys + 0.00 cusr 0.00 csys = 4.13 CPU)
    big-tables: Total time: 45 wallclock secs ( 2.09 usr 0.31 sys + 0.00 cusr 0.00 csys = 2.40 CPU)
    connect: Total time: 112 wallclock secs (30.41 usr 11.75 sys + 0.00 cusr 0.00 csys = 42.16 CPU)
    create: Total time: 3415 wallclock secs ( 5.40 usr 1.13 sys + 0.00 cusr 0.00 csys = 6.53 CPU)
    insert: Estimated total time: 20175 wallclock secs (229.31 usr 41.32 sys + 0.00 cusr 0.00 csys = 270.63 CPU)
    select: Total time: 327 wallclock secs (24.06 usr 2.46 sys + 0.00 cusr 0.00 csys = 26.52 CPU)
    transactions: Test skipped because the database doesn’t support transactions
    wisconsin: Total time: 449 wallclock secs ( 2.36 usr 0.54 sys + 0.00 cusr 0.00 csys = 2.90 CPU)

    All 9 test executed successfully
    Tests with estimated time have a + at end of line

    Operation seconds usr sys cpu tests
    alter_table_add 36.00 0.01 0.01 0.02 100
    alter_table_drop 35.00 0.00 0.00 0.00 91
    connect 7.00 3.98 0.74 4.72 10000
    connect+select_1_row 9.00 4.42 0.77 5.19 10000
    connect+select_simple 8.00 4.45 0.86 5.31 10000
    count 6.00 0.02 0.00 0.02 100
    count_distinct 3.00 0.13 0.02 0.15 1000
    count_distinct_2 7.00 0.10 0.01 0.11 1000
    count_distinct_big 7.00 2.44 0.03 2.47 120
    count_distinct_group 7.00 0.49 0.02 0.51 1000
    count_distinct_group_on_key 5.00 0.14 0.02 0.16 1000
    count_distinct_group_on_key_parts 6.00 0.51 0.02 0.53 1000
    count_distinct_key_prefix 2.00 0.10 0.02 0.12 1000
    count_group_on_key_parts 5.00 0.48 0.01 0.49 1000
    count_on_key 63.00 5.60 0.82 6.42 50100
    create+drop 1102.00 1.69 0.34 2.03 10000
    create_MANY_tables 891.00 1.14 0.18 1.32 10000
    create_index 1.00 0.01 0.00 0.01 8
    create_key+drop 1222.00 1.78 0.32 2.10 10000
    create_table 3.00 0.00 0.00 0.00 31
    delete_all_many_keys 1826.00 0.01 0.00 0.01 1
    delete_big 0.00 0.00 0.00 0.00 1
    delete_big_many_keys 1825.00 0.01 0.00 0.01 128
    delete_key 61.00 0.35 0.19 0.54 10000
    delete_range 11.00 0.00 0.00 0.00 12
    drop_index 1.00 0.00 0.00 0.00 8
    drop_table 0.00 0.00 0.00 0.00 28
    drop_table_when_MANY_tables 197.00 0.49 0.14 0.63 10000
    insert 4999.00 15.29 5.82 21.11 350768
    insert_duplicates 16.00 3.64 2.05 5.69 100000
    insert_key 2374.00 6.25 1.71 7.96 100000
    insert_many_fields 36.00 0.21 0.04 0.25 2000
    insert_select_1_key 5.00 0.00 0.00 0.00 1
    insert_select_2_keys 7.00 0.00 0.00 0.00 1
    min_max 3.00 0.01 0.00 0.01 60
    min_max_on_key 20.00 9.79 1.23 11.02 85000
    multiple_value_insert 21.00 0.08 0.00 0.08 100000
    once_prepared_select 21.00 5.14 1.54 6.68 100000
    order_by_big 7.00 5.67 0.06 5.73 10
    order_by_big_key 6.00 5.87 0.11 5.98 10
    order_by_big_key2 6.00 5.64 0.07 5.71 10
    order_by_big_key_desc 7.00 5.86 0.10 5.96 10
    order_by_big_key_diff 7.00 5.68 0.06 5.74 10
    order_by_big_key_prefix 7.00 5.66 0.08 5.74 10
    order_by_key2_diff 1.00 0.92 0.01 0.93 500
    order_by_key_prefix 0.00 0.48 0.01 0.49 500
    order_by_range 1.00 0.50 0.01 0.51 500
    outer_join 7.00 0.00 0.00 0.00 10
    outer_join_found 7.00 0.00 0.00 0.00 10
    outer_join_not_found 6.00 0.01 0.00 0.01 500
    outer_join_on_key 5.00 0.01 0.00 0.01 10
    prepared_select 28.00 9.87 1.77 11.64 100000
    select_1_row 16.00 3.29 1.66 4.95 100000
    select_1_row_cache 15.00 3.20 1.49 4.69 100000
    select_2_rows 16.00 3.52 1.95 5.47 100000
    select_big 8.00 5.75 0.08 5.83 80
    select_big_str 4.00 1.31 0.40 1.71 10000
    select_cache 32.00 0.96 0.15 1.11 10000
    select_cache2 31.00 0.99 0.14 1.13 10000
    select_column+column 16.00 1.89 1.35 3.24 100000
    select_diff_key 1.00 0.06 0.01 0.07 500
    select_distinct 1.00 0.68 0.02 0.70 800
    select_group 8.00 0.60 0.05 0.65 2911
    select_group_when_MANY_tables 3.00 0.29 0.15 0.44 10000
    select_join 0.00 0.23 0.01 0.24 100
    select_key 51.00 18.04 3.04 21.08 200000
    select_key2 56.00 20.67 3.44 24.11 200000
    select_key2_return_key 54.00 19.87 3.44 23.31 200000
    select_key2_return_prim 54.00 20.30 3.17 23.47 200000
    select_key_prefix 57.00 21.20 3.65 24.85 200000
    select_key_prefix_join 3.00 1.52 0.02 1.54 100
    select_key_return_key 51.00 16.16 3.12 19.28 200000
    select_many_fields 9.00 1.88 0.27 2.15 2000
    select_range 18.00 3.03 0.05 3.08 410
    select_range_key2 6.00 1.98 0.25 2.23 25010
    select_range_prefix 7.00 2.32 0.26 2.58 25010
    select_simple 11.00 2.33 1.31 3.64 100000
    select_simple_cache 10.00 2.02 1.22 3.24 100000
    select_simple_join 1.00 0.27 0.00 0.27 500
    update_big 83.00 0.00 0.00 0.00 10
    update_of_key 784.00 2.32 0.84 3.16 50000
    update_of_key_big 40.00 0.02 0.01 0.03 501
    update_of_primary_key_many_keys 4076.00 0.01 0.01 0.02 256
    update_with_key 4554.00 13.11 5.03 18.14 300000
    update_with_key_prefix 1544.99 7.33 1.85 9.18 100000 +
    wisc_benchmark 2.00 0.96 0.04 1.00 114
    TOTALS 26565.99 293.04 57.67 350.71 3425950 +

数据库系统的优化,可以从几个方面来考虑:

  1. 连接优化
  2. 索引优化
  3. 查询优化
  4. 实践

本系列也将按照此顺序来进行,逐步探索获取最有效率的结构。

WORKING ON…,and KEEP EYES ON IT…

下一篇 mysql优化之连接优化


(原文链接 http://ddbiz.com/?p=931)

标签:

留言/有话要说

Copyright © 2012 Dimensions Data Business. All rights reserved.
server process 3.830 seconds.
WP-Backgrounds Lite by InoPlugs Web Design and Juwelier Schönmann 1010 Wien