mysql相关

1：innodb

Innodb和myisam区别

完整支持ACID事务,实现了事务的四种隔离级别，默认是REPEATABLE，同时使用next-key-lock避免泛读产生；

支持外建：主键保证了数据的唯一性，外键保证了数据的完整性。当一个外键列没有显示的加上索引，则innodb引擎会自动对其加一个索引，避免全局扫描，加表锁。

行锁设计。

提供一致性非锁定读：通过mvcc方式读取当前执行时间数据库中行的数据。当读取的行正在执行update或者delete操作的时候，这时读取操作不会因此等待行锁的释放，相反会去读取一个快照数据，不需要等待访问行上X锁的释放。快照数据是指该行之前版本的数据，是通过undo段实现。对于read committed的事务隔离级别读取的是最新版本数据，对于repeatable read读取的总是事务开始时候的数据。

不支持全文索引（新版本支持）

myisam的叶子结点的data域存储的是数据记录的地址，而innodb的data域保存的是完整的数据记录（聚集索引）或者是相应记录的主键值（非聚集索引）【具体见第三小点】

MyISAM 适合 SELECT 密集型的表，而 InnoDB 适合 INSERT，update密集型。MyISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MyISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

Innodb的四大特性：

插入缓冲：针对的是非聚集的辅助索引（即索引不是唯一的）。当对非聚集索引进行插入或者更新的时候，先判断在缓冲池中是否存在插入的非聚集索引页，若存在直接插入，不存在的话则先放到insert buffer。然后以一定的频率将insert buffer和辅助索引页子节点进行合并。将多个插入操作合并到一个操作中，从而提高性能。

自适应哈希：根据访问频率和策略为热点数据自动生成哈希索引。

二次写 ：在对缓冲中的賍页进行刷新时候，并不是直接写到磁盘，而是先写入内存中的doublewrite buffer，然后由doublewrite buffer分两次写到物理磁盘并同步磁盘。这样相当于是给賍页刷新之前创建一个副本。即使在刷新的过程中发生宕机，也可以通过副本进行賍页的恢复，然后再进行重做。保证了数据页的可靠性。

预读（局部性原理）

Innodb存储引擎的总体架构：

该存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因为cpu和磁盘速度的高度不匹配问题，因此需要在内存中开辟一个缓冲区进行调和，对数据的读取需要先将磁盘上读到的页存放到缓冲区，然后下一次读取相同页的时候直接从缓冲区读取，不用读取磁盘，进而减少读取磁盘的时间和开销，当数据发生修改后，需要先在缓冲区修改，然后以一定的频率，通过checkpoint技术将数据刷回到磁盘上。做这些动作的是master thread，其主要作用就是保持缓冲区的数据是最新的，同时将修改刷新到磁盘，还要负责当数据库发生宕机的时候能够进行恢复。Master thread的内部主要是通过四个循环实现上述的功能，分别是主循环（每秒循环，每10秒循环），后台循环，刷新循环，暂停循环。

缓冲区主要包含缓冲池，重做日志缓冲池，额外的内存缓冲池。缓冲池里面含有数据页，索引页，自适应哈希索引等。具体来说用LRU list，FREE list，FLUSH list三个链表对其中的数据页进行管理。当从磁盘调入页的时候，先判断Free List是否还有多余的空闲页，若有直接调入，若没有则采用lru算法从LRU list中进行页面淘汰，若被选中淘汰的页被修改过则需要刷新到磁盘上。LRU list用来管理页的可用性，采用的是lru算法，但是稍微做了改进，就是新页并不是直接插入到表头，默认插入到链表的5/8处（可以调节参数innodb_old-blocks-pct进行设置，minpoint=3/8.minpoint之前的数据称为热点数据）。这样做是为了避免热点数据全部被换到磁盘（扫描操作插入的页数众多，导致前面的热点数据全部被淘汰置换到磁盘上），从而下次访问的时候缓冲不命中，失效。FLUSH list记录的是被修改的页，也叫做賍页，需要通过checkpoint技术将其刷新到磁盘，是为了保持一致性，一般不需要设置太大，因为默认一秒一次的频率将重做日志缓冲的文件刷新到磁盘（事务提交的时候和重做日志缓冲空间小于1/2时也会被刷新到磁盘）。重做日志缓冲是为了实现持久性，当事务提交的时候先做重做日志，再修改页，当发生宕机导致数据丢失的时候可以通过重做日志进行恢复。

