如何杀掉空闲事务

11月 29th, 2011 | Posted by | Filed under 未分类

我们经常遇到一个情况,就是网络断开或程序Bug导致COMMIT/ROLLBACK语句没有传到数据库,也没有释放线程,但是线上事务锁定等待严重,连接数暴涨,尤其在测试库这种情况很多,线上也偶有发生,于是想为MySQL增加一个杀掉空闲事务的功能。

那么如何实现呢,通过MySQL Server层有很多不确定因素,最保险还是在存储引擎层实现,我们用的几乎都是InnoDB/XtraDB,所以就基于Percona来修改了,Oracle版的MySQL也可以照着修改。

需求:
1. 一个事务启动,如果事务内最后一个语句执行完超过一个时间(innodb_idle_trx_timeout),就应该关闭链接。
2. 如果事务是纯读事务,因为不加锁,所以无害,不需要关闭,保持即可。
虽然这个思路被Percona的指出Alexey Kopytov可能存在“Even though SELECT queries do not place row locks by default (there are exceptions), they can still block undo log records from being purged.”的问题,但是我们确实有场景SELECT是绝对不能kill的,除非之后的INSERT/UPDATE/DELETE发生了,所以我根据我们的业务特点来修改。
跟Percona的Yasufumi Kinoshita和Alexey Kopytov提出过纯SELECT事务不应被kill,但通过一个参数控制的方案还没有被Alexey Kopytov接受,作为通用处理我提出了用两个变量分别控制纯读事务的空闲超时时间和有锁事务的空闲超时时间,还在等待Percona的回复,因为这个方案还在测试,就先不开放修改了,当然如果你很熟悉MYSQL源码,我提出这个思路你肯定知道怎么分成这两个参数控制了。

根据这两个需求我们来设计方法,首先想到这个功能肯定是放在InnoDB Master Thread最方便,Master Thread每秒调度一次,可以顺便检查空闲事务,然后关闭,因为在事务中操作trx->mysql_thd并不安全,所以一般来说最好在InnoDB层换成Thread ID操作,并且InnoDB中除了ha_innodb.cc,其他地方不能饮用THD,所以Master Thread中需要的线程数值,都需要在ha_innodb中计算好传递整型或布尔型返回值给master thread调用。
阅读全文…

MySQL的timeout那点事

11月 24th, 2011 | Posted by | Filed under 未分类

因为最近遇到一些超时的问题,正好就把所有的timeout参数都理一遍,首先数据库里查一下看有哪些超时:

root@localhost : test 12:55:50> show global variables like "%timeout%";
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| connect_timeout            | 10     |
| delayed_insert_timeout     | 300    |
| innodb_lock_wait_timeout   | 120    |
| innodb_rollback_on_timeout | ON     |
| interactive_timeout        | 172800 |
| net_read_timeout           | 30     |
| net_write_timeout          | 60     |
| slave_net_timeout          | 3600   |
| table_lock_wait_timeout    | 50     | # 这个参数已经没用了
| wait_timeout               | 172800 |
+----------------------------+--------+

我们一个个来看

connect_timeout

手册描述:
The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds as of MySQL 5.1.23 and 5 seconds before that.
Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at ‘XXX’, system error: errno.
解释:在获取链接时,等待握手的超时时间,只在登录时有效,登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快,一般默认即可。

delayed_insert_timeout

手册描述:
How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating.
解释:这是为MyISAM INSERT DELAY设计的超时参数,在INSERT DELAY中止前等待INSERT语句的时间。

innodb_lock_wait_timeout

手册描述:
The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the –innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See also Section 13.6.12, “InnoDB Error Handling”.)
innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.
InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.
For the built-in InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.
解释:描述很长,简而言之,就是事务遇到锁等待时的Query超时时间。跟死锁不一样,InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务,锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源,被回滚的肯定是请求锁的那个Query。

innodb_rollback_on_timeout

手册描述:
In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.
解释:这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query,打开的话事务遇到超时就回滚整个事务。

interactive_timeout/wait_timeout

手册描述:
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also
解释:一个持续SLEEP状态的线程多久被关闭。线程每次被使用都会被唤醒为acrivity状态,执行完Query后成为interactive状态,重新开始计时。wait_timeout不同在于只作用于TCP/IP和Socket链接的线程,意义是一样的。

net_read_timeout / net_write_timeout

手册描述:
The number of seconds to wait for more data from a connection before aborting the read. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
On Linux, the NO_ALARM build flag affects timeout behavior as indicated in the description of the net_retry_count system variable.
解释:这个参数只对TCP/IP链接有效,分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间,这是在Activity状态下的线程才有效的参数

