Dark Mode

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Sign up
Appearance settings

Getting start

Jump to bottom
Mingyu Chen edited this page Dec 24, 2018 * 5 revisions

Ji Chu Shi Yong Zhi Nan

DorisCai Yong mysqlXie Yi Jin Xing Tong Xin ,Yong Hu Ke Tong Guo mysql clientHuo Zhe JDBCLian Jie Dao DorisJi Qun . Xuan Ze mysql clientBan Ben Shi Jian Yi Cai Yong 5.1Zhi Hou De Ban Ben ,Yin Wei 5.1Zhi Qian Bu Neng Zhi Chi Chang Du Chao Guo 16Ge Zi Fu De Yong Hu Ming . Ben Wen Yi mysql clientWei Li ,Tong Guo Yi Ge Wan Zheng De Liu Cheng Xiang Yong Hu Zhan Shi DorisDe Ji Ben Shi Yong Fang Fa .

1. Chuang Jian Yong Hu

1.1 RootYong Hu Deng Lu Yu Mi Ma Xiu Gai

DorisNei Zhi rootYong Hu ,Mi Ma Mo Ren Wei Kong ,Qi Dong Wan DorisCheng Xu Zhi Hou ,Ke Yi Tong Guo rootYong Hu Lian Jie Dao DorisJi Qun . Jia Ru mysqlKe Hu Duan He Doris FECheng Xu Bu Shu Zai Tong Yi Tai Ji Qi ,Shi Yong Mo Ren Duan Kou ,Xia Mian Ming Ling Ji Ke Deng Lu Doris.

mysql -h 127.0.0.1 -P9030 -uroot

Xiu Gai rootMi Ma

set password for 'root' = PASSWORD('root');

1.2 Chuang Jian cluster (Ke Xuan ,Ru Guo Xu Yao Shi Yong dorisDe Duo Zu Hu Gong Neng )

Ru Guo Xu Yao Shi Yong Duo Zu Hu Gong Neng ,Ze Zai Bu Shu De Shi Hou Xu Yao An Zhao An Zhuang Wen Dang 3.2Jie Zhong Ti Shi De Fang Fa Tian Jia be. Zai Duo Zu Hu Mo Shi Xia ,Yong Hu Yi Ji Xiang Guan De Shu Ju Ku Du Zai clusterZhi Xia . Xiu Gai Wan rootYong Hu Mi Ma Zhi Hou ,Jin Jie Zhao Xu Yao Chuang Jian cluster,Chuang Jian clusterShi Hui Wei clusterChuang Jian Yi Ge superuserYong Hu ,Chuang Jian clusterDe Ming Ling Ru Xia :

CREATE CLUSTER example_cluster PROPERTIES("instance_num"="1") IDENTIFIED BY 'superuser';

Shang Shu Ming Ling Chuang Jian Liao Yi Ge example_clusterDe cluster,Mi Ma Wei superuserDe superuserYong Hu ,propertiesZhong De instance_numBiao Shi Zhe Ge clusterYun Xing Zai Yi Ge BEJie Dian Zhi Shang .

Ci Shi Ke Shi Yong rootYong Hu Deng Lu Doris,Bing Jin Ru example_cluster.

mysql -h 127.0.0.1 -P9030 -uroot -proot
enter example_cluster;

1.3 Chuang Jian Xin Yong Hu

Ru Guo Bu Shi Yong Duo Zu Hu Ze An Zhao 1.3.1,Shi Yong Ze An Zhao 1.3.2.

1.3.1 Bu Shi Yong Duo Zu Hu

Tong Guo Xia Mian De Ming Ling Chuang Jian Yi Ge Pu Tong Yong Hu .

create user 'test' identified by 'test';

Ye Ke Yi Chuang Jian Yi Ge Guan Li Yuan superuserYong Hu ,Zhi Ding Mi Ma De Fang Shi Ru 1.1Jie Miao Shu .

create user 'test' superuser;

Hou Xu Deng Lu Shi Jiu Ke Yi Tong Guo Xia Lie Lian Jie Ming Ling Deng Lu .

mysql -h FE_HOST -P QUERY_PORT -uUSERNAME -pPASSWORD
  • FE_HOST: Bu Shu FEDe Ji Qi .
  • QUERY_PORT: Zai fe.confZhong Jin Xing Pei Zhi ,Mo Ren Pei Zhi Wei 9030.
  • USERNAME: Yong Hu Ming .
  • PASSWORD: Chuang Jian Yong Hu Shi Zhi Ding De Mi Ma .

