数据库知识整理

Read uncommitted：读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

Read committed：读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

Repeatable read：重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。

Serializable 序列化：Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

值得一提的是：大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

2、什么是幻读？

幻读 : 是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。 同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象 发生了幻觉一样。

例如：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

3、MYSQL有哪些存储引擎，各自优缺点。

两种存储引擎的大致区别表现在：

1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

3）InnoDB支持外键，MyISAM不支持

4）从MySQL5.5.5以后，InnoDB是默认引擎

5）InnoDB不支持FULLTEXT类型的索引

6）InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表。

7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。

9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

有人说MYISAM只能用于小型应用，其实这只是一种偏见。

如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，读写分离，而不是单纯地依赖存储引擎。

现在一般都是选用InnoDB了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。

总之：

1.MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。

2.MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

4、高并发下，如何做到安全的修改同一行数据。

1）、使用悲观锁。即是当前只有一个线程执行操作，排斥外部请求的修改。

2）、FIFO（First Input First Output，先进先出）缓存队列思路。即是直接将请求放入队列中，就不会导致某些请求永远获取不到锁。有点强行将多线程变成单线程的感觉 3）、使用乐观锁（推荐）。相对于“悲观锁”采用更为宽松的加锁机制，大都是采用带版本号（Version）更新

更详细请阅读：

https://blog.csdn.net/riemann_/article/details/89980750

5、乐观锁和悲观锁是什么，INNODB的标准行级锁有哪2种，解释其含义。

乐观锁（Optimistic Concurrency Control，缩写”OCC”）：是一种并发控制的方法。乐观的认为多用户并发的事务在处理时不会彼此互相影响，各事务能够在使用锁的情况下处理各自的数据。

悲观锁（Pessimistic Concurrency Control，缩写”PCC”）：与乐观锁相对应的就是悲观锁。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟 java 中的 synchronized 很相似，所以悲观锁需要耗费较多的时间。它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

6、SQL优化的一般步骤是什么，怎么看执行计划，如何理解其中各个字段的含义。

【1】、通过 show status 命令了解各种 sql 的执行频率。mysql 客户端连接成功后，通过show [session|global] status 命令可以提供服务状态信息，也可以使用 mysqladmin extend-status 命令获取这些消息。

通常比较关心的是以下几个统计参数：

1）、Comselect：执行 select 操作的次数，一次查询只累加1。

2）、Cominsert：执行 insert 操作的次数，对于批量插入的 insert 操作，只累加一次。

3）、Comupdate：执行 update 操作的次数。 4）、Comdelete：执行 delete 操作的次数。

上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对 innodb 的，累加的算法也略有不同：

1）、Innodbrowsread : select 查询返回的行数。 2）、Innodbrowsinserted : 执行 insert 操作插入的行数。

3）、Innodbrowsupdated : 执行 update 操作更新的行数。

4）、Innodbrowsdeleted : 执行 delete 操作删除的行数。

通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 sql 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。

对于事务型的应用，通过 Comcommit 和 Comrollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

此外，以下几个参数便于用户了解数据库的基本情况：

1）、Connections ： 试图连接 mysql 服务器的次数 2）、Uptime ： 服务器工作时间 3）、Slow_queries：慢查询次数

【2】、查询执行效率较低的 sql 语句：

■ 通过慢查询日志定位那些执行效率较低的 sql 语句，用 --log-slow-queries[=filename] 选项启动时，mysqld 写一个包含所有执行时间超过 longquery_time 秒的 sql 语句的日志文件。

■ 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 mysql 在进行的线程，包括线程的状态、是否锁表等，可以实时的查看 sql 的执行情况，同时对一些锁表操作进行优化。

【3】、通过 explain 分析低效 SQL 的执行计划： 查询到效率低的 SQL 语句后，可以通过 explain 或者 desc 命令获取 MySQL 如何执行 select 语句的信息，包括在 select 语句执行过程中表如何连接和连接的顺序。

以下是 explain 语句返回参数： 1）、 id：select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。

三种情况：