slave_net_timeout

手册描述:
The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement or –master-connect-retry option, and the number of reconnection attempts is limited by the –master-retry-count option. The default is 3600 seconds (one hour).
解释:这是Slave判断主机是否挂掉的超时设置,在设定时间内依然没有获取到Master的回应就人为Master挂掉了

在NUMA处理器绑定多实例到固定核心

7月 1st, 2011 | Posted by | Filed under 未分类

另发在:http://www.mysqlops.com/2011/07/01/mysql_multi_using_numactl.html

关于NUMA的介绍我这里就不多说了,网上太多资料了,我在这篇文章要介绍的是如何在MySQL多实例场景下使用numactl来绑定各个实例到具体的物理节点上,避免跨节点分配内存和跨节点访问寄存器。

至于为何使用多实例,因为MySQL对于多处理机和大内存的利用效率不佳,采用多实例可以很大程度提高MySQL对资源的利用,详情可以看Percona的白皮书中对多实例的测试:Scaling MySQL With Virident Flash Drives and Multiple Instances of Percona Server .

numactl这个程序的用法可以参照man手册:http://linux.die.net/man/8/numactl

基本用法是“numactl  [option] 程序路径”,例如我希望用numactl启动mysqld则是numactl  [option] /usr/local/mysql/bin/mysqld。曾经我误以为numactl是控制某一个程序名,汗……亲手做过才明白是程序路径。

我只介绍几个重要参数
–interleave=all 这是使用交叉分配模式启动一个程序,也就是说程序可以随意跨节点用其他节点的内存,传说中这是效率最高的关闭NUMA特性的方法,只是传说。
–cpunodebind=node 这是把程序绑定在指定的node节点上运行,即使另一个物理节点是idle的,也不会去使用。
–localalloc 严格控制只在节点内分配内存,禁止分配其他节点下的内存到当前节点运行的程序。

我们启动MySQL希望的参数是 numactl –cpunodebind=node –localalloc mysqld_path
为了运维方便,我不可能每次mysql启动都这么执行,我依然希望通过/etc/init.d/mysql和mysqld_multi来管理mysql启动和关闭,于是我采用自定义启动脚本的方式。

首先编写自定义启动脚本如下:

#!/bin/sh

# Program Path
NUMACTL=`which numactl`
MYSQLD=/usr/alibaba/mysql/libexec/mysqld
PS=`which ps`
GREP=`which grep`
CUT=`which cut`
WC=`which wc`
EXPR=`which expr`

