MySQL高阶知识点

最新推荐文章于 2024-10-31 23:41:03 发布
最新推荐文章于 2024-10-31 23:41:03 发布
阅读量411 收藏
点赞数
分类专栏： mysql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_55931044/article/details/132227457
版权

MySQL

文章目录

char和varchar的区别

1.长度可变性：

varchar类型用于存储可变长度的字符串，比固定长度类型更加节省空间。有一种情况除外：如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储。

char类型用于存储定长字符串。

2.存储方式

varchar需要1或者2个额外字节记录字符串的长度：如果列的最大长度小于或者等于255字节，则只用使用1个字节表示，否则需要两个字节表示。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MylSAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

char适合存储很短或者长度近似的字符串。例如密码的MD5值，因为这是一个定长值。对于经常改变的值，char要比varchar好，因为定长的char类型不容易产生碎片。对于非常短的列，char在空间存储上比varcahr更有效率，例如用char（1）来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是varchar（1）却需要两个字节，因为还有一个记录长度的额外字节。

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态索引数据的查询。

创建视图：视图用create view语句来创建。

/*CREATE VIEW 视图名称 AS SQL查询语句*/
CREATE VIEW view1 AS
SELECT
  account,
  admin.name aname,
  role.name rname
FROM
  admin,
  admin_role,
  ROLE
WHERE admin.`id` = admin_role.adminid
  AND admin_role.`roleid` = role.`id`

执行语句:

SELECT * FROM view1

修改:

#方式一：
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
#方式二：
ALTER VIEW 视图名
AS
查询语句;

删除语句

DROP VIEW 视图1,视图2,...;

视图的特点

  1. 简化sql语句
  2. 提高SQL的重用性
  3. 保护基本表数据,提高了安全性

小结：

视图为虚拟的表。它包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT语句的层次。

存储过程

存储过程：简单来说，存储过程就是为以后使用而保存的一条或者多条SQL语句。可以将其视为批文件，虽然他们的作用不仅限于批处理。

在存储过程的定义中可以定义参数，参数分为IN、OUT、ONOUT三种类型。

  1. IN类型：表示接受调用者传入的数据；
  2. OUT类型：表示向调用者返回数；
  3. INOUT类型：既可以接受调用者传入的参数，也可以向调用者返回数据。

存储过程的特点

  1. 通过处理封装在容易使用的单元中，简化了复杂的操作。
  2. 简化对变动的管理。如表名、列名、或者业务逻辑有了变化。只需要更改存储过程的代码。使用它的人不用修改自己的代码。
  3. 有助于提高应用程序的性能
  4. 减少应用程序与数据库服务器之间的流量。应为应用程序不必发送多个冗长的SQL语句，只用发送存储过程中的名称和参数即可。

缺点：

  1. 大量使用存储过程，这些存储过程的每个链接的内存使用量将大大增加。
  2. 如果在存储过程中大量使用逻辑操作，CPU的使用率也会增加。MySQL数据库最初的设计就侧重于高效的查询，而不是逻辑运算。
CREATE
    PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])
	BEGIN
		[DECLARE 变量名 类型 [DEFAULT 值];]
		存储过程的语句块;
	END;

定义一个存储过程：

CREATE
    PROCEDURE `demo2`(IN s_sex CHAR(1),OUT s_count INT)
	-- 存储过程体
	BEGIN
		-- 把SQL中查询的结果通过INTO赋给变量
		SELECT COUNT(*) INTO s_count FROM student WHERE sex = s_sex;
		SELECT s_count;
		
	END$$

调用这个存储过程：

-- @s_count表示测试出输出的参数
CALL demo2 ('男',@s_count);
/* 结果
s_count
    6
*/

触发器

触发器trigger是一种特殊的存储过程,其特殊性在于它并不需要用户直接调用，而是对表添加、修改、删除之前或者之后自动执行的存储过程。

触发器的特点

  1. 与表相关联

    触发器定义在特定的表上，这个表称为触发器表。

  2. 自动激活触发器

    当表中的数据执行INSERT、UPDATE或者DELETE操作时，如果表上的这个操作定义了触发器，该触发器自动执行，这是不可以撤销的。

  3. 不能直接调用

    与存储过程不同，触发器不能被直接调用，也不能传递或者接受参数。

  4. 作为事务的一部分

    触发器和激活触发器的语句一起作为一个单一的事务来对待，可以从触发器中的任何位置回滚。

