本文详细介绍了MySQL的基础知识，包括SQL基础、进阶特性、数据库操作、索引类型及其优化、事务处理和存储引擎。重点讲解了SQL查询的优化策略，如合理使用分页、避免null值判断、不使用%前缀模糊查询等，还探讨了存储过程的使用。此外，文章提及了InnoDB和MyISAM存储引擎的区别，强调了索引在数据库性能提升中的重要作用。
1、 SQL基础
入职时间最晚的员工信息
select * from emp
order by hire_date desc
limit 1;LIMIT 子句用于限制由 SELECT 语句返回的数据数量。
- limit子句可以被用于强制select语句返回指定记录数
- limit接受一个或两个整型参数
- limit一个参数表示输出钱多少行
- Oracle分页是RowNum关键字。
- 第一个参数指定第一个从第几行开始返回，第二个参数指定返回记录行的最大数目（不管升序还是降序，都从序号最小的作为第一个输出）
- 初始记录行的偏移量是0(不是1)
- 为了与pgsql兼容，MySQL也支持语法 limit # offset #
- limit子句位于order by子句之后
- order by 默认是升序asc,desc是降序
例：*
select * from table limit 5,10;*** //检索记录行6-15
2、SQL进阶
-
设置时区： serverTimezone=GMT
-
日期格式转换：select date_format(now(),’%Y-%m-%d’);
-
编写顺序：
select→from→where→group by→having→order by -
悲观锁分为共享锁（读锁）和排它锁（写锁），属于标记锁定。
-
for update就是加锁 (悲观锁)的一种典型策略。-- 1. 查询商品库存信息 select quantity from items where id=1 for update; -
Oracle默认隔离级别，读已提交（ read committed ），存在不可重复读，幻读问题
-
MySQL默认隔离级别，可重复读( Repeated read )，存在幻读问题
-
⭐️事务的隔离级别有哪些？分别有什么问题？
常量 解释 存在问题 READ_UNCOMMITTED（读未提交） 一个事务会读取另一个事务未提交的数据。 脏读、不可重复读、幻读 READ_COMMITTED（读已提交） 事务要等另一个事务提交后才可以读取。（Oeacle默认） 不可重复读、幻读 REPEATABLE_READ（可重复读） 开始读取事务时，不允许修改操作。（MySQL默认） 幻读（插入不一致问题未解决） SERIALIZABLE（串行化） 事务顺序执行，隔离级别最高 无 -
读未提交
事务A和事务B同时对一张数据表进行操作，事务A在操作过程中事务没有被提交，但B可以看到A的操作结果。
-
读已提交
事务A要等待，事务B对数据的操作提交后才能读取，事务B的操作内容。（避免了数据不一致问题）
-
可重复读
开启读取事务时，不允许其他事务进行修改操作
-
串行化（排它锁类似）
同时开启事务A和事务B，对统一数据库的同一张表进行操作。事务A对表插入一条数据，并未
提交，然后B插入第二条数据的时候发现插入不进去（卡机了），这时事务A，执行进行提交，
然后事务B才能插入第二条数据（前提是事务A的操作不能超时）。
-
脏读(读未提交出现的后果)
一个事务可以读到另一个事务未提交的数据，
-
幻读（增加、删除产生的，数据数量不一致）
事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符
合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。
-
不可重复读（修改产生的，数据内容不一致）
事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次
读取的时候，发现数据不匹配了，就是所谓的不可重复读了。
和幻读类似，区别在于不可重复读针对的是update或delete ，幻读针对的是insert
-
死锁
三个事务同时对数据封锁后，事务之间有彼此需要的资源，每个事务只能拿到部分数据，而造
成事务之间相互等待
-
活锁
事务T1,T2,T3，想对数据R进行封锁，T1想进行执行封锁，T1释放锁后，T3进行插队对数据进行封锁，导致T2不能对数据进行加锁，这就造成了活锁
事务有四大基本要素：ACID。
- 原子性。事务的所有操作要么全部执行，要么由于出错而被整体取消。
- 一致性。致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性（数据总量不会发生改变）。
- 隔离性。操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
- 持久性。持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
-
乐观锁的实现方式
1.版本号机制
一般是说在数据表中加上一个数据库版本号version字段，在表述数据被修改的次数当数据被修改时，它的version 值会加1。
如：
当然线程A需要更新数据值时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值为当前数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功。
2.CAS 算法
CAS(compare and swap) 意思是比较并交换， CAS有3个操作数，内存值V，旧的预期值A，要修改的新值B。当且仅当预期值A和内存值V相同时，将内存值V修改为B，不相等则会循环比较直到相等,整个比较赋值操作是一个原子操作;
CAS缺点：
1.循环时间开销大：当内存地址V与预期值B不相等时会一直循环比较直到相等，
2.只能保证一个共享变量的原子操作，
3.如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那么我们就能说明它的值没有被其他线程修改过吗?很明显不是，因为在这段时间内它的值可能被改为其他值，然后又被改回A，那CAS操作就会认为它从来没被改过，这个问题就被称为 CAS 操作的“ABA” 问题
-
总的来说，MySQL这3种锁的特性可大致归纳如下：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。锁定粒度从大到小排序：表锁 > 页锁 > 行锁
总结： 在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
-
共享锁：共享锁是读锁，是在执行读取操作的时候创建的。如果事务 T 对 数据 A 添加了共享锁，那么其他事务只能再对数据 A 添加 共享锁而不能添加其他类型的锁，对于已经添加了共享锁的事务，只能执行读操作，而不能执行写操作。共享锁的使用方式需要在查询语句后面添加 LOCK IN SHARE MODE
排它锁：排它锁是写锁，如果事务 T 对 数据 A 添加排它锁，那么其他事务就不能再添加其他类型的锁，已经添加了排它锁的事务，既能执行读操作也能执行写操作，排它锁的使用方式需要在查询语句中添加 FOR UPDATE
-
意向锁
InnoDB所用的表级锁，其设计目的主要是为了在一个事务中揭示下一步将要被请求的锁的类型。
InnoDB中的两个表锁：
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是InnoDB自动加的，不需要用户干预。
-
MVCC
MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问 ，在编程语言中实现事务内存。 准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念
MVCC在MySQL引擎InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读
当前读：排它锁和共享锁，都是当前读的操作
快照读:像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
参考资源： https://www.jianshu.com/p/8845ddca3b23
3、数据库操作
- SHOW CREATE DATABASE mydb2; #查看创建数据库时的默认字符集
- desc 表名; #查看表结构，以及数据类型信息。
查询：
where和having（过滤）组多用于双重查询
in表示在枚举范围内查找
统计函数不能用在where子句中，可以用在having子句中
group by 关键字用于分组
SELECT name,gender from employee GROUP BY gender,name;
- order by排序
SELECT * from student GROUP BY age;
左右连接(内连接)中的条件限制关键字是on
复合查询的两种思路
- 子查询
- 分组查询
数据查询语言DQL（Data Query Language）：select、where、order by、group by、having 。
数据定义语言DDL（Data Definition Language）：create、alter、drop。
数据操作语言DML（Data Manipulation Language）：insert、update、delete 。
事务处理语言TPL（Transaction Process Language）：commit、rollback 。
数据控制语言DCL（Data Control Language）：grant、revoke。
指针控制语言CCL fetch
4.索引
主键索引的数据结构是B+树，叶子节点存放数据行，非叶子节点不存放数据
如果定义了主键，PK就是主键索引，如果未定义主键，第一个非空唯一索引列就是就是聚集索引，否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
聚集索引不用回表，因为相当于主键索引的作用，普通索引会回表。
非主键索引的叶子节点存放的有主键值，然后到主键索引上区寻找数据行，这个过程称为回表。
在联合索引中存在索引覆盖问题，也可以不回表就可以查询出数据。
比如：以age为条件进行查找时,要想不回表就要把age也建立index(name,age)，这样就会存在联合索引，如此便可避免回 表，进而提高查询效率；这个操作就是索引覆盖。
单列索引审计为联合索引，可以优化索引，避免回表。
索引下推，多条件间查询时，mysql5.6以后添加了索引下推，先对所有的条件进行过滤，符合所有条件以后，再进行回表；不再是之前的最左前缀，只要一个条件符合就对这个条件回表查询，索引下推可以减少回表次数，进而提升查询效率。
索引类型：四种
-
普通索引
最基本的索引，没有任何限制，是我们经常使用到的索引。
-
唯一索引（主键索引）
与普通索引类似，不同的是，唯一索引的列值必须唯一，但允许为空值。
主键索引是特殊的唯一索引，不允许有空值。
-
联合索引
将几个列作为一条索引进行检索，使用最左匹配原则。
为什么使用联合索引？
**减少开销。**建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三
个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联
合索引会大大的减少开销！
-
全文索引
全文索引（fulltext）仅可以适用于MyISAM引擎的数据表，作用于char，varchar、
text 数据类型的列。
聚簇索引
聚簇索引不是索引类型，只是一种存储方式，
性能特别高，
InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构
造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据
页。这个特性决定了索引组织表中数据也是索引的一部分；
一般建表会用一个自增主键做 聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。
我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，现在找到主键索引再通过主键索引找数据；
覆盖索引
覆盖索引也不是索引类型，只是一种存储方式，
查询的字段，都是索引所覆盖的字段
SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。
- 每张表中的索引最大不能超过13条
- 使用索引时要保证索引生效
建立索引:
- 选择唯一性索引
- 经常需要排序、分组和联合操作的字段建立索引
- 常作为查询条件的字段建立索引
- 限制索引的数目
- 尽量使用数据量少的索引
- 尽量使用前缀来索引
- 删除不再使用或者很少使用的索引
- 不参与计算的列作为索引
索引失效:
-
or语句的，前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
-
组合索引，不是使用第一列索引，索引失效。
-
like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
-
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
-
在索引列上使用 is null 或 is not null 操作。
索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
-
where中索引列有运算;
-
where中索引列使用了函数;
-
当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
5.事务传播行为
什么是传播行为？
一个事务方法被另一个事务方法调用时，这个事务方法应该怎么进行。
| 事务传播行为类型 | 说明 |
|---|---|
| propagation_required | 如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。这是最常见的选择。 |
| propagation_supports | 支持当前事务，如果当前没有事务，就以非事务方式执行。 |
| propagation_mandatory | 使用当前的事务，如果当前没有事务，就抛出异常。使用JtaTransactionManager作为事务管理器。 |
| propagation_requires_new | 新建事务，如果当前存在事务，把当前事务挂起。 |
| propagation_not_supported | 以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。使用JtaTransactionManager作为事务管理器。 |
| PROPAGATION_NEVER | 以非事务方式执行，如果当前存在事务，则抛出异常。 |
| PROPAGATION_NESTED | 如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。 |
6.索引的底层结构
B+ tree:
- 兼容了B树和B-树的特点
- 扫库，扫表，的能力强，
- 因为是二叉树，所以默认排序还会，算法的复杂度稳定
- 数据只存储在叶子结点中
- 左闭合区间，最佳左匹配原则
- 叶子结点是有序的单向链表
B- 树特点？
B-树和B+树最重要的一个区别就是，B+树只有叶节点存放数据，其余节点用来索引，
而B-树是每个索引节点都会有Data域。
就决定了B+树更适合用来存储外部数据，也就是磁盘数据。
Mysql衡量查询效率是通过磁盘IO次数。
B-树(B类树)的特点就是，每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，
当查询数据的时候，最好的情况就是很快找到目标索引，然后读取数据，
但是 B-树的每个节点都有data域(指针)，，这会增加节点大小，
说白了增加了磁盘IO次数
(磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO是很耗时的操作！)，
而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一
叶子结点的数据结构
B+树的叶子结点，以页为单位存储数据，每页大小为16KB
每一个页都包含两个页指针，一个是previous page指针，指向上一个页，一个是next page指针，指向下一个页。
头部还有Page的类型信息，和用来唯一标识Page的编号。根据这个指针分布可以想象到Page链接起来就是一个双向链表
什么是最佳左匹配？
最左匹配原则就是指在联合索引中，
如果 SQL 语句中用到了联合索引中的最左边的索引，
那么这条 SQL 语句就可以利用这个联合索引去进行匹配。
索引的底层是一颗 B+ 树，联合索引也是一颗 B+ 树，
只不过联合索引的健值数量不是一个，而是多个。
构建一颗 B+ 树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建 B+ 树。
7.sql优化
三步走：定位、 分析原因 、 解决问题
定位
慢查询：慢查询是一种日志文件，查询超过指定时长，就记录日志
认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件和数据库表。
分析原因
编写顺序：select→from→where→group by→having→order by
SQL语句的解析顺序：
1、FROM FROM后面的表标识了这条语句要查询的数据源。和一些子句，生成虚拟表。
2、WHERE 对以上过程中生成的临时表进行过滤，满足where子句的列被插入到VT2表中。
3、GROUP BY 这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。
4、HAVING 这个子句对VT3表中的不同的组进行过滤，满足HAVING条件的子句被加入到VT4表中。
5、SELECT 这个子句对SELECT子句中的元素进行处理，生成VT5表。
(5-1)计算表达式 计算SELECT子句中的表达式，生成VT5-1
(5-2)DISTINCT 寻找VT5-1中的重复列，并删掉，生成VT5-2
(5-3)TOP 从ORDER BY子句定义的结果中，筛选出符合条件的列。生成VT5-3表
6、ORDER BY 从VT5-3中的表中，根据ORDER BY子句的条件对结果进行排序，生成VC6表
7、limit分页
解决问题
现在水平能做的优化？
- 使用合理的分页方式以提高分页的效率
- 避免在where子句中对字段进行null值判断
- 尽量不使用%前缀模糊查询
- 避免在where子句中对字段进行表达式操作
a 优化什么？
在数据库优化上有两个主要方面：即安全与性能。
-
安全->数据可持续性；
-
性能->数据的高性能访问。
b 优化的范围有哪些？
存储、主机和操作系统方面：
主机架构稳定性；I/O规划及配置；Swap交换分区；OS内核参数和网络问题。
应用程序方面：
应用程序稳定性；SQL语句性能；串行访问资源；性能欠佳会话管理；这个应用适不适合MySQL。
数据库优化方面：
内存；数据库结构（物理&逻辑）；实例配置。
c 优化维度？
数据库优化维度有四个：
硬件、系统配置、数据库表结构、SQL及索引。
8.存储引擎
默认支持的也是InnoDB
有十几种引擎，比较常用到的有三种：InnoDB，MyISAM，MeMory
InnoDB是行级锁，MyISAM, MeMory是表级别的锁
InnoDB
InnoDB是默认的数据库存储引擎，他的主要特点有：
（1）以页为单位存储数据，每页有16KB大小。
（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
（3）使用的锁粒度为行级锁，可以支持更高的并发；
（4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
（5）配合一些热备工具可以支持在线热备份；
（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+树的叶子节点上；
当然InnoDB的存储表和索引也有下面两种形式：
（1）使用共享表空间存储：所有的表和索引存放在同一个表空间中。
（2）使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。
对于InnoDB来说，最大的特点在于支持事务。但是这是以损失效率来换取的。
MyISAM
使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。
（1）frm文件：存储表的定义数据
（2）MYD文件：存放表具体记录的数据
（3）MYI文件：存储索引
MeMory
将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。
（1）支持的数据类型有限，比如：不支持TEXT和blob类型，对于字符串类型的数据，只支持固 定长度的行，VARCHAR会被自动存储为CHAR类型；
（2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
（3）由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；
（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表 就会转化为MyISAM类型的表，性能会急剧降低；
（5）默认使用hash索引。
（6）如果一个内部表很大，会转化为磁盘表。
在这里只是给出3个常见的存储引擎。使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能
辅助索引
InnoDB引擎中索引的结构，与MyISAM不同的一点，
InnoDB中辅助索引的数据域,存储的是，主键的值，而不是地址，
也就是说，正常情况下通过，辅助索引查询数据时，先从辅助索引拿到主键值，
再通过主键值走一次主索引拿数据**(辅助索引搜索需要检索两遍索引)**。
9.存储过程
存储过程 (Stored Procedure) 是一组为了完成特定功能(CRUD)的 SQL 语句集 , 存储在数据库中 , 经过第一次
编译后再次调用不需要再次编译。
储过程中可以包含
逻辑控制语句和数据操纵语句, 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;
所有的变量传入，必须以@开头。
简单的存储过程
create GC_Name GetUsers()
begin
select * from user;
end;
# 调用存储过程
call GC_Name();
# 删除存储过程
drop procedure if exists GC_Name;
存储过程复杂实例
ID 获取货品的价格 , 并根据参数判断是否折扣 :
create procedure GetPriceByID(
in prodID int,
in isDisc boolean,
out prodPrice decimal(8,2)
)
begin
declare tmpPrice decimal(8,2);
declare prodDiscRate decimal(8,2);
set prodDiscRate = 0.88;
select price from products
where id = prodID
into tmpPrice;
if isDisc then
select tmpPrice*prodDiscRate into tmpPrice;
end if;
select tmpPrice into prodPrice;
end;
该存储过程传入三个参数 , 货品 ID ,
是否折扣以及返回的价格 , 在存储过程内部 ,
定义两个局部变量 tmpPrice 和 prodDiscRate ,
把查询出来的结果赋给临时变量 , 再判断是否折扣 ,
最后把局部变量的值赋给输出参数 ;
调用如下 :
call GetPriceByID(1, true, @prodPrice);
select @prodPrice;
在mybatis中调用存储过程
创建insert_user存储过程
CREATE PROCEDURE insert_user(OUT u_id INTEGER,IN u_name VARCHAR(20),IN u_sex VARCHAR(20),IN u_age INTEGER)
BEGIN
INSERT INTO t_user (name,sex,age) VALUES (u_name,u_sex,u_age);
SET u_id=LAST_INSERT_ID();
END
在UserMapper.xml中调用insert_user存储过程
<!-- 添加用户 -->
<insert id="addUser" parameterType="com.po.User" statementType="CALLABLE">
{call insert_user(
#{id,mode=OUT,jdbcType=INTEGER},#{name,mode=IN},#{sex,mode=IN},# {age,mode=IN})}
</insert>
扫一扫
407
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
