mysql - 创建索引以提高mysql连接性能的困惑

Question

我在论坛上读了很多帖子，但我仍然对创建索引以加速 mysql 中的联接查询感到困惑，这是我的疑问

我有两张表，一张是category表，只有几千行，包含有关数据的所有信息，另一个是geo_data表，包含大量数据数据，我根据 2 个键 s_key1 和 s_key2 加入 geo_data 表。以下是表的结构

类别表格

CREATE TABLE `category` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `s_key1` int(11) DEFAULT NULL,
  `s_key2` int(11) DEFAULT NULL,
  `STD_DATE` datetime DEFAULT NULL,
  `LATITUDE` float DEFAULT NULL,
  `LONGITUDE` float DEFAULT NULL,
  `COUNTRY_CD` varchar(15) DEFAULT NULL,
  `INSTR_CODE` varchar(15) DEFAULT NULL,
  `CANADACR_CD` varchar(15) DEFAULT NULL,
  `PROBST_T` varchar(15) DEFAULT NULL,
  `TYPE` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=32350 DEFAULT CHARSET=latin1; 

geo_data表

CREATE TABLE `geo_data` (
  `s_key1` int(11) DEFAULT NULL,
  `s_key2` int(11) DEFAULT NULL,
  `MAGNETIC` float DEFAULT NULL,
  `GRAVITY` float DEFAULT NULL,
  `BATHY` float DEFAULT NULL,
  `CORE` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

我有很多表，例如geo_data表，其中包含s_key1、s_key2和其他列，在我的应用程序中我经常使用字段std_date、latitude ,经度,country_cd,来自 category 表的类型

我进行内部联接，有时根据要求进行左联接，例如我的查询如下所示

SELECT 
        c.s_key1,
        c.s_key2,
        c.std_date,
        c.latitude,
        c.longitude,
        g.magnetic,
        g.bathy 
FROM 
        category c, geo_data g 
WHERE 
        c.s_key1 = g.s_key1 && c.s_key2 = g.s_key2;

有时我的 where 子句也会有类似的内容

WHERE 
            c.latitude between -30 to 30 AND
            c.longitude between 10 to 140  AND
            c.country_cd = 'INDIA' AND
            c.type = 'NON_PROFIT';

那么创建索引来加速查询的正确方法是什么，是否是下面一种方法？请有人帮忙

create index `myindex` on 
`category` (s_key1,s_key2,std_date,latitude,longitude,country_cd)

create index `myindex` on 
`geo_data` (s_key1,s_key2)

还有一个疑问是两个表(category，geo_data)都应该有索引键以提高性能还是只有geo_data表？

Answer 1

根据 where 条件，将第一个索引简化为:

create index `myindex` on 
 `category` (s_key1,s_key2)

然而，原始版本可以提高性能，因为它不必访问完整的表行来获取其他值。然而，它使索引更大，因此速度更慢。因此，这取决于这是否仅针对此查询进行优化，还是有更多查询仅使用 s_key1 和 s_key2(或与其他列组合)。

关于澄清 - 对于 lat/lng 检查，将 std_date 移到 lat/lng 之后(或完全删除)是有意义的:

create index `myindex` on 
`category` (s_key1,s_key2,latitude,longitude,std_date,country_cd)