Mysql 5.7 melalui partisi oleh

Apa itu partisi MySQL?

Partisi MySQL adalah tentang mengubah – idealnya, mengoptimalkan – cara mesin database menyimpan data secara fisik. Ini memungkinkan Anda untuk mendistribusikan sebagian dari data tabel (mis. k. a. partisi) di seluruh sistem file berdasarkan seperangkat aturan yang ditentukan pengguna (a. k. a. "fungsi partisi"). Dengan cara ini, jika kueri yang Anda lakukan hanya mengakses sebagian kecil dari data tabel dan fungsi partisi diatur dengan benar, pemindaian akan lebih sedikit dan kueri akan lebih cepat

Penting untuk dicatat bahwa mempartisi paling masuk akal saat menangani kumpulan data besar. Jika Anda memiliki kurang dari satu juta baris atau hanya ribuan rekaman, mempartisi tidak akan membuat perbedaan

Horisontal vs. Partisi Vertikal

Partisi horizontal berarti bahwa semua baris yang cocok dengan fungsi partisi akan ditetapkan ke partisi fisik yang berbeda. Partisi vertikal memungkinkan kolom tabel yang berbeda dipecah menjadi partisi fisik yang berbeda. Saat ini, MySQL mendukung partisi horizontal tetapi tidak vertikal. Dokumentasi mesin dengan jelas menyatakan tidak akan mendukung partisi vertikal dalam waktu dekat. ”Saat ini tidak ada rencana untuk memperkenalkan partisi vertikal ke dalam MySQL. ”

Partisi vertikal adalah tentang memisahkan kolom

Partisi horizontal adalah tentang memisahkan baris

Manfaat Utama Partisi

Beberapa keuntungan menggunakan partisi adalah

• Penyimpanan. Dimungkinkan untuk menyimpan lebih banyak data dalam satu tabel daripada yang dapat disimpan di satu disk atau partisi sistem file
• Penghapusan. Menjatuhkan partisi yang tidak berguna hampir seketika, tetapi kueri

  ERROR 1488 (HY000): Duplicate partition name myvertabelopart
  

  5 klasik yang dijalankan dalam tabel yang sangat besar dapat memakan waktu beberapa menit
• Pemangkasan Partisi. Ini adalah kemampuan untuk mengecualikan partisi yang tidak cocok dan datanya dari pencarian; . Juga MySQL5. 7 mendukung pemilihan partisi eksplisit dalam kueri, yang sangat meningkatkan kecepatan pencarian. (Jelas, ini hanya berfungsi jika Anda mengetahui sebelumnya partisi mana yang ingin Anda gunakan. ) Ini juga berlaku untuk pernyataan

  ERROR 1488 (HY000): Duplicate partition name myvertabelopart
  

  5,

  ERROR 1488 (HY000): Duplicate partition name myvertabelopart
  

  7,

  ERROR 1488 (HY000): Duplicate partition name myvertabelopart
  

  8, dan

  ERROR 1488 (HY000): Duplicate partition name myvertabelopart
  

  9 serta

  CREATE TABLE userslogs (
      id INT NOT NULL AUTO_INCREMENT,
      username VARCHAR(20) NOT NULL,
      logdata BLOB NOT NULL,
      created DATETIME NOT NULL,
      PRIMARY KEY(id),
      UNIQUE KEY (username, created)
  )
  PARTITION BY HASH( TO_DAYS(created) )
  PARTITIONS 10;
  

  0 dan

  CREATE TABLE userslogs (
      id INT NOT NULL AUTO_INCREMENT,
      username VARCHAR(20) NOT NULL,
      logdata BLOB NOT NULL,
      created DATETIME NOT NULL,
      PRIMARY KEY(id),
      UNIQUE KEY (username, created)
  )
  PARTITION BY HASH( TO_DAYS(created) )
  PARTITIONS 10;
  

  1

Cara Memeriksa Apakah Mesin Anda Mendukung Partisi

