数据库知识点

1、数据库中SQL语句有哪几种，分别是做什么的

DDL：数据库定义语言，是用来定义对象(数据库，表，字段)

DML：数据库操作语言，是用来对数据进行增删改

DQL：数据库查询语言，是用来对数据进行查询

DCL：数据库控制语言，是用来创建数据库用户和控制数据库访问权限

2、 mysql 的内连接、左连接、右连接有什么区别？

内连接：

    inner jion：在连接查询时，会返回两张表中满足完全匹配关系的结果集

左连接：

    left jion:在连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

右连接：

    right jion:在连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

3、约束

        约束             描述                                                                                          关键字
    非空约束        限制字段的数据不能为null                                                       NOT NULL
    唯一约束        保证字段上的数据是唯一不重复                                                UNIQUE
    主键约束        主键是一行数据的唯一标识只能是非空唯一                          PRIMARY KEY
    默认约束        保存数据时，如果未指定该字段的值，则用默认值                   DEFAULT
    检查约束        保证字段值满足某一条件                                                            CHECK
    外键约束        用来让两张表之间建立连接，保证数据一致和完整性            FOREIGN KEY

 4、事务

什么是事务：

        事务是一组数据操作的集合，它是一个不可分割的工作但愿，事务会把所有操作作为一个整体一起向系统提交或者回滚操作请求，要么同时成功，要么同时失败

比如：两个人张三和李四，每一个账号都有2000，一共是有4000，当张三向李四转1000，操作完成后，要保证两个人的账号总金额还要是4000

事务的四大特性：

     原子性：事务是不可分割的最小单元，要么成功，要么失败

     一致性：事务完成时，必须使得数据保存一致状态

     持久性：事务一旦回滚或者提交，它对数据库的数据改变是永久的

     隔离性：数据库操作系统提供的隔离机制，保证事务不受外部并发操作影响的独立环境下运行

事务隔离：

   脏读：读未提交

           开启俩个事务，一个事务进行增加一条数据，但是未提交，而另一个事务可以查到这条数据

  不可重复读：读已提交，解决了之前的脏读，但是出现不可重复读问题

           开启两个事务，一个事务先查询一条数据，另一个事务在查询后对这条数据进行修改并提交后，第一个事务再去查询时和第一次的数据不一致

    可重复读： 解决了不可重复读，但是出现幻读

            开启两个事务，一个事务先进行查询时这条数据并不存在，而第二个事务在第一条查询后对这条数据进行插入，并提交，而第一个事务在去插入的时候发现这个条数据已经存在了，但是又去查询时发现并没有这条数据

     串行化：  它解决以上的所有问题

            但是它的性能非常低

#幻读在不提升隔离级别怎么解决：

可以通过加锁(实际问题：假设一个事务在查询某个范围内的订单数量，然后另一个事务插入了一个新的订单，导致第一个事务再次查询时，订单数量增加了)

        加行锁，查询操作时对相关数据行加锁，可以阻止其他事务对这些数据行进行插入或删除操作

        加临键锁，是一种结合了行锁和间隙锁的锁机制，它同时对行数据和其所在的范围进行加锁，以确保数据在事务处理期间不被其他事务修改或删除。临键锁主要应用于InnoDB存储引擎的事务处理中。

#临键锁优势：

1. 减少死锁：由于临键锁同时锁定记录本身和记录之间的间隙，可以避免因其他事务在间隙中插入记录而引发的死锁情况。
2. 提高并发性能：通过锁定间隙，临键锁可以减少其他事务对目标记录的并发访问，从而提高数据库的并发性能。
3. 防止幻读：临键锁可以防止其他事务在锁定记录的间隙中插入新的记录，从而避免幻读的问题。

5、存在引擎： 

InnoDB、MyISAM、Memory:

   InnoDB存储引擎：

       数据库5.5之后默认的引擎

       DML操作遵循ACID模型，支持事务

      支持行级锁，提高并发访问性能

      支持外键约束，保证数据的完整性和正确性

  MyISAM存储引擎:

       是MySQL早期默认的存储引擎

       不支持事务，不支持外键

       只支持表锁，不支持行锁

       访问速度快

  Memory存储引擎:

       表数据存储在内存中，由于受到硬件问题或断电问题影响，只能将这些表作为临时表或缓存使用