Shi Yong rootDeng Lu DorisJi Qun .

mysql -h 127.0.0.1 -P9030 -uroot -proot

Shi Yong testDeng Lu DorisJi Qun .

mysql -h 127.0.0.1 -P9030 -utest -ptest
1.3.2 Shi Yong Duo Zu Hu

Jin Ru Dao Zhi Ding clusterZhi Hou ,Ke Yi Zai Li Mian Chuang Jian Xin De Yong Hu .

create user 'test' identified by 'test';

Hou Xu Deng Lu Shi Jiu Ke Yi Tong Guo Xia Lie Lian Jie Ming Ling Deng Lu Dao Zhi Ding cluster

mysql -h FE_HOST -P QUERY_PORT -uUSERNAME@CLUSTER_NAME -pPASSWORD
  • FE_HOST: Bu Shu FEDe Ji Qi .
  • QUERY_PORT: Zai fe.confZhong Jin Xing Pei Zhi ,Mo Ren Pei Zhi Wei 9030.
  • USERNAME: Yong Hu Ming .
  • CLUSTER_NAME: Chuang Jian De clusterMing Cheng .
  • PASSWORD: Chuang Jian Yong Hu Shi Zhi Ding De Mi Ma .

Shi Yong rootDeng Lu DorisJi Qun ,Bing Jin Ru example_cluster.

mysql -h 127.0.0.1 -P9030 -uroot@example_cluster -proot

Dang Ran ,rootZhang Hu Yi Ran Ke Yi Cai Yong Xian Deng Lu DorisJi Qun ,Hou enterDao Zhi Ding clusterDe Fang Shi ;Er Qi Ta Yong Hu Deng Lu Bi Xu Xian Shi Zhi Ming clusterDe Ming Cheng .

Shi Yong superuserDeng Lu DorisJi Qun ,Bing Jin Ru example_cluster.

mysql -h 127.0.0.1 -P9030 -usuperuser@example_cluster -psuperuser

Shi Yong testDeng Lu DorisJi Qun ,Bing Jin Ru example_cluster.

mysql -h 127.0.0.1 -P9030 -utest@example_cluster -ptest

2 Shu Ju Biao De Chuang Jian Yu Shu Ju Dao Ru

2.1 Chuang Jian Shu Ju Ku

DorisZhong Zhi You rootZhang Hu He superuserZhang Hu You Quan Xian Jian Li Shu Ju Ku ,Shi Yong rootHuo superuserYong Hu Deng Lu ,Jian Li example_dbShu Ju Ku :

CREATE DATABASE example_db;
  • Suo You Ming Ling Du Ke Yi Shi Yong 'HELP your_command'Cha Kan Dao Xiang Xi De Zhong Wen Bang Zhu
  • Ru Guo Bu Qing Chu Ming Ling De Quan Ming ,Ke Yi Shi Yong 'help Ming Ling Mou Yi Zi Duan ' Jin Xing Mo Hu Cha Xun . Ru Jian Ru 'HELP CREATE',Ke Yi Pi Pei Dao CREATE DATABASE, CREATE TABLE, CREATE USERSan Ge Ming Ling

Shu Ju Ku Chuang Jian Wan Cheng Zhi Hou ,Ke Yi Tong Guo show databasesCha Kan Shu Ju Ku Xin Xi .

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| test |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

information_schemaShi Wei Liao Jian Rong mysqlXie Yi Er Cun Zai ,Shi Ji Zhong Xin Xi Ke Neng Bu Shi Hen Zhun Que ,Suo Yi Guan Yu Ju Ti Shu Ju Ku De Xin Xi Jian Yi Tong Guo Zhi Jie Cha Xun Xiang Ying Shu Ju Ku Er Huo De .

2.2 Zhang Hu Shou Quan

example_dbChuang Jian Wan Cheng Zhi Hou ,Ke Yi Tong Guo rootZhang Hu Huo Zhe superuserZhang Hu Jiang example_dbDu Xie Quan Xian Shou Quan Gei testZhang Hu , Shou Quan Zhi Hou Cai Yong testZhang Hu Deng Lu Jiu Ke Yi Cao Zuo example_dbShu Ju Ku Liao .

grant all on example_db to test;

2.3 Jian Biao

Shi Yong CREATE TABLEMing Ling Jian Li Yi Ge Biao (Table). Geng Duo Xiang Xi Can Shu Ke Yi Cha Kan :

HELP CREATE TABLE;

