一、什么是MySQL?

MySQL 是一种关系型数据库,在Java企业级开发中非常常用,因为 MySQL 是开源免费的,并且方便扩展。阿里巴巴数据库系统也大量用到了 MySQL,因此它的稳定性是有保障的。MySQL是开放源代码的,因此任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL的默认端口号是3306

一. 1 MySQL 基础架构分析

下图是 MySQL 的一个简要架构图,从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。

先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图,再详细介绍这些组件的作用。

  • 连接器: 身份认证和权限相关(登录 MySQL 的时候)。
  • 查询缓存: 执行查询语句的时候,会先查询缓存(MySQL 8.0 版本后移除,因为这个功能不太实用)。
  • 分析器: 没有命中缓存的话,SQL 语句就会经过分析器,分析器说白了就是要先看你的 SQL 语句要干嘛,再检查你的 SQL 语句语法是否正确。
  • 优化器: 按照 MySQL 认为最优的方案去执行。
  • 执行器: 执行语句,然后从存储引擎返回数据。

2019-08-10-18-17-49.png

简单来说 MySQL 主要分为 Server 层和存储引擎层:

  • Server 层:主要包括连接器、查询缓存、分析器、优化器、执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图,函数等,还有一个通用的日志模块 binglog 日志模块。
  • 存储引擎: 主要负责数据的存储和读取,采用可以替换的插件式架构,支持 InnoDB、MyISAM、Memory 等多个存储引擎,其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

*-_

1) 连接器

连接器主要和身份认证和权限相关的功能相关,就好比一个级别很高的门卫一样。

主要负责用户登录数据库,进行用户的身份认证,包括校验账户密码,权限等操作,如果用户账户密码已通过,连接器会到权限表中查询该用户的所有权限,之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据,也就是说,后续只要这个连接不断开,即时管理员修改了该用户的权限,该用户也是不受影响的。

2) 查询缓存

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

连接建立后,执行查询语句的时候,会先查询缓存,MySQL 会先校验这个 sql 是否执行过,以 Key-Value 的形式缓存在内存中,Key 是查询预计,Value 是结果集。如果缓存 key 被命中,就会直接返回给客户端,如果没有命中,就会执行后续的操作,完成后也会把结果缓存起来,方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限,是否有该表的查询条件。

MySQL 查询不建议使用缓存,因为查询缓存失效在实际业务场景中可能会非常频繁,假如你对一个表更新的话,这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说,使用缓存还是可以的。

所以,一般在大多数情况下我们都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能,官方也是认为该功能在实际的应用场景比较少,所以干脆直接删掉了。

3) 分析器

MySQL 没有命中缓存,那么就会进入分析器,分析器主要是用来分析 SQL 语句是来干嘛的,分析器也会分为几步:

第一步,词法分析,一条 SQL 语句有多个字符串组成,首先要提取关键字,比如 select,提出查询的表,提出字段名,提出查询条件等等。做完这些操作后,就会进入第二步。

第二步,语法分析,主要就是判断你输入的 sql 是否正确,是否符合 MySQL 的语法。

完成这 2 步之后,MySQL 就准备开始执行了,但是如何执行,怎么执行是最好的结果呢?这个时候就需要优化器上场了。

4) 优化器

优化器的作用就是它认为的最优的执行方案去执行(有时候可能也不是最优,这篇文章涉及对这部分知识的深入讲解),比如多个索引的时候该如何选择索引,多表查询的时候如何选择关联顺序等。

可以说,经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

5) 执行器

当选择了执行方案后,MySQL 就准备开始执行了,首先执行前会校验该用户有没有权限,如果没有权限,就会返回错误信息,如果有权限,就会去调用引擎的接口,返回接口执行的结果。

*-_

一. 2 语句执行流程

1 查询语句

说了以上这么多,那么究竟一条 sql 语句是如何执行的呢?其实我们的 sql 可以分为两种,一种是查询,一种是更新(增加,更新,删除)。我们先分析下查询语句,语句如下:

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

