Skip to content

Commit 3b54855

Browse files
author
代码风水师
committed
更新了SQL优化
1 parent ccd755d commit 3b54855

2 files changed

Lines changed: 89 additions & 68 deletions

File tree

‎README.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -106,7 +106,7 @@
106106

107107
### 七、:closed_book::green_book::blue_book::notebook_with_decorative_cover::books:高性能 MySQL
108108
* [数据库 (第 01 篇)精讲:数据库的三大范式与五大约束](https://github.com/about-cloud/JavaCore/blob/master/resource/markdown/database/NormalformAndConstraint.md)
109-
* [数据库 (第 01 篇)精讲:事务的四大特性与四种隔离级别](https://github.com/about-cloud/JavaCore/blob/master/resource/markdown/database/ACIDAndIsolationLevel.md)
109+
* [数据库 (第 01 篇)精讲:事务的ACID四大特性与四种隔离级别](https://github.com/about-cloud/JavaCore/blob/master/resource/markdown/database/ACIDAndIsolationLevel.md)
110110
* [数据库 (第 01 篇)精讲: MySQL InnoDB 索引](https://github.com/about-cloud/JavaCore/blob/master/resource/markdown/database/InnoDB.md)
111111
* [数据库 (第 01 篇)精讲: MySQL锁机制](https://github.com/about-cloud/JavaCore/blob/master/resource/markdown/database/MySQLLock.md)
112112
* [数据库 (第 01 篇)精讲: 悲观锁与乐观锁](https://github.com/about-cloud/JavaCore/blob/master/resource/markdown/database/PessimisticlockAndOptimisticlock.md)

‎resource/markdown/database/SQLOptimization.md‎

Lines changed: 88 additions & 67 deletions
Original file line numberDiff line numberDiff line change
@@ -99,94 +99,59 @@ WHERE t0.product_status IN ('正常销售', '促销中', '可预订');
9999

100100

101101

102-
#### 2、禁止 ~~`左模糊`~~ 或 ~~`全模糊`~~ 查询,否则不会命中索引
102+
#### 3、禁止 ~~`左模糊`~~ 或 ~~`全模糊`~~ 查询,否则不会命中索引
103+
104+
*反例*:❌
103105

104106
```mysql
105107
SELECT t0.product_id
106108
FROM product t0
107-
WHERE t0.product_name LIKE ('');
109+
WHERE t0.product_name LIKE '%化工品%';
110+
-- 或
111+
SELECT t0.product_id
112+
FROM product t0
113+
WHERE t0.product_name LIKE '%化工品原料';
108114
```
109115

116+
正例:✅
110117

118+
```mysql
119+
SELECT t0.product_id
120+
FROM product t0
121+
WHERE t0.product_name LIKE '危险化工品%';
122+
```
111123

112124

113125

114-
严禁 SELECT 使用 `*` 代替查询字段,应一一指明字段。
126+
#### 4、字段的默认值不要为 ~~`null`~~,否则不会命中索引:
127+
> 使用默认约束 (Default Counstraint) 填充数据的默认值
115128
116129

117130

118-
##### ~~NOT IN ()`~~ 不会命中索引::x:
131+
#### 5、在 ~~`字段上计算`~~ 后,不会命中索引:
119132

120-
```sql
121-
SELECT
122-
t0.product_id
123-
FROM product t0
124-
WHERE t0.product_type NOT IN ('日用品', '电子产品');
125-
```
126-
应改为::white_check_mark:
127-
```sql
128-
SELECT
129-
t0.user_name
130-
FROM product t0
131-
WHERE t0.product_type NOT IN ('日用品', '电子产品');
132-
```
133-
134-
##### 2、禁止 ~~`左模糊`~~ 或 ~~`全模糊查询`~~ ,否则不会命中索引:
135-
```sql
136-
SELECT t0.user_name
137-
FROM user to
138-
WHERE t0.user_name LIKE '%zhangsan%';
139-
```
140-
应改为(或者使用`搜索引擎`):
141-
```sql
142-
SELECT t0.user_name
143-
FROM user to
144-
WHERE t0.user_name LIKE 'zhangsan%';
145-
```
146-
147-
##### 3、字段的默认值不要为 ~~`null`~~,否则不会命中索引:
148-
> 填充默认值
149-
150-
##### 4、判断是否为空,只能使用 `ISNULL()`函数(否则结果有出入):(待验证)
151-
```sql
152-
SELECT ISNULL(t0.user_name)
153-
FROM user to
154-
```
133+
*反例*:❌
155134

156-
##### 5、在 ~~`字段上计算`~~ 后,不会命中索引:
157135
```sql
158-
SELECT t0.user_name
159-
FROM user to WHERE FROM_UNIXTIME(t0.gmt_create) < CURDATE();
136+
SELECT t0.trade_id
137+
FROM trade_order t0
138+
WHERE DATE(t0.gmt_create) < CURDATE();
160139
```
161-
应改为:
140+
正例:✅
162141
```sql
163-
SELECT t0.user_name
164-
FROM user to WHERE t0.gmt_create < FROM_UNIXTIME(CURDATE());
142+
SELECT t0.trade_id
143+
FROM trade_order t0
144+
WHERE t0.gmt_create >= DATE(NOW())
165145
```
166146

167-
##### 6、`varchar`查询性能比 ~~`bigint`~~ 好:
168-
> 因为 在 ~~`bigint`~~ 类型字段上会`全表扫描`,而在 `varchar` 上每个字符判断会走`索引`,这样尽量避免全表扫描。
169-
170-
##### 7、小数类型使用 `decimal`,禁止使用 ~~`float`~~ 与 ~~`double`~~:
171-
> ~~`float`~~ 与 ~~`double`~~存储数据时,可能会损失精度,进而判断的时候导致结果不准,`强制` 使用 `decimal` 数据类型。
172-
173-
##### 8、表达`是否`的概念时,字段使用`is_`开头,数据类型使用 `unsigned tinyint`类型,`1`表示`是`,`0`表示`否`。
174147

175-
##### 9、`任何非负数`都必须声明为`unsigned`类型:
176-
> 比如年龄、状态码等等
177148

178-
##### 10、如果存储的`字符串长度几乎相等`,`必须`使用`char`定长字符串类型:
179-
> 比如 11 位长度的手机号。
180-
181-
##### 11、有时候是不需要建索引:
182-
> 性别字段、状态,这种不同值很少的字段是不需要建索引的。
149+
#### 6、`组合索引` 的 `最左前缀原则`:
183150

184-
##### 12、单表行数超过 `500万行` 或者单表容量超过 `2G`,才推荐分表;
185151

186-
##### 13、进行 `UPDATE` 或 `DELETE` 时,必先 `SELETE`,避免出现`误删`数据;
187152

188-
##### 14、`组合索引` 的 `最左前缀原则`:
189153
> 建立 `(id_sex_city)组合索引`会自动建立 `id`、`id_sex`和`id_sex_ciry`三个索引。所以下面三个语句都会 `命中索引`:
154+
190155
```sql
191156
-- statement1:
192157
SELECT t0.user_name
@@ -203,7 +168,9 @@ SELECT t0.user_name
203168
FROM user t0
204169
WHERE t0.id = '1001';
205170
```
171+
206172
> 但下列语句 ~~`不会命中索引`~~,因为根据`组合索引`的`最左前缀原则`只会生成靠左、逐渐递进的索引:
173+
207174
```sql
208175
-- statement4:
209176
SELECT t0.user_name
@@ -225,14 +192,68 @@ WHERE t0.ciry = 'Shanghai';
225192

226193
> (组合索引、复合索引、联合索引都是一个意思)。
227194
228-
##### 15、尽量使用不要让数据库帮我们做数据类型转换:
195+
196+
197+
#### 7、关于 NUMBER 类型的字段不加单引号也会走索引(亲测有效):
198+
229199
```sql
230-
SELECT t0.user_name FROM user t0 WHERE t0.user_id = 1000000378;
200+
SELECT t0.product_id
201+
FROM product t0
202+
WHERE t0.product_id = 201805010001;
231203
```
232-
上述语句会扫描全表,应改为下面代码:
204+
205+
(前提user_id字段加了索引)上面和下面的语句都会走索引:
233206

234207
```sql
235-
SELECT t0.user_name FROM user t0 WHERE t0.user_id = '1000000378';
208+
SELECT t0.product_id
209+
FROM product t0
210+
WHERE t0.product_id = '201805010001';
236211
```
237212

238-
##### 16、对于多表 `JOIN` 时的 `ON` 条件中`字段类型`一定要一致,否则也不会命中索引.
213+
214+
215+
#### 8、对于多表 `JOIN` 时的 `ON` 条件中`字段类型`一定要一致,否则也不会命中索引.
216+
217+
218+
219+
#### 9、`varchar`查询性能比 ~~`bigint`~~ 好(亲测有效):
220+
221+
> 因为 在 ~~`bigint`~~ 类型字段上会`全表扫描`,而在 `varchar` 上每个字符判断会走`索引`,这样尽量避免全表扫描。
222+
223+
224+
225+
#### 10、小数类型使用 `decimal`,禁止使用 ~~`float`~~ 与 ~~`double`~~:
226+
> ~~`float`~~ 与 ~~`double`~~存储数据时,可能会损失精度,进而判断的时候导致结果不准,`强制` 使用 `decimal` 数据类型。
227+
228+
229+
230+
#### 11、表达`是否`的概念时,字段使用`is_`开头,数据类型使用 `unsigned tinyint`类型,`1`表示`是`,`0`表示`否`。
231+
232+
233+
234+
#### 12、`任何非负数`都必须声明为`unsigned`类型:
235+
> 比如年龄、状态码等等,这样最大容量正值会扩大一倍。
236+
237+
238+
239+
#### 13、如果存储的`字符串长度几乎相等`,`必须`使用`char`定长字符串类型:
240+
> 比如中国大陆 11 位长度的手机号。
241+
242+
243+
244+
#### 14、有时候是不需要建索引:
245+
> 性别字段、状态,这种不同值很少的字段是不需要建索引的。
246+
247+
248+
249+
#### 15、单表行数超过 `500万行` 或者单表容量超过 `2G`,才推荐分表;
250+
251+
252+
253+
#### 16、进行 `UPDATE` 或 `DELETE` 时,必先 `SELETE`,避免出现`误删`数据;
254+
255+
256+
257+
:sparkles:更多内容请参考《阿里巴巴Java开发手册》和《唯品会Java开发手册》。
258+
259+
#####

0 commit comments

Comments
 (0)