Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.1.24-rc-communitymysql> show create table users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) COLLATE gb2312_bin NOT NULL DEFAULT '',
`email` varchar(30) COLLATE gb2312_bin NOT NULL DEFAULT '',
PRIMARY KEY (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin /*!50
100 PARTITION BY RANGE (uid) (PARTITION p0 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (6) ENGINE = MyISAM, PARTITION p2 VALUES LESS THA
N (9) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) *
/
1 row in set (0.00 sec)mysql> explain select uid from users where uid=2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: Using index
1 row in set (0.00 sec)发现select count(*) from users where uid>2 也是一样的结果,没有用到PARTITION
Your MySQL connection id is 7 to server version: 5.1.24-rc-communitymysql> show create table users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) COLLATE gb2312_bin NOT NULL DEFAULT '',
`email` varchar(30) COLLATE gb2312_bin NOT NULL DEFAULT '',
PRIMARY KEY (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin /*!50
100 PARTITION BY RANGE (uid) (PARTITION p0 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (6) ENGINE = MyISAM, PARTITION p2 VALUES LESS THA
N (9) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) *
/
1 row in set (0.00 sec)mysql> explain select uid from users where uid=2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: Using index
1 row in set (0.00 sec)发现select count(*) from users where uid>2 也是一样的结果,没有用到PARTITION
The following is correct.
explain partitions select uid from users where uid=2\G I advice you read the document again.
Using the statement EXPLAIN PARTITIONS SELECT to see which partitions are used by a given SELECT
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
ENGINE=MYISAM PARTITION BY RANGE (uid) (
PARTITION p0 VALUES LESS THAN (3)
DATA DIRECTORY = 'd:/data0/data'
INDEX DIRECTORY = 'd:/data0/idx', PARTITION p1 VALUES LESS THAN (6)
DATA DIRECTORY = 'd:/data1/data'
INDEX DIRECTORY = 'd:/data1/idx', PARTITION p2 VALUES LESS THAN (9)
DATA DIRECTORY = 'd:/data2/data'
INDEX DIRECTORY = 'd:/data2/idx', PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'd:/data3/data'
INDEX DIRECTORY = 'd:/data3/idx'
);insert into `users`(`uid`,`name`,`email`) values (1,'test','dsds'),(2,'test2','sdsdsd'),(3,'ddd','dddd'),(4,'kkkk','ffff'),(5,'llll','ggg'),(6,'pppp','llll'),(7,'gg','kk');无乱上面是用绝对还是相对路径,都没有发现DATA DIRECTORY 或是 INDEX DIRECTORY ,不知道该如何实现物理上的分离?
还望大家继续指正,谢谢!