Secara default, binari komunitas menyertakan dukungan partisi. Anda dapat memeriksa apakah itu didukung dalam instans Anda saat ini dengan menjalankan pernyataan

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

| Name       | Status   | Type           | Library | License |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

Alternatif lain adalah dengan mencentang

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Anda harus mendapatkan sesuatu seperti ini

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Kedua output mencatat bahwa plugin yang Anda cari bernama "partisi" dan disetel ke AKTIF. Jika Anda tidak melihat partisi yang terdaftar dengan salah satu dari metode ini, versi MySQL Anda mungkin diinstal tanpa dukungan partisi. Dalam hal ini, kompilasi MySQL lain harus dilakukan dengan opsi –

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

Dasar-dasar Partisi

Sebelum melangkah lebih jauh, penting untuk mencatat beberapa pertimbangan umum terkait dengan partisi

• Fungsi Datetime yang Dioptimalkan. Fungsi , , dan dioptimalkan untuk digunakan dalam partisi. Anda juga dapat menggunakan fungsi tanggal dan waktu yang mengembalikan nilai bilangan bulat atau NULL, seperti , , atau
• Identifikasi Partisi. Partisi selalu diberi nomor secara berurutan, otomatis dimulai dari 0 saat dibuat. Baris disisipkan menggunakan nomor partisi untuk mengidentifikasi ke mana setiap baris pergi. Misalnya, jika Anda mempartisi tabel menjadi empat, maka MySQL akan menggunakan nomor partisi 0, 1, 2, dan 3 untuk mengidentifikasi setiap partisi.

• Konvensi Penamaan. Nama partisi harus mengikuti konvensi penamaan MySQL yang sama dengan yang digunakan untuk tabel dan database. Perlu dicatat bahwa nama partisi tidak peka huruf besar-kecil. Jika Anda mencoba menamai dua partisi untuk tabel yang sama – mis. e. “myVertabeloPart” dan “myvertabelopart” – Anda akan mendapatkan pesan kesalahan ini

  ERROR 1488 (HY000): Duplicate partition name myvertabelopart
  

• Direktori. Partisi dapat ditetapkan ke direktori tertentu selama pembuatan. Anda dapat melakukan ini menggunakan opsi

  CREATE TABLE userslogs (
      id INT NOT NULL AUTO_INCREMENT,
      username VARCHAR(20) NOT NULL,
      logdata BLOB NOT NULL,
      created DATETIME NOT NULL,
      PRIMARY KEY(id),
      UNIQUE KEY (username, created)
  )
  PARTITION BY HASH( TO_DAYS(created) )
  PARTITIONS 10;
  

  _5 dan

  CREATE TABLE userslogs (
      id INT NOT NULL AUTO_INCREMENT,
      username VARCHAR(20) NOT NULL,
      logdata BLOB NOT NULL,
      created DATETIME NOT NULL,
      PRIMARY KEY(id),
      UNIQUE KEY (username, created)
  )
  PARTITION BY HASH( TO_DAYS(created) )
  PARTITIONS 10;
  

  6 untuk klausa

  CREATE TABLE userslogs (
      id INT NOT NULL AUTO_INCREMENT,
      username VARCHAR(20) NOT NULL,
      logdata BLOB NOT NULL,
      created DATETIME NOT NULL,
      PRIMARY KEY(id),
      UNIQUE KEY (username, created)
  )
  PARTITION BY HASH( TO_DAYS(created) )
  PARTITIONS 10;
  

  7 dari pernyataan CREATE TABLE
• Mesin. Partisi dari tabel yang sama harus menggunakan mesin penyimpanan yang sama. Jika Anda mempartisi tabel MyISAM, semua partisi akan menjadi MyISAM;
• Partisi Indeks. Partisi berlaku untuk semua data dan indeks dalam tabel; . Anda juga tidak dapat mempartisi hanya sebagian dari tabel
• Kunci Asing. Tabel InnoDB yang dipartisi tidak mendukung kunci asing. Implikasi integritas data ini sangat penting. Anda tidak dapat menambahkan kunci asing (menunjuk ke tabel lain) di tabel yang dipartisi. Sebaliknya, jika tabel memiliki kunci asing, Anda tidak akan dapat mempartisinya. Juga, tabel yang tidak dipartisi tidak dapat memiliki kolom kunci asing yang menunjuk ke kolom tabel yang dipartisi
• Kolom Partisi. Aturan praktisnya di sini adalah bahwa semua kolom yang digunakan dalam ekspresi partisi harus menjadi bagian dari setiap kunci unik dalam tabel yang dipartisi. Pernyataan yang tampaknya sederhana ini memberlakukan batasan penting tertentu, yang akan kita bahas selanjutnya

Catatan. Dokumentasi MySQL menjelaskan batasan dan batasan partisi

Kolom Partisi Dijelaskan

Seperti yang dinyatakan sebelumnya, semua kolom yang digunakan dalam ekspresi partisi harus ada di setiap kunci unik dalam tabel, termasuk kunci utama (yang menurut definisi UNIK). Dengan kata lain, semua kunci unik dalam tabel harus menggunakan semua kolom dalam ekspresi partisi

Misalnya, ini…

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

.. tidak dapat dipartisi, karena "dibuat" bukan bagian dari setiap kunci unik dalam tabel. Dalam hal ini, kunci utama tidak menyertakan "dibuat", sehingga pernyataan tersebut gagal

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Kami dapat menulis ulang

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

Jika Anda membuat tabel tanpa kunci unik (termasuk kunci utama), kolom apa pun dapat digunakan dalam ekspresi pemartisian yang kompatibel dengan tipe pemartisian

Selanjutnya, saya akan menjelaskan jenis partisi apa yang tersedia di MySQL pada versi 5. 7

Jenis Partisi

Pada dasarnya ada empat jenis partisi yang tersedia.

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Partisi RANGE

Jenis partisi ini menetapkan baris ke partisi berdasarkan nilai kolom yang termasuk dalam rentang yang dinyatakan. Nilai harus bersebelahan, tetapi tidak boleh saling tumpang tindih. Operator

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Seperti contoh di atas, kita bisa mempartisi tabel seperti ini

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB 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

Perhatikan ekspresi

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Seiring berjalannya waktu dan 2017 dimulai, Anda mungkin (bergantung pada kasus penggunaan Anda) merasa berguna untuk membuat partisi baru untuk tabel ini. Anda melakukan ini dengan mengubah definisi partisi untuk menghasilkan partisi baru untuk baris dari 2016 (

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Sehubungan dengan nilai NULL, penting untuk dicatat bahwa baris tersebut akan berada di partisi yang lebih rendah. Dalam contoh sebelumnya, TAHUN dengan NULL akan dievaluasi sebagai

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

• Menghapus Data Lama. Dalam contoh di atas, jika log dari tahun 2013 perlu dihapus, Anda cukup menggunakan

  CREATE TABLE userslogs (
      username VARCHAR(20) NOT NULL,
      logdata BLOB NOT NULL,
      created DATETIME NOT NULL,
      PRIMARY KEY(username, created)
  )
  PARTITION BY HASH( TO_DAYS(created) )
  PARTITIONS 10;
  

  3 untuk menghapus semua baris. Proses ini akan memakan waktu hampir tidak lama, sedangkan menjalankan

  CREATE TABLE userslogs (
      username VARCHAR(20) NOT NULL,
      logdata BLOB NOT NULL,
      created DATETIME NOT NULL,
      PRIMARY KEY(username, created)
  )
  PARTITION BY HASH( TO_DAYS(created) )
  PARTITIONS 10;
  

  4 bisa memakan waktu beberapa menit jika ada banyak baris untuk dihapus
• Data Seri. Bekerja dengan rentang ekspresi data muncul secara alami saat Anda berurusan dengan data tanggal atau waktu (seperti dalam contoh) atau jenis data "seri" lainnya
• Frequent Query pada Kolom Ekspresi Partisi. Jika Anda sering melakukan kueri yang secara langsung melibatkan kolom yang digunakan dalam ekspresi partisi (di mana mesin dapat menentukan partisi mana yang perlu dipindai berdasarkan langsung pada klausa

  CREATE TABLE userslogs (
      username VARCHAR(20) NOT NULL,
      logdata BLOB NOT NULL,
      created DATETIME NOT NULL,
      PRIMARY KEY(username, created)
  )
  PARTITION BY HASH( TO_DAYS(created) )
  PARTITIONS 10;
  

  5),

  CREATE TABLE userslogs (
      id INT NOT NULL AUTO_INCREMENT,
      username VARCHAR(20) NOT NULL,
      logdata BLOB NOT NULL,
      created DATETIME NOT NULL,
      PRIMARY KEY(id),
      UNIQUE KEY (username, created)
  )
  PARTITION BY HASH( TO_DAYS(created) )
  PARTITIONS 10;
  

  9 cukup efisien. Kami akan membahas ini secara lebih rinci di bagian Pemangkasan Partisi

Alternatif untuk

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

CREATE TABLE rc1 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 12),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

