Yyh blog

数据库提升

事务

【数据库事务ACID四大特性】

  1. 原子性（Atomicity） 　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

  2. 一致性（Consistency） 　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。 拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

  3. 隔离性（Isolation） 　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。 即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

  4. 持久性（Durability） 　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。 例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交， 即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

【事务的传播行为】

  1. PROPAGATION_REQUIRED：

    如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。

  2. PROPAGATION_SUPPORTS：

    支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。

  3. PROPAGATION_MANDATORY：

    支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。

  4. PROPAGATION_REQUIRES_NEW：

    创建新事务，无论当前存不存在事务，都创建新事务。

  5. PROPAGATION_NOT_SUPPORTED：

    以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。

  6. PROPAGATION_NEVER：

    以非事务方式执行，如果当前存在事务，则抛出异常。

  7. PROPAGATION_NESTED：

    如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

【怎么理解原子性】

原子性是指一个事物的操作是不可分割的，要么都发生，要么都不发生。

举例：

张三到银行给李四转账1000元，张三卡里原来有2000元，李四卡里原来也有两千元，那么转账的步骤应该如下：

张三扣除1000，剩余1000 –》银行收到张三的扣款–》李四增加1000元，变成3000元

如果张三的钱扣完，银行系统瘫痪了，怎么办呢？

张三的1000块钱会被会没呢，当然不会。这时候你的钱会退回来。也就是说银行的转账业务要么成功张三（1000元）李四（3000元），要么不发生张三（2000元）李四（2000元）。

【MySQL如何实现事务中持久性】

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

为了实现事务的原子性和持久性，mysql引入了undo和redo日志（即undo log和redo log）

一、undo

undo日志记录的是修改前的值，当事务提交之后，undo不会被马上删除，而是放入待删除队列，由purge线程来判断是否删除和处理。

二、redo

redo日志记录的是修改后最新的数据和冗余的undo日志，另外，未提交的事务和回滚了的事务也会计入redo日志。mysql恢复策略是：

  1. 恢复时，先根据redo重做所有事务（包括未提交和回滚了的）
  2. 再根据undo回滚未提交的事务。

利用这两个就能够实现事务的持久性

隔离级别

【脏读/虚读(幻读)/重复读什么意思？发生在哪 ？】

  • 脏读：

    一个事务读取了另一个事务改写但还未提交的数据，如果这些数据被回滚，则读到的数据是无效的。

    比如：

    事务T1修改了一行数据，但是还没有提交，这时候事务T2读取了被事务T1修改后的数据，之后事务T1因为某种原因Rollback了，那么事务T2读取的数据就是脏的

  • 不可重复读：

    在同一事务中，多次读取同一数据返回的结果有所不同，读到了之前的数据后又读到了其他事务已经提交的更新的数据。

    比如：

    //1、在事务1中，JoonWhee读取了自己的工资为1000，但是此时事务1的操作还并没有完成 ，后面还有1次相同的读取操作。
select salary from employee where employeeName ="JoonWhee";  
//2、在事务2中，这时财务人员修改了JoonWhee的工资为2000，并提交了事务。
update employee set salary = 2000 where employeeName = "JoonWhee";    
//3、在事务1中，JoonWhee再次读取自己的工资时，工资变为了2000 。
select salary from employee where employeeName ="JoonWhee";
//在一个事务中前后两次读取的结果并不致，导致了不可重复读。

  • 幻读（虚读）：

    一个事务读取了几行记录后，另一个事务插入（insert）一些记录，再后来的查询中，第一个事务就会发现有些原来没有的记录。

    比如

    //1、事务1查询id=1的用户是否存在
select * from User where id = 1;  
//2、在事务1查询完以后，事务2插入了一条id为1的记录
insert into `User`(`id`, `name`) values (1, 'Joonwhee');  
//3、由于事务1查询到id为1的用户不存在，因此插入1条id为1的数据。
insert into ` User`(`id`, `name`) values (1, 'Chillax');  
//4、但是由于事务2已经插入主键为1的记录了，因此此时会报主键冲突，对于事务1 的业务来说是执行失败的，这里事务1 就是发生了幻读

【不可重复读和幻读的区别】

幻读

