mysql MYISAM引擎子分区分散IO.sql

来源：互联网发布：转区软件ntleas 编辑：程序博客网时间：2024/05/15 03:37

mkdir -p /disk1/data
mkdir -p /disk2/data
mkdir -p /disk3/data
mkdir -p /disk4/data
mkdir -p /disk5/data
mkdir -p /disk6/data
mkdir -p /disk7/data
mkdir -p /disk8/data
mkdir -p /disk9/data
mkdir -p /disk10/data
mkdir -p /disk11/data
mkdir -p /disk12/data
mkdir -p /disk1/idx
mkdir -p /disk2/idx
mkdir -p /disk3/idx
mkdir -p /disk4/idx
mkdir -p /disk5/idx
mkdir -p /disk6/idx
mkdir -p /disk7/idx
mkdir -p /disk8/idx
mkdir -p /disk9/idx
mkdir -p /disk10/idx
mkdir -p /disk11/idx
mkdir -p /disk12/idx
chown -R mysql.mysql /disk1/idx
chown -R mysql.mysql /disk2/idx
chown -R mysql.mysql /disk3/idx
chown -R mysql.mysql /disk4/idx
chown -R mysql.mysql /disk5/idx
chown -R mysql.mysql /disk6/idx
chown -R mysql.mysql /disk7/idx
chown -R mysql.mysql /disk8/idx
chown -R mysql.mysql /disk9/idx
chown -R mysql.mysql /disk10/idx
chown -R mysql.mysql /disk11/idx
chown -R mysql.mysql /disk12/idx
chown -R mysql.mysql /disk1/data
chown -R mysql.mysql /disk2/data
chown -R mysql.mysql /disk3/data
chown -R mysql.mysql /disk4/data
chown -R mysql.mysql /disk5/data
chown -R mysql.mysql /disk6/data
chown -R mysql.mysql /disk7/data
chown -R mysql.mysql /disk8/data
chown -R mysql.mysql /disk9/data
chown -R mysql.mysql /disk10/data
chown -R mysql.mysql /disk11/data
chown -R mysql.mysql /disk12/data

CREATE TABLE `t_jiwei` (
`logid` bigint(11) NOT NULL DEFAULT '0',
`clientid` int(11) NOT NULL,
`unitid` int(11) NOT NULL,
`msign` int(11) NOT NULL,
`userip` varchar(15) NOT NULL,
`areaid` int(11) DEFAULT NULL,
`userid` int(11) DEFAULT NULL,
`visittime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`visityear` int(11) NOT NULL,
`visitmonth` int(11) NOT NULL,
`visitday` int(11) NOT NULL,
`visithour` int(11) NOT NULL,
`visitpath` varchar(255) NOT NULL,
`visitparams` varchar(1000) DEFAULT NULL,
`pagereferer` varchar(255) DEFAULT NULL,
`browseruseragent` varchar(255) DEFAULT NULL,
`visitcookies` varchar(4096) DEFAULT NULL
) ENGINE=MYISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (visityear)
SUBPARTITION BY HASH (visitmonth)
SUBPARTITIONS 12
(PARTITION p01 VALUES LESS THAN (2014)
(subpartition s1_2013
             DATA DIRECTORY = '/disk1/data'
             INDEX DIRECTORY = '/disk1/idx',
subpartition s2_2013
             DATA DIRECTORY = '/disk2/data'
             INDEX DIRECTORY = '/disk2/idx',
subpartition s3_2013
             DATA DIRECTORY = '/disk3/data'
             INDEX DIRECTORY = '/disk3/idx',
subpartition s4_2013
             DATA DIRECTORY = '/disk4/data'
             INDEX DIRECTORY = '/disk4/idx',
subpartition s5_2013
             DATA DIRECTORY = '/disk5/data'
             INDEX DIRECTORY = '/disk5/idx',
subpartition s6_2013
             DATA DIRECTORY = '/disk6/data'
             INDEX DIRECTORY = '/disk6/idx',
subpartition s7_2013
             DATA DIRECTORY = '/disk7/data'
             INDEX DIRECTORY = '/disk7/idx',
subpartition s8_2013
             DATA DIRECTORY = '/disk8/data'
             INDEX DIRECTORY = '/disk8/idx',
subpartition s9_2013
             DATA DIRECTORY = '/disk9/data'
             INDEX DIRECTORY = '/disk9/idx',
subpartition s10_2013
             DATA DIRECTORY = '/disk10/data'
             INDEX DIRECTORY = '/disk10/idx',
subpartition s11_2013
             DATA DIRECTORY = '/disk11/data'
             INDEX DIRECTORY = '/disk11/idx',
subpartition s12_2013
             DATA DIRECTORY = '/disk12/data'
             INDEX DIRECTORY = '/disk12/idx'
));

--------------------------------------------------------------------------------
insert into t_jiwei

(clientid,unitid,msign,userip,areaid,userid,visittime,visityear,visitmonth,visitday,visithour,visitpath,visitparams,pagereferer,browseruseragent,visitcookies)
select clientid,unitid,msign,userip,areaid,userid,visittime,visityear,visitmonth,visitday,visithour,visitpath,visitparams,pagereferer,browseruseragent,visitcookies

from logdetail1

select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,SUBPARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS from

PARTITIONS where TABLE_NAME like 't_jiwei'

------------------------------------------------------------------------------

添加新的年份

alter table t3 add partition(PARTITION p02 VALUES LESS THAN (2015)
(subpartition s01_2014
             DATA DIRECTORY = '/disk1/data'
             INDEX DIRECTORY = '/disk1/idx',
subpartition s02_2014
             DATA DIRECTORY = '/disk2/data'
             INDEX DIRECTORY = '/disk2/idx',
subpartition s03_2014
             DATA DIRECTORY = '/disk3/data'
             INDEX DIRECTORY = '/disk3/idx',
subpartition s04_2014
             DATA DIRECTORY = '/disk4/data'
             INDEX DIRECTORY = '/disk4/idx',
subpartition s05_2014
             DATA DIRECTORY = '/disk5/data'
             INDEX DIRECTORY = '/disk5/idx',
subpartition s06_2014
             DATA DIRECTORY = '/disk6/data'
             INDEX DIRECTORY = '/disk6/idx',
subpartition s07_2014
             DATA DIRECTORY = '/disk7/data'
             INDEX DIRECTORY = '/disk7/idx',
subpartition s08_2014
             DATA DIRECTORY = '/disk8/data'
             INDEX DIRECTORY = '/disk8/idx',
subpartition s09_2014
             DATA DIRECTORY = '/disk9/data'
             INDEX DIRECTORY = '/disk9/idx',
subpartition s010_2014
             DATA DIRECTORY = '/disk10/data'
             INDEX DIRECTORY = '/disk10/idx',
subpartition s011_2014
             DATA DIRECTORY = '/disk11/data'
             INDEX DIRECTORY = '/disk11/idx',
subpartition s012_2014
             DATA DIRECTORY = '/disk12/data'
             INDEX DIRECTORY = '/disk12/idx'
));

-----------------------------------------------------------------

注：innodb引擎里的 DATA DIRECTORY INDEX DIRECTORY是忽略的，必须使用MYISAM引擎，

对一些日志表有用