公布MySQL分表优化实验

正如前文所提到的，MySQL易学、易部署、易管理和易维护。我们的项目中有很多多少不即是的情形。明天写这篇文章复杂的剖析一下怎样个优化法。
这里的分表逻辑是依据t_group表的user_name组的个数来分的。
由于这类情形独自user_name字段上的索引就属于烂索引。起不了啥名分明的效果。
1、实验PROCEDURE.
DELIMITER$$
DROPPROCEDURE`t_girl`.`sp_split_table`$$
CREATEPROCEDURE`t_girl`.`sp_split_table`()
BEGIN
declaredoneintdefault0;
declarev_user_namevarchar(20)default;
declarev_table_namevarchar(64)default;
--Getallusersname.
declarecur1cursorforselectuser_namefromt_groupgroupbyuser_name;
--Dealwitherrororwarnings.
declarecontinuehandlerfor1329setdone=1;
--Opencursor.
opencur1;
whiledone1
do
fetchcur1intov_user_name;
ifnotdonethen
--Gettablename.
setv_table_name=concat(t_group_,v_user_name);
--Createnewextratable.
set@stmt=concat(createtable,v_table_name,liket_group);
prepares1from@stmt;
executes1;
dropprepares1;
--Loaddataintoit.
set@stmt=concat(insertinto,v_table_name,select*fromt_groupwhereuser_name=,v_user_name,);
prepares1from@stmt;
executes1;
dropprepares1;
endif;
endwhile;
--Closecursor.
closecur1;
--Freevariablefrommemory.
set@stmt=NULL;
END$$
DELIMITER;
2、实验表。
我们用一个有一万万笔记录的表来做测试。
MySQL>selectcount(*)fromt_group;
+----------+
|count(*)|
+----------+
|10388608|
+----------+
1rowinset(0.00sec)
表布局。
mysql>desct_group;
+-------------+------------------+------+-----+-------------------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------------+------------------+------+-----+-------------------+----------------+
|id|int(10)unsigned|NO|PRI|NULL|auto_increment|
|money|decimal(10,2)|NO||||
|user_name|varchar(20)|NO|MUL|||
|create_time|timestamp|NO||CURRENT_TIMESTAMP||
+-------------+------------------+------+-----+-------------------+----------------+
4rowsinset(0.00sec)
索引情形。
mysql>showindexfromt_group;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|t_group|0|PRIMARY|1|id|A|10388608|NULL|NULL||BTREE||
|t_group|1|idx_user_name|1|user_name|A|8|NULL|NULL||BTREE||
|t_group|1|idx_combination1|1|user_name|A|8|NULL|NULL||BTREE||
|t_group|1|idx_combination1|2|money|A|3776|NULL|NULL||BTREE||
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4rowsinset(0.00sec)
PS:
idx_combination1这个索引是必需的，由于要对user_name来GROUPBY。此时属于松懈索引扫描！固然完了后你能够干失落她。
idx_user_name这个索引是为了加速独自实行constant这类范例的查询。
我们要依据用户名来分表。
mysql>selectuser_namefromt_groupwhere1groupbyuser_name;
+-----------+
|user_name|
+-----------+
|david|
|leo|
|livia|
|lucy|
|sarah|
|simon|
|sony|
|sunny|
+-----------+
8rowsinset(0.00sec)
以是了局表应当是如许的。
mysql>showtablesliket_group_%;
+------------------------------+
|Tables_in_t_girl(t_group_%)|
+------------------------------+
|t_group_david|
|t_group_leo|
|t_group_livia|
|t_group_lucy|
|t_group_sarah|
|t_group_simon|
|t_group_sony|
|t_group_sunny|
+------------------------------+
8rowsinset(0.00sec)
3、对照了局。
mysql>selectcount(*)fromt_groupwhereuser_name=david;
+----------+
|count(*)|
+----------+
|1298576|
+----------+
1rowinset(1.71sec)
实行了快要2秒。
mysql>selectcount(*)fromt_group_david;
+----------+
|count(*)|
+----------+
|1298576|
+----------+
1rowinset(0.00sec)
几近是刹时的。
mysql>selectcount(*)fromt_groupwhereuser_namedavid;
+----------+
|count(*)|
+----------+
|9090032|
+----------+
1rowinset(9.26sec)
实行了快要10秒，能够设想，这个是实践的项目中是不克不及忍耐的。
mysql>select(selectcount(*)fromt_group)-(selectcount(*)fromt_group_david)astotal;
+---------+
|total|
+---------+
|9090032|
+---------+
1rowinset(0.00sec)
几近是刹时的。
我们来看看会萃函数。
关于原表的操纵。
mysql>selectmin(money),max(money)fromt_groupwhereuser_name=david;
+------------+------------+
|min(money)|max(money)|
+------------+------------+
|-6.41|500.59|
+------------+------------+
1rowinset(0.00sec)
最小，最年夜值都是FULLINDEXSCAN。以是是刹时的。
mysql>selectsum(money),avg(money)fromt_groupwhereuser_name=david;
+--------------+------------+
|sum(money)|avg(money)|
+--------------+------------+
|319992383.84|246.417910|
+--------------+------------+
1rowinset(2.15sec)
其他会萃函数的了局就不是FULLINDEXSCAN了。耗时2.15秒。
关于小表的操纵。
mysql>selectmin(money),max(money)fromt_group_david;
+------------+------------+
|min(money)|max(money)|
+------------+------------+
|-6.41|500.59|
+------------+------------+
1rowinset(1.50sec)
最年夜最小值完整是FULLTABLESCAN,耗时1.50秒，不划算。以此看来。
mysql>selectsum(money),avg(money)fromt_group_david;
+--------------+------------+
|sum(money)|avg(money)|
+--------------+------------+
|319992383.84|246.417910|
+--------------+------------+
1rowinset(1.68sec)
获得这两个了局也是花了快2秒，快了一点。
我们来看看这个小表的布局。
mysql>desct_group_david;
+-------------+------------------+------+-----+-------------------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------------+------------------+------+-----+-------------------+----------------+
|id|int(10)unsigned|NO|PRI|NULL|auto_increment|
|money|decimal(10,2)|NO||||
|user_name|varchar(20)|NO|MUL|||
|create_time|timestamp|NO||CURRENT_TIMESTAMP||
+-------------+------------------+------+-----+-------------------+----------------+
4rowsinset(0.00sec)
分明的user_name属性是过剩的。那末就干失落它。
mysql>altertablet_group_daviddropuser_name;
QueryOK,1298576rowsaffected(7.58sec)
Records:1298576Duplicates:0Warnings:0
如今来从头对小表运转查询
mysql>selectmin(money),max(money)fromt_group_david;
+------------+------------+
|min(money)|max(money)|
+------------+------------+
|-6.41|500.59|
+------------+------------+
1rowinset(0.00sec)
此时是刹时的。
mysql>selectsum(money),avg(money)fromt_group_david;
+--------------+------------+
|sum(money)|avg(money)|
+--------------+------------+
|319992383.84|246.417910|
+--------------+------------+
1rowinset(0.94sec)
此次算是把持在一秒之内了。
mysql>Aborted
小总结一下：分出的小表的属性只管越少越好。勇敢的往干吧。
曾经的功能列表可能会迅速变得过时了。而且，有些功能对有的应用程序非常重要，但是对别的应用程序则不一定。

从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

相信各位对数据库和怎么样学习数据库都有一些经验和看法，也会有人走了一些弯路总结出自己的经验来，希望大家能把各自的看法和经验拿出来分享，给别人一份帮助，给自己一份快乐

多走走一此相关论坛，多看一些实例开发，多交流0经验，没什么的，我也是刚学没多久!加油

再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

分区表是个亮点！从分区表也能看出微软要做大作强SQLServer的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQLServer2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。

从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

相信各位对数据库和怎么样学习数据库都有一些经验和看法，也会有人走了一些弯路总结出自己的经验来，希望大家能把各自的看法和经验拿出来分享，给别人一份帮助，给自己一份快乐

这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。