Chapter 13
Index and Partition
Index
MYSQL မှာ data တွေဟာ သန်း ချီ ပြီး ရှိနိုင်ပါတယ်။ 100 Millions records မှာ ဆိုရင် ရှာဖွေ ရတာ ကြာမြင့်နိုင်ပါတယ်။ Data တွေများသည့် အခါမှာ မြန်မြန် ရှာဖွေနိုင်အောင် Indexing ကို အသုံးပြုနိုင်ပါတယ်။
Indexing ဆိုတာ စာအုပ် တစ်အုပ်မှာ ပါသည့် မာတိကာ သဘောမျိုးမှာ။ ဥပမာ Chapater 4 ဟာ စာမျက်နှာ ၄၀ မှာ ရှိတယ် ဆိုရင် Chapter 4 ကို ဖတ်ဖို့ အတွက် စာမျက်နှာ် ၄၀ ကို တန်းပြီးသွားရုံပါပဲ။ တစ်ရွက်ခြင်းဆီ လှန်ပြီး သွားနေဖို့ မလိုပါဘူး။
ပုံမှန် အားဖြင့် primary key မှာ Indexing ပါဝင်ပြီးသား ဖြစ်ပါတယ်။ တခြား columns တွေကို ထည့်ချင်သည့် အခါမှာတော့ Indexing ကို ထည့်နိုင်ပါတယ်။
Index ကို ပထမဆုံး database create လုပ်သည့် အချိန်မှာ ထည့်သွင်းနိုင်ပါတယ်။
create table newstudents (
id int auto_increment primary key,
name varchar(255) not null,
join_date DATE,
bio Text,
room_id int,
created_at timestamp default current_timestamp,
INDEX (name));
အခု code မှာ ဆိုရင် name ကို index လုပ်ထားမယ် လို့ ဆိုပါတယ်။ အကယ်၍ query လုပ်သည့် အခါမှာ name နဲ့ room_id ကို ပေါင်းရှာတတ်တယ် ဆိုရင်တော့ INDEX (name,room_id) ဆိုပြီး column ၂ ခု ပေါင်းပြီး index ထောက်သင့်ပါတယ်။
SHOW INDEX from newstudents;
လက်ရှိ newstudents မှာ ထောက်ထားသည့် index တွေကို ကြည့်တာပါ။
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| newstudents | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| newstudents | 1 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
PRIMARY key id နဲ့ name ကို ထောက်ထားတာကို တွေ့နိုင်ပါတယ်။
အခု ရှိပြီးသား students table ကို index ထည့်ပါမယ်။
CREATE INDEX name_index ON students(name);
အခု students table ထဲကို name_index ဆိုသည့် နာမည် နဲ့ name ကို index ထောက်ပြီးပါပြီ။
အကယ်၍ ကျွန်တော်တို့ဟာ name နဲ့ dep_code တွဲပြီး query ရှာမယ် ဆိုရင် ၂ ခု တွဲပြီး index ထောက်သင့်ပါတယ်။
SELECT * FROM students WHERE name = 'Moe Moe' and dep_code = 'CS_102';
အခု name နဲ့ dep_code ပေါင်းပြီး index ထောက်ပါမယ်။
CREATE INDEX name_dep ON students(name,dep_code);
အခု ပြန်ပြီး index ကို ကြည့်ရအောင်။
SHOW INDEX from students;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | student_id | A | 9 | NULL | NULL | | BTREE | | |
| students | 1 | dep_code | 1 | dep_code | A | 9 | NULL | NULL | YES | BTREE | | |
| students | 1 | name_index | 1 | name | A | 9 | NULL | NULL | | BTREE | | |
| students | 1 | name_dep | 1 | name | A | 9 | NULL | NULL | | BTREE | | |
| students | 1 | name_dep | 2 | dep_code | A | 9 | NULL | NULL | YES | BTREE | | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
name_dep နဲ့ name နဲ့ dep_code ကို index ထောက်ထားတာ ကြည့်နိုင်ပါတယ်။
Index ထည့်ခြင်းဟာ SELECT query ကို ပိုမြန်နိုင်သော်လည်း INSERT ထည့်သည့် အခါမှတော့ ပိုနှေးစေပါတယ်။
Partition
MySQL မှာ နောက်ထပ် query တွေကို မြန်စေသည့် နည်းလမ်းကတော့ Partition ထည့်နိုင်ပါတယ်။ Partiion ကို မှန်ကန်စွာ ပိုင်းထားခဲ့လျှင် INSERT , DELETE တွေကို မြန်ဆန်စေပါတယ်။
SHOW PLUGINS;
အဲဒီ query ကို run လိုက်ရင်
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
ဆိုတာကို တွေ့ရပါလိမ့်မယ်။
အဲဒါပါခဲ့ရင်တော့ partition ကို အသုံးပြုနိုင်ပါတယ်။
Partition Type
RANGE Partitioning
RANGE ကိုတော့ VALUES LESS THAN နဲ့ အသုံးပြုပါတယ်။ ဥပမာ 2010 ထက် ငယ်တာတွေကို partition တစ်ခု 2011 ထက် ငယ်တာကို partition တစ်ခု ဆိုပြီး ခွဲထုတ် နိုင်ပါတယ်။
CREATE TABLE userslogs (
username VARCHAR(20) NOT NULL,
logdata TEXT NOT NULL,
created DATETIME NOT NULL,
PRIMARY KEY(username, created)
)
PARTITION BY RANGE( YEAR(created) )(
PARTITION from_2013_or_less VALUES LESS THAN (2014),
PARTITION from_2014 VALUES LESS THAN (2015),
PARTITION from_2015 VALUES LESS THAN (2016),
PARTITION from_2016_and_up VALUES LESS THAN MAXVALUE
);
Primary Key ကို username နဲ့ created ကို ထားထားပါတယ်။ Partition ခွဲသည့် အခါမှာ primary key နဲ့ ပဲ ခွဲလို့ ရပါမယ်။ ဒါကြောင့် Primary key ကို ကြေငြာသည့် အခါမှာ PRIMARY KEY(username, created) ကို သုံးထားပါတယ်။
PARTITION BY RANGE( YEAR(created) ) ဆိုတာကတော့ created မှာ ပါသည့် date ၏ YEAR အလိုက် ခွဲမယ်လို့ ဆိုတာပါ။ 2014 အောက်ကို from_2013_or_less partition ထဲမှာ ထားပြီးတော့ 2015 အောက်ကို from_2014 မှာ သိမ်းမယ် ဆိုပြီး နေရာတွေ ခွဲသိမ်းထားတာပါ။
လက်ရှိ table မှာ partition ထည့်မယ်ဆိုရင်တော့ ALTER TABLE နဲ့ အသုံးပြုနိုင်ပါတယ်။
ALTER TABLE userslogs
PARTITION BY RANGE( YEAR(created) )(
PARTITION from_2013_or_less VALUES LESS THAN (2014),
PARTITION from_2014 VALUES LESS THAN (2015),
PARTITION from_2015 VALUES LESS THAN (2016),
PARTITION from_2016_and_up VALUES LESS THAN MAXVALUE
);
ပိုပြီး နားလည် လွယ်ကူအောင် အောက်ကလို table တစ်ခု ဖန်တီးရအောင်။
CREATE TABLE sample (
a INT,
b INT,
PRIMARY KEY (a)
)
PARTITION BY RANGE COLUMNS(a) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
အခု sample ထဲကို data ကို ထည့်ကြည့်ရအောင်။
INSERT INTO sample(a,b) VALUES
(1,10),
(2,10),
(3,10),
(10,10),
(11,10),
(12,10),
(20,10),
(21,10),
(25,10)
;
+----+------+
| a | b |
+----+------+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 10 | 10 |
| 11 | 10 |
| 12 | 10 |
| 20 | 10 |
| 21 | 10 |
| 25 | 10 |
+----+------+
အခု ကျွန်တော်တို့ partition ထဲက data ကို ဆွဲထုတ်ကြည့်ရအောင်။
SELECT * FROM sample PARTITION (p0);
+---+------+
| a | b |
+---+------+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
+---+------+
partition p0 ထဲမှာ LESS THAN 10 ဖြစ်သည့် အတွက် 1,2,3 ပဲ ရှိတာကို တွေ့နိုင်ပါတယ်။
SELECT * FROM sample PARTITION (p1);
+----+------+
| a | b |
+----+------+
| 10 | 10 |
| 11 | 10 |
| 12 | 10 |
+----+------+
p1 မှာတော့ 10,11,12 ရှိတာကို တွေ့နိုင်ပါတယ်။ Query နဲ့ ရှာသည့် အခါမှာ ကိုယ့်ရဲ့ data ပေါ်မှာ မူတည်ပြီး သက်ဆိုင်ရာ partition မှာ ရှာဖွေခြင်းဟာ ပိုမို မြန်ဆန် စေပါတယ်။
ဥပမာ
SELECT * FROM sample PARTITION (p2) WHERE a = 20;
ဆိုရင် sample table ရဲ့ partition p2 က a 20 ဖြစ်တာကို သွားရှာပေးမှာ ဖြစ်ပါတယ်။ table တစ်ခု လုံးမှာ ရှာမည့် အစား partition အပိုင်းမှာပဲ ရှာသည့် အတွက်ကြောင့် ပို ပြီးတော့ မြန်ဆန်စေပါတယ်။
LIST Partitioning
LIST partition ကတော့ RANGE လိုမျိုးပါပဲ။ ကွာခြားချက်ကတော့ COLUMN ထဲမှာ ပါသည့် value စာရင်း နဲ့ ကွာခြားပါတယ်။ VALUES IN နဲ့ အသုံးပြုပါတယ်။
CREATE TABLE userslogsList (
username VARCHAR(20) NOT NULL,
logdata TEXT NOT NULL,
created DATETIME NOT NULL,
PRIMARY KEY(username, created)
)
PARTITION BY LIST( YEAR(created) )(
PARTITION p0 VALUES IN (2014,2015,2016),
PARTITION p1 VALUES IN (2017,2018,2019),
PARTITION p2 VALUES IN (2020,2021,2022)
);
LIST partition မှာ သတိပြုရမှာ က MAXVALUE အသုံးပြုလို့မရပါဘူး။ ဖြစ်နိုင်သမျှ အကုန်
HASH Partitioning
HASH ကတော့ Data value ကို Hash လုပ်ပြီး သိမ်းသည့် အပိုင်းပါ။ Partition ကို အလိုအလျောက် ပိုင်ပေးပါတယ်။ Partition name ကို ကြေငြာနေဖို့ မလိုပါဘူး။ ဒါပေမယ့် Partition ဘယ် ၂ ခု သုံးမလဲ ဆိုတာကို သတ်မှတ်ပေးရပါတယ်။
CREATE TABLE serverlogs2 (
serverid INT NOT NULL,
logdata BLOB NOT NULL,
created DATETIME NOT NULL
)
PARTITION BY HASH (serverid)
PARTITIONS 10;
Hash ကို N = MOD(expr, num) ပုံစံ နဲ့ သိမ်းပါတယ်။ N ဆိုတာကတော့ partiton name ပါ။ expr ကတော့ expression ပါ။ Hash လုပ်ပြီး ထွက်လာမယ့် နံပတ်ပါ။ num ကတော့ number of partition ပါ။
LINEAR HASH Partitioning
Modulo နဲ့ မသုံးချင်ရင်တော့ LINEAR HASH ကို အသုံးပြုနိုင်ပါတယ်။ LINEAR HASH ကိုတော့ data တွေ အရမ်းများလာသည့် အခါမှာ အသုံးပြုသင့်ပါတယ်။
CREATE TABLE serverlogs2 (
serverid INT NOT NULL,
logdata BLOB NOT NULL,
created DATETIME NOT NULL
)
PARTITION BY LINEAR HASH (serverid)
PARTITIONS 10;
HAHS နဲ့ အတူတူပါပဲ။ ကွာသွားတာကတော့ ရှေ့မှာ LINEAR ပါသွားတာပဲ ရှိပါတယ်။
KEY Partitioning
KEY Partition က Hash partition လိုမျိုးပါပဲ။ ဒါပေမယ့် KEY ကို အသုံးပြုရင်တော့ UNIQUE value တစ်ခု ရှိရပါမယ်။
CREATE TABLE serverlogs4 (
serverid INT NOT NULL,
logdata BLOB NOT NULL,
created DATETIME NOT NULL,
UNIQUE KEY (serverid)
)
PARTITION BY KEY()
PARTITIONS 10;
KEY ကို အခြား COLUMN တွေနဲ့လည်း တွဲသုံးပြီး partition လုပ်နိုင်ပါတယ်။
CREATE TABLE serverlogs5 (
serverid INT NOT NULL,
logdata BLOB NOT NULL,
created DATETIME NOT NULL,
label VARCHAR(10) NOT NULL
)
PARTITION BY KEY(serverid, label, created)
PARTITIONS 10;
KEY Partition မှာလည်း LINEAR ကို အသုံးပြုနိုင်ပါတယ်။
CREATE TABLE serverlogs6 (
serverid INT NOT NULL,
logdata BLOB NOT NULL,
created DATETIME NOT NULL
)
PARTITION BY LINEAR KEY(serverid)
PARTITIONS 10;
Explain Partition
ကျွန်တော်တို့ query ကို ဆွဲထုတ်လိုက်သည့် အခါမှာ ဘယ် partition ကနေ အလုပ်လုပ်သွားတယ် ဆိုတာကို ပြန်ကြည့်နိုင်ပါတယ်။
SELECT * FROM sample WHERE a = 20;
ကို ဘယ် partition က အလုပ်လုပ်သွားလဲ လို့ သိချင်တယ် ဆိုရင် အောက်က အတိုင်း စစ်နိုင်ပါတယ်။
EXPLAIN PARTITIONS SELECT * FROM sample WHERE a = 20;
+------+-------------+--------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | sample | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+--------+------------+-------+---------------+---------+---------+-------+------+-------+
partitions မှာ p2 လို့ ရေးထားတာ တွေ့နိုင်ပါတယ်။ a = 20 ကို partition p2 ကနေ ရှာပြီး ထုတ်သွားတာကို တွေ့ရမှာပါ။