MySQL memahami apa itu jadual partition? Apakah jadual sementara?

Apakah itu jadual partition? Apakah jadual sementara? Artikel berikut akan membawa anda melalui jadual partition dan jadual sementara dalam MySQL Saya harap ia akan membantu anda!

Jadual sementara

Terdapat jadual dalam MySQL yang dipanggil jadual sementara, yang merujuk kepada jadual yang dibuat dengan CREATE TEMPORARY TABLE pernyataan. Ia adalah jenis jadual khas yang membenarkan penyimpanan hasil sementara, yang boleh digunakan semula beberapa kali dalam satu sesi Ia tidak dapat dilihat oleh sambungan lain Apabila sambungan terputus, jadual data akan hilang ia juga boleh digunakan DROP TABLE Alih keluar secara jelas jika anda tidak memerlukannya. [Cadangan berkaitan: tutorial video mysql]

CREATE TEMPORARY TABLE table_name( 
column_1_definition, 
column_2_definition,
....
);

Jika anda ingin mencipta jadual sementara dengan struktur yang sama seperti jadual sedia ada, ia akan menjadi terlalu menyusahkan untuk menggunakan CREATE TEMPORARY TABLE pernyataan. Anda boleh Satu lagi ciri menggunakan pernyataan berikut

CREATE TEMPORARY TABLE temp_table_name SELECT * FROM table_name LIMIT 0;

ialah jadual sementara boleh mempunyai nama yang sama seperti jadual lain Sebagai contoh, walaupun jadual pengguna wujud dalam pangkalan data, milik pengguna jadual sementara juga boleh dibuat dalam pangkalan data.

Buat contoh jadual sementara

Buat jadual sementara baharu bernama tblemployee.

create temporary table tblemployee
(
id int auto_increment Primary key,
emp_name varchar(500),
emp_address varchar(500),
emp_dept_id int
)

Masukkan data ke dalamnya.