Shou Xian Qie Huan Shu Ju Ku :

USE example_db;

DorisZhi Chi Zhi Chi Dan Fen Qu He Fu He Fen Qu Liang Chong Jian Biao Fang Shi .

Zai Fu He Fen Qu Zhong :

  • Di Yi Ji Cheng Wei Partition,Ji Fen Qu . Yong Hu Ke Yi Zhi Ding Mou Yi Wei Du Lie Zuo Wei Fen Qu Lie (Dang Qian Zhi Zhi Chi Zheng Xing He Shi Jian Lei Xing De Lie ),Bing Zhi Ding Mei Ge Fen Qu De Qu Zhi Fan Wei .

  • Di Er Ji Cheng Wei Distribution,Ji Fen Tong . Yong Hu Ke Yi Zhi Ding Mou Ji Ge Wei Du Lie (Huo Bu Zhi Ding ,Ji Suo You KEYLie )Yi Ji Tong Shu Dui Shu Ju Jin Xing HASHFen Bu .

Yi Xia Chang Jing Tui Jian Shi Yong Fu He Fen Qu

  • You Shi Jian Wei Du Huo Lei Si Dai You You Xu Zhi De Wei Du :Ke Yi Yi Zhe Lei Wei Du Lie Zuo Wei Fen Qu Lie . Fen Qu Li Du Ke Yi Gen Ju Dao Ru Pin Ci , Fen Qu Shu Ju Liang Deng Jin Xing Ping Gu .
  • Li Shi Shu Ju Shan Chu Xu Qiu :Ru You Shan Chu Li Shi Shu Ju De Xu Qiu (Bi Ru Jin Bao Liu Zui Jin N Tian De Shu Ju ). Shi Yong Fu He Fen Qu ,Ke Yi Tong Guo Shan Chu Li Shi Fen Qu Lai Da Dao Mu De . Ye Ke Yi Tong Guo Zai Zhi Ding Fen Qu Nei Fa Song DELETEYu Ju Jin Xing Shu Ju Shan Chu .
  • Jie Jue Shu Ju Qing Xie Wen Ti :Mei Ge Fen Qu Ke Yi Dan Du Zhi Ding Fen Tong Shu Liang . Ru An Tian Fen Qu ,Dang Mei Tian De Shu Ju Liang Chai Yi Hen Da Shi ,Ke Yi Tong Guo Zhi Ding Fen Qu De Fen Tong Shu ,He Li Hua Fen Bu Tong Fen Qu De Shu Ju ,Fen Tong Lie Jian Yi Xuan Ze Qu Fen Du Da De Lie .

Yong Hu Ye Ke Yi Bu Shi Yong Fu He Fen Qu ,Ji Shi Yong Dan Fen Qu . Ze Shu Ju Zhi Zuo HASHFen Bu .

Xia Mian Yi Ju He Mo Xing Wei Li ,Fen Bie Yan Shi Liang Chong Fen Qu De Jian Biao Yu Ju .

Dan Fen Qu

Jian Li Yi Ge Ming Zi Wei table1De Luo Ji Biao . Shi Yong Quan hashFen Tong ,Fen Tong Lie Wei siteid,Tong Shu Wei 10.

Zhe Ge Biao De schemaRu Xia :

  • siteid:Lei Xing Shi INT(4Zi Jie ), Mo Ren Zhi Wei 10
  • citycode:Lei Xing Shi SMALLINT(2Zi Jie )
  • username:Lei Xing Shi VARCHAR, Zui Da Chang Du Wei 32, Mo Ren Zhi Wei Kong Zi Fu Chuan
  • pv:Lei Xing Shi BIGINT(8Zi Jie ), Mo Ren Zhi Shi 0; Zhe Shi Yi Ge Zhi Biao Lie , DorisNei Bu Hui Dui Zhi Biao Lie Zuo Ju He Cao Zuo , Zhe Ge Lie De Ju He Fang Fa Shi Qiu He (SUM)

Jian Biao Yu Ju Ru Xia :

