mysql总结

mysql总结

什么是MySQL

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

mysql数据库主要组成部分

MySQL 主要分为 Server 层和存储引擎层：

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

mysql的sql语句执行流程

1、client和server建立连接，client发送sql至server（对应连接器这一过程）

2、server如果在查询缓存中发现了该sql，则直接使用查询缓存的结果返回给client，如果查询缓存中没有，则进入下面的步骤（对应查询缓存这一过程，8.0这一过程已经不存在了，8.0完全抛弃了这个功能）

3、server对sql进行语法分析，识别出sql里面的字符串是否符合标准，比如select关键字不能写错（对应分析器这一过程）

4、server对sql进行语义分析，识别出sql里面的字符串的具体意思是什么，比如T1是表名，C1是列名（对应分析器这一过程）

5、server确定sql的执行路径，比如走索引还是全表（对应优化器这一过程）

6、server对执行sql的用户进行权限检查，比如对表是否有权限执行(对应执行器这一过程)

7、server执行该sql语句，发送结果给client(对应执行器这一过程)

连接器–>查询缓存–>分析器–>优化器–>执行器

连接器：

用来和服务器建立连接，TCP握手后完成认证过程。

• 账号密码不对，报错：“Access denied for user”
• 认证通过，在权限表中查询拥有的权限

只有在连接过程才会从权限表中读取权限信息，中途对权限的修改不会影响已经建立的连接，只有重新登陆后才会使用新的权限信息。

查询缓存：

一个请求进来，首先查询缓存，是否存在该记录。之前执行过的语句，则会以语句为key,执行后的结果集为value存储在内存中。

• 如果在缓存中查找到，则回直接返回给客户端。
• 如果没有找到记录，继续执行后面的操作，并将该语句与结果分别以key-value存入内存。

不建议使用查询缓存，此操作弊大于利 ( MySQL8.0后将会删除该功能 )

分析器：

1、词法分析
识别SQL关键字，提出主要成分。MySQL根据"select"得出这是一条查询语句，根据"from T"识别表T，将"ID"识别为列名。

2、语法分析
根据语法规则判断是否符合SQL的语法。如果出现错误，则提示："You have an error in your SQL syntax...",后面是错误的地方，需要你关注"use naer"后的内容。

优化器：

这个步骤将会选取最优的执行方式，例如：

1. 当涉及到多个索引时，决定用哪个索引
2. 多表关联时，决定连接顺序

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

该步骤决定了先从t1表中取出c=10的ID值还是先查找出t2表中d=20的ID值。

执行器：

真正执行步骤

根据连接器取到的权限判断，是否对表T有权限。

• 若有权限，打开表继续操作。

打开表后，根据表的引擎定义，使用引擎提供的接口。
例：

• 表T中ID字段无索引：
  • 调用InnoDB提供的接口，取出第一行，当ID=10时，将该行数据放入结果集。若不符合则字啊次调用引擎接口获取下一行，再次判断，直至最后一行数据。
  • 将结果集存入缓存、返回给客户端。
• 表T中ID字段有索引：
  • 调用InnoDB的"获取满足条件的第一行"接口，server层还会再判断一次值是否正确，然后放入结果集，接着继续访问"满足条件的下一行"接口，这些接口是引擎已经定义好的。

在MySQL的慢查询日志中，rows_examined字段表示该语句执行过程中扫描了多少行，这个值就是调用引擎获取数据行的时候添加的。

索引

因为我们不想扫描整表，故考虑用索引。

单列索引：

ALTER TABLE people ADD INDEX lname (lname);

将lname列建索引，这样就把范围限制在lname='Liu’的结果集1上，之后扫描结果集1，产生满足fname='Zhiqun’的结果集2，再扫描结果集2，找到 age=26的结果集3，即最终结果。

由 于建立了lname列的索引，与执行表的完全扫描相比，效率提高了很多，但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引，再创建fname或者age 列的索引，但是，不论在哪个列上创建索引搜索效率仍旧相似。

多列索引：

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。

注：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

最左前缀：

顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。

注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

建立索引的时机

​ 在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%'

下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin'

索引的不足之处

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

使用索引注意事项

• 索引不会包含有NULL值的列

​ 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

• 使用短索引

​ 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

• 索引列排序

​ MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

• like语句操作

​ 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

• 不要在列上进行运算

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01’;

• 不使用NOT IN和<>操作

mysql主从复制

​ 主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

MySQL主从复制的两种情况：同步复制和异步复制，实际复制架构中大部分为异步复制。

复制过程

1、Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。

2、Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。

3、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”。

4、Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

数据库乐观锁和悲观锁

悲观锁

​ 悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

​ Java synchronized 就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。

乐观锁

​ 乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

乐观锁一般以两种方式实现：使用数据版本号。使用时间戳

数据库的隔离级别

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据。

提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。

可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读。

串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

数据库索引底层实现

​ 数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

​ 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

哪些情况下索引会失效

没有遵循最左匹配原则。

一些关键字会导致索引失效，例如 or， ！= ， not in，is null ,is not unll

like查询是以%开头

隐式转换会导致索引失效。

对索引应用内部函数，索引字段进行了运算。

数据库两种引擎

InnoDB是聚集索引，支持事务，支持行级锁；

MyISAM是非聚集索引，不支持事务，只支持表级锁。

数据库水平拆分与垂直拆分

​ 垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。 缺点是单表大数据量依然存在性能瓶颈

​ 水平拆分，上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里 。

通俗理解：水平拆分行，行数据拆分到不同表中， 垂直拆分列，表数据拆分到不同表中。

JDBC的反射及作用

​ 通过反射com.mysql.jdbc.Driver类，实例化该类的时候会执行该类内部的静态代码块，该代码块会在Java实现的DriverManager类中注册自己,DriverManager管理所有已经注册的驱动类，当调用DriverManager.geConnection方法时会遍历这些驱动类，并尝试去连接数据库，只要有一个能连接成功，就返回Connection对象，否则则报异常。

事务相关

ACID特性

• 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性指事务前后数据的完整性必须保持一致。
• 隔离性指多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。
• 持久性是指一个事务一旦提交，它对数据库中数据的改变就是永久性的，即便数据库发生故障也不应该对其有任何影响。

并发事务带来的问题

​ 多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。

**脏读（Dirty read）: **当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

**丢****失修改（Lost to modify）: **指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

**不可重复读（Unrepeatableread）: **指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

**幻读（Phantom read）: **幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别

不可重复读的重点是修改，幻读的重点在于新增或者删除。

​ 例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

​ 例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

事务隔离级别

**READ-UNCOMMITTED(读取未提交)：**最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

**READ-COMMITTED(读取已提交)：**允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

**REPEATABLE-READ(可重复读)：**对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

**SERIALIZABLE(可串行化)：**最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。