MySQL阶段总结

1、查询

数据库连接查询有哪些？
  • 显性内连接：INNER JOIN ,展示的是两表之间的交集
  • 隐性内连接：WHERE， 查询出来的结果和inner join是一样的。（优化器自动把where转成了inner join，但是多表还是建议显性）
  • 左连接：LEFT JOIN/LEFT OUTER JOIN, 以左表为主表（展示全部），右表为从表（展示符合条件）
  • 右连接：RIGHT JOIN/RIGHT OUTER JOIN, 以右表为主表（展示全部），以左表为从表（展示符合条件）
  • 全外连接：MySQL不支持，可以用UNION（连接查询结果）代替
  • 自然连接：NATURAL JOIN,默认匹配两个表列名相同的列，不重复显示
聚合函数是什么？

聚合函数会做一些简单的统计和计算，比如 AVG、COUNT、SUM、MAX等。

怎么防止SQL注入？

SQL注入就是sql参数传递时，被恶意的写入SQL语句，类似于1=1这样一定为true的条件，导致数据库信息泄露。
要防止SQL注入，一般使用预编译语句的方式，就是把被传递的参数用“?”代替，这样即使刚才那种情况也只是会被识别为字符串来进行查询。在Mapper.xml中，替代参数时使用#{}代替${}。

SQL语句执行顺序？
  • 编写顺序：select —> from —>where —> group by —> having —> order by
  • 执行顺序：如下 解释顺序：
    from：需要从哪个数据表检索数据。
    where：过滤表中数据的条件。
    group by：如何将过滤出的数据分组。
    having ：对上面已经分组的数据进行过滤的条件（对分组后的数据进行统计）。
    select：查看结果集中的那个列，或列的结算结果。
    order by：按照什么样的顺序来查看返回的数据。

2、事务

什么是事务？

一个session中所进行的所有的操作，要么都成功，要么都失败。<

事务有四大基本要素：ACID。

  • 原子性 事务的所有操作要么全部执行，要么由于出错而被整体取消。
  • 一致性 一致性是指事务必须是数据库从一个一致性状态变换到另一个一致性状态，事务执行前执行后必须处于一致性状态。
  • 隔离性 操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
  • 持久性 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
事务的隔离级别有哪些？分别有什么问题？

解释

存在问题

READ_UNCOMMITTED（读未提交）

一个事务会读取另一个事务未提交的数据。

脏读、不可重复读、幻读

READ_COMMITTED（读已提交）

事务要等另一个事务提交后才可以读取。

不可重复读、幻读

REPEATABLE_READ（可重复读）

开始读取事务时，不允许修改操作。（MySQL默认）

幻读

SERIALIZABLE（序列化）

事务顺序执行，隔离级别最高。

无

3、锁

MySQL的锁机制是什么？
  • 对于 UPDATE,DELETE,INSERT语句，InnoDB会自动给涉及数据集加 排他锁。
  • MyISAM在执行查询语句select前，会自动给涉及的所有表加读锁，在执行更新操作（update、delete、insert）前，会自动给涉及的表加写锁，这个过程不需要用户干预。

在数据库中，使用锁来管理对共享资源的并发访问，维护数据一致性。
在数据库中国有两类锁，分别是latch和lock。
latch一般称为闩锁（轻量级锁），要求锁定的时间必须非常短，若是持续的时间长，则其性能将会非常差。在InnoDB存储引擎中又分mutex（互斥量）和rwlock（读写锁）。其目的是保证并发线程操作临界资源的正确性，并且通常没有死锁检测机制。
lock的对象是事务，用来锁定的是数据库中的对象，如表，页，行。并且一般lock的对象仅在事务commit（提交）或rollback（回滚）后进行释放（不同事务隔离级别释放的时间可能不同）。lock有死锁机制，通过waits-for graph、time out 等机制进行死锁检查与处理。
二者对比：

名称

lock

latch

对象

事务

线程

保护

数据库内容

内存资源结构

持续时间

整个事务过程

临界资源

模式

行锁、表锁、意向锁

读写锁、互斥量

死锁

通过waits-for graph、time out等机制进行死锁检查与处理

无死锁检查与处理机制

存在于

Lock Manager的哈希表中

每个数据结构的对象中

死锁以及解决死锁的理解?

事务A与事务B由于某种调度顺序，可能会相互等待对方释放资源的锁，进而造成死锁忙等待。在数据库中，解决死锁采用两种方式，预防死锁和解决死锁。

发生死锁的四个条件：

  • 互斥
  • 请求与保持
  • 不剥夺
  • 循环等待

预防死锁的方式：

  • 一次封锁法：任务事务必须一次同时申请所有的枷锁请求，若不能加锁成功，则全部不加锁，并处于等待状态；若是全部加锁成功，则可继续执行，在执行过程中不能对任何数据申请加锁。
  • 顺序封锁法：预先对所有数据对象规定一个顺序，任何一个事务要对几个数据对象进行封锁时，必须按照此规定的顺序进行，若有一个对象封锁未成功，只能等待之，不得先封锁后面的数据对象。

解决死锁的方式：

  • 超时法
  • 等待图法
  • 银行家算法：当一个进程申请使用资源的时候，银行家算法通过先试探分配给该进程资源，然后通过安全性算法判断分配后的系统是否处于安全状态，若不安全则试探分配作废，让该进程继续等待。
什么是悲观锁？什么是乐观锁？

悲观锁是认为自己读数据时正好别人在写自己刚读的数据，会在修改数据之前就先锁定，再进行修改。悲观锁分为共享锁（读锁）和排他锁（写锁）。
乐观锁是认为自己读的时候别人不会写自己读的数据，所以在数据提交更新的时候才会正式对数据的冲突与否进行检测。一般是用控制版本号来实现，适用于读操作多的情况。

1、传统的关系型数据库都是悲观锁，比如行锁，表锁等，读锁，写锁等，都是在操作之前先上锁
2、java里面的同步 synchronized关键字的实现。
乐观锁可能导致类似循环等待的死锁状态，解决方案是把事务中的语句执行顺序进行调整。

如何在SQL中使用悲观锁？

要使用悲观锁，必须关闭MySQL数据库的自动提交属性。因为MySQL默认使用autocommit模式，也就是说，当执行一个更新操作后，，MySQL会立刻将结果进行提交。（SQL语句：set autocommit=0）
悲观锁的使用：

--0.开始事务
begin;
--1.查询商品库存信息
select quantity from items where id=1 for update;
--2.修改商品库存为2
update items set quantity=2 where id=1;
--3.提交事务
commit;

这里在1的查询SQL语句里，for update 就是加锁的一种典型策略。

如何使用乐观锁？

乐观锁每次在执行数据修改操作时，都会带上一个版本号，一旦版本号和数据的版本号一致就可以执行修改操作并对版本号+1操作，否则执行失败。
因为每次操作的版本号都会随之增加，所以不会出现ABA问题。除了version版本号以外，还可以使用时间戳，因为时间戳天然具有顺序递增性。

4、索引

什么是数据库索引？

索引是存储引擎一种用于快速查找纪录的数据结构。

  • 优势
    提高数据检索的效率，降低数据库的IO成本
    降低数据排序的成本，降低了CPU的消耗
  • 劣势
    实际上索引也是一张表，所以索引也是要占用空间的
    虽然索引提高了查询速度，却也降低了增删改表的速度，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。每次更新添加了索引列的字段，都会调整因为更新锁带来的键值变化后的索引信息
    索引只是提高效率的一个因素，如果表数据量很大，就需要话时间研究建立最优秀的索引，或优化查询速度。
索引数据结构？

常见的MySQL索引主要有两种结构：Hash索引和B+Tree索引；现在常用的是InnoDB引擎，默认的是B+Tree，索引分为唯一索引、组合索引、主键索引、普通索引。

MySQL索引类型？
  • MyISAM索引实现
    MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据纪录的地址。
  • InnoDB索引实现
    虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
    第一个区别是InnoDB的数据文件本身就是索引文件，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据纪录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据纪录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
    第二个区别于MyISAM索引的不同是InnoDB的辅助索引data域存储相应纪录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都是引用主键所谓data域。
B+Tree索引和Hash索引区别？

哈希索引适合等值查询，但无法进行范围查询；
哈希索引没办法利用索引完成排序；
哈希索引不支持多列联合索引的最左匹配原则；
如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

主键与唯一性索引的区别
  • 主键一定是唯一性索引，唯一性索引不一定是主键
  • 一个表中只能有一个主键，可以有多个唯一性索引
  • 主键不允许空值，而唯一性索引列允许空值
  • 主键可以被其他字段作外键引用，而索引不能作为外键引用
索引什么时候创建？
  • 列中数据值分布范围很广
  • 列中包含大量空值
  • 列经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
建立索引时你会考虑什么？
  • 选择唯一性索引
  • 为经常需要排序、分组和联合的字段建立索引
  • 为经常作为查询条件的字段建立索引
  • 尽量使用数据量少的索引
  • 尽量使用最左前缀匹配原则，非常重要的原则
  • 尽量选择区分度高的列作为索引（男女）
  • 索引列不能参与计算，保持列“干净”

5、设计

数据库中，主键使用的类型以及取值范围？ 当数据库数据超出最大范围的时候，怎么处理？

类型

字节长度

范围

int

4

无符号[0,232 -1]，有符号[-231,231-1]

BigInt

8

无符号[0,264-1]，有符号[-263 ,263 -1]

VarChar

(M)

变长字符串，要求M ≤ 255

数据库主键一般采用UUID和auto_increment（数据库自增主键）；两种方式。

  • auto_increment的优点
    字段长度较UUID小很多，可以是bigint甚至int类型。这对检索的性能会有所影响，表格检索数据的时候，哪怕走索引，也是先根据索引找到主键，然后有主键找到这条纪录。所以主键的长度短的话，读的性能会更好。
    在写的方面，因为是自增的，所以主键是趋势自增的，也就是说新增的数据永远在后面，这点对于性能有很大的提升
  • auto_increment的缺点
    最致命的一个缺点就是很容易被别人知道业务量，然后很容易被网络爬虫爬取
    高并发的情况下，竞争自增锁会降低数据库的吞吐能力
    数据迁移的时候，特别是发生表格合并这种操作的时候，ID相同冲突，非常麻烦
  • UUID的优点
    每个UUID都是唯一的，绝对不会冲突
    可以在应用层生成，减少数据库负担，提高数据库吞吐能力
    是string类型，写代码的时候方便
  • UUID的缺点
    与自增相比，最大的缺陷就是随机IO,因为InnoDB这个默认引擎，表中数据是按照主键顺序存放的，也就是说发生了随机IO，那么就会频繁地移动磁盘块，当数据量大的时候，写的短板将很明显，当然这个缺点可以通过nosql的一些产品解决
    读取出来的数据也是没有规律的，通常需要order by ，其实也消耗数据库资源
    看起来比较丑
    UUID雪花算法
    开发中可以使用IDworker工具类来进行雪花UUID的生成
数据库读写分离的理解

读写分离的目的是为了提高数据库服务的性能，从而支持更大规模的并发访问。
读写分离采用数据冗余的方式，每台从机保存了完整的业务数据。结构上采用一主多从的结构，主机负责处理写操作，从机负责处理读操作。数据同步由主机执行
读写分离常用代理方式实现，代理服务器接收应用程序传来的读写请求，然后决定转发到哪个具体的服务器进行处理。读写分离能提高性能的原因在于：

  • 主从服务器负责各自的读和写，极大程度上缓解了锁的争用。
  • 从服务器可以使用MyISAM，从而提供更高性能的查询并节约开销。
  • 增加冗余的同时，也提高可用性。
读写分离使用的场景
  • 并发访问量大，单机已经无法满足并发请求
  • 读操作远远多于写操作
  • 对数据的实时性的要求不严苛
读写分离存在的瓶颈以及相应的解决方案

采用读写分离时，最大的问题就是存在主从复制延迟。数据写入主服务器后，由于主服务器数据同步到从服务器存在延迟，导致读取不到最新的数据。
解决方案：…

