Category Archives: MySQL

mysql性能优化-慢查询分析、优化索引和配置

目录

一、优化概述

二、查询与索引优化分析

1性能瓶颈定位

Show命令

慢查询日志

explain分析查询

profiling分析查询

2索引及查询优化

三、配置优化

1)      max_connections

2)      back_log

3)      interactive_timeout

4)      key_buffer_size

5)      query_cache_size

6)      record_buffer_size

7)      read_rnd_buffer_size

8)      sort_buffer_size

9)      join_buffer_size

10)    table_cache

11)    max_heap_table_size

12)    tmp_table_size

13)    thread_cache_size

14)    thread_concurrency

15)    wait_timeout

一、 优化概述

MySQL 数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于 内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候那么平瓶颈就会出现在网络上,我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。

除了服务器硬件的性能瓶颈,对于MySQL系统本身,我们可以使用工具来优化数据库的性能,通常有三种:使用索引,使用EXPLAIN分析查询以及调整MySQL的内部配置。

二、查询与索引优化分析

在优化MySQL时,通常需要对数据库进行分析,常见的分析手段有慢查询日志,EXPLAIN 分析查询,profiling分析以及show命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

1 性能瓶颈定位Show命令

我们可以通过show命令查看MySQL状态及变量,找到系统的瓶颈:

Mysql> show status ——显示状态信息(扩展show status like ‘XXX’)

Mysql> show variables ——显示系统变量(扩展show variables like ‘XXX’)

Mysql> show innodb status ——显示InnoDB存储引擎的状态

Mysql> show processlist ——查看当前SQL执行,包括执行状态、是否锁表等

Shell> mysqladmin variables -u username -p password——显示系统变量

Shell> mysqladmin extended-status -u username -p password——显示状态信息

查看状态变量及帮助:

Shell> mysqld –verbose –help [|more #逐行显示]

比较全的Show命令的使用可参考: http://blog.phpbean.com/a.cn/18/

慢查询日志

慢查询日志开启:

在配置文件my.cnf或my.ini中在[mysqld]一行下面加入两个配置参数

log-slow-queries=/data/mysqldata/slow-query.log

long_query_time=2

注:log-slow-queries参数为慢查询日志存放的位置,一般这个目录要有mysql的运行帐号的可写权限,一般都将这个目录设置为mysql的数据存放目录;

long_query_time=2中的2表示查询超过两秒才记录;

在my.cnf或者my.ini中添加log-queries-not-using-indexes参数,表示记录下没有使用索引的查询。

log-slow-queries=/data/mysqldata/slow-query.log

long_query_time=10

log-queries-not-using-indexes

慢查询日志开启方法二:

我们可以通过命令行设置变量来即时启动慢日志查询。由下图可知慢日志没有打开,slow_launch_time=# 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加

设置慢日志开启

MySQL后可以查询long_query_time 的值 。

为了方便测试,可以将修改慢查询时间为5秒。

慢查询分析mysqldumpslow

我们可以通过打开log文件查看得知哪些SQL执行效率低下

[root@localhost mysql]# more slow-query.log

# Time: 081026 19:46:34

# User@Host: root[root] @ localhost []

# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961

select count(*) from t_user;

从日志中,可以发现查询时间超过5 秒的SQL,而小于5秒的没有出现在此日志中。

如果慢查询日志中记录内容很多,可以使用mysqldumpslow工具(MySQL客户端安装自带)来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总,显示汇总后摘要结果。

进入log的存放目录,运行

[root@mysql_data]#mysqldumpslow  slow-query.log

Reading mysql slow query log from slow-query.log

Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql

select count(N) from t_user;

mysqldumpslow命令

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log

这会输出记录次数最多的10条SQL语句,其中:

-s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;

-t, 是top n的意思,即为返回前面多少条的数据;

-g, 后边可以写一个正则匹配模式,大小写不敏感的;

例如:

/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log

得到返回记录集最多的10个查询。

/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log

得到按照时间排序的前10条里面含有左连接的查询语句。

使 用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句,对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。 开启慢查询日志后,由于日志记录操作,在一定程度上会占用CPU资源影响mysql的性能,但是可以阶段性开启来定位性能瓶颈。

explain分析查询

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

– 表的读取顺序

– 数据读取操作的操作类型

– 哪些索引可以使用

– 哪些索引被实际使用

– 表之间的引用

– 每张表有多少行被优化器查询

EXPLAIN字段:

ØTable:显示这一行的数据是关于哪张表的

Øpossible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

Økey:实际使用的索引。如果为NULL,则没有使用索引。MYSQL很少会选择优化不足的索引,此时可以在SELECT语句中使用USE INDEX(index)来强制使用一个索引或者用IGNORE INDEX(index)来强制忽略索引

Økey_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

Øref:显示索引的哪一列被使用了,如果可能的话,是一个常数

Ørows:MySQL认为必须检索的用来返回请求数据的行数

Øtype:这是最重要的字段之一,显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL

nsystem、const:可以将查询的变量转为常量.  如id=1; id为 主键或唯一键.

neq_ref:访问索引,返回某单一行的数据.(通常在联接时出现,查询使用的索引为主键或惟一键)

nref:访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生

nrange:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西,并且该字段上建有索引时发生的情况(注:不一定好于index)

nindex:以索引的顺序进行全表扫描,优点是不用排序,缺点是还要全表扫描

nALL:全表扫描,应该尽量避免

ØExtra:关于MYSQL如何解析查询的额外信息,主要有以下几种

nusing index:只用到索引,可以避免访问表.

nusing where:使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.

nusing tmporary:用到临时表

nusing filesort:用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)

nrange checked for eache record(index map:N):没有好的索引.

profiling分析查询

通过慢日志查询可以知道哪些SQL语句执行效率低下,通过explain我们可以得知SQL语句的具体执行情况,索引使用等,还可以结合show命令查看执行状态。

如果觉得explain的信息不够详细,可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。

profiling默认是关闭的。可以通过以下语句查看

 

打开功能: mysql>set profiling=1; 执行需要测试的sql 语句:

mysql> show profiles\G; 可以得到被执行的SQL语句的时间和ID

mysql>show profile for query 1; 得到对应SQL语句执行的详细信息

Show Profile命令格式:

SHOW PROFILE [type [, type] … ]

    [FOR QUERY n]

    [LIMIT row_count [OFFSET offset]]

type:

    ALL

  | BLOCK IO

  | CONTEXT SWITCHES

  | CPU

  | IPC

  | MEMORY

  | PAGE FAULTS

  | SOURCE

  | SWAPS

以 上的16rows是针对非常简单的select语句的资源信息,对于较复杂的SQL语句,会有更多的行和字段,比如converting HEAP to MyISAM 、Copying to tmp table等等,由于以上的SQL语句不存在复杂的表操作,所以未显示这些字段。通过profiling资源耗费信息,我们可以采取针对性的优化措施。

测试完毕以后 ,关闭参数:mysql> set profiling=0

2     索引及查询优化

索引的类型

Ø 普通索引:这是最基本的索引类型,没唯一性之类的限制。

Ø 唯一性索引:和普通索引基本相同,但所有的索引列值保持唯一性。

Ø 主键:主键是一种唯一索引,但必须指定为”PRIMARY KEY”。

Ø 全文索引:MYSQL从3.23.23开始支持全文索引和全文检索。在MYSQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B树中存储。空间列类型的索引使用R-树,MEMORY表支持hash索引。