结合上面的说明,我们分析下这个语句的执行流程:

  • 先检查该语句是否有权限,如果没有权限,直接返回错误信息,如果有权限,会先查询缓存,以这条 sql 语句为 key 在内存中查询是否有结果,如果有直接缓存,如果没有,执行下一步。

  • 通过分析器进行词法分析,提取 sql 语句的关键元素,比如提取上面这个语句是查询 select,提取需要查询的表名为 tb_student,需要查询所有的列,查询条件是这个表的 id=’1’。然后判断这个 sql 语句是否有语法错误,比如关键词是否正确等等,如果检查没问题就执行下一步。

  • 接下来就是优化器进行确定执行方案,上面的 sql 语句,可以有两种执行方案:

      a.先查询学生表中姓名为“张三”的学生,然后判断是否年龄是 18。
      b.先找出学生中年龄 18 岁的学生,然后再查询姓名为“张三”的学生。
    

    那么优化器根据自己的优化算法进行选择执行效率最好的一个方案(优化器认为,有时候不一定最好)。那么确认了执行计划后就准备开始执行了。

  • 进行权限校验,如果没有权限就会返回错误信息,如果有权限就会调用数据库引擎接口,返回引擎的执行结果。

2 更新语句

以上就是一条查询 sql 的执行流程,那么接下来我们看看一条更新语句如何执行的呢?sql 语句如下:

update tb_student A set A.age='19' where A.name=' 张三 ';

我们来给张三修改下年龄,在实际数据库肯定不会设置年龄这个字段的,不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走,只不过执行更新的时候肯定要记录日志啦,这就会引入日志模块了,MySQL 自带的日志模块式 binlog(归档日志) ,所有的存储引擎都可以使用,我们常用的 InnoDB 引擎还自带了一个日志模块 redo log(重做日志),我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下:

  • 先查询到张三这一条数据,如果有缓存,也是会用到缓存。
  • 然后拿到查询的语句,把 age 改为 19,然后调用引擎 API 接口,写入这一行数据,InnoDB 引擎把数据保存在内存中,同时记录 redo log,此时 redo log 进入 prepare 状态,然后告诉执行器,执行完成了,随时可以提交。
  • 执行器收到通知后记录 binlog,然后调用引擎接口,提交 redo log 为提交状态。
  • 更新完成。

这里肯定有同学会问,为什么要用两个日志模块,用一个日志模块不行吗?

这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ,MySQL 自带的引擎是 MyISAM,但是我们知道 redo log 是 InnoDB 引擎特有的,其他存储引擎都没有,这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启,之前提交的记录都不会丢失),binlog 日志只能用来归档。

并不是说只用一个日志模块不可以,只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么,又会有同学问,我用两个日志模块,但是不要这么复杂行不行,为什么 redo log 要引入 prepare 预提交状态?这里我们用反证法来说明下为什么要这么做?

  • 先写 redo log 直接提交,然后写 binlog,假设写完 redo log 后,机器挂了,binlog 日志没有被写入,那么机器重启后,这台机器会通过 redo log 恢复数据,但是这个时候 bingog 并没有记录该数据,后续进行机器备份的时候,就会丢失这一条数据,同时主从同步也会丢失这一条数据。
  • 先写 binlog,然后写 redo log,假设写完了 binlog,机器异常重启了,由于没有 redo log,本机是无法恢复这一条记录的,但是 binlog 又有记录,那么和上面同样的道理,就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了,写完 binglog 后,然后再提交 redo log 就会防止出现上述的问题,从而保证了数据的一致性。那么问题来了,有没有一个极端的情况呢?假设 redo log 处于预提交状态,binglog 也已经写完了,这个时候发生了异常重启会怎么样呢? 这个就要依赖于 MySQL 的处理机制了,MySQL 的处理过程如下:

  • 判断 redo log 是否完整,如果判断是完整的,就立即提交。
  • 如果 redo log 只是预提交但不是 commit 状态,这个时候就会去判断 binlog 是否完整,如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。

