MySQL -二（索引优化分析）

1、性能下降，SQL慢，执行时间长，等待时间长

1）、查询数据多

2）、关联太多的表，太多join

3）、没有利用到索引

单值

复合

4）、服务器调优以及各个参数设置

2、常见的join查询

        1、SQL执行顺序

手写：

        2、机读

 

        3、总结

                mysql的SQL执行顺序是

                先执行from(笛卡尔积) -->on(主表保留)-->join && where -->group by --> having -->select -->order by --->limit

 

 3、join总结图

 内联(inner join )： select * from tableA a inner join tableB on a.key=b.key

左连接(left join):      select * from tableA a left join tableB on a.key=b.key

右连接(right join):    select * from tbaleA a right join tableB on a.key=b.key

左外连接: select * from tableA a left join tableB on a.key=b.key where b.key is null

右外连接：select * from tbaleA a right join tableB on a.key=b.key where a.key is null

全连接：select * from tableA  a full outer join tableB b on a.key=b.key

mysql:

        

mysql> select * from t_emp a left join t_dept b on a.deptId=b.id
    -> union
    -> select * from t_emp a right join t_dept b on a.deptId=b.id;

 

全外连接:select * from table A a full outer join tableB b on a.key=b.key where a.key is null or b.key is null

        mysql:

mysql> select * from t_emp a left join t_dept b on a.deptId=b.id where b.id is null
    -> union
    -> select * from t_emp a right join t_dept b on a.deptId=b.id where a.deptId is null;

4、索引简绍

索引是帮助Mysql高效获取数据的数据结构

索引的本质：索引是数据结构

索引的目的：在于提高查询效率，可以类别字典。 

即排好序的快速查找数据结构

数据库还维护着满足特点查找算法的数据结构，这些数据结构以指针的方式引用数据。

这样就可以在数据结构上实现高级查找算法。着这就是数据结构，就是索引。

 在维护数据的同时还维护了物理地址。指针-->物理地址 -->对应的数据

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

索引的优势：

提高数据的检索的效率，降低数据库的IO成本，对数据进行排列，降低数据排序的成本，降低CPU的消耗 

劣势：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，

都会调整因为更新所带来的键值变化后的索引信息

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

5、索引的分类

1、单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

2、唯一索引

索引列的值必须唯一，但允许有空值

3、复合索引

即一个索引包含多个列

4、语法

创建：

ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length))

删除：

DROP INDEX [indexName] ON mytable;

查看：

SHOW INDEX FROM table_name\G

6、索引结构（BTree索引）

 真实的数据存在于叶子节点 ，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非叶子节点不存储真实的数据，只存储指引搜索方向的数据项 ，如17、35并不真实存在于数据表中。每次磁盘交换发送一次IO

7、索引的使用(单表数据量在100w一下都不太需要。超过百w .看SQL查询数据是否超过3秒)

1）、主键自动建立唯一索引

2）、频繁作为查询条件的字段应该创建索引(where 后面的语句)

3）、查询中与其它表关联的字段，外键关系建立索引

4）、单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)

5）、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

6）、查询中统计或者分组字段

不需要创建索引的场景

1）、表记录太少

2）、经常增删改的表

Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

3）、Where条件里用不到的字段不创建索引

4）、数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。

注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。