定义触发器：

CREATE TRIGGER 触发器名称 触发时机 触发事件
ON 表名称
	FOR EACH ROW -- 行级触发
BEGIN
	语句
END;

语法解析：

  1. 发器名称：用来标识触发器的，由用户自定义。
  2. 触发时机：其值为before或者after。
  3. 触发事件：insert、update和delete
  4. 表名称：表示在哪张表建立触发器
  5. 语句：触发器程序体，触发程序可以使用begin和end作为开始和结束，中间包含多条语句；

删除用户时，自动删除用户菜单关系

DELIMITER $$
CREATE TRIGGER delete_user_menu BEFORE DELETE
ON t_user
FOR EACH ROW
BEGIN
	DELETE FROM t_user_menu WHERE user_id = old.id;
END$$;

新增用户时 自动向其他表中插入数据

DELIMITER $$
CREATE TRIGGER save_user_log AFTER INSERT
ON user
FOR EACH ROW
BEGIN
	INSERT INTO test(id,NAME)VALUES(new.id,new.account);
END$$;

在行级触发器代码中,可以使用old和new访问到该行的旧数据和新数据,old和new是对应表的行记录类型变量。

MySQL引擎

数据库引擎用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并且快速处理事务，从而满足企业内大多数需要大量数据的应用程序要求。

存储引擎主要有：

MyIsam、InnoDB、Memory、Blackhole等。

mysql数据库默认引擎是InnoDB

InnoDB是一个事务型的存储引擎，有行级锁和外键约束，支持全文检索（索引），它的设计目标是处理大容量数据库系统，MySQL运行时InnoDB会在内存中建立缓冲池，用于缓冲数据和索引；支持主键自增，不存储表的总行数。

索引

为什么要有索引呢？

假设某张表中有10万条数据，这100万条数据在硬盘上是存储在数据页上的，一页数据大小为16K，100万条数据需要很多数据页，现在如果要查询id = 8900条数据信息。MySQL需要全表扫描来找到id = 8900 的数据，也就是从“数据页1”来查询，对于大量数据查询起来是非常慢的。

什么是索引

索引就是一个排好序的快速查找的数据结构。

数据库除存储数据本身之外，还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这要就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

在这里插入图片描述

左边是数据表，一共两列七行记录，最左边表示数据记录的地址，为了加快Col2的查找速度，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个执行对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到响应数据，而快速的检索出符合条件的记录。

索引的原理类似于查字典，查询书籍等。本质都是不断地缩小数据范围筛选出想要的结果。

索引的优势

提高数据检索的效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的劣势

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用磁盘内存的。

索引提高查询速度的同时也会降低更新表的速度，例如进行INSERT、UPDATE和DELETE因为更新时，MySQL不仅要保证数据，还要保证一下，索引文件，每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。

索引类型

FULLTEXT：全文索引，一般用于查找文本中的关键字，而不是直接比较是否相等，多在char、varchar、text 等数据类型中创建全文索引。全文索引主要是用来解决like模糊匹配效率低的问题

HASH：哈希索引，多用于等值查询，时间复杂度为o（1），效率非常高，但是不支持排序、范围查询和模糊查询等

BTREE：B+数索引，InnoDB存储引擎默认的索引，支持排序，分组，范围查询，模糊查询等，并且性能稳定。

RTREE：空间数据索引，多用于地理数据的存储，优势在于支持范围查找。

索引种类

主键索引：设定为主键后数据库会自动创建索引；

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

单列索引：一个索引值包含一个列，一个表可以有多个单列索引

CREATE INDEX 索引名 ON 表名(列名);

唯一索引：索引列的值必须唯一，允许为null

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

组合索引：一个索引包含多个列，在数据库操作期间，符合索引比单值左印所需要的的开销更小（对于相同的多个列建立索引）

CREATE INDEX 索引名 ON 表名(列 1,列 2…);

组合索引最左前缀原则

