SQL索引优化

海上的魔鬼船

已于 2022-08-15 09:17:28 修改

阅读量365

点赞数

文章标签： sql 数据库 java

于 2022-08-15 08:51:30 首次发布

本文链接：https://blog.csdn.net/youngbird1998/article/details/126339892

版权

问：执行时间长，等待时间长
可能是SQL语句太次，索引失效，关联查询太多join，服务器调优及参数设置（缓冲池）
SQL执行顺序：

FROM <LEFT_TABLE>
ON <JOIN_CONDITION>
<JOIN_TYPR> JOIN <RIGHT_TABLE>
WHERE 
GROUP BY 
HAVING
SELECT
DISTINCT
ORDER BY

JOIN详解：
在这里插入图片描述
最后两个由于MySQL不支持FULL OUTER JOIN，所以用 UNION 连接，即1和2的语句进行UNION得到6，3和4的语句进行UNION得到7。

索引
索引：排好序的快速查找数据结构；
优点：提高数据检索的效率，降低数据排序的成本；
缺点：会降低更新表的速度；

单值索引：一个索引只包含单个列，一个表可以由多个单值索引；
唯一索引：索引列的值必须唯一，允许有空值；
复合索引：即一个索引包含多个列；

CREATE [UNIQUE] INDEX indexname ON tablename(columnname(length));
ALTER table tablename ADD [UNIQUE] INDEX indexname (columnname(length));

索引结构：
BTree索引，Hash索引，full-text全文索引，R-Tree索引；

B+树与B树的不同在于：
（1）所有关键字存储在叶子节点，非叶子节点不存储真正的data
（2）为所有叶子节点增加了一个链指针

下图是BTree索引数据结构
在这里插入图片描述
索引性能分析：
Explain（执行计划）：
使用：EXPLAIN + sql语句；
作用：
1、表的读取顺序；
2、数据读取操作的操作类型；
3、哪些索引可以使用；
4、那些索引被实际使用；
5、表之间的引用；
6、每张表有多少行被优化器查询；

Join连接索引分析
单表分析
例：select id from A where c1 = 1 and c2 > 1 order by v1 desc limit 1;
建立联合索引（c1,c2,v1），但explain时候发现 type 是range，extra中使用using filesort，这需要优化；
产生原因：按照BTree索引工作原理，先排序c1，如果c1相同，排序c2，c2相同在排序v1，当c2字段在联合索引中处于中间位置，因为c2 > 1条件是一个范围值（range），MySQL无法利用索引在对后面的v1部分进行索引。所以建立（c1，v1）解决这个问题。

双表分析
左连接加在右表的索引，右连接加在左表的索引；
Left Join 条件用于确定如何从右表搜索行，左边数据一定有，所以右边数据一定要建索引。

三表分析
建索引和双表的原理相同；

总结
尽可能减少Join语句的NestedLoop的循环总次数，永远用小结果集驱动大的结果集；
优先优化NestedLoop的内层循环；
保证Join语句中被驱动表上Join条件字段已经被索引；

DML
DML（data manipulation language）数据操纵语言，比如SELECT、UPDATE、INSERT、DELETE；
DDL
DDL（data definition language）数据库定义语言，CREATE、ALTER、DROP等；
DCL
DCL（Data Control Language）数据库控制语言，grant,deny,revoke等；