mysql join 很慢,mysql – 3个表上的JOIN很慢

大家.

无法弄清楚缺少哪些索引或如何重新定义我的查询.

我在每个表中创建了大约一万个测试条目以查看性能.查询大约需要1.5秒,这比我需要的时间要多得多(应该非常直接).

谢谢你的帮助.

表：

CREATE TABLE `chars` (

`pid` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

`x` int(11) NOT NULL DEFAULT '0',

`y` int(11) NOT NULL DEFAULT '0',

`z` int(11) NOT NULL DEFAULT '0',

`zone` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

`zonefrom` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

`password` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

`session` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

`surname` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

`bind_zone` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

`angle` int(11) NOT NULL DEFAULT '0',

`bind_x` int(11) NOT NULL DEFAULT '0',

`bind_y` int(11) NOT NULL DEFAULT '0',

`bind_z` int(11) NOT NULL DEFAULT '0',

`bind_angle` int(11) NOT NULL DEFAULT '0',

`xp` bigint(20) unsigned NOT NULL DEFAULT '0',

`hp` int(11) NOT NULL DEFAULT '0',

`mana` int(10) unsigned NOT NULL DEFAULT '0',

`endurance` int(10) unsigned NOT NULL DEFAULT '0',

`copper` int(10) unsigned NOT NULL DEFAULT '0',

`silver` int(10) unsigned NOT NULL DEFAULT '0',

`gold` int(10) unsigned NOT NULL DEFAULT '0',

`platinum` int(10) unsigned NOT NULL DEFAULT '0',

`zoning_ts` int(10) unsigned NOT NULL DEFAULT '0',

`clearance` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`pid`),

UNIQUE KEY `name` (`name`),

KEY `zone` (`zone`)

) ENGINE=MyISAM AUTO_INCREMENT=1000004 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `inv` (

`pid` int(10) unsigned NOT NULL DEFAULT '0',

`slot` int(10) unsigned NOT NULL DEFAULT '0',

`institem` bigint(20) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`pid`,`slot`),

KEY `pid` (`pid`,`institem`),

KEY `institem` (`institem`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `institems` (

`id` bigint(11) NOT NULL AUTO_INCREMENT,

`item` int(10) unsigned NOT NULL DEFAULT '0',

`charges` int(10) unsigned NOT NULL DEFAULT '0',

`container_slot_1` bigint(20) unsigned NOT NULL DEFAULT '0',

`container_slot_2` bigint(20) unsigned NOT NULL DEFAULT '0',

`container_slot_3` bigint(20) unsigned NOT NULL DEFAULT '0',

`container_slot_4` bigint(20) unsigned NOT NULL DEFAULT '0',

`container_slot_5` bigint(20) unsigned NOT NULL DEFAULT '0',

`container_slot_6` bigint(20) unsigned NOT NULL DEFAULT '0',

`container_slot_7` bigint(20) unsigned NOT NULL DEFAULT '0',

`container_slot_8` bigint(20) unsigned NOT NULL DEFAULT '0',

`container_slot_9` bigint(20) unsigned NOT NULL DEFAULT '0',

`container_slot_10` bigint(20) unsigned NOT NULL DEFAULT '0',

`amount` int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `container_slot_1` (`container_slot_1`),

KEY `container_slot_2` (`container_slot_2`),

KEY `container_slot_3` (`container_slot_3`),

KEY `container_slot_4` (`container_slot_4`),

KEY `container_slot_5` (`container_slot_5`),

KEY `container_slot_6` (`container_slot_6`),

KEY `container_slot_7` (`container_slot_7`),

KEY `container_slot_8` (`container_slot_8`),

KEY `container_slot_9` (`container_slot_9`),

KEY `container_slot_10` (`container_slot_10`),

KEY `item` (`item`)

查询：

SELECT SQL_NO_CACHE chars.name

FROM chars

LEFT JOIN inv ON (chars.pid = inv.pid)

LEFT JOIN institems ON (inv.institem = institems.id)

WHERE

inv.institem = 10001013730 ||

institems.container_slot_1 = 10001013730 ||

institems.container_slot_2 = 10001013730 ||

institems.container_slot_3 = 10001013730 ||

institems.container_slot_4 = 10001013730 ||

institems.container_slot_5 = 10001013730 ||

institems.container_slot_6 = 10001013730 ||

institems.container_slot_7 = 10001013730 ||

institems.container_slot_8 = 10001013730 ||

institems.container_slot_9 = 10001013730 ||

institems.container_slot_10 = 10001013730;

解释以上内容：

+----+-------------+-----------+--------+---------------+---------+---------+----------------------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------+--------+---------------+---------+---------+----------------------+---------+-------------+

| 1 | SIMPLE | chars | index | NULL | PRIMARY | 4 | NULL | 1000003 | Using index |

| 1 | SIMPLE | inv | ref | PRIMARY,pid | pid | 4 | evq_pok.chars.pid | 10 | Using index |

| 1 | SIMPLE | institems | eq_ref | PRIMARY | PRIMARY | 8 | evq_pok.inv.institem | 1 | Using where |

+----+-------------+-----------+--------+---------------+---------+---------+----------------------+---------+-------------+

3 rows in set (0.00 sec)

解决方法:

使用普通JOIN,而不是LEFT

SELECT SQL_NO_CACHE chars.name

FROM chars

JOIN inv ON (chars.pid = inv.pid)

JOIN institems ON (inv.institem = institems.id)

WHERE

inv.institem = 10001013730 ||

institems.container_slot_1 = 10001013730 ||

institems.container_slot_2 = 10001013730 ||

institems.container_slot_3 = 10001013730 ||

institems.container_slot_4 = 10001013730 ||

institems.container_slot_5 = 10001013730 ||

institems.container_slot_6 = 10001013730 ||

institems.container_slot_7 = 10001013730 ||

institems.container_slot_8 = 10001013730 ||

institems.container_slot_9 = 10001013730 ||

institems.container_slot_10 = 10001013730;

很明显,因为使用左连接,你会得到很多行,其中inv和institems将是NULL并且WHERE子句没有任何意义但是负载很重.

标签：mysql,join,optimization

来源： https://codeday.me/bug/20190806/1597903.html