MySQL调优

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

提示：以下是本篇文章正文内容，下面案例可供参考

一、mysql架构及模块

client端
	向数据库发送请求
server端
	0、缓存			 	 8.0版本开始取消了缓存	
	1、连接器				控制用户的连接
	2、解析器				语法词法的解析
	3、优化器				优化sql语句（执行的流程）
	4、执行器				执行sql的组件
	5、存储引擎

二、性能监控

1、show profile

set proiling = 1；						开启查询sql执行的时间记录
show profiles；							查看执行sql的消耗时间
show profiles for query n；				查看具体的第n条sql消耗时间的记录
---------------------------------------------------
show profile all for query n；			显示所有性能信息
show profile block io for query n；		显示块IO操作的次数

2、performance schema

略、。。。。。

3、show processlist

show processlist 			 查看mysql一共有多少个连接

三、数据类型优化

表设计

1、更小更好：对于字段的类型，尽量精确（能使用tinyint，就不使用int）
2、时间类型：Date占两个字符，同时可以使用mysql日期类函数操作
3、尽量避免null： not null 可以提升性能。对于有索引且字段值为null的需要额外的数据进行记录
4、字符集选择： 当需要存储中文时，官方推荐使用utf8mb4，减少不必要的存储空间浪费

MyISAM和InnoDB的区别

	MyISAM	InnoDB
索引类型	非聚簇索引	聚簇索引
支持事务	否	是
支持表锁	是	是
支持行锁	否	是
支持外键	否	是
支持全文检索	是	是
适合操作类型	大量的查	大量的增删改操作
持久化数据	3个文件（数据和索引分开存放）	2个文件

四、执行计划

主要就是看以下几个字段：
1、id					表示执行的顺序
2、type					表示访问的类型（性能由好到差）
	system				表只有一行记录，相当于系统表
	const				用过索引一次就能查到
	eq_ref				唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
	ref					非唯一性索引扫描，返回匹配某个单独值的所有行
	range				只检索给定范围的行，使用一个索引来选择行
	index				只遍历索引树
	all					全表扫描
3、possible_keys 		表示可能用到的索引
4、extra					表示额外的信息
	using filesort		无法利用索引进行排序
	using where			说那个where进行条件过滤
5、row					扫描数据的行数

五、通过索引进行优化

数据结构

Hash表 ----》 二叉树 ----》 平衡二叉树 ----》 红黑树 ----》 B树 ----》 B+树

演变 与 优劣

hash表：
	需要占用大量的内存空间
	等值查询比较快，但是范围查询比较慢
二叉树：
	使用的是二分查找法
	会出现极端的情况，递增或者递减
	树高会比较深，IO频繁
平衡二叉树：
	左右子树的高度差不能超过1
	树高会比较深，IO频繁
红黑树：
	左右子树的高度差不能超过1，且有颜色的要求
	树高会比较深，IO频繁
B树：
	每个节点都存储指针和数据
	每个此判断存储的数据量较少
B+树：
	非叶子节点，存储的是指针
	叶子节点存储的是数据，3层树高就能覆盖千万级别的数据

索引

主键索引：
	每个表都会有主键索引，即使没有指定也会有默认的主键索引
唯一索引：
	给表的唯一列添加的索引
普通索引：
	又称为二级索引和辅助索引，表示出了主键，唯一键添加的索引
全文索引：
	一般很少用，作用类似于全文检索
复合索引：
	由多个列组成的一个索引
回表：
	普通索引，复合索引下面记录的是主键的id，当通过id进行数据查询的时候，这个过程叫做回表。
覆盖索引：
	在列的索引树的叶子节点，可以直接查找的值，不需要再通过主键进行查询的操作。
最左匹配原则：
	主要作用于复核索引中。
聚簇索引：
	即主键索引。索引和值都存放在一起。其他索引存储的是主键的id。
非聚簇索引：
	非主键索引都是非聚簇索引。

索引失效

1、 模糊查询中以 % 开头
2、 加入运算
3、 表示否定的条件
4、 不符合最左匹配原则
5、 条件为 or

六、表锁

MyISAM

共享读锁
	不会阻塞其他用户对同一表的读请求。但是会阻塞写请求。
独占写锁
	阻塞其他用户的读和写操作

InnoDB

共享锁
	又称为读锁。允许其他事务进行读操作，但是阻止写操作
排他锁
	又称为写锁。阻止其他事务的读操作和写操作。

七、传播特性和隔离级别

传播特性

propagation_requeied	: 如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该事务是最常用的设置。
propagation_nested		：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与propagation_requeied类似的操作。
propagation_supports	：支持当前事务，如果当前存在事务，就加入，如果不存在事务，就以非事务执行。
propagation_mandatory	：支持当前事务，如果当前存在事务，就加入，如果不存在事务，就抛出异常。
propagation_requireds_new:无论当前存不存在事务，都创建新事务。
propagation_not_supported:以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
propagation_never		：以非事务方式执行，如果当前存在事务，则抛出异常。

隔离级别

脏读：一个事务读到了另一个事务未提交的数据，导致查询结果不一致。
不可重复读：一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致。
幻读：一个事务读到了另一个事务已经提交的insert诗句，导致多次查询结果不一致。
---------------------------------------------------
read uncommitted： 脏读，不可重复读，幻读都有可能发生
read committed	： 避免脏读。但是不可重复读和幻读都有可能发生
repeatbale read	： 可重复读。避免脏读和不可重复读，但是幻读有可能发生
serializable	： 避免脏读，不可重复读，幻读。

总结

提示：这里对文章进行总结：后续补充