---

2：B+树，B树，红黑树的选择

B树是严格的二叉查找树，且每一个节点的左右子树高度不超过1的平衡树。平衡树是通过旋转来维持平衡，而旋转是非常耗时的操作，因此平衡树适用于查找较多，修改较少的场景。红黑树是一种多路查找树，但在每个节点增加一个存储位表示节点的颜色，可以是red或black。通过对任何一条从根到叶子的路径上各个节点着色的方式的限制，红黑树确保没有一条路径会比其它路径长出两倍。它是一种弱平衡树(由于是弱平衡，可以推出，相同的节点情况下，AVL树的高度低于红黑树)，相对于要求严格的AVL树来说，它的旋转次数变少，所以对于搜索、插入、删除操作多的情况下，我们就用红黑树。

B+树是为磁盘及其他存储辅助设备而设计一种平衡多路查找树，从根节点到每一个叶子结点的高度差值不超过1，而且同层级的节点有指针相互连接，是有序的。B+树从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动。

哈希索引和B+树索引的区别：

1.哈希索引是无序的，B+树索引是有序的

2.在等值查询的时候，哈希索引具有绝对的优势（前提是不能包含大量的重复键），

3.B+树索引适用于范围查询和排序

4.INNODB默认采用B+树索引，但是当认为建立哈希索引可以提供查询效率的时候，会自动建立哈希索引

为什么说B+树比B树更适合数据库索引？

1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3：B+树的叶子节点有指向相邻叶子节点内的指针，从而极大提高了区间访问性能。因为叶子结点的键值从左到右是非递减的顺序排列。

为什么说B+树比红黑树更适合数据库索引？

1：更少的查找次数：一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。B+树的的时间复杂度为O（LogdN）,其中d为每一个结点的出度，红黑树的出度一般是2，B+树的出度一般非常大，索引查找的次数更少。

2：利用磁盘的预读性质：为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读（基于空间局部性原理）。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先

与红黑树等平衡树相比，跳跃表具有以下优点：

跳跃表的基本性质：

跳跃表是一个多层的结构；每一层都是一个有序链表；最底层包含所有的元素；一个元素出现在第i层的链表，必然也会出现在i层之下的所有链表里；每一个元素都有向下和向右的两个指针。

插入速度非常快速，因为不需要进行旋转等操作来维护平衡性；

更容易实现。

---

3：索引

Mysql的所有列类型都可以添加索引，引入索引的目的是为了加快查询速度，索引其实也是一种特殊的数据结构文件。但是引入索引也是有开销的。首先创建和维护索引需要时间，其次索引也要占据一定的存储空间，最后对数据库的增，删，改操作，对应的索引也需要动态的维护，因为这些操作会破坏平衡树的平衡性，在操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。因此索引并不是越多越好，要分具体的情况，一般来说有三个原则。

1：对查询较多的建立索引，对修改较多的避免创建过多索引

2：具有低选择性的字段不推荐建立索引

3：数据量本身就很小的时候不建立索引。因为建立索引，查询索引的时间可能比遍历整个数据库都耗时。

为啥是最左匹配？

 mysql创建复合索引的规则是首先对复合索引最左边的（这里是name）字段的数据进行排序，在此基础上，再对后面的字段（这里是cid）进行排序，类似于order by name cid这样 * 第一个字段（name）是绝对有序的，后面的字段就是无序的了，一般情况下第二个cid字段进行条件判断是用不到索引的，可能出现type是index类型的，这就是mysql最左前缀的原因。

为啥用自增列作为作为主键？

如果我们定义了主键，则innodb会选择主键作为聚集索引，如果没有显示定义，则会选择第一个不包含null值的唯一索引作为主键索引，若是没有这样的唯一索引，则会选择内置的6字节长的ROWID作为隐含的聚集索引

数据记录本身存放在聚集索引的叶子结点上，并且是按照主键的顺序进行排放。因此插入一条新纪录的时候，会根据主键值将其插入到适当的节点位置，若是页面达到装载因子（默认15/16），则会开辟新页。