单列索引和多列索引(复合索引)

索引可以是单列索引,也可以是多列索引。对相关的列使用索引是提高SELECT操作性能的最佳途径之一。

多列索引:

MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型,可以索引列的左前缀,列的顺序非常重要。

多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。一般来说,即使是限制最严格的单列索引,它的限制能力也远远低于多列索引。

最左前缀

多列索引有一个特点,即最左前缀(Leftmost Prefixing)。假如有一个多列索引为key(firstname lastname age),当搜索条件是以下各种列的组合和顺序时,MySQL将使用该多列索引:

firstname,lastname,age

firstname,lastname

firstname

也就是说,相当于还建立了key(firstname lastname)和key(firstname)。

索引主要用于下面的操作:

Ø 快速找出匹配一个WHERE子句的行。

Ø 删除行。当执行联接时,从其它表检索行。

Ø 对 具体有索引的列key_col找出MAX()或MIN()值。由预处理器进行优化,检查是否对索引中在key_col之前发生所有关键字元素使用了 WHERE key_part_# = constant。在这种情况下,MySQL为每个MIN()或MAX()表达式执行一次关键字查找,并用常数替 换它。如果所有表达式替换为常量,查询立即返回。例如:

SELECT MIN(key2), MAX (key2)  FROM tb WHERE key1=10;

Ø 如果对一个可用关键字的最左面的前缀进行了排序或分组(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有关键字元素后面有DESC,关键字以倒序被读取。

Ø 在一些情况中,可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列,为了更快,可以从索引树检索出值。

SELECT key_part3 FROM tb WHERE key_part1=1

有 时MySQL不使用索引,即使有可用的索引。一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时。(在这种情况下,表扫描可能会更快 些)。然而,如果此类查询使用LIMIT只搜索部分行,MySQL则使用索引,因为它可以更快地找到几行并在结果中返回。例如:

合理的建立索引的建议:

(1)  越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。

(2)  简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。

(3)  尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值

这部分是关于索引和写SQL语句时应当注意的一些琐碎建议和注意点。

1. 当结果集只有一行数据时使用LIMIT 1

2. 避免SELECT *,始终指定你需要的列

从表中读取越多的数据,查询会变得更慢。他增加了磁盘需要操作的时间,还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟,仅仅是因为数据不必要的在服务器之间传输。

3. 使用连接(JOIN)来代替子查询(Sub-Queries)

       连接(JOIN).. 之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

4. 使用ENUMCHAR 而不是VARCHAR,使用合理的字段属性长度

5. 尽可能的使用NOT NULL

6. 固定长度的表会更快

7. 拆分大的DELETE INSERT 语句

8. 查询的列越小越快

 Where条件

在查询中,WHERE条件也是一个比较重要的因素,尽量少并且是合理的where条件是很重要的,尽量在多个条件的时候,把会提取尽量少数据量的条件放在前面,减少后一个where条件的查询时间。

有些where条件会导致索引无效:

Ø where子句的查询条件里有!=,MySQL将无法使用索引。

Ø where子句使用了Mysql函数的时候,索引将无效,比如:select * from tb where left(name, 4) = ‘xxx’

Ø 使用LIKE进行搜索匹配的时候,这样索引是有效的:select * from tbl1 where name like ‘xxx%’,而like ‘%xxx%’ 时索引无效

三、    配置优化

安 装MySQL后,配置文件my.cnf在 /MySQL安装目录/share/mysql目录中,该目录中还包含多个配置文件可供参考,有my-large.cnf ,my-huge.cnf,  my-medium.cnf,my-small.cnf,分别对应大中小型数据库应用的配置。win环境下即存在于MySQL安装目录中的.ini文 件。

下面列出了对性能优化影响较大的主要变量,主要分为连接请求的变量和缓冲区变量。

1.   连接请求的变量:

1)     max_connections

MySQL 的最大连接数,增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多, 介于MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。

数值过小会经常出现ERROR 1040: Too many connections错误,可以过’conn%’通配符查看当前状态的连接数量,以定夺该值的大小。

show variables like ‘max_connections’ 最大连接数

show  status like ‘max_used_connections’响应的连接数

如下:

mysql> show variables like ‘max_connections‘;

+———————–+——-+

| Variable_name | Value |

+———————–+——-+

| max_connections | 256  |

+———————–+——-+

mysql> show status like ‘max%connections‘;

+———————–+——-+

| Variable_name       | Value |

+—————————-+——-+

| max_used_connections | 256|

+—————————-+——-+

max_used_connections / max_connections * 100% (理想值≈ 85%)

如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了,低于10%则设置过大。

2)     back_log

MySQL 能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用。如果MySQL的连接数据达到 max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过 back_log,将不被授予连接资源。

back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。

当 观察你主机进程列表(mysql> show full processlist),发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时,就要加大back_log 的值了。

默认数值是50,可调优为128,对于Linux系统设置范围为小于512的整数。

3)     interactive_timeout

一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE 选项的客户。

默认数值是28800,可调优为7200。

2.   缓冲区变量

全局缓冲:

4)     key_buffer_size

key_buffer_size 指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道 key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。

key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

举例如下:

mysql> show variables like ‘key_buffer_size‘;

+——————-+————+

| Variable_name | Value      |

+———————+————+

| key_buffer_size | 536870912 |

+———— ———-+————+

key_buffer_size为512MB,我们再看一下key_buffer_size的使用情况:

mysql> show global status like ‘key_read%‘;

+————————+————-+

| Variable_name   | Value    |

+————————+————-+

| Key_read_requests| 27813678764 |

| Key_reads   |  6798830      |

+————————+————-+

一共有27813678764个索引读取请求,有6798830个请求在内存中没有找到直接从硬盘读取索引,计算索引未命中缓存的概率:

key_cache_miss_rate =Key_reads / Key_read_requests * 100%,设置在1/1000左右较好

默认配置数值是8388600(8M),主机有4GB内存,可以调优值为268435456(256MB)。

5)     query_cache_size

使用查询缓冲,MySQL将查询结果存放在缓冲区中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。

通 过检查状态值Qcache_*,可以知道query_cache_size设置是否合理(上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得)。如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,如果Qcache_hits的值也 非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;如果Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反 而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type指定是否使用查询缓冲,可以设置为0、1、2,该变量是SESSION级的变量。

query_cache_limit指定单个查询能够使用的缓冲区大小,缺省为1M。

query_cache_min_res_unit 是在4.1版本以后引入的,它指定分配缓冲区空间的最小单位,缺省为4K。检查状态值Qcache_free_blocks,如果该值非常大,则表明缓冲 区中碎片很多,这就表明查询结果都比较小,此时需要减小query_cache_min_res_unit。

举例如下:

mysql> show global status like ‘qcache%‘;

+——————————-+—————–+

| Variable_name                  | Value        |

+——————————-+—————–+

| Qcache_free_blocks        | 22756       |

| Qcache_free_memory     | 76764704    |

| Qcache_hits           | 213028692 |

| Qcache_inserts         | 208894227   |

| Qcache_lowmem_prunes   | 4010916      |

| Qcache_not_cached | 13385031    |

| Qcache_queries_in_cache | 43560 |

| Qcache_total_blocks          | 111212      |

+——————————-+—————–+

mysql> show variables like ‘query_cache%‘;

+————————————–+————–+

| Variable_name            | Value      |

+————————————–+———–+

| query_cache_limit         | 2097152     |