mysql> insert into tblemployee values(1,'张三','北京',2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tblemployee;
+----+----------+-------------+-------------+
| id | emp_name | emp_address | emp_dept_id |
+----+----------+-------------+-------------+
|  1 | 张三     | 北京        |           2 |
+----+----------+-------------+-------------+
1 row in set (0.01 sec)

mysql>

Buat berdasarkan struktur jadual sedia ada

Mula-mula buat dua jadual.

create table tb_user(user_name varchar(255),user_id int(11));

insert tb_user values("张三",1);
insert tb_user values("李四",2);
insert tb_user values("王五",3);

create table balance(user_id int(11),balance decimal(5,2));
insert balance values(1,200);
insert balance values(2,150);
insert balance values(3,100);

Buat jadual sementara dengan nama dan baki

create temporary table temp_user_balance select user_name,balance from tb_user left join balance on tb_user.user_id=balance.user_id;

Lihat data dalam jadual sementara.

mysql> select * from temp_user_balance;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |  200.00 |
| 李四      |  150.00 |
| 王五      |  100.00 |
+-----------+---------+
3 rows in set (0.00 sec)

Tetapi apabila sesi lain melihat jadual ini, ralat akan dilaporkan.

mysql> select * from temp_user_balance;
ERROR 1146 (42S02): Table 'test.temp_user_balance' doesn't exist
mysql>

Padam jadual sementara

DROP TEMPORARY TABLE table_name;

Jadual terbahagi

MySQL dalam 5.1 mula menyokong fungsi partitioning merujuk kepada memperuntukkan rekod baris yang berbeza dalam jadual yang sama kepada fail fizikal yang berbeza mengikut peraturan tertentu Setiap kawasan adalah bebas dan boleh diproses secara bebas atau sebagai sebahagian daripada jadual tidak menjejaskan perniagaan.

MySQL hanya menyokong pembahagian mendatar dan tidak menyokong pembahagian menegak memperuntukkan rekod baris berlainan jadual yang sama kepada fail fizikal yang berbeza, manakala pembahagian menegak merujuk kepada memperuntukkan rekod lajur berbeza pada jadual yang sama dalam. fail fizikal yang berbeza.

Anda boleh menggunakan arahan SHOW PLUGINS untuk menyemak sama ada MySQL telah mendayakan fungsi pembahagian.

MySQL menggunakan partition by statement untuk mentakrifkan data yang disimpan dalam setiap partition apabila membuat partition apabila membuat pertanyaan, pengoptimum akan menapis partition yang tidak mempunyai data yang kami perlukan berdasarkan definisi partition, supaya. tidak perlu bertanya Imbas semua partition untuk meningkatkan kecekapan.

Jenis partition

Julat partition

Ia berdasarkan julat selang berterusan, Pengagihan data kepada partition yang berbeza ialah jenis partition yang paling biasa digunakan Mari kita buat jadual partition dengan selang lajur id.

create table user(id int(11),user_name varchar(255))
partition by range(id)(
partition user0 values less than (100),
partition user1 values less than (200));

Selepas mencipta jadual ini, jadual tidak lagi terdiri daripada ibd, tetapi ibd setiap partition apabila ia dibuat Anda boleh menyemak lokasi direktori data terlebih dahulu melalui pernyataan berikut, dan kemudian semak ibd yang dibuat selepas dipisahkan.

show global variables like "%datadir%"
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

root@hxl-PC:/var/lib/mysql/test# ls
user#p#user0.ibd  user#p#user1.ibd
root@hxl-PC:/var/lib/mysql/test#

Kemudian kami memasukkan 3 keping data ke dalamnya, tetapi kami dapat melihat bahawa bahagian ketiga dengan id 250 melaporkan ralat Alasannya ialah kami ingin memasukkan nilai yang tidak ditakrifkan dalam partition MySQL membuang pengecualian.

mysql> insert user values(50,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(150,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(250,"张三");
ERROR 1526 (HY000): Table has no partition for value 250
mysql>

Penyelesaian ialah menambah partition dengan nilai MAXXXVALUE, supaya semua nilai lebih daripada 200 disimpan di dalamnya. Kini anda boleh memasukkan nilai lebih daripada 200.

alter table user add partition (partition user3 values less than maxvalue);

mysql> insert user values(250,"张三");
Query OK, 1 row affected (0.02 sec)

Anda boleh menanyakan jadual PARTITIONS melalui pernyataan berikut untuk mendapatkan maklumat khusus setiap partition.

select * from information_schema.partitions where table_schema=database() and table_name='user'\G;

Memandangkan kini terdapat tiga partition, akan ada tiga baris TABLE_ROWS dalam setiap baris menunjukkan bilangan storan, jadi semuanya 1 sekarang dan PARTITION_METHOD menunjukkan jenis partition.

.....
*************************** 1. row ***************************

             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: `id`
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 100
                   TABLE_ROWS: 1
.....

Anda juga boleh menggunakan explain untuk melihat partition yang digunakan dalam pertanyaan.

LIST partition

LIST partition adalah serupa dengan RANGE, kecuali nilai lajur partition hanya boleh menyimpan nilai tertentu, iaitu himpunan nilai dalam senarai enumerasi. RANGE ialah satu set nilai selang berterusan

create table user (id int(11)) 
partition by list(id)(
partition user0 values in(1,3,5,7,9),
partition user1 values in(0,2,4,6,8)
);

Juga masukkan beberapa data Anda boleh melihat bahawa pengecualian dilemparkan apabila memasukkan 10. Sebabnya ialah data yang dimasukkan tidak lagi dalam partition. takrifan.

mysql> insert user values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(2);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(6);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(9);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(10);
ERROR 1526 (HY000): Table has no partition for value 10
mysql>

Baki 1, 2, 6 dan 9 ialah dua dalam setiap partition user0 dan user1.

Pembahagian HASH

Tujuan HASH adalah untuk mengagihkan data secara sama rata ke dalam setiap partition yang ditentukan untuk memastikan bahawa jumlah data dalam setiap partition adalah lebih kurang sama Tidak perlu menentukan partition yang mana nilai tertentu harus disimpan seperti RANGE dan HASH partitioning dilakukan secara automatik.

create table user (id int(11)) partition by hash(id) partitions 4;

Kemudian bagaimana anda tahu partition mana data ini disimpan. Ambil 500 sebagai contoh, ia diperoleh melalui mod (500, bilangan partition), jadi 500 berada dalam partition pertama.

mysql> select mod(500,4)
    -> ;
+------------+
| mod(500,4) |
+------------+
|          0 |
+------------+

Sebagai contoh, 31, kemudian mod(31,4) ialah 3, jadi dalam partition ke-4, jika anda memasukkan dua nombor ini dan melihatnya melalui information_schema.partitions, maka 1, 4 TABLE_ROWS daripada partition semuanya 1.

通过条件查找数据时，使用到的分区也不一样，比如查找相等的数，那么首先计算这个值应该在哪个分区，然后在进行查找，如果使用<、>来范围查找，则会使用所有分区。

还有HASH可以使用一些函数或其他有效表达式，比如创建时可以使用partition by hash(abs(id))，但并不是所有函数都可以使用，可使用的函数可以参考官网 ；

KEY分区

Key分区和HASH类似，不同的是， HASH 分区允许使用用户自定义的表达式，KEY 分区不允许使用用户自定义的表达式，需要使用 HASH 函数

KEY分区允许多列，而HASH分区只允许一列，另外在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

create table user(id int(11)) partition by key(id) partitions 4;

但是我并没有找到关于详细分区介绍的算法，而是看到一些说通过PASSWORD的运算，没有搞懂。

Columns分区

Columns分区是5.5引入的分区类型，在此之前，RANGE分区和LIST分区只能支持整数分区，从而需要额外的函数来计算，Columns分区解决了这个问题。

Columns分区可以细分为RANGE Columns 和LIST Columns分区，支持的类型如下：

• TINYINT、 SMALLINT、 MEDIUMINT、 INT ( INTEGER) 和 BIGINT，但是不支持DECIMAL或 FLOAT。

• DATE和 DATETIME。

• CHAR， VARCHAR， BINARY，和 VARBINARY，TEXT和 BLOB列不支持。

create table user (
    a int,
    b int
)
partition by range columns(a, b) (
    partition p0 values less than (5, 12),
    partition p1 values less than (maxvalue, maxvalue)
);

现在插入一些数据

insert into user (a,b) values (4,11);
insert into user (a,b) values (6,13);

第一条由于(4,11) < (5,12) 所以在p0分区，而(6,13) < (5,12) ，超出预期，在p1分区。

子分区

子分区也称为复合分区，可以对分区表RANGE和LIST上分区再进分区。

create table user (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
);

对NULL的处理

MySQL可以在分区键上使用NULL，会把他当做最小分区来处理，也就是会存放到第一个分区，但是在List分区中，NULL值必须定义在列表中，否则不能被插入。

更多编程相关知识，请访问：编程视频！！

Atas ialah kandungan terperinci MySQL memahami apa itu jadual partition? Apakah jadual sementara?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!