MySQL 优化

查询

count 优化

count 的工作过程(简版)

在使用 count 需要注意的是:一个优化点是在数据量大的时候,可以找一个字段长度相对较小的列做一个二级索引,这样 mysql 在统计的时候加载的数据量更小,IO需要的时间就更少;count 不计算 NULL 值

更进一步的计数优化,需要在架构上做更多的设计

参考:

Order By 优化

CREATE TABLE `user_profile` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

-- 查询某个城市按姓名排名的前1000个人的城市、姓名和年龄
select city, name, age from user_profile where city = '上海' order by name limit 1000;

explain select city, name, age from user_profile where city = '上海' order by name limit 1000;
+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | user_profile | NULL       | ref  | city          | city | 66      | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+-------+------+----------+---------------------------------------+
  • 简单条件的 order by

select city, name, age from user_profile where city = '上海' order by name limit 1000 是如何工作的?

Order by
优化方向

要点:max_length_for_sort_data / sort_buffer_size / number_of_tmp_files / examined rows / using filesort / 归并排序 / 快速排序 / 堆排序 / sort mode

如果内存够,就要多利用内存,尽量减少磁盘访问。在 sort_buffer_size 足够的情况下,会避免使用外部排序(外部排序是借助多个临时的磁盘文件进行排序,把排好序的数据放入多个临时的小文件,然后将有序的小文件依次按序放入内存,直到满足条件为止)

  • 稍微复杂一点

在 city, name 上建立一个联合索引,select city, name, age from user_profile where city in ('杭州','上海') order by name limit 100 该如何工作?又该怎么优化呢?

这个语句是需要排序的,也就是 using filesort,工作方式和上图是类似的。如何避免呢?需要把这个SQL拆成两部分:

select city, name, age from user_profile where city = '杭州' order by name limit 100

select city, name, age from user_profile where city = '上海' order by name limit 100

然后使用归并排序,取出前 100。

或者

select * from (
  select * from t where city = '杭州' limit 100
  union all
  select * from t where city = '苏州' limit 100
) as tt order by name limit 100

最后更新于

这有帮助吗?