使用自增主键的时候，记录就会顺序的添加到当前索引节点的后续位置，若是非自增，每次插入的主键值都近似随机值，则mysql不得不为将该记录插入到合适的位置上而去移动其他记录，这种频繁的移动，甚至移动导致的分页操作会导致大量的碎片产生。

Uuid的长度太长，导致每一个索引项较大，因此同一个页面存放的索引就少。因此要查询某一索引可能需要多查询多个页，即要进行多次IO操作。

MYISAN引擎的索引

 

INNODB引擎的索引

聚集索引：数据页上存放的是完整的每行记录数据，而在非数据页的索引页上存放的仅仅是自身的键值和指向数据页的偏移量。

辅助索引（非聚集索引）：索引页存放的是自身键值和指向数据页的偏移量，但此时的数据页存储的不是数据，而是一个书签（书签就是相应行数据的聚集索引键）。

辅助索引的存在不会影响数据在聚集索引中的组织，因此每张表可以有多个辅助索引，但聚集索引是按照每张表的主键构造的，因此只能存在一个。在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。即非聚集索引需要进行回表操作。但是若采用的是覆盖索引，则也可以像聚集索引那样直接取得数据。

覆盖索引： 就是select的数据列只用从索引中就能够取得。一个索引包含（覆盖）所有需要查询（selcct,where ,group by,order by后面跟随的字段）的字段的值，就是这个索引可以覆盖所有查询的列。这时候从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。同时因为辅助索引不包含整行的记录信息，所以其大小要远小于聚集索引，可以减少大量的IO操作。

联合索引

Alter table t add index_id_date (id,date):为表t建立列id和date组成的联合索引

Select * from t where id =1会使用联合索引index_id_date

Select* from t where id=1 and date=2019会使用联合索引index_id_date

Select * from t where date=2019不会使用联合索引。

但是不管是id键，还是date键都是已经排好序。因此在查询某个用户的情况并按照时间的顺序进行排序的3条记录，这时使用index_id_date联合索引会避免多一次的排序操作，因为date已经排好序。直接找到对应id的用户记录并按要求从前到后依次取出3条即可。

索引创建

1:Create index index_name on table_name(col_name)//创建索引

2:alter table table_name add index index_name(col_name)//修改表结构

3:create table table_name( id int not null,index index_name(col_name))//创建表时指定

索引删除

Drop index index_name on table_name

alter table table_name drop index index_name

索引失效：

1：条件中用or，即使其中有条件带索引，也不会使用索引查询。使用or，又想索引生效，只能将or条件中的每个列都加上索引。

2：对于多列索引，不是使用的第一部分，则不会使用索引。

3：like的模糊查询以%开头，索引失效

4：如果列类型是字符串，一定要在条件中将数据用引号引用起来，否则不会使用索引

5：如果MySQL预计使用全表扫描要比使用索引快，则不使用索引

索引信息查询：

Show index from table_name.查出的信息有几个字段着重解释

Non_unique:非唯一的索引，唯一索引的时候是0，普通索引是1

Seq_in_index:索引在该列的位置，在联合索引中比较直观

Cardinality：优化器会根据该字段的值判断是否使用这个索引，但该值不是实时更新，是通过采样的方法进行预估，因为实际操作中对索引的更新操作非常频繁。以此来减少系统开销，可以通过analyze table命令来刷新cardinality的值。Cardinality标识的是索引中不重复记录数量的估计值。Cardinality/表的数据行数=1时，说明该索引具有高选择性，即更能唯一标识一行数据。因此在访问具有高区分度的属性字段并从中取出少量数据的时候，应该为该字段添加索引。当Cardinality/表的数据行数=较小值时，此时该索引具有低区分度，即使为该属性建立索引，在查询的时候也不一定使用。

索引长度：（InnoDB情况）

单列的索引长度限制767B，当大于时，也会创建成功，不过取得是前767B的前缀索引

组合索引的限制长度是3072B，大于的时候会创建失败。一个varchar类型对应3B，因此对类型为varchar的列建立索引，最大的字符是255.

---

4：mysql的复制（异步）

步骤一：主库db的更新事件(update、insert、delete)被写到binlog

步骤二：从库发起连接，连接到主库

步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库

步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog并写入到relay log.

步骤五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db.

---

5：事务

其目的就是将数据库从一个一致性状态转为另外一个一致性状态。默认情况下每一个sql语句都当做一个事务。

 原子性：事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用回滚日志（undo）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

一致性：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。

 隔离性：一个事务所做的修改在最终提交以前，对其它事务是不可见的。

 持久性：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。使用重做日志(redo)来保证持久性。

隔离级别：定义的是事务在数据库读写方面的控制范围。Mysql默认的是repeatable read.

脏读：在不同的事务下，当前事务读取到另外事务中未提交的数据。违反了数据库的隔离性。

不可重复读的重点是修改:在一个事务内多次读取同一数据集合，在这个事务还没有结束时，另外一个事务也访问了该数据集合 ，并做了DML操作，因此会导致在第一个事务中的两次读数据之间，由于第二个事务的修改，而导致第一个事务的两次读取数据不一致。因此产生一个事物内的多次读取数据不一致情况。违背了数据库的一致性。 

幻读的重点在于新增或者删除 (数据条数变化)。同样的条件, 第1次和第2次读出来的记录数不一样

传播行为：定义的是事务的控制范围。

Required:当前有事务，则加入当前事务执行。当前没有事务，则开创新的事务执行。

Requirs-new:：不论当前是否有事务，都开创新的事务执行。

Supports:当前有事务，则加入当前事务执行，当前没有事务，则以非事务执行

Not-supporeted:当前有事务，则先挂起。以非事务运行结束后重新开始当前事务

Mandatory:必须在事务状态下运行，否则异常抛错

Never:必须在非事务状态下运行，否则异常抛错

Nested:当前若存在事务，则嵌套事务执行，若不存在则新建事务

嵌套事务的理解：

嵌套是子事务套在父事务中执行，子事务是父事务的一部分，在进入子事务之前，父事务建立一个回滚点，叫save point，然后执行子事务，这个子事务的执行也算是父事务的一部分，然后子事务执行结束，父事务继续执行。

如果子事务回滚，会发生什么？

父事务会回滚到进入子事务前建立的save point，然后尝试其他的事务或者其他的业务逻辑，父事务之前的操作不会受到影响，更不会自动回滚。

如果父事务回滚，会发生什么？

父事务回滚，子事务也会跟着回滚！为什么呢，因为父事务结束之前，子事务是不会提交的，我们说子事务是父事务的一部分，正是这个道理。那么：

事务的提交，是什么情况？

是父事务先提交，然后子事务提交，还是子事务先提交，父事务再提交？答案是第二种情况，还是那句话，子事务是父事务的一部分，由父事务统一提交。

---

6：mysql调优

1）数据库层面上：

•除了主键索引，唯一索引之外，对于常用的查询字段也要加索引。查询的时候尽量使用主键索引。当然索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，需要酌情考虑。

•索引列不能是表达式的一部分，也不能是函数的参数，需要作为独立列

•让选择性强的索引放在前面 

•优化查询语句

尽量采用确认性查询语句，减少 or，in，not in，%xxx%语法的使用。

只返回必要的列，最好不使用select * 语句

只返回必要的行，使用limit来限制返回的数据

当通配符（%，_）出现在词首的时候应该减少通配符（like）的使用。因为这个时候的搜索不会利用索引。

2）应用层面上：

•采用缓存机制，将常用的数据进行缓存，增加访问速度；

•分库分表，读写分离，将数据分开读写，提升性能

---

7：复杂sql的手写

explain+SQL语句的显示分析：

select_type：查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

1、simple ——简单的select查询，查询中不包含子查询或者UNION

2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记

3、subquery——在select或where列表中包含了子查询

4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中

5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived

6、union result:UNION 的结果

Type:显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序

1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。

2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量

3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描

4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体

5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描

6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。

7、all：遍历全表以找到匹配的行

注意:一般保证查询至少达到range级别，最好能达到ref。

Key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠

常见的sql语句操作：

Select * from AAA limit 0,10;查询从第1行（头）开始的共计10条数据

distinct：这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是 distinct只能返回它的目标字段，而无法返回其它字段.

笛卡尔积：去掉内连接的where条件，就是第一个表的每一行与第二个表的每一行匹配。返回的结果个数是第一个表中的行数*第二个表中的行数

内连接也称作等值联结：inner join on将第一个表的每一行与第二个表的每一行匹配，on作为过滤条件，筛选出符合匹配条件的行

