# Mysql优化

## limit对于越大的offset查询越慢

场景：

导出数据的时候，利用`select * from table limit ${offset},${limit}`，但是当offset越来越大的时候(比如100万)，查询非常耗时。

解决思路：

## 组合主键不满足最左前缀

项目实践中，发现如果是组合主键的话，按照最左前缀去查询也不能使用到索引，不知为什么？？？所以一般最好建立组合索引去按照最左前缀查询。

## 修改组合索引顺序来满足最左前缀

项目中发现有大量下面的查询：

```
SELECT * FROM message WHERE from = :from AND to = :to AND seq IN (:seqs)
```

但是message表的组合索引为：

```
KEY `zuhe` (`id`,`from`,`to`,`seq`)
```

很明显无法满足最左前缀原则，由于id字段很少用到，我们将组合索引改为：

```
KEY `zuhe` (`from`,`to`,`seq`,`id`)
```

这样上面那么大量的查询就可以用到索引提高查询效率了。

## 分表

对于数据量较大的表可以进行分表，比如使用id%100分布到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://jun-wang.gitbook.io/learnjava/ji-shu-xue-xi/mysql/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.