SQL优化

1.首先进行SQL性能分析

　　　1.1 查询当前库下各种类型语句的执行次数

SHOW GLOBAL STATUS LIKE 'com_______'

　　　　1.2 慢查询日志（https://blog.csdn.net/chengqiuming/article/details/120402562）

-- 查询是否慢查询日志是否开启
SHOW VARIABLES LIKE '%slow_query_log%';

-- 开启慢查询日志
SET GLOBAL slow_query_log=1

--设置语句超时时间，超时则会被记录到日志中（单位为秒）
SET GLOBAL long_query_time=2;

　　　　1.3 profile分析

-- 查询是否开启了profile
SELECT @@profiling

-- 开启profile
SET profiling = 1;

-- 展示当前会话下的所有SQL语句以及耗时
SHOW profiles;

　　　1.4 explain分析（https://blog.csdn.net/wang5701071/article/details/117782061）

　　　Type:

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
 
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
 
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
 
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
 
range:只检索给定范围的行，使用一个索引来选择行
 
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
 
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
 
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
 
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

2.索引

　　2.1 索引类型

　　　　2.1.1 单列索引（一个索引只包含单列）：

　　　　　（1）普通索引：允许数据重复和null

　　　　　（2）唯一索引：索引中值必须是唯一的，但允许为null

　　　　　（3）主键索引：一种特殊的唯一索引，不允许为空

　　　　　 既然是唯一索引，为什么表可以创建多个主键呢？

　　　　　　 “主键是唯一的索引”是有歧义的。应该是“当表中只有一个主键时，它是唯一的索引；当表中有多个主键时，称为复合主键，复合主键联合保证唯一索引”。某些业务场景中，由于业务需要，可能存在id重复的现象，可能会把name也作为主键，那如果name也会重复的话，可能还会把age也作为主键，以此类推。复合主键和单个主键一样，只是为了保证唯一索引的唯一性。

　　　　　（4）前缀索引：只是用与字符串类型的数据，对文本的前几个字符创建索引

　　　　2.1.2 组合索引：包含多列，使用时需要注意最左前缀原则

-- 最左前缀原则 ： 如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到
SELECT * FROM user WHERE name ='123' -- 命中
SELECT * FROM user WHERE name ='234' AND city = '456' -- 不行
SELECT * FROM user WHERE city = '789' -- 命中
 


　  -- 例如组合索引（a,b,c），组合索引的生效原则是

　　-- 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

　　-- 比如

　　-- where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；

　　-- where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果

　　-- where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；

　　-- where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

　　　　2.1.3 全文索引：https://blog.csdn.net/dreamyuzhou/article/details/120432893

　　2.2 innoDB下的索引分类

回表查询思考题: 已知name是二级索引,通过name查询的时候是怎么查询的？

答: 由于数据innodb底层是b+树实现的, 所以查询的时候会通过二叉树的查找模式来进行查找. b+树的每个叶子节点都会存放数据, 而二级索引存放的是这行数据的id值, 所以拿到id后,需要在查找一下聚集索引.聚集索引的叶子节点存放的是这行数据的所有信息. 这个行为也称为:回表查询.

2.2.1 索引原理（http://blog.codinglabs.org/articles/theory-of-mysql-index.html）

聚簇索引:聚簇索引是指索引的结构和排列规则是和实际数据的存储结构和排列规则是一样的（比如说书本的目录和内容就相当于聚簇索引，书本的内容顺序总是和目录顺序一样的）,每个表至多只能创建一个聚簇索引
非聚簇索引：非聚簇索引则可能是通过其他算法规则构成的一种索引结构，索引的结构和实际数据存储的结构是不同的
MyISAM:
B+Tree叶节点存放的是数据记录的地址，在检索的时候，先找到索引对应的数据记录的地址，再根据地址读取相应的数据记录，这种查找方式被称为“非聚集索引”。
InnoDB:
它的主键索引是聚集索引，聚集索引也是一种聚簇索引。即主键和行记录放在同一个叶节点，找到了主键也就找到了行记录；而它的非主键索引，或者说是辅助索引，是非聚集索引，跟MyISAM引擎的非聚集索引不同的是，MyISAM叶节点保存的是地址，而InnoDB是主键，InnoDB非聚集索引的索引文件和数据文件分开存储，索引文件的叶节点只保存主键，在查找时，要先找到叶节点中的主键，再根据主键去主索引文件查找详细行记录；因此，在设计表的时候，主键字段不宜过长。
上述InnoDB引擎中，非主键索引查找数据时需要先找到主键，再根据主键查找具体行数据，这种现象叫回表查询

举例（避免回表查询）：有个用户表，有id、name、age、addr四个字段，其中id为主键，主键自带主键索引，无需创建
值1：1、小张、18、成都；
值2：2、小黄、20、北京；
```
select * from table_t where name = "小张"
```
这种查询就必须先在索引文件中找到name为小张的索引节点，很明显这个节点里面只有id，因为这张表只有主键索引，再根据id去数据文件查找具体数据

如果把name、age、addr建立到联合索引，在找到name为小张的索引节点时，发现里面已经有了我们所需要的age、addr，就无需再到数据文件查找；