MySQL 优化

查询

count 优化

count 的工作过程（简版）

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

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

参考：

1. select count 底层做了什么？
2. https://www.zhihu.com/question/34781415

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