一、MySQL逻辑架构
1.连接层
最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2.服务层
2.1 Management Serveices & Utilities: 系统管理和控制工具
2.2 SQL Interface: SQL接口
接受用户的SQL命令,并且返回用户需要查询的结果。比如select from就是调用SQL Interface
2.3 Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。
2.4 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
用一个例子就可以理解: select uid,name from user where gender= 1;
优化器来决定先投影还是先过滤。
2.5 Cache和Buffer: 查询缓存。
如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等
3.引擎层
存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
4.存储层
数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互。
5.查询说明
mysql的查询流程大致是:
-
mysql客户端通过协议与mysql服务器建连接,发送查询语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析,也就是说,在解析查询之前,服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中,服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可,这将大大提高系统的性能。
-
语法解析器和预处理:首先mysql通过关键字将SQL语句进行解析,并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询;预处理器则根据一些mysql规则进一步检查解析树是否合法。
-
查询优化器当解析树被认为是合法的了,并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。
-
然后,mysql默认使用的BTREE索引,并且一个大致方向是:无论怎么折腾sql,至少在目前来说,mysql最多只用到表中的一个索引。
二、 MySQL存储引擎
1.用命令查看
# 查看当前mysql已提供了什么存储引擎
show engines;
# 查看当前默认的存储引擎
show variables like '%storage_engine%';
2.各个引擎简介
1、InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎,它被设计用来处理大量的短期(short-lived)事务。
除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。
2、MyISAM存储引擎
MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务和行级锁,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
3、Archive引擎
Archive档案存储引擎只支持INSERT和SELECT操作,在MySQL5.1之前不支持索引。Archive表适合日志和数据采集类应用。根据英文的测试结论来看,
Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。
4、Blackhole引擎
Blackhole引擎没有实现任何存储机制,它会丢弃所有插入的数据,不做任何保存。但服务器会记录Blackhole表的日志,所以可以用于复制数据到备库,
或者简单地记录到日志。但这种应用方式会碰到很多问题,因此并不推荐。
5、CSV引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理,但不支持索引。
CSV引擎可以作为一种数据交换的机制,非常有用。
CSV存储的数据直接可以在操作系统里,用文本编辑器,或者excel读取。
6、Memory引擎
如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用Memory表是非常有用。
Memory表至少比MyISAM表要快一个数量级。
7、Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理,尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此默认是禁用的
3.MyISAM和InnoDB对比
MyISAM | InnoDB | |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响 |
关注点 | 节省资源、消耗少、简单业务 | 并发写、事务、更大资源 |
默认安装 | Y | Y |
默认使用 | N | Y |
自带系统表使用 | Y | N |
三、索引简介
1.何为索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质:索引是数据结构。
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图就是一种可能的索引方式示例:
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址,为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。
总的来说,数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
2.索引的优劣
- 优势:
1)提高数据检索的效率,降低数据库的IO成本
2)通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗 - 劣势:
1)虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
2)实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
3.MySQL索引结构
BTree索引
- 原理图
- 在BTree索引中,真实的数据存在于叶子节点,非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项。
B+Tree索引
- 原理图
- BTree和B+Tree总结
B+Tree与B-Tree 的区别
1)B-树的关键字和记录是放在一起的,叶子节点可以看作外部节点,不包含任何信息;B+树的非叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。
2)在B-树中,越靠近根节点的记录查找时间越快,只要找到关键字即可确定记录的存在;而B+树中每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好,而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比B-树多,树高比B-树小,这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的性能可能还会好些,而且B+树的叶子节点使用指针连接在一起,方便顺序遍历(例如查看一个目录下的所有文件,一个表中的所有记录等),这也是很多数据库和文件系统使用B+树的缘故。
为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引?
- B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。- B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
聚簇索引与非聚簇索引
- 聚簇索引的好处:
按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量的io操作。 - 聚簇索引的限制:
对于mysql数据库目前只有innodb数据引擎支持聚簇索引,而Myisam并不支持聚簇索引。
由于数据物理存储排序方式只能有一种,所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的id,比如uuid这种。
四、MySQL索引分类
1.四类索引
- MySQL索引分为:单值索引、唯一索引、主键索引和复合索引
2.语法
# 创建索引
CREATE [UNIQUE ] INDEX [indexName] ON table_name(column)) ;
# 删除索引
DROP INDEX [indexName] ON mytable;
# 查看索引
SHOW INDEX FROM table_name;
# 使用ALTER命令
ALTER TABLE tbl_name ADD [UNIQUE ] INDEX [indexName] table_name(column)) ;
3.创建索引的说明
何时需要建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该建立索引
- 查询中与其他表关联的字段,外检关系应建立索引
- 单值、组合索引的选择问题,组合索引性价比更高
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或分组字段
何时不需要建索引
- 表记录太少
- 经常增删改的表或字段
(虽然提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件) - where条件里用不到的字段不需要创建索引
- 过滤性不好的不适合建立索引
五、性能优化(Explain)
1.Explain简介
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是
如何处理SQL语句的。可以分析查询语句或是表结构的性能瓶颈。
2.Explain名词解释
(1)id
- id表示select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
- 三种情况
① id相同:执行顺序由上至下
② id不同:如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
③ id有相同有不同:id如果相同,可以认为是一组,从上往下顺序执行;
在所有组中,id值越大,优先级越高,越先执行 - id号每个号码,表示一趟独立的查询。一个sql 的查询趟数越少越好。
(2)select_type
- 此属性为查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
- 八种类型
① SIMPLE:简单的 select 查询,查询中不包含子查询或者UNION
② PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为Primary
③ DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。
④ SUBQUERY:在SELECT或WHERE列表中包含了子查询
⑤ DEPENDENT SUBQUERY:在SELECT或WHERE列表中包含了子查询,子查询基于外层
⑥ UNCACHEABLE SUBQUREY
⑦ UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
⑧ UNION RESULT:从UNION表获取结果的SELECT
(3)table
- 显示这一行的数据是关于哪张表的
(4)type
- type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:system>const>eq_ref>ref>range>index>ALL(一般来说,得保证查询至少达到range级别,最好能达到ref)
- system
表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计- const
表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快
如将主键置于where列表中,MySQL就能将该查询转换为一个常量- eq_ref
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描- ref
非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体- range
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。- index
出现index是sql使用了索引但是没用通过索引进行过滤,一般是使用了覆盖索引或者是利用索引进行了排序分组- all
Full Table Scan,将遍历全表以找到匹配的行- index_merge
在查询过程中需要多个索引组合使用,通常出现在有 or 的关键字的sql中- ref_or_null
对于某个字段既需要关联条件,也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。- index_subquery
利用索引来关联子查询,不再全表扫描。- unique_subquery
该联接类型类似于index_subquery。 子查询中的唯一索引
(5)possible_keys
- 显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。
(6)key
- 实际使用的索引。如果为NULL,则没有使用索引
- 查询中若使用了覆盖索引,则该索引和查询的select字段重叠
(7)key_len
- 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。
- key_len字段能够帮你检查是否充分的利用上了索引
(8)ref
- 显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值
(9)rows
- rows列显示MySQL认为它执行查询时必须检查的行数。(越少越好)
(10)Extra
- 包含不适合在其他列中显示但十分重要的额外信息
- Using filesort
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” - Using temporary
使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。 - USING index
表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引只是用来读取数据而非利用索引执行查找。
或者利用索引进行了排序或分组。 - Using where
表明使用了where过滤 - using join buffer
使用了连接缓存: - impossible where
where子句的值总是false,不能用来获取任何元组 - select tables optimized away
在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
六、单表使用索引及常见索引失效
1.索引失效情况
- 如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。一旦跳过某个字段,索引后面的字段都无法被使用。
- 不要在索引列上做任何操作(计算、函数、(自动or手动)类型转换),否则会导致索引失效而转向全表扫描。
- 存储引擎不能使用索引中范围条件右边的列
- mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
- is not null 也无法使用索引,但是is null是可以使用索引的
- like以通配符、下划线开头(’%abc…’,’_abc…’)mysql索引失效会变成全表扫描的操作
- 字符串不加单引号索引失效
2.建议
- 对于单键索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面
- 书写sql语句时,尽量避免造成索引失效的情况
七、关联查询和子查询优化
1.关联查询优化
- 建议
- 保证被驱动表的join字段已经被索引
- left join 时,选择小表作为驱动表,大表作为被驱动表。
- inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
- 子查询尽量不要放在被驱动表,有可能使用不到索引。
- 能够直接多表关联的尽量直接关联,不用子查询。
2.子查询优化
- 尽量不要使用not in 或者 not exists
- 用left outer join on xxx is null 替代
八、排序分组优化和覆盖索引
1.排序分组优化
- ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序
- 当范围条件和group by 或者 order by 的字段出现二选一时 ,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。
- GROUP BY关键字优化
group by 使用索引的原则几乎跟order by一致 ,唯一区别是groupby 即使没有过滤条件用到索引,也可以直接使用索引。
2.覆盖索引
什么是覆盖索引?
简单说就是,select 到 from 之间查询的列 <=使用的索引列+主键
九、视图
1.视图简介
视图:
视图是将一段查询sql封装为一个虚拟的表。 这个虚拟表只保存了sql逻辑,不会保存任何查询结果。
视图的作用:
1、封装复杂sql语句,提高复用性
2、逻辑放在数据库上面,更新不需要发布程序,面对频繁的需求变更更灵活
视图的使用场景:
1、很多地方可以共用的一组查询结果
2、报表
2.语法
# 视图的创建
CREATE VIEW view_name
AS
SELECT column_name(s)
FROM table_name
WHERE condition;
# 视图的使用
SELECT *
FROM view_name;
# 视图的更新
CREATE OR REPLACE VIEW view_name
AS
SELECT column_name(s)
FROM table_name
WHERE condition;
十、主从复制
1.复制的基本原理
- slave会从master读取binlog来进行数据同步
- MySQL复制过程分成三步:
- master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件,binary log events;
- slave将master的binary log events拷贝到它的中继日志(relay log);
- slave重做中继日志中的事件,将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的。
2.复制的基本原则
- 每个slave只有一个master
- 每个slave只能有一个唯一的服务器ID
- 每个master可以有多个salve
3.一主一从配置(Windows+Linux)
- mysql版本一致且后台以服务运行
- 主从都配置在[mysqld]结点下,都是小写
- 主机修改my.ini配置文件
3.1 主服务器唯一ID
server-id=1
3.2 启用二进制日志
log-bin=自己本地的路径/data/mysqlbin
3.3 设置不要复制的数据库
binlog-ignore-db=mysql
3.4 设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字(设置一个之前没有的数据库)
3.5 设置logbin格式
binlog_format=STATEMENT(默认) - mysql主从复制起始时,从机不继承主机数据
- 从机配置文件修改my.cnf的[mysqld]栏位下
5.1 server-id = 2
5.2 relay-log=mysql-relay - 因修改过配置文件,请主机+从机都重启后台mysql服务
- 主机从机都关闭防火墙
7.1 windows手动关闭
7.2 关闭虚拟机linux防火墙 service iptables stop - 在Windows主机上建立帐户并授权slave
8.1 GRANT REPLICATION SLAVE ON . TO ‘slave’@‘从机器数据库IP’ IDENTIFIED BY ‘123456’;
8.2 查询master的状态
① show master status;
② 记录下File和Position的值
8.3 执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化 - 在Linux从机上配置需要复制的主机
9.1 subtopic:
CHANGE MASTER TO MASTER_HOST=‘主机IP’,MASTER_USER=‘slave’,MASTER_PASSWORD=‘123456’,MASTER_LOG_FILE=‘File名字’,MASTER_LOG_POS=Position数字;
9.2 启动从服务器复制功能
start slave;
9.3 show slave status\G(下面两个参数都是Yes,则说明主从配置成功!)
① Slave_IO_Running: Yes
② Slave_SQL_Running: Yes - 主机新建库、新建表、insert记录,从机复制
- 如何停止从服务复制功能(stop salve;)