左外连接 left join on：左边表的所有行，右边表符合条件的行。当左边有的，右边没有，用null填充

右外连接：right join on:右边表的所有行，左边符合条件的行。当右边有的，左边没有的用null填充。

Where和having的区别：其功能都是为了对数据进行过滤，但where是基于行过滤，having是基于分组；where在数据分组前进行过滤，having是在数据分组后进行过滤。一般出现having的时候，要出现group by。

删除命令的区别

drop直接删掉表。会将表和索引占用的空间全部释放掉。

truncate删除表中数据，再插入时自增长id又从1开始。将表和索引空间恢复到初始大小

delete删除表中数据，可以加where字句。不会释放表和索引占用的空间。

从一个表复制到另一个表：

1:表不存在，在运行时候自动创建一个和旧表相同结构的新表，并复制旧表数据到新表

CREATE TABLE aCopy AS SELECT * FROM a

2:表结构相同的表，且在同一数据库（如，table1,table2)
insert into table1 select * from table2 (完全复制)
insert into table1 select distinct * from table2(不复制重复纪录）
insert into table1 select top 5 * from table2 (前五条纪录)
3:不在同一数据库中（如，db1 table1,db2 table2)
insert into db1..table1 select * from db2..table2 (完全复制)
insert into db1..table1 select distinct * from db2table2(不复制重复纪录）
insert into tdb1..able1 select top 5 * from db2table2 (前五条纪录)

---

8：海量数据处理问题

1、局部淘汰法。用一个容器保存前10000个数，然后将剩余的所有数字一一与容器内的最小数字相比，如果所有后续的元素都比容器内的10000个数还小，那么容器内这个10000个数就是最大10000个数。如果某一后续元素比容器内最小数字大，则删掉容器内最小元素，并将该元素插入容器，最后遍历完这1亿个数，得到的结果容器中保存的数即为最终结果了。

这个容器可以用（小顶堆）最小堆来实现。我们知道完全二叉树有几个非常重要的特性，就是假如该二叉树中总共有N个节点，那么该二叉树的深度就是log2N，对于小顶堆来说移动根元素到 底部或者移动底部元素到根部只需要log2N，相比N来说时间复杂度优化太多了（1亿的logN值是26-27的一个浮点数）。基本的思路就是先从文件中取出1000个元素构建一个小顶堆数组k，然后依次对剩下的100亿-1000个数字进行遍历m，如果m大于小顶堆的根元素，即k[0]，那么用m取代k[0]，对新的数组进行重新构建组成一个新的小顶堆。这个算法的时间复杂度是O((100亿-1000)log(1000))，即O((N-M)logM)，空间复杂度是M

这个算法优点是性能尚可，空间复杂度低，IO读取比较频繁，对系统压力大。

2、分治法，即大数据里最常用的MapReduce。

a、将100亿个数据分为1000个大分区，每个区1000万个数据

b、每个大分区再细分成100个小分区。总共就有1000*100=10万个分区

c、计算每个小分区上最大的1000个数。

为什么要找出每个分区上最大的1000个数？举个例子说明，全校高一有100个班，我想找出全校前10名的同学，很傻的办法就是，把高一100个班的同学成绩都取出来，作比较，这个比较数据量太大了。应该很容易想到，班里的第11名，不可能是全校的前10名。也就是说，不是班里的前10名，就不可能是全校的前10名。因此，只需要把每个班里的前10取出来，作比较就行了，这样比较的数据量就大大地减少了。我们要找的是100亿中的最大1000个数，所以每个分区中的第1001个数一定不可能是所有数据中的前1000个。

d、合并每个大分区细分出来的小分区。每个大分区有100个小分区，我们已经找出了每个小分区的前1000个数。将这100个分区的1000*100个数合并，找出每个大分区的前1000个数。

e、合并大分区。我们有1000个大分区，上一步已找出每个大分区的前1000个数。我们将这1000*1000个数合并，找出前1000.这1000个数就是所有数据中最大的1000个数。

（a、b、c为map阶段，d、e为reduce阶段）

具体案例：有一个1G大小的一个文件，里面每一行是一个词，词的大小不超过16字节，内存限制大小是1M。返回频数最高的100个词

对于这问题，主要考虑的是划分(每个文件要小于1M)，这里可以考虑划分为2028份。

对于第二步可以考虑用堆排序 ，即常用的TopN手段。

第三部就是更为常见的归并排序

3、Hash法。如果这1亿个数里面有很多重复的数，先通过Hash法，把这1亿个数字去重复，这样如果重复率很高的话，会减少很大的内存用量，从而缩小运算空间，然后通过分治法或最小堆法查找最大的10000个数。

---

9：数据库的锁

锁的对象是事务，用来锁定的是数据库的对象，包括表，页，行等。并且一般都只在事务提交或者回滚的时候释放锁。

锁的粒度：MySQL 中提供了两种封锁粒度：行级锁以及表级锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。

但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

锁的类型：行级锁和表级锁

Innodb实现两种标准的行级锁：

共享锁（S  Lock）:允许事务读一行数据

排它锁（X  Lock）:允许事务删除或者更新一行数据

意向锁是表锁，用来表示一个事务想要在表中的某个数据行上加X锁或S锁

意向共享锁（IS Lock）：事务想要获得一张表中的某几行的共享锁

意向排它锁（IX Lock）：事务想要获得一张表中的某几行的排它锁

只有在增删改查时匹配的条件字段带有索引时，innodb才会使用行级锁，在增删改查时匹配的条件字段不带有索引时，innodb使用的将是表级锁。因为当你匹配条件字段不带有所引时，数据库会全表查询，所以这需要将整张表加锁,才能保证查询匹配的正确性。

一致性非锁定读：

Innodb存储引擎通过MVCC方式（见14）去读取执行时间数据库中行的数据，若该行正在执行update或者delete操作的时候，不用等待排它锁X的释放，而是去读该行的一个快照数据（即该行之前版本的数据，通过undo实现），从而提高了数据库访问的并发性。仅在READ COMMITTED和REPEATABLE READ的事务隔离级别下会采用这种读取方式。READ COMMITTED对于快照数据读取的总是被锁定行的最新一份版本数据，REPEATABLE READ对于快照数据读取的总是被锁定行事务开始时的版本数据。

锁的算法实现：

Record lock:仅仅是对单个行记录加锁

Gap lock:锁定一个范围，但是不包含记录本身。

Next-key lock:锁定一个范围，并且包含记录本身（可以避免幻读的产生）

---

10：mysql 的分页查询

Limit子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)，当只有一个参数的时候表示的是返回的数目，换句话说，LIMIT n 等价于 LIMIT 0,n。当为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1。

