按列值对 MySQL 表进行分区

2022-01-05 00:00:00 sql partitioning database mysql phpmyadmin

我有一个包含 2000 万行的 MySQL 表.我想分区以提高速度.表格格式如下:

I have a MySQL table with 20 million rows. I want to partition to boost speed. The table is in the following format:

column    column   column   sector

data      data     data     Capital Goods
data      data     data     Transportation
data      data     data     Technology
data      data     data     Technology
data      data     data     Capital Goods
data      data     data     Finance
data      data     data     Finance

我使用以下代码应用了分区:

I have applied partitions using the following code:

ALTER TABLE technical
PARTITION BY LIST COLUMNS (sector)
(
PARTITION P1 VALUES IN ('Capital Goods'),
PARTITION P2 VALUES IN ('Health Care'),
PARTITION P3 VALUES IN ('Transportation'),
PARTITION P4 VALUES IN ('Finance'),
PARTITION P5 VALUES IN ('Technology'),
PARTITION P6 VALUES IN ('Consumer Services'),
PARTITION P7 VALUES IN ('Energy'),
PARTITION P8 VALUES IN ('Healthcare'),
PARTITION P9 VALUES IN ('Consumer Non-Durables'),
PARTITION P10 VALUES IN ('Consumer Durables')
);

到目前为止一切正常，但是当我通过 phpMyAdmin 查看表格时，它显示:

Its all fine so far, but when I look at the table through phpMyAdmin it displays this:

sector怎么会小于资本品?

我做错了什么?

推荐答案

问题只是 phpMyAdmin 以不寻常的方式重新发送数据.数据库现在按预期工作，查询如下:

The problem is simply phpMyAdmin reresenting the data in an unusual way. The database is now working as expected and queries such as:

SELECT * WHERE ... AND sector = "Energy"

运行速度更快.

感谢 Drew、Uueerdo、Rick James 和其他所有人帮助我理解这个问题.

Thanks to Drew, Uueerdo, Rick James and everybody else for helping me understand this problem.

相关文章