区别：

       InnoDB支持事务， MyISAM不支持

       InnoDB支持行锁和表锁，MyISAM只支持表锁，不支持行锁

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

       Memory不支持事务

6、索引

什么叫索引：

      索引是帮助MySQL高效获取数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

索引结构：

     二叉树:

        每个节点不超过2的有序树，是每个节点最多有两个子树的树节点。

为什么不使用二叉树:

        当数据量大时，树的高度会比较高，查询会比较慢

Hash索引：

     对于索引key进行一次hash计算就可以定位出数据存储位置

为什么不是hash索引：

        B+树可以进行范围查询，Hash索引不支持

        B+树支持联合索引的最左侧原则，Hash索引不支持(最左原则:在联合查询时，查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)

        B+树支持order by排序，Hash索引不支持

        B+树支持like进行模糊查询，like后面(%结尾)的话可以起到优化作用，Hash索引不支持

B树：

       无论是叶子节点还是非叶子节点，都会保存数据，叶节点的指针可以为空，所有索引元素不重复，节点中的数据索引从左到右递增排列

B+树：

        非叶子节点不存储data，只存储索引（冗余），可有存放更多的节点，叶子节点包含了所有索引字段，所有的数据都存放在叶子节点上，叶子节点使用指针访问，提升区间访问性能，从左至右递增

为什么不是B树：

        对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
         B+Tree范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯
         B+Tree磁盘读写能力更强（叶子节点不保存真实数据，因此一个磁盘块能保存的关键字更多，因此每次加载的关键字越多）
        B+Tree扫表和扫库能力更强（B-Tree树需要扫描整颗树，B+Tree树只需要扫描叶子节点）

索引有哪几种类型？

        主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
        唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
        普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
        全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
        覆盖索引：查询列要被所建的索引覆盖，不必读取数据行(查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖)
        组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

7、索引什么时候会失效 

     查询条件包含or，会导致索引失效。
    隐式类型转换，会导致索引失效，例如age字段类型是int，我们where age = “1”，这样就会触发隐式类型转换。
    like通配符会导致索引失效。注意："ABC%“会走range索引，”%ABC"索引才会失效。
    联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
    对索引字段进行函数运算。
    对索引列运算（如，+、-、*、/），索引失效。
    索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。
    索引字段上使用is null， is not null，可能导致索引失效。
    相join的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算
    mysql估计使用全表扫描要比使用索引快,则不使用索引。        

索引不适合哪些场景：

        数据量少的不适合加索引

        更新比较频繁的也不适合加索引

        离散性低的字段不适合加索引（如性别）

如何检查是否有索引或者是否用了索引：

        可以使用if exists或者是explain

        if exists没有语句没有索引会报错可以在创建索引语句搭配 if not exists 去判断是否有索引

        explain可以配合查询语句，看看索引是否生效

 8、锁

全局锁：

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态

        应用场景：对数据库中的数据进行备份

        加锁：flush table with read lock; 

        数据备份：mysql dump -uroot -p1234 bady >bady.sql

        释放锁：unlock tales;

表级锁：

 表级锁：表锁分为共享读锁和表独占写锁(当在update的时候条件不是没有索引的时候会锁整张表)

        读锁：lock tables tb_user read  unlock tables 所有人只能读，不能写

        写锁：lock tables tb_user write unlock tables  自己能读能写，其他人不能读、不能写

        注意：自己指的是某一个人开启事务，然后在该事务中加了锁

元数据锁：当对一张表进行增删改查的时候，加MDL读锁（共享），当对表结构进行变更操作的时候，加MDL写锁(排他锁)

        注意：元数据锁，数据库自动帮我们加，不需要我们写加锁的sql语句；元数据的共享锁和排他锁是不能同时存在的