事务在插入已经检查过不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测获取到的数据如同鬼影一般。

不可重复读

同样的条件，你读取过的数据，再次读取出来发现值不一样了。

【MySQL的事务隔离级别，分别解决什么问题】

MySQL使用可重复读(Repeatable reads)作为默认的事务隔离级别

事务隔离级别 脏读 不可重复读 幻读
未提交读(Read uncommitted) 是 是 是
提交读/不可重复读(Read committed) 否 是 是
可重复读(Repeatable reads) 否 否 是
可串/序行化(Serializable) 否 否 否

  • 未提交读(Read uncommitted)

    未提交读(READ UNCOMMITTED)是最低的隔离级别。通过名字我们就可以知道，在这种事务隔离级别下，一个事务可以读到另外一个事务未提交的数据

    未提交读的数据库锁情况（实现原理）

    事务在读数据的时候并未对数据加锁。事务在修改数据的时候只对数据增加行级共享锁

    未提交读会导致脏读

  • 提交读/不可重复读(Read committed)

    提交读(READ COMMITTED)也可以翻译成读已提交，通过名字也可以分析出，在一个事务修改数据过程中，如果事务还没提交，其他事务不能读该数据。

    提交读的数据库锁情况（实现原理）

    事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；

    事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

    提交读可以解决脏读的现象，不能解决不可重复读的读现象

  • 可重复读(Repeatable reads)

    可重复读(REPEATABLE READS),由于提交读隔离级别会产生不可重复读的读现象。所以，比提交读更高一个级别的隔离级别就可以解决不可重复读的问题。这种隔离级别就叫可重复读。

    可重复读的数据库锁情况

    事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加行级共享锁，直到事务结束才释放；

    事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

    能够解决不可重复读的读现象,可以解决脏读的现象

  • 可串/序行化(Serializable)

    可序列化(Serializable)是最高的隔离级别，前面提到的所有的隔离级别都无法解决的幻读，在可序列化的隔离级别中可以解决。

    可串列化的数据库锁情况（实现原理）

    事务在读取数据时，必须先对其加表级共享锁 ，直到事务结束才释放；

    事务在更新数据时，必须先对其加表级排他锁 ，直到事务结束才释放。

    然可序列化解决了脏读、不可重复读、幻读等读现象。但是序列化事务会产生以下效果：

    ​ 1.无法读取其它事务已修改但未提交的记录。

    ​ 2.在当前事务完成之前，其它事务不能修改目前事务已读取的记录。

    ​ 3.在当前事务完成之前，其它事务所插入的新记录，其索引键值不能在当前事务的任何语句所读取的索引键范围中。

【如何用可重复读的隔离级别避免幻读】

1、在快照读读情况下，MySQL通过MVCC来避免幻读。

2、在当前读读情况下，MySQL通过next-key（行锁+GAP锁也就是间隙锁）来避免幻。

【可重复读底层实现】

事务ID是递增的。

使用MVCC（多版本并发控制）。InnoDB为每行记录添加了一个事务ID，每当修改数据时，将当事务ID写入。 在读取事务开始时，系统会给事务一个当前版本号(事务ID)，事务会读取版本号<=当前版本号的数据，这时就算另一个事务插入一个数据，并立马提交，新插入这条数据的版本号会比读取事务的版本号高，因此读取事务读的数据还是不会变。

执行引擎

【你了解那些数据库引擎，说说它们的异同优劣】

  • MyISAM：

    默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。

  • InnoDB：

    用于事务处理应用程序，具有众多特性，包括ACID事务支持。(提供行级锁)

  • BDB：

    可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

  • Memory：

    将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

  • Merge：

    允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

  • Archive：

    为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

  • Federated：

    能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

  • Cluster/NDB：

    MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

  • Other：

    其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。

【MySQL常见的执行引擎有哪些】

常见的有四种：Memory、InnoDB、MyISAM、Archive

【MySQL数据库默认存储引擎，有什么优点】

InnoDB 引擎(MySQL5.5以后默认使用)

优点如下：

  • 灾难恢复性好
  • 支持事务
  • 使用行级锁
  • 支持外键关联
  • 支持热备份
  • 对于InnoDB引擎中的表，其数据的物理组织形式是簇表（Cluster Table），主键索引和数据是在一起的，数据按主键的顺序物理分布
  • 实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取
  • 支持热备份

