MySQL高级

大小写规范

  1. MySQL在Windows下是大小写不敏感的，会全部转换成小写

  2. MySQL在Linux下的大小写规则：

    1. 数据库名、表名、表的别名、变量名是严格区分大小写的；
    2. 关键字、函数名称在SQL中不区分大小写
    3. 列名（或字段名）与列的别名（或字段别名）在所有的的情况下均是忽略大小写的

SQL编写建议

  1. 关键字和函数名全部大写
  2. 数据库名、表名、表别名、字段名、字段别名全部小写
  3. SQL语句必须以分号结尾

用户与权限管理

1.用户管理

  1. 创建用户

    create user 'zhangsan'@'localhost' identified by 'zs123'

  2. 删除用户

    DROP USER user[,user...]

  3. 修改密码

    ALTER USER '用户名'@'主机' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';

2.权限管理

MySQL有哪些权限？

show privileges;

查看权限

  1. 查看当前用户权限

    show grants;
-- 或
show grants for current_user;
-- 或
show grants for current_user();

  2. 查看某用户的全局权限

    show grants for 'user'@'主机地址'

授权

授权方法有两种，分别是赋予用户角色和直接给用户授权。

命令：

GRANT 权限1，权限2...权限n ON 数据库名.表名 TO '用户名'@'用户地址' [IDENTIFIED BY '密码']

  • 如果没有发现这个用户，则会直接新建一个用户。

  • 给zhangsan用户，授予test数据库下所有表的增、删、改、查的权限

    GRANT SELECT,INSERT,UPDATE,DELETE ON test.* TO 'zhangsan'@'localhost'

  • 授予zhangsan用户，所有数据库的所有权限，注意：这里不包括grant权限

    GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost'

收回权限

收回权限命令

REVOKE 权限1，权限2...权限n ON 数据库名.表名 FROM 用户名@用户地址

3.权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。权限表最重要的有

user表、db表。除此之外，还有tables_priv表、column_priv表和proc_priv表等。

4.角色管理

4.1创建角色

CREATE ROLE 'role_name'[@'host_name'][,'role_name'[@'host_name']]...

角色命名的规则和用户名类似。如果host_name省略，默认为%，role_name不可省略。

只要创建一个角色，系统会自动给一个USAGE权限，意思是连接登录数据库的权限。

4.2给角色赋予权限

GRANT 权限 ON 数据库名.表名 TO 'role_name[@'host_name']'

4.3查看角色权限

SHOW PRIVILEGES FOR '角色名'[@'主机名']

4.4回收角色权限

REVOKE 权限 ON 数据库名.表名 FROM '角色名'[@'主机名']

4.5删除角色

DROP 角色1[,角色2...]

4.6给用户赋予角色

GRANT 角色1[,角色2...] TO 用户1[,用户2...]

4.7查看当前角色

SELECT CURRENT_ROLE();

4.8激活角色

激活角色有两种方式

方式一：使用set default role命令激活

SET DEFAULT ROLE '角色名'[@'主机名'] ALL TO '用户名1'[@'主机名'],'用户名2'[@'主机名']...

方式二：将变量activate_all_roles_on_login设置为ON，意思是：对所有角色永久激活。

SET GLOBAL activate_all_roles_on_login=ON;

注意：

MySQL创建角色后，默认都是未激活的，也就是不能用，需要手动激活

配置文件的使用

配置文件中的启动选项被划分为若干个组，每一个组有一个组名，用[]括起来，像下面这样：

[server]
(具体的启动选项...)

[mysqld]
(具体的启动选项...)

[mysqld_safe]
(具体的启动选项...)

[client]
(具体的启动选项...)

[mysql]
(具体的启动选项...)

[mysqladmin]
(具体的启动选项...)

SQL执行流程

了解查询语句底层执行过程：select @@profiling; 或者show variables like '%profiling%'查看是否开启计划。开启它可以让MySQL收集在SQL执行时所使用的资源情况，命令如下:

select @@profiling

profiling=0代表关闭，设置为1则把profiling打开

set @@profiling=1;

执行sql操作后，执行以下命令，查看过程：

# 查看所有的执行sql
show profiles;

# 查看最后执行sql的执行过程
show profile;

# 查看指定的sql执行过程
show profile for query query_id;

还可以加上cpu、io阻塞等参数进行查询。

show profile,cpu,block io for query query_id;

存储引擎

1.查看存储引擎

  • 查看mysql提供什么存储引擎：

    show engines;

  • 查看默认存储引擎

    SELECT @@default_storage_engine;
# 或
show variables like '%storage_engine%';