6、优化

一条SQL语句执行的很慢的原因

偶尔很慢的情况

  • 数据在刷新脏页：数据库插入、更新一条数据是，并不会马上持久化到磁盘中，而是将纪录写入redolog中，等到空闲的时候，再通过redolog里的日志将最新的数据同步到磁盘中。当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为脏页。内存数据写入到磁盘后，内存和磁盘上的数据页内容就一致了，称为干净页。如果redolog写满了，就必须暂停执行其他操作，将所有数据同步到磁盘后再继续执行，导致sql语句执行地很慢。
  • 无法拿到锁
    一直很慢的情况
  • 没有使用索引：待搜索字段没有索引，执行全部扫描；或字段有索引，但是没有用到索引，例如不满足最左前缀原则；对字段使用函数，导致无法使用索引。
  • 辅助索引导致的聚簇索引二次搜索
常用SQL优化？

1.查询语句中不用 *
2.使用#{}，而不是使用${}
3.使用关联查询（left join等）而不是使用子查询（查询嵌套查询）
4.能使用数字类型字段就不用字符串
5.使用varchar而不是char
…

触发器

触发器，是一类有数据库操作事件（插入，删除，修改）驱动的特殊过程，一旦有某个用户定义，任何用户对该触发器指定的数据进行增删改操作时，系统将自动激活响应的触发动作，在数据库服务器上进行集中的完整性控制。触发器的定义包括两部分内容：

  • 指明触发器的触发事件
  • 指明触发器的触发动作
    触发器的类型：

触发方式

For each statement

For each row

before选项

语句前触发器，在执行触发语句前激活触发器一次

行前触发器，在执行触发语句所影响的每一行前，激活触发器一次

after选项

语句前触发器，在执行触发语句后激活触发器一次

行前触发器，在执行触发语句所影响的每一行后，激活触发器一次

优点：

  • 安全性：可以基于数据库的值使用户具体操作数据库的某种权利。可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据；可以基于数据库中的数据限制用户的操作，例如不允许股票价格升幅一次超过10%
  • 审计：可以跟踪用户对数据库的操作。审计用户操作数据库的语句；把用户对数据库的更新写入审计表。
  • 实现复杂的数据完整性规则：实现非标准的数据完整性检查和约束，触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象，例如触发器可回退任何企图吃进超过自己保证金的期货。提供可变的缺省值。
  • 同步实时复制表中的数据
    缺点：
  • 消耗资源
  • 延长响应时间

7、存储引擎

MyISAM

它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，不支持外键，如果执行大量的select，insert MyISAM比较适合。

InnoDb

支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和每秒查询率（QPS)较高的情况。

MyISAM和InnoDb的区别

事务支持

MyISAM不支持事务。

InnoDB支持事务。

表锁差异

MyISAM：只支持表级锁。

用户在操作MyISAM表时，增删改查语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。也可以通过lock table命令来锁表，这样操作主要是可以模仿事务，但是消耗非常大，一般只在实验演示中使用。

InnoDb：支持事务和行级锁。

行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只有支持WHERE的主键是有效的情况，非主键的WHERE都会锁全表。

数据库文件差异

MyISAM：属于堆表。

InnoDB：属于索引组织表。

关于主键

MyISAM：允许没有任何索引和主键的表存在，myisam的索引都是保存行的地址。

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，InnoDB的数据是主索引的一部分，附加索引保存的是主索引的值。

CURD

MyISAM：如果执行大量的查询操作，MyISAM是更好的选择。
InnoDB：如果执行大量的增加修改，应该使用InnoDB表。

删除从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在InnoDB上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

外键

MyISAM：不支持。

InnoDB：支持。

索引

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

InnoDB是聚集索引，数据文件是和（主键）索引绑在一起的。

应用场景
  • MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
  • InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

总结：
MyISAM ：不支持事务、也不支持外键，使用的是表锁。适用于经常读的表。
InnoDB ：支持事务，有索引，使用的是行级锁（并发效率高）。支持外键、自增，适合经常更新的表。