例如某个表中有a、b、c三列，a、b为组合索引，那么使用时需要满足最左侧索引原则。在使用组合索引的列作为条件时，必须出现最左侧列作为条件，否则组合索引不生效。

-- 列如
select * from table where a=’’and b=’’-- 索引生效
select * from table where b=’’and a=’’-- 索引生效
select * from table where a=’’and c=’’-- 索引生效
select * from table where b=’’and c=’’-- 索引不生效

索引创建原则

需要创建索引

  • 主键自动创建唯一索引
  • 频繁作为查询条件的字段应该设置为索引，就是where后面的语句
  • 查询中与其他表关联的字段，外键关系建立索引
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  • 分组中的字段需要建立索引

不要创建索引

  • 表记录太少

  • 经常增删改的表：索引提高了查询的速度，同时会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE，因为更新表时，MySQL不仅要存储数据，还要更新索引文件

  • where条件里用不到的字段不创建索引

  • 数据重复且分布平均的表字段

B树和B+树的区别

数据库为什么使用B+树而不是B树

聚簇索引和非聚簇索引

聚簇索引

找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引。

非聚簇索引

索引的存储和数据是分离的，也就是说找到了索引但是没有找到数据，需要根据索引上的值再次回表查询，非聚簇索引也叫辅助索引。

一个例子：

CREATE TABLE student (
	id BIGINT,
    NO VARCHAR (20), 
    NAME VARCHAR (20), 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `idx_no` (`no`)
)

创建一个学生表，做三种查询：

SELECT * FROM student WHERE id = 1

直接根据主键查询获取所有字段数据，此时主键是聚簇索引，因为主键对应的索引叶子节点存储了id=1的所有字段的值。

SELECT NO,NAME FROM student WHERE NO = 123

根据编号查询，编号本身是一个唯一索引，但查询的列包含学生编号和学生姓名，当命中编号索引时，该索引的节点的数据存储的是主键ID，需要根据主键ID重新查询一次，所以这种查询下编号no不是聚簇索引。

SELECT NO FROM student WHERE NO = 123

根据编号查询编号，？这有啥好差的？要的。可能是验证数据库中是否存在该编号，这种查询命中编号索引时，直接返回编号，因为需要的数据就是该索引，不需要回查，这种场景下no就是聚簇索引。

数据库事务

MySQL事务处理主要有两种方法

  1. 用BEGIN、ROLLBACK、COMMIT来实现

    BEGIN：开启一个事务

    ROLLBACK：事务回滚

    COMMIT：事务确认

  2. 直接用set来改变MySQL的自动提交模式：

    set global autocommit = 0;-- 禁止自动提交

set global autocommit = 1;-- 开启自动提交

查看autocommit模式:

show global variables like 'autocommit';

事务隔离级别

为什么要有隔离级别?

MySQL是一个服务器/客户端软件架构，也就是说，多个客户端连接服务器后，可以同时在不同的会话（一个客户端与服务器连接成功后就叫一个会话）中对服务器进行操作，输入各种语句，这些语句可以作为事务的一部分进行处理。不同的会话可以同时发送请求，也就是说服务器可能同时在处理多个事务，这样可能会导致不同的事务同时访问相同的数据。因为一个事务具有隔离性，当一个事务提交后其他事务才能执行继续访问这个数据。这样对性能影响很大，所以设计数据库时就提出来各种隔离级别，来最大限度的提升系统并发处理事务的能力。

查看隔离级别：

SELECT @@global.transaction_isolation,@@transaction_isolation;

mysql数据库提供了四种隔离级别，实际开发根据不同场景选择不容的隔离级别，除了串行化其他级别都存在某种问题。

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

原数据users表:

idnameage
1jim20
设置隔离级别为未提交读（read uncommitted）：

同时开启两事务A、B，B事务在A事务还没有提交的时候读取A中修改的数据。

事务A：

BEGIN;

UPDATE users SET age = 18 WHERE id = 1 -- 1

ROLLBACK -- 3

COMMIT; -- 4

事务B:

BEGIN;

SELECT age FROM users WHERE id = 1 -- 2

COMMIT;-- 5
/*
结果: 18
数据库表 age = 20
*/

B读取可A未提交的数据，A事务中发生了回滚，数据库中age并没有被修改，所以B事务读取到的数据是错的，所以称之为脏读。

