mysql查询性能优化

查询长时间不返回

select * from information_schema.processlist;//这条语句跟下面的语句相同
show processlist;//展示出当前的进程信息

特殊的，在模拟一种MDL写锁阻塞的情况可以通过该命令查到阻塞的查询，这种情况一般第一时间执行该语句：

通过kill指令杀掉线程，恢复执行：

kill 4;//kill pid,通过上面的命令查询到可以查询到对应线程的pid

select * from information_schema.innodb_locks;//可以查看死锁，注意锁等待信息不会在这里显示出来，除非发生死锁。包括gap锁和x锁可能导致的死锁等待，数据库开启了死锁等待机制可以检测死锁。

开启慢查询

show variables like "%query%"

result:

-- 会在当前session立即生效
set global slow_query_log='ON';
-- 不会在当前的session生效，很坑，需要开启另一个session才会查询到设置效果
set global long_query_time=0;

截取一个slow log的记录结果：

慢查询优化步骤

业务上有个审核表的表结构如下：

CREATE TABLE `audit_order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `order_id` int(11) unsigned NOT NULL COMMENT '销售订单号',
  `order_code` varchar(32) NOT NULL DEFAULT '' COMMENT '订单中心订单号',
  `user_id` varchar(32) DEFAULT NULL COMMENT '申请人id',
  `auditor_id` varchar(32) DEFAULT NULL COMMENT '审核人id',
  `status` tinyint(4) NOT NULL DEFAULT '10' COMMENT '状态 10：待审核 20：审核通过 30：审核驳回 40：审核未通过',
  `reject_reason` varchar(128) DEFAULT NULL COMMENT '不通过原因',
  `updater_id` varchar(32) DEFAULT '' COMMENT '更新人',
  `date_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `date_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `business_type` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_order_id` (`order_id`),
  KEY `idx_order_code` (`order_code`),
  KEY `date_update` (`date_update`),
  KEY `idx_date_create` (`date_create`)
) ENGINE=InnoDB AUTO_INCREMENT=50571 DEFAULT CHARSET=utf8 COMMENT='订单审核表';

分析下面语句实际扫描的行数：

SELECT id, order_code, status, date_create, date_update
	, reject_reason
FROM audit_order
WHERE (business_type = 'CUSTOMIZED_CAR'
	AND status IN (20)
	AND date_update > '2018-01-01')
ORDER BY date_update
LIMIT 100

我们explain一下：

这里分析显示的扫描行数rows是24786行，但是实际上我们通过慢查询日志发现，实际的扫描行数是接近五万行，接近两倍。using index condition表示通过date_update索引查询过滤到记录之后回表，执行时间在全表数据较少的情况下用了100ms，符合我们的预期：

加一个索引，重新执行explain，并观察慢查询日志真实扫描行数：

ALTER TABLE audit_order
	ADD INDEX idx_type_status_date (status, business_type, date_update);

通常我们观察range级别是索引可用级别，但是实际的扫描行数还是需要参考explain的rows和日志中的rows_examinzed，其中explain是估计值，真正准确的还是rows_examinzed。

count(*)性能

innnodb在统计行数的时候没有像myisam一样保存了行数，需要重新扫描一遍表。我们比较一下集中查询方式的性能。插入一个两百多万行的数据表word。

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `inputdata`()
begin
  declare i int;
  set i=1;
  while(i<=2800000)do
    insert into words values(i,i);
    set i=i+1;
  end while;
end;;
DELIMITER ;
call inputdata;

-- count(id)
# Time: 2019-01-09T14:10:05.038579Z
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 0.349229  Lock_time: 0.000062 Rows_sent: 1  Rows_examined: 0
SET timestamp=1547043005;
select count(id) from words;

-- count(1)
# Time: 2019-01-09T14:10:02.488929Z
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 0.305565  Lock_time: 0.000060 Rows_sent: 1  Rows_examined: 0
SET timestamp=1547043002;
select count(1) from words;

-- count(字段)
# Time: 2019-01-09T14:09:56.943701Z
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 0.544218  Lock_time: 0.000066 Rows_sent: 1  Rows_examined: 2709998
SET timestamp=1547042996;
select count(word) from words;

-- count(*)
# Time: 2019-01-09T14:09:59.196291Z
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 0.301123  Lock_time: 0.000057 Rows_sent: 1  Rows_examined: 0
SET timestamp=1547042999;
select count(*) from words;

结论：count(*)约等于count(1)>count(id)>count(字段)，推荐使用前三个，count字段有磁盘io，会把字段的值取出来，进行判断是否为null值。通常我们通过innodb的事务性特征，将总记录条数在一个事务中去取，mysql即使在异常情况下也不会导致数据不一致。