SQL优化理论篇(MySQL随笔)

SQL优化的第一要义(个人理解)：想方设法命中索引！

一、索引的概述

1. 什么是索引？

举个例子：查字典
	我们一般查字典是先根据拼音或者偏旁部首去查目录，找那个字所在的页，然后再去对应的页上去找具体的字。
	当然，我们也可以从第一页开始，一页一页的去找，直到找到我们想查的字为止，这当然可行，但是相比之下，平均效率肯定是不如前一种查询方式的。

	这第一种查询方式就类似SQL语句走索引的过程，第二种查询方式就类似全表扫描。

	可以简单的理解一下，索引就是数据库表的目录，某一个具体索引就是为数据库表中的某些字段创建的一个目录。
	(虽然比较形象，但是老感觉怪怪的，暂且先这样理解)

2. 索引的种类？

a.主键索引：主键自带索引（不需要手动创建，在建表时数据库自己就创建好了）
b.普通索引：根据需要创建的普通单列索引
c.联合索引：同时创建多个列的索引

3. 为什么索引这么快？

使用了特殊的数据结构———— B+树

二、索引的数据结构（超链接----数据结构学习神器----建议收藏）

1. 为什么不是线性表？

线性顺序表(数组)：索引查询快、增删慢
线性链式表(链表)：增删快、查询慢

2. 为什么不是哈希表？

哈希表：都挺好，可惜无序，不方便范围查询

3. 为啥不是其它树？

结合上面两种数据结构，索引大概的要求————基本操作不能有短板，要有序，方便范围查询

a.查找树：查询速度跟树的高度有关，而树的高度跟插入数据的顺序有关(不会自旋),这很不好。
		  可以自己去体验一下(用上面的神器，不同顺序插入相同的数据，得到的树的高度可能不同)
		  
b.AVL(平衡二叉树)：在查找树的基础上，增加了自旋，那么相同的数据，得到的树的高度是唯一的。
				  但是，一个节点只能放一条数据，那么数据库里的数据多了之后，树的高度也会非常的高，那树的高度又会影响到树的查询速度，还是欠点意思。

c.B树：好家伙，一个节点一条数据太少了，那我就放多条吧，毋庸置疑，B树在一定程度上缓解了树的高度问题。
	   但是，还有一个致命的问题————范围查找
	   例如：where id >=6;然后用上面的神器，建个B树，去找找试试，能找，麻烦，不太好

4. 为啥是B+树？

B+树：为索引而生，在B树的基础上做了改进
	*只在叶子节点中存放整条数据，其他节点(暂且称呼为索引节点)中，只存放建立索引的字段
		*好处：在索引节点大小固定的情况下，B+树在一个索引节点中能存放更多的记录(叉更多了，树更矮了，这很好)
		*缺点：占用了更多的空间，索引字段重复(时间更重要！)
		
	*在叶子节点中添加指向下一个兄弟节点的指针（ni了个大ce，范围查找的问题完美解决）

三、MySQL常用的两大存储引擎(虽然都是B+树，但是还是有区别)
[在索引方面粗浅的比较一下]

1. innoDB引擎(用得多)：聚集索引

聚集索引：索引树的叶子节点直接存放数据行（一个文件）

其实也只有主键索引是聚集索引，其它的索引叶子节点存放的是对应的主键(键值对)，因此是非聚集索引
（有什么好处呢？思考方向：不这么做会怎么样？）

2. MyISAM引擎：非聚集索引

非聚集索引：索引树的叶子节点存放数据行的地址（数据一个文件，索引树一个文件）

3. 一些衍生问题

a. InnoDB的非聚集索引的叶子节点为什么只存放主键而不存放整个数据行？
	如果存放完整的数据行的话，首先造成数据冗余，虽然性能提升了，但是需要更大的存储空间；
	其次，之后的索引维护不方便，增加删除还好，但是修改的话，那么所有的索引树都需要修改。

b. 整型自增的主键有什么好处？
	首先，主键会有主键索引树，按照索引查找需要进行比较操作，而整型之间的比较，无疑是最方便的；
	其次，如果主键是乱序的，那么，索引树需要更多的自旋操作，来保持整体的平衡性，因此，自增的有序的主键，开销最小。

四、联合索引和覆盖索引

1. 最左前缀原则

现在假设有一张表table(id,a,b,c,d,e)，主键id
表中有一个联合索引树index_a_b_c=(a,b,c)->id//字段a,b,c的联合索引，叶子节点指向对应id

那么这棵索引树的排序顺序是先按a从小到大排，a相同时按b从小到大，b也相同时按c从小到大
这时可以想象，如果在不确定a的情况下去找b，那么b其实是无序的(想象一下，在一个无序的树中，找一个值)，
这时候说b不满足最左前缀原则

最左前缀原则：如果要找c,还要命中索引，就必须保证联合索引中的c字段的左前缀，
			 必须已经明确，否则不能命中索引（要全表扫描）

3. 联合索引（要满足最左前缀原则才能完全命中）

现在执行
select * from table where a=? and b=? and c=?;// ?代表任意指定的值
select * from table where a=? and b=?;
select * from table where a=?;
以上三条语句都是可以命中索引index_a_b_c的，因为满足最左前缀法则
select *from table where c=? and a=? and b=?;
也可以命中索引，因为MySQL有内部优化器，它会做一些调整，可以粗浅的理解成，经过内部优化器的优化，
实际上执行的还是select * from table where a=? and b=? and c=?;

select *from table where a=? and c=?;
a字段命中索引，但是c字段没有命中
select *from table where a=? and b>? and c=?;
a命中，b命中，c没有命中，因为b字段的查询结果是范围，在范围内c字段可能无序，但是b字段是命中索引的

4. 覆盖索引(SQL优化的常用操作)

覆盖索引，其实是一种操作方式，不是索引树

select c from table where a=? and b=?;
这就是覆盖索引，首先where条件可以走index_a_b_c索引树，
而且要查找的c字段在索引树中有，可以直接拿到（只走了一个索引）

select e from table where a=? and b=?;
这就不是覆盖索引，首先where条件可以走index_a_b_c索引树，
但是要查找的e字段在索引树中没有，只能在index_a_b_c中拿到id,
然后去主键索引树种，根据id，再拿到对应的e字段(走了两棵索引树)