?????????????????? ???????????? def ?????? ?????? ?????? password ?????? ??????
?????????????????????????????????mysql???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????database
???????????????namespace
????????????database
?????????????????????????????????database???
???????????????????????????????????????employees
????????????
mysql> show tables in employees;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------+
????????????????????????????????????
# haitian at haitian-coder.local in /usr/local/var/mysql/employees on git:master ??? [21:19:47]
??? ls
db.opt dept_emp.frm dept_manager.ibd salaries.frm titles.ibd
departments.frm dept_emp.ibd employees.frm salaries.ibd
departments.ibd dept_manager.frm employees.ibd titles.frm
database
????????????????????????namespace
??????????????????MySQL
???database
?????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????user????????????????????????????????????????????????????????????????????????forum??????????????????????????????????????????????????????
??????????????????????????????????????????????????????????????????????????????????????????????????????user?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
??????????????????????????????????????????B-Tree??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????I/O????????????????????????????????????????????????????????????????????????????????????????????????????????????I/O?????????????????????????????????
?????????
1.??????MySQL????????????????????????????????????????????????????????????Innodb
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
2.????????????????????????????????????????????????
??????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????
1.???????????????????????????table?????????????????????????????????????????????
2.????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
??????????????? ?????????????????????????????????????????????
????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????500w?????????????????????Hash????????????Hash????????????
?????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????flickr
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????uuid???????????????????????????????????????????????????Instagram
???ID????????????
?????????Hash???
???????????????Hash??????????????????Hash??????????????????????????????????????????????????????????????????????????????Hash???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????ip???Hash??????????????????????????????????????????????????????????????????????????????Hash???
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????SQL
????????????????????????????????????????????????????????????????????????????????????????????????
??????????????????MySQL
?????????????????????????????????MySQL
???????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????????????????????????????????SQL
??????????????????MySQL
????????????????????????????????????SQL
?????????????????????MySQL
???????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????????????
1.???5.6.7????????????????????????????????????1024
???????????????5.6.7?????????????????????????????????8192
????????????
2.???????????????????????????????????????
3.??????????????????????????????????????????????????????????????????????????????MySQL
???????????????????????????
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
???????????????????????????????????????????????????????????????MySQL
?????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????PARTITION BY RANGE
, VALUES LESS THAN
?????????????????????COLUMNS
????????????RANGE
??????????????????????????????????????????????????????????????????
?????????????????????
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT ???1970-01-01???,
separated DATE NOT NULL DEFAULT ???9999-12-31???,
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
??????TIMESTAMP
?????????
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP(???2008-01-01 00:00:00???) ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP(???2008-04-01 00:00:00???) ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP(???2008-07-01 00:00:00???) ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP(???2008-10-01 00:00:00???) ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP(???2009-01-01 00:00:00???) ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP(???2009-04-01 00:00:00???) ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP(???2009-07-01 00:00:00???) ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP(???2009-10-01 00:00:00???) ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP(???2010-01-01 00:00:00???) ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
??????COLUMNS
?????????????????????integer??????????????????????????????????????????COLUMNS
???????????????????????????????????????????????????????????????????????????????????????????????????
??????DATE
???DATETIME
?????????
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
PARTITION p0 VALUES LESS THAN (???1960-01-01???),
PARTITION p1 VALUES LESS THAN (???1970-01-01???),
PARTITION p2 VALUES LESS THAN (???1980-01-01???),
PARTITION p3 VALUES LESS THAN (???1990-01-01???),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
?????????????????????
CREATE TABLE rc3 (
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (0,10),
PARTITION p1 VALUES LESS THAN (10,20),
PARTITION p2 VALUES LESS THAN (10,30),
PARTITION p3 VALUES LESS THAN (10,35),
PARTITION p4 VALUES LESS THAN (20,40),
PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
???????????????????????????????????????????????????????????????PARTITION BY LIST
???VALUES IN
???????????????Range
????????????????????????COLUMNS
????????????List
??????????????????????????????????????????????????????????????????
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT ???1970-01-01???,
separated DATE NOT NULL DEFAULT ???9999-12-31???,
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
??????????????????????????????????????????????????????????????????????????????????????????????????????
mysql> CREATE TABLE h2 (
-> c1 INT,
-> c2 INT
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (1, 4, 7),
-> PARTITION p1 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3
??????????????????????????????????????????????????????????????????Innodb
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
????????????IGNORE
????????????????????????????????????????????????????????????????????????????????????????????????
mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM h2;
Empty set (0.00 sec)
mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 5 Duplicates: 2 Warnings: 0
mysql> SELECT * FROM h2;
+------+------+
| c1 | c2 |
+------+------+
| 7 | 5 |
| 1 | 9 |
| 2 | 5 |
+------+------+
3 rows in set (0.00 sec)
???Range
?????????????????????COLUMNS
???????????????????????????????????????
Hash
??????????????????????????????????????????????????????????????????????????????Hash
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT ???1970-01-01???,
separated DATE NOT NULL DEFAULT ???9999-12-31???,
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT ???1970-01-01???,
separated DATE NOT NULL DEFAULT ???9999-12-31???,
job_code INT,
store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;
Hash
????????????????????????Hash
??????????????????????????????????????????MySQL
?????????????????????????????????Hash
????????????????????????Hash
??????????????????????????????????????????LINEAR
????????????????????????????????????????????????????????????????????????
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT ???1970-01-01???,
separated DATE NOT NULL DEFAULT ???9999-12-31???,
job_code INT,
store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;
??????KEY???????????????????????????HASH???????????????HASH?????????????????????????????????????????????KEY????????? ??????????????????MySQL ??????????????????MySQL ??????Cluster???????????????MD5()?????????KEY????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????PASSWORD()????????????????????????
Key
?????????Hash
????????????????????????Hash
???????????????????????????Hash
??????????????????Key
???????????????Key
???????????????????????????Hash
?????????Key
???????????????
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
??????????????????????????????????????????????????????Key
?????????????????????Mysql
????????????????????????????????????????????????????????????????????????????????????
??????????????????????????????????????????????????????????????????????????????
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
???
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0
DATA DIRECTORY = ???/disk0/data???
INDEX DIRECTORY = ???/disk0/idx???,
SUBPARTITION s1
DATA DIRECTORY = ???/disk1/data???
INDEX DIRECTORY = ???/disk1/idx???
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2
DATA DIRECTORY = ???/disk2/data???
INDEX DIRECTORY = ???/disk2/idx???,
SUBPARTITION s3
DATA DIRECTORY = ???/disk3/data???
INDEX DIRECTORY = ???/disk3/idx???
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4
DATA DIRECTORY = ???/disk4/data???
INDEX DIRECTORY = ???/disk4/idx???,
SUBPARTITION s5
DATA DIRECTORY = ???/disk5/data???
INDEX DIRECTORY = ???/disk5/idx???
)
);
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????SUBPARTITION
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????
???????????????????????????????????????????????????Range
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????100???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????REORGANIZE PARTITION
???????????????????????????????????????????????????????????????100??????????????????????????????
?????????????????????????????????????????????Hash???Key?????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????explain partitions
???????????????SQL
??????????????????????????????
????????????????????????where
???????????????????????????
?????????salaries
????????????
mysql> show create table salariesG;
*************************** 1. row ***************************
Table: salaries
Create Table: CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (year(from_date))
(PARTITION p1 VALUES LESS THAN (1985) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1986) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (1987) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (1988) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (1989) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (1991) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (1992) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (1993) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (1994) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (1996) ENGINE = InnoDB,
PARTITION p13 VALUES LESS THAN (1997) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (1998) ENGINE = InnoDB,
PARTITION p15 VALUES LESS THAN (1999) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p17 VALUES LESS THAN (2001) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
?????????????????????????????????????????????partitions
??????????????????????????????
mysql> explain partitions select * from salaries where salary > 100000G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: salaries
partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2835486
Extra: Using where
?????????where
???????????????????????????????????????????????????????????????????????????
mysql> explain partitions select * from salaries where salary > 100000 and from_date > ???1998-01-01???G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: salaries
partitions: p15,p16,p17,p18
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1152556
Extra: Using where
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
mysql> explain partitions select * from salaries where salary > 100000 and year(from_date) > 1998G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: salaries
partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2835486
Extra: Using where
count
???sum
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. A simple example of such a query might be SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. By ???parallelized,??? we mean that the query can be run simultaneously on each partition, and the final result obtained merely by summing the results obtained for all partitions.
???????????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????
tddl?????????????????????matrix???group???atom??????
??????????????????????????????HA???????????????HA?????????slave??????
1 ????????????????????????
2 jboss?????????, ip port ????????????????????? ???????????????
3 Thread count??????,?????? ?????????????????????,?????????????????????????????????
4 ??????????????????SQL??????
5 ???????????????????????????
????????????tddl?????????????????????????????????????????????????????????id??????????????????????????????????????????????????????. ????????????????????????????????????????????????????????????????????????id.
???????????????????????????????????????1??????????????????2???????????????3???????????????
tddl???????????????????????????????????????????????????????????????????????? ????????????????????? ?????????????????????????????????????????????????????????1000???
group | value |
group_0 | 0 |
group_1 | 1000 |
group_2 | 2000 |
group_3 | 3000 |
???????????????????????????????????????4???group??????????????????????????????value+?????????id???????????????group_1??????1000???1000+1000???id??????????????????????????????????????????????????????????????????????????????????????????
group | value |
group_0 | 0 |
group_1 | 5000 |
group_2 | 2000 |
group_3 | 3000 |
??????????????????????????????group???????????????value+group?????????*?????????