| query_cache_min_res_unit      | 4096    |

| query_cache_size         | 203423744 |

| query_cache_type        | ON           |

| query_cache_wlock_invalidate | OFF   |

+————————————–+—————+

查询缓存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。

查询缓存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小,要不就是碎片太多。

查询缓存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

示例服务器查询缓存碎片率=20.46%,查询缓存利用率=62.26%,查询缓存命中率=1.94%,命中率很差,可能写操作比较频繁吧,而且可能有些碎片。

每个连接的缓冲

6)    record_buffer_size

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。

默认数值是131072(128K),可改为16773120 (16M)

7)     read_rnd_buffer_size

随 机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜 索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。

一般可设置为16M

8)     sort_buffer_size

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。

默认数值是2097144(2M),可改为16777208 (16M)。

9)     join_buffer_size

联合查询操作所能使用的缓冲区大小

record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size为每个线程独占,也就是说,如果有100个线程连接,则占用为16M*100

10)  table_cache

表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tablesOpened_tables,可以决定是否需要增加table_cache的值。如 果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了 (上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能 不稳定或者连接失败。

1G内存机器,推荐值是128-256。内存在4GB左右的服务器该参数可设置为256M或384M。

11)  max_heap_table_size

用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变,即set @max_heap_table_size=#

这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。

12)  tmp_table_size

通过设置tmp_table_size选项来增加一张临时表的大小,例如做高级GROUP BY操作生成的临时表。如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于硬盘的MyISAM表

mysql> show global status like ‘created_tmp%‘;

+——————————–+———+

| Variable_name             | Value |

+———————————-+———+

| Created_tmp_disk_tables | 21197  |

| Created_tmp_files   | 58  |

| Created_tmp_tables  | 1771587 |

+——————————–+———–+

每 次创建临时表,Created_tmp_tables增加,如果临时表大小超过tmp_table_size,则是在磁盘上创建临时 表,Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数,比较理想的配 置是:

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% =1.20%,应该相当好了

默认为16M,可调到64-256最佳,线程独占,太大可能内存不够I/O堵塞

13)  thread_cache_size

可以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以这个变量值。

通过比较 Connections和Threads_created状态的变量,可以看到这个变量的作用。

默认值为110,可调优为80。

14)  thread_concurrency

推荐设置为服务器 CPU核数的2倍,例如双核的CPU, 那么thread_concurrency的应该为4;2个双核的cpu, thread_concurrency的值应为8。默认为8

15)  wait_timeout

指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。

3.    配置InnoDB的几个变量

innodb_buffer_pool_size

对于InnoDB表来说,innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器,最大可以把该值设置成物理内存的80%。

根据MySQL手册,对于2G内存的机器,推荐值是1G(50%)。

innodb_flush_log_at_trx_commit

主 要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,取值分别为0、1、2三个。0,表示当事务提交时,不做日志写入操作,而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次;1,则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作,确保了事务的 ACID;设置为2,每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。

实际测试发现,该值对插入数据的速度影响非常大,设置为2时插入10000条记录只需要2秒,设置为0时只需要1秒,而设置为1时则需要229秒。因此,MySQL手册也建议尽量将插入操作合并成一个事务,这样可以大幅提高速度。

根据MySQL手册,在允许丢失最近部分事务的危险的前提下,可以把该值设为0或2。

innodb_log_buffer_size

log缓存大小,一般为1-8M,默认为1M,对于较大的事务,可以增大缓存大小。

可设置为4M或8M。

innodb_additional_mem_pool_size

该参数指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大,只要够用就行,应该与表结构的复杂度有关系。如果不够用,MySQL会在错误日志中写入一条警告信息。

根据MySQL手册,对于2G内存的机器,推荐值是20M,可适当增加。

innodb_thread_concurrency=8

推荐设置为 2*(NumCPUs+NumDisks),默认一般为8

refer:

mysql索引攻略

from:http://awen7916.iteye.com/blog/2266320

MySQL索引原理及慢查询优化

MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色,但所谓“好马配好鞍”,如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重。
本人从13年7月份起,一直在美团核心业务系统部做慢查询的优化工作,共计十余个系统,累计解决和积累了上百个慢查询案例。随着业务的复杂性提升,遇到的问题千奇百怪,五花八门,匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。

一个慢查询引发的思考

select
   count(*) 
from
   task 
where
   status=2 
   and operator_id=20839 
   and operate_time>1371169729 
   and operate_time<1371174603 
   and type=2;

系统使用者反应有一个功能越来越慢,于是工程师找到了上面的SQL。
并且兴致冲冲的找到了我,“这个SQL需要优化,给我把每个字段都加上索引”
我很惊讶,问道“为什么需要每个字段都加上索引?”
“把查询的字段都加上索引会更快”工程师信心满满
“这种情况完全可以建一个联合索引,因为是最左前缀匹配,所以operate_time需要放到最后,而且还需要把其他相关的查询都拿来,需要做一个综合评估。”
“联合索引?最左前缀匹配?综合评估?”工程师不禁陷入了沉思。
多数情况下,我们知道索引能够提高查询效率,但应该如何建立索引?索引的顺序如何?许多人却只知道大概。其实理解这些概念并不难,而且索引的原理远没有想象的那么复杂。

MySQL索引原理

##索引目的
索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的,如果我想找到m开头的单词呢?或者ze开头的单词呢?是不是觉得如果没有索引,这个事情根本无法完成?

##索引原理
除了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?我们回想字典的例子,能不能把数据分成段,然后分段查询呢?最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段……这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。但如果是1千万的记录呢,分成几段比较好?稍有算法基础的同学会想到搜索树,其平均复杂度是lgN,具有不错的查询性能。但这里我们忽略了一个关键的问题,复杂度模型是基于每次相同的操作成本来考虑的,数据库实现比较复杂,数据保存在磁盘上,而为了提高性能,每次又可以把部分数据读入内存来计算,因为我们知道访问磁盘的成本大概是访问内存的十万倍左右,所以简单的搜索树难以满足复杂的应用场景。

###磁盘IO与预读
前面提到了访问磁盘,那么这里先简单介绍一下磁盘IO和预读,磁盘读取数据靠的是机械运动,每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分,寻道时间指的是磁臂移动到指定磁道所需要的时间,主流磁盘一般在5ms以下;旋转延迟就是我们经常听说的磁盘转速,比如一个磁盘7200转,表示每分钟能转7200次,也就是说1秒钟能转120次,旋转延迟就是1/120/2 = 4.17ms;传输时间指的是从磁盘读出或将数据写入磁盘的时间,一般在零点几毫秒,相对于前两个时间可以忽略不计。那么访问一次磁盘的时间,即一次磁盘IO的时间约等于5+4.17 = 9ms左右,听起来还挺不错的,但要知道一台500 -MIPS的机器每秒可以执行5亿条指令,因为指令依靠的是电的性质,换句话说执行一次IO的时间可以执行40万条指令,数据库动辄十万百万乃至千万级数据,每次9毫秒的时间,显然是个灾难。下图是计算机硬件延迟的对比图,供大家参考:
various-system-software-hardware-latencies
考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。

###索引的数据结构
前面讲了生活中索引的例子,索引的基本原理,数据库的复杂性,又讲了操作系统的相关知识,目的就是让大家了解,任何一种数据结构都不是凭空产生的,一定会有它的背景和使用场景,我们现在总结一下,我们需要这种数据结构能够做些什么,其实很简单,那就是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,b+树应运而生。