1.limit方法的局限性

limit是最常用的分页方法,它在执行过程中，相当于先遍历了前100个，然后取了第100到500个，舍弃了前100个.但是limit仅适用于小数据范围内的分页查询 小数据

select * from articles limit 170500,171000，查询19s

2.通过主键id过滤的方法

这种方法的好处是首先通过id索引快速定位到了分页数据的起始位置(避免了全文检索)，并且用limit 方法快速获取了从起始位置往后的500个数据。

select * from articles where id >(select id from articles limit 170500,1) limit 500 > 查询 2.5s

3.between and

最优方法between and 前一种方法相当于是先利用id索引的优势快速定位到了分页的起始位置，然后往后截取了500条数据.between and的方法相当于也是利于id索引的优势,但是同时定位了分页的起始和终止的位置,截取了中间的信息,因此速度会更快.

select * from articles where id between (select id from articles limit 170000,1) and (select id from articles limit 170500,1) > 查询 2s

---

11：char和varchar，的关系

char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节。使用 utf8mb4的话插入m个中文,会占用m*4个字节。

char定长，即不管实际value都会占用n个字符的空间，不足n个字符的时候用空格填充，而varchar变长，即只会占用实际字符应该占用的字节空间。

超过n的字符两者方式都会将n后面的截断

char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。
varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。Char的上限是255字节（不是字符），varchar的上限是65535字节

因为char长度固定，char的存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar则刚好相反，以时间换空间。

---

12：mysql中日志的分类及作用

慢查询日志：错误日志可以得到数据库优化信息，慢查询日志则可以定位到存在问题的sql语句，从而进行sql语句层面上的优化。先在mysql启动的时候设置一个阈值（long_query_time的默认值为10），将所有运行时间超过该阈值的sql语句记录下来，从而确定是否需要优化。