总结

  • MySQL 主要分为 Server 层和引擎层,Server 层主要包括连接器、查询缓存、分析器、优化器、执行器,同时还有一个日志模块(binlog),这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。
  • 引擎层是插件式的,目前主要包括,MyISAM,InnoDB,Memory 等。
  • 查询语句的执行流程如下:权限校验(如果命中缓存)—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
  • 更新语句执行流程如下:分析器—-》权限校验—-》执行器—》引擎—redo log(prepare 状态—》binlog—》redo log(commit状态)

*-_

二、存储引擎

InnoDB

是 MySQL 默认的事务型存储引擎,只有在需要它不支持的特性时,才考虑使用其它存储引擎。

实现了四个标准的隔离级别,默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下,通过多版本并发控制(MVCC)+ Next-Key Locking 防止幻影读。

主索引是聚簇索引,在索引中保存了数据,从而避免直接读取磁盘,因此对查询性能有很大的提升。

内部做了很多优化,包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份,要获取一致性视图需要停止对所有表的写入,而在读写混合场景中,停止写入可能也意味着停止读取。

MyISAM

设计简单,数据以紧密格式存储。对于只读数据,或者表比较小、可以容忍修复操作,则依然可以使用它。

提供了大量的特性,包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁,只能对整张表加锁,读取时会对需要读到的所有表加共享锁,写入时则对表加排它锁。但在表有读取操作的同时,也可以往表中插入新的记录,这被称为并发插入(CONCURRENT INSERT)。

可以手工或者自动执行检查和修复操作,但是和事务恢复以及崩溃恢复不同,可能导致一些数据丢失,而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项,在每次修改执行完成时,不会立即将修改的索引数据写入磁盘,而是会写到内存中的键缓冲区,只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能,但是在数据库或者主机崩溃时会造成索引损坏,需要执行修复操作。

比较

  • 事务:InnoDB 是事务型的,可以使用 Commit 和 Rollback 语句。

  • 并发:MyISAM 只支持表级锁,而 InnoDB 还支持行级锁。

  • 外键:InnoDB 支持外键。

  • 备份:InnoDB 支持在线热备份。

  • 崩溃恢复:MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多,而且恢复的速度也更慢。

  • 其它特性:MyISAM 支持压缩表和空间数据索引。

    一些常用命令

    查看MySQL提供的所有存储引擎

    mysql> show engines;
    

    查看MySQL当前默认的存储引擎

    我们也可以通过下面的命令查看默认的存储引擎。

    mysql> show variables like '%storage_engine%';
    

    查看表的存储引擎

    show table status like "table_name" ;
    

    修改表的存储引擎

    alter table table_name engine=engine_name
    

*-_

三、SQL大表优化

当MySQL单表记录数过大时,数据库的CRUD性能会明显下降,一些常见的优化措施如下:

单表优化

数据库三范式是什么

·第一范式(1NF):

字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式)数据库表中的字段都是单一属性的,不可再分。例如,姓名字段,其中的姓和名必须作为一个整体,无法区分哪部分是姓,哪部分是名,如果要区分出姓和名,必须设计成两个独立的字段。

·第二范式(2NF):

第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列,以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。

第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是非主属性非部分依赖于主关键字。

第三范式的要求如下:

满足第三范式(3NF)必须先满足第二范式(2NF)。简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。所以第三范式具有如下特征: 1,每一列只有一个值 2,每一行都能区分。 3,每一个表都不包含其他表已经包含的非主关键字信息。

例如,帖子表中只能出现发帖人的 id,而不能出现发帖人的 id,还同时出现发帖人姓名,否则,只要出现同一发帖人 id 的所有记录,它们中的姓名部分都必须严格保持一致,这就是数据冗余。

*-_

除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量:

字段

  • 尽量使用TINYINTSMALLINTMEDIUM_INT作为整数类型而非INT,如果非负则加上UNSIGNED
  • VARCHAR的长度只分配真正需要的空间
  • 使用枚举或整数代替字符串类型
  • 尽量使用TIMESTAMP而非DATETIME
  • 单表不要有太多字段,建议在20以内
  • 避免使用NULL字段,很难查询优化且占用额外索引空间
  • 用整型来存IP

索引

  • 索引并不是越多越好,要根据查询有针对性的创建,考虑在WHEREORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描
  • 应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描
  • 值分布很稀少的字段不适合建索引,例如”性别”这种只有两三个值的字段
  • 字符字段只建前缀索引
  • 字符字段最好不要做主键
  • 不用外键,由程序保证约束
  • 尽量不用UNIQUE,由程序保证约束
  • 使用多列索引时主意顺序和查询条件保持一致,同时删除不必要的单列索引