①、id相同：执行顺序由上而下 ②、id不同：如果是子查询，id 序号会递增，id 越大优先级越高，越先被执行 ③、id既有相同的也有不同的，两者同时存在--->id 如果相同，可以认为是一组，由上往下执行；在所有组里id越大，优先级越高，越先执行。

2）、select_type：类型主要用于区别普通查询、联合查询、子查询等的复杂程度。

■ SIMPLE：简单的 select 查询，查询中不包含子查询或者UNION。

■ PRIMARY：查询中若包含任何复杂的自查询，最外层查询为 PRIMARY。

■ SUBQUERY：在 SELECT 或 WHERE 中包含子查询。

■ DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生）MySQL会递归执行这些子查询，把结果放进临时表。

■ UNION：若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询，则外层SELECT将被标记为DERIVED。

■ UNION RESULT：从UNION表中获取结果的SELECT。

3）、table：显示这行数据是关于那张表的。

4）、type：主要类型如下：

从最好到最差：system > const > eqref > ref > range > index > ALL，一般达到 rang 级别，最好达到 ref 级别。

5）、possiblekeys ：显示可能应用到这张表中的索引，查询字段上若存在索引则列出来，但不一定被查询实际使用。

6）、keys：实际使用的索引。如果未null，则没有使用索引。若查询中出现了覆盖索引（覆盖索引：查询的字段和创建的索引的字段和个数完全一样时），则该索引只出现 key。

7）、keylen：表示索引中使用的字节数，可通过该列查找出使用索引的长度。在不损坏精准性的情况下，长度越短越好。keylen显示的值为索引字段的最大可能长度，并非实际长度，即 key_len 是根据表定义实际计算出来的，不是通过表内检出来的。

8）、ref：显示索引的那一列被使用，如果可能的话，是一个常数。那些列或常量被用于查找索引上的值。

9）、rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录的行数。

10）、Extra：包含不适合在其他列中显示，但十分重要的信息。

7、mysql怎么解决死锁。产生死锁的四个必要条件：

①、互斥条件：一个资源每次只能被一个进程使用。

②、请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。

③、不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。

④、循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

这里提供两个解决数据库死锁的方法：

①、重启数据库。②、杀掉抢资源的进程

8、Mysql的索引原理，索引的类型有哪些，如何创建合理的索引，索引如何优化。

MySql索引的原理：

1）、通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总用同一种查找方式来锁定数据。

2）、索引是通过复杂的算法，提高数据查询性能的手段。从磁盘 io 到内存 io 的转变。

MySql索引的类型：

1）、普通索引 index：加速查找 2）、唯一索引：①、主键索引：primary key：加速查找+主键唯一约束且不为空。②、唯一索引：unique：加速查找+主键唯一约束。