Nilai akan dibandingkan dalam tupel;

INSERT INTO rc1 (a,b) VALUES (4,11);
INSERT INTO rc1 (a,b) VALUES (5,11);
INSERT INTO rc1 (a,b) VALUES (6,11);
INSERT INTO rc1 (a,b) VALUES (4,12);
INSERT INTO rc1 (a,b) VALUES (5,12);
INSERT INTO rc1 (a,b) VALUES (6,12);
INSERT INTO rc1 (a,b) VALUES (4,13);
INSERT INTO rc1 (a,b) VALUES (5,13);
INSERT INTO rc1 (a,b) VALUES (6,13);

.. akan ditempatkan dengan cara berikut

Anda dapat memverifikasi ini sendiri menggunakan cuplikan kode ini

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

DAFTAR Partisi

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB 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

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Perhatikan bahwa dalam partisi

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

Perlu dicatat bahwa di sini nilai NULL diperlakukan seperti nilai lainnya. Dalam contoh di atas, jika

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB 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

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB 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

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

CREATE TABLE rc1 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 12),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Mempertahankan banyak kolom dengan banyak nilai biasanya akan merepotkan, jadi kemungkinan besar Anda akan menggunakan

CREATE TABLE rc1 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 12),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Partisi HASH

Dalam

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Untuk

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Catatan. Jika Anda tidak menyertakan kata kunci PARTITIONS, jumlah partisi default adalah "1", dan itulah jumlah yang akan Anda dapatkan

Berikut adalah beberapa rekomendasi tentang penggunaan

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

• INSERT INTO rc1 (a,b) VALUES (4,11);
  INSERT INTO rc1 (a,b) VALUES (5,11);
  INSERT INTO rc1 (a,b) VALUES (6,11);
  INSERT INTO rc1 (a,b) VALUES (4,12);
  INSERT INTO rc1 (a,b) VALUES (5,12);
  INSERT INTO rc1 (a,b) VALUES (6,12);
  INSERT INTO rc1 (a,b) VALUES (4,13);
  INSERT INTO rc1 (a,b) VALUES (5,13);
  INSERT INTO rc1 (a,b) VALUES (6,13);
  

  1 (ekspresi) harus mengembalikan nilai integer non-konstan dan non-acak. (Dengan kata lain, nilai yang dikembalikan harus bervariasi tetapi deterministik. ) Ekspresi

  INSERT INTO rc1 (a,b) VALUES (4,11);
  INSERT INTO rc1 (a,b) VALUES (5,11);
  INSERT INTO rc1 (a,b) VALUES (6,11);
  INSERT INTO rc1 (a,b) VALUES (4,12);
  INSERT INTO rc1 (a,b) VALUES (5,12);
  INSERT INTO rc1 (a,b) VALUES (6,12);
  INSERT INTO rc1 (a,b) VALUES (4,13);
  INSERT INTO rc1 (a,b) VALUES (5,13);
  INSERT INTO rc1 (a,b) VALUES (6,13);
  

  2 atau

  Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
  partitioning function
  

  7 baik digunakan dengan fungsi – e. g. berdasarkan kolom "dibuat" dalam contoh kita
