MySQL高级-1-基础知识

本文最后更新于:2020年8月7日 下午

mysql Ver 14.14 Distrib 5.5.62

1.1 逻辑架构

MySQL的架构可以在多种不同的场景中应用并发挥良好作用。主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要来选择合适的存储引擎。

MySQL逻辑架构

1.1.1 连接层

  最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似TCP/IP的通信。主要完成一些类似于连接处理、授权认证等相关的安全方案。在该层上引入了线程池的概念,为用过认证,安全接入的客户端提供线程。同样在该层上可以实现基于SQL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

1.1.2 服务层

  第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询、SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现。服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定查询表的顺序、是否利用索引等,最后生成相应的执行操作。如果是SELECT语句,服务器还会查询内部的缓存。如果缓存空间足够大,能够在大量的读操作环境中,很好的提升系统的性能。

1.1.3 引擎层

  存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的实际需要进行选取。

1.1.4 存储层

  数据存储层,主要是将数据存储在运行与裸设备的文件系统之上,并完成与存储引擎的交互。

1.2 存储引擎

1.2.1 用命令查看

  1. 查看你的MySQL提供的存储引擎。

    show engines;
  2. 查看你的MySQL当前默认的存储引擎。

    show variables like '%storage_engine%';

1.2.2 MyISAM和InnoDB比较

对比项MyISAMInnoDB
主外键不支持支持
事务不支持支持
行表锁表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作行锁,操作时只锁某一行,不对其他行有影响,适合高并发的操作
缓存只缓存索引,不缓存真实数据不仅缓存索引,还缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响
表空间
关注点性能事务
默认安装YY

1.2.3 Percona介绍

Percona为MySQL数据库服务器进行了改进,在功能和性能上较MySQL有着很显著的提升。该版本提升了在高负载情况下的InnoDB的性能,为DBA提供了一些非常有用的性能诊断工具,并且有更多的参数和命令来控制服务器行为。

1.3 SQL性能下降原因

执行时间长,等待时间长。

  1. 查询语句待完善。
  2. 索引失效(包含单值索引和复合索引),含义为建立了索引但实际使用没用到。
  3. 关联查询使用了太多JOIN,有可能是设计缺陷或是不得已的需求。
  4. 服务器调优及各个参数的设置,如缓冲、线程数等。

1.4 SQL执行顺序

1.4.1 手写

SELECT DISTINCT < select_list >
FROM < left_table > 
< join_type > JOIN < right_table > ON < join_condition >
WHERE < where_condition >
GROUP BY < group_by_list >
HAVING < having_condition >
ORDER BY < order_by_condition >
LIMIT < limit_number >

1.4.2 机读

FROM	<left_table>
ON	<join_condition >
<join_type>	JOIN	<right_table>
WHERE	<where_condition>
GROUP BY	<group_by_list>
HAVING	<having_condition>
SELECT DISTINCT	<select_list>
ORDER BY	<order_by_condition>
LIMIT	<limit_number>

1.4.3 总结

SQL执行顺序

1.5 七种JOIN理论

七种JOIN理论


版权声明:自由转载-非商用-非衍生-保持署名 Creative Commons BY-NC-ND 3.0 协议,转载请注明出处!

 目录