mysql locked myisam_MySql 事务 隔离级别 知识点

1. SQL 语句

常用的Sql语句总结：

--------s--------

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log INNER JOIN Websites ON access_log.site_id=Websites.id) GROUP BY Websites.name HAVING SUM(access_log.count) > 200;

INSERT INTO table_nameVALUES (value1,value2,value3,...);

UPDATE table_nameSET column1=value1,column2=value2,...WHERE some_column=some_value;

DELETE FROM table_nameWHERE some_column=some_value;

CREATE TABLE Persons(P_Id int NOT NULL PRIMARY KEY,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))

------------------

2. mysql transaction 事务

请简洁描述Mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？

SQL标准定义的四个隔离级别为：

read uncommited：读到未提交数据,脏读

read committed：不可重复读

repeatable read：可重读 (默认)

serializable：串行事物

3. mysql lock

MyISAM支持表锁，InnoDB支持表锁和行锁，默认为行锁

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低

行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

4, mysql index

InnoDB 存储引擎中的 B+ 树索引

聚集索引 VS 非聚集索引

在上节介绍 B+ 树索引的时候，我们提到了图中的索引其实是聚集索引的实现方式。

那什么是聚集索引呢？在 MySQL 中，B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

这里我们着重介绍 InnoDB 中的聚集索引和非聚集索引：

①聚集索引(聚簇索引)：以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。

这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。

这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。

②非聚集索引(非聚簇索引)：以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

5. mysql 分布式

使用分布式主从架构, 利用主从在达到高可用的同时，也可以通过读写分离提供吞吐量。

1). master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件binary log events)

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务

2) slave将master的binary log events拷贝到它的中继日志(relay log)

slave将master的binary log拷贝到它自己的中继日志(Relay log)。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程(I/O thread)将这些事件写入中继日志

3) slave重做中继日志中的事件，将改变反映它自己的数据

SQL线程(sql thread)从中继日志读取事件，更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。

为什么需要分布式：

1).用户请求量太大：因为单服务器TPS，内存，IO都是有限的。 解决方法：分散请求到多个服务器上； 其实用户请求和执行一个sql查询是本质是一样的，都是请求一个资源，只是用户请求还会经过网关，路由，http服务器等。

2).单库太大：单个数据库处理能力有限；单库所在服务器上磁盘空间不足；单库上操作的IO瓶颈 解决方法：切分成更多更小的库。

3).单表太大：CRUD都成问题；索引膨胀，查询超时 解决方法：切分成多个数据集更小的表。

6.Statement and PreparedStatement

Statement执行不带参数的简单SQL语句,并返回它所生成结果的对象,每次执行SQL语句时,数据库都要编译该sql语句

PreparedStatement用来执行带参数的预编译的SQL语句

PreparedStatement的优点:

①效率高.

使用PreparedStatement执行SQL命令时,命令会被数据库编译和解析,并放到命令缓冲区.以后每当执行同一个PreparedStatement对象时,预编译的命令就可以重复使用

②代码可读性和可维护性好

③安全性好.

使用PreparedStatement可以防止SQL注入.

SQL注入如:

select * from user where name = ‘aa’ and password = ‘bb’ or 1=1

7. 一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15？

(1)如果表的类型是MyISAM，那么是18因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失

(2)如果表的类型是InnoDB，那么是15InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失

8.CHAR和VARCHAR

char和varchar类型声明长度表示用户想保存的最大字符数，其中char(M)定义的列的长度为固定的，M的取值可以0-255之间，当保存char值时，在它们的右边填充空格以达到指定的长度。当检索到char值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。char存储定长数据很方便，char字段上的索引效率很高。

varchar(M)定义的列的长度是可变长度字符串，在MySQL5.0以上的版本中，varchar的数据类型长度支持到了65535，因为起始位和结束位占去了3个字节，所以其整体最大长度为65532字节(varchar的最大有效长度由最大行大小和使用的字符集确定)。

除此之外，与char比，varchar值保存时只保存需要的字符数，另加一个字节来记录长度(长度超过255时需要2个字节)。

同样在char和varchar尾部加空格，检索时char类型后的被删掉，而varchar类型的保存。

Varchar往往用来保存可变长度的字符串。简单的说，我们只是给其固定了一个最大值，然后系统会根据实际存储的数据量来分配合适的存储空间

CHAR数据类型与VARCHAR数据类型不同，其采用的是固定长度的存储方式.如果在保存CHAR值的时候，如果其后面有空值，系统还会自动过滤其空格。而在进行数据比较时，系统又会将空格填充到字符串的末尾。

适用情况：

对于MyISAM表，尽量使用Char，对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间；

对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分(所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针)，所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利；

存储很短的信息，比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的现在得不偿失。

固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。

十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

9. LIKE声明中的％和_是什么意思？

％对应于0个或更多字符，_只是LIKE语句中的一个字符。

