Mysql-事务、引擎、索引、视图基本知识整理

事务

1.事务四大特性

原子性，要么执行，要么不执行
隔离性，所有操作全部执行完以前其它会话不能看到过程
一致性，事务前后，数据总额一致
持久性，一旦事务提交，对数据的改变就是永久的

2.并行处理问题及数据库隔离级别
脏读：事务B读取事务A还没有提交的数据
不可重复读：一次事务两次查询读的数据不一致，可能有别的操作更新了数据
幻读:两次读之间有别的事务增删，在一个事务的两次查询中数据笔数不一致，

对应隔离级别：
1.READ UNCOMMITTED： 读未提交，不处理。
事务还没提交，而别的事务可以看到他其中修改的数据的结果，也就是脏读。
2.READ COMMITTED：读已提交，只读提交的数据，无脏读；
大多数默认隔离级别是READ COMMITTED，这种隔离级别就是一个事务的开始，只能看到已经完成的事务的结果，正在执行的，是无法被其他事务看到的。这种级别会出现读取旧数据的现象（不可重复读）。
3.REPEATABLE READ：可重复读，加行锁，两次读之间不会有修改，无脏读无重复读；
4.SERIALIZABLE: 串行化，加表锁，全部串行，无所有问题。
最高的隔离级别，它通过强制事务串行执行（注意是串行），避免了前面的幻读情况，由于他大量加上锁，导致大量的请求超时，因此性能会比较低下，在需要数据一致性且并发量不需要那么大的时候才可能考虑这个隔离级别。

3.事务的使用
1.start transcation;//事务开始
delete from user;//执行操作
2.savepoint dele;//保留回滚位置
**//一系列操作
3.rollback dele;//回滚
4.commit;//提交

引擎

1.MYSQL的两种存储引擎区别（事务、锁级别等等），各自的适用场景
关系数据库表是用于存储和组织信息的数据结构。

（1）MYISAM ：不支持外键，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描，不支持事务
（2）INNODB ：支持外键，行锁，查表总行数时，全表扫描，增删改快
（3）MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

选择密集还是更新密集，MyISAM读操作性能更好，InnoDb写操作更好
并发度如何，InnoDB支持事务，并发下表现更好
是否需要外键支持
如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

2.MySQL中InnoDB引擎的行锁是通过加在什么上完成
InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将完成表锁,并发将无从谈起
3.MyIASM和Innodb两种引擎所使用的索引的数据结构是什么？
都是B+树
MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

索引

1、普通索引
1.1.1、创建表的时候同时创建索引
create table healerjean (
id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT ‘主键’,
name VARCHAR(32) NOT NULL COMMENT ‘姓名’,
email VARCHAR(64) NOT NULL COMMENT ‘邮箱’,
message text DEFAULT NULL COMMENT ‘个人信息’,
INDEX index_name (name) COMMENT ‘索引name’
) COMMENT = ‘索引测试表’;

1.1.2、在存在的表上创建索引
CREATE INDEX可用于对表增加普通索引或UNIQUE索引
create index index_name on healerjean(name)
//create只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

或者alter语句
//普通索引
alter table table_name add index index_name (column_list) ;
//唯一索引
alter table table_name add unique (column_list) ;
//主键索引
alter table table_name add primary key (column_list) ;

1.1.3、注意：
　如果是CHAR，VARCHAR类型，length可以小于字段实际长度
对于创建索引时如果是blob 和 text 类型，必须指定length。
create index ix_extra on in1(message(200));
alter table employee add index emp_name (name);
1.2、删除索引
三种形式：
drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;
1.3、查看索引
show index from healerjean;

1.4 组合索引与前缀索引
在这里要指出，组合索引和前缀索引是对建立索引技巧的一种称呼，并不是索引的类型。建立一个demo表如下

create table USER_DEMO
(
   ID                   int not null auto_increment comment '主键',
   LOGIN_NAME           varchar(100) not null comment '登录名',
   PASSWORD             varchar(100) not null comment '密码',
   CITY                 varchar(30) not null comment '城市',
   AGE                  int not null comment '年龄',
   SEX                  int not null comment '性别(0:女 1：男)',
   primary key (ID)
);

可以考虑建立组合索引，即将LOGIN_NAME,CITY,AGE三个字段建到一个索引里：

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE); 

建表时，LOGIN_NAME长度为100，这里用16，是因为一般情况下名字的长度不会超过16，加快索引查询速度，减少索引文件的大小，提高INSERT，UPDATE的更新速度。（这就是前缀索引）
　　
建立这样的组合索引，就相当于分别建立如下三种组合索引：
LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME
　　为什么没有CITY,AGE等这样的组合索引呢？这是因为mysql 组合索引“最左前缀”的结果（只从最左边的开始组合）。
　　explain显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。
　　 使用方法,在select语句前加上Explain就可以了：

Explain select * from user where id=1;