• Penggunaan ekspresi hashing yang melibatkan banyak kolom tidak terlalu disarankan;
• Ekspresi yang disediakan pengguna dievaluasi setiap kali catatan dimasukkan atau diperbarui. Itu juga dapat dievaluasi ketika catatan dihapus (tergantung pada keadaan)
• MySQL menentukan partisi mana yang menyimpan nilai dengan menggunakan rumus N = MOD(expr, num) di mana N adalah nomor partisi yang dihasilkan, expr adalah ekspresi dan num adalah jumlah partisi yang ditentukan dalam kata kunci

  INSERT INTO rc1 (a,b) VALUES (4,11);
  INSERT INTO rc1 (a,b) VALUES (5,11);
  INSERT INTO rc1 (a,b) VALUES (6,11);
  INSERT INTO rc1 (a,b) VALUES (4,12);
  INSERT INTO rc1 (a,b) VALUES (5,12);
  INSERT INTO rc1 (a,b) VALUES (6,12);
  INSERT INTO rc1 (a,b) VALUES (4,13);
  INSERT INTO rc1 (a,b) VALUES (5,13);
  INSERT INTO rc1 (a,b) VALUES (6,13);
  

  4. Alternatif untuk ini adalah

  INSERT INTO rc1 (a,b) VALUES (4,11);
  INSERT INTO rc1 (a,b) VALUES (5,11);
  INSERT INTO rc1 (a,b) VALUES (6,11);
  INSERT INTO rc1 (a,b) VALUES (4,12);
  INSERT INTO rc1 (a,b) VALUES (5,12);
  INSERT INTO rc1 (a,b) VALUES (6,12);
  INSERT INTO rc1 (a,b) VALUES (4,13);
  INSERT INTO rc1 (a,b) VALUES (5,13);
  INSERT INTO rc1 (a,b) VALUES (6,13);
  

  _5

HASH LINEAR

Alih-alih menggunakan modulo yang dijelaskan di atas, ketika MySQL menggunakan

INSERT INTO rc1 (a,b) VALUES (4,11);
INSERT INTO rc1 (a,b) VALUES (5,11);
INSERT INTO rc1 (a,b) VALUES (6,11);
INSERT INTO rc1 (a,b) VALUES (4,12);
INSERT INTO rc1 (a,b) VALUES (5,12);
INSERT INTO rc1 (a,b) VALUES (6,12);
INSERT INTO rc1 (a,b) VALUES (4,13);
INSERT INTO rc1 (a,b) VALUES (5,13);
INSERT INTO rc1 (a,b) VALUES (6,13);

INSERT INTO rc1 (a,b) VALUES (4,11);
INSERT INTO rc1 (a,b) VALUES (5,11);
INSERT INTO rc1 (a,b) VALUES (6,11);
INSERT INTO rc1 (a,b) VALUES (4,12);
INSERT INTO rc1 (a,b) VALUES (5,12);
INSERT INTO rc1 (a,b) VALUES (6,12);
INSERT INTO rc1 (a,b) VALUES (4,13);
INSERT INTO rc1 (a,b) VALUES (5,13);
INSERT INTO rc1 (a,b) VALUES (6,13);

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

