mysql - 查询慢，索引哪些字段

Question

我有这个问题:

select * from 

当我执行它时，35k 条记录需要大约 45 秒。每天我都会向 gps_unit_location 表中添加 5k+ 条新记录。所以 table 会增长。我当前所有 id 的索引。添加任何其他索引会帮助我提高此查询的性能吗？

谢谢。

Answer 1

所以，

1. 确保您有 NOT NULL 列和索引:

   INDEX ON gps_unit_location.idgps_unit_location
   INDEX ON user.iduser
   INDEX ON user_to_gps_unit.iduser
   INDEX ON user_to_gps_unit.idgps_unit
   INDEX ON gps_unit.idgps_unit
   INDEX ON gps_unit_location.idgps_unit
   

2. 确保您确实需要选择带有该星号的所有字段*

3. 试试这个查询:

   SELECT 
       `gps_unit`.`idgps_unit`, 
       `gps_unit`.`name as name`, 
       `gps_unit`.`notes as notes`, 
       `gps_unit`.`serial`, 
       `gps_unit_location`.`dt` as dt, 
       `gps_unit_location`.`idgps_unit_location`,
       `gps_unit_location`.`lat`, 
       `gps_unit_location`.`long`,
       `ip`,
       `unique_id`,
       `loc_age`,
       `reason_code`,
       `speed_kmh`,
       `VehHdg`,
       `Odometer`,
       `event_time_gmt_unix`,
       `switches`,
       `engine_on_off`
   FROM user 
   INNER JOIN user_to_gps_unit  ON user.iduser = user_to_gps_unit.iduser 
   INNER JOIN gps_unit          ON user_to_gps_unit.idgps_unit = gps_unit.idgps_unit
   INNER JOIN gps_unit_location ON gps_unit.idgps_unit = gps_unit_location.idgps_unit
   INNER JOIN
           (SELECT
                `gps_unit_location`.`idgps_unit`,
                MAX(`gps_unit_location`.`dt`) dtmax
            FROM `gps_unit_location`
            GROUP BY 1
            ) r1 ON r1.idgps_unit = gps_unit_location.idgps_unit AND r1.dtmax = gps_unit_location.dt
   WHERE 
       user.iduser = 14
   

附带说明一下，我认为您不需要定义为主键的列的唯一索引，这会导致插入/更新语句的写入开销。