你都是会点啥技术（五）--- 数据库

你都是会点啥技术（五）— 数据库

写在前面的话：还记得2018年的时候开发的项目上线，经过大概一个月，因为数据量增加，造成项目查询页面的延迟，因为项目使用的群体是固定的，所以当时提出来后并没有着力解决，不过我一直对数据库优化这块耿耿于怀，抽出时间来基于MYSQL学习一下！

1.概念知识补充

数据库系统图：

2.查询优化技术分类

（1.）查询重用：①查询结果重用；②查询计划重用。
（2.）查询重写规则：①基于关系型代数；②视图重写、子查询优化、等价位置重写、条件化简、外联接消除、联接消除、嵌套联接消除；③语义优化。
（3.）查询优化算法（基于代价估算模型）：①单表扫描算法（全表扫描、索引扫描、行定位扫描）；②两表连接算法（嵌套联接算法、归并连接算法、哈希连接算法）；③多表连接算法（贪婪）。
（4.）并行查询优化：利用分解查询SQL，进行并行计算查询，提高查询目的。
（5.）分布式查询优化：利用分布式系统优化查询。

3.数据库调优

数据库调优包括数据库管理系统优化和查询优化，主要目的是使数据库有更高的吞吐量和更高的响应时间。

4.差不多了，实际应用开始走起！

第一招：explain：

显示了mysql如何使用索引来处理语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句!我们以select语句为例子
详情请参考：https://www.cnblogs.com/yycc/p/7338894.html

第二招：子查询优化：
1.子查询合并：

原理：把多次表扫描、多次连接减少为低次表扫描和低次连接。

/*
*优化前
*/
 SELECT * FROM t_user WHERE zhuhuid < 20 AND (
 EXISTS (SELECT id FROM t_zhuhu WHERE id<15 AND id=5) OR
 EXISTS (SELECT id FROM t_zhuhu WHERE id<15 AND id=10));
/*
*优化后
*/
 SELECT * FROM t_user WHERE zhuhuid < 20 AND (
 EXISTS (SELECT id FROM t_zhuhu WHERE id<15 AND id=5 OR id=10))

2.子查询展开：

原理：将子查询上拉到父查询，实质是把某些子查询重写为等价的多表连接操作，这样有关的的访问路径、连接方法、连接顺序可能被有效使用、使得查询语句的层次尽可能减少。

/*
*优化前
*/
SELECT * FROM t_user a,(SELECT * FROM t_zhuhu WHERE 
t_zhuhu.id>10) b WHERE a.zhuhuid<10 AND b.id<20
/*
*优化后
*/
SELECT * FROM t_user a,t_zhuhu b WHERE a.zhuhuid<10
 AND b.id<20 AND b.id>10

3.聚集子查询消除

原理：利用SQL函数消除子查询的优化技术。

SELECT * FROM t_user a WHERE a.zhuhuid > (SELECT AVG(b.id)
 FROM t_zhuhu b)

能够做子查询优化格式要求：
1.简单Select查询中的子查询，
2.带有DISTINCT、ORDERBY、LIMIT操作的简单SELECT查询中的子查询。
不能做子查询优化的格式：
1.带有UNION操作
2.带有GROUPBY、HAVING、聚集函数
3.使用ORDERBY中带有LIMIT
4.内表、外表的个数超过MySQL支持的最大表的连接数（mysql最大连接数63）。

第三招：视图重写

定义：视图是数据库中基于表的一种对象，把对表的查询固化，这种固化是视图。
原理：MySQL支持简单视图优化技术，MySQL把视图转为对基表的查询，然后进行类似子查询的优化。但不支持复杂视图（带有GROUP BY、Oreder By 等操作称为复杂视图）优化。

/*
 * 创建视图
 */
CREATE VIEW t_view AS SELECT * FROM t_user   
/*
* 未使用视图
*/
SELECT a.id FROM (SELECT id,zhuhuid FROM t_user) as a 
WHERE a.zhuhuid>10
/*
* 使用视图
*/
SELECT id FROM t_view WHERE zhuhuid>10

第四招：等价谓词重写

原理：把逻辑表达式重写成等价的且效率更高的形式，提高查询执行效率。

1.LIKE规则重写

如果name列上存在索引，则可以利用索引提高查询效率。

/*
* 重写前
*/
name LIKE 'abc'
/*
* 重写后
*/
name = 'abc'

2.BETWEEN-AND规则

如果sno上建立了索引，则可以用索引扫描代替原来BETWEEN-AND谓词限定的全表扫描，从而提高了查询的效率。

/*
* 重写前
*/
sno BETWEEN 10 AND 20
/*
* 重写后
*/
sno >=10 AND sno <=20

3.IN转换OR规则

看实际情况使用，如果age列上存在索引，则转换后查询效率会提高。

/*
* 重写前
*/
age IN (8,12,21)
/*
* 重写后
*/
age=8 OR age=12 OR age=21

4.NOT规则

如果col_1建立了索引，则可以用索引扫描代替原来的全表扫描，从而提高查询的效率

/*
* 重写前
*/
NOT (col_1 != col_2)
/*
* 重写后
*/
col_1 = col_2

5.OR重写并集规则

可以分别利用列sex和age上的索引，进程索引扫描，执行UNION操作获得最终结果。

/*
* 重写前
*/
SELECT * FROM student WHERE (sex='f' and age>15) OR age>18
/*
* 重写后
*/
SELECT * FROM student WHERE sex='f' and age>15
UNION
SELECT * FROM student WHERE age>18

第五招：条件化简
1.把HAVING条件并入WHERE条件，前提是SQL中不存在GROUPBY条件或聚集函数的情况下，才能将HAVING条件与WHERE条件合并。

/*
*例子
*/
SELECT * FROM t1 WHERE a1>1 having a2=</