【InnoDB和MYISAM的区别】

  1. InnoDB支持事务，MyISAM不支持

    对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

  2. InnoDB支持外键，而MyISAM不支持

    对一个包含外键的InnoDB表转为MYISAM会失败；

  3. InnoDB是聚集索引，MyISAM是非聚集索引

    InnoDB数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

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

  4. InnoDB不保存表的具体行数，MyISAM用一个变量保存了整个表的行数

    InnoDB执行select count(*) from table时需要全表扫描。

    而MyISAM执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）

  5. Innodb不支持全文索引，而MyISAM支持全文索引

    在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

  6. MyISAM表格可以被压缩后进行查询操作

  7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

  8. InnoDB表必须有主键而MyISAM可以没有

    InnoDB用户没有指定的话会自己找或生产一个主键

  9. Innodb存储文件有frm、ibd，而MyISAM是frm、MYD、MYI

    Innodb：frm是表定义文件，ibd是数据文件

    Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

【InnoDB多版本并发控制】

MVCC：Multi-Version Concurrency Control 多版本并发控制。

什么是多版本并发控制呢 ？其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号

MVCC工作在 ：在REPEATABLE READ和READ COMMITED 两种事务下面

MVCC并不是MySql独有的，Oracle，PostgreSQL等都在使用。

MVCC并没有简单地使用行锁，而是使用“行级别锁”（row-level locking）。

MVCC的基本原理：

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

MVCC的基本特征：

  • 每行数据都存在一个版本，每次数据更新时都更新该版本。
  • 修改时Copy出当前版本随意修改，各个事务之间无干扰。
  • 保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

InnoDB存储引擎MVCC的实现策略：

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空）。这里的版本号并不是实际的时间值，而是系统版本号。每开始 个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

索引

【索引是什么概念？有什么作用？】

概念：

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

作用：

加快查询速度

【索引的存储】

计算机主存（RAM）和外部存储器（如硬盘、CD、SSD等）

【索引的类型】

  • primary

    主键索引，唯一且不能为空；一张表只能有一个主键索引

  • normal

    普通索引

  • unique

    唯一索引，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

  • Full text

    全文索引,在检索长文本的时候，效果最好

  • spatial

    空间索引,是对空间数据类型的字段建立的索引

【索引的数据结构】

目前大部分数据库系统及文件系统都采用B Tree或其变种B+Tree作为索引结构

相关概念介绍

  • B-树（B树）：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
  • B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；
  • B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；

【索引方法】

  • BTREE
  • HASH

【数据库索引结构为什么快？索引原理】

索引的本质是数据结构，就像书的目录一样；

我们知道Tree的时间复杂度为O(logN),而大多数数据库利用B+Tree作为索引数据结构，其搜索的时间复杂度可以达到O(1)

【索引失效】

  • 列与列对比

    某个表中，有两列（id和c_id）都建了单独索引，下面这种查询条件不会走索引

    select * from test where id=c_id;

    这种情况会被认为还不如走全表扫描。

  • 存在NULL值条件

    我们在设计数据库表时，应该尽力避免NULL值出现，如果非要不可避免的要出现NULL值，也要给一个DEFAULT值，数值型可以给0、-1之类的， 字符串有时候给空串有问题，就给一个空格或其他。如果索引列是可空的，是不会给其建索引的，索引值是少于表的count(*)值的，所以这种情况下，执行计划自然就去扫描全表了。

    select * from test where id is not null;

  • NOT条件

    我们知道建立索引时，给每一个索引列建立一个条目，如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists

    select * from test where id<>500;
