Mysql优化学习笔记

一、mysql存储引擎分类

1、InnoDB

- 文件保存目录
  - .frm 表结构文件
  - .idb 数据索引+数据文件 
- 数据存储结构
	- b+ tree 

2、MyISAM

- 文件保存目录
  - .frm表结构文件
  - .MYD 数据文件（非聚簇索引：索引和数据文件不在一起）
  - .MYI 索引文件
- 数据存储结构
	- b+ tree

二、数据存储结构B+Tree

1、B Tree

2、B+ Tree

3、B+ Tree的优点

• 相对于Btree
  • 1、b+ 只有最底层叶子节点存储数据，之前的节点都是指针和键值。
  • 2、b+ 的最底层的叶子节点是有序的，并且叶子节点与叶子节点是有双向链接的，可以用于方便的范围查找
  • 3、Mysql会提前加载非叶子节点内容，这样寻址，只会在需要的时候调用磁盘。

三、慢sql优化

1、开始慢查询

• 在线开启

show variables like "%slow%"; # 查看慢查询的配置信息
# slow_query_log off 关闭 on开启
# slow_query_log_file 日志记录文件
# slow_launch_time 设置慢查询定义，默认超过两秒就为慢查询
show variables like "%long%"; # 查看慢查询的时间定义
# 8.20 中没有这个参数 set global log_slow_queries=ON ; # 打开慢查询
set global slow_query_log=ON; # 打开慢查询

• 配置开启
  配置文件my.cnf [mysqld] 增加参数
  slow_query_log = 1 # 0 关闭 1打开
  slow_query_log_file # 指定慢查询路径，需要对该目录有权限
  long_query_time = 1 # 规定大于等于1s的查询才为慢查询。
  重启mysql服务器
  windows -任务管理器-服务-MySQLX
  liunx sudo service mysqld restart

2、分析并处理

• 打开慢查询的日志位置 如果是默认路径的话，在数据库data的文件夹中。

四、索引优化

1、索引类型

• 聚簇索引：索引和数据在一起,例如主键索引
• 非聚簇索引：索引和数据不在一起
• 主键索引、唯一索引、普通索引、全文索引、组合索引
• 主键索引，创建主键的时候创建索引
  • 没有指定主键：首先去找列内不重复的字段作为主键，找不到的话，系统会维护一个不可见的虚拟主键。

2、查看索引是否命中

• 1、命令explain
  • 举例：explain select * from 表名称
  • 查询结果
  • 索引是否命中判断type类型
  • 以下引用来自：MySql优化-你的SQL命中索引了吗

执行结果每一列的说明:
1、 select_type : 查询类型，常见的值[SIMPLE：简单表，不使用表连接或子查询。PRIMARY : 主查询，外层的查询。UNION 第二个或者后面的查询语句。SUBQUERY : 子查询中的第一个select]
2、table ：输出结果的表
3、type : 表示MySql在表中找到所需行的方式，或者叫访问类型。常见的类型：
ALL index range ref eq_ref const,system NULL
从左到右，性能由最差到最好。
　　3.1 type＝ALL　全表扫描，
　　3.2 type＝index 索引全扫描，遍历整个索引来查询匹配的行
　　3.3 type=range 索引范围扫描，常见于　<,<=,>,>=,between,in等操作符。
　　例
　　　　explain select * from adminlog where id>0 ,
　　　　explain select * from adminlog where id>0 and id<=100
　　　　explain select * from adminlog where id in (1,2)
　　3.4 type=ref　使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行。ref还经常出现在JOIN操作中
　　3.5 type=eq_ref 类似于ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中有一条记录匹配；简单来说，说是多表连接中使用　主建或唯一健作为关联条件
　　3.6 type=const/system 单表中最多有一个匹配行。主要用于比较primary key [主键索引]或者unique[唯一]索引,因为数据都是唯一的，所以性能最优。条件使用=。
　　3.7 type=NULL　不用访问表或者索引，直接就能够得到结果　
例　explain select 1 from dual,类型type 还有其他值　如ref_or_null : 与ref 类似，区别在于条件中包含对NULL的查询. index_merge : 索引合并优化, unique_subquery : in的后面是一个主键字段的子查询。index_subquery : 与 unique_subquery 类似,区别在于in的后面是查询非唯一索引字段的子查询
4、possible_keys : 可能使用的索引列表.
5、key : 实现执行使用索引列表
6、key_len : 索引的长度
7、ref : 显示使用哪个列或常数与key一起从表中选择行。
8、row : 执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引
9、filtered:
10、Extra: 该列包含MySQL解决查询的详细信息。
　　10.1 Not exists
　　10.2 range checked for each record
　　　　没有找到合适的索引
　　10.5 using index 只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。就是建议取索引列。这样就可以不要通过索引去实际表中找数据了。直接返回索引列的数据。一次查询。否则就是索引表查一次，实际表中查一次。
　　10.6 using temporary
为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

五、数据库事务