--語句事件記錄表，這些表記錄了語句事件資訊，目前語句事件表events_statements_current、曆史語句事件表events_statements_history和長語句曆史事件表events_statements_history_long、以及聚合後的摘要表summary，其中，summary表還可以根據帳号(account)，主機(host)，程式(program)，線程(thread)，使用者(user)和全局(global)再進行細分)
show tables like '%statement%';
--等待事件記錄表，與語句事件類型的相關記錄表類似：
show tables like '%wait%';
--階段事件記錄表，記錄語句執行的階段事件的表
show tables like '%stage%';
--事務事件記錄表，記錄事務相關的事件的表
show tables like '%transaction%';
--監控檔案系統層調用的表
show tables like '%file%';
--監視記憶體使用的表
show tables like '%memory%';
--動态對performance_schema進行配置的配置表
show tables like '%setup%';
--1、哪類的SQL執行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪類SQL的平均響應時間最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪類SQL排序記錄數最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪類SQL掃描記錄數最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪類SQL使用臨時表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪類SQL傳回結果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪個表實體IO最多？
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
--8、哪個表邏輯IO最多？
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9、哪個索引通路最多？
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
--10、哪個索引從來沒有用過？
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
--11、哪個等待事件消耗時間最多？
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1、剖析某條SQL的執行情況，包括statement資訊，stege資訊，wait資訊
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、檢視每個階段的時間消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、檢視每個階段的鎖等待情況
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;
explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
select_type
主要用來分辨查詢的類型，是普通查詢還是聯合查詢還是子查詢
select_type
Value
Meaning
SIMPLE
Simple SELECT (not using UNION or subqueries)
PRIMARY
Outermost SELECT
UNION
Second or later SELECT statement in a UNION
DEPENDENT UNION
Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT
Result of a UNION.
SUBQUERY
First SELECT in subquery
DEPENDENT SUBQUERY
First SELECT in subquery, dependent on outer query
DERIVED
Derived table
UNCACHEABLE SUBQUERY
A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION
The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
--sample:簡單的查詢，不包含子查詢和union
explain select * from emp;
--primary:查詢中若包含任何複雜的子查詢，最外層查詢則被标記為Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
--union:若第二個select出現在union之後，則被标記為union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;
--dependent union:跟union類似，此處的depentent表示union或union all聯合而成的結果會受外部表影響
explain select * from emp e where e.empno in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)
--union result:從union表擷取結果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;
--subquery:在select或者where清單中包含子查詢
explain select * from emp where sal > (select avg(sal) from emp) ;
--dependent subquery:subquery的子查詢要受到外部表查詢的影響
explain select * from emp e where e.deptno in (select distinct deptno from dept);
--DERIVED: from子句中出現的子查詢，也叫做派生類，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
--UNCACHEABLE SUBQUERY：表示使用子查詢的結果不能被緩存
explain select * from emp where empno = (select empno from emp where [email protected]@sort_buffer_size);
--uncacheable union:表示union的查詢結果不能被緩存：sql語句未驗證
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情況下，得保證查詢至少達到range級别，最好能達到ref
--all:全表掃描，一般情況下出現這樣的sql語句而且資料量比較大的話那麼就需要進行優化。
explain select * from emp;
--index：全索引掃描這個比all的效率要好，主要有兩種情況，一種是目前的查詢時覆寫索引，即我們需要的資料在索引中就可以索取，或者是使用了索引進行排序，這樣就避免資料的重排序
explain select empno from emp;
--range：表示利用索引查詢的時候限制了範圍，在指定範圍内進行查詢，這樣避免了index的全索引掃描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()
explain select * from emp where empno between 7000 and 7500;
--index_subquery：利用索引來關聯子查詢，不再掃描全表
explain select * from emp where emp.job in (select job from t_job);
--unique_subquery:該連接配接類型類似與index_subquery,使用的是唯一索引
explain select * from emp e where e.deptno in (select distinct deptno from dept);
--index_merge：在查詢過程中需要多個索引組合使用，沒有模拟出來
--ref_or_null：對于某個字段即需要關聯條件，也需要null值的情況下，查詢優化器會選擇這種通路方式
explain select * from emp e where e.mgr is null or e.mgr=7369;
--ref：使用了非唯一性索引進行資料的查找
create index idx_3 on emp(deptno);
explain select * from emp e,dept d where e.deptno =d.deptno;
--eq_ref ：使用唯一性索引進行資料查找
explain select * from emp,emp2 where emp.empno = emp2.empno;
--const：這個表至多有一個比對行，
explain select * from emp where empno = 7369;
--system：表隻有一行記錄（等于系統表），這是const類型的特例，平時不會出現
--using filesort:說明mysql無法利用索引進行排序，隻能利用排序算法進行排序，會消耗額外的位置
explain select * from emp order by sal;
--using temporary:建立臨時表來儲存中間結果，查詢完成之後把臨時表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;
--using index:這個表示目前的查詢時覆寫索引的，直接從索引中讀取資料，而不用通路資料表。如果同時出現using where 表名索引被用來執行索引鍵值的查找，如果沒有，表面索引被用來讀取資料，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;
--using where:使用where進行條件過濾
explain select * from t_user where id = 1;
--using join buffer:使用連接配接緩存，情況沒有模拟出來
--impossible where：where語句的結果總是false
explain select * from emp where empno = 7469;
--建立資料表
create table citydemo(city varchar(50) not null);
insert into citydemo(city) select city from city;
--重複執行5次下面的sql語句
insert into citydemo(city) select city from citydemo;
--更新城市表的名稱
update citydemo set city=(select city from city order by rand() limit 1);
--查找最常見的城市清單，發現每個值都出現45-65次，
select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;
--查找最頻繁出現的城市字首，先從3個字首字母開始，發現比原來出現的次數更多，可以分别截取多個字元檢視城市出現的次數
select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
--此時字首的選擇性接近于完整列的選擇性
--還可以通過另外一種方式來計算完整列的選擇性，可以看到當字首長度到達7之後，再增加字首長度，選擇性提升的幅度已經很小了
select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6,
count(distinct left(city,7))/count(*) as sel7,
count(distinct left(city,8))/count(*) as sel8
from citydemo;
--計算完成之後可以建立字首索引
alter table citydemo add key(city(7));
--注意：字首索引是一種能使索引更小更快的有效方法，但是也包含缺點：mysql無法使用字首索引做order by 和 group by。
select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
--通過檢視執行計劃發現type=all,需要進行全表掃描
explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
--優化一、為transaction_id建立唯一索引
create unique index idx_order_transaID on itdragon_order_list (transaction_id);
--當建立索引之後，唯一索引對應的type是const，通過索引一次就可以找到結果，普通索引對應的type是ref，表示非唯一性索引賽秒，找到值還要進行掃描，直到将索引檔案掃描完為止，顯而易見，const的性能要高于ref
explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
--優化二、使用覆寫索引，查詢的結果變成 transaction_id,當extra出現using index,表示使用了覆寫索引
explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";
第二個案例
--建立複合索引
create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
--建立索引之後發現跟沒有建立索引一樣，都是全表掃描，都是檔案排序
explain select * from itdragon_order_list order by order_level,input_date;
--可以使用force index強制指定索引
explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
--其實給訂單排序意義不大，給訂單級别添加索引意義也不大，是以可以先确定order_level的值，然後再給input_date排序
explain select * from itdragon_order_list where order_level=3 order by input_date;
如果需要排序的資料量小于排序緩沖區(show variables like '%sort_buffer_size%';),mysql使用記憶體進行快速排序操作，如果記憶體不夠排序，那麼mysql就會先将樹分塊，對每個獨立的塊使用快速排序進行排序，并将各個塊的排序結果存放再磁盤上，然後将各個排好序的塊進行合并，最後傳回排序結果
set @one :=1
set @min_actor :=(select min(actor_id) from actor)
set @last_week :=current_date-interval 1 week;
2. 自定義變量的限制
1、無法使用查詢緩存
2、不能在使用常量或者辨別符的地方使用自定義變量，例如表名、列名或者limit子句
3、使用者自定義變量的生命周期是在一個連接配接中有效，是以不能用它們來做連接配接間的通信
4、不能顯式地聲明自定義變量地類型
5、mysql優化器在某些場景下可能會将這些變量優化掉，這可能導緻代碼不按預想地方式運作
6、指派符号：=的優先級非常低，是以在使用指派表達式的時候應該明确的使用括号
7、使用未定義變量不會産生任何文法錯誤
3. 自定義變量的使用案例
1. 優化排名語句
1、在給一個變量指派的同時使用這個變量
select actor_id,@rownum:[email protected]+1 as rownum from actor limit 10;
2、查詢擷取演過最多電影的前10名演員，然後根據出演電影次數做一個排名
select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;
2. 避免重新查詢剛剛更新的資料
1、當需要高效的更新一條記錄的時間戳，同時希望查詢目前記錄中存放的時間戳是什麼
update t1 set lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;
update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;
3. 确定取值的順序
在指派和讀取變量的時候可能是在查詢的不同階段
set @rownum:=0;
select actor_id,@rownum:[email protected]+1 as cnt from actor where @rownum<=1;
因為where和select在查詢的不同階段執行，是以看到查詢到兩條記錄，這不符合預期
set @rownum:=0;
select actor_id,@rownum:[email protected]+1 as cnt from actor where @rownum<=1 order by first_name
當引入了orde;r by之後，發現列印出了全部結果，這是因為order by引入了檔案排序，而where條件是在檔案排序操作之前取值的
解決這個問題的關鍵在于讓變量的指派和取值發生在執行查詢的同一階段：
set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:[email protected]+1)<=1;
範圍分區表的分區方式是：每個分區都包含行資料且分區的表達式在給定的範圍内，分區的範圍應該是連續的且不能重疊，可以使用values less than運算符來定義。
1、建立普通的表
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
);
2、建立帶分區的表，下面建表的語句是按照store_id來進行分區的，指定了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 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 (21)
);
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
);
--maxvalue表示始終大于等于最大可能整數值的整數值
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 NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
);
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 RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
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)
);
--timestamp不允許使用任何其他涉及值的表達式
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( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
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)
);
2、基于範圍列的分區，使用date或者datatime列作為分區列
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 no_part_tab
(id INT DEFAULT NULL,
remark VARCHAR(50) DEFAULT NULL,
d_date DATE DEFAULT NULL
)ENGINE=MYISAM;
#分區的表
CREATE TABLE part_tab
(id INT DEFAULT NULL,
remark VARCHAR(50) DEFAULT NULL,
d_date DATE DEFAULT NULL
)ENGINE=MYISAM
PARTITION BY RANGE(YEAR(d_date))(
PARTITION p0 VALUES LESS THAN(1995),
PARTITION p1 VALUES LESS THAN(1996),
PARTITION p2 VALUES LESS THAN(1997),
PARTITION p3 VALUES LESS THAN(1998),
PARTITION p4 VALUES LESS THAN(1999),
PARTITION p5 VALUES LESS THAN(2000),
PARTITION p6 VALUES LESS THAN(2001),
PARTITION p7 VALUES LESS THAN(2002),
PARTITION p8 VALUES LESS THAN(2003),
PARTITION p9 VALUES LESS THAN(2004),
PARTITION p10 VALUES LESS THAN maxvalue);
#插入未分區表記錄
DROP PROCEDURE IF EXISTS no_load_part;
DELIMITER//
CREATE PROCEDURE no_load_part()
BEGIN
DECLARE i INT;
SET i =1;
WHILE i<80001
DO
INSERT INTO no_part_tab VALUES(i,'no',ADDDATE('1995-01-01',(RAND(i)*36520) MOD 3652));
SET i=i+1;
END WHILE;
END//
DELIMITER ;
CALL no_load_part;
#插入分區表記錄
DROP PROCEDURE IF EXISTS load_part;
DELIMITER&&
CREATE PROCEDURE load_part()
BEGIN
DECLARE i INT;
SET i=1;
WHILE i<80001
DO
INSERT INTO part_tab VALUES(i,'partition',ADDDATE('1995-01-01',(RAND(i)*36520) MOD 3652));
SET i=i+1;
END WHILE;
END&&
DELIMITER ;
CALL load_part;
清單分區
類似于按range分區，差別在于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)
);
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 LINEAR HASH(YEAR(hired))
PARTITIONS 4;