设置提交读隔离级别（read committed）：

同时开启两个事务A、B，B事务在A事务提交后才能读取到A中修改的结果。

A事务未提交时，B事务获取到的仍然是事务开启前的结果。

A事务：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN;

UPDATE users SET age = 18 WHERE id = 1-- 1

COMMIT;-- 3

B 事务:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN;

SELECT age FROM users WHERE id = 1 -- 2  结果 20

SELECT age FROM users WHERE id = 1 -- 4 结果 18

COMMIT;-- 5

提交读隔离级别可以解决脏读问题，但是B事务中两次查询的结果不一致，称为不可重复读。

设置可重复读隔离级别（repeatable read）：

MySQL默认的隔离级别。同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但是除InnoDB 外幻读依然存在。

事务A：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN;

UPDATE users SET age = 18 WHERE id = 1 -- 2

COMMIT; -- 3

事务B:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN;

SELECT age FROM users WHERE id = 1 -- 1 结果18

SELECT age FROM users WHERE id = 1 -- 4 结果18

COMMIT; -- 4

可重复读，可能会产生幻读问题，幻读就是B事务查询了users 表，发现只有id=1的用户想要添加一个id = 2的用户，但是A事务快一步添加，B事务在添加的时候就会显示重复的主键，可是B事务查询明明没有id = 2的数据。

设置串行化(serializable)
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

解决幻读问题：

读写加锁，B事务如果未提交，A事务是无法insert的，必须等待B事务提交；

事务A：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN;

INSERT INTO users(id,NAME,age) VALUE (3 ,"ldl",18)-- 2 执行后一直在等待状态,直到B事务提交.

COMMIT;

事务B:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN;

SELECT * FROM users  -- 1
 
COMMIT;
事务隔离级别脏读不可重复读幻读
read uncommitted可能可能可能
read committed不可能可能可能
repeatable read不可能不可能可能
serializable不可能不可能不可能

事务实现的原理

MySQL中日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种日志，redolog（重做日志）和undolog（回滚日志）。其中redolog用于保存事务的持久性；undolog则是事务原子性和隔离性实现的基础。

在这里插入图片描述

原子性实现原理

原子性实现的关键，是当事务发生回滚的时候，能撤销所有已经成功执行的SQL语句。InnoDB实现回滚靠的是undolog：当事务对数据库中的数据进行修改时，InnoDB会生成对应的undolog；如果事务执行失败或者是执行ROLLBACK，导致事务需要回滚时，就可以利用undolog中的数据来回滚到修改之前的样子。

undolog属于逻辑日志，它记录的是SQL执行的相关信息。当发生回滚时，InnoDB会根据undolog的内容做与之前相反的工作：对于每个insert，回滚时执行delete，每个delete执行insert，每个update执行一个相反的update，把数据改回去。

持久性实现原理

redolog叫做重做日志，是保证实物持久性的重要机制。当mysql服务器意外崩溃或者宕机后，保证已经提交的事务，确定持久化到磁盘中的一种措施。

InnoDB是以页为单位来管理存储空间的，任何的增删改查操作最终都会操作完整的一个页，会将整页的数据加载到buffer pool中，然后对需要修改的记录进行修改，修改完毕后不会立即刷新到磁盘中，而且仅仅修改了一条记录，刷新一个完整的数据页的话过于浪费了。但是如果不刷新的话，数据此时还在内存中，如果此时系统崩溃最终数据会丢失。因此引入了redolog，也就是说，修改完成之后不立即刷新，而是记录一条日志，日志内容就是记录那个页面，多少偏移量，什么数据发生了什么改变。这样即使系统崩溃，在恢复后也可以根据日志进行数据恢复。另外，redolog是循环写入固定的文件，是顺序写入磁盘的。

总结：redolog就是记录在操作之后，记录哪个页面，多少偏移量，什么数据发生了怎样的变化。

隔离级别实现原理MVCC

MVCC是什么？

多版本并发控制（MVCC，Multi-Version Concurrent Control），是MySQL提高性能的一种方式，配合undolog和版本链，让不同事务的读写、写读操作可以并发执行，减少锁的使用，从而提升系统的性能。