意向锁：在执行DML操作时，会涉及的行加行锁(写锁/排他锁)，同时也会对该表加上意向锁(意向排他锁)，此时，其他人对该表加表锁时，直接判断表是否有意向锁，有则不能加表锁，没有则可以加表锁(作用：不需要一行一行的去判断每条数据是否加了行锁，提高数据库是否能加表锁的效率)

意向共享锁:需要手动写sql加锁，其他人可以加表的读锁，不能加表的写锁
意向排他锁:不需要手动写sql加锁，只要执行了DML语句，会自动加行锁，加了行锁就会自动加意向排他锁，此时则不能加表的读锁和写锁。

行级锁：每次操作锁住对应的行数据

行锁：锁定单个行记录的锁 update ...where id=2给这条数据加了排他锁

共享锁：select语句可以加共享锁，手动加锁

排他锁：DML，增删改自动加排他锁，不能加共享锁，select语句可以加排他锁；一个事务修改了数据库中id为1的数据(自动加排他锁)，事务还未提交，其他事务不能对这条数据进行增删改操作。

间隙锁：锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert。一个事务修改了id为5的这条记录中的数据(id为5的数据在数据库中不存在)，则会把id为5的前后两个值之间的间隙值加锁
           select * from tb_user where age = 18 lock in share mode  
           给age范围为18-20之间的值加锁并且给18加临键锁

临键锁：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙    

9、 数据库自增长主键会遇到什么问题

使用自增长主键，在做分库分表的时候主键可能会重复等问题。

自增主键会产生表锁，从而引发问题 自增主键可能用完问题

解决方法：简单点可以使用雪花算法去生成主键。

什么时候用主键自增还是用雪花算法

        如果业务量小，推荐采用自增ID，

        如果业务量大，推荐采用雪花算法生成的随机ID。

原因：

        假如我们使用主键自增和雪花算法去加入100万条数据的时候

        当在次去追加30万条数据时

可以明显的看出：主键自增>雪花算法

但是使用主键自增的时候：

1. 别人一旦爬取你的数据库，就可以根据数据库的自增id获取到你的业务增长信息，很容易进行数据窃取。

2. 其次，对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争。

         如果业务量小，推荐采用自增ID，

        如果业务量大，推荐采用雪花算法生成的随机ID。

雪花算法和主键自增区别：

雪花ID：
    优点:
        1.分布式环境下唯一性：
                 雪花ID在分布式系统中生成唯一的ID，可以满足分布式环境下的需求。
    缺点：
       1.依赖于机器时钟：
                雪花ID的生成依赖于机器的时钟，如果时钟回拨或者时钟不同步，可能会导致生成的ID不唯一。
        2.存储空间较大
              雪花ID占用的存储空间较大，通常为64位，如果作为主键，会占用更多的存储空间。
       3.查询效率低
              由于雪花ID是随机生成的，不具有顺序性，导致索引效率较低。

MYSQL自增:
    优点：
        1.简单易用
​                 MySQL自增ID的生成由数据库自动完成，无需额外的代码逻辑。
        2.唯一性
                自增ID保证了每条记录都有一个唯一的标识符。
        3.效率高
                自增ID是按顺序递增的，可以提高插入和查询的效率。
        4 .索引效率高
                自增ID可以作为主键或索引列，提高查询效率。

     缺点：
        1.不适用于分布式系统
                在分布式环境下，多个节点生成的自增ID可能会冲突，需要额外的处理机制。
        2.不适用于需要保密的场景
                 自增ID的递增规律可能暴露系统的使用情况，不适用于需要保密的业务场景。
         3.查询效率低
                由于雪花ID是随机生成的，不具有顺序性，导致索引效率较低。综上所述，虽然UUID和雪花ID在某些场景下具有唯一性和分布式支持的优点，但由于存储空间大、索引效率低等缺点，以及不适用于分布式和保密场景，不推荐将它们作为主键。相比之下，MySQL自增ID具有简单易用、唯一性、效率高和索引效率高等优点，适用于大多数场景，因此推荐使用自增ID作为主键。

10、mysql中in 和exists（yi个z死t）的区别，not exists和not in