2.修改存储引擎

  • 修改默认存储引擎

    SET DEFAULT_STORAGE_ENGINE=MyISAM;

  • 或修改my.cnf文件

    default-storage-engine=MyISAM

3.InnoDB引擎

  • 支持外键和事务
  • 数据文件结构
    • 表名.frm ：存储表结构（MySQL8.0时，合并在表名.ibd中）
    • 表名.ibd：存储数据和索引
  • 对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且占用更多的磁盘空间以保存数据和索引
  • 不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响。

4.MyISAM引擎

  • mysql5.5之前的默认存储引擎
  • 优势：访问数据快，对事务完整性没有要求或以SELECT、INSERT为主的应用
  • 数据文件结构
    • 表名.frm： 存储表结构
    • 表名.MYD： 存储数据（MYData）
    • 表名.MYI ：存储索引（MYIndex）
  • 应用场景：只读应用或以读为主的业务

5.InnoDB与MyISAM对比

对比项 InnoDB MyISAM
外键 支持 不支持
事务 支持 不支持
行表锁 行锁，操作时只锁住一行，适合高并发操作 表锁，操作一条数据，也会锁住整个表，不适合高并发情况
缓存 不仅缓存索引还缓存真实数据，对内存要求比较高，而且内存大小对性能有决定性影响 只缓存索引，不缓存真实数据
关注点 事务：并发写、更大资源 性能：节省资源、消耗小、简单事务
自带系统表使用 N Y
默认安装 Y Y
默认使用 Y N

索引

1.索引的分类

MySQL索引包含：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引等。

  • 从功能逻辑上划分，有四种：普通索引、唯一索引、主键索引、全文索引。
  • 从物理实现实现方式，分为：聚簇索引、非聚簇索引。
  • 从作用字段个数，分为：单例索引、联合索引。

2.创建索引

  1. 创建表CREATE TABLE中指定索引

  2. 使用ALTER TABLE语句在存在的表上创建索引

  3. 使用CREATE INDEX语句在已存在的表上创建索引

  4. 全文索引，只能在类型为CHAR、VARCHAR、TEXT的字段上创建

    • 全文索引查询数据采用以下语句：

      SELECT * FROM table_name WHERE MATCH(列名) AGAINST ('查询字符串')

2.1 创建表的时候创建

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

2.2在已有表的时候创建

  • 第一种

    ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] 索引名(列名)

  • 第二种

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

3查看索引

SHOW INDEX FROM 表名

4删除索引

# 通过删除主键约束的方式删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY

# 直接删除索引
ALTER TABLE 表名 DROP INDEX 索引名

# 直接删除
DROP INDEX 索引名 ON 表名

5.MySQL8.0索引新特性

  • 支持索引降序

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

  • 隐藏索引：删除索引前，先隐藏索引，验证是否有影响，无影响后再删除索引。

    ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] 索引名(列名) invisible;

    # 修改索引的可见性
ALTER TABLE 表名 ALTER INDEX 索引名 (invisible | visible)

索引的设计原则

