数据库sql优化总结之2-百万级数据库优化方案+案例分析

怼烎@ 2022-05-20 07:23 195阅读 0赞

**目录**

项目背景

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）

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

## 案例分析 ##

![70][]

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

## 案例分析 ##

![70 1][]

执行时间是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子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。 #

## 案例分析 ##

![70 2][]

## 优化方案 ##

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

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

![70 3][]

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

来验证一下

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

![70 4][]

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

![70 5][]

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

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

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

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

## 案例分析 ##

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

![70 6][]

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

![70 7][]

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

![70 8][]

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

![70 9][]

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

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

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

create table \#t(…)

参考：

https://mp.weixin.qq.com/s?\_\_biz=MzIxMjg4NDU1NA==&mid=2247483684&idx=1&sn=f5abc60e696b2063e43cd9ccb40df101&chksm=97be0c01a0c98517029ff9aa280b398ab5c81fa1fcfe0e746222a3bfe75396d9eea1e249af38&mpshare=1&scene=1&srcid=0606XGHeBS4RBZloVv786wBY\#rd

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

作者：小虚竹  
欢迎任何形式的转载，但请务必注明出处。  
限于本人水平，如果文章和代码有表述不当之处，还请不吝赐教。

我不是个伟大的程序员，我只是个有着一些优秀习惯的好程序员而己

[70]: /images/20220520/37e45cfedfff4a3dae6ea766bbcc2ebd.png
[70 1]: /images/20220520/aebb1f390f7e4b97bc009f4dcb832852.png
[70 2]: /images/20220520/3a067e54a80b4ca6b877d0283d0cfb87.png
[70 3]: /images/20220520/9873fc1587974dddae7b795eee606a71.png
[70 4]: /images/20220520/8025bd78854f485b8d0d95bac0a2e822.png
[70 5]: /images/20220520/7627fcfa70454f8baa0c028180ffc01f.png
[70 6]: /images/20220520/0dfff1e2c3a342979ade9ec2209f55ab.png
[70 7]: /images/20220520/8e0fbbf4eb5947c293676e0f56bca4d9.png
[70 8]: /images/20220520/bd0972b84dbd4b469c8321b1d83be30b.png
[70 9]: /images/20220520/e2cbdb7073b74b12aa57445ad72758c1.png