使用索引时，有一些技巧：
1.索引不会包含有NULL的列
2.使用短索引（将字段长度控制短一点）
短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。
4.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。
5.不要在列上进行运算
6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的
7.索引要建立在经常进行select操作的字段上。
8.索引要建立在值比较唯一的字段上。
9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。
10.在where和join中出现的列需要建立索引。
11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。
12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。
13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。

4.索引B+索引和hash索引区别

（1）Hash hash索引，查询特定值效率高，不能排序,不能进行范围查询
（2）B+ 数据有序,范围查询
5.聚集索引和非聚集索引
（1）聚集索引： 数据按索引顺序存储，中子结点存储真实的物理数据
（2）非聚集索引： 存储指向真正数据行的指针
6.索引的优缺点，什么时候使用索引，什么时候不能使用索引
索引最大的好处是提高查询速度，
缺点是更新数据时效率低，因为要同时更新索引
对数据进行频繁查询进建立索引，如果要频繁更改数据不建议使用索引。

7.InnoDB索引和MyISAM索引的区别
1 存储结构（主索引／辅助索引）
InnoDB的数据文件本身就是主索引文件。而MyISAM的主索引和数据是分开的。
InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

innoDB是聚簇索引，数据挂在逐渐索引之下。

2 锁
MyISAM使用的是表锁
InnoDB使用行锁

3 事务
MyISAM没有事务支持和MVCC
InnoDB支持事务和MVCC

4 全文索引
MyISAM支持FULLTEXT类型的全文索引
InnoDB不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好

5 主键
MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址
InnoDB如果没有设定主键或非空唯一索引，就会自动生成一个6字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值

6 外键
MyISAM不支持
InnoDB支持
8.索引的底层实现（B+树，为何不采用红黑树，B树）重点
树 区别
（1）红黑树 增加，删除，红黑树会进行频繁的调整，来保证红黑树的性质，浪费时间
（2）B树/ B-树 B树，查询性能不稳定，查询结果高度不致，每个结点保存指向真实数据的指针，相比B+树每一层每屋存储的元素更多，显得更高一点。
（3）B+树 B+树相比较于另外两种树,显得更矮更宽，查询层次更浅

索引查找过程中就要产生磁盘I/O消耗,主要看IO次数，和磁盘存取原理有关。
根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，
将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入

10.Sql语句的优化

1.sql尽量使用索引,而且查询要走索引
2.对sql语句优化
子查询变成left join
limit 分布优化，先利用ID定位，再分页
or条件优化，多个or条件可以用union all对结果进行合并（union all结果可能重复）
不必要的排序
where代替having,having 检索完所有记录，才进行过滤
避免嵌套查询
对多个字段进行等值查询时，联合索引

13.索引分类，索引失效条件
索引类型 概念
普通索引 最基本的索引，没有任何限制
唯一索引 与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引 它是一种特殊的唯一索引，不允许有空值。
全文索引 针对较大的数据，生成全文索引很耗时好空间。
组合索引 为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则
失效条件
条件是or,如果还想让or条件生效，给or每个字段加个索引
like查询，以%开发
内部函数
对索引列进行计算
is null不会用，is not null 会用
14.索引是什么？有什么作用以及优缺点？
（1）是一种快速查询表中内容的机制，类似于目录
（2）运用在表中某个些字段上，但存储时，独立于表之外
索引表把数据变成是有序的，快速定位到硬盘中的数据文件

16.varchar和char的使用场景
类型 使用场景
varchar 字符长度经常变的
char 用字符长度固定的

19.分库分表，主从复制，读写分离 应付高并发场景
读写分离，读从库，写主库，然后主库会自动把数据给同步到从库上去。
数据库分表
垂直划分和水平划分
垂直切分—分模块使用字段不同的而划分不同字段结构的表，把字段分开；
水平切分—分同一个模块下的多个结构相同的子表，字段相同数据分开，例如:用户表user分为 user_1 , user_2分别存储男用户和女用户。

数据库分库
1、垂直划分
基本的思路就是按照业务模块来划分出不同的数据库，而不是像早期一样将所有的数据表都放到同一个数据库中。

例如:商场订单模块放主机1中数据库, 商品模块放主机2中数据库

2、水平划分
水平分库方式主要根据数据属性（如商品所属地市, 种类）拆分物理数据库,从而解决单库中数据量过大IO密集的问题。

分库分表存在的问题：
1）事务问题
2）还要跨库跨表的join
3）额外的数据管理负担和数据运算压力。例如，对于一个记录用户乘积的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表之前，只需要一个order by语句就可以搞定，但是在进行分表之后，将需要n个order by语句，分别查出每一个分表的前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。

读写分离和主从复制
通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。
要求所有的数据库(主库从库)的表结构和数据一致。
读写分离就是读从库写主库，在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。

mysql支持的复制类型
1） 基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。
2） 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
3） 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

20.MySQL 主从复制原理的？
主库将变更写入 binlog 日志，然后从库连接到主库之后，从库有一个 IO 线程，主库的 binlog 日志拷贝到自己本地，写入一个 relay 中继日志中。接着从库中有一个 SQL 线程会从中继日志读取 binlog，然后执行 binlog 日志中的内容，也就是在自己本地再次执行一遍 SQL，这样就可以保证自己跟主库的数据是一样的。