select * from test where id in (1,2,3,4,5);
select * from test where not in (6,7,8,9,0);
select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

  • LIKE通配符

    当使用模糊搜索时，尽量采用后置的通配符，例如：name   ’%’，因为走索引时，其会从前去匹配索引列，这时候是可以找到的，如果采用前匹配，那么查索引就会很麻烦；

    比如查询所有姓张的人，就可以去搜索’张%’。相反如果你查询所有叫‘明’的人，那么只能是%明。这时候索引如何定位呢？前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走INDEX RANGE SCAN。

    所以业务设计的时候，尽量考虑到模糊搜索的问题，要更多的使用后置通配符。

    select * from test where name like 张||'%';

  • 条件上包括函数

    查询条件上尽量不要对索引列使用函数，比如下面这个SQL

    select * from test where upper(name)='SUNYANG';

    这样是不会走索引的，因为索引在建立时会和计算后可能不同，无法定位到索引。但如果查询条件不是对索引列进行计算，那么依然可以走索引。比如

    select * from test where name=upper('sunyang');
--INDEX RANGE SCAN

    这样的函数还有：to_char、to_date、to_number、trunc等

  • 复合索引前导列区分大

    当复合索引前导列区分小的时候，我们有INDEX SKIP SCAN，当前导列区分度大，且查后导列的时候，前导列的分裂会非常耗资源，执行计划想，还不如全表扫描来的快，然后就索引失效了。

    select * from test where owner='sunyang';

  • 数据类型的转换

    当查询条件存在隐式转换时，索引会失效。比如在数据库里id存的number类型，但是在查询时，却用了下面的形式：

    select * from sunyang where id='123';

  • Connect By Level

    使用connect by level时，不会走索引。

  • 谓词运算

    我们在上面说，不能对索引列进行函数运算，这也包括加减乘除的谓词运算，这也会使索引失效。建立一个sunyang表，索引为id，看这个SQL：

    select * from sunyang where id/2=:type_id;
这里很明显对索引列id进行了’/2’除二运算，这时候就会索引失效，这种情况应该改写为：
select * from sunyang where id=:type_id*2;
就可以使用索引了。

【B树和B+树的区别】

B+Tree 只会在叶子节点挂载数据

  • 1) B+树的磁盘读写代价更低

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

  • 2) B+树的查询效率更加稳定

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

B+Tree与B-Tree 的使用总结：

在内存有限的情况下，B+TREE 永远比 B-TREE好。无限内存则后者方便

【MySQL为什么用B+树不用B树？使用B+树的好处】

注意：MySQL建立索引的时候索引方法里面可选的Hash/BTREE，其中BTREE其实是B+树

  1. B+树的数据都集中在叶子节点，分支节点只负责索引，占用比较小，可以把索引完全加载至内存中。
  2. B+树的层高会小于B树，平均的Io次数会远小于B树
  3. B+树更擅长范围查询。叶子节点数据是按顺序放置的双向链表,而B树范围查询只能中序遍历。

【HASH与B+树对比？哪种场景下应用更合适？】

​ Hash这种数据结构更加适合于键值对查询（即等值查询），当键值都是唯一的时候，查询只需要1次；但是非唯一的时候，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

  1. Hash索引仅满足“=”、“IN”和“<=>”查询，不能使用范围查询

    因为hash索引比较的是经常hash运算之后的hash值，因此只能进行等值的过滤，不能基于范围的查找，因为经过hash算法处理后的hash值的大小关系，并不能保证与处理前的hash大小关系对应。

  2. Hash索引无法被用来进行数据的排序操作

    由于hash索引中存放的都是经过hash计算之后的值，而hash值的大小关系不一定与hash计算之前的值一样，所以数据库无法利用hash索引中的值进行排序操作。

  3. 对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

  4. Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

    对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

使用场景：

如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引

【各种索引存储结构的应用场景】

【MySQL的联合索引？如何创建？数据结构】

概念：

联合索引又叫复合索引，即对多个字段同时建立的索引。

联合索引(a,b,c)为例：建立这样的索引相当于建立了索引a、ab、abc三个索引

创建：

//1、创建表的时候
CREATE TABLE `test` (
	'a' VARCHAR (16) NOT NULL DEFAULT '',
	'b' VARCHAR (16) NOT NULL DEFAULT '',
	'c' INT (11) UNSIGNED NOT NULL DEFAULT 0,
	KEY `sindex` (`a`, `b`, `c`)
) ENGINE = InnoDB COMMENT = '';

//2、表创建完成以后
alert table test add INDEX `sindex` (`a`,`b`,`c`)

数据结构：

