本文共 6024 字，大约阅读时间需要 20 分钟。
文章目录
1.认识mysql
1.1 架构图
1.1.1 简介
大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎（存储引擎是基于表的！）。
1.1.2 详细
上面是宏观图，下面是详细图
-
初始化模块：数据库引擎的配置、端口、缓存等数据初始化
-
连接模块：初始化完成之后模块会监听请求，当有请求进来分配一个线程，模块作用是管理数据库连接池（与线程池原理类似）
-
用户模块：请求拿到之后会去验证一下用户权限
-
命令分发器：授权通过之后会对请求进行判断分发（mysql有query与comand两种命令）
-
命令解析器：对语句进行解析（parse）与执行计划分析（explain）进如不同的执行模块
-
执行模块：查询优化器、表变更、表维护、复制模块（master，slaver）、状态模块
-
访问控制模块：所有连接又重新指向该模块进行访问控制
-
表管理模块
-
存储引擎接口：目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色
-
查询缓存模块：会把查询语句与查询结果缓存起来（键值对），如果有缓存就不走命令解析
#查询缓存show variables like 'have_query_cache';
- 日志模块：binlog，redo，undo日志
- 核心API：类似JVM的部分功能，一个manner，对内存进行管理
1.2 存储引擎介绍
mysql存储是基于表的，schema对应在本地是一个目录（~/mysql5.6/data/），表对应两个存储文件：xx.ibd与xxx.frm
Innodb索引与数据放在一个文件中，索引是基于主键展开
Myisam 两个文件，一个索引文件，一个数据文件
count有专门存储的地方（Innodb优化->分页的count(1)可以缓存起来）2.性能
2.1 影响性能的因素
- 人为因素：需求
- 程序员因素：过于面向对象
查询文章表的前10篇文章的评论数
方法1：面向对象->查询前10篇文章、根据文章id查询评论数（消耗了1+10 = 11次IO） 方法2：查询10篇文章、根据文章id集合group by查询（消耗1+1 = 2次IO）
- cache：缓存设计缺陷，都打到数据库层面了
- 对可拓展的过度追求：泛型、扩展字段、冗余字段，导致表字段过多出现问题
- 表范式：对表的不合理拆分
- 应用场景 ：
OLTP （on-line transaction processing）
OLAP （On-Line Analytical Processing）
2.2 索引原理
2.2.1 索引简介
select * from t_user where seq_id=2;
如果没有索引的话，该语句会轮询全表扫描
如果有索引的话，那么直接查询索引，找到索引后对应有个数据存储地址
索引结构图
2.2.2 几种索引
1）B-Tree索引
一般来说，MySQL中的B-Tree索引的物理文件大多都是以BalanceTree的结构来存储的，也就是所有实际需要的数据都存放于Tree的LeafNode，而且到任何一个LeafNode的最短路径的长度都是完全相同的，所以我们大家都称之为B-Tree索引当然，可能各种数据库（或MySQL的各种存储引擎）在存放自己的B-Tree索引的时候会对存储结构稍作改造。如Innodb存储引擎的B-Tree索引实际使用的存储结构实际上是B+Tree，也就是在B-Tree数据结构的基础上做了很小的改造，在每一个LeafNode上面出了存放索引键的相关信息之外，还存储了指向与该LeafNode相邻的后一个LeafNode的指针信息，这主要是为了加快检索多个相邻LeafNode的效率考虑。2）Hash索引
Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，而且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
Hash索引无法被利用来避免数据的排序操作； Hash索引不能利用部分索引键查询； Hash索引在任何时候都不能避免表扫面； Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；3）Full-text索引
Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。
2.2.3 几种树
红黑树、B-Tree、B+Tree
树插入实验网址：为什么不用红黑树？因为红黑树的深度不可控，查询的时候数据多就太深了
B树：不管数据多大，查询深度都是固定的
B-Tree：数据与节点存在一起
例如
B+Tree：数据都放在叶子节点上，其他节点是key
例如
2.2.4 Myisam与Innodb的索引树结构图
Myisam
查询id为1的数据时，会去B+Tree索引搜索，找到id=1对应的地址后，再去MYD文件里面定位
Innodb
Innodb的数据本身就存在索引树里面，在Myisam里面以给那么加索引，那么name会指向数据地址，但是在这边，不能指向数据地址，要指向主键。
表结构：id user_id create_time
复合索引：(user_id,create_time) 当查询user_id为2的用户日志时，不需要排序，因为索引已经排序好了！！ select * from t_user_log where user_id=2 ;
2.3锁
2.3.1 行锁
#行锁查询show status like 'innodb_row_lock%';
2.3.2 表锁
#表锁查询show status like 'table%';
2.3.3 页锁
2.3.4 死锁的发生
行锁的情况下会出现死锁，表锁不会出现死锁
2.3.5 共享锁与排它锁
2.4优化
2.4.1 原则
2.4.2 explain 执行计划
在sql语句前面添加explain进行语句分析 参考
| id | SELECT识别符。这是SELECT的查询序列号 |
| select_type | SELECT类型,可以为以下任何一种:
|
| table | 输出的行所引用的表 |
| type | 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
|
| possible_keys | 指出MySQL能使用哪个索引在该表中找到行 |
| key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
| key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 |
| ref | 显示使用哪个列或常数与key一起从表中选择行。 |
| rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
| filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
| Extra | 该列包含MySQL解决查询的详细信息
|
2.4.3 Profiling性能分析
set profiling=1;select nickname,count(*) from user group by nickname;show profiles;show profile cpu,block io for query 75;（75是show profiles的id）
2.4.4 join底层实现
原理图解
A表当做驱动表（小结果集）驱动B表（大结果集），联表查询得出result
当出现C表之后，A与B表的join会产生join buffer再与C表进行关联得出resultshow variables like 'join_%';#查join的buffer大小等
2.4.5 order by底层实现
实现原理图解
先把user表的birthday字段拷贝到buffer进行排序，然后返回实际数据地址，再进行处理得出result（做了两次IO）
第二种方式是把数据需要返回的字段都拷贝到内存进行排序，指针指向的是内存中的数据地址，排序后直接返回result，空间换时间（所以尽量少使用select * ）
这两种排序方式是mysql自动选择的，如果buffer够用就用第二种
show variables like '%sort%';#查询order by排序缓存的大小
概览图
2.4.6 group by底层实现
group by与order by类似，它会先排序然后分组
2.4.7 distinct
distinct是基于group by的结果进行计算的
2.4.8 limit
#分页查找SELECT * FROM user limit 10000,10;#offset很大的时很慢，因为要取10010这么多条数据#根据索引进行优化SELECT * FROM user where id> 10000 limit 10;
2.4.9 union
join是笛卡尔积，union是叠加
2.4.10 性能优化
当定位到是数据库出现性能问题的时候可以进行如下优化排查
#show sql配置，慢查询日志[mysqld]show_query_log=1show_query_log_file=/path/to/filelong_query_time=0.2log_output=FILE
通过show full processlist定位当前卡住的查询语句，然后对该语句进行explain分析，再对照explain表格进行分析，看是不是没用到索引
3.参考
转载地址：http://kvmxb.baihongyu.com/