本文共 5004 字，大约阅读时间需要 16 分钟。

MySQL高级部分：

1. MySQL的架构：
   
   • 从上图上看 MySQL的架构分为四层：
     • 连接层 最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的 通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证 安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验 证它所具有的操作权限
     • 服务层：
       
     • .引擎层 存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同 的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取 （常用的就是两种）
     • .存储层 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互（在高大上的文件系统最终也是需要文件系统的落地）
2. MySQL中的7种 join：
   • 需要注意的两点：
     • MySQL中的不支持全连接：也就是FULL OUTER JOIN
     • mYSQL 中实现全连接使用的是 union 进行结果的合并和 去重
3. MySQL中的索引分析：
   • 索引相关的概念：
     • 什么是索引： MySQL 官方对索引的定义为索引（Index）是帮助 MySQL 高效获取数据的数据结构可以得到索引的本质： 索引是数据结构 索引的作用：可以简单理解为排好序的快速查找数据结构（详细的知识 在上一篇博客中有解释）
     • 使用索引的优缺点：
       • 优点：
         • 提高数据检索的效率，降低数据库的IO成本
         • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
       • 缺点：
         • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE因为 更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
         • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
   • MySQL中使用的索引：
     • B+Tree 与 B-Tree 的区别
       • B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中
       • 在 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中的索引类型：
     • 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引 创建语法：CREATE INDEX 索引名称 ON 表名(字段名称); 如：CREATE INDEX idx_customer_name ON customer(customer_name);
     • 唯一索引：索引列的值必须唯一，但允许有空值 和创建单值索引的创建过程相似 只是加上了一个UNIQUE 语法：CREATE UNIQUE INDEX 索引名称 ON 表名(字段名称);
     • 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
     • 复合索引：即一个索引包含多个列
     • 索引适用的场景 和 不适用的场景：
       • 适合创建索引的情况：
         • 主键自动建立唯一索引
         • 频繁作为查询条件的字段应该创建索引
         • 查询中与其它表关联的字段，外键关系建立索引
         • 单键/组合索引的选择问题， 组合索引性价比更高
         • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
         • 查询中统计或者分组字段
       • 不适合创建索引的情况
         • 表记录太少
         • 经常增删改的表或者字段
         • Where 条件里用不到的字段不创建索引
4. MySQL中的索引优化方式：
   • Explain 性能分析：
     • 概念：使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分 析你的查询语句或是表结构的性能瓶颈
     • 用法：Explain+SQL 语句
     • EXPLAIN中的字段详解：加粗的字段表示的是重要的字段
       • id： select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序
         • id 相同 执行顺序由上至下
           
         • id 不同 如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
         • id中 既有相同也有不同 id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行
       • select_type ：代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
         
       • table：表示的是 这个数据是基于哪张表的
       • 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 全表扫描将遍历全表以找到匹配的行
       • possible_keys 显示可能应用在这张表中的索引，一个或多个 查询涉及到的字段上若存在索引，则该索引将被列出，但不一 定被查询实际使用
       • key 实际使用的索引 如果为NULL，则没有使用索引
       • key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度 key_len 字段能够帮你检查是否充分的 利用上了索引 ken_len 越长，说明索引使用的越充分
       • ref 显示索引的哪一列被使用了，如果可能的话，是一个常数 哪些列或常量被用于查找索引列上的值
       • rows 显示 MySQL 认为它执行查询时必须检查的行数越少越好
       • 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，不能用来获取任何元组 一般都是where条件出问题
5. 索引失效问题：
   • 单表中索引失效：
     • 创建如下索引：CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME); 使用age deptid 和 name 创建复合索引 使用一下的SQL都能使用到索引 另一点注意的就是：SQL 中查询字段的顺序，跟使用索引中字段的顺序，没有关系。优化器会在不影响 SQL 执行结果的前提下，给 你自动地优化

       EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

       
     • 最佳左前缀法则: 查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效
       • 原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则 指的是查询从索引的最左前列开始并且不跳过索引中的列
       • 结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用
     • 不要在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描如 ：使用函数 和 字符串不加上单引号进行类型的隐式转换(工作中是严禁不允许的不写单引号)
     • 索引列上不能有范围查询 将可能做范围查询的字段的索引顺序放在最后
     • 尽量使用覆盖索引 即 查询列和索引列一致，不要写 select *
     • 使用不等于(!= 或者<>)的时候 可能会无法使用索引会导致全表扫描
     • like 的前后模糊匹配 尽量是不要使用前缀匹配 而是使用 'xxx%'的形式
     • 减少使用 or 使用 union all 或者 union 来替代

转载地址：http://qfxrn.baihongyu.com/