MySQL数据库优化（一）

数据库性能下降可能的原因

• I/O吞吐量小，形成了瓶颈效应
• CPU性能差
• 内存不足，磁盘空间不足
• 没有检索或者没用到检索
• 查询语句性能差，没有优化
• 检索返回的数据量太大
• 返回了不必要的字段
• 锁或者死锁
• 配置参数没有优化

如果是服务器性能问题，可以使用这些指令进行查询：top命令、htop命令、free命令和df命令等等。

SQL执行顺序

如果我们想知道我们的SQL操作为什么性能低下，我们就需要对SQL执行顺序有所了解。

你看见SQL是这样的：

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_list>
LIMIT <limit_number>

系统看见SQL是这样的：

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT <select_list>
DISTINCT <select_list>
ORDER BY <order_by_list>
LIMIT <limit_number>

1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
2. ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
7. HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
11. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

常见表连接

左连接

获取以table1为基础的所有数据

mysql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id

右连接

获取以table2为基础的所有数据

mysql> SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id

内连接

获取两张表的交集数据

mysql> SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id

左独查询

获取只有table1有的数据

mysql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL

右独查询

获取只有table2有的数据

mysql> SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id WHERE t1.id IS NULL

全连接

获取两张表的全部数据，MySQL没有FULL关键字，只能用别的方法查询

mysql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id UNION SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id

注：UNION有去重功能，而UNION ALL则没有。

非交集查询

获取两张表自己独有的数据

mysql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL UNION SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id WHERE t1.id IS NULL