INSERT INTO rc1 (a,b) VALUES (4,11);
INSERT INTO rc1 (a,b) VALUES (5,11);
INSERT INTO rc1 (a,b) VALUES (6,11);
INSERT INTO rc1 (a,b) VALUES (4,12);
INSERT INTO rc1 (a,b) VALUES (5,12);
INSERT INTO rc1 (a,b) VALUES (6,12);
INSERT INTO rc1 (a,b) VALUES (4,13);
INSERT INTO rc1 (a,b) VALUES (5,13);
INSERT INTO rc1 (a,b) VALUES (6,13);

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Dianjurkan untuk menggunakan hashing linier ketika berhadapan dengan tabel yang berisi jumlah yang sangat besar (mis. e. terabyte) data. Dalam kasus seperti itu, menambah, menjatuhkan, menggabungkan, dan memisahkan partisi jauh lebih cepat. Namun, ini ada harganya. distribusi data tidak sama dengan hashing biasa. Yang terbaik adalah memilih jumlah partisi yang merupakan kekuatan 2 (2,4,8,16, dll. ), jika tidak, ukuran partisi tengah akan cenderung dua kali ukuran partisi luar. Ini karena distribusi statistik dari hash linier

Partisi KUNCI

Ini sangat mirip dengan partisi HASH, tetapi fungsi hash disediakan oleh MySQL. Partisi

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Di sini

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Seperti yang dinyatakan sebelumnya, kita dapat mendefinisikan beberapa kolom sebagai bagian dari kata kunci KEY. Hal ini ditunjukkan dalam contoh berikut

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

INSERT INTO rc1 (a,b) VALUES (4,11);
INSERT INTO rc1 (a,b) VALUES (5,11);
INSERT INTO rc1 (a,b) VALUES (6,11);
INSERT INTO rc1 (a,b) VALUES (4,12);
INSERT INTO rc1 (a,b) VALUES (5,12);
INSERT INTO rc1 (a,b) VALUES (6,12);
INSERT INTO rc1 (a,b) VALUES (4,13);
INSERT INTO rc1 (a,b) VALUES (5,13);
INSERT INTO rc1 (a,b) VALUES (6,13);

INSERT INTO rc1 (a,b) VALUES (4,11);
INSERT INTO rc1 (a,b) VALUES (5,11);
INSERT INTO rc1 (a,b) VALUES (6,11);
INSERT INTO rc1 (a,b) VALUES (4,12);
INSERT INTO rc1 (a,b) VALUES (5,12);
INSERT INTO rc1 (a,b) VALUES (6,12);
INSERT INTO rc1 (a,b) VALUES (4,13);
INSERT INTO rc1 (a,b) VALUES (5,13);
INSERT INTO rc1 (a,b) VALUES (6,13);

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Pemangkasan Partisi

Konsep di balik pemangkasan partisi sangat sederhana. jangan mencari partisi di mana tidak ada yang bisa ditemukan. Ambil contoh berikut

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Jika kita melakukan pernyataan ini…

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

pengoptimal akan masuk dan meninggalkan partisi yang tidak perlu dari pencarian. Dengan demikian, kueri lebih efisien. Anda dapat melihat ini dengan menjalankan pernyataan

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Keluaran menunjukkan bahwa hanya partisi

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Pengoptimal secara otomatis "memangkas" hanya jika kondisi

