php - 优化 select mysql 中的索引

Question

我有一个约会网站，多年来我一直试图优化这个查询。几乎每天该网站都会因此而卡住。

用户表结构:

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL,
  `name` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `latitude` float(6,2) NOT NULL DEFAULT '0.00',
  `longitude` float(6,2) NOT NULL DEFAULT '0.00',
  `birth_year` smallint(4) NOT NULL DEFAULT '1997',
  `gender` tinyint(1) NOT NULL DEFAULT '1',
  `gender_search` tinyint(1) NOT NULL DEFAULT '2',
  `minimum_age` tinyint(2) NOT NULL DEFAULT '18',
  `maximum_age` tinyint(2) NOT NULL DEFAULT '80',
  `unix_timestamp_online` int(10) NOT NULL DEFAULT '0',
  `user_blocked` tinyint(1) NOT NULL DEFAULT '1',
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

索引

ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD KEY `multiple_index`(
    `unix_timestamp_online`,
    `user_blocked`,
    `gender`,
    `gender_search`,
    `maximum_age`,
    `minimum_age`,
    `birth_year`,
    `latitude`,
    `longitude`
) USING BTREE;

查询示例

SELECT id, latitude, longitude 
FROM users 
WHERE gender=2 
AND id NOT IN (11111111,2222222) 
AND (birth_year BETWEEN 1961 AND 1999) 
AND (latitude BETWEEN -25.14 AND -16.13) 
AND (longitude BETWEEN -54.87 AND -45.86) 
AND user_blocked=0 
AND minimum_age<=35 
AND maximum_age>=35 
AND gender_search IN(0,1) 
ORDER BY unix_timestamp_online DESC 
LIMIT 20

总结示例查询的条件..

• 我是male=1并搜索female=2
• 排除一些id
• 出生于1961 年至 1999 年之间
• 在纬度、经度中...
• 没有任何类型的block=0
• 我的 age=35 必须为这些用户所接受
• 我的性别也需要被接受(all=0,male=1)
• 首先显示在线用户进行排序

解释示例查询

id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: index
possible_keys: PRIMARY
key: multiple_index
key_len: 19
ref: NULL
rows: 20
filtered: 0.25
Extra: Using where; Using index

此查询平均需要 0.0020 秒

如果我将birth_year条件更改为(BETWEEN 1999 AND 1999)

解释相同，查询平均耗时 9.1376 秒

这个多重索引看起来不太漂亮，但它是迄今为止最好的。任何帮助表示赞赏。谢谢。

Answer 1

为什么不这样进行查询，在复杂的条件之前使用简单的条件。

SELECT id, latitude, longitude 
FROM users 
WHERE gender=2 
AND user_blocked=0 
AND minimum_age<=35 
AND maximum_age>=35 
AND gender_search IN(0,1)
AND id NOT IN (11111111,2222222) 
AND (birth_year BETWEEN 1961 AND 1999) 
AND (latitude BETWEEN -25.14 AND -16.13) 
AND (longitude BETWEEN -54.87 AND -45.86) 
ORDER BY unix_timestamp_online DESC 
LIMIT 20