###详解b+树
b+树
如上图,是一颗b+树,关于b+树的定义可以参见B+树,这里只说一些重点,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

###b+树的查找过程
如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

###b+树性质
1.通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。
2.当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

慢查询优化

关于MySQL索引原理是比较枯燥的东西,大家只需要有一个感性的认识,并不需要理解得非常透彻和深入。我们回头来看看一开始我们说的慢查询,了解完索引原理之后,大家是不是有什么想法呢?先总结一下索引的几大基本原则

建索引的几大原则

1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

回到开始的慢查询

根据最左匹配原则,最开始的sql语句的索引应该是status、operator_id、type、operate_time的联合索引;其中status、operator_id、type的顺序可以颠倒,所以我才会说,把这个表的所有相关查询都找到,会综合分析;
比如还有如下查询

select * from task where status = 0 and type = 12 limit 10;
select count(*) from task where status = 0 ;

那么索引建立成(status,type,operator_id,operate_time)就是非常正确的,因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则

查询优化神器 – explain命令

关于explain命令相信大家并不陌生,具体用法和字段含义可以参考官网explain-output,这里需要强调rows是核心指标,绝大部分rows小的语句执行一定很快(有例外,下面会讲到)。所以优化语句基本上都是在优化rows。

慢查询优化基本步骤

0.先运行看看是否真的很慢,注意设置SQL_NO_CACHE
1.where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
2.explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果,不符合预期继续从0分析

几个慢查询案例

下面几个例子详细解释了如何分析和优化慢查询

复杂语句写法

很多情况下,我们写SQL只是为了实现功能,这只是第一步,不同的语句书写方式对于效率往往有本质的差别,这要求我们对mysql的执行计划和索引原则有非常清楚的认识,请看下面的语句

select
   distinct cert.emp_id 
from
   cm_log cl 
inner join
   (
      select
         emp.id as emp_id,
         emp_cert.id as cert_id 
      from
         employee emp 
      left join
         emp_certificate emp_cert 
            on emp.id = emp_cert.emp_id 
      where
         emp.is_deleted=0
   ) cert 
      on (
         cl.ref_table='Employee' 
         and cl.ref_oid= cert.emp_id
      ) 
      or (
         cl.ref_table='EmpCertificate' 
         and cl.ref_oid= cert.cert_id
      ) 
where
   cl.last_upd_date >='2013-11-07 15:03:00' 
   and cl.last_upd_date<='2013-11-08 16:00:00';

0.先运行一下,53条记录 1.87秒,又没有用聚合语句,比较慢

53 rows in set (1.87 sec)

1.explain

+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| id | select_type | table      | type  | possible_keys                   | key                   | key_len | ref               | rows  | Extra                          |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
|  1 | PRIMARY     | cl         | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date     | 8       | NULL              |   379 | Using where; Using temporary   |
|  1 | PRIMARY     | <derived2> | ALL   | NULL                            | NULL                  | NULL    | NULL              | 63727 | Using where; Using join buffer |
|  2 | DERIVED     | emp        | ALL   | NULL                            | NULL                  | NULL    | NULL              | 13317 | Using where                    |
|  2 | DERIVED     | emp_cert   | ref   | emp_certificate_empid           | emp_certificate_empid | 4       | meituanorg.emp.id |     1 | Using index                    |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+

简述一下执行计划,首先mysql根据idx_last_upd_date索引扫描cm_log表获得379条记录;然后查表扫描了63727条记录,分为两部分,derived表示构造表,也就是不存在的表,可以简单理解成是一个语句形成的结果集,后面的数字表示语句的ID。derived2表示的是ID = 2的查询构造了虚拟表,并且返回了63727条记录。我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据,首先全表扫描employee表13317条记录,然后根据索引emp_certificate_empid关联emp_certificate表,rows = 1表示,每个关联都只锁定了一条记录,效率比较高。获得后,再和cm_log的379条记录根据规则关联。从执行过程上可以看出返回了太多的数据,返回的数据绝大部分cm_log都用不到,因为cm_log只锁定了379条记录。
如何优化呢?可以看到我们在运行完后还是要和cm_log做join,那么我们能不能之前和cm_log做join呢?仔细分析语句不难发现,其基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表,如果ref_table是Employee就关联employee表,我们完全可以拆成两部分,并用union连接起来,注意这里用union,而不用union all是因为原语句有“distinct”来得到唯一的记录,而union恰好具备了这种功能。如果原语句中没有distinct不需要去重,我们就可以直接使用union all了,因为使用union需要去重的动作,会影响SQL性能。
优化过的语句如下

select
   emp.id 
from
   cm_log cl 
inner join
   employee emp 
      on cl.ref_table = 'Employee' 
      and cl.ref_oid = emp.id  
where
   cl.last_upd_date >='2013-11-07 15:03:00' 
   and cl.last_upd_date<='2013-11-08 16:00:00' 
   and emp.is_deleted = 0  
union
select
   emp.id 
from
   cm_log cl 
inner join
   emp_certificate ec 
      on cl.ref_table = 'EmpCertificate' 
      and cl.ref_oid = ec.id  
inner join
   employee emp 
      on emp.id = ec.emp_id  
where
   cl.last_upd_date >='2013-11-07 15:03:00' 
   and cl.last_upd_date<='2013-11-08 16:00:00' 
   and emp.is_deleted = 0

4.不需要了解业务场景,只需要改造的语句和改造之前的语句保持结果一致

5.现有索引可以满足,不需要建索引

6.用改造后的语句实验一下,只需要10ms 降低了近200倍!

+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| id | select_type  | table      | type   | possible_keys                   | key               | key_len | ref                   | rows | Extra       |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
|  1 | PRIMARY      | cl         | range  | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where |
|  1 | PRIMARY      | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 | Using where |
|  2 | UNION        | cl         | range  | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where |
|  2 | UNION        | ec         | eq_ref | PRIMARY,emp_certificate_empid   | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 |             |
|  2 | UNION        | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.ec.emp_id  |    1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL                            | NULL              | NULL    | NULL                  | NULL |             |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
53 rows in set (0.01 sec)

明确应用场景

举这个例子的目的在于颠覆我们对列的区分度的认知,一般上我们认为区分度越高的列,越容易锁定更少的记录,但在一些特殊的情况下,这种理论是有局限性的

select
   * 
from
   stage_poi sp 
where
   sp.accurate_result=1 
   and (
      sp.sync_status=0 
      or sp.sync_status=2 
      or sp.sync_status=4
   );

0.先看看运行多长时间,951条数据6.22秒,真的很慢

951 rows in set (6.22 sec)

1.先explain,rows达到了361万,type = ALL表明是全表扫描

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | sp    | ALL  | NULL          | NULL | NULL    | NULL | 3613155 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

2.所有字段都应用查询返回记录数,因为是单表查询 0已经做过了951条

3.让explain的rows 尽量逼近951

看一下accurate_result = 1的记录数

select count(*),accurate_result from stage_poi  group by accurate_result;
+----------+-----------------+
| count(*) | accurate_result |
+----------+-----------------+
|     1023 |              -1 |
|  2114655 |               0 |
|   972815 |               1 |
+----------+-----------------+

我们看到accurate_result这个字段的区分度非常低,整个表只有-1,0,1三个值,加上索引也无法锁定特别少量的数据

再看一下sync_status字段的情况

