MySQL5.7上的多重自连接--性能差

Question

我有一个包含大量地理名称的表，为了获得交叉引用的信息，我正在使用self查询它。

唯一的问题是，执行查询需要7/10秒(并锁定表)，这表示应用程序的瓶颈。

我检查了其他的答案，但我无法为我找到解决办法。我想我创造了所有能帮助我的索引。我不是一个MySql的专家，所以如果你需要更多的信息，以更好的理解，请随便问。

这是一个查询：

select  gp.*,
                        gp1.description as desc_adm1,
                        gp2.description as desc_adm2,
                        gp3.description as desc_adm3,
                        gp4.description as desc_adm4
                 from
                 geonames_table gp left join
                 geonames_table gp1 on gp1.country = gp.country
                                        and gp1.geonames_code = 'ADM1'
                                        and gp1.adm1 = gp.adm1
                                        left join
                 geonames_table gp2 on gp2.country = gp.country
                                        and gp2.geonames_code = 'ADM2'
                                        and gp2.adm2 = gp.adm2
                                        left join
                 geonames_table gp3 on gp3.country = gp.country
                                        and gp3.geonames_code = 'ADM3'
                                        and gp3.adm3 = gp.adm3
                                        left join
                 geonames_table gp4 on gp4.country = gp.country
                                        and gp4.geonames_code = 'ADM4'
                                        and gp4.adm4 = gp.adm4
                 where 1  and LOWER(gp.description) like 'lo%'  limit 10;

它是针对这个表格结构启动的：

id  bigint(20)  NO  PRI     
description varchar(200)    NO  MUL     
alternative_names   varchar(5001)   NO          
country varchar(2)  NO  MUL     
adm1    varchar(20) NO  MUL     
adm2    varchar(80) NO  MUL     
adm3    varchar(20) YES MUL     
adm4    varchar(20) YES MUL     
latitudine  decimal(65,7)   NO          
longitudine decimal(65,7)   NO          
geonames_class  varchar(1)  NO          
geonames_code   varchar(10) NO  MUL 

谢谢你提前提供帮助。

在这里，显示创建表输出：

CREATE TABLE `31_geonames_places` (
   `id` bigint(20) NOT NULL,
   `description` varchar(200) NOT NULL,
   `alternative_names` varchar(5001) NOT NULL,
   `country` varchar(2) NOT NULL,
   `adm1` varchar(20) NOT NULL,
   `adm2` varchar(80) NOT NULL,
   `adm3` varchar(20) DEFAULT NULL,
   `adm4` varchar(20) DEFAULT NULL,
   `latitudine` decimal(65,7) NOT NULL,
   `longitudine` decimal(65,7) NOT NULL,
   `geonames_class` varchar(1) NOT NULL,
   `geonames_code` varchar(10) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `idx_nome_place` (`description`),
   KEY `Sigla_paese_place` (`country`),
   KEY `idx_geonames_code` (`geonames_code`),
   KEY `idx_adm1` (`adm1`),
   KEY `idx_adm2` (`adm2`),
   KEY `idx_adm3` (`adm3`),
   KEY `idx_adm4` (`adm4`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

您需要4个复合索引，如

INDEX(country, geonames_code, adm2)

(列可以按任何顺序排列。)

请提供SHOW CREATE TABLE；下面我的两个评论将由它澄清。

LOWER(gp.description) like 'lo%'需要description的排序成为..._ci。如果是的话，这样做会好得多：

`gp.description like 'lo%'`

也就是说，“不区分大小写”的排序规则消除了对LOWER()的需求。

如果没有以description开头的索引，请添加这样的索引。

对于lat/液化天然气来说，decimal(65,7)太大了--它需要33个字节！这讨论了更小的替代方案。