百万级数据库优化

女爷i 2024-02-18 22:11 20阅读 0赞

项目说明  
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。  
2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描  
3、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。  
案例分析：  
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描  
案例分析：  
案例分析2：  
5.in 和 not in 也要慎用，否则会导致全表扫描  
案例分析  
6、like模糊全匹配也将导致全表扫描  
案例分析

7、在 where 子句中使用参数，是不会导致全表扫描。  
8、在 where 子句中对字段进行表达式操作，是不会导致全表扫描。不过查询速度会变慢，所以尽量避免使用。  
9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。  
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。  
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用（这个在mysql中不对），并且应尽可能的让字段顺序与索引顺序相一致。  
12.不要写一些没有意义的查询，如需要生成一个空表结构：

## 项目说明 ##

有三张百万级数据表

（ex\_subject\_point）9,316条数据

（ex\_question\_junior）2,159,519条数据 有45个字段

（ex\_question\_r\_knowledge）3,156,155条数据

数据库为：mysql （5.7）

## 1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 ##

### 案例分析： ###

SELECT ex_question_junior.QUESTION_ID
    FROM ex_question_junior 
    WHERE ex_question_junior.GRADE_ID=15

执行时间：17.609s （多次执行，在17s左右徘徊）

优化后：给GRADE\_ID字段添加索引后

执行时间为：11.377s（多次执行，在11s左右徘徊）

备注：我们一般在什么字段上建索引？

这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：

a、字段出现在查询条件中，并且查询条件可以使用索引；

b、语句执行频率高，一天会有几千次以上；

c、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？

这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：

小表(记录数小于10000行的表)：筛选比例<10%；

大表：(筛选返回记录数)<(表总记录数\*单条记录长度)/10000/16

单条记录长度≈字段平均内容长度之和+字段数\*2

以下是一些字段是否需要建B-TREE索引的经验分类：

![1406993-20180712193844726-751607431.png][]

## 2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描 ##

select id from t where num is null

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num = 0

### 案例分析： ###

在mysql数据库中对字段进行null值判断，是不会放弃使用索引而进行全表扫描的。

![70][]

SELECT ex_question_junior.QUESTION_ID
    FROM ex_question_junior 
    WHERE IS_USE is NULL

执行时间是：11.729s

![70 1][]

SELECT ex_question_junior.QUESTION_ID
    FROM ex_question_junior 
    WHERE IS_USE =0

执行时间是12.253s

时间几乎一样。

## 3、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。 ##

### 案例分析： ###

在mysql数据库中where 子句中使用 != 或 <> 操作符，引擎不会放弃使用索引。

EXPLAIN
    SELECT ex_question_junior.QUESTION_ID
    FROM ex_question_junior 
    WHERE ex_question_junior.GRADE_ID !=15

![70 2][]

执行时间是：17.579s

![70 3][]

执行时间是：16.966s

## 4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描 ##

### 案例分析： ###

GRADE\_ID字段有索引，QUESTION\_TYPE没索引

![70 4][]

执行时间是：11.661s

优化方案：

通过union all 方式，把有索引字段和非索引字段分开。索引字段就有效果了

![70 5][]

执行时间是：11.811s

但是，非索引字段依然查询速度会很慢，所以查询条件，能加索引的尽量加索引

## 5.in 和 not in 也要慎用，否则会导致全表扫描 ##

### 案例分析 ###

注：在mysql数据库中where 子句中对索引字段使用 in 和 not in操作符，引擎不会放弃使用索引。

![70 6][]

注：在mysql数据库中where 子句中对不是索引字段使用 in 和 not in操作符，会导致全表扫描。

![70 7][]

### 案例分析2： ###

用between和in的区别

SELECT ex_question_junior.QUESTION_ID
    FROM ex_question_junior
    WHERE ex_question_junior.QUESTION_TYPE  IN(1,2,3,4)

执行时间为1.082s

SELECT ex_question_junior.QUESTION_ID
    FROM ex_question_junior
    WHERE ex_question_junior.QUESTION_TYPE between 1 and 4

执行时间为0.924s时间上是相差不多的

### 案例分析3： ###

用exists 和 in区别：结论

1. in()适合B表比A表数据大的情况（A<B）

select \* from A

where id in(select id from B)

2. exists()适合B表比A表数据小的情况(A>B)

select \* from A

where exists(

select 1 from B where B.id = A.id

)

3、当A表数据与B表数据一样大时,

in与exists效率差不多,可任选一个使用.语法

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

ex\_question\_r\_knowledge(A)表数据量大，ex\_subject\_point表数据量小(B)(A>B)

用exists适合

SELECT *
    FROM ex_question_r_knowledge
    WHERE ex_question_r_knowledge.SUBJECT_POINT_ID IN
    (
        SELECT ex_subject_point.SUBJECT_POINT_ID
        FROM ex_subject_point
        WHERE ex_subject_point.SUBJECT_ID=7
    )

执行时间是：38.404s

SELECT *
    FROM ex_question_r_knowledge
    WHERE  exists
    (
        SELECT 1
        FROM ex_subject_point
        WHERE ex_subject_point.SUBJECT_ID=7
        AND ex_subject_point.SUBJECT_POINT_ID = ex_question_r_knowledge.SUBJECT_POINT_ID
    )

执行时间是：13.537s

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

ex\_subject\_point表数据量小(A),ex\_question\_r\_knowledge(B)表数据量大(A<B)

用in适合

SELECT * 
    FROM ex_subject_point 
    WHERE ex_subject_point.SUBJECT_POINT_ID IN(
     　 SELECT ex_question_r_knowledge.SUBJECT_POINT_ID 
    　　FROM ex_question_r_knowledge 
        WHERE ex_question_r_knowledge.GRADE_TYPE=2 )