还是B+Tree，只不过联合索引的健值数量不是一个，而是多个

【联合索引怎么使用？最左匹配的原理】

使用：

单表尽可能不要超过一个联合索引，单个联合索引不超过3个字段

最左匹配原则：

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

最左匹配原则的原理：

假如创建一个（a,b)的联合索引，那么它的索引树是这样的

a值是有序的，比如1->1->2->3，而b值是无序的，比如2->1->3，所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引

【联合索引和几个单个的索引有什么区别？】

多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！

【聚簇索引和非聚簇索引的区别】

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引

非聚簇索引(二级索引)：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

【主键索引与二级索引的区别】

主键索引和非主键索引的区别是：

非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。

【查询主键/非主键索引的实现方式】

1、如果查询语句是 select * from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。

2、如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100,再到ID索引树搜索一次，这个过程也被称为回表。

【数据库回表】

概念：

  • 简单来说就是数据库根据索引找到了指定的记录所在行后，还需要根据rowid再次到数据块里取数据的操作。
  • 比如这样的执行计划，先索引扫描，再通过rowid去取索引中未能提供的数据，即为回表。
  • 回”一般就是指执行计划里显示的“TABLE ACCESS BY INDEX ROWID”。
  • 再例如，虽然只查询索引里的列，但是需要回表过滤掉其他行。

怎么避免回表：

​ 将需要的字段放在索引中去。查询的时候就能避免回表。

【主键索引存储什么数据】

主键索引的叶子节点存放的是整行数据

【建立索引要注意什么。索引的原则 】

  1. 在经常用作过滤器的字段上建立索引；

  2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；

  3. 在不同值较少的字段上不必要建立索引，如性别字段；

  4. 对于经常存取的列避免建立索引；

  5. 用于联接的列（主健/外健）上建立索引；

  6. 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；

  7. 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，

    如：含有有限数目（不是很少）唯一的列；进行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决于DBA的所设计的数据库结构。

【为什么主键用auto_increment】

auto_increment是自增量，一般是修饰int型，效率主要体现在一下几个方面:

  1. 一般该列会作为索引，在innodb中，非聚簇索引是建立在聚簇索引上的，有主键就主键是聚簇索引，否i则隐藏一个唯一的列作为聚簇索引，在非聚簇索引中不是包含行指针而是会包含主键，或没有主键时会包含唯一的那个隐藏列来一起存储，这样以对主键的长度有一定的要求，越短会对索引存储空间的消耗越少，同时索引查询也会快一点。

  2. 在数据插入时，如果是自增的，那么可以保证后面插入的数据在页内是接在前一个记录后面的，这样不会因为聚簇索引的物理顺序而引起排序，否则可能导致后面的记录比已经插入的记录跟靠前，导致页内记录向后移动

  3. 因为innodb缓存的是数据和索引，索引减小，对缓存空间的消耗也有一定贡献

【如何生成唯一主键】

1、UUID

String id = UUID.randomUUID().toString()

2、随机数

Random random = new Random();
Integer number = random.nextInt(900000) + 100000;
return System.currentTimeMillis() + String.valueOf(number);

3、数据库利用自增Id(MySQL)或者序列（ORACLE）

【UUID的缺点】

  1. UUID字符串占用的空间比较大。
  2. 索引效率很低。
  3. 生成的ID很随机，不是人能读懂的。
  4. 做不了递增，如果要排序的话，基本不太可能。

【UUID怎么做的，了解哪些UUID生成策略 】

snowflake雪花算法（在使用BeetlSQL的时候，其自动生成的算法就是雪花算法）

其大致由：首位无效符、时间戳差值，机器(进程)编码，序列号四部分组成。

特点(自增、有序、适合分布式场景)

  • 时间位：可以根据时间进行排序，有助于提高查询速度。
  • 机器id位：适用于分布式环境下对多节点的各个节点进行标识，可以具体根据节点数和部署情况设计划分机器位10位长度，如划分5位表示进程位等。
  • 序列号位：是一系列的自增id，可以支持同一节点同一毫秒生成多个ID序号，12位的计数序列号支持每个节点每毫秒产生4096个ID序号

【身份证如何有效建立索引】

可以建立唯一索引（身份证每个人都是唯一的不重复）

