mysql gis 时间_MySQL查询耗时分析及存储空间分析

查询耗时分析

查看执行语句：

MySQL [dbname]> show processlist;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 1 | event_scheduler | localhost | NULL | Daemon | 27675 | Waiting for next activation | NULL |

| 2 | root | 114.55.72.122:41679 | dbname| Sleep | 25 | | NULL |

| 3 | root | 114.55.72.122:41682 | dbname| Sleep | 51 | | NULL |

| 4 | root | 114.55.72.122:41680 | dbname| Sleep | 25 | | NULL |

| 218 | root | 114.55.72.121:55010 | dbname| Killed | 27625 | updating | delete from msg_log_t where recv_time < DATE_SUB(NOW(),INTERVAL 2 DAY)

使用kill关键字取消后台耗时操作：

MySQL [dbname]> kill 218; //其中218对应上表中查询ID

Query OK, 0 rows affected (0.00 sec)

使用explain关键字分析慢查询：

MySQL [dbname]> explain select recv_time from msg_log_t order by recv_time desc limit 10;

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

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

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

| 1 | SIMPLE | car_gb_msg_log_t | index | NULL | update | 24 | NULL | 35173644 | Using index; Using filesort |

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

1 row in set (0.00 sec)

如果出现Extra列出现Using filesort，即要使用外部排序，则会造成查询缓慢。

存储空间分析

查看表及索引占用存储空间：

MySQL [(none)]> use dbname;

Database changed

MySQL [dbname]> select table_name,concat(truncate(data_length/1024/1024,2),'MB') as data_size,

-> concat(truncate(index_length/1024/1024,2),'MB') as index_size

-> from information_schema.tables where table_schema='dbname'

-> order by data_length desc;

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

| table_name | data_size | index_size |

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

| msg_log_t | 10271.75MB | 3272.12MB |

MySQL [dbname]> show index from msg_log_tfrom btkjdb;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| msg_log_t| 0 | PRIMARY | 1 | id | A | 41523525 | NULL | NULL | | BTREE | | |

| msg_log_t| 1 | car_id | 1 | car_id | A | 21 | NULL | NULL | YES | BTREE | | |

| msg_log_t| 1 | car_id | 2 | msg_type | A | 21 | NULL | NULL | YES | BTREE | | |

| msg_log_t| 1 | update | 1 | car_id | A | 21 | NULL | NULL | YES | BTREE | | |

| msg_log_t| 1 | update | 2 | length | A | 21 | NULL | NULL | YES | BTREE | | |

| msg_log_t| 1 | update | 3 | recv_time | A | 41523525 | NULL | NULL | YES | BTREE | | |

| msg_log_t| 1 | update | 4 | upload_status | A | 41523525 | NULL | NULL | YES | BTREE | | |

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

索引优化

联合索引，如INDEX(C1,C2,C3,C4)，通常是先按C1列排序，C1相同，再按C2列排序，以次类推。此时，order by后的字段，只对C1列优化。如果要order by C2，最好新建INDEX(C2)，否则很可能会出现Using filesort现象。具体使用哪个索引字段，最好使用explain分析下。

转载自：https://blog.csdn.net/weixin_41344042/article/details/80282541