这里有一个非常重要的一点，就是从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行 SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。

而且这里还有另外一个问题，就是如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。

所以 MySQL 实际上在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。

这个所谓半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。

所谓并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

20.数据库三范式
级别 概念
1NF 字段单一属性，不可分
2NF 非主键属性，完全依赖于主键属性表中的字段必须完全（都）依赖于（所有）全部主键而非部分主键。
学号为1024的同学，姓名为Java3y，年龄是22岁。姓名和年龄字段都依赖着学号主键。

3NF 非主键属性无传递依赖
满足第二范式，非主键外的所有字段必须互不依赖（无传递依赖），就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖
21.数据库运行于哪种状态下可以防止数据的丢失？
在archivelog mode(归档模式)只要其归档日志文件不丢失，就可以有效地防止数据丢失。

23.mysql有关权限的表都有哪几个
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：
user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

24.SQL 约束有哪几种？
NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。
25.关系型数据库和非关系型数据库区别
关系型数据库

优点
1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；
3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
4、支持SQL，可用于复杂的查询。
5.支持事务
缺点
1、为了维护一致性所付出的巨大代价就是其读写性能比较差；
2、固定的表结构；
3、不支持高并发读写需求；
4、不支持海量数据的高效率读写

非关系型数据库

1、使用键值对存储数据；
2、分布式；
优点
无需经过sql层的解析，读写性能很高
基于键值对，数据没有耦合性，容易扩展
存储数据的格式：nosql的存储格式是key,value形式
缺点
不提供sql支持

26.数据库中join的inner join, outer join, cross join
1.以A，B两张表为例
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。
right join 同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。

2.inner join
求两个表的交集
3.cross join (笛卡尔积)
将A表的每一条记录与B表的每一条记录强行拼在一起。
例如A中有4条，B中有4条，cross join 就有16条记录
27.有哪些锁,select时怎么加排它锁
锁 概念
乐观锁 加一个版本号或者时间戳，提交前进行一次比较
悲观锁 共享锁，多个事务，只能读不能写，加 lock in share mode
排它锁 一个事务，只能写，for update
行锁 作用于数据行
表锁 作于用表
28.死锁怎么解决
找到进程号，kill 进程
29.数据库的乐观锁和悲观锁是什么？
确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
在查询完数据的时候就把事务锁起来，直到提交事务（性能低）
实现方式：使用数据库中的锁机制
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
实现方式：加一个版本号或者时间戳，提交前进行一次比较

30.最左匹配原则

最左匹配原则是针对索引的
举例来说：两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的，
这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了，
查询条件where name=‘xxx’ and age=xx 这时的话，就利用到索引了，再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗，
按照正常的原则来讲是不会利用到的，但是优化器会进行优化，把位置交换下。这个sql也能利用到索引了
31.什么是存储过程？有哪些优缺点？
存储过程就像我们编程语言中的函数一样，封装了我们的代码(PLSQL、T-SQL)
存储过程的优点：
能够将代码封装起来，保存在数据库之中让编程语言进行调用
存储过程是一个预编译的代码块，执行效率比较高
一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率
存储过程的缺点：
每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
业务逻辑放在数据库上，难以迭代

视图

1.什么是视图以及视图的使用场景有哪些
视图是一种基于数据表的一种虚表
（1）视图是一种虚表，视图建立在已有表的基础上。
（2）向视图提供数据内容的语句为 SELECT 语句,视图是向用户提供基表数据的另一种表现形式。
（3）视图没有存储真正的数据，真正的数据还是存储在基表中，程序员虽然操作的是视图，但最终视图还会转成操作基表
（4）一个基表可以有0个或多个视图
使用场景：
我们只关心一张数据表中的某些字段，而另外的一些人只关系同一张数据表的另外某些字段…
我们应该做到：他们想看到什么样的数据，我们就给他们什么样的数据
1.让他们只关注自己的数据，也保证数据表一些保密的数据不会泄露出来
2.视图可以将查询出来的数据进行封装，那么我们在使用的时候就会变得非常方便
使用视图可以让我们专注与逻辑，但不提高查询效率

2.删除语句drop、delete与truncate分别在什么场景之下使用？
对比一下他们的区别：
drop table
属于DDL，不可回滚，不可带where，表内容和结构删除，删除速度快
truncate table
属于DDL，不可回滚，不可带where，表内容删除，删除速度快
delete from
属于DML，可回滚，可带where，表结构在，表内容要看where执行的情况，删除速度慢,需要逐行删除
drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate
3.局部性原理与磁盘预读
为了提高效率，要尽量减少磁盘I/O。
磁盘往往不是严格按需读取，而是 每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
这样做的理论依据局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（页的 大小通常为4k）。
4.存储过程与触发器的区别
触发器与存储过程非常相似，触发器也是SQL语句集。
两者唯一的区别是执行方式不同。一个是调用，一个是触发激活。
触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。