SQL优化笔记

最新推荐文章于 2024-01-11 20:12:27 发布

高新普惠_搬砖手

最新推荐文章于 2024-01-11 20:12:27 发布

阅读量794

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/dananhai381/article/details/32335619

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

优化思路

1. 首先从业务角度考虑，是否使用数据库；

2. 统计在一个周期内sql的调用比例+慢日志中的记录

3. 优化方式：索引、优化参数、加硬件、垂直分表、水平分表

Sql优化

执行顺序

Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。

这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快

分析查询语句

使用explain 对select语句进行分析，如

EXPLAIN SELECT * FROM account WHERE id=10，得到结果：

Type代表时间复杂度，此处为常量；key为primary代表是主键索引

All代表全表扫描，key为null代表没有索引

对于update的分析，可以将update改成select ，from后边不变

分析慢查询

在运行环境中，对各种查询进行explain分析不太现实，而且你不知道什么时候去分析

位置： my.cnf的log-slow-queries 得到慢sql位置

使用mysqldumpslow、 mysqlsla(第三方统计统计)查看慢查询，

Msyqldumpslow

/path/mysqldumpslow -s c -t 10/database/mysql/slow-log

这会输出记录次数最多的10条SQL语句，其中：

-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

Mysqlsla

mysqlsla –lt slow /data/var/mysql_slow.log

Mysqlreport

第三方的mysql的状态报告工具，对showstatus的结果执行一系列后期处理，以可读性好的方式展现。

操作符优化

in or 和 not in

子句常会使用临时表使索引失效。

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况

推荐方案：

在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替； {}为连续值使用betweenand替换；离散值最频繁的放在前边

select num from a where num in (select num from b)

select num from a where exist( select * from b whereb.num=a.num)

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

NOTIN操作符

此操作是强列不推荐使用的，因为它不能应用表的索引。

推荐方案：

用NOT EXISTS 方案代替

子查询

尽量少使用子查询。方法：

1）使用联合查询，或者是外连接查询，并对链接字段建立索引。

2）如果不可避免，在子查询中尽量过滤较多的行

null 和 not null

不会应用索引，如：

select * from emp where dept_code is not null (不使用)
select * from emp where dept_code > 0 (使用)

推荐方案：

修改用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。

对该字段设置默认值替代空值，如 a==0

> <和 >= <=

如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

推荐方案：

使用 <=替换< >=替换>

!=

不会使用索引，而是全表扫描

select account_name from test where amount != 0 (不使用)
select account_name from test where amount > 0 (使用)

LIKE

如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。

推荐方案：

第一个字符尽量是确定的

实在不行考虑全文检索

UNION 和OR

OR经常会产生临时表。考虑把or的每个值改成一条一句，该子句中应该包含索引

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys union select* from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：

采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

Order by

尽量简化或避免对大型表的排序，以下是不好的： 1）order by 中没有索引中定义的列； 2）group by 或order by列的次序和索引中顺序不一致；3）排序的列

ORDER BY 子句只在两种严格的条件下使用索引：1）ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。2）ORDER BY中所有的列必须定义为非空。

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列。

字段

1. 优先选择数字型字段，而不是字符型（比较方便）

2. 使用varchar 和nvarchar 替代char和nchar（空间小）

3. 尽量减少事务执行时间，多commit

1）越小越好，如年龄使用tinyint ；

2）简单就好,多用整形、字符等基础的数据类型

整形：取决于范围，如int bigInt smallint tinyint mediumint

实数选择：精度，如float double decimal

字符串：enum/set char/varchar

时间类型： year/date(很少用)； datetime （有时区，8个字节，有缺省值，更有优势） timestamp（无时区，4个字节）

IP怎么存：转化为整形存储， inet_aton ,inet_ntoa

索引

使用原则

l 在经常进行连接，但是没有指定为外键的列上建立索引

l 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。

l 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。

l 数据库表更新大量数据后，删除并重建索引可以提高查询速度。

1. 索引控制在5个以内

2. 如果数据被多次查询，将较大的字段放入另一张表中，分两次获取，类似于列表和详情的感觉；

3. 不要在select 结果上直接做运算

4. 建立索引不光要考虑select，还需要考虑update insert

where子句

在子句上创建组合索引：（索引包含多列）；使用最频繁、最大减少结果集的列作为索引前导列；

where子句第一个字段是索引第一个，后边的不要求

order by 和group by中也可使用

如索引 key'normal_key' （key1,key2,key3）对以下查询都能应用索引

select * from keyt wherekey1=1 ...where key1=1 and key2=2

select * from key_t order bykey1,key2,key3

函数处理、计算的字段

进行了显式或隐式的运算的字段不能进行索引，如：

select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用)

elect * from emp where hire_date = to_char('20080411','yyyymmdd') (使用)

select * from record where amount/30 <1000（不使用）

select * from record where amount <1000*30 （使用）

substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bhlike ‘5400%’

trunc(sk_rq)=trunc(sysdate)，优化处理：sk_rq>=trunc(sysdate)and sk_rq<trunc(sysdate+1)

ss_df+20>50，优化处理：ss_df>30

‘X’|| hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’

sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5

索引的权衡

1. 关键是select和更新的比例，在mysqlreport的DMS部分

2. 该列重复值太多

3. 尽可能减少更新clusted index列

聚集索引

表中只有一个，用于

1）包含大量非重复值的列。

2）使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <= group by order by。

3）返回大型结果集的查询。如select date,sum(amount) from record group by date

反范式化设计

在表中增加冗余信息，违反第三范式，修改时需要考虑冗余信息的一致性：

1. 修改行为比较罕见

2. 影响范围比较小

3. 允许短时间的数据不一致

4. 通过定期的数据整理自动修复不一致的数据

使用临时表

把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：
         SELECT cust.name，rcvbles.balance，……other columns   FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id   AND rcvblls.balance> 0   AND cust.postcode> “98000”  ORDER BY cust.name

         如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序：
SELECT cust.name，rcvbles.balance，……other columns   FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id   AND rcvblls.balance> 0   ORDER BY cust.name  INTO TEMP cust_with_balance
         然后以下面的方式在临时表中查询：
         SELECT ＊ FROM cust_with_balance   WHERE postcode> “98000”

注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。