MySQL数据库优化-CSDN博客

一、SQL语句及索引的优化

1、索引的底层实现原理及优化

（1）MySQL使用的是B+Tree，其特点如下：

所有叶节点包含全部关键字及指向相应记录的指针，而且叶节点中将关键字按大小顺序排列，并且相邻叶节点按大小顺序相互链接起来。
所有分支节点（可看做索引的索引）中仅包含它的各个子节点（即下一级的索引块）中关键字的最大值即指向其子节点的指针。
B+树中，叶节点包含信息，所有非叶结点仅起到索引作用，非叶节点中的每个索引项只含有对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址。
叶节点包含了所有的关键字，即在非叶节点出现的关键字也会出现在叶子节点中。

B+树有两个头指针，一个指向根节点，另一个指向关键字最小的叶节点。B+树进行两种查找运算：从最小关键字开始的顺序查找，另一种从根节点开始的多路查找。

原理：叶子节点是按关键字大小顺序排列，且增加了指向下一个叶子节点的指针。

优化：InnoDB建议大部分表使用默认的自增的主键作为索引

二、数据表结构的优化

尽量避免耗时操作
带有distinct、union、intersect、order by的SQL语句会启动SQL引擎执行耗费资源的排序功能。distinct需要一次排序操作，而其他的至少需要两次排序操作。
如果无需排除重复值或是操作集无重复则用UNION ALL， UNION更费事（因为要比较）
UNION因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用UNION ALL，如xx模块的某个查询程序就曾经存在这种情况，见，由于语句的特殊性，在这个脚本中几个子集的记录绝对不可能重复，故可以改用UNION ALL）连接操作
避免在WHERE子句中使用in，not in，or 或者having。
可以使用 exist 和not exist代替in和not in。可以使用表链接代替 exist。Having可以用where代替，如果无法代替可以分两步处理
不要在建立的索引的数据列上进行下列操作:
- 避免对索引字段进行计算操作
- 避免在索引字段上使用not，<>，!=
- 避免在索引列上使用IS NULL和IS NOT NULL
- 避免在索引列上出现数据类型转换
- 避免在索引字段上使用函数
- 避免建立索引的列中使用空值。
查询的模糊匹配
应尽量避免在一个复杂的查询里面使用like ‘%parm1%’–红色标识位置的百分号会导致相关列的索引无法使用，最好不要用。
避免使用临时表
(1)除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;
(2)大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。
尽量少做重复的工作
尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：
A、控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的
B、减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。
C、杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。
D、合并对同一表同一条件的多次UPDATE，比如
UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’
UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’
这两个语句应该合并成以下一个语句
UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’
WHERE EMP_ID=’ VPA30890F’
E、UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。
F、不要写一些没有意义的查询，比如
SELECT * FROM EMPLOYEE WHERE 1=2