select count(*),sync_status from stage_poi  group by sync_status;
+----------+-------------+
| count(*) | sync_status |
+----------+-------------+
|     3080 |           0 |
|  3085413 |           3 |
+----------+-------------+

同样的区分度也很低,根据理论,也不适合建立索引

问题分析到这,好像得出了这个表无法优化的结论,两个列的区分度都很低,即便加上索引也只能适应这种情况,很难做普遍性的优化,比如当sync_status 0、3分布的很平均,那么锁定记录也是百万级别的

4.找业务方去沟通,看看使用场景。业务方是这么来使用这个SQL语句的,每隔五分钟会扫描符合条件的数据,处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多,1000个左右。了解了业务方的使用场景后,优化这个SQL就变得简单了,因为业务方保证了数据的不平衡,如果加上索引可以过滤掉绝大部分不需要的数据

5.根据建立索引规则,使用如下语句建立索引

alter table stage_poi add index idx_acc_status(accurate_result,sync_status);

6.观察预期结果,发现只需要200ms,快了30多倍。

952 rows in set (0.20 sec)

我们再来回顾一下分析问题的过程,单表查询相对来说比较好优化,大部分时候只需要把where条件里面的字段依照规则加上索引就好,如果只是这种“无脑”优化的话,显然一些区分度非常低的列,不应该加索引的列也会被加上索引,这样会对插入、更新性能造成严重的影响,同时也有可能影响其它的查询语句。所以我们第4步调差SQL的使用场景非常关键,我们只有知道这个业务场景,才能更好地辅助我们更好的分析和优化查询语句。

无法优化的语句

select
   c.id,
   c.name,
   c.position,
   c.sex,
   c.phone,
   c.office_phone,
   c.feature_info,
   c.birthday,
   c.creator_id,
   c.is_keyperson,
   c.giveup_reason,
   c.status,
   c.data_source,
   from_unixtime(c.created_time) as created_time,
   from_unixtime(c.last_modified) as last_modified,
   c.last_modified_user_id  
from
   contact c  
inner join
   contact_branch cb 
      on  c.id = cb.contact_id  
inner join
   branch_user bu 
      on  cb.branch_id = bu.branch_id 
      and bu.status in (
         1,
      2)  
   inner join
      org_emp_info oei 
         on  oei.data_id = bu.user_id 
         and oei.node_left >= 2875 
         and oei.node_right <= 10802 
         and oei.org_category = - 1  
   order by
      c.created_time desc  limit 0 ,
      10;

还是几个步骤
0.先看语句运行多长时间,10条记录用了13秒,已经不可忍受

10 rows in set (13.06 sec)

1.explain

+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                       | key                     | key_len | ref                      | rows | Extra                                        |
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | oei   | ref    | idx_category_left_right,idx_data_id | idx_category_left_right | 5       | const                    | 8849 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | bu    | ref    | PRIMARY,idx_userid_status           | idx_userid_status       | 4       | meituancrm.oei.data_id   |   76 | Using where; Using index                     |
|  1 | SIMPLE      | cb    | ref    | idx_branch_id,idx_contact_branch_id | idx_branch_id           | 4       | meituancrm.bu.branch_id  |    1 |                                              |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY                             | PRIMARY                 | 108     | meituancrm.cb.contact_id |    1 |                                              |
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+

从执行计划上看,mysql先查org_emp_info表扫描8849记录,再用索引idx_userid_status关联branch_user表,再用索引idx_branch_id关联contact_branch表,最后主键关联contact表。
rows返回的都非常少,看不到有什么异常情况。我们在看一下语句,发现后面有order by + limit组合,会不会是排序量太大搞的?于是我们简化SQL,去掉后面的order by 和 limit,看看到底用了多少记录来排序

select
  count(*)
from
   contact c  
inner join
   contact_branch cb 
      on  c.id = cb.contact_id  
inner join
   branch_user bu 
      on  cb.branch_id = bu.branch_id 
      and bu.status in (
         1,
      2)  
   inner join
      org_emp_info oei 
         on  oei.data_id = bu.user_id 
         and oei.node_left >= 2875 
         and oei.node_right <= 10802 
         and oei.org_category = - 1  
+----------+
| count(*) |
+----------+
|   778878 |
+----------+
1 row in set (5.19 sec)

发现排序之前居然锁定了778878条记录,如果针对70万的结果集排序,将是灾难性的,怪不得这么慢,那我们能不能换个思路,先根据contact的created_time排序,再来join会不会比较快呢?
于是改造成下面的语句,也可以用straight_join来优化
select
c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = – 1
where
c.id = cb.contact_id
)
order by
c.created_time desc limit 0 ,
10;

验证一下效果 预计在1ms内,提升了13000多倍!
```sql
10 rows in set (0.00 sec)

本以为至此大工告成,但我们在前面的分析中漏了一个细节,先排序再join和先join再排序理论上开销是一样的,为何提升这么多是因为有一个limit!大致执行过程是:mysql先按索引排序得到前10条记录,然后再去join过滤,当发现不够10条的时候,再次去10条,再次join,这显然在内层join过滤的数据非常多的时候,将是灾难的,极端情况,内层一条数据都找不到,mysql还傻乎乎的每次取10条,几乎遍历了这个数据表!
用不同参数的SQL试验下

select
   sql_no_cache   c.id,
   c.name,
   c.position,
   c.sex,
   c.phone,
   c.office_phone,
   c.feature_info,
   c.birthday,
   c.creator_id,
   c.is_keyperson,
   c.giveup_reason,
   c.status,
   c.data_source,
   from_unixtime(c.created_time) as created_time,
   from_unixtime(c.last_modified) as last_modified,
   c.last_modified_user_id    
from
   contact c   
where
   exists (
      select
         1        
      from
         contact_branch cb         
      inner join
         branch_user bu                     
            on  cb.branch_id = bu.branch_id                     
            and bu.status in (
               1,
            2)                
         inner join
            org_emp_info oei                           
               on  oei.data_id = bu.user_id                           
               and oei.node_left >= 2875                           
               and oei.node_right <= 2875                           
               and oei.org_category = - 1                
         where
            c.id = cb.contact_id           
      )        
   order by
      c.created_time desc  limit 0 ,
      10;
Empty set (2 min 18.99 sec)

2 min 18.99 sec!比之前的情况还糟糕很多。由于mysql的nested loop机制,遇到这种情况,基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。
通过这个例子我们可以看到,并不是所有语句都能优化,而往往我们优化时,由于SQL用例回归时落掉一些极端情况,会造成比原来还严重的后果。所以,第一:不要指望所有语句都能通过SQL优化,第二:不要过于自信,只针对具体case来优化,而忽略了更复杂的情况。

慢查询的案例就分析到这儿,以上只是一些比较典型的案例。我们在优化过程中遇到过超过1000行,涉及到16个表join的“垃圾SQL”,也遇到过线上线下数据库差异导致应用直接被慢查询拖死,也遇到过varchar等值比较没有写单引号,还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累,如果我们熟悉查询优化器、索引的内部原理,那么分析这些案例就变得特别简单了。

写在后面的话

本文以一个慢查询案例引入了MySQL索引原理、优化慢查询的一些方法论;并针对遇到的典型案例做了详细的分析。其实做了这么长时间的语句优化后才发现,任何数据库层面的优化都抵不上应用系统的优化,同样是MySQL,可以用来支撑Google/FaceBook/Taobao应用,但可能连你的个人网站都撑不住。套用最近比较流行的话:“查询容易,优化不易,且写且珍惜!”

参考

参考文献如下:
1.《高性能MySQL》
2.《数据结构与算法分析》

from:http://tech.meituan.com/mysql-index.html

Ubuntu10下搭建MySQL Proxy读写分离

一、MySQL-Proxy基础

MySQL Proxy是一个处于你的Client端和MySQL server端之间的简单程序,它可以监测、分析或改变它们的通信。它使用灵活,没有限制,常见的用途包括:负载平衡,故障、查询分析,查询过滤和修改等等。

p_1

(Figure1:MySQL Proxy)

MySQL-Proxy, announced in June, is a binary application that sits between your MySQL client and server, and supports the embedded scripting language Lua. The proxy can  be used to analyze, monitor and transform communication, and supports a wide range of scenarios including:

One of the more powerful features of MySQL Proxy is the ability to do “Read/Write Splitting“. The basic concept is to have a master database handle transactional queries while slaves handle SELECT queries. Replication is used to synchronize the changes due to transactional queries with the slaves in the cluster. 

MySQL-Proxy是处在你的MySQL数据库客户和服务端之间的程序,它还支持嵌入性脚本语言Lua。这个代理可以用来分析、监控和变换(transform)通信数据,它支持非常广泛的使用场景:

  • 负载平衡和故障转移处理
  • 查询分析和日志
  • SQL宏(SQL macros)
  • 查询重写(query rewriting)
  • 执行shell命令

MySQL Proxy更强大的一项功能是实现“读写分离(Read/Write Splitting)”。基本的原理是让主数据库处理事务性查询,而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。

 

二、实战过程

测试环境:Ubuntu 10.04.2 LTS + MySQL5.1.41-3ubuntu12.10-log

192.168.1.147  proxy 代理 入口

192.168.1.126  master  主机 只写

192.168.1.145  slaver  从机 只读

程序上只需要链接到192.168.1.147,而192.168.1.126和192.168.1.145对于程序来说是透明的,你完全不需要理会,也不需要知道192.168.1.126和192.168.1.145,你对数据库的所有操作都只对192.168.1.147进行操作。

1.     安装脚本lua

#apt-get install lua5.1

MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的,因此需要安装lua。

2.     安装配置MySQL-Proxy

#apt-get  mysql-proxy

当前获取到的版本是:mysql-proxy 0.8.0(查看版本命令:#mysql-proxy -V)

3.     修改rw-splitting.lua

       #vim /usr/share/mysql-proxy/rw-splitting.lua

配置并使用rw-splitting.lua读写分离脚本,脚本目录是 /usr/share/mysql-proxy,修改读写分离脚本rw-splitting.lua,修改默认连接数,进行快速测试,如果不修改连接数的话要达到连接数为4时才会启用读写分离。

– connection pool

if not proxy.global.config.rwsplit then

proxy.global.config.rwsplit = {

min_idle_connections = 1, //默认为4

max_idle_connections = 1, //默认为8

is_debug = false

}

end

这是因为mysql-proxy会检测客户端连接,当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上。

4.     新建文件夹/var/log/mysql-proxy/和文件mysql-proxy.log

       #mkdir /var/log/mysql-proxy

       #vi mysql-proxy.log

5.     执行读写分离

#sudo mysql-proxy –proxy-read-only-backend-addresses=192.168.1.145:3306 –proxy-backend-addresses=192.168.1.126:3306 –proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua >/var/log/mysql-proxy/mysql-proxy.log &

参数说明:

192.168.1.147  proxy 代理 入口

192.168.1.126  master  主机 只写

192.168.1.145  slaver  从机 只读

       当运行sudo mysql-proxy 上面语句后,查询进程没有4040的时候,需要重启mysql ( sudo /etc/init.d/mysql restart) 之后再输入proxy设置。

6.     查看进程端口

#netstat -ant

#netstat –ntl

p_2

(Figure2:端口)

tcp        0      0 0.0.0.0:4040            0.0.0.0:*               LISTEN    

tcp        0      0 0.0.0.0:4041            0.0.0.0:*               LISTEN    

7.     查看数据库链接

mysql> show processlist\G;

p_3

(Figure3:进程)

可以看到,产生了一个新连接。如果想杀掉某个链接,可以使用mysql>help kill查看kill的帮助信息,杀掉36进程的命令:mysql>kill 36;

8.     测试读写分离

1)     在mysql-proxy机子进入MySQL

#mysql -u gaizai -p -P4040 -h 192.168.1.147

必须指定-h参数,不然报下面错误:

p_4

(Figure4:出错)

2)     显示数据库列表:

mysql> show databases;

如果你是搭建MySQL-Proxy成功的话,你上面查看到的数据库列表应该是192.168.1.145服务器上的数据库列表。(可以在145和126分别创建不同的数据库进行测试)

3)     进入测试数据库:

mysql> use weibo;

4)     查询表记录:

mysql>select * from blog;

5)     插入一条记录:

mysql> INSERT INTO `blog` (`TaskID`, `Content`, `Quote`, `Author`, `Time`, `Url`, `ImageUrl`, `Transmits`, `Comments`, `Hash`, `AddOn`) VALUES(’10’,’fefef’,’fefef’,’efef’,NOW(),’http://www.cnblogs.com/zgx/archive/2011/09/13/2174823.html’,NULL,’0′,’0′,’33333333′,NOW());

6)     查询表记录:

mysql>select * from blog;

对比两次查询表的记录,看记录是否有变化,我们插入了数据(确认插入成功),但两次的数据是没有变化的,这就对了,这就是读写分离了(我们读的是145的数据库,插入的是126的数据库,而我们的145与126又没有设置Replication;如果之前设置了,请先停止后进行测试)

注:有时候mysql_proxy(38)库里会显示出数据,重启系统系统,重新启动mysql后就没有此现象了。

7)     进入主写服务器(192.168.1.126) 查看数据

#mysql -u gaizai -p -h 192.168.1.126

mysql> use weibo;

mysql>select * from blog;

可以查看已经写入了一条记录。

8)     进入从读服务器(192.168.1.145)

#mysql -u gaizai -p -h 192.168.1.145

mysql> use weibo;

mysql>select * from blog;

因为没有数据显示,说明只能读,不能写。

在使用工具SQLyog执行查询时,在Proxy服务器上会自动显示下面的信息:

 p_5

(Figure5:信息)

9.     MySQL-Proxy+Replication

上面的测试只是测试了插入数据后,在没有进行Master与Slave的Replication设置的情况下,读取Master与Slave的数据是不同,如果想达到Figure1的效果,我们还需要设置Master与Slave之间的数据复制(Replication),详情请参考:Ubuntu10下MySQL搭建Master Slave

三、MySQL-Proxy命令

帮助命令:$mysql-proxy –help-all

查看下MySQL Proxy的版本:$ mysql-proxy -V 

编译启动脚本:$vi /etc/init.d/mysql-proxy

启动命令:$ /etc/init.d/mysql-proxy start

停止命令:$ /etc/init.d/mysql-proxy stop

重启命令:$ /etc/init.d/mysql-proxy restart

 

四、注意事项

1.      在启动mysql-proxy的时候,可以把启动命令保存为文件:

建议使用配置文件的形式启动, 注意配置文件必须是660权限, 否则无法启动. 如果有多个Slave的话, proxy-read-only-backend-addresses参数可以配置多个以逗号分隔的IP:Port从库列表。

杀掉mysql-proxy进程:# killall mysql-proxy 

新建一个文件:# vi /etc/mysql-proxy.cnf

在文件中输入两个分隔符中间的内容:

——————————————————

[mysql-proxy]

admin-username=viajarchen

admin-password=123123

admin-lua-script = /usr/share/mysql-proxy//admin-sql.lua 

proxy-backend-addresses=192.168.1.126:3306

proxy-read-only-backend-addresses=192.168.1.145:3306

proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua

log-file=/var/tmp/mysql-proxy.log

log-level=debug

daemon=true

keepalive=true

max-open-files=1024

——————————————————

设置权限:# chmod 660 /etc/mysql-proxy.cnf

或者#chmod +x /etc/init.d/mysql-proxy

设置启动文件:# mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

查看信息:# ps -ef | grep mysql-proxy | grep -v grep

root      1869     1  0 18:16 ?        00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

root      1870  1869  0 18:16 ?        00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

查看日志:# tail -50f /var/tmp/mysql-proxy.log

2.      mysql-proxy参数

–admin-address=host:port 指定一个mysqo-proxy的管理端口, 缺省是4041;

-P, –proxy-address=<host:port> 是mysql-proxy 服务器端的监听端口, 缺省是4040;

-r, –proxy-read-only-backend-addresses=<host:port> 只读Slave的地址和端口, 缺省为不设置;

-b, –proxy-backend-addresses=<host:port> 远程Master地址和端口, 可设置多个做failover和load balance, 缺省是127.0.0.1:3306;

–defaults-file=<file>配置文件, 可以把mysql-proxy的参数信息置入一个配置文件里;

–daemon mysql-proxy以守护进程方式运行

–keepalive try to restart the proxy if it crashed, 保持连接启动进程会有2个, 一号进程用来监视二号进程, 如果二号进程死掉自动重启proxy。

–log-level=debug定义log日志级别,由高到低分别有

(error|warning|info|message|debug)

–proxy-lua-script=file指定一个Lua脚本程序来控制mysql-proxy的运行和设置,这个脚本在每次新建连接和脚本发生修改的的时候将重新调用。

–max-open-files:指定最大档案开启数为1024,否则会有【could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 1024.】的log讯息出现。

3.      当MySQL主从复制在 show slave status\G 时出现Slave_IO_Running或Slave_SQL_Running 的值不为YES时,,需要首先通过 stop slave 来停止从服务器,然后再进行测试读写分离。

4.      MySQL-Proxy的rw-splitting.lua脚本在网上有很多版本,但是最准确无误的版本仍然是源码包中所附带的lib/rw-splitting.lua脚本,如果有lua脚本编程基础的话,可以在这个脚本的基础上再进行优化;

5.      MySQL-Proxy实际上非常不稳定,在高并发或有错误连接的情况下,进程很容易自动关闭,因此打开–keepalive参数让进程自动恢复是个比较好的办法,但还是不能从根本上解决问题,因此通常最稳妥的做法是在每个从服务器上安装一个MySQL-Proxy供自身使用,虽然比较低效但却能保证稳定性;

6.      一主多从的架构并不是最好的架构,通常比较优的做法是通过程序代码和中间件等方面,来规划,比如单双server-id号分开写入等方式来实现两个或多个主服务器;

7.      MySQL-Cluster 的稳定性也不是太好;

8.      Amoeba for MySQL 是一款优秀的中间件软件,同样可以实现读写分离,负载均衡等功能,并且稳定性要大大超过MySQL-Proxy,建议大家用来替代MySQL-Proxy,甚至MySQL-Cluster。

9.      mysql proxy不支持old_password。另外也可以通过查看密码长度的方式来判断:select length(password) from mysql.user如果长度为16位则是old_password无疑。

10.   安装了mysql-proxy实现读写分离,有master x 1, slave x 2。为了测试failover,停掉了一个slave,然后mysql-proxy会一直报错,提示无法连接。这个情况比单点的mysql还糟糕,挂掉一个就全挂掉!mysql的工程师给提供了一段代码,替换掉:

src/network-mysqld-proxy.c的NETWORK_MYSQLD_PLUGIN_PROTO函数可以解决这个问题。network-mysqld-proxy-function.c文件。

(经过测试:我停止掉slave数据库,proxy的查询就会转移到master上,当把slave启动后,proxy依然在读master,当有新的链接进来的时候才会去读取slave的数据)

11.   如果在mysql-proxy的机器上也安装了mysql的话,新手就会在这个时候混乱了,到底要如何进行测试和链接呢?比如使用命令:#mysql -u gaizai -p -P4040 -h 192.168.1.147是表示登陆本机的4040端口,使用gaizai帐号,这个帐号可以不是本地mysql的帐号,这样就比较容易区分了。

12.   在上述环境中,mysql-proxy、mysql-master、mysql-slave三台服务器均存在单点故障。为了避免mysql-proxy单点隐患有两种方法:一种方法是mysql-proxy配合keepalived做双机,另一种方法是将mysql-proxy和应用服务安装到同一台服务器上;为了避免mysql-master单点故障可以使用DRBD+heartbear做双机;为了避免mysql-slave单点故障可以添加多台mysql-slave,mysql-proxy会自动屏蔽后端发生故障的mysql-slave。

13.   用sysbench (或者super-smack)测试mysql性能:

#sysbench –test=oltp –mysql-table-engine=innodb –oltp-table-size=1000 –mysql-socket=/tmp/mysql.sock –mysql-password=123456 –mysql-user=gaizai –mysql-host=192.168.1.126 –mysql-db=weibo –num-threads=15 prepare

#sysbench –test=oltp –mysql-table-engine=innodb –oltp-table-size=1000 –mysql-socket=/tmp/mysql.sock –mysql-password=123456 –mysql-user=gaizai –mysql-host=192.168.1.126 –mysql-db=weibo –oltp-test-mode=complex run

14.   关于mysql-proxy的启动和关闭的shell脚本的编写:

15.   读写分离不能回避的问题之一就是延迟,可以考虑Google提供的SemiSyncReplicationDesign补丁。

16.   MySQL-Proxy缺省使用的是4040端口,如果你想透明的把3306端口的请求转发给4040的话,那么可以:iptables -t nat -I PREROUTING -s ! 127.0.0.1 -p tcp –dport 3306 -j REDIRECT –to-ports 4040如果想删除这条规则,可以把上面例子中的-I换成-D。参考链接

17.   当使用bigint 时,mysql_insert_id()存在问题,详情见手册,不过对于大多数人而言,bigint基本不会遇到,所以你可以无视这个问题)注:对于这两个问题,官方BUG库里有人给出了相应的补丁

 

五、错误

在执行命令的时候出现了下面的错误:

p_6

(Figure6:错误信息)

could not raise RLIMIT_NOFILE to 8192

这个一个警告级别的错误,意思是MySQL Proxy在你的系统上不能把open files limit提升到8192,不过没关系的,MySQL Proxy依然好好的运行在你的电脑上。

可以通过设置启动–max-open-files参数解决

MySQL Proxy安装和使用(一)

mysql proxy master and slave test

加入–max-open-files=8192后报下面的错误:

p_7

(Figure7:错误信息)

 

六、疑问与解答

1.      当slave宕机后,mysql-proxy是如何读取的?(经过测试:我停止掉slave数据库,proxy的查询就会转移到master上,当把slave启动后,proxy依然在读master,当有新的链接进来的时候才会重新去读取slave的数据。有时可能需要重启下mysql-proxy)

2.      如何知道mysql-proxy当前执行的select是在哪台机器上执行的?

3.      当slave宕机一段时间后,如果再次同步master的缺失的数据?

4.      当配置中设置了proxy-read-only-backend-addresses=192.168.1.145:3306

,192.168.1.147:3306类似这样的两个slave,如果两个slave的数据不同步,那么是怎么读取数据的?# tail -50f /var/tmp/mysql-proxy.log测试

5.      生产环境中除了进行程序调试外,其它不要开启mysql查询日志,因为查询日志记录了客户端的所有语句,频繁的IO操作将会导致mysql整体性能下降。如何设置呢?

6.      mysql-proxy.cnf文件中的管理员帐号和密码有什么用?使用命令进入管理

mysql -u viajarchen -p -P 4041 -h 192.168.1.147 密码是123123

mysql> select * from proxy_connections;

mysql> select * from proxy_config;

p_8

(Figure8:信息)

7.      关于mysql-proxy的启动和关闭的shell脚本的编写?测试

8.      对于/usr/share/mysql-proxy/rw-splitting.lua脚本中的

local min_idle_connections = 4 local max_idle_connections = 8应该如何理解?min的话就是要达到这个值的时候才会读写分离,那么max的是什么意思呢?最大能有8个链接?

9.      mysqld是什么意思?是mysql的守护进程!

10.   HAProxy和keepalived怎么一起搭建使用?能解决什么问题?

 

七、参考文献

ubuntu 9.04 安装mysql-proxy 成功版

ubuntu10.0.4上配置MYSQL主从复制与读写分离(MySQL-Proxy)

[MySQL管理] mysql主从复制,读写分离 (MySQL Proxy)

MySQL Proxy 安装与读写分离体验

MySQL Proxy(官网)

Getting Started with MySQL Proxy(LUA文件介绍)

Read/Write Splitting with MySQL-Proxy(LUA)

MySQL Proxy learns R/W Splitting(牛人)

MySQL-Proxy负载平衡测试遇到的问题及其分析 && MySQL-Proxy工作机制(Lua)

http://www.lua.org

MySQL Proxy(常用命令)

MySQL Proxy 0.8.2 alpha(下载)

MySQL-Haproxy

mysql-proxy读写分离技术文档整理【原创】(sysbench测试)

用sysbench(或者super-smack)测试mysql性能

mysql-proxy读写分离设置(新建启动文件mysql-proxy.cnf +log)

MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践(主从复制数据+经验分享)

使用MySQL-Proxy读写分离时的注意事项(查询乱码)

MySQL Proxy快速实现读写分离以及负载均衡(启动脚本)

Mysql-proxy主从搭建读写分离全过程(shell脚本启动)

MYSQL-PROXY测试实例(Lua脚本构建自己的轮询连接算法+管理MySQL Proxy )

from:http://www.cnblogs.com/gaizai/archive/2012/11/21/2780816.html

核心业务系统数据库平台迁移: Oracle -> MySQL

为了对核心技术拥有更多的自主控制能力,为了解决数据库的线性扩展问题,为了尽量减少对商业软件的依赖,为了摆脱对高端硬件的依赖,为了…  基于以上多种原因,2年前,我们计划将公司某核心应用平台进行大手术:数据库平台从软件到硬件全部重构。当然,这其中应用架构的改造也不可避免的进行了大 换血。

这个项目无论是从技术角度还是是业务角度来说,都对我们有着非常大的价值,也必定会带来非常深远的影响。项目历时2年多,分4个阶段才完成:

    • 应用接口统一

这一阶段主要是为了后面真正迁移的时候做准备工作,将该核心应用系统的所有数据访问入口统一到一起,全部以服务化的接口方式呈现给其他有需要的系统,一来方便后续变更的控制,二来也推进了服务化的进程。

    • Oracle数据库中拆分(1拆16)

这个阶段本不是必要的,但是由于项目启动稍微晚了点,数据出现了爆发性增长,导致该系统的数据表太大(单表不带索引过500GB),原  Oracle 数据库已经快撑不住了。为了安全起见,先在 Oracle 中从一个主表以会员ID进行 hash  运算后再进行水平拆分,从1个表分拆成了16个。附表由于访问量稍小,而且全部是根据主键访问,暂时保留原样。

当然,这样的水平拆分,必然会带来数据访问路由以及数据合并的问题。我们专门为此开发了具有分布式数据库路由/数据合并,数据库读写分离,数据库链接管理等功能的数据访问中间层,专门解决拆分后给应用服务器带来的影响,使得应用服务器完全感受不到后端数据库的变化。

这个数据访问中间层,对前端应用服务器来说,就是一个完整的数据库,所有数据请求都从这里实现,以协议的方式和前端应用服务器的jdbc驱动进行交互,以便让数据库对应用服务器彻底透明。

    • Oracle迁移至 MySQL(16拆128)

这个阶段是整个阶段中历时最长,复杂度最高,风险系数最高的,未知因素也最多的一个阶段。虽然 MySQL 数据库已经在互联网行业占据了大片江山,但是对于阿里巴巴来说,却仍然是一个新鲜玩意儿,因为之前我们一直都用 Oracle 来提供所有的业务系统的数据库服务。

在此之前,我们从来没有在如此核心业务系统的数据库上使用过 PC Server 和本地硬盘来承载数据库,一直是使用 IBM  小型机和中高端存储设备来解决高性能和高可靠的问题。在更换成 PC Server 和本地硬盘来承载数据库之后,我们就必须面对 PC Server  本身硬件可能存在的不可靠性所带来的 Crash,所以我们必须有一套完善的 HA 切换机制,要比小型机厂商所提供的商业 HA  管理软件更加高效更加自动化更加可控,才能我们降低了设备本身可靠性之后达到原有的可用性要求。

对于一个需要满足 365 *  24 * 7 的核心业务系统来说,肯定是不可能给我们太多时间来进行数据迁移的,所以我们不得不设计出一个对现有系统影响尽可能小的迁移方案,这势必会造成方案的高度复杂化,带来更多的风险。最后的迁移方案要经历如下4个阶段:

1. Oracle 读/写;;MySQL 初始化并增量写
2. Oracle 读/写; MySQL 写
3. Oracle 写; MySQL 读/写
4. Oracle 停访问; MySQL 读/写

当然,也正式由于有如此复杂的方案,才确保了在整个迁移过程中的的停机时间被控制在了10分钟之类。

    • 附属Detail信息迁移至 MySQL

从项目开始,至完成主表拆分结束,已经接近2年了。这2年时间内,数据量一直都在飞涨,这让即使仅仅只是按照主键访问的附表也快无法承受持续增长的 业务压力,附表的拆分也就成了必行之势。由于在原来主表拆分的过程中,整个项目组已经积累了大量的经验,附表拆分过程非常顺利,基本没有出现任何问题。虽 然附表的拆分过程与主表相比除了 1拆16这个阶段外没有减少其他任何环节,但是整个拆分过程也才2个月就全部搞定了。

这个迁移项目算是彻底完成了,但是我们的迁移之路并不会就此止步,还有很多的系统仍然存在扩展性问题,还有很多的数据库应用等着我们去拆分。

注:同事们还为此送了我们一个虽不太雅但也意思相近的名称 “拆迁队”。

from:http://isky000.com/database/core-databas-system-migration-from-oracle-to-mysql