Mysql复习

Mysql常用引擎

• Innodb引擎：提供了对数据库的ACID事务支持，并且还提供了行级锁的约束。他的目标是处理大数据容量的数据库
• MyLASM引擎：不提供事务的支持，也不支持行级锁和外键
• MEMORY引擎： 所有的数据都在内存中，数据的处理速度很快，但是安全性不高

MylSAM与InnoDB的区别

1. 存储结构：MyISAM每张表存放在三个文件（frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件），Innodb所有的表都保存在一个数据文件当中。（也有可能是多个文件，或者是独立的表空间文件），Innodb的大小只会受限操作系统文件的大小，一般为2GB
2. 存储空间：MyISAM存储空间比较小，因为它可被压缩。而Innodb需要更多的内存和存储，它会在主内存当中建立其专用的缓冲池用于高速缓冲数据和索引
3. 可移植性：MyLSAM是以文件形式存储，所以跨平台的数据转移中会很方便，在备份和恢复的时间可单独针对某个表进行操作。而Innodb是通过拷贝数据文件，备份binlog，在数据达到几十G的时候相对麻烦
4. 文件格式：MyLSAM数据.MYD，索引.MYI,Innodb:数据和索引是集中存储的，.ibd
5. 记录存储顺序：MyLSAM按记录插入顺序保存，Innodb按照主键大小有序插入
6. MyLSAM不支持外键和事务而Innodb支持外键与事务。而MyLSAM是表级锁定，Innodb支持行级锁定、表级锁定，锁定力度小并发能力高
7. 查询方面MyLSAM更好，而更新操作Innodb更优。索引方面都是B+树索引，但是myisam是堆表，innodb是索引组织表。myisam不支持哈希索引，innodb支持哈希索引，相对的myisam支持全文索引但是innodb不支持

聚簇索引与非聚簇索引

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。
　优点：

1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

2.聚簇索引对于主键的排序查找和范围查找速度非常快
　　缺点：

1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
　　　　2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
　　　　3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

• 非聚簇索引
  在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
  　Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。
  　辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

MyISAM索引和InnoDB索引的区别

1. InnoDB是聚簇索引而MyISAM是非聚簇索引
2. InnoDB叶子节点存储行数据，所以主键索引非常高效
3. MyISAM叶子节点存储的是行地址。需要在根据地址查询一次才能获取数据
4. InnoDB非主键索引存储的是主键索引和它带索引的值。因此查询的时候做到索引覆盖会非常高效

InnoDB引擎的四大特性

• 插入缓冲（insert buffer)

• 二次写(double write)

• 自适应哈希索引(ahi)

• 预读(read ahead)

引擎的选择

一般没有特别的需求默认Innodb即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

• 优点
  可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
• 缺点
  创建索引和修改索引比较耗时，当对数据进行更新、删除、增加的时候需要动态的维护索引。会降低增删改的效率
  索引需要物理存储空间

索引使用场景

1. where

2. order by
   当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。
   但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

3. join
   对join语句匹配关系（on）涉及的字段建立索引能够提高效率

4. 索引覆盖
   如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

索引的类型

主键索引：数据列不允许重复，不允许为NULL值，一个表只能有一个主键索引
普通索引：
　普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。
唯一索引：普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
• 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
• 注意：唯一索引对null不起作用，也就是字段为null的话可以重复；
• 注意：唯一索引对" “不起作用，也就是字段为” "的话不可以重复；
  全文索引：文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。
  　　这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加：
  　　ALTER TABLE tablename ADD FULLTEXT(column1, column2)
  　　有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法：
  　　SELECT * FROM tablename
  　　WHERE MATCH(column1, column2) AGAINST(‘word1’, ‘word2’, ‘word3’)
  　　上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。
• 注意：innoDB不支持全文索引（mysql 5.5之后的版本已经支持全文索引了）

外键索引：如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
复合索引：索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。

索引的数据结构（B树，hash）

InnoDB默认采用B+树做索引，对于hash索引，底层就是hash表一般用作与单条数据的查询。其他情况下还是采用B+树索引

1. B树索引
mysql通过存储引擎取数据，基本上90%的人用的就是InnoDB了，按照实现方式分，InnoDB的索引类型目前只有两种：BTREE（B树）索引和HASH索引。B树索引是Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引）
查询方式：