查询SQL

  • 可通过开启慢查询日志来找出较慢的SQL
  • 不做列运算:SELECT id WHERE age + 1 = 10,任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能将操作移至等号右边
  • sql语句尽可能简单:一条sql只能在一个cpu运算;大语句拆小语句,减少锁时间;一条大sql可以堵死整个库
  • 不用SELECT *
  • OR改写成INOR的效率是n级别,IN的效率是log(n)级别,in的个数建议控制在200以内
  • 不用函数和触发器,在应用程序实现
  • 避免%xxx式查询
  • 少用JOIN
  • 使用同类型进行比较,比如用'123''123'比,123123
  • 尽量避免在WHERE子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描
  • 对于连续数值,使用BETWEEN不用INSELECT id FROM t WHERE num BETWEEN 1 AND 5
  • 列表数据不要拿全表,要使用LIMIT来分页,每页数量也不要太大

引擎

目前广泛使用的是MyISAM和InnoDB两种引擎:

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎,它的特点是:

  • 不支持行锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁
  • 不支持事务
  • 不支持外键
  • 不支持崩溃后的安全恢复
  • 在表有读取查询的同时,支持往表中插入新纪录
  • 支持BLOBTEXT的前500个字符索引,支持全文索引
  • 支持延迟更新索引,极大提升写入性能
  • 对于不会进行修改的表,支持压缩表,极大减少磁盘空间占用
InnoDB

InnoDB在MySQL 5.5后成为默认索引,它的特点是:

  • 支持行锁,采用MVCC来支持高并发
  • 支持事务
  • 支持外键
  • 支持崩溃后的安全恢复
  • 不支持全文索引

总体来讲,MyISAM适合SELECT密集型的表,而InnoDB适合INSERTUPDATE密集型的表。

分表优化

读写分离

也是目前常用的优化,从库读主库写,一般不要采用双主或多主引入很多复杂性,尽量采用其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离。

垂直拆分

垂直分库是根据数据库里面的数据表的相关性进行拆分,比如:一个数据库里面既存在用户数据,又存在订单数据,那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式,常见的是把一个多字段的大表按常用字段和非常用字段进行拆分,每个表里面的数据记录数一般情况下是相同的,只是字段不一样,使用主键关联

比如原始的用户表是:

2019-08-10-17-00-59.png

垂直拆分后是:

2019-08-10-17-06-13.png

垂直拆分的优点是:

  • 可以使得行数据变小,一个数据块(Block)就能存放更多的数据,在查询时就会减少I/O次数(每次查询时读取的Block 就少)
  • 可以达到最大化利用Cache的目的,具体在垂直拆分的时候可以将不常变的字段放一起,将经常改变的放一起
  • 数据维护简单

缺点是:

  • 主键出现冗余,需要管理冗余列

  • 会引起表连接JOIN操作(增加CPU开销)可以通过在业务服务器上进行join来减少数据库压力

  • 依然存在单表数据量过大的问题(需要水平拆分)

  • 事务处理复杂

水平拆分

概述

水平拆分是通过某种策略将数据分片来存储,分库内分表和分库两部分,每片数据会分散到不同的MySQL表或库,达到分布式的效果,能够支持非常大的数据量。MySQL的表分区本质上也是一种特殊的库内分表

库内分表,仅仅是单纯的解决了单一表数据过大的问题,由于没有把表的数据分布到不同的机器上,因此对于减轻MySQL服务器的压力来说,并没有太大的作用,大家还是竞争同一个物理机上的IO、CPU、网络,这个就要通过分库来解决

前面垂直拆分的用户表如果进行水平拆分,结果是:

2019-08-10-17-10-06.png

实际情况中往往会是垂直拆分和水平拆分的结合,即将Users_A_MUsers_N_Z再拆成UsersUserExtras,这样一共四张表

水平拆分的优点是:

  • 不存在单库大数据和高并发的性能瓶颈
  • 应用端改造较少
  • 提高了系统的稳定性和负载能力

缺点是:

  • 分片事务一致性难以解决
  • 跨节点Join性能差,逻辑复杂
  • 数据多次扩展难度跟维护量极大

