MySQL Ver 15.1 不分区表

Question

我正在尝试创建一个表，该表将在服务器中使用 phpmyadmin 按年份范围进行分区:

mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

(在 Centos 7 中使用 mysql -V)

我用来创建的代码是:

CREATE TABLE `datosenfermedades` (
  `clues` varchar(15) NOT NULL,
  `unidadmedica` varchar(75) NOT NULL,
  `fechaconsulta` datetime NOT NULL,
  `idconsulta` int(15) NOT NULL,
  `clavediagnostico` varchar(6) NOT NULL,
  `primeravez` tinyint(1) NOT NULL,
  `clavemunicipio` int(3) NOT NULL,
  `edad` int(3) NOT NULL,
  `sexo` varchar(1) NOT NULL,
  `grupoedad` varchar(20) NOT NULL,
  `derechohabiencia` varchar(30) NOT NULL,
  `servicio` varchar(40) NOT NULL,
  `nivelatencion` int(1) NOT NULL
) 
PARTITION BY RANGE (year(fechaconsulta)) (
  PARTITION p0 VALUES LESS THAN (2008) ENGINE=MyISAM,
  PARTITION p1 VALUES LESS THAN (2009) ENGINE=MyISAM,
  PARTITION p2 VALUES LESS THAN (2010) ENGINE=MyISAM,
  PARTITION p3 VALUES LESS THAN (2011) ENGINE=MyISAM,
  PARTITION p4 VALUES LESS THAN (2012) ENGINE=MyISAM,
  PARTITION p5 VALUES LESS THAN (2013) ENGINE=MyISAM,
  PARTITION p6 VALUES LESS THAN (2014) ENGINE=MyISAM,
  PARTITION p7 VALUES LESS THAN (2015) ENGINE=MyISAM,
  PARTITION p8 VALUES LESS THAN (2016) ENGINE=MyISAM,
  PARTITION p9 VALUES LESS THAN (2017) ENGINE=MyISAM,
  PARTITION p10 VALUES LESS THAN (2018) ENGINE=MyISAM,
  PARTITION p11 VALUES LESS THAN (2019) ENGINE=MyISAM,
  PARTITION p12 VALUES LESS THAN (2020) ENGINE=MyISAM,
  PARTITION p13 VALUES LESS THAN (2021) ENGINE=MyISAM,
  PARTITION p14 VALUES LESS THAN (2022) ENGINE=MyISAM,
  PARTITION p15 VALUES LESS THAN (2023) ENGINE=MyISAM,
  PARTITION p16 VALUES LESS THAN (2024) ENGINE=MyISAM,
  PARTITION p17 VALUES LESS THAN (2025) ENGINE=MyISAM,
  PARTITION p18 VALUES LESS THAN (2026) ENGINE=MyISAM,
  PARTITION p19 VALUES LESS THAN (2027) ENGINE=MyISAM,
  PARTITION p20 VALUES LESS THAN (2028) ENGINE=MyISAM,
  PARTITION p21 VALUES LESS THAN (2029) ENGINE=MyISAM,
  PARTITION p22 VALUES LESS THAN (2030) ENGINE=MyISAM,
  PARTITION p23 VALUES LESS THAN (2031) ENGINE=MyISAM,
  PARTITION p24 VALUES LESS THAN (2032) ENGINE=MyISAM,
  PARTITION p25 VALUES LESS THAN (2033) ENGINE=MyISAM,
  PARTITION p26 VALUES LESS THAN (2034) ENGINE=MyISAM,
  PARTITION p27 VALUES LESS THAN (2035) ENGINE=MyISAM,
  PARTITION p28 VALUES LESS THAN (2036) ENGINE=MyISAM,
  PARTITION p29 VALUES LESS THAN (2037) ENGINE=MyISAM,
  PARTITION p30 VALUES LESS THAN (2038) ENGINE=MyISAM,
  PARTITION p31 VALUES LESS THAN (2039) ENGINE=MyISAM,
  PARTITION p32 VALUES LESS THAN (2040) ENGINE=MyISAM,
  PARTITION p33 VALUES LESS THAN (2041) ENGINE=MyISAM,
  PARTITION p34 VALUES LESS THAN (2042) ENGINE=MyISAM,
  PARTITION p35 VALUES LESS THAN (2043) ENGINE=MyISAM,
  PARTITION p36 VALUES LESS THAN (2044) ENGINE=MyISAM,
  PARTITION p37 VALUES LESS THAN (2045) ENGINE=MyISAM,
  PARTITION p38 VALUES LESS THAN (2046) ENGINE=MyISAM,
  PARTITION p39 VALUES LESS THAN (2047) ENGINE=MyISAM,
  PARTITION p40 VALUES LESS THAN (2048) ENGINE=MyISAM,
  PARTITION p41 VALUES LESS THAN (2049) ENGINE=MyISAM,
  PARTITION p42 VALUES LESS THAN MAXVALUE ENGINE=MyISAM
);

该表显然已正确创建，当我使用突击队SHOW CREATE TABLE datosenfermedades时，我得到:

CREATE TABLE `datosenfermedades` (
     `clues` varchar(15) COLLATE utf8_spanish_ci NOT NULL,
     `unidadmedica` varchar(75) COLLATE utf8_spanish_ci NOT NULL,
     `fechaconsulta` datetime NOT NULL,
     `idconsulta` int(15) NOT NULL,
     `clavediagnostico` varchar(6) COLLATE utf8_spanish_ci NOT NULL,
     `primeravez` tinyint(1) NOT NULL,
     `clavemunicipio` int(3) NOT NULL,
     `edad` int(3) NOT NULL,
     `sexo` varchar(1) COLLATE utf8_spanish_ci NOT NULL,
     `grupoedad` varchar(20) COLLATE utf8_spanish_ci NOT NULL,
     `derechohabiencia` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
     `servicio` varchar(40) COLLATE utf8_spanish_ci NOT NULL,
     `nivelatencion` int(1) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
    /*!50100 PARTITION BY RANGE (year(fechaconsulta))
    (PARTITION p0 VALUES LESS THAN (2008) ENGINE = MyISAM,
    PARTITION p1 VALUES LESS THAN (2009) ENGINE = MyISAM,
    PARTITION p2 VALUES LESS THAN (2010) ENGINE = MyISAM,
    PARTITION p3 VALUES LESS THAN (2011) ENGINE = MyISAM,
    PARTITION p4 VALUES LESS THAN (2012) ENGINE = MyISAM,
    PARTITION p5 VALUES LESS THAN (2013) ENGINE = MyISAM,
    PARTITION p6 VALUES LESS THAN (2014) ENGINE = MyISAM,
    PARTITION p7 VALUES LESS THAN (2015) ENGINE = MyISAM,
    PARTITION p8 VALUES LESS THAN (2016) ENGINE = MyISAM,
    PARTITION p9 VALUES LESS THAN (2017) ENGINE = MyISAM,
    PARTITION p10 VALUES LESS THAN (2018) ENGINE = MyISAM,
    PARTITION p11 VALUES LESS THAN (2019) ENGINE = MyISAM,
    PARTITION p12 VALUES LESS THAN (2020) ENGINE = MyISAM,
    PARTITION p13 VALUES LESS THAN (2021) ENGINE = MyISAM,
    PARTITION p14 VALUES LESS THAN (2022) ENGINE = MyISAM,
    PARTITION p15 VALUES LESS THAN (2023) ENGINE = MyISAM,
    PARTITION p16 VALUES LESS THAN (2024) ENGINE = MyISAM,
    PARTITION p17 VALUES LESS THAN (2025) ENGINE = MyISAM,
    PARTITION p18 VALUES LESS THAN (2026) ENGINE = MyISAM,
    PARTITION p19 VALUES LESS THAN (2027) ENGINE = MyISAM,
    PARTITION p20 VALUES LESS THAN (2028) ENGINE = MyISAM,
    PARTITION p21 VALUES LESS THAN (2029) ENGINE = MyISAM,
    PARTITION p22 VALUES LESS THAN (2030) ENGINE = MyISAM,
    PARTITION p23 VALUES LESS THAN (2031) ENGINE = MyISAM,
    PARTITION p24 VALUES LESS THAN (2032) ENGINE = MyISAM,
    PARTITION p25 VALUES LESS THAN (2033) ENGINE = MyISAM,
    PARTITION p26 VALUES LESS THAN (2034) ENGINE = MyISAM,
    PARTITION p27 VALUES LESS THAN (2035) ENGINE = MyISAM,
    PARTITION p28 VALUES LESS THAN (2036) ENGINE = MyISAM,
    PARTITION p29 VALUES LESS THAN (2037) ENGINE = MyISAM,
    PARTITION p30 VALUES LESS THAN (2038) ENGINE = MyISAM,
    PARTITION p31 VALUES LESS THAN (2039) ENGINE = MyISAM,
    PARTITION p32 VALUES LESS THAN (2040) ENGINE = MyISAM,
    PARTITION p33 VALUES LESS THAN (2041) ENGINE = MyISAM,
    PARTITION p34 VALUES LESS THAN (2042) ENGINE = MyISAM,
    PARTITION p35 VALUES LESS THAN (2043) ENGINE = MyISAM,
    PARTITION p36 VALUES LESS THAN (2044) ENGINE = MyISAM,
    PARTITION p37 VALUES LESS THAN (2045) ENGINE = MyISAM,
    PARTITION p38 VALUES LESS THAN (2046) ENGINE = MyISAM,
    PARTITION p39 VALUES LESS THAN (2047) ENGINE = MyISAM,
    PARTITION p40 VALUES LESS THAN (2048) ENGINE = MyISAM,
    PARTITION p41 VALUES LESS THAN (2049) ENGINE = MyISAM,
    PARTITION p42 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

如果我是正确的 mysql，请评论我的分区说明。毕竟，从 2007 年到 2017 年，我在表中插入了 3,142,157 个值，当我尝试使用命令“SELECT * FROM datosenfermedades PARTITION (p1)”时，我收到下一个错误:

    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(p1)
LIMIT 0, 25' at line 1 

Answer 1

几乎没有理由使用“分区选择”。您应该让引擎来确定要查找哪个分区(或多个分区)以满足查询。

现在，您应该放弃 MyISAM 并转向 InnoDB。

int(3) 是一个 4 字节数字。 (3) 没有任何意义。查找 TINYINT。

该表上没有索引？使用索引来提高性能，而不是分区。

如果您有 INDEX(fechaconsulta)，这将获取相同的行:

SELECT * FROM 
    WHERE fechaconsulta >= '2009-01-01'
      AND fechaconsulta  < '2009-01-01' + INTERVAL 1 YEAR;

无论有没有分区，它都可以高效地完成任务。