创建表

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(128) DEFAULT NULL,
  `password` varchar(128) DEFAULT NULL,
  `tenant_id` int DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `tenant` (
  `tenant_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`tenant_id`)
);

创建函数

# 函数一：随机产生字符串
DELIMITER //
CREATE FUNCTION rand_str(n INT) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
		DECLARE chars_str VARCHAR(100) DEFAULT 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM';
		DECLARE return_str VARCHAR(255) DEFAULT '';
		DECLARE i INT DEFAULT 0;
		WHILE i < n DO
		SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOAT(1 + RAND() * 52), 1));
		SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

# 函数二：产生随机数字
DELIMITER //
CREATE FUNCTION rand_num (from_num INT, to_num INT) RETURNS INT(11)
DETERMINISTIC
BEGIN
		DECLARE i INT DEFAULT 0;
		SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
RETURN i;
END //
DELIMITER ;

创建存储过程

# 存储过程一：插入user表
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(max_num INT, tenant_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
 SET autocommit = 0;  # 设置手动提交事务
 REPEAT # 循环
 SET i = i + 1;
 INSERT INTO user(username, password, tenant_id, created_time) VALUES (rand_str(6), rand_str(10), rand_num(1, tenant_num), NOW());
 UNTIL i = max_num
 END REPEAT;
 COMMIT;  # 提交事务
 
 END
 
# 存储过程二：插入tenant表
DELIMITER //
CREATE PROCEDURE insert_tenant(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
 SET autocommit = 0;  # 设置手动提交事务
 REPEAT # 循环
 SET i = i + 1;
 INSERT INTO tenant(name) VALUES (rand_str(6));
 UNTIL i = max_num
 END REPEAT;
 COMMIT;  # 提交事务
 
 END //
 DELIMITER ;

调用存储过程

call insert_tenant(100);
call insert_user(1000000, 100);

哪些情况适合建索引

1.字段的数值有唯一性限制

2.频繁作为WHERE查询条件的字段

3.经常GROUP BY 或 ORDER BY的列

4.UPDATE、DELETE 的 WHERE 条件列

如果进行更新的时候，更新的字段是非索引字段，提升的效率会更加明显，这是因为非索引字段更新时不需要对索引进行维护

5.DISTINCT字段需要创建索引

6.多表JOIN连接操作时，创建索引注意事项

  1. 首先，连表数量尽量不要超过3张。
  2. 其次，对WHERE条件列创建索引。
  3. 最后，对于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

7.使用列的类型小的创建索引

类型大小指的是该类型表示的数据范围的大小

8.使用字符串前缀创建索引

怎么计算不同长度的选择性？

先看一下字段在全部数据中的选择度：

select count(distinct 列名) / count(*) from 表名

通过不同长度去计算，与全表选择性对比

公式：

count(distinct left(列名, 索引长度)) / count(*)

例如：

select count(distinct left(address, 10)) / count(*) as sub10,    -- 截取前10个字符选择度
count(distinct left(address, 15)) / count(*) as sub10, 			 -- 截取前15个字符选择度
count(distinct left(address, 20)) / count(*) as sub10, 			 -- 截取前20个字符选择度
count(distinct left(address, 25)) / count(*) as sub10 			 -- 截取前25个字符选择度
from user;

拓展：Alibaba《Java开发手册》

【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引。

9.区分高度（散列性高） 的列适合做索引

公式：

select count(distinct 列名) / count(*) from 表名;

说明：越接近1越好，一般超过33%，就算是比较高的索引了。

10.使用最频繁的列放到联合索引的左侧

11.在多个字段都要创建索引的情况下，联合索引要优于单列索引。

12.限制索引的数量

建议单张表索引数量不超过6个。原因：

  1. 索引需要占用磁盘空间，索引越多，磁盘空间占用越大。
  2. 索引会影响INSERT、UPDATE、DELETE语句的性能，因为数据的更新，索引也会进行调整和更新，会造成负担。
  3. 对优化器进行索引评估，降低了性能。

哪些情况不适合创建索引

1.WHERE（包括GROUP BY、ORDER BY）条件没有用到的字段不要建索引

2.数据量小的表不要建索引

3.有大量重复数据的列上不要建索引

比如重复度高于10%的时候不要创建索引

4.避免对经常更新的表创建过多的索引

5.不建议用无序的值创建索引

列如身份证、UUID（在索引比较时需要转为ASCLL，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等。

性能分析工具的使用

1.查看系统性能参数

在MySQL中可以使用SHOW STATUS语句查询MySQL数据库服务器的性能参数、执行频率。

语法：

SHOW [GLOBAL | SESSION] STATUS LIKE '参数';

常用参数：

  • Connections：MySQL连接数
  • Uptime：MySQL服务器上线时间
  • Slow_queries：慢查询次数
  • Innodb_rows_read：Select查询返回行数
  • Innodb_rows_inserted：执行INSERT插入的行数
  • Innodb_rows_updated：执行UPDATE操作更新的行数
  • Innodb_rows_deleted：执行DELETE操作的行数
  • Com_select：查询操作的次数
  • Com_insert：插入操作的次数。对于批量操作，只累加一次
  • Com_update：更新操作的次数
  • Com_delete：删除操作的次数

2.定位执行慢的SQL：慢查询日志

运行时间超过long_query_time值的SQL，会被记录到慢查询日志中。

long_query_time默认值是10，意思是执行10秒以上的语句会被记录。

默认情况下，MySQL数据库没有开启慢查询日志，需要手动开启。如果不是调优需要，不建议开启改参数

，开启会有一定的性能影响。

2.1开启慢查询日志参数

使用前我们查看下是否开启：

show variables like '%slow_query_log%';

开启：

set global slow_query_log = 'ON';

2.2慢查询日志分析工具：mysqldumpslow（不是在mysql环境下执行）

查看命令帮助：

mysqldumpslow --hlep

分析查询语句：EXPLAIN

定位查询慢的SQL后，我们就可以使用 EXPLAIN 或 DESCRIBE 工具做针对性的分析查询语句。

1.能做什么？

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

基本语法

EXPLAIN SELECT * FROM table;
# 或
EXPLAIN UPDATE table set ...;

EXPLAIN语句输出各个列的作用如下：

列名 描述
id 查询语句中select关键字对应的唯一id
select_type SELECT关键字对于的查询类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上用到的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估需要读取的记录条数，值越小越好
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

小结：

  • id如果相同，可以认为是一组，从上往下顺序执行
  • 所有组中，id值越大，优先级越高，越先执行
  • 每个id，表示一趟独立的查询，一个sql查询趟数越少越好

select_type

名称 描述
SIMPLE
PRIMARY
UNION
UNION RESULT
SUBQUERY
DEPENDENT SUBQUERY
DEPENDENT UNION
DERIVED
MATERIAKIZED
UNCACHEABLE SUBQUERY
UNCACHEABLE UNION

type

比如，看到type列的值为ref，表明MySQL会使用ref访问方法来执行对表的查询

完整访问方法入下：

  1. system：表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system

  2. const：当我们根据主键或唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const

  3. eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较）

  4. ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表

  5. fulltext

  6. ref_or_null：当对普通二级索引进行等值匹配查询时，改索引列的值也可以是NULL值时

  7. index_merge：有两个单列索引可以选择时

  8. unique_subquery：针对于一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话

  9. index_subquery

  10. range：如果使用索引获取某些范围区间的记录，那么可能使用到range方法访问

  11. index：当我们可以使用索引覆盖，当需要扫描全部的索引记录时，该表的访问方法就是index

  12. ALL：全表扫描

注意：越往后，性能越低

SQL优化的目标：至少达到range级别，要求是ref级别，最好时const级别

possible_keys和key

可能使用到的索引和实际使用到的索引

EXPLAIN进一步使用

1.四种输出格式

  • 传统格式

  • JSON格式

    EXPLAIN FORMAT = JSON SELECT ...

  • TREE格式

    EXPLAIN FORMAT = TREE SELECT ...

  • 可视化输出：可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划

索引优化与查询优化

事务

1.显示事务

  • 使用start transaction或者begin来开启事务
  • start transaction后面可以跟：read only、read write(默认) 、with consistent snapshot

2.隐式事务

  • 关键字：autocommit(默认ON)：SHOW VARIABLES LIKE 'autocommit' ;
  • 关闭自动提交：SET autocommit = false;，或者使用start transaction或begin开启事务，不会自动提交;

事务日志

  • 事务的隔离性是由锁机制实现的
  • 而事务的原子性、一致性、和持久性是由事务的redo日志和undo日志来保证的
    • redo log称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性
    • undo log称为回滚日志，回滚记录到某个特定版本，用来保证事务的原子性、一致性

锁

数据操作的类型划分：读锁、写锁

  • 对读记录加S锁（共享锁）

    SELECT ... LOCK IN SHARE MODE;
-- 或
SELECT ... FOR SHAR; -- (8.0新增语法)

  • 对读取的记录加X锁（排他锁）

    SELECT ... FOR UPDATE;

写操作

  1. DELETE:

    b+树中定位到这条数据的位置，然后获取这条记录的X锁，再执行delete mark操作。

  2. UPDATE:

    • 情况1：未修改主键值，并且更新其他列值，列占用的储存空间未发生了变化：

      b+树中定位数据的位置，再获取X锁，然后在原记录的位置上进行修改

    • 情况2：未修改主键值，并且更新其他列值，列占用的储存空间发生了变化：

      b+树中定位数据的位置，再获取X锁，将该记录彻底删掉（把记录彻底拉入垃圾链表），最后插入一条新数据。新插入的操作由INSERT操作提供的隐式锁进行保护

    • 情况3：修改了该记录的主键值：

      则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了

  3. INSERT:

    一般情况下，新插入一条记录并不加锁，通过一种称为隐式锁的结构来保护这条新插入的记录在本事务提交前不被别的事务访问。

数据操作的粒度划分：表级锁、页锁、行锁

1.表锁

锁整张表，MySQL最基本的锁策略，并不依赖储存引擎。所有储存引擎，表锁策略都一样。表锁开销小，能避免死锁问题，但是降低了并发率

  • InnoDB储存引擎对表t加S锁：

    LOCK TABLES t READ

  • InnoDB储存引擎对表t加x锁：

    LOCK TABLES t WRITE
posted @   有梦想的程序员。  阅读(44)  评论(0)  编辑  收藏  举报
相关博文：
· Mycat学习笔记
· Redis学习笔记
· MySQL数据库学习笔记
· Mysql笔记
· MySQL
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
点击右上角即可分享
微信分享提示
AI IDE Trae