查询速度慢，哪些字段建立索引

Question

我有这个疑问：

select * from 

当我执行它，它需要〜45秒用35K的记录。每天我都会向gps_unit_location表添加5k +个新记录。所以表会增长。 我所有的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 
   

在附注上，我认为您不需要定义为主键的列上的唯一索引，这会导致insert/update语句的写入开销。

Answer 2

通用的答案是索引那些用于连接和约束的列（ON和WHERE子句）。使用复合索引（首先连接，然后使用最低基数约束的约束）。

哦，并使所有的ID'无符号'。