10. BLOB和TEXT类型

BLOB是一个二进制对象

TEXT是一个不区分大小写的BLOB

BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写，对TEXT值不区分大小写

11. MYSQL数据表在什么情况下容易损坏？

服务器突然断电导致数据文件损坏。强制关机，没有先关闭mysql服务等。

12. InnoDB 和 MyISAM 的区别

区别：

1). InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

2). InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

3). InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4). InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5). InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

如何选择：

1). 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；

2). 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3). 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；

4). MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

13. 分库分表

单库太大，看看是因为表多导致数据多，还是因为单表数据多。如果是表多导致数据多的话，使用垂直切分，根据业务分成不同的库。如果是因为单表数据多的话，使用水平切分。

分库分表的顺序应该是先垂直分，后水平分。

垂直拆分

– 垂直分表：

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长(比如text类型字段)的拆分到“扩展表“。 一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

– 垂直分库：

根据业务把表划分为不同的库，个人理解为相当于项目中模块的意思。然后放到不同的服务器上，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

水平拆分

– 水平分表：

针对数据量巨大的单张表(比如订单表)，按照某种规则(RANGE,HASH取模等)，切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。

– 水平分表分库：

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

– 切分规则：

1).range:从0到10000一个表，10001到20000一个表

2).HASH取模 离散化:一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。

3).地理区域:比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。

4).时间：按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

分库分表后面临的问题

事务支持：

– 分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

多库结果集合并(group by，order by)

跨库join

– 分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。 粗略的解决方法： 全局表：基础数据，所有库都拷贝一份。 字段冗余：这样有些字段就不用join去查询了。 系统层组装：分别查询出所有，然后组装起来，较复杂。

14. 数据库常见概念

(1) 数据库管理系统

数据定义，数据库的建立和维护，数据操作，存储功能，备份和维护，

(2) 范式

第一范式： 每个属性不可再分，最低，存在冗余，插入、删除、修改异常。优点只查询一次。

第二范式：消除主属性对非主属性的部分函数依赖，找出主键，主键的依赖关系，拆表。存在插入、删除异常。改善修改异常和冗余。

第三范式：消除主属性对非主属性的传递函数依赖(X->Y->Z)。改善删除、插入 、冗余、修改异常。一般设计是第三范式

BCNF: 在第三范式的基础上消除主属性对主键的部分和传递函数的依赖(XY->Z)

第四范式：取消了多值依赖

第五范式：

第一范式包含第二范式。。。

这个只能根据每一层范式的特点逐个对照，从第一层开始。

如有一张表，一个单元格里有多个值。那么它还没达到第一范式的要求。

如有一张表，每个单元格里只有单个值，但是不是所有的键都依赖于他的主键，或者叫存在部分依赖。那么他就满足第一层，而不满足第二层范式。

如有一张表，每个单元格里只有单个值，而且没有部分依赖，但是存在传递依赖，那么它就满足第一，第二层，而不满足第三层。

还有就是： 所谓达到n层范式要求：就是要已经满足n层范式要求，但是不满足(n+1)层范式要求。

(3) 数据库事务

Begin Transaction 开始

Commit 提交

RollBack 回滚

ACID: 原子性、一致性、隔离性和持续性

原子性：要么完成，要么不做。过程的角度。

一致性： 一旦完成，确保处于一致的状态。结果的角度。

隔离性：每个事务与其他事务隔离开。

持续性：一旦完成，结果都不应该被影响。无论发生什么系统错误，都能恢复，持久化到存储器中。

(4) 数据库的备份和恢复

1) 数据转储(定期备份)

静态转储： 无运行事务

动态转储： 事务和转储并发执行。使用日志文件和数据恢复。

海量转储

增量转储

2) 故障恢复方法

事务故障：日志恢复

系统故障：日志恢复

介质故障：备份恢复

(5) 并发控制和锁

事务隔离级别

1)未提交读(Read Uncommitted)：一个事务能够读取到别的事务中没有提交的更新数据。事务可以读取到未提交的数据，这也被称为脏读(dirty read)。所以这种级别很有可能读到脏数据，隔离级别最低。

2)提交读(Read Committed)：一个事务只能读取到别的事务提交的更新数据。

一般我们提交读就可以了。只能读取到已经提交的数据。即解决了脏读，但未解决不可重复读。(oracle默认的)

3)可重复读(Repeated Read)：保证同一事务中先后执行的多次查询将返回同意结果，不受其他事务的影响。这种隔离级别可能出现幻读。(mysql默认的)

4)序列化(Serializable)：不允许事务并发执行，强制事务串行执行，就是在读取的每一行数据上都加上了锁，读写相互都会阻塞。这种隔离级别最高，是最安全的，性能最低，不会出现脏读，不可重复读，幻读，丢失更新。

锁

排它锁(写锁)，共享锁(读锁)