大型数据库查询的优化

Question

我想问一下，是否有任何方法来优化以下查询：

SELECT SQL_BIG_RESULT DISTINCT u.spy_tech,
                               u.computer_tech,
                               u.military_tech,
                               u.defence_tech,
                               u.shield_tech,
                               u.energy_tech,
                               u.hyperspace_tech,
                               u.combustion_tech,
                               u.impulse_motor_tech,
                               u.hyperspace_motor_tech,
                               u.laser_tech,
                               u.ionic_tech,
                               u.buster_tech,
                               u.intergalactic_tech,
                               u.expedition_tech,
                               u.metal_proc_tech,
                               u.crystal_proc_tech,
                               u.deuterium_proc_tech,
                               u.technologia_bankowa,
                               u.technologia_kolonizacyjna,
                               u.technologia_optymalizacji,
                               u.technologia_otchlani,
                               u.graviton_tech,
                               SUM(p.small_ship_cargo)        as small_ship_cargo,
                               SUM(p.big_ship_cargo)          as big_ship_cargo,
                               SUM(p.light_hunter)            as light_hunter,
                               SUM(p.heavy_hunter)            as heavy_hunter,
                               SUM(p.crusher)                 as crusher,
                               SUM(p.battle_ship)             as battle_ship,
                               SUM(p.colonizer)               as colonizer,
                               SUM(p.recycler)                as recycler,
                               SUM(p.spy_sonde)               as spy_sonde,
                               SUM(p.bomber_ship)             as bomber_ship,
                               SUM(p.solar_satelit)           as solar_satelit,
                               SUM(p.destructor)              as destructor,
                               SUM(p.dearth_star)             as dearth_star,
                               SUM(p.battleship)              as battleship,
                               SUM(p.lune_noir)               as lune_noir,
                               SUM(p.ev_transporter)          as ev_transporter,
                               SUM(p.star_crasher)            as star_crasher,
                               SUM(p.giga_recykler)           as giga_recykler,
                               SUM(p.dm_ship)                 as dm_ship,
                               SUM(p.apocalypse)              as apocalypse,
                               SUM(p.annihilator)             as annihilator,
                               SUM(p.devastator)              as devastator,
                               SUM(p.fleet_save)              as fleet_save,
                               u.r_badawczy,
                               u.r_budowlany,
                               u.r_militarny,
                               u.r_obrony,
                               u.r_paliwowy,
                               u.r_gospodarczy,
                               u.r_planetarny,
                               u.r_magazynowy,
                               u.r_energetyczny,
                               u.r_ekonomiczny,
                               u.r_odkrywczy,
                               u.r_max,
                               u.r_speed,
                               u.r_moon,
                               u.r_dm,
                               u.r_terra,
                               SUM(p.misil_launcher)          as misil_launcher,
                               SUM(p.small_laser)             as small_laser,
                               SUM(p.big_laser)               as big_laser,
                               SUM(p.gauss_canyon)            as gauss_canyon,
                               SUM(p.ionic_canyon)            as ionic_canyon,
                               SUM(p.buster_canyon)           as buster_canyon,
                               SUM(p.small_protection_shield) as small_protection_shield,
                               SUM(p.big_protection_shield)   as big_protection_shield,
                               SUM(p.planet_protector)        as planet_protector,
                               SUM(p.graviton_canyon)         as graviton_canyon,
                               SUM(p.orbital_station)         as orbital_station,
                               SUM(p.dzialo_pulsacyjne)       as dzialo_pulsacyjne,
                               SUM(p.dzialo_fotonowe)         as dzialo_fotonowe,
                               SUM(p.interceptor_misil)       as interceptor_misil,
                               SUM(p.interplanetary_misil)    as interplanetary_misil,
                               u.id,
                               u.ally_id,
                               u.onlinetime,
                               s.tech_rank                    AS old_tech_rank,
                               s.build_rank                   AS old_build_rank,
                               s.defs_rank                    AS old_defs_rank,
                               s.fleet_rank                   AS old_fleet_rank,
                               s.total_rank                   AS old_total_rank,
                               s.total_points_old             AS total_points_old,
                               s.fleet_points_old             AS fleet_points_old,
                               s.defs_points_old              AS defs_points_old,
                               s.build_points_old             AS build_points_old,
                               s.tech_points_old              AS tech_points_old
FROM test_users as u
         LEFT JOIN test_statpoints as s ON s.stat_type = 1 AND s.id_owner = u.id
         LEFT JOIN test_planets as p ON u.id = p.id_owner
WHERE u.user_lastip != "127.0.0.1"
  AND u.bana = "0"
  AND u.authlevel = "0"
  AND u.universe = "1"
GROUP BY s.id_owner, u.id

在我的VPS上，查询需要8秒，结果大约是10,000个结果。虽然我对机器进行了两次改进，达到8x2，4 has，16 to内存，但查询时间没有改变。所以我想优化上面提到的查询或者做VPS配置，找出一个瓶颈来产生这样的时间。

表STATPOINTS:

​

​

表用户：

​

​

我不知道我是否已经给你一个洞察力的表，当涉及到其他事情，我会要求指导。

解释：

​

​

Answer 1

如果添加以下索引，查询可以运行得更快：

create index ix1 on user (bana, authlevel, universe);

create index ix2 on test_statpoints (stat_type, id_owner);

create index ix3 on test_planets (id_owner);

它们根据连接谓词包括访问列。