mysql - 是我表设计错误还是建表时选择的索引错误？

Question

我构建了Web应用程序作为消除人民表中不必要的数据的工具，该应用程序主要是过滤有效获得选举权的人民的所有数据。起初，当主表仍然有几行时，这不是问题，但是当表填满大约 200K 行时，情况真的很糟糕(6 秒)(更糟糕的是，因为表将达到 600 万行) .

我的表设计如下，我正在与 4 个表进行连接(区域表从省、市、区和镇开始)。每个区域表都通过自己的 id 相互关联:

CREATE TABLE `peoples` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `id_prov` smallint(2) NOT NULL,
    `id_city` smallint(2) NOT NULL,
    `id_district` smallint(2) NOT NULL,
    `id_town` smallint(4) NOT NULL,
    `tps` smallint(4) NOT NULL,
    `urut_xls` varchar(20) NOT NULL,
    `nik` varchar(20) NOT NULL,
    `name` varchar(60) NOT NULL,
    `place_of_birth` varchar(60) NOT NULL,
    `birth_date` varchar(30) NOT NULL,
    `age` tinyint(3) NOT NULL DEFAULT '0',
    `sex` varchar(20) NOT NULL,
    `marital_s` varchar(20) NOT NULL,
    `address` varchar(160) NOT NULL,
    `note` varchar(60) NOT NULL,
    `m_name` tinyint(1) NOT NULL DEFAULT '0',
    `m_birthdate` tinyint(1) NOT NULL DEFAULT '0' ,
    `format_birthdate` tinyint(1) NOT NULL DEFAULT '0' ,
    `m_sex` tinyint(1) NOT NULL DEFAULT '0' COMMENT ,
    `m_m_status` tinyint(1) NOT NULL DEFAULT '0' ,
    `sex_double` tinyint(1) NOT NULL DEFAULT '0',
    `id_import` bigint(10) NOT NULL,
    `id_workspace` tinyint(4) unsigned NOT NULL DEFAULT '0',
    `stat_valid` smallint(1) NOT NULL DEFAULT '0' ,
    `add_manual` tinyint(1) unsigned NOT NULL DEFAULT '0' ,
    `insert_by` varchar(12) NOT NULL,
    `update_by` varchar(12) DEFAULT NULL,
    `mark_as_duplicate` smallint(1) NOT NULL DEFAULT '0' ,
    `mark_as_trash` smallint(1) NOT NULL DEFAULT '0' ,
    `in_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ind_import` (`id_import`),
    KEY `ind_duplicate` (`mark_as_duplicate`),
    KEY `id_workspace` (`id_workspace`),
    KEY `tambah_manual` (`tambah_manual`),
    KEY `il` (`stat_valid`,`mark_as_trash`,`in_date_time`),
    KEY `region` (`id_prov`,`id_kab`,`id_kec`,`id_kel`,`tps`),
    KEY `name` (`name`),
    KEY `place_of_birth` (`place_of_birth`),
    KEY `ind_birth` (`birthdate`(10)),
    KEY `ind_sex` (`sex`(2))
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

城镇:

CREATE TABLE `town` (
    `id` smallint(4) NOT NULL,
    `id_district` smallint(2) NOT NULL,
    `id_city` smallint(2) NOT NULL,
    `id_prov` smallint(2) NOT NULL,
    `name_town` varchar(60) NOT NULL,
    `handprint` blob,
    `pps_1` varchar(60) DEFAULT NULL,
    `pps_2` varchar(60) DEFAULT NULL,
    `pps_3` varchar(60) DEFAULT NULL,
    `tpscount` smallint(2) DEFAULT NULL,
    `pps_4` varchar(60) DEFAULT NULL,
    `pps_5` varchar(60) DEFAULT NULL,
    PRIMARY KEY (`id_prov`,`id_kab`,`id_kec`,`id`),
    KEY `name_town` (`name_town`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

和查询类似

SELECT `E`.`id`, `E`.`id_prov`, `E`.`id_city`, `E`.`id_district`, `E`.`id_town`, 
  `B`.`name_prov`,`C`.`name_city`,`D`.`name_district`, `A`.`name_town`,
  `E`.`tps`, `E`.`urut_xls`, `E`.`nik`,`E`.`name`,`E`.`place_of_birth`,
  `E`.`birth_date`, `E`.age, `E`.`sex`,   `E`.`marital_s`, `E`.`address`,
  `E`.`note` 
FROM peoples E
JOIN test_prov B ON  E.id_prov = B.id
JOIN test_city C ON E.id_city = C.id 
    AND (C.id_prov=B.id)
JOIN test_district D ON E.id_district = D.id 
    AND ((D.id_city = C.id) AND (D.id_prov= B.id))
JOIN test_town A ON E.id_town = A.id 
    AND ((A.id_district = D.id) 
    AND (A.id_city = C.id) 
    AND (A.id_prov = B.id)) 
    AND E.stat_valid=1 
    AND E.mark_as_trash=0

mark_as_trash是一个标记列，只包含1和0，只是为了知道数据是否已被标记为删除记录，stat_valid是过滤后的结果值 - 如果值为1则数据有效，可以获取权限选举。

我尝试查看解释，但没有列用作索引查找。我相信这就是应用程序在 200K 行中如此缓慢的问题。上面的查询仅显示两个条件，但该应用程序具有按姓名、出生地、出生日期、带范围的年龄等进行过滤的功能。

我怎样才能让它表现得更好？

Answer 1

一个城市可以同时属于两个省吗？如果不是，那么如果 E.id_city = C.id 应该只给你一行，你为什么要检查 C.id_prov=B.id 呢？

此外，您的查询似乎很慢，因为您选择了 200k 行。索引会提高性能，但您真的需要同时处理所有行吗？您应该使用分页(限制、偏移)。