MySQL逻辑架构简介

  • 和其它数据库相比,MySQL有点与众不同,它的架构可以在多种不同的场景中应用并发挥良好作用。主要体现在存储引擎的架构上。插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
  • 连接层
    • 最上层是一些客户端的链接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于链接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • 服务层
    • 第二层架构主要完成大多少的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样解决大量读操作的环境中能够很好的提升系统的性能
  • 引擎层
    • 存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API于存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实例需要进行选取。
  • 存储层
    • 数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成于存储引擎的交互。

存储引擎

  • SHOW ENGINES; # 查看数据库所有的引擎
  • SHOW VARIABLES LIKE '%storage_engine%';# 查看当前默认引擎
  • 对比项MyISAMInnoDB
    主外键不支持支持
    事物不支持支持
    行表锁表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作行锁,操作时只锁某一行,不对其它行有影响
    缓存只缓存索引,不缓存真实数据不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响。适合高并发操作
    表空间
    关注点性能事物
    默认安装YY

SQL性能下降的原因

  • 查询语句写的有问题
  • 索引失效
    • 单值
    • 复合
  • 关联查询太多JOIN(设计缺陷或不得已的需求)
  • 服务器调优及各个参数设置(缓冲、线程数等)

SQL执行顺序

索引

  • MySQL官方对索引的定义为:索引是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构
  • 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这种结构以某种方式引用数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
  • 简单理解为:排好序的快速查找数据结构
  • 影响WHERE和ORDER BY
  • 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上
  • 我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,符合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈希索引等。

索引的优势

  • 提高数据检索的效率,降低数据库的IO成本
  • 通过索引对数据进行排序,降低了数据排序的成本,降低了CPU的消耗

劣势

  • 索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引也要占用磁盘空间的
  • 虽然索引大大提高查询速度,同时也会降低更新表的速度,对表进行增删改的操作时。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所来的键值变化后的索引信息
  • 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

索引分类

  • 单值索引,即一个索引只包含单个列,一个表可以有多个单列索引
  • 唯一索引,索引的值必须唯一,但允许有空值
  • 复合索引,即一个索引包含多个列
  • 语法:
    • 创建:
      • CREATE [UNIQUE] INDEX indexName ON table(columnname(length))
      • ALTER table ADD [UNIQUE] INDEX [indexName] ON (columnname(length))
    • 删除
      • DROP INDEX [indexName] ON table
    • 查看
      • SHOW INDEX FROM table\G

索引结构

  • BTREE索引
  • Hash索引
  • full-text全文索引
  • R-Tree索引

什么时候需要建立索引

  • 主键自动建立唯一索引
  • 频繁作为查询操作的字段
  • 外键
  • 排序字段
  • 查询中统计或者分组的字段

什么时候不需要创建索引

  • 表记录太少
  • 经常需要修改的表
  • 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

EXPLAIN 性能分析

  • 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
  • EXPLAIN表头
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

id列解释

  • id 相同,执行顺序由上至下
  • id 不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
  • id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行。

select_type列解释

  • SIMPLE 简单的select查询,查询中不含子查询或UNION
  • PRIMARY 查询中若包含任何复杂的子部分,最外层查询被标记为PRIMARY
  • SUBQUERY 在SELECT 或WHERE列表中包含子查询
  • DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里。
  • UNION 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED
  • UNION RESULT 从UNION表中获取的结果SELECT

table

  • 显示这一行数据是哪张表的

type

  • 访问类型: ALL INDEX RANGE REF EQ_REF CONST SYSTEM NULL
  • 从最好到最差依次是:system > const > eq_ref > ref >range > index > ALL
  • system 表只有一行记录,这是const类型的特征,平时不会出现,这个也可以忽略不计
  • const 表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只匹配一行数据,所以很快,如将主键置于where列表中,MySQL就能将该查询转换为一个常量
  • eq_ref 唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
  • ref 非唯一索引扫描,返回匹配某个单独值得所有行。本质上也是一种索引访问,它返回所有匹配某个单独值得行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
  • range 只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引,一般就是在你的where语句中出现了between、<、>、in等查询。这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。
  • index full index scan,index于ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但是index是从索引中读取的,而all是从硬盘中读取的)
  • ALL全表扫描 将遍历全表以找到匹配的行
  • 一般来说,得保证查询至少达到range级别,最好是ref级别

possible_keys、key

  • possible_keys显示可能应用在这张表中的索引,一个或多个。查询设计到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
  • key 实际使用的索引。如果为null,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中

key_len

  • 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好
  • key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

ref

  • 显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值

rows

  • 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

Extra

  • 包含不适合在其它列中显示,但又十分重要的额外信息
  • Using filesort 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序称为“文件排序”
  • Using temporary 使用了临时表保存中间结果,MySQL在查询结果排序时使用临时表。常见于排序ORDER BY 和 GROUP BY
  • Using Index 表示相应的select操作中使用了覆盖索引,避免了访问了表的数据行,效率不错。如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找操作。
    • 覆盖索引:select的数据列只从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
  • Using where 表明使用了where过滤
  • Using join buffer 使用了连接缓存
  • impossible where where子句总是false,不能用来获取元组
  • select tables optimized away 在没有GROUP BY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行生成的阶段即完成优化
  • distinct 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

避免索引失效

  • 遵守最佳左前缀原则
    • 如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  • 不在索引列上做任何操作(计算、函数、类型转换)
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引
  • 在使用不等于的时候无法使用索引
  • is null 和 is not null 也无法使用索引
  • like 以通配符开头会导致索引失效(以通配符开头时可以使用覆盖索引)
  • 字符串不加单引号会导致索引失效
  • 少用or,用它来连接时会索引失效

建议

  • 对于单值索引,尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
  • 在选择组合索引的时候,尽量选择能够包含当前query中的where子句更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

查询分析

  • 慢查询的开发并捕获
  • explain + 慢SQL分析
  • show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况
  • SQL数据库服务器的参数调优