执行时间是：1.554s

SELECT *
        FROM ex_subject_point
        WHERE  exists(
        SELECT ex_question_r_knowledge.SUBJECT_POINT_ID
        FROM ex_question_r_knowledge
        WHERE ex_question_r_knowledge.GRADE_TYPE=2
        AND ex_question_r_knowledge.SUBJECT_POINT_ID= ex_subject_point.SUBJECT_POINT_ID
    )

执行时间是：11.978s

## 6、like模糊全匹配也将导致全表扫描 ##

### 案例分析 ###

EXPLAIN

SELECT \*

FROM ex\_subject\_point

WHERE ex\_subject\_point.path like "%/11/%"

![70 8][]

若要提高效率，可以考虑全文检索。lucene了解一下。或者其他可以提供全文索引的nosql数据库，比如tt server或[MongoDB][]

昨天晚上突发奇想，like 模糊全匹配，会导致全表扫描，那模糊后匹配和模糊前匹配也会是全表扫描吗？

like模糊后匹配，不会导致全表扫描

![70 9][]

like模糊前匹配，会导致全表扫描

![70 10][]

MY SQL的原理就是这样的，LIKE模糊全匹配会导致索引失效，进行全表扫描；LIKE模糊前匹配也会导致索引失效，进行全表扫描；但是LIKE模糊后匹配，索引就会有效果。

## 7、在 where 子句中使用参数，是不会导致全表扫描。 ##

### 案例分析 ###

![1406993-20180714192827284-1648869483.png][]

## 8、在 where 子句中对字段进行表达式操作，是不会导致全表扫描。不过查询速度会变慢，所以尽量避免使用。 ##

### 案例分析 ###

![70 11][]

执行时间是1.064s

### 优化方案 ###

SELECT ex_question_junior.QUESTION_ID
    FROM ex_question_junior 
    WHERE ex_question_junior.QUESTION_CHANNEL_TYPE =4/2;

执行时间是0.012s

## 9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。 ##

### 案例分析 ###

![1406993-20180714192844325-345780832.png][]

### 优化方案 ###

SELECT *
    FROM ex_subject_point 
    WHERE CREATE_DT >= "2018-05-31"
    AND CREATE_DT < "2018-07-01"

但是EXPLAIN一下，发现这样还是全表扫描的

![1406993-20180714192853022-239981510.png][]

难道是因为日期字段索引没有效果吗？还是因为用了>=和<运算符号？

来验证一下

缩小查询范围，发现索引是有效果的。所以不是日期字段的问题。

![1406993-20180714192901474-707243761.png][]

换个字段查询，用>=和<运算符号，索引还是有效果的。但那是什么原因呢？

![1406993-20180714192911542-1913535983.png][]

后来去网上查找了资料，原因是查询数量是超过表的一部分,mysql30%,oracle 20%（这个数据可能不准确，不是官方说明，仅供参考），导致索引失效。

## 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 ##

例子请看第8点和第9点。

## 11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用（这个在mysql中不对），并且应尽可能的让字段顺序与索引顺序相一致。 ##

### 案例分析 ###

复合索引字段：PATH,PARENT\_POINT\_ID

![1406993-20180714192920045-98942610.png][]

调换WHERE子句中的条件顺序。发现还是可以使用索引的

![1406993-20180714192927216-1389970630.png][]

复合索引只查询第一个字段，是有效果的

![1406993-20180714192934239-1967762304.png][]

复合索引只查询第二个字段，发现索引没有效果了。

![1406993-20180714192941077-786151740.png][]

## 12.不要写一些没有意义的查询，如需要生成一个空表结构：（一般开发也不会这么无聊啦，在正式的项目上写这种玩意） ##

select col1,col2 into \#t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table \#t(…)

[1406993-20180712193844726-751607431.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bb544958099d483c81a23bb136e07c71.png
[70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ca0bce08d4fd46bc9896863dae76db70.png
[70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5d8bf592ffff4f77bbb36017599e04b1.png
[70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/86fb209180bd4cf7b437046f9f4a9558.png
[70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/7bec77e21d6c4183a2efeee8e46e2c75.png
[70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/defbbbec2e6f4a6bb9050cb9d8b244cb.png
[70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6b4eea86fdda45048407c1c3ca269242.png
[70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5812c9ba3630480aa92f3774cbcc05b3.png
[70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c8ec8f34ce474569be3a1d79cadc589c.png
[70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/324325049cb545b1b21472285381e461.png
[MongoDB]: http://lib.csdn.net/base/mongodb
[70 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f3c8cf1dae384ecfbb5488e080d33f06.png
[70 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/93bb11d5be464802aa2a38898cef6db5.png
[1406993-20180714192827284-1648869483.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e96b16df58e24e63bfddd7e00636592b.png
[70 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/0d132c8e02e44d24902f8c5195e355bc.png
[1406993-20180714192844325-345780832.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/353d6c70b605409390d086f1154515ff.png
[1406993-20180714192853022-239981510.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b4ba44897a544660b80b2a2738ab5d26.png
[1406993-20180714192901474-707243761.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/175bd4c453d14bbeb59e989ffbdc6772.png
[1406993-20180714192911542-1913535983.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/66cfc52872d04bf5afbb0fec5d189aa9.png
[1406993-20180714192920045-98942610.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/40ddae53b2e8421dbfdd50d7f88b3088.png
[1406993-20180714192927216-1389970630.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/93b33979cdb44f56ae9636e3c002ce57.png
[1406993-20180714192934239-1967762304.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d1888ec503f14f78a2642030ae16e7ed.png
[1406993-20180714192941077-786151740.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b63dca81951a43cbb127c1af98ab4c2d.png