1、体系结构1).连接层最上层是一些客户端和链接服务包含本地sock通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程 池的概念为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务 器也会为安全接入的每个客户端验证它所具有的操作权限。2).服务层第二层架构主要完成大多数的核心服务功能如SQL接口并完成缓存的查询SQL的分析和优化部 分内置函数的执行。所有跨存储引擎的功能也在这一层实现如 过程、函数等。在该层服务器会解 析查询并创建相应的内部解析树并对其完成相应的优化如确定表的查询的顺序是否利用索引等 最后生成相应的执行操作。如果是select语句服务器还会查询内部的缓存如果缓存空间足够大 这样在解决大量读操作的环境中能够很好的提升系统的性能。3).引擎层存储引擎层 存储引擎真正的负责了MySQL中数据的存储和提取服务器通过API和存储引擎进行通 信。不同的存储引擎具有不同的功能这样我们可以根据自己的需要来选取合适的存储引擎。数据库 中的索引是在存储引擎层实现的。4).存储层数据存储层 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询 日志、慢查询日志等)存储在文件系统之上并完成与存储引擎的交互。 和其他数据库相比MySQL有点与众不同它的架构可以在多种不同场景中应用并发挥良好作用。主要 体现在存储引擎上插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。2、存储引擎存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的而不是 基于库的所以存储引擎也可被称为表类型。我们可以在创建表的时候来指定选择的存储引擎如果 没有指定将自动选择默认的存储引擎面试题:InnoDB引擎与MyISAM引擎的区别?①. InnoDB引擎,支持事务,而MyISAM不支持。②. InnoDB引擎,支持行锁和表锁,而MyISAM仅支持表锁,不支持行锁。③. InnoDB引擎,支持外键,而MyISAM是不支持的。引擎选择InnoDB:是Mysql的默认存储引擎支持事务、外键。如果应用对事务的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询之外还包含很多的更新、删除操作那么InnoDB存储引擎是比较合适的选择。MyISAM 如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完整性、并发性要求不是很高那么选择这个存储引擎是非常合适的。MEMORY将所有数据保存在内存中访问速度快通常用于临时表及缓存。MEMORY的缺陷就是 对表的大小有限制太大的表无法缓存在内存中而且无法保障数据的安全性。3、索引索引index是帮助MySQL高效获取数据的数据结构(有序)。在数据之外数据库系统还维护着满足 特定查找算法的数据结构这些数据结构以某种方式引用指向数据 这样就可以在这些数据结构 上实现高级查找算法这种数据结构就是索引B树和B树B树B树是一种多叉路衡查找树相对于二叉树B树每个节点可以有多个分支即多叉。以一颗最大度数max-degree为5(5阶)的b-tree为例那这个B树每个节点最多存储4个key5个指针特点以5阶B树为例5阶的B树每一个节点最多存储4个key对应5个指针。一旦节点存储的key数量到达5就会裂变中间元素向上分裂在B树中非叶子节点和叶子节点都会存放数据eg:插入100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250B树BTree 与B-Tree相比主要有以下三点区别所有的数据都会出现在叶子节点。叶子节点形成一个单向链表。非叶子节点仅仅起到索引数据作用具体的数据都是在叶子节点存放的。具体插入过程可使用数据结构可视化网站进行演示https://www.cs.usfca.edu/~galles/visualization/为什么使用B树哈希表优点等值查询时效率很高时间复杂度为O(1)缺点不支持范围快速查找范围查找时还是只能通过扫描全表方式二叉树问题由于二叉树的特性当数据量大时树的层级一定很高那么查询时磁盘IO操作一定很多B树多叉平衡查找树特点非叶子节点存储了索引数据和记录数据优点由于是多叉树当数据量大时树的层级较低缺点由于节点中存储的是 索引记录且记录数据的大小往往远超索引数据大小且由于一个节点的大小即为一个页的大小16KB所以一个节点中能存储的空间大部分被记录数据占用了那么树的分叉(分支因子)一定会小即树的高度会高查询时IO次数多B树多叉平衡查找树特点非叶子节点只存储索引数据所有数据(索引记录)存储在叶子节点 分支因子大树的层数低在叶子节点之间形成了双向链表 便于范围查询优点分支因子大即一个节点有很多个子节点树的高度低查询时IO次数少支持高效的范围查找3、索引分类在MySQL数据库将索引的具体类型主要分为以下几类主键索引、唯一索引、常规索引、全文索引而在在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种聚集索引主键索引 如果表中没有设置主键则聚集索引是 第一个不为null的唯一索引 二级索引非主键索引聚集索引树主键索引树的叶子节点存储的是索引数据整行记录二级索引树的叶子节点存储的是索引数据主键值4、SQL性能分析SQL执行频率慢查询日志慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有 SQL语句的日志profile详情ExplainExplain执行计划中各个字段的含义5、索引使用最左前缀法则最左前缀法则指的是查询从索引的最左列开始 并且不跳过索引中的列。如果跳跃某一列索引将会部分失效(后面的字段索引失效)。之所以有最左前缀法则是因为联合索引的索引树是这样排列的当缺少了左侧列为查询条件时右侧字段列⽆法根据索引树去查找数据所以索引失效范围查询联合索引中出现范围查询(,)范围查询右侧的列索引失效。在业务允许的情况下尽可能的使用类似于或这类的范围查询而避免使用或索引失效1 上述两种情况最左前缀法则和范围查询2 索引列运算或使用函数3字符串不加引号4模糊查询5or连接6、索引覆盖和回表覆盖索引是指 查询使用了索引并且需要返回的列在该索引中已经全部能够找到selectid,age,namefromtb.studentwhereage1;当查询语句中 以二级索引的列为 查询条件时会先走二级索引树查到叶子节点取到主键值再回到主键索引树上以刚取到的主键值去匹配找到叶子节点取得 id、age、name上述过程中查询主键索引树的这个过程叫回表需要尽量避免回表7、SQL优化慢SQL的定位 - 分析慢SQL的问题几方面分析 - 问题的解决方案就是优化慢SQL的定位首先要开启慢查询日志慢查询日志中会记录下查询时间超过设定值的SQL慢SQL的分析慢的原因可能出现在哪里数据量太大并发量太大索引没有使用或使用不当SQL 语句书写不当表结构设计不当业务设计不合理数据库服务器实例性能配置差提升服务器性能配置