Mysql 优化实验数据结论记录

1.索引的insert时间影响

a.Innodb (建表索引部分语句，下同)

PRIMARY KEY (id),

KEY index_log_data_on_log_time (log_time),

KEY index_log_data_on_req_site (req_site),

KEY index_log_data_on_req_originalUrl (req_originalUrl))

Query OK, 1500000 rows affected, 65535 warnings (16 min 42.59 sec)

b.Innodb

PRIMARY KEY (id),

KEY index_log_data_on_log_time (log_time),

KEY index_log_data_on_req_site (req_site)

Query OK, 1500000 rows affected, 65535 warnings (3 min 32.72 sec)

实验1的a和b的区别只在于req_originalUrl的索引，req_originalUrl是一个很长的url字符串。效果相当明显。

下面引用自 http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html 

 3.1.2、按primary key的顺序插入行(InnoDB)

如果你用InnoDB，而且不需要特殊的聚簇索引，一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列，这会保证记录按照顺序插入，而且能提高使用primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键，例如，字符串主键就是一个不好的选择，它使得插入操作变得随机。

2.不同引擎的百万数据Insert时间区别

a.Innodb

PRIMARY KEY (id,log_time),

KEY index_log_data_on_req_site (req_site)

Query OK, 1500000 rows affected, 65535 warnings (1 min 48.38 sec)

b.MyISAM

PRIMARY KEY (id,log_time),

KEY index_log_data_on_req_site (req_site)

Query OK, 1500000 rows affected, 65535 warnings (29.98 sec)

Innodb和MyISAM两种引擎对比，MyISAM较快。

该实验其实意义不大，查了不少资料，Innodb是mysql里面专门为海量数据而生的引擎。

因为实际应用是千万级的应用，还是选择Innodb。

3.增加parttition优化前的准备工作

MyISAM引擎：数据库创建三个文件：表结构、表索引、表数据空间。（默认是在/var/lib/mysql中可以查看到）

InnoDB：所有的数据库表数据默认是存储在一个共享空间：ibdata1

即使删除数据的时候ibdata1也不会自动收缩！此默认存储方式存在很多弊端。

改进：

在配置文件 /etc/my.cnf 里面的 [mysqld] 下面添加一行如下（如果已有则改写）

innodb_file_per_table=1

可以在 /var/lib/mysql/[yourdatabase] 目录下面明显看到多出一个idb文件。

这个文件存储该数据库的数据。

则不再使用ibdata1。

这样做的坏处是insert耗时会有少量的增加，但是其他几乎所有操作都有明显的速度提升。

4.增加分区后的Insert时间影响（选择的分区是range，分区的关键字是log_time）

a.Innodb

PRIMARY KEY (id,log_time),

KEY index_log_data_on_req_site (req_site)

Query OK, 3000000 rows affected, 65535 warnings (4 min 18.92 sec)

b.Innodb partition

PARTITION BY RANGE(TO_DAYS(log_time))

(

 PARTITION log_data1 VALUES LESS THAN (TO_DAYS('2012-10-23')),

 PARTITION log_data2 VALUES LESS THAN (TO_DAYS('2012-10-24'))

)

Query OK, 3000000 rows affected, 65535 warnings (4 min 18.87 sec)

可以看出，在百万级的数据Insert时间基本无影响。

但是可以推断在表的数据量进入千万级数据量后。

在未分区的情况，插入时间会随着已有的数据量显著下降。

而在分区之后，每天使用一个新的分区。把旧的久远的分区删除（分区后也会大大减小delete的时候，因为直接drop partition即可，而在分区前所以的数据都存在ibdata1里面，delete的时间损耗根本无法忍受，通常只能drop整个表，然后新建），从而控制数据的总量来保持Insert性能。

经三天的插入实验观察：现在的insert速度保守估计是平均每小时可以插入1000W+。

5.对于已分区表的select对比实验

A:

mysql> explain partitions SELECT count(id) FROM log_data WHERE log_time = '2012-10-29';

+----+-------------+----------+---------------------+-------+---------------+---------+---------+------+----------+--------------------------+

| id | select_type | table    | partitions          | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |

+----+-------------+----------+---------------------+-------+---------------+---------+---------+------+----------+--------------------------+

|  1 | SIMPLE      | log_data | log_data_2012_10_29 | index | NULL          | PRIMARY | 12      | NULL | 65081435 | Using where; Using index |

+----+-------------+----------+---------------------+-------+---------------+---------+---------+------+----------+--------------------------+

1 row in set (0.00 sec)

B:

mysql> explain partitions SELECT count(id) FROM log_data WHERE to_days(log_time) = to_days('2012-10-29');

+----+-------------+----------+-----------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

| id | select_type | table    | partitions                                                                                          | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |

+----+-------------+----------+-----------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

|  1 | SIMPLE      | log_data | log_data_2012_10_25,log_data_2012_10_27,log_data_2012_10_28,log_data_2012_10_29,log_data_2012_10_30 | index | NULL          | PRIMARY | 12      | NULL | 65081436 | Using where; Using index |

+----+-------------+----------+-----------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+----------+--------------------------+

1 row in set (0.00 sec)

注意：AB两个select的where条件写法不同对于查询过程中，对分区的使用造成的影响。

影响如下：B的实际使用时间是A的2倍以上。

mysql> SELECT count(id) FROM log_data WHERE log_time >= '2012-10-28 00:00:00' and log_time <= '2012-10-28 23:59:59';

+-----------+

| count(id) |

+-----------+

|  17174606 |

+-----------+

1 row in set (34.07 sec)

mysql> SELECT count(id) FROM log_data WHERE to_days(log_time) = to_days('2012-10-28');

+-----------+

| count(id) |

+-----------+

|  17174606 |

+-----------+

1 row in set (1 min 18.73 sec)

做这个实验对比的原因是之前我的查询语法写得不好，多了to_days()，一直纳闷explain partition时还是全部分区。

查到这个链接才解决:

http://topic.csdn.net/u/20120809/18/31f7c1c7-3cbe-414f-8f3a-d45526521273.html?r=79400294

其实道理很简单。

原因在于mysql内部的查询语法分析，当查询语法里面含有分区使用的主键log_time时候，mysql会对查询进行分区范围的优化。

而to_days(log_time)将log_time转化，mysql的查询语法分析引擎不知道含有log_time这个分区的关键字，

只能对所有分区进行检索，显然速度较慢。

而且：

该实验的表数据总量6000W+，而查询出来的分区1700W+，并不是非常悬殊。

当表数据总量远远大于分区数据量时，查询时间的区别会显得更加悬殊。

总结：

优化的核心在于索引和分区。

以上实验不包括配置里做的一些修改，对mysql的优化也不是很熟悉，都是根据网上现查现用，暂时只能优化到这。