CREATE TABLE userslogs (
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

• SELECT
  	PLUGIN_NAME as Name,
  	PLUGIN_VERSION as Version,
  	PLUGIN_STATUS as Status
  	FROM INFORMATION_SCHEMA.PLUGINS
  	WHERE PLUGIN_TYPE='STORAGE ENGINE';
  

  _09

• SELECT
  	PLUGIN_NAME as Name,
  	PLUGIN_VERSION as Version,
  	PLUGIN_STATUS as Status
  	FROM INFORMATION_SCHEMA.PLUGINS
  	WHERE PLUGIN_TYPE='STORAGE ENGINE';
  

  _10

Anda juga dapat memilih partisi secara eksplisit seperti ini

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Ketika nama partisi dihasilkan oleh MySQL (seperti pada partisi

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

Error Code: 1503. A PRIMARY KEY must include all columns in the table's 
partitioning function

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Kasus Penggunaan Khas. Data Deret Waktu

Partisi biasanya digunakan saat menangani rangkaian data waktu yang berisi banyak record. Ini bisa berupa log atau catatan panggilan telepon, faktur, sampel, dll. Sebagian besar waktu, Anda akan membaca data baru, jadi akan ada banyak baris lama yang perlu dihapus setelah "basi"

Untuk mengilustrasikan cara mengatasi masalah ini, saya akan menyajikan skenario kasus pengguna menggunakan 1. 8 juta rekaman dari jaringan stasiun cuaca. Saya akan melakukan beberapa operasi umum pada tabel "pengukuran" apa adanya;

Jutaan Rekor

Kami akan menggunakan tabel berikut

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Selanjutnya, kita akan membuat salinan dari yang pertama, menambahkan

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Tolok Ukur Kueri SELECT

Mari jalankan sepasang kueri umum di kedua tabel dan lihat perbandingannya. (Saya akan menggunakan kata kunci

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Dibutuhkan sekitar setengah detik pada mesin lokal saya untuk mengembalikan hitungan 112.153 baris untuk kedua kueri (dipartisi dan tidak dipartisi). Perhatikan bahwa kedua tabel memiliki indeks pada

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

Jika saya menghapus indeks di kedua tabel, saya dapat melihat dampak apa pun yang ditimbulkan oleh partisi pada

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Kesimpulan. Menjalankan SELECT pada tabel yang tidak dipartisi membutuhkan waktu sekitar dua kali lipat dari kueri yang sama pada tabel yang dipartisi

Tolok Ukur Penghapusan Besar

Saya akan mencoba menghapus data lama secara besar-besaran dan melihat berapa lama waktu yang dibutuhkan. Sebelum saya melakukannya, mari tambahkan indeks kembali pada kolom

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Kami memilikinya seperti aslinya, dengan data yang diindeks

Sekarang mari kita lakukan penghapusan besar-besaran pada tabel yang tidak dipartisi

| Name               | Version | Status |
| partition          | 1.0     | ACTIVE |

Dibutuhkan sekitar 0. 7 detik untuk menghapus 85.314 baris. Bagaimana jika saya menjalankan

SELECT
	PLUGIN_NAME as Name,
	PLUGIN_VERSION as Version,
	PLUGIN_STATUS as Status
	FROM INFORMATION_SCHEMA.PLUGINS
	WHERE PLUGIN_TYPE='STORAGE ENGINE';

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

Itu menjatuhkannya dalam waktu kurang dari 0. 06 detik. Mari coba hapus semua data yang lebih lama dari 2016

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

… dan kemudian menjatuhkan data yang setara dari tabel yang dipartisi

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

Lihatlah output untuk masing-masing

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

ERROR 1488 (HY000): Duplicate partition name myvertabelopart

Seperti yang Anda lihat, proses menghapus data jauh lebih cepat ketika Anda memiliki partisi

Kesimpulan

Dari pengalaman pribadi, mempartisi adalah bagian terakhir dari setiap proses pengoptimalan yang akan saya lakukan. Saya akan melakukannya hanya setelah menghabiskan alternatif lain, seperti mengerjakan ulang kueri yang lambat. Secara umum, mempartisi paling masuk akal saat Anda berurusan dengan jutaan rekaman. Dalam hal ini, menurut saya

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

CREATE TABLE userslogs (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    logdata BLOB NOT NULL,
    created DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY (username, created)
)
PARTITION BY HASH( TO_DAYS(created) )
PARTITIONS 10;

Sebelum Anda mengatur partisi apa pun, ingatlah bahwa partisi tersebut menerapkan beberapa batasan – cara pembuatan kunci unik dan primer;

Apa yang Anda temukan dari bekerja dengan partisi di MySQL?

Apakah MySQL5. 7 dukungan partisi oleh?

Apakah MySQL5. 7 klausul dukungan?

Apakah MySQL5. 6 mendukung partisi?

Apakah MySQL mendukung partisi dengan?