exists:把外表作为loop循环，每次loop循环在对内表进行查询，只返回true或者false，不会返回UNKNOWN，返回的结果没有限制
in:通过hash进行连接外表和内表(内查询)，返回结果必须只有一个字段，当遇到NULL情况会返回UNKNOWN

怎么用：

        如果查询的两个表大小相等，用in和exists差别不大，如果两个表中一个较小，一个较大，子查询表大的则用exists，子查询表小的则用in

not in和not exists怎么区别：

not in那么内外表都进行全表扫描，没有用到索引；而not exists的子查询依然可以用到表上的索引，所以无论哪个表大，not exists都比not in快

11、聚集索引与非聚集索引的区别 

• 一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。
• 聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
• 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
• 聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

何时使用聚集索引与非聚集索引

描述	聚集索引	非聚集索引
主键列	可以	可以
外键列	可以	可以
频繁修改	不可以	可以
频繁更新	不可以	可以
返回某数据范围值	可以	不可以
列经常被分组排序	可以	可以

13、在高并发情况下，如何做到安全的修改同一行数据

要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案

使用悲观锁
悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外~ 比如，可以使用select…for update

select * from tb_user where name=‘**’ for update
以上这条sql语句会锁定了User表中所有符合检索条件（name=‘**’）的记录。本次事务提交之前，别的线程都无法修改这些记录。

使用乐观锁
乐观锁思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

14、数据库悲观锁和乐观锁的区别 

悲观锁：

        悲观锁她专一且缺乏安全感了，她的心只属于当前事务，每时每刻都担心着它心爱的数据可能被别的事务修改，所以一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行。

乐观锁：

        反之，总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。

15、select for update有什么含义，会锁表还是锁行还是其他？ 

select for查询语句是不会加锁，但select for update除了有查询的作用外会加锁，而且还是悲观锁，至于是是什么锁，这要看是不是主键/索引，如果没有则是表锁，但是有索引/主键则是行锁

16、 什么是数据库连接池?为什么需要数据库连接池呢?

数据库连接池原理：

        在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法；

数据库连接池好处：

• 方法的复用
• 更快的系统响应速度
• 新的资源分配手段，统一连接管理，避免数据库连接泄露

附加应用程序和数据库建立连接的过程

• 通过TCP协议的三次握手和数据库服务器建立连接(数据库服务器---->服务端

客户端向服务端发送连接请求

服务端向客服端发送一个响应，通知客户端收到连接请求

客服端在次向服务端发送连接确认请求，确认连接

)

• 发送数据库用户账号密码，等待数据库验证用户身份
• 完成身份验证后，系统可以提交SQL语句到数据库执行
• 把连接关闭，TCP四次挥手告别。(数据库服务器---->服务端

客服端向服务端发送取消连接请求

服务端向客户端返回一个请求，表示收到取消请求

服务端向客服端发送确认取消连接信息

客服端再次向服务端发送确认取消请求信息

)

17、是用datetime还是timestamp 

数据类型：

• timestamp本质是int型，datetime是字符串，timestamp无论是存储空间还是查询效率都比datetime好

存储空间要求：

• datetime数据类型需要8个字节的存储空间,timestamp数据类型需要4个字节的存储空间。若是对存储空间有严格要求用timestamp是一个较好的选择

存储方式：

• datetime以固定的时区存储，不受时区设置的影响。 timestamp以UTC时间（协调世界时）存储，并根据时区设置进行转换，若是程序需要在不同时区之间工作，那么timestamp是一个较好的选择

数据范围：

• datetime数据类型的有效范围是从1000年到9999年，精度为秒。 timestamp数据类型的有效范围是从1970年到2038年，精度为秒。那么就需要根据你所需要的时间范围去选择

默认值和自动更新：

• datetime可以设置默认值和自动更新值，但不会记录变更的时间，datetime类型适合用来记录数据的原始的创建时间。而 timestamp可以设置默认值和自动更新值，并会记录变更的时间，timestamp类型适合用来记录数据的最后修改时间。

总结：

        如果你存储的时间超出timestamp 范围或者你不想时区影响到存储的时间值可以使用datetime；如果你需要考虑到不同时区问题或者节省存储空间可以使用timestamp