MVCC的目的是让读写,写读操作并发执行.

MVCC使得数据库读不会对数据加锁，普通的select请求不会加锁，提高了数据库的并发处理能力。借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别。

在这里插入图片描述

事务D的两次查询在不同的隔离级别结果是不同的。

读未提交：name = 张二三 ；name = 张三三

读已提交：name = 张三 ；name = 张二三

可重复读： name = 张三； name = 张三

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。一个保存行的事务ID（TRX_ID）,一个保存了行的回滚指ROLL_PT).

trx_id：每次对某行记录执行改动时，都会把对应事务id赋值给trx——id隐藏列。

roll_pt：每次对记录进行改动时，都会把旧版本写到undolog日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到修改前的信息。
在这里插入图片描述

对该记录每次更新后，都会将旧值放到一条 undolog 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pt 属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务 id，这个信息很重要。

ReadView 是什么

ReadView是“快照读”SQL执行时MVCC提取数据的依据，是一个数据结构，包含4个字段

m_ids：当前活跃的事务编号集合

min_trx_id：最小活跃事务编号

max_trx_id：预分配事务编号，当前最大事务编号+1

creator_trx_id：ReadView创建者的事务编号

读已提交(RC)：

在这里插入图片描述
在这里插入图片描述

可重复读(RR):
在这里插入图片描述

读已提交级别: 称为当前读,当每个事物每次读取时,会生成一个 readVew,读取

的是最新数据.

可重复读级别: 称为快照读,当一个事务第一次查询时,会生成一个 readView,第二次查询时仍会从当前 readView 中读数据.

锁机制

行锁、间隙锁、表锁

按照粒度，锁可以分为行锁、间隙锁和表锁。间隙锁位于行锁和表锁之间。表锁在操作数据时会锁定整张表，并发性能差；行锁只锁定需要操作的数据，并发性能好，但是加锁本身就需要消耗资源（获取锁、检查锁、释放锁等都需要耗费资源），因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同存储引擎支持的锁是不一样的，MyIsam支持表锁，InnoDB同时支持表锁和行锁。

行锁

MySQL中粒度最小的一种锁，表示只针对当前操作的行进行加锁。行锁能大大减少数据库操作的冲突。其锁的粒度小，但是加锁的开销大

特点：开销大；加锁慢；会出现死锁；锁定力度小，发生冲突概率低；并发度也高。

间隙锁

锁的是一个区间,当我们用范围条件而不是相等条件检索数据，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙",InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。 例如：

select * from student where id>2 and id<5
/*
数据库中只有id为1和2的数据
即使数据库中没哟id = 3 的数据,其他线程想要添加id为3的数据也是不可以的,因为2到5的数据已经被锁定.
*/
表锁

表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享锁与表排他锁。

特点：

开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低.

共享锁(s)

共享锁又称为读锁。允许一个事务去读一行，组织其他事务获取相同数据的排他锁。若事务T对数据A加上S锁，则事务T可以读取A但是不能修改A，其他事务只能对A加S锁，不能加X锁，直到T释放A上的S锁。

排他锁(X)

排他锁又称为写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务 T 对数据对象 A 加上 X 锁，事务 T可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到 T 释放 A 上的锁。update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型。

SQL优化

  1. 尽量不要使用select * ，而是使用具体字段。可能用到覆盖索引，减少回表，提高查询效率。

  2. 避免在where字句中使用or来链接条件，使用union all吧两个SQL结果合并。使用or可能会使索引失效，从而全表扫描。

  3. 尽量用数值代替字符串类型。如：主键用int类型，性别用0/1.在查询和链接时会逐个比较字符串的每个字符；数值类型只需要比较一次就可以了。

  4. 对于查询的优化，尽量避免全表扫描，首先应该考虑在where以及order by涉及到的列上建立索引。

  5. 尽量避免索引失效
    5.1 在where字句中对字段进行null值判断，否则将会导致引擎放弃使用索引进而全表扫描，可以在num上设置默认值0，

    5.2 避免在where字句中使用or来链接条件，将会导致引擎无法使用索引进而全表扫描，使用union all吧两个SQL结果合并。

    5.3 in和not in 也要谨慎使用，例如in（1,2,3）这种连续的数值，能用between就不要用in，between 1 and 3

    5.4 模糊查询也将导致全表扫描。

    5.5尽量避免在where字句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：: select id from t where substring(name,1,3)='abc

  6. inner join、left join、right join优先使用inner join

    三种连接如果结果相同，优先使用 inner join

    inner join 内连接，只保留两张表中完全匹配的结果集；

    left join 会返回左表所有的行，即使在右表中没有匹配的记录；

    right join 会返回右表所有的行，即使在左表中没有匹配的记录；

  7. 使用group by 时，建议先过滤在分组。

  8. 清空表时，优先使用truncate

    truncate table 比 delete 速度快，且使用的系统和事务日志资源少.

    delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate

    table 通过释放存储表数据所用的数据页来删除数据.

  9. 表链接不宜太多。

  10. 不建议在索引上使用内置函数。