【索引值改变了，树的结构如何改变】

存储过程

封锁

【数据库有哪些锁】

共享锁/排他锁/更新锁

乐观锁/悲观锁

【锁协议】

在运用排他锁 和 共享锁对数据对象加锁时，还需要约定一些规则，例如何时申请 排他锁 或 共享锁、持锁时间、何时释放等。称这些规则为封锁协议（Locking Protocol）。对封锁方式规定不同的规则，就形成了各种不同的封锁协议。不同的封锁协议对应不同的隔离级别。

  • 一级封锁协议（对应read uncommited） 一级封锁协议是：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。

    一级封锁协议可防止丢失更新，并保证事务T是可恢复的。

    在一级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，所以它不能保证可重复读和不读“脏”数据。

  • 二级封锁协议（对应read commited） 二级封锁协议是：一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁（瞬间S锁）。

    二级封锁协议除防止了丢失更新，还可进一步防止读“脏”数据。

  • 三级封锁协议（对应reapetable read） 三级封锁协议是：一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。

    三级封锁协议除防止了丢失更新和不读‘脏’数据外，还进一步防止了不可重复读和覆盖更新。

  • 四级封锁协议（对应serialization） 四级封锁协议是对三级封锁协议的增强，其实现机制也最为简单，直接对 事务中 所 读取 或者 更改的数据所在的表加表锁，也就是说，其他事务不能 读写 该表中的任何数据。这样五类并发问题都得以避免！

【数据库乐观锁和悲观锁】

乐观锁

  • 版本号
  • 时间戳
  • 待更新字段
  • 所有字段

悲观锁

  • 悲观锁按使用性质划分

    排他锁：

    X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。 性质

    1、仅允许一个事务封锁此页；

    2、其他任何事务必须等到X锁被释放才能对该页进行访问；

    3、X锁一直到事务结束才能被释放。

    产生排他锁的SQL语句如下：select * from ad_plan for update;

    共享锁：

    S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。 性质：

    1、多个事务可封锁同一个共享页； 2、任何事务都不能修改该页；

    3、通常是该页被读取完毕，S锁立即被释放。

    更新锁：

    U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。

    性质 1、用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁； 2、当被读取的页要被更新时，则升级为X锁； 3、U锁一直到事务结束时才能被释放。

  • 悲观锁按作用范围划分为

    行锁/表锁

【MySQL表锁和行锁的区别】

行锁

  1. 支持的存储引擎：Innodb；
  2. InnoDB行锁是通过给索引上的索引项加锁来实现的，意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
  3. 适用场景：有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用
  4. 特点：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
  5. 分析：show status like ‘innodb_row_lock%’;分析系统上行锁的争夺情况如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

表锁

  1. 支持的存储引擎：Innodb、MYIsam

  2. 适用场景：以查询为主，只有少量按索引条件更新数据的应用

  3. 特点：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

  4. 两种模式：

    • 表共享读锁

    • 表独占写锁

  5. 对两张表显示加锁、解锁

    Lock tables orders read local, order_detail read local;

    Select sum(total) from orders;

    Select sum(subtotal) from order_detail;

    Unlock tables;

  6. MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁

【哪些场景需要加表锁】

以查询为主，只有少量按索引条件更新数据的应用，如WEB应用

【MySQL查询加行锁怎么写】

1、行锁必须创建索引，select …where 字段（必须是索引） 不然行锁就无效

2、必须要有事务，这样才是 行锁（排他锁）

3、在select 语句后面 加 上 FOR UPDATE；

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE

【插入一条数据需要加什么锁】

表锁

【间隙锁】

间隙锁（Gap Lock）是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制。

在可重复读隔离级别下，数据库是通过行锁和间隙锁共同组成的（next-key lock），来实现的。

加锁规则有以下特性：

  • 1.加锁的基本单位是（next-key lock）,他是前开后闭原则
  • 2.插叙过程中访问的对象会增加锁
  • 3.索引上的等值查询–给唯一索引加锁的时候，next-key lock升级为行锁
  • 4.索引上的等值查询–向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
  • 5.唯一索引上的范围查询会访问到不满足条件的第一个值为止

​

搜索