MySQL原理以及SQL语句优化

一、索引

1. 索引本身也是一个文件，在磁盘存储，在使用时，从磁盘加载到内存！

2. 索引适用的时机：
   ①查询时，可以加快效率
   ②排序时，也可以使用索引上的顺序

不适用的时机：
①不经常查询的表或字段，不用建索引
②经常更新的表，不适合建索引

3. MySql使用的索引
   select * from xxx where id=1
   通常为id这列建立索引

   B-tree: 多路平衡树
   5.4之前使用B(balance)-tree
   每个节点存储： ①当前索引列的列值
   ②指向下一个节点的指针
   ③指向当前列所在磁盘块位置的指针
   5.4之后使用B+tree
   每个节点存储： ①当前索引列的列值
   ②指向下一个节点的指针
   最底层的叶子节点，存储指向当前列所在磁盘块位置的指针

   原因： 索引本身也是一个文件，在磁盘存储，在使用时，从磁盘加载到内存！
   数据量越大，索引文件越大，内存有限，一次性无法全部加载，分批加载！
   B+tree节点保存的数据小，每次加载的节点数多，减少文件的加载次数，避免索引查询时
   大量的磁盘IO，磁盘IO效率低！

4. 聚簇索引和非聚簇索引

聚簇索引： 索引中节点的顺序和真实数据的顺序是一致的！
什么样的字段适合做聚簇索引： 可以不连续，必须有序
非聚簇索引： 索引中节点的顺序和真实数据的顺序不一致的！

一、创建索引的时机

	select  xxx ④  from  xxx  join  xxx  on  xxx=xxx   ①
	where xxx    ②
	group by xxx  ③
	having xxx
	order by xxx  ⑤
	limit xx

①在关联表时，如果关联的字段创建了索引，可以提升关联表的效率
		on 后的字段可以创建索引，有外键关系的字段可以创建索引
		
②where后过滤的字段可以创建索引

③分组也可以使用索引

④ select后的字段也可以直接从索引上取值，而无需开表查询

⑤排序也可以直接参考索引上字段的顺序

二、Explain指标分析

id： id代表每次开表查询的优先级，id越大，优先级越高，id值越大，SQL越复杂，一般都是多层子查询嵌套！
id越小越好！

select_type(没有用) : 此次查询的类型，用来区分当前是简单查询，子查询等

table： 本次查询需要从哪张表取数据！

type(定性指标)：
All： ①表没有索引
②表有索引，但是无法使用
③有索引，但是索引失效

	避免all，让sql语句能够使用上索引！
	
	最好可以优化到ref级别，至少到range!

possible_keys: 可能用上的索引

key: 最终选择的索引！索引由优化器自动选择的！ 通常情况下，一趟查询，一个表只能选择一个索引！

key_len：where过滤时，过滤的字段在当前索引上使用的字段的长度。
通常见于复合索引！
可以用来判断复合索引使用的充分性！
key_len越大越好！

ref : 查询索引时，参考值的类型！

rows（定量）:　当次查询扫描的表的行数！　在数据量一定的情况下，rows越小越好！

extra : 其他重要的信息！
出现了using filesort, order by排序的字段没有使用上索引！

	group by: 先分组，再按照分组字段排序！
	
	出现了using temporary 和using filesort，group by排序的字段没有使用上索引！
	
	出现了using index 代表使用了覆盖索引！
	
	出现了Using join buffer，关联时on后面参考的字段没有使用上索引！