CREATE TABLE table1
(
siteid INT DEFAULT '10',
citycode SMALLINT,
username VARCHAR(32) DEFAULT '',
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(siteid, citycode, username)
DISTRIBUTED BY HASH(siteid) BUCKETS 10
PROPERTIES("replication_num" = "1");

Fu He Fen Qu

Jian Li Yi Ge Ming Zi Wei table2De Luo Ji Biao .

Zhe Ge Biao De schema Ru Xia :

  • event_day:Lei Xing Shi DATE,Wu Mo Ren Zhi
  • siteid:Lei Xing Shi INT(4Zi Jie ), Mo Ren Zhi Wei 10
  • citycode:Lei Xing Shi SMALLINT(2Zi Jie )
  • username:Lei Xing Shi VARCHAR, Zui Da Chang Du Wei 32, Mo Ren Zhi Wei Kong Zi Fu Chuan
  • pv:Lei Xing Shi BIGINT(8Zi Jie ), Mo Ren Zhi Shi 0; Zhe Shi Yi Ge Zhi Biao Lie , Doris Nei Bu Hui Dui Zhi Biao Lie Zuo Ju He Cao Zuo , Zhe Ge Lie De Ju He Fang Fa Shi Qiu He (SUM)

Wo Men Shi Yong event_dayLie Zuo Wei Fen Qu Lie ,Jian Li 3Ge Fen Qu : p1, p2, p3

  • p1:Fan Wei Wei [Zui Xiao Zhi , 2017-06-30)
  • p2:Fan Wei Wei [2017-06-30, 2017-07-31)
  • p3:Fan Wei Wei [2017-07-31, 2017-08-31)

Mei Ge Fen Qu Shi Yong siteidJin Xing Ha Xi Fen Tong ,Tong Shu Wei 10

Jian Biao Yu Ju Ru Xia :

CREATE TABLE table2
(
event_day DATE,
siteid INT DEFAULT '10',
citycode SMALLINT,
username VARCHAR(32) DEFAULT '',
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(event_day, siteid, citycode, username)
PARTITION BY RANGE(event_day)
(
PARTITION p1 VALUES LESS THAN ('2017-06-30'),
PARTITION p2 VALUES LESS THAN ('2017-07-31'),
PARTITION p3 VALUES LESS THAN ('2017-08-31')
)
DISTRIBUTED BY HASH(siteid) BUCKETS 10
PROPERTIES("replication_num" = "1");

Biao Jian Wan Zhi Hou ,Ke Yi Cha Kan example_dbZhong Biao De Xin Xi :

mysql> show tables;
+----------------------+
| Tables_in_example_db |
+----------------------+
| table1 |
| table2 |
+----------------------+
2 rows in set (0.01 sec)

mysql> desc table1;
+----------+-------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-------+---------+-------+
| siteid | int(11) | Yes | true | 10 | |
| citycode | smallint(6) | Yes | true | N/A | |
| username | varchar(32) | Yes | true | | |
| pv | bigint(20) | Yes | false | 0 | SUM |
+----------+-------------+------+-------+---------+-------+
4 rows in set (0.00 sec)

mysql> desc table2;
+-----------+-------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-------+---------+-------+
| event_day | date | Yes | true | N/A | |
| siteid | int(11) | Yes | true | 10 | |
| citycode | smallint(6) | Yes | true | N/A | |
| username | varchar(32) | Yes | true | | |
| pv | bigint(20) | Yes | false | 0 | SUM |
+-----------+-------------+------+-------+---------+-------+
5 rows in set (0.00 sec)

Zhu Yi Shi Xiang :

  • Shang Shu Biao Tong Guo She Zhi replication_numJian De Du Shi Dan Fu Ben De Biao ,DorisJian Yi Yong Hu Cai Yong Mo Ren De 3Fu Ben She Zhi ,Yi Bao Zheng Gao Ke Yong .
  • Ke Yi Dui Fu He Fen Qu Biao Dong Tai De Zeng Shan Fen Qu . Xiang Jian 'HELP ALTER TABLE'Zhong PARTITIONXiang Guan Bu Fen .
  • Shu Ju Dao Ru Ke Yi Dao Ru Zhi Ding De partition. Xiang Jian 'HELP LOAD'.
  • Ke Yi Dong Tai Xiu Gai Biao De Schema.
  • Ke Yi Dui TableZeng Jia Shang Juan Biao (Rollup)Yi Ti Gao Cha Xun Xing Neng ,Zhe Bu Fen Ke Yi Can Jian Gao Ji Shi Yong Zhi Nan Guan Yu RollupDe Miao Shu .

2.4 Dao Ru Shu Ju

Doris Zhi Chi Liang Chong Shu Ju Dao Ru Fang Shi :

  • Xiao Pi Liang Dao Ru :Zhen Dui Xiao Pi Liang Shu Ju De Dao Ru . Xiang Jian 'HELP MINI LOAD'
  • Pi Liang Dao Ru :Zhi Chi Du Qu HDFSWen Jian ,Bu Shu Bu Tong brokerKe Yi Du Qu Bu Tong Ban Ben HDFSShu Ju . Xiang Jian 'HELP LOAD'

Wo Men Zhe Li Fen Bie Ti Gong Liang Chong Dao Ru Fang Shi De Cao Zuo Shi Li ,Wei Kuai Su Wan Cheng Dao Ru Jian Yi Shi Yong Fang Cai Yong Xiao Pi Liang Dao Ru Jin Xing Shu Ju Dao Ru De Ce Shi .

Xiao Pi Liang Dao Ru

Xiao Pi Liang Dao Ru : Zhu Yao Yong Yu Rang Yong Hu Ke Yi Bu Yi Lai HDFS,Dao Ru Ben Di Mu Lu Wen Jian .

Xiao Pi Liang Dao Ru Shi DorisZhong Wei Yi Bu Shi Yong mysql-clientZhi Xing De Ming Ling ,Cai Yong httpXie Yi Wan Cheng Tong Xin . Xiao Pi Liang Dao Ru De Duan Kou Shi fe.confZhong Pei Zhi De http port.

Shi Li 1:Yi "table1_20170707"Wei Label,Shi Yong Ben Di Wen Jian table1_dataDao Ru table1Biao .

curl --location-trusted -u test:test -T table1_data http://127.0.0.1:8030/api/example_db/table1/_load?label=table1_20170707

Ben Di table1_dataYi \tZuo Wei Shu Ju Zhi Jian De Fen Ge ,Ju Ti Nei Rong Ru Xia :

1 1 'jim' 2
2 1 'grace' 2
3 2 'tom' 2
4 3 'bush' 3
5 3 'helen' 3

Shi Li 2: Yi "table2_20170707"Wei Label,Shi Yong Ben Di Wen Jian table2_dataDao Ru table2Biao .

curl --location-trusted -u test:test -T table2_data http://127.0.0.1:8030/api/example_db/table2/_load?label=table2_20170707

Ben Di table2_dataYi \tZuo Wei Shu Ju Zhi Jian De Fen Ge ,Ju Ti Nei Rong Ru Xia :

2017-07-03 1 1 'jim' 2
2017-07-05 2 1 'grace' 2
2017-07-12 3 2 'tom' 2
2017-07-15 4 3 'bush' 3
2017-07-12 5 3 'helen' 3

Zhu Yi Shi Xiang :

Pi Liang Dao Ru

Shi Li :Yi "table1_20170707"Wei Label,Shi Yong HDFSShang De Wen Jian Dao Ru table1Biao

LOAD LABEL table1_20170707
(
DATA INFILE("hdfs://your.namenode.host:port/dir/table1_data")
INTO TABLE table1
)
WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password")
PROPERTIES
(
"timeout"="3600",
"max_filter_ratio"="0.1"
);

Shi Li :Yi "table2_20170707"Wei Label,Shi Yong HDFSShang De Wen Jian Dao Ru table2Biao

LOAD LABEL table2_20170707
(
DATA INFILE("hdfs://your.namenode.host:port/dir/table2_data")
INTO TABLE table2
)
WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password")
PROPERTIES
(
"timeout"="3600",
"max_filter_ratio"="0.1"
);

Zhu Yi Shi Xiang :

2.5 Cha Xun Dao Ru Ren Wu De Zhuang Tai

Dao Ru Ren Wu Shi Yi Bu Zhi Xing De . Zhi Xing Dao Ru Ming Ling Hou ,Xu Yao Tong Guo SHOW LOAD Ming Ling Cha Xun Dao Ru Ren Wu De Zhuang Tai . Geng Duo Xiang Xi Can Shu Ke Yi Cha Kan :

HELP SHOW LOAD;

Dao Ru Ren Wu De Zhu Yao Xin Xi Wei :

Shi Li 1:Xian Shi Dang Qian Shu Ju Ku Nei Yi "table1_20170707"Wei Label De Suo You Ren Wu De Zhuang Tai De Xiang Xi Xin Xi

SHOW LOAD WHERE LABEL = "table1_20170707";

Shi Li 2:Xian Shi Dang Qian Zheng Zai Zuo ETLDe Suo You Ren Wu De Zhuang Tai Xin Xi

SHOW LOAD WHERE STATE = "ETL";

Shi Li 3:Xian Shi Dang Qian Shu Ju Ku Nei Zui Hou 20Ge Dao Ru Ren Wu De Zhuang Tai

SHOW LOAD ORDER BY CreateTime DESC LIMIT 20;

Zhu Yi Shi Xiang :

2.6 Qu Xiao Dao Ru Ren Wu

Shi Yong CANCEL LOADMing Ling Qu Xiao Yi Ge Zheng Zai Zhi Xing De Dao Ru Ren Wu . Bei Qu Xiao De Ren Wu Shu Ju Bu Hui Dao Ru Doris. Yi Jing Chu Yu cancelledHuo finishedZhuang Tai De Ren Wu Wu Fa Bei Qu Xiao .

Shi Li :Qu Xiao Dang Qian Shu Ju Ku Zhong LabelWei "table1_20170707"De Ren Wu

CANCEL LOAD WHERE LABEL = "table1_20170707";

3 Shu Ju De Cha Xun

3.1 Jian Dan Cha Xun

Shi Li :

mysql> select * from table1 limit 3;
+--------+----------+----------+------+
| siteid | citycode | username | pv |
+--------+----------+----------+------+
| 2 | 1 | 'grace' | 2 |
| 5 | 3 | 'helen' | 3 |
| 3 | 2 | 'tom' | 2 |
+--------+----------+----------+------+

3.2 order byCha Xun

Shi Li :

mysql> select * from table1 order by citycode;
+--------+----------+----------+------+
| siteid | citycode | username | pv |
+--------+----------+----------+------+
| 2 | 1 | 'grace' | 2 |
| 1 | 1 | 'jim' | 2 |
| 3 | 2 | 'tom' | 2 |
| 4 | 3 | 'bush' | 3 |
| 5 | 3 | 'helen' | 3 |
+--------+----------+----------+------+
5 rows in set (0.07 sec)

Zhu Yi Shi Xiang : Jian Yu order byDe Te Shu Xing ,order byHou Mian Jian Yi Yi Ding Yao Jia Ru limit,Ru Guo Wei Jia limit,Xi Tong Dang Qian Mo Ren Hui Zi Dong Wei Ni Tian Jia limit 65535.

3.3 Dai You joinDe Cha Xun

Shi Li :

mysql> select sum(table1.pv) from table1 join table2 where table1.siteid = table2.siteid;
+--------------------+
| sum(`table1`.`pv`) |
+--------------------+
| 12 |
+--------------------+
1 row in set (0.20 sec)

3.4 Dai You Zi Cha Xun De Cha Xun

Shi Li :

mysql> select sum(pv) from table2 where siteid in (select siteid from table1 where siteid > 2);
+-----------+
| sum(`pv`) |
+-----------+
| 8 |
+-----------+
1 row in set (0.13 sec)

Gao Ji Shi Yong Zhi Nan

1 Shu Ju Biao De Chuang Jian He Dao Ru Xiang Guan

1.1 Xiu Gai Schema

Shi Yong ALTER TABLEMing Ling Ke Yi Xiu Gai Biao De Schema,Bao Gua Ru Xia Xiu Gai :

* Zeng Jia Lie
* Shan Chu Lie
* Xiu Gai Lie Lei Xing
* Gai Bian Lie Shun Xu

Yi Xia Ju Li Shuo Ming .

Yuan Biao table1De SchemaRu Xia :

+----------+-------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-------+---------+-------+
| siteid | int(11) | Yes | true | 10 | |
| citycode | smallint(6) | Yes | true | N/A | |
| username | varchar(32) | Yes | true | | |
| pv | bigint(20) | Yes | false | 0 | SUM |
+----------+-------------+------+-------+---------+-------+

Wo Men Xin Zeng Yi Lie uv,Lei Xing Wei BIGINT,Ju He Lei Xing Wei SUM,Mo Ren Zhi Wei 0:

ALTER TABLE table1 ADD COLUMN uv BIGINT SUM DEFAULT '0' after pv;

Ti Jiao Cheng Gong Hou ,Ke Yi Tong Guo Yi Xia Ming Ling Cha Kan :

SHOW ALTER TABLE COLUMN;

Dang Zuo Ye Zhuang Tai Wei FINISHED,Ze Biao Shi Zuo Ye Wan Cheng . Xin De Schema Yi Sheng Xiao .

ALTER TABLEWan Cheng Zhi Hou , Ke Yi Tong Guo desc tableCha Kan Zui Xin De schema.

mysql> desc table1;
+----------+-------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-------+---------+-------+
| siteid | int(11) | Yes | true | 10 | |
| citycode | smallint(6) | Yes | true | N/A | |
| username | varchar(32) | Yes | true | | |
| pv | bigint(20) | Yes | false | 0 | SUM |
| uv | bigint(20) | Yes | false | 0 | SUM |
+----------+-------------+------+-------+---------+-------+
5 rows in set (0.00 sec)

Ke Yi Shi Yong Yi Xia Ming Ling Qu Xiao Dang Qian Zheng Zai Zhi Xing De Zuo Ye :

CANCEL ALTER TABLE COLUMN FROM table1;

Zhu Yi Shi Xiang :

Qing Shi Yong HELP ALTER TABLE Cha Kan Geng Duo Xiang Xi Xin Xi .

1.2 Chuang Jian Rollup

RollupKe Yi Li Jie Wei TableDe Yi Ge Wu Hua Suo Yin Jie Gou . Wu Hua Shi Yin Wei Qi Shu Ju Zai Wu Li Shang Du Li Cun Chu ,Er Suo Yin De Yi Si Shi ,RollupKe Yi Diao Zheng Lie Shun Xu Yi Zeng Jia Qian Zhui Suo Yin De Ming Zhong Lu ,Ye Ke Yi Jian Shao keyLie Yi Zeng Jia Shu Ju De Ju He Du .

Yi Xia Ju Li Shuo Ming .

Yuan Biao table1De SchemaRu Xia :

+----------+-------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-------+---------+-------+
| siteid | int(11) | Yes | true | 10 | |
| citycode | smallint(6) | Yes | true | N/A | |
| username | varchar(32) | Yes | true | | |
| pv | bigint(20) | Yes | false | 0 | SUM |
| uv | bigint(20) | Yes | false | 0 | SUM |
+----------+-------------+------+-------+---------+-------+

Dui Yu table1Ming Xi Shu Ju Shi siteid, citycode, usernameSan Zhe Gou Cheng Yi Ge key,Cong Er Dui pvZi Duan Jin Xing Ju He ;Ru Guo Ye Wu Fang Jing Chang You Kan Cheng Shi pvZong Liang De Xu Qiu ,Ke Yi Jian Li Yi Ge Zhi You citycode, pvDe rollup.

ALTER TABLE table1 ADD ROLLUP rollup_city(citycode, pv);

Ti Jiao Cheng Gong Hou ,Ke Yi Tong Guo Yi Xia Ming Ling Cha Kan :

SHOW ALTER TABLE ROLLUP;

Dang Zuo Ye Zhuang Tai Wei FINISHED,Ze Biao Shi Zuo Ye Wan Cheng . RollupJian Li Wan Cheng Zhi Hou Ke Yi Shi Yong desc table1 allCha Kan Biao De rollupXin Xi .

mysql> desc table1 all;
+-------------+----------+-------------+------+-------+--------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+-------------+----------+-------------+------+-------+---------+-------+
| table1 | siteid | int(11) | Yes | true | 10 | |
| | citycode | smallint(6) | Yes | true | N/A | |
| | username | varchar(32) | Yes | true | | |
| | pv | bigint(20) | Yes | false | 0 | SUM |
| | uv | bigint(20) | Yes | false | 0 | SUM |
| | | | | | | |
| rollup_city | citycode | smallint(6) | Yes | true | N/A | |
| | pv | bigint(20) | Yes | false | 0 | SUM |
+-------------+----------+-------------+------+-------+---------+-------+
8 rows in set (0.01 sec)

Ke Yi Shi Yong Yi Xia Ming Ling Qu Xiao Dang Qian Zheng Zai Zhi Xing De Zuo Ye :

CANCEL ALTER TABLE ROLLUP FROM table1;

Zhu Yi Shi Xiang :

2 Shu Ju Biao De Cha Xun

2.1 Nei Cun Xian Zhi

Xian Shi Cha Xun Nei Cun Xian Zhi :

mysql> show variables like "%mem_limit%";
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| exec_mem_limit| 2147483648 |
+---------------+------------+
1 row in set (0.00 sec)

exec_mem_limitDe Dan Wei Shi byte,Ke Yi Tong Guo setMing Ling Gai Bian exec_mem_limitDe Zhi .

set exec_mem_limit = 4294967296;

mysql> show variables like "%mem_limit%";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| exec_mem_limit| 10 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> select * from table1;
ERROR:
Memory limit exceeded

2.2 Cha Xun Chao Shi

Dang Qian Mo Ren Cha Xun Shi Jian She Zhi Wei Zui Chang Wei 300Miao ,Ru Guo Yi Ge Cha Xun Zai 300 Miao Nei Mei You Wan Cheng ,Ze Cha Xun Hui Bei DorisXi Tong cancelDiao . Yong Hu Ke Yi Tong Guo Zhe Ge Can Shu Lai Ding Zhi Zi Ji Ying Yong De Chao Shi Shi Jian ,Shi Xian Lei Si wait(timeout) De Zu Sai Fang Shi .

Cha Kan Dang Qian Chao Shi She Zhi :

mysql> show variables like "%query_timeout%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| QUERY_TIMEOUT | 300 |
+---------------+-------+
1 row in set (0.00 sec)

Xiu Gai Chao Shi Shi Jian Dao 1Fen Zhong :

set query_timeout = 60;

Zhu Yi Shi Xiang :

Dang Qian Chao Shi De Jian Cha Jian Ge Wei 5Miao ,Suo Yi Xiao Yu 5Miao De Chao Shi Bu Hui Tai Zhun Que . Zhe Ge Wei Lai Hui Jiang Jing Du Ti Gao Dao Miao Ji Bie .

2.3 broadcast join He shuffle join

Xi Tong Mo Ren Shi Xian joinDe Fang Shi ,Shi Jiang Xiao Biao Jin Xing Tiao Jian Guo Lu Hou ,Jiang Qi Yan Bo Dao Da Biao Suo Zai De Ge Ge Jie Dian Shang ,Xing Cheng Yi Ge Nei Cun hashBiao ,Ran Hou Liu Shi Du Chu Da Biao De Shu Ju Jin Xing hash join. Dan Shi Ru Guo Dang Xiao Biao Guo Lu Hou De Shu Ju Liang Wu Fa Fang Ru Nei Cun De Hua ,Ci Shi join Jiang Wu Fa Wan Cheng ,Tong Chang De Bao Cuo Ying Gai Shi Shou Xian Zao Cheng Nei Cun Chao Xian .

Ru Guo Yu Dao Shang Shu Qing Kuang ,Jian Yi Shi Yong shuffle joinDe Fang Shi ,Ye Bei Cheng Zuo partitioned join. Ji Jiang Xiao Biao He Da Biao Du An Zhao joinDe keyJin Xing hash,Ran Hou Jin Xing Fen Bu Shi De join. Zhe Ge Dui Nei Cun De Xiao Hao Jiu Hui Fen Tan Dao Ji Qun De Suo You Ji Suan Jie Dian Shang .

Shi Yong broadcast join(Mo Ren ):

mysql> select sum(table1.pv) from table1 join table2 where table1.siteid = 2;
+--------------------+
| sum(`table1`.`pv`) |
+--------------------+
| 10 |
+--------------------+
1 row in set (0.20 sec)

Shi Yong broadcast join(Xian Shi Zhi Ding ):

mysql> select sum(table1.pv) from table1 join [broadcast] table2 where table1.siteid = 2;
+--------------------+
| sum(`table1`.`pv`) |
+--------------------+
| 10 |
+--------------------+
1 row in set (0.20 sec)

Shi Yong shuffle join:

mysql> select sum(table1.pv) from table1 join [shuffle] table2 where table1.siteid = 2;
+--------------------+
| sum(`table1`.`pv`) |
+--------------------+
| 10 |
+--------------------+
1 row in set (0.15 sec)

2.4 failover He load balance

Di Yi Chong

Zi Ji Zai Ying Yong Ceng Dai Ma Jin Xing Zhong Shi He Fu Zai Jun Heng . Bi Ru Fa Xian Yi Ge Lian Jie Gua Diao ,Jiu Zi Dong Zai Qi Ta Lian Jie Shang Jin Xing Zhong Shi . Ying Yong Ceng Dai Ma Zhong Shi Xu Yao Ying Yong Zi Ji Pei Zhi Duo Ge dorisQian Duan Jie Dian Di Zhi .

Di Er Chong

Ru Guo Shi Yong mysql jdbc connector Lai Lian Jie Doris,Ke Yi Shi Yong jdbc De Zi Dong Zhong Shi Ji Zhi :

jdbc:mysql://[host:port],[host:port].../[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

Di San Chong

Ying Yong Ke Yi Lian Jie Dao He Ying Yong Bu Shu Dao Tong Yi Ji Qi Shang De mysql proxy,Tong Guo Pei Zhi mysql proxyDe failoverHe loadbalanceGong Neng Lai Da Dao Mu De .

http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-using.html

Zhu Yi Shi Xiang :

Contents

Clone this wiki locally