Undo log：回滚日志：主要是为了实现事务的一致性。 是逻辑格式的日志，在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，根据每行进行记录。事务开始之前，将当前时刻的版本生成 undo log，undo也会产生redo来保证undo log的可靠性， 当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否有其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间）

Redo log：重做日志，是事务层面：主要是为了实现事务的持久性。是物理格式的日志，记录的是物理数据页面的修改的信息，将重做日志刷新到磁盘上需要采用两次写的策略。首先重做日志会被首先写入到内存的重做日志缓冲区，然后重做日志缓冲区按照采用的同步策略将其刷新到磁盘上（主循环的每秒一次，事务的提交，重做日志缓冲不足1/2时都会触发同步磁盘操作）所以即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存同步到磁盘。重做日志随着事务的开始就产生。当重做日志全部写到磁盘后，重做日志空间就可以进行重用。

Binlog：二进制日志，是数据库层面：主要是为了实现主从数据库的一致性（复制），是逻辑格式的日志，主要记录的是所有更改数据库操作的SQL语句和对应sql语句反向的信息，包括innodb，myisam等其他存储引擎的日志，而innodb存储引擎的重做日志只记录有关该存储引擎的事务日志。 事务提交的时候，一次性将事务中的SQL语句（一个事物可能对应多个 SQL 语句）按照一定的格式记录到binlog中。 这里与redo log很明显的差异就是 redo log 并不一定是在事务提交的时候刷新到磁盘，redo log 是在事务开始之后就开始逐步写入磁盘。 因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了 bin_log 的情况下，对于较大事务的提交，可能会变得比较慢一些。这是因为 binlog 是在事务提交的时候一次性写入造成的，这些可以通过测试验证。

中继日志：用在主从复制里。

---

13：封锁协议

一级封锁协议：事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

二级封锁协议：在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。

三级封锁协议：在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

---

14：innodb解决幻读

 MVCC 允许数据具有多个版本，innodb 会为每一行添加两个字段，分别表示该行创建的版本和删除的版本

select：满足以下两个条件 innodb 会返回该行数据：

该行的创建版本号<=当前版本号，用于保证在select操作之前所有的操作已执行落地。

该行的删除版本号>当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。

insert：将新插入的行的创建版本号设置为当前系统的版本号。

delete：将要删除的行的删除版本号设置为当前系统的版本号。

update：不执行原地 update，而是转换成 insert + delete。将旧行的删除版本号设置为当前版本号，并将新行 insert 同时设置创建版本号为当前版本号。

MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。当查询的列是聚集索引列（存在多个索引和唯一索引由多个列组成都不符合）时，会降级为Record lock。当查询的是辅助索引列的时候，聚集索引加上record-lock，辅助索引加上next-key lock，同时对辅助索引的下一个键值加上gap lock。

---

15：备份

热备：数据库运行中直接备份，对正在运行的数据库操作没有任何影响

冷备：数据库停止的情况下备份，实现最为简单，只需要复制相关的数据库物理文件

温备：数据库运行中进行备份，但是会对当前数据库产生影响，比如加一个全局读锁以保证备份数据的一致性。

复制可以实现备份，但不仅仅是备份的功能。

---

16：三大范式

1NF:数据库表中的每一列都是不可分割的原子项

解决办法：将可分割的列进行拆分（家庭信息和学校信息）

2NF:在1NF的基础上，数据库表中每一列对主键都是完全依赖，而不能部分依赖主（主要针对的是联合主键）

解决办法是：将存在部分依赖的列单独建立新表（主键是订单号和产品号，但是订单金额和订单时间只依赖联合主键的的订单号，因此需要拆分建立新表）

3NF:在2NF的基础上，保证每一列对主键都是直接依赖，而不能是间接的传递依赖

解决办法：将具有传递依赖的列单独建立新表（主键是学号，每一列都完全依赖主键，符合2NF原则，但是班主任的性别和年龄可以由班主任姓名决定。因此存在学号-班主任姓名-班主任年龄的传递依赖）

---

17：存储过程

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

调用：

1）可以用一个命令对象来调用存储过程。

2）可以供外部程序调用，比如：java程序。

优点：

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，可减少数据库开发人员的工作量。