主键索引区:PI(关联保存的时数据的地址)按主键查询,

普通索引区:si(关联的id的地址,然后再到达上面的地址)。所以按主键查询,速度最快

2. 哈希索引
简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。

索引的基本原理

索引用来快速寻找那些具有特定值的记录，如果没有索引，一般来说执行时遍历整张表。

1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿倒排表内容，再去取出数据地址链。从而拿到数据

创建索引的原则

1. 最左前缀匹配原则，复合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询（ > < 、betweent、like）就停止匹配。如 a = 1 and b= 2 and c > 3 and d =4 如果建立（a,b,c,d）顺序的索引，
2. 较频繁作为查询条件的字段才会去创建索引
3. 更新频繁的不适合做索引
4. 不能有效区分数据的列不适合做索引。如性别男、女、未知最多就三种
5. 尽量扩展索引，如原来有一个a索引，现在要加（a,b）那么只需要修改原来的索引就可以了
6. 定义有外键的数据列一定要建立索引
7. 对于查询少的列，重复值较多的不要建立索引
8. 对于定义为text、image和bit的数据类型的列不要建立索引。

创建索引的方式

• 第一种方式：在执行CREATE TABLE时创建索引

CREATE  TABLE  index1 (id    INT , 
name   VARCHAR(20) , 
sex    BOOLEAN , 
INDEX ( id) 
);

• 第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

• 第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

• 删除索引

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名,如果要删除主键索引先要先取消自动增长（自动增长依赖于主键索引）

alter table table_name  drop KEY index_name 

创建索引注意事项

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高

使用索引查询一定能提高查询的性能吗？为什么

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

• 索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%

数据特别多如何删除

1. 先删除索引
2. 删除无用数据
3. 删除完成之后重新创建索引。

前缀索引

语法 ： index（field(10)）使用字段的前十个字符用作索引，默认是使用字段全部内容建立的索引。
前提： 前缀的标识度高。比如密码，因为密码几乎各不相同。
我们可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）

Hash索引和B+树所有有什么区别或者说优劣呢?

• hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。
  因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。

• hash索引不支持使用索引进行排序，原理同上。

• hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。

• hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。

• hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

为什么使用B+树不适用B-树

B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。 在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

索引什么时候会失效

• 如果条件有or，即使其中有条件带索引也不会使用，如果想要使用or又想让索引生效，只能将or条件中的每个列都加上索引
• 复合索引不满足最左匹配原则
• like查询以%开头
• 如果mysql全表扫描比走索引快，则不会使用索引

数据库事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

1. 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
3. 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

什么是脏读？幻读？不可重复读？

• 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
• 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
• 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

Mysql的默认隔离级别

数据库定义了四种不同的事务隔离级别。由低到高一次是 Read uncommitted、Read committed、Repeatable read 、Serializable。这四个级别可以逐个解决脏读、不可重复度、幻读这几类问题

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

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

锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

隔离级别与锁的关系

• 在Read Uncommitted的级别下，读取数据不需要加共享锁，这样不会跟被修改的数据上的排他锁冲突
• 在Read Committed的级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁
• 在 Repeatable Read级别下，读操作需要添加共享锁，但是在事务提交之前并不释放共享所，也就是必须等待事务执行完毕之后才释放共享锁
• SERIALIZABLE是最高的隔离级别，因为该级别锁定整个范围的键，并一直有锁，直到事务结束

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁
• 行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。必须要有索引。
  特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
  特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低
• 页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
  特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

间隙锁

为了避免幻读，引入了间隙锁，Mysql解决了幻读，他锁定是记录范围，不包含记录本身，也就是不允许在范围内插入数据。唯一索引等值只会产生行锁。范围查询才会产生间隙锁。

• 记录锁（行锁）和间隙锁的组合叫做临键锁，也是为了解决幻读

共享锁和排他锁

• 共享锁：又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

• 排他锁：又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

MySQL中InnoDB引擎的行锁是怎么实现的？

答：InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

悲观锁和乐观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

• 应用场景
  从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

UNION与UNION ALL的区别

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

• 如果使用UNION ALL，不会合并重复的记录行
• 效率 UNION ALL高于 UNION

字段为什么要求定义为not null？

null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

MVCC,多版本并发控制

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。