# MySQL 优化

### 查询

#### count 优化

![count 的工作过程（简版）](/files/-ME3MAyWxlk65_sYfKRT)

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

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

![](/files/-ME3RIdDmrXSJI6bDLUz)

参考：

1. [select count 底层做了什么？](https://zhuanlan.zhihu.com/p/71333492)
2. <https://www.zhihu.com/question/34781415>

#### Order By 优化

```sql
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](/files/-MEOGC2v4IQ9k_or1Nv8)

![优化方向](/files/-MEOGJh4y5fA8RJd0yuI)

要点：**max\_length\_for\_sort\_data / sort\_buffer\_size / number\_of\_tmp\_files / examined rows / using filesort / 归并排序 / 快速排序 / 堆排序 / sort mode**

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

* 稍微复杂一点

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

这个语句是需要排序的，也就是 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`&#x20;

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

或者

```sql
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
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://shniu.gitbook.io/cs/middleware/mysql/you-hua.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.