3）、联合索引：①、primary key(id,name)：联合主键索引。 ②、unique(id,name)：联合唯一索引。 ③、unique(id,name）：联合普通索引。

4）、全文索引 fulltext：用于搜索很长一篇文章的时候，效果最好。

5）、空间索引 spatial：了解就好，几乎不用。

9、聚集索引和非聚集索引的区别。

“聚簇”：就是索引和记录紧密在一起。

“非聚簇索引”：索引文件和数据文件分开存放，索引文件的叶子页只保存了主键值，要定位记录还要去查找相应的数据块。

10、select for update 是什么含义，会锁表还是锁行或是其他。

select for update 语句是我们经常使用手工加锁语句。借助 for update 子句，我们可以在应用程序的层面手工实现数据加锁保护操作。属于并发行锁。

11、为什么要用Btree实现，它是怎么分裂的，什么时候分裂，为什么是平衡的。

为什么使用B+树？言简意赅，就是因为：

1）.文件很大，不可能全部存储在内存中，故要存储到磁盘上 2）.索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数（为什么使用B-/+Tree，还跟磁盘存取原理有关。） 3）.局部性原理与磁盘预读，预读的长度一般为页（page）的整倍数，（在许多操作系统中，页得大小通常为4k） 4）.数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，(由于节点中有两个数组，所以地址连续)。而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性。

Key 超过1024才分裂，因为随着数据的增多，一个结点的 key 满了，为了保持 B 树的特性，就会产生分裂，就向红黑树和 AVL树为了保持树的性质需要进行旋转一样！

12、数据库的ACID是什么。

A（atomic）：原子性，要么都提交，要么都失败，不能一部分成功，一部分失败。

C（consistent）：一致性，事务开始及结束后，数据的一致性约束没有被破坏

I（isolation）：隔离性，并发事务间相互不影响，互不干扰。

D（durabilit）：持久性，已经提交的事务对数据库所做的更新必须永久保存。即便发生崩溃，也不能被回滚或数据丢失。

13、某个表有近千万数据，CRUD比较慢，如何优化。

数据千万级别之多，占用的存储空间也比较大，可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间。

1）、作为关系型数据库，是什么原因出现了这种大表？是否可以做表拆分，减少单表字段数量，优化表结构。

2）、在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。

3）、在程序逻辑中采用手动事务控制，不要每插入一条数据就自动提交，而是定义一个计数器，进行批量手动提交，能够有效提高运行速度。

更多分析可阅读文章：

https://blog.csdn.net/riemann_/article/details/93676341

14、Mysql怎么优化全表扫描（table scan）的。

■ 避免在 where 子句中对字段进行 is null 判断。

■ 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将会导致引擎放弃使用索引而进行全表扫描。

■ 避免在 where 子句中使用 or 来连接条件。

■ in 和not in 也要慎用。

■ Like 查询（非左开头）。

■ 不要使用 NUM=@num 参数这种。

■ 不要where 子句中对字段进行表达式操作 num/2=XX。

■不要在where子句中对字段进行函数操作。

15、如何写sql能够有效的使用到复合索引。

由于复合索引=组合索引，类似多个木板拼接在一起，如果中间断了就无法用了，所以要能用到复合索引，首先开头(第一列)要用上，比如index(a,b) 这种，我们可以select table tname where a=XX 用到第一列索引 如果想用第二列 可以 and b=XX 或者and b like ‘TTT%’。

16、mysql中in 和exists 区别。

mysql 中的 in 语句是把外表和内表作 hash 连接，而 exists 语句是对外表作 loop 循环，每次 loop 循环再对内表进行查询。一直大家都认为 exists 比 in 语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

㊤、如果查询的两个表大小相当，那么用 in 和 exists 差别不大。

㊥、如果两个表中一个较小，一个是大表，则子查询表大的用 exists，子查询表小的用 in。

㊦、not in 和 not exists 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用 not exists 都比 not in 要快。

EXISTS 只返回 TRUE 或 FALSE，不会返回 UNKNOWN IN 当遇到包含NULL的情况，那么就会返回 UNKNOWN

17、数据库自增主键可能的问题。

【1】、使用自增主键对数据库做分库分表，可能出现一些诸如主键重复等的问题。

【2】、数据库导入的时候，可能会因为主键出现一些问题。

可参考文章：https://yq.aliyun.com/articles/38438

18、你做过的项目里遇到分库分表了吗，怎么做的，有用到中间件么，比如sharding jdbc等,它们的原理知道么。

参考文章：https://www.cnblogs.com/butterfly100/p/9034281.html

19、MYSQL的主从延迟怎么解决。

实际上主从同步延迟根本没有什么一招制敌的办法，因为所有的 SQL 必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入， 那么一旦有延迟产生，那么延迟加重的可能性就会越来越大。 当然我们可以做一些缓解的措施。

a）、最简单的减少 slave 同步延时的方案就是在架构上做优化，尽量让主库的 DDL 快速执行。还有就是主库是写，对数据安全性较高，比如 syncbinlog=1，innodbflushlogattrxcommit = 1 之类的设置，而 slave 则不需要这么高的数据安全，完全可以将 syncbinlog 设置为 0 或者关闭 binlog，innodbflushlog 也可以设置为 0 来提高 sql 的执行效率。另外就是使用比主库更好的硬件设备作为 slave。

b）、把一台从服务器当作备份使用， 而不提供查询， 这样他的负载就下来了， 执行 relay log 里面的 SQL 效率自然就高了。

c）、增加从服务器，这个目的还是分散读的压力， 从而降低服务器负载。