Mysql调优系列视频【索引】【索引优化】【SQL优化】
mysql的优化大纲:
数据结构算法: 二叉树---->平衡二叉树(红黑树)---->B树(平衡多路查找树)---->B+树(B树的进阶),也就是mysql的底层数据结构
二叉树: 每个节点最多有两个子节，并遵循左边小于右边，数据随机性情况树杈越明显,缺点:随着数据的增多，树的结果会越高，数据查询的效率会变慢，如果数据是按顺序来排序依次进入的话，树的高度则会更明显
红黑树:虽通过自旋平衡，子节点自动分为2个，从而减少树的高度，当数据有序时，更明显，检索结果更佳，数据量越大树也会越高
b-tree：b-tree的出现，可以解决树的高度的问题，他不在限制一个父节点中只有两个子节点，而是允许M个子节点(M>2)。不仅如此，B树的一个节点可以存储多个元素，相交于二叉树的结构又将整体的树高度降低了
B+tree：b+tree是b树的一种升级优化，更适合做存储索引结构，在B+tree中，非叶子节点上仅存储key，不存储数据，所有的数据均存储在叶子的节点上面，并且是按照顺序来排序的，此外在B+tree中·各个数据页之间是双向链表连接的，叶子节点中的数据是单向链表连接的。
聚集索引和非聚集索引的区别？
按物理存储分类：innerdb属于聚集索引，mysisam属于非聚集索引
聚集索引:它的子叶同时存储索引和数据
非聚集索引：它的子叶只存储索引和指针，需要通过指针来查找具体的数据
效率：聚集索引更快
二级索引：所有非主键索引都称为二级索引，并且二级索引为非聚集索引，查找数据需要进行回表
覆盖索引: 需要查询的字段都在索列中的情况称为覆盖索引，索引列覆盖了查询字段的意思
索引下推：是mysql5.6之后针对扫描二级索引的一项改进，用来在范围查询是减少回表的次数，将需要回表的查询一次性完成
单列索引：只有一个索引字段(name)
联合索引：多个索引字段(name,age,heigt)，联合索引需要遵循最左前缀原则,因为没有第一列的话，直接访问第二列，那么第二列肯定是无序的，直接访问后面的列就用不到索引了
单向索引和联合索引分别在什么场景创建，优势是什么？
联合索引的优势：
1.减少开销:
建立一个联合索引，实际上相当于创建了多个索引((a), (a,b), (a,b,c)),每单独建一个索引，都会增加写操作的开销和磁盘的开销，对于大量的数据，使用联合查询会大大减少开销
2.覆盖索引:
select a,b,c from table where a ='xx' and b='xxx'
那么mysql可以直接遍历索引取得数据，而无需回表，减少了很多的io操作，减少io操作，特别是随机io是dba主要的优化机制
3.效率高：
索引列多，通过联合索引筛选出的数据越少，比如有100万条的数据表，有如下sql：
select col1,col2,col3 from table where col1='1' and colw='2' and col2='3'
如果是单列的话需要查找三次
索引的优点：
1.提高检索效率
2.降低排序成本，索引对应的字段已经有一个自动排序·功能的，默认是升序asc
索引的缺点:
1.创建索引和维护索引需要耗费时间，这种时间会随着数据增加而增加
2.索引需要占用无聊空间，数据量越大，占用越多
3.会降低表增删改的效率，因为每次增删改索引，都需要进行动态维护
sql以及索引优化:
1.创建索引减少扫描量·
2.调整索引减少计算量
3.索引覆盖(减少不必访问的列，避免回表查询)
4.干预执行计划
5.sql改写
如果索引失效，需要排查：
手动添加索引：force index(idx_name_age_position)
1.遵循最左前缀原则
2.不在索引列上做任何操作(计算，函数，(自动or手动)， 类型转换),会导致索引失效而造成全表扫描
3.索引引擎不能使用索引中范围条件中右边的列,范围查询会使得字段无序，造成部分索引失效
4.尽量使用覆盖索引(只访问索引的查询(索引列包含列),减少select * 语句，覆盖索引不写*
5.不等空值还有or，索引失效要少用
6.like百分写在最右
7.字符串不加单引号索引会失效
8.范围查询优化
trace工具用法:(跟踪查询过程和结果)
第一阶段:sql准备阶段，格式化sql
第二阶段:sql优化阶段
第三阶段:预估表的访问成本 -->对全表扫描情况行数，查询成本,再查询索引的成本，再根据估值分决定用索引还是全盘扫描
sql最佳实践：
1.避免使用select *：
1).会增加查询解析器的成本
2).不走覆盖索引会产生大量的回表查询
3).浪费cpu和内存资源
2.小表驱动大表：
用数据量少的表用索引和条件大表进行数据筛选，从而减少数据计算量，提高查询效率
3.用连接查询代替子查询:
1).子查询需要执行两次数据库查询，一次是外部，一次是嵌套子查询。因此，使用连接查询可以减少数据库查询的次数，提高查询的效率
2).连接查询可以更好的利用数据库索引，提高查询性能。子查询通常会使用临时表或内存表，而连接查询可以直接利用表上的索引。这意味着连接查询可以更快的访问表中的数据，减少查询的资源消耗
3).对于大型数据集，使用连接查询通常比使用子查询更高效，子查询通常需要扫描整个表，而连接查询可以利用索引加速读取操作