棪小木
关注
专栏目录
mysql高级课程知识点
码上人生的博客
03-28 1032
思维导图:
mysql高级知识点
samsung_samsung的专栏
12-26 981
外键约束用于在一个表中创建对另一个表中数据的引用，从而确保关联表中的数据与主表中的数据保持一致。外键约束可以限制插入、更新和删除操作，防止关联表中出现无效的引用或不一致的数据。外键约束可以在创建表时定义，也可以在已有的表上添加或删除。创建表时定义外键约束的示例： 下面是一个在创建表时定义外键约束的示例，假设我们有两个表orders和customers，其中orders表中有一个字段，用于关联customers表中的id字段。在上面的示例中，我们在创建orders表时定义了一个外键约束，将。
一篇就够mysql高阶知识总结
2401_83447580的博客
07-19 993
【代码】一篇就够mysql高阶知识总结。
MySQL高阶语句
pangdongqiqi的博客
09-12 1012
前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程， 轻松而高效的去完成这个需求，有点类似shell脚本里的函数。1、存储过程是一组为了完成特定功能的SQL语句集合。两个点 第一 触发器（定时任务） 第二个判断。
MySQL高级 知识点汇总
fulin9452的博客
04-02 756
PS:图片来源 尚硅谷 Mysql高级篇 文章目录架构索引Explain慢查询批量插入数据show profile全局查询日志表锁行锁主从复制主从复制 架构 mysql配置文件 mysql分层（插件式，可拔插）：插件式存储引擎架构将查询处理和其他任务以及数据的存储提取相分离 连接层 最上一层是客户端和连接服务（JDBC之类），包含本地sock通信和大多数基于客户端/服务器的工具实现...
MySQL常见知识点总结
m0_49692893的博客
02-12 2032
文章目录一、MySQL高频面试30问1.1 MySQL的三大范式？1.2、MyISAM与InnoDB区别?二、总结 一、MySQL高频面试30问 1.1 MySQL的三大范式？ 范式是我们创建表的时候要遵循的规范。满足最低要求的范式为第一范式1NF，在第一范式的基础上满足更多规范要求的称为第二范式2NF，第三范式3NF以此类推。 1NF： 第一范式即表的列具有原子性，不可以在分割，即列的信息不能再分解不能是集合和数组。只要是关系型数据库就自动满足1NF。如果一个表中的字段有多个值时必须拆分成不同属性。通俗
MYSQL高阶
qq_52286612的博客
12-23 643
MYSQL 存储引擎 innoDB 1.支持事务 对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务;(也不是说越多越好，多了会造成卡顿)。 2.支持表锁和行锁 InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。 例如： t_user(uid, uname, age, sex) innodb; uid..
MySQL高阶知识点(一)事务的并发问题和隔离级别
数据库技术
08-14 757
简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。
Mysql的高阶知识之索引的优化
weixin_43791059的博客
06-19 355
文章目录mysql高级知识mysql的架构介绍索引优化分析 mysql高级知识 mysql的架构介绍 索引优化分析 mysql的简介 MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。 MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 Mysql是开源的，所以你不需要支付额外的费用。 Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。 Mysql支持大
【檀越剑指大厂--mysql】mysql高阶篇
热门推荐
檀越的博客
08-17 1万+
为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead log策略即当事务提交时，先写重做日志，再修改页。当由干发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中D(Durability持久性)的要求。checkpoint的作用缩短数据库的恢复时间;缓冲池不够用时，将脏页刷新到磁盘;Lru列表不够用时,强制刷新脏页重做日志不可用时，刷新脏页。对于InnoDB存储引擎而言，其是通过LSN(LogSequence Number)来标记版本的。.........
游乐园系统 java高阶期末大作业 java gui（windowbuilder+mysql）
07-24
游乐园系统是一个基于Java GUI和MySQL数据库的高阶期末大作业，旨在让学生深入理解并实践Java编程、图形用户界面设计以及数据库管理。这个系统利用了WindowBuilder工具来创建用户友好的图形界面，使得非程序员也能...
MySQL知识点全面总结3：Mysql高级篇
qq_44724899的博客
03-07 482
学习
Java高阶知识体系总结（一）
Feify博客
05-12 8314
Java高阶知识体系总结 作为Java开发者多年，是否感觉自己只是凌乱，掌握的知识没有体系化？那么通过此次总结，我们来尝试将高阶知识体系化。
【Linux】MySQLMGR主从复制
Stringzhua的博客
10-30 869
MySQL是⽬前最流⾏的开源关系型数据库，国内⾦融⾏业也开始全⾯使⽤，其中MySQL5.7.17提出的 MGR(MySQL Group Replication)既可以很好的保证数据⼀致性⼜可以⾃动切换，具备故障检测功能、 ⽀持多节点写⼊，MGR是⼀项被普遍看好的技术。MGR(MySQL Group Replication)是MVSQL⾃带的⼀个插件，可以灵活部署。
easy-es使用以及Es和MySQL同步
weixin_51918722的博客
10-31 483
它主要就是简化了ES相关的API, 使用起来像MP一样舒服。
【重学 MySQL】七十九、深入探索用户变量
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
10-30 888
定义：用户变量以“@”符号开头，后面跟着变量名。变量名可以是任何随机的、复合的标量表达式，只要其中没有列指定。赋值可以使用SET语句来定义和赋值用户变量，如。也可以使用SELECT语句来为用户变量赋值，但必须使用“:=”赋值运算符，因为在SELECT语句中，MySQL将“=”运算符视为等于运算符。例如，。作用域：会话用户变量的作用域是当前会话，而局部变量的作用域是其定义的BEGIN/END语句块。命名：会话用户变量以“@”符号开头，而局部变量不需要。使用场景。
MYSQL--完整性约束
最新发布
2301_76890677的博客
10-31 1235
左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键,关联的是另一张表的主键。外键是另一张表的主键！例如员工表与部门表之间就存在关联关系，其中员工表中的部门编号字段就是外键，是相对部门表的外键。创建学生表：id（唯一 不能为空），name（非空），sex（默认值 男），age，email（唯一）
mysql 8.0.39 Caused by: java.sql.SQLException: Illegal mix of collations 异常解决
LFCuiYs的博客
10-30 399
发现设置utf8mb3_general_ci 会报错，所以要与参数一直 改成utf8mb4_general_ci 即可生效。java服务可以正常启动，页面发现查询报错Illegal mix of collations。经查看发现是关联表排序异常，因是mysql 8.0.39。然后定位异常 调成一直参数问题解决。
【MySQL】 运维篇—备份与恢复：使用MySQL Replication进行数据冗余与高可用性
thinking_chou的专栏
10-30 695
通过使用 MySQL Replication，企业可以实现数据的冗余和高可用性，确保在主服务器出现故障时，从服务器可以快速接管服务。设置 MySQL 复制需要在主服务器和从服务器上进行相应的配置，并确保网络连接正常。在实际应用中，建议定期监控复制状态，并进行故障演练，以确保在出现问题时能够迅速恢复业务。同时，合理规划主从架构（如多从服务器配置）可以进一步提高系统的性能和可靠性。
全面掌握MySQL：从基础知识到高阶技能必问面试题
资源摘要信息:"MySQL面试题史上最强汇总" 一、基础知识 ...以上是针对给定文件标题和描述中提及的MySQL面试题知识点的详细解读。在实际应用中，掌握这些知识点对于数据库开发、管理和维护人员非常重要。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值