# Variables
CPU_BIND=(`$NUMACTL --show | $GREP nodebind | $CUT -d: -f2 `)   # CPU bins list
CPU_BIND_NUM=${#CPU_BIND[@]}    # How many CPU binds
MYSQLD_NUM=`$PS aux | $GREP mysqld | $GREP -v grep | $GREP '\' | $WC -l`
MYSQLD_NUM=`$EXPR $MYSQLD_NUM + 1`
BIND_NO=`$EXPR $MYSQLD_NUM % $CPU_BIND_NUM ` # Calc Which CPU to Bind

# echo CMD
echo "$NUMACTL --cpunodebind=$BIND_NO --localalloc $MYSQLD" > /tmp/mysqld.$MYSQLD_NUM

# use exec to avoid having an extra shell around.
exec $NUMACTL --cpubind=$BIND_NO --localalloc $MYSQLD "$@"

方法是查看当前有多少个mysqld进程已经存在,并且通过numactl –show判断有多少个物理节点,从而判断当前的进程应该分配给哪个节点,例如有2个物理节点,没有mysqld进程,则分配当前进程到0节点,再启动一个实例,当前已经有1个mysqld进程,则分配到1节点,再启动一个实例到0节点……依次循环。

然后在my.cnf文件中配置使用我们自己的脚本启动:

[mysqld_safe]
......
ledir=/usr/local/mysql/bin/ # 放自定义脚本的目录
mysqld=mysqld_using_numactl # 自定义脚本的名称

然后再用/etc/init.d/mysql或mysqld_multi启动mysqld进程就可以实现绑定了。
你可以先启动一个实例,然后在MySQL里做一些消耗CPU的操作,可以观察到只有一个物理节点上的core有活动,哪怕这个节点的core全是100%的利用率,另一个节点的core也全部都是闲的~

有兴趣的话赶紧尝试一下吧~

如果可以,我们一起留在宜春

6月 6th, 2011 | Posted by | Filed under 心灵感触

如果可以,我们一起留在宜春

不要那些所谓的理想      不要那些所谓的奋斗

不想去英国、美国,读书留学      不想去上海、北京,打拼奋斗

就一起留在我们熟悉的城市

每一条街道都能叫出名字    每一个邻居都认识

想打个麻将,唱个歌

一个电话,半个小时

人就聚齐了

 

如果可以,我们一起留在宜春

无聊了一起去南昌玩一趟

开个车三四个小时就到江西首府了

过年过节几个朋友窜窜门吃顿饭

谁要是不来,打个车几分钟就到他家门口

直接拖出来

 

如果可以,我们一起留在宜春

嘴馋的时候

满大街吃美味的小吃

或者到鼓楼、到麻辣大王,吃个烧烤

水果出来的季节

到白马农庄摘草莓

都是一箱一箱的买

因为便宜又好吃

 

如果可以,我们一起留在宜春

周末的时候还能骑车满世界转悠

心血来潮就去明月山

不好就去袁山

找个野山,带着烧烤架

美滋美滋的自助烧烤

 

如果可以,我们一起留在宜春

冬天的时候一起堆雪人

夏天去秀江游个泳

累了就随便找个KTV、棋牌室、桌游社呼朋唤友

打打麻将,斗斗地主    输赢都在这个圈子

每个人我们都熟悉    知根知底

 

如果可以,我们一起留在宜春

等我们工作了

没有那么大的压力

不用天天加班到10点

不用没有节假日

不用周周出差

只要8点上班,5点下班

不想做饭了就找个哥们家蹭顿饭

饭后可以不用洗碗

还可以一起散散步

 

如果可以,我们一起留在宜春

看着朋友结婚,每个人的婚礼都能参加

等我们有了孩子

我们要让他们也天天在一起玩

让我们成为世交他们也成为世交

礼拜天领着他们去森林公园

他们看植物,我们看他们

让他干爹干妈一大堆

过年压岁钱多的拿不了

让他一出生就学普通话

而不是一出生周围就是不知道哪个地方的方言或者英语

 

如果可以,我们一起留在宜春

等我们老了可以天天有人陪着

走不动了还可以打麻将

商量着什么时候再去趟宜春中学

什么时候再爬趟明月山

什么时候再去鼓楼吃小吃

什么时候再……

把年轻的事情都再做一遍

 

 

如果……如果……如果可以……

标签:

MySQL中创建及优化索引组织结构的思路

6月 2nd, 2011 | Posted by | Filed under 未分类

原文链接:http://www.mysqlops.com/2011/05/23/mysql%E4%B8%AD%E5%88%9B%E5%BB%BA%E5%8F%8A%E4%BC%98%E5%8C%96%E7%B4%A2%E5%BC%95%E7%BB%84%E7%BB%87%E7%BB%93%E6%9E%84%E7%9A%84%E6%80%9D%E8%B7%AF.html

【导读】
通过一个实际生产环境中的数据存取需求,分析如何设计此存储结构,如何操纵存储的数据,以及如何使操作的成本或代价更低,系统开销最小。同时,让更多初学者明白数据存储的表上索引是如何一个思路组织起来的,希望起到一个参考模板的价值作用。

测试用例描述
测试用例为B2C领域,一张用于存储用户选购物品而生成的产品订单信息表,不过去掉一些其他字段,以便用于测试,其表中的数据项也不特别描述,字段意思见表

USE `test`;
DROP TABLE IF EXISTS `test`.`goods_order`;
CREATE TABLE `goods_order`(
`order_id`        INT UNSIGNED      NOT NULL             COMMENT ‘订单单号’,
`goods_id`        INT UNSIGNED      NOT NULL DEFAULT ’0′ COMMENT ‘商品款号’,
`order_type`      TINYINT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘订单类型’,
`order_status`    TINYINT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘订单状态’,
`color_id`        SMALLINT  UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘颜色id’,
`size_id`         SMALLINT  UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘尺寸id’,
`goods_number`    MEDIUMINT  UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘数量’,
`depot_id`        INT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘仓库id’,
`packet_id`       INT UNSIGNED  NOT NULL DEFAULT ’0′ COMMENT ‘储位code’,
`gmt_create`      TIMESTAMP     NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘添加时间’,
`gmt_modify`      TIMESTAMP     NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘更新时间’,
PRIMARY KEY(order_id,`goods_id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET ‘utf8′ COLLATE ‘utf8_general_ci’;

其中,主键信息:PRIMARY KEY(order_id,`goods_id`),为何主键索引索引字段的顺序为:order_id,`goods_id`,而不是: `goods_id`, order_id呢?原因很简单,goods_id在订单信息表中的重复率会比order_id高,也即order_id的筛选率更高,可以减少扫描索引记录个数,从而达到更高的效率,同时,下面即将会列出的SQL也告诉我们,有部分SQL语句的WHERE字句中只出现order_id字段,为此更加坚定我们必须把字段:order_id作为联合主键索引的头部,`goods_id`为联合主键索引的尾部。

数据存储表设计的小结:
设计用于存储数据的表结构,首先要知道有哪些数据项,也即行内常说的数据流,以及各个数据项的属性,比如存储的数据类型、值域范围及长度、数据完整性等要求,从而确定数据项的属性定义。存储的数据项信息确定之后,至少进行如下三步分析:
l 首先,确定哪些数据项或组合,可以作为记录的唯一性标志;
l 其次,要确定对数据记录有哪些操作,每个操作的频率如何,对网站等类型应用,还需要区分前台操作和后台操作,也即分外部用户的操作,还是内部用户的操作;
l 最后,对作为数据记录操作的条件部分的数据项,分析其数据项的筛选率如何,也即数据项不同值占总数据记录数的比例关心,比例越接近1则是筛选率越好,以及各个值得分布率;
综上所述,再让数据修改性操作优先级别高于只读性操作,就可以创建一个满足要求且性能较好的索引组织结构。
数据的存取设计,就涉及一块非常重要的知识: 关系数据库的基础知识和关系数据理论的范式。对于范式的知识点,特别解释下,建议学到BCNF范式为止,1NF、2NF、3NF和BCNF之间的差别,各自规避的问题、存在的缺陷都要一清二楚,但是在真实的工作环境中,不要任何存取设计都想向范式靠,用一句佛语准确点表达:空即是色,色即是空。

用于生成测试数据的存储过程代码
创建索引,就离不开表存储的真实数据,为此编写一个存储过程近可能模拟真实生产环境中的数据,同时也方便大家使用此存储过程,在自己的测试环境中,真实感受验证,
存储过程代码:

DELIMITER $$
DROP PROCEDURE IF EXISTS `usp_make_data` $$
CREATE PROCEDURE `usp_make_data`()
BEGIN
DECLARE iv_goods_id INT UNSIGNED DEFAULT 0;
DECLARE iv_depot_id INT UNSIGNED DEFAULT 0;
DECLARE iv_packet_id INT UNSIGNED DEFAULT 0;

SET iv_goods_id=5000;
SET iv_depot_id=10;
SET iv_packet_id=20;

WHILE iv_goods_id>0
DO
START  TRANSACTION;
WHILE iv_depot_id>0
DO
WHILE iv_packet_id>0
DO
INSERT INTO goods_order(order_id,goods_id,order_type,order_status,color_id,size_id,goods_number,depot_id,packet_id,gmt_create,gmt_modify)
VALUES(SUBSTRING(RAND(),3,8),iv_goods_id,SUBSTRING(RAND(),3,1),SUBSTRING(RAND(),5,1)%2,SUBSTRING(RAND(),3,3),SUBSTRING(RAND(),4,3),SUBSTRING(RAND(),5,2),
iv_depot_id,SUBSTRING(RAND(),4,2)*iv_packet_id,DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),2,3) DAY),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) DAY)
);
SET iv_packet_id=iv_packet_id-1;
END WHILE;
SET iv_packet_id=20;
SET iv_depot_id=iv_depot_id-1;
END WHILE ;

COMMIT;
SET iv_depot_id=10;
SET iv_goods_id=iv_goods_id-1;
END WHILE ;
END $$
DELIMITER ;

业务逻辑描述
l 非注册用户,或网站的注册用户不登陆,都能可选购买物品,生成订单号对应的用户UID为系统默认的;
l 订单与用户UID关联、描述等信息,存储其它的表中,通过订单号的模式关联;
l 用户的订单信息,在未付款之前都可以再修改,付款之后则无法修改;
l 已经付费的订单信息,自动发送到物流部门,进行后续工序的操作。处理完毕之后,会更新订单中涉及物品的存储位置信息;
l 定期读取部分数据到数据仓库分析系统,用于统计分析;
l 个人订单查询,前后台都有;
l 购物记录查询显示;

根据业务规则描述需要使用操纵数据的SQL语句
(1). EXPLAIN SELECT * FROM goods_order WHERE `order_id`=40918986;
(2). SELECT * FROM goods_order WHERE `order_id` IN (40918986,40717328,30923040…) ORDER BY gmt_modify DESC;
(3). UPDATE goods_order SET gmt_modify=NOW(),…. WHERE `order_id`=40717328 AND goods_id=4248;
(4). SELECT COUNT(*) FROM goods_order WHERE depot_id=0 ORDER BY gmt_modify DESC LIMIT 0,50;
(5). SELECT * FROM goods_order WHERE depot_id=6 AND packet_id=0 ORDER BY gmt_modify DESC LIMIT 0,50;
(6). SELECT COUNT(*) FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1
(7). SELECT * FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 ORDER BY gmt_modify DESC LIMIT 0,50;
(8). SELECT * FROM goods_order WHERE gmt_modify>=’ 2011-04-06’;
8条SQL语句按触发其执行的用户分类:
l 前台用户点击触发的操作而会执行的SQL语句为:(1)、(2)、(3);
l 后台内部用户点击触发的操作而会执行的SQL语句为:(1)、(2)、(3)、(4)、(5)、(6)、(7);
l 后台系统自动定期执行:(4)、(5)、(6)、(7),工作时间正常情况每隔15分钟执行一次,以检查是否有已付款而没有准备货物的订单、是否有收款而未发货的订单等;
l 统计分析系统定期导出数据而执行的SQL语句为:(8),频率为每24小时一次;
我们再分析上述列出来的SQL,分为2类,一类是读操作的SQL(备注:SELECT操作),另外一类为修改性操作(备注:UPDATE、DELETE操作),分别如下:
SELECT 的WHERE子句、GROUP BY子、ORDER BY 子句和HAVING 子句中,出现的字段:
(1). order_id
(2). order_id+gmt_modify
(3). depot_id+gmt_modify
(4). depot_id+packet_id+gmt_modify
(5). goods_id+order_status+order_type
(6). goods_id+order_status+order_type+gmt_modify
(7). gmt_modify
修改性操作的WHERE子句中出现的条件字段:
(8). order_id+ goods_id

我们已经存在主键索引:PRIMARY KEY(order_id,`goods_id`),另外考虑到此表数据的操作以SELECT和INSERT为主,UPDATE的SQL量其次,再根据上述SQL语句,为此我们可以初步确定需要创建的索引:
ALTER TABLE goods_order
ADD INDEX idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify),
ADD INDEX idx_depotID_packetID_gmtmodify(depot_id,packet_id,gmt_modify);

总结:
文章中也分析了为何联合主键索引的顺序为:order_id,`goods_id`,再补充下作为主键的联合索引的字段属性的其他特性:字段值写入之后不变化、字段值长度短且最好为数值类型;
对于编号SQL:(8),每天按更新日期读取一次数据的操作,以采用全表扫描的方式实现,牺牲其数据读取的性能,以减少更新字段修改日期的值而带来的索引维护开销;
对于编号SQL:(4)、(5),考虑到每次都是读取最新的50条记录,以及读取的数据基本上可肯定为热数据,为此不得不牺牲其中一条SQL的数据读取性能,而少创建一个联合索引,从而减少维护索引字段的IO量;
对于编号SQL:(6)、(7),创建的联合索引,需要特别注意联合索引:idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify)中的字段顺序,其中:
l goods_id字段的筛选率高于order_type,order_status,另外gmt_modify字段只出现在ORDER BY子句中,为此只有让goods_id字段作为联合索引的头部,以提高索引的筛选率,从而提高索引的效率,减少逻辑或物理的读。
l order_status字段只有0或1两种值,而order_type有多种,以及根据SQL语句,必须order_type出现在联合中的位置要比order_status靠近头部;
l gmt_modify字段出现在ORDER BY子句中,为此必须放到联合索引字段的最后;

最后,再梳理一下从需求到设计存储结构,再到编写SQL和创建索引结构,我们应该做的步骤:
l 整理业务产生的数据流,读取数据的方式;
l 整理清楚数据流中的每个数据项属性信息;
l 分析业务指标,推测需要存储数据的规模(备注:一定要以多少GB作为容量单位);
l 选择可能用于支持业务的硬件设备和数据库架构;
l 把所有可能操纵数据的条件和操作类型,都整理清楚;
l 分析操纵数据条件字段各自的数据筛选率;
l 权衡各个SQL的性能和IO量,也即类似于哪个操作权重高一些,那些操作权重适当低一些;
l 创建索引组织结构;
l 收集测试和生产环境的反馈信息,优化索引组织结构;

备注:
本想再用测试环境结合业务的方式,跑一套模拟测试脚本程序,让大家更加直观地看到不同索引组织情况下,相同的SQL操作及频率,数据库服务器的处理能力和负载变化及对比信息,可惜唯一的服务器无法使用了,只好放弃。对于分析相同的SQL,走不通索引,其需要的逻辑IO和物理IO量也是一个办法,此次就不分析了,有需要的朋友可以去玩玩,另外建议初学者一定要好好阅读下mysql 手册上的相关章节内容:7.2.6. Index Merge Optimization。