索引什么时候失效 和 如何排查慢查询 |
您所在的位置：网站首页 › 轨迹排查表是什么 › 索引什么时候失效 和 如何排查慢查询 |
|
1.索引什么时候失效？
经常在编写代码时候出现慢查询问题，这说明很多时候索引没有使用对。 记忆口诀：模糊匹配、类型隐转、最左匹配，这三种情况可能导致索引会失效。 以%开头的LIKE语句，模糊搜索。索引没有办法去利用，因为B+树的key没有办法再去直接比较了。 出现隐式类型转换，需要注意参数类型跟SQL查询时候类型是不是一样的，因为两个类型不同没法比较（在 Python 这种动态语言查询中需要注意） 没有满足最左前缀原则（想想为什么是最左匹配？最左前缀原则，如果是多列索引的时候需要满足最左前缀才能利用上索引。例如：多列索引有三个值a, b, c三个字段，如(a, b, c)。有两个key值(1, 2, 3) 和 (1, 2, 4)作比较，先比较第一个都是1相等，再比交第二个都是2相等，最后比较第三个3小于4 ，这时候就知道二叉树往哪个方向。假如不满足最左匹配就没有办法直接比较，如 (a,b,c), (a,b), (a),这三种情况下都是可以使用索引的，但是(b,c)就不可以，因为连第一个比较的基准都没有，根本没有办法再往后比较了） 三种情况总结成一句话，当Mysql B+树的key没有办法去比较的时候，索引就会失效。 2.如何排查慢查询？慢查询通常是缺少索引，索引不合理或者业务代码实现导致 ① 开启并且查询慢查询日志 开启慢查询功能 slow_query_log_file 慢查询日志文件路径 slow_query_log 用于指定是否打开慢查询日志 long_query_time 超过多少秒的查询就写入日志 ② 通过 explain 排查索引问题 ③ 调整数据修改索引；业务代码层限制不合理访问，如：因为数据类型不匹配导致全表扫描；一次获取太多数据等 3.查找分析查询速度慢的原因分析SQL查询慢的方法： ① 记录慢查询日志。 分析查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用 pt-query-digest 工具进行分析。 ② 使用 show profile。 开启 set profiling = 1，服务器上执行的所有语句会检测消耗时间，存到临时表中。 mysql> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec)show profiles ，查看临时表 show profile for query 临时表ID，通过临时表的主键ID去查一条sql语句是在什么地方发生慢的操作。 mysql> show profiles; +----------+------------+-------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------+ | 1 | 0.00315025 | SELECT DATABASE() | | 2 | 0.04271325 | select * from a | +----------+------------+-------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> show profile for query 2; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.001101 | | checking permissions | 0.000016 | | Opening tables | 0.037732 | | init | 0.000040 | | System lock | 0.000021 | | optimizing | 0.000006 | | statistics | 0.002043 | | preparing | 0.000025 | | executing | 0.000003 | | Sending data | 0.001608 | | end | 0.000006 | | query end | 0.000007 | | closing tables | 0.000013 | | freeing items | 0.000071 | | cleaning up | 0.000022 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)③ 使用show status show status会返回一些计数器，show global status查看服务器级别的所有计数。 有时根据这些计数，可以猜测出哪些操作代价较高或者消耗时间多 ④ 使用show processlist 观察是否有大量线程处于不正常的状态或者特征 mysql> show processlist; +----+------+-----------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+----------+------------------+ | 3 | root | localhost:14778 | test | Query | 0 | starting | show processlist | +----+------+-----------------+------+---------+------+----------+------------------+ 1 row in set (0.01 sec)⑤ 使用 explain(或 使用别名DESC) 分析单条SQL语句，注意格式化 \G 输出后不需要加分号。 mysql> explain select * from a; +----+-------------+-------+------------+------+---------------+------+---------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | +----+-------------+-------+------------+------+---------------+------+---------+ | 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | +----+-------------+-------+------------+------+---------------+------+---------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from a\G -- 注意:格式化输出内容，\G相当于; *************************** 1. row ********** id: 1 select_type: SIMPLE table: a partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
|
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |