查看慢日志系统状态
SHOW VARIABLES LIKE "%slow%";
+-------------------------+------------------------+
| Variable_name | Value |
+-------------------------+------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/data/slow.log |
+-------------------------+------------------------+
5 rows in set (0.02 sec)
查看慢日志时间
mysql> SHOW VARIABLES LIKE "long_query_time";
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| long_query_time | 2.000000 |
+----------------+----------+
1 row in set (0.03 sec)
查看慢语句
mysql> SHOW FULL PROCESSLIST;
+-----------+-------+-----------------+-------+----------+------+------------------+------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----------+-------+-----------------+-------+----------+------+------------------+------------------------------------------------------------------------------------------------+
| 162249371 | ciscn | 192.168.1.4:53908 | ciscn | Query | 0 | init | SHOW FULL PROCESSLIST |
| 162250930 | ciscn | 192.168.1.4:55479 | ciscn | Sleep | 13 | | NULL |
| 162250931 | ciscn | 192.168.1.4:55480 | ciscn | Sleep | 84 | | NULL |
| 162251225 | ciscn | 192.168.1.4:55808 | ciscn | Execute | 0 | Sending data | select count(*) as aggregate from `team_relations` where `team_id` = '9570' and (`is_delete` = '0') |
| 162251226 | ciscn | 192.168.1.4:55810 | ciscn | Execute | 1 | Creating sort index | select * from `team_relations` where `team_id` = '8720' and (`is_delete` = '0') order by `id` asc |
| 162251229 | ciscn | 192.168.1.4:55813 | ciscn | Execute | 1 | Sending data | select count(*) as aggregate from `team_relations` where `status` = '1' and (`team_id` = '9533') and (`is_delete` = '0') |
| 162251238 | ciscn | 192.168.1.4:55823 | ciscn | Sleep | 1 | | NULL |
+-----------+-------+-----------------+-------+----------+------+------------------+------------------------------------------------------------------------------------------------+
7 rows in set (0.04 sec)
查看索引使用情况
mysql> explain select count(*) as aggregate from `notification_user` where `user_id` = '76973' and `status` = '0';
+----+------------+----------------+------+--------------+--------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+------------+----------------+------+--------------+--------+---------+-------+-------+-------------+
| 1 | SIMPLE | notification_user | ref | user_id | user_id | 4 | const | 4 | Using where |
+----+------------+----------------+------+--------------+--------+---------+-------+-------+-------------+
1 row in set (0.04 sec)
可见没有索引的使用
mysql> explain select count(*) as aggregate from `team_relations` where `team_id` = '9570' and (`is_delete` = '0');
+----+------------+--------------+------+--------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+------------+--------------+------+--------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | team_relations | ALL | NULL | NULL | NULL | NULL | 38723 | Using where |
+----+------------+--------------+------+--------------+------+---------+------+-------+-------------+
1 row in set (0.04 sec)
添加索引
mysql> ALTER TABLE `ciscn`.`team_relations` ADD INDEX `team_id`(`team_id`);
去添加索引后在查看
mysql> explain select count(*) as aggregate from `team_relations` where `team_id` = '9570' and (`is_delete` = '0');
+----+------------+--------------+------+--------------+---------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+------------+--------------+------+--------------+---------+---------+-------+-------+-------------+
| 1 | SIMPLE | team_relations | ref | team_id | team_id | 4 | const | 4 | Using where |
+----+------------+--------------+------+--------------+---------+---------+-------+-------+-------------+
1 row in set (0.04 sec)
在对条件字段 team_id 添加普通索引后,可见语句执行只扫描了4行.对比优化前的 38723 行扫描,效率提升是非常明显的。
但在结果返回的操作时间都是 0.04 sec 好像又效率不高的错觉。其中mysql 是有语句查询缓存的
查看跟缓存相关的变量
mysql> show global variables like 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.02 sec)
query_cache_type:查询缓存类型:是否开启缓存功能,开启方式有三种{ON|OFF|DEMAND}
DEMAND:意味着select语句明确使用sql_cache选项才会缓存 (SQL_NO_CACHE则不会缓存)
query_cache_size:总空间;单位为字节,大小必须为1024的整数倍.mysql启动时,会一次分配并立即初始化这里指定大小的内存空间;
这意味着,如果修改此大小,会清空缓存并重新初始化的.
query_cache_min_res_unit:存储缓存的最小内存块;(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache 能够获得一个理想的值
设置过大,容易浪费;设置过小,容易产生大量碎片
query_cache_limit:单个缓存对象的最大值,超出时则不予缓存;手动使用SQL_NO_CACHE可以人为的避免尝试缓存返回超出此参数限定值得语句
query_cache_wlock_invalidate:如果某个表被其他用户连接锁住了,是否仍然从缓存中返回结果,OFF表示返回
如何判断命中率:
分为次数和字节命中率(命中以后,所省去的数据传输量或解析本身所开销的大小)