分片原则

  • 能不分就不分,参考单表优化
  • 分片数量尽量少,分片尽量均匀分布在多个数据结点上,因为一个查询SQL跨分片越多,则总体性能越差,虽然要好于所有数据在一个分片的结果,只在必要的时候进行扩容,增加分片数量
  • 分片规则需要慎重选择做好提前规划,分片规则的选择,需要考虑数据的增长模式,数据的访问模式,分片关联性问题,以及分片扩容问题,最近的分片策略为范围分片,枚举分片,一致性Hash分片,这几种分片都有利于扩容
  • 尽量不要在一个事务中的SQL跨越多个分片,分布式事务一直是个不好处理的问题
  • 查询条件尽量优化,尽量避免Select * 的方式,大量数据结果集下,会消耗大量带宽和CPU资源,查询尽量避免返回大量结果集,并且尽量为频繁使用的查询语句建立索引。
  • 通过数据冗余和表分区赖降低跨库Join的可能

这里特别强调一下分片规则的选择问题,如果某个表的数据有明显的时间特征,比如订单、交易记录等,则他们通常比较合适用时间范围分片,因为具有时效性的数据,我们往往关注其近期的数据,查询条件中往往带有时间字段进行过滤,比较好的方案是,当前活跃的数据,采用跨度比较短的时间段进行分片,而历史性的数据,则采用比较长的跨度存储。

总体上来说,分片的选择是取决于最频繁的查询SQL的条件,因为不带任何Where语句的查询SQL,会遍历所有的分片,性能相对最差,因此这种SQL越多,对系统的影响越大,所以我们要尽量避免这种SQL的产生。

解决方案

由于水平拆分牵涉的逻辑比较复杂,当前也有了不少比较成熟的解决方案。这些方案分为两大类:客户端架构和代理架构。

客户端架构

通过修改数据访问层,如JDBC、Data Source、MyBatis,通过配置来管理多个数据源,直连数据库,并在模块内完成数据的分片整合,一般以Jar包的方式呈现

这是一个客户端架构的例子:

2019-08-10-17-14-02.png

可以看到分片的实现是和应用服务器在一起的,通过修改Spring JDBC层来实现

客户端架构的优点是:

  • 应用直连数据库,降低外围系统依赖所带来的宕机风险
  • 集成成本低,无需额外运维的组件

缺点是:

  • 限于只能在数据库访问层上做文章,扩展性一般,对于比较复杂的系统可能会力不从心
  • 将分片逻辑的压力放在应用服务器上,造成额外风险
代理架构

通过独立的中间件来统一管理所有数据源和数据分片整合,后端数据库集群对前端应用程序透明,需要独立部署和运维代理组件

这是一个代理架构的例子:

2019-08-10-17-15-53.png

代理组件为了分流和防止单点,一般以集群形式存在,同时可能需要Zookeeper之类的服务组件来管理

代理架构的优点是:

  • 能够处理非常复杂的需求,不受数据库访问层原来实现的限制,扩展性强
  • 对于应用服务器透明且没有增加任何额外负载

缺点是:

  • 需部署和运维独立的代理中间件,成本高

  • 应用需经过代理来连接数据库,网络上多了一跳,性能有损失且有额外风险

四、索引

MySQL 索引

概述

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。

创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

4.1 索引的优点

为什么要创建索引?这是因为,创建索引可以大大提高系统的查询性能。

第一、通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

第二、可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。

第三、可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

第四、在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

第五、通过使用索引,可以在查询的过程中,使用查询优化器,提高系统的性能。

4.2 索引的缺点

也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点, 但是,为表中的每一个列都增加索引,是非常不明智的。 这是因为,增加索引也有许多不利的一个方面:

第一、创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

第二、索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。如果要建立聚簇索引,那么需要的空间就会更大。

第三、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

4.3 什么样的字段适合创建索引

索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。 一般来说,应该在具备下述特性的列上创建索引:

第一、在经常需要搜索的列上,可以加快搜索的速度;

第二、在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

第三、在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;

第四、在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

第五、在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;

第六、在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

建立索引,一般按照select的where条件来建立,比如: select的条件是where f1 and f2,那么如果我们在字段f1或字段f2上建立索引是没有用的,只有在字段f1和f2上同时建立索引才有用等。

4.4 什么样的字段不适合创建索引:

同样,对于有些列不应该创建索引。一般来说,不应该创建索引的这些列具有下述特点:

第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

Mysql如何为表字段添加索引???

1.添加PRIMARY KEY(主键索引)

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

6.显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下实例:

mysql> SHOW INDEX FROM table_name; \G
........

*-_

4.5 MySQL中的索引种类

MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择BTree索引。

MySQL的BTree索引使用的是B树中的B+Tree,索引是在存储引擎层实现的,而不是在服务器层实现的,所以不同存储引擎具有不同的索引类型和实现。

  • MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其 data 域的值,然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
  • InnoDB: 其数据文件本身就是索引文件。相比MyISAM,索引文件和数据文件是分离的,其表数据文件本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引(或聚集索引)”。而其余的索引都作为辅助索引,辅助索引的data域存储相应记录主键的值而不是地址,这也是和MyISAM不同的地方。在根据主索引搜索时,直接找到key所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,再走一遍主索引。 因此,在设计表的时候,不建议使用过长的字段作为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。 

1. B+Tree 索引

是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描,只需要对树进行搜索即可,所以查找速度快很多。

因为 B+ Tree 的有序性,所以除了用于查找,还可以用于排序和分组。

可以指定多个列作为索引列,多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找,其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找,则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录,这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值,因此在使用辅助索引进行查找时,需要先查找到主键值,然后再到主索引中进行查找。

2.哈希索引

哈希索引能以 O(1) 时间进行查找,但是失去了有序性:

  • 无法用于排序与分组;
  • 只支持精确查找,无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”,当某个索引值被使用的非常频繁时,会在 B+Tree 索引之上再创建一个哈希索引,这样就让 B+Tree 索引具有哈希索引的一些优点,比如快速的哈希查找。

3. 全文索引

MyISAM 存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等。

查找条件使用 MATCH AGAINST,而不是普通的 WHERE。

全文索引使用倒排索引实现,它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

4. 空间数据索引

MyISAM 存储引擎支持空间数据索引(R-Tree),可以用于地理数据存储。空间数据索引会从所有维度来索引数据,可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

索引优化

1. 独立的列

在进行查询时,索引列不能是表达式的一部分,也不能是函数的参数,否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引:

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

2. 多列索引

在需要使用多个列作为条件进行查询时,使用多列索引比使用多个单列索引性能更好。例如下面的语句中,最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

3. 索引列的顺序

让选择性最强的索引列放在前面。

索引的选择性是指:不重复的索引值和记录总数的比值。最大值为 1,此时每个记录都有唯一的索引与其对应。选择性越高,每个记录的区分度越高,查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高,因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;
   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

4. 前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列,必须使用前缀索引,只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定。

5. 覆盖索引

索引包含所有需要查询的字段的值。

具有以下优点:

  • 索引通常远小于数据行的大小,只读取索引能大大减少数据访问量。
  • 一些存储引擎(例如 MyISAM)在内存中只缓存索引,而数据依赖于操作系统来缓存。因此,只访问索引可以不使用系统调用(通常比较费时)。
  • 对于 InnoDB 引擎,若辅助索引能够覆盖查询,则无需访问主索引。

6.最左前缀原则

MySQL中的索引可以以一定顺序引用多列,这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city),而最左前缀原则指的是,如果查询的时候查询条件精确匹配索引的左边连续一列或几列,则此列就可以被用到。如下:

select * from user where name=xx and city=xx ; //可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引            

这里需要注意的是,查询的时候如果两个条件都用上了,但是顺序不同,如 city= xx and name =xx,那么现在的查询引擎会自动优化为匹配联合索引的顺序,这样是能够命中索引的。

由于最左前缀原则,在创建联合索引时,索引字段的顺序需要考虑字段值去重之后的个数,较多的放前面。ORDER BY子句也遵循此规则。

7.注意避免冗余索引

冗余索引指的是索引的功能相同,能够命中 就肯定能命中 ,那么 就是冗余索引如(name,city )和(name )这两个索引就是冗余索引,能够命中后者的查询肯定是能够命中前者的 在大多数情况下,都应该尽量扩展已有的索引而不是创建新索引。

MySQL5.7 版本后,可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

索引的优点

  • 大大减少了服务器需要扫描的数据行数。
  • 帮助服务器避免进行排序和分组,以及避免创建临时表(B+Tree 索引是有序的,可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建,不需要排序和分组,也就不需要创建临时表)。
  • 将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的,会将相邻的数据都存储在一起)。

索引的使用条件

  • 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效;
  • 对于中到大型的表,索引就非常有效;
  • 但是对于特大型的表,建立和维护索引的代价将会随之增长。这种情况下,需要用到一种技术可以直接区分出需要查询的一组数据,而不是一条记录一条记录地匹配,例如可以使用分区技术。

索引总结

最后总结一下,MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引,不过除非是数据量真的很多,否则过多的使用索引也不是那么好玩的。

建议:一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

数据库索引总结

*-_

五、sql优化

5.1 避免全表扫描

对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

5.2 避免判断null值

应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

5.3 避免不等值判断

应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。

5.4 避免使用or逻辑

应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num=10 or num=20

可以这样查询:

select id from t where num=10

union all

select id from t where num=20

5.5 慎用in和not in逻辑

in 和 not in 也要慎用,否则会导致全表扫描,如:

select id from t1 where num in(select id from t2 where id > 10)

此时外层查询会全表扫描,不使用索引。可以修改为:

select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id

此时索引被使用,可以明显提升查询效率。

5.6 注意模糊查询

下面的查询也将导致全表扫描:

select id from t where name like ‘%abc%’

模糊查询如果是必要条件时,可以使用select id from t where name like ‘abc%’来实现模糊查询,此时索引将被使用。如果头匹配是必要逻辑,建议使用全文搜索引擎(Elastic search、Lucene、Solr等)。

5.7 避免查询条件中字段计算

应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num/2=100

应改为:

select id from t where num=100*2

5.8 避免查询条件中对字段进行函数操作

应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where substring(name,1,3)=’abc’–name以abc开头的id

应改为:

select id from t where name like ‘abc%’

5.9 WHERE子句“=”左边不要运算

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

5.10 组合索引使用

在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

5.11 不要定义无异议的查询

不要写一些没有意义的查询,如需要生成一个空表结构:

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

create table #t(…)

5.12 exists

很多时候用 exists 代替 in 是一个好的选择:

select num from a where num in(select num from b)

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)

5.13 索引也可能失效

并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

5.14 表格字段类型选择

尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

尽可能的使用 varchar 代替 char ,因为首先可变长度字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

5.15 查询语法中的字段

任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

5.16 索引无关优化

不使用*、尽量不使用union,union all等关键字、尽量不使用or关键字、尽量使用等值判断。

表连接建议不超过5个。如果超过5个,则考虑表格的设计。(互联网应用中)

表连接方式使用外联优于内联。

外连接有基础数据存在。如:A left join B,基础数据是A。

A inner join B,没有基础数据的,先使用笛卡尔积完成全连接,在根据连接条件得到内连接结果集。

大数据量级的表格做分页查询时,如果页码数量过大,则使用子查询配合完成分页逻辑。

Select * from table limit 1000000, 10

Select * from table where id in (select pk from table limit 100000, 10)

六、性能优化

1.1 慢查询

如何发现有问题的SQL?使用mysql慢查询日志对有效率问题的SQL进行监控

Copy//查看慢查询日志是否开启
show variables like 'slow_query_log';

//查看慢查询日志存储位置
show variables like 'slow_query_log_file';

//开启慢查询日志
set global slow_query_log=on;

//指定慢查询日志存储位置
set global show_query_log_file='/var/lib/mysql/homestead-slow.log';

//记录没有使用索引的sql
set global log_queries_not_using_indexes=on;

//记录查询超过1s的sql
set global long_query_time=1;

慢查询日志所包含的内容:

Copy#User@Host:root[root] @localhost[]//执行sql的主机信息
#Query_time:0.0000024 Lock_time:0.00 Rows_sent:0 Rows_esamined:0//sql的执行信息
SET timestamp=1402389324//sql执行时间
select * from store; //sql的内容

MySQL慢查询日志分析工具之mysqldumpslow(mysql官方)

安装完MySQL后,默认就带了mysqldumpslow,很常用的一个工具。(有问题,使用不了)

Copy//查看参数列表
mysqldumpslow -h

//分析慢查询日志中前三条比较慢的sql
mysqldumpslow -t 3 /var/lib/mysql/homestead-slow.log | more 

//输出样式效果
Count:1 Time:0.00s Lock=0.00s Rows=10.0
root[rppt]@localhost
select * from store

MySQL慢查询日志分析工具之pt-query-digest

分析结果比mysqldumpslow更详细全面

Copy//输出到文件
pt-query-digest slow-log > slow_log.report

//输出到数据表
pt-query-digest slow.log -review \
    h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review \
    --create-reviewtable \
    --review-history t=hostname_slow

基本使用

Copy//查看参数列表
pt-query-digest --help

//分析慢查询日志中前三条比较慢的sql
pt-query-digest /var/lib/mysql/homestead-slow.log | more 

//输出分为三部分
1.显示除了日志的时间范围,以及总的sql数量和不同的sql数量
2.Response Time:响应时间占比 Calls:sql执行次数
3.sql的具体日志

如何通过慢查询日志发现有问题的SQL?

Copy1.查询次数多且每次查询占用时间长的SQL
通常为pt-query-digest分析的前几个查询

2.IO大的SQL(数据库主要瓶颈出现在IO层次)
注意pt-query-digest分析中的Rows examine项

3.未命中索引的SQL
注意pt-query-digest分析中的Rows examine和Rows Send的对比

1.2通过explain查询和分析SQL的执行计划

explain select customer_id,,first_name,last_name from customer;
Column 1 Column 2 Column 3
id    
select_type SIMPLE  
table customer 该数据关于哪张表。
type ALL 示连接使用了何种类型。从好到差const,eq_reg,ref,range,index和ALL。
possible_keys NULL 可能应用在该表的索引,空,没有可能的索引。
key NULL 实际使用的索引。空,没有使用索引。
key_len NULL 使用的索引长度。不损失精度下,越短越好。
ref NULL 显示索引的哪一列被使用了,常数。
rows 671 mysql认为必须检查的数据的行数。
Extra 注意:Using filesort,Using tempoary

1.2 Count()和Max()的优化

//查询最后支付时间--优化max()函数
explain select max(payment_date) from payment;
create index idx_paydate on payment(payment_data);//给payment_date建立索引(覆盖索引)

//在一条SQL中同时查出2006年和2007年电影的数量--优化Count()函数
select count(release_year='2006' or null) as '2006年电影数量',count(release_year='2007' or null) as '2007年电影数量' from film;
//有关count()函数
https://blog.csdn.net/wendychiang1991/article/details/70909958/

1.3 子查询优化

通常情况下,需要把子查询优化为join查询,但在优化时要注意关联键是否有一对多的关系,要注意重复数据。(distinct去重)
//查询sandra出演的所有影片
explain select title,release_year,LENGTH from film
where film_id in (
select film_id from film_actor where actor_id in (
select actor_id from actor where first_name='sandra'));

1.4 group by的优化

//改前 临时表
explain select actor.first_name,actor_last_name,count(*) from sakila.film_actor
inner join sakila.actor USING(actor_id)
group by film_actor.actor_id;
//改后 结合子查询 索引
explain select actor.first_name,actor.last_name,c.cnt from sakila.film_actor
inner join (
select actor_id,count(*) as cnt from sakila.film_actor group by actor_id) as c USING(actor_id);

1.5 limit优化

limit常用于分页处理,时常会伴随order by 从句使用,因此大多时候会使用Filesorts这样会造成大量的IO问题。

//文件排序,IO大
explain select film_id,description from sakila.film order by title limit 50,5;
1.优化:使用有索引的列或主键进行order by操作(order by film_id)
2.记录上次返回的主键,在下次查询的时候用主键过滤,避免了数据量大时扫描过多的记录
select film_id,description from sakila.film where film_if>55 and film_id<=60 order by film_id limit 1,5; 
页数越大,IO越大

###

六、事务

事务隔离级别有哪些?MySQL的默认隔离级别是?

SQL 标准定义了四个隔离级别:

  • READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读
  • READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生
  • REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生
  • SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读

隔离级别 脏读 不可重复读 幻影读
READ-UNCOMMITTED
READ-COMMITTED ×
REPEATABLE-READ × ×
SERIALIZABLE × × ×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)。我们可以通过SELECT @@tx_isolation;命令来查看

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这里需要注意的是:与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ(可重读) 事务隔离级别下使用的是Next-Key Lock 锁算法,因此可以避免幻读的产生,这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读) 已经可以完全保证事务的隔离性要求,即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。因为隔离级别越低,事务请求的锁越少,所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ,但是你要知道的是InnoDB 存储引擎默认使用 REPEAaTABLE-READ(可重读) 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

八、补充

MySQL高性能优化规范建议

MySQL高性能优化规范建议

一条SQL语句执行得很慢的原因有哪些?

腾讯面试:一条SQL语句执行得很慢的原因有哪些?—不看后悔系列