# 2016/11/03\_sql optimization principle

#### 表设计时的优化

* 字段设计时使用not null
* 大表按业务需求拆分成小表 比如说时间
* 字段尽量设计为定长

#### 索引优化

* 经常在where子句使用的列，设置索引
* 多个列where或者order by子句的，建立复合索引
* 可以限制数据量最小的索引，放在where条件的最左边
* 当子查询比较耗时或数据量大时，为了减少扫描数据，建议把母查询的条件为赋予子查询

#### 语句优化（慢查询优化）

* 使用explain和profile分析查询语句
* 查看慢查询日志
* 千万级分页时使用limit
* 不要select \*
* 多表连接时 小表join大表
* 经常使用的查询，可以开启缓存
* 不要使用not in和<>操作


---

# 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://note.lonelylty.com/coding/sql_optimization_principle.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.