Mysql 相关面试问题

最新推荐文章于 2024-04-04 05:28:49 发布
最新推荐文章于 2024-04-04 05:28:49 发布
阅读量389 收藏
点赞数
分类专栏： 笔记 文章标签： mysql 面试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45228674/article/details/110210816
版权

文章目录

Mysql 相关面试问题

mysql 关系型数据库

转自:Mysql各种存储引擎对比总结

面试常问的20个数据库高频面试题详解！

MySQL面试常见问题

1.Mysql常见的3种引擎?

常见的3种引擎 1.InnoDB默认(5.5之后) 2.MyISAM 3.MEMOR

常用的就两个 InnoDB MyISAM

# 常见的3中引擎有什么区别?

功能MyISAMMEMORYInnoDB
存储限制256TBRAM64TB
支持事务不支持-支持
支持全文索引支持--
支持B树索引支持支持支持
支持哈希索引-支持-
支持集群索引--支持
支持数据索引-支持支持
支持数据压缩支持--
空间使用率低-高
支持外键--支持

# 两种常用引擎特点 小结一下:

  1. InnoDB支持事务 ,MyISAM 不支持 .

  2. MyISAM 适合查询以及插入为主的应用.

  3. InnoDB 适合频繁修改以及涉及到安全性高的应用.

  4. InnoDB支持外键 , MyISAM不支持

  5. 从Mysql5.5之后,默认引擎就是InnoDB.

  6. InnoDB不支持 fulltext 全文索引.

  7. InnoDB中不保存表的行数 , 如 select count(*) from table 时,InnoDB需要扫描整个表计算有多少行,但MyISAM只要读出保存好的行数即可.但当count(*)后有where条件时 MyISAM也需要扫描整张表.

  8. delete from table时,InnoDB不会重新建立表,而是一行一行删除,效率慢.MyISAM则会重新建表.

  9. InnoDB支持行锁,(某些情况下还是锁整个表,如update table set status=1 where name like '%lee%').

  10. 引擎特性
    MYISAM不支持外键，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描
    INNODB支持外键，行锁，查表总行数时，全表扫描

# MySQL的MyISAM与InnoDB两种存储引擎在，事务、锁级别，各自的适用场景？

事务处理上方面

  • MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
  • InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

锁级别

  • MyISAM：只支持表级锁，用户在操作MyISAM表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
  • InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

# MyISAM查询效率高?

如果应用程序对查询性能要求较高，就要使用MyISAM了。MyISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MyISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

（1）frm文件：存储表的定义数据

（2）MYD文件：存放表具体记录的数据

（3）MYI文件：存储索引

问 : 两种引擎所使用的索引的数据结构是什么？

都是B+树.

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引

B+树 是啥?

快看这位大佬写的 刘召考的博客 » MySQL索引-B+树（看完你就明白了）

有关索引的知识再看: 数据库常见面试题（附答案）

2.事务?

# 什么是事务?

什么是 事务 ?

  • 事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

  • 事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所以操作。

# 事务的特性(ACID)?

原子性（Atomicity）

  • 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）

  • 事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）

  • 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

    同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

持久性（Durability）

  • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

# 事务隔离级别

事务隔离级别脏读不可重复读幻读
读未提交 read-uncommitted是是是
不可重复读 read-committed否是是
可重复读 repeatable-read否否是
串行化 serializable否否否

  • 读未提交：另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读

  • 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。

  • 可重复读：在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象

  • 串行化：最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样

MySQL默认的事务隔离级别为repeatable-read

  • MySQL 支持 4 中事务隔离级别.
  • 事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持.
  • Oracle 支持的 2 种事务隔离级别：READ_COMMITED , SERIALIZABLE
补充：
  1. SQL规范所规定的标准，不同的数据库具体的实现可能会有些差异
  2. MySQL中默认事务隔离级别是“可重复读”时并不会锁住读取到的行
  • 事务隔离级别：未提交读时，写数据只会锁住相应的行。
  • 事务隔离级别为：可重复读时，写数据会锁住整张表。
  • 事务隔离级别为：串行化时，读写数据都会锁住整张表。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

# 如何更改事务隔离级别?

//查看当前事物级别：
SELECT @@tx_isolation;

@@tx_isolation   
-----------------
REPEATABLE-READ  

//设置mysql的隔离级别：
set session transaction isolation level 设置事务隔离级别

//设置read uncommitted级别：
set session transaction isolation level read uncommitted;

//设置read committed级别：
set session transaction isolation level read committed;

//设置repeatable read级别：
set session transaction isolation level repeatable read;

//设置serializable级别：
set session transaction isolation level serializable;

3.存储过程 触发器 函数 事件 视图 你了解多少?

# 存储过程

转自:

mysql存储过程学习笔记

一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

1、能完成较复杂的判断和运算
2、可编程行强，灵活
3、SQL编程的代码可重复使用
4、执行的速度相对快一些
5、减少网络之间的数据传输，节省开销

创建存储过程语法

create procedure 名称()
begin
.........
end

调用存储过程

call 存储过程名称();

示例:

DELIMITER $$
create procedure test()
begin
  -- 使用 declare语句声明一个变量
  declare username varchar(32) default '';
  -- 使用set语句给变量赋值
  set username='xiaoxiao';
  -- 将users表中id=1的名称赋值给username
  select name into username from users where id=1;
  -- 返回变量
  select username;
end$$
DELIMITER ;
(1)、变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用；
(2)、变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能制定默认值、字符集和排序规则等；
(3)、变量可以通过set来赋值，也可以通过select into的方式赋值；
(4)、变量需要返回，可以使用select语句，如：select 变量名。

-- -
call test()
调用存储过程

# 触发器

触发器与函数、存储过程一样，触发器是一种对象，它能根据对表的操作时间，触发一些动作，这些动作可以是insert,update,delete等修改操作。

  • 语法:
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
        执行语句列表
END;
  • 触发器创建
DELIMITER $$
create trigger tr_users_insert after insert on users
    for each row 
    begin 
        insert into oplog(userid,username,action,optime)
        values(NEW.id,NEW.name,'insert',now());
    end$$
DELIMITER ;

创建触发器 当 users表 insert 后 触发 执行 insert into oplog(userid,username,action,optime) values(NEW.id,NEW.name,'insert',now());

  • NEW与OLD详解

    MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

    • 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
    • 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
    • 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；
    • 使用方法: NEW.columnName （columnName为相应数据表某一列名）
    • 另外，OLD是只读的，而NEW则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

# 函数

函数与存储过程最大的区别是函数必须有返回值，否则会报错

  • 语法:

    create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;
    • 参数列表的格式是： 变量名 数据类型

  • 示例:

    DELIMITER $$
create function myfun(name varchar(15)) returns int
begin 
    declare c int;
    select id from class where cname=name into c;
    return c;
end$$
DELIMITER ;

---
调用函数
select myfun('java');
    • ...into c; ? 就是将select id from class where cname=name 的值赋值给变量c

# 事件

转自: MySQL事件(定时任务)

一组SQL集，用来执行定时任务，跟触发器很像，都是被动执行的，事件是因为时间到了触发执行，而触发器是因为某件事件（增删改）触发执行；

  • 语法:

    CREATE
	[DEFINER={user | CURRENT_USER}]
	EVENT [IF NOT EXISTS] event_name
	ON SCHEDULE schedule
	[ON COMPLETION [NOT] PRESERVE]
	[ENABLE | DISABLE | DISABLE ON SLAVE]
	[COMMENT 'comment']
	DO event_body;

    CREATE EVENT语句的子句：

    子句说明
    DEFINER可选，用于定义事件执行时检查权限的用户
    IF NOT EXISTS可选项，用于判断要创建的事件是否存在
    EVENT event_name必选，用于指定事件名，event_name的最大长度为64个字符，如果为指定event_name，则默认为当前的MySQL用户名（不区分大小写）
    ON SCHEDULE schedule必选，用于定义执行的时间和时间间隔
    ON COMPLETION [NOT] PRESERVE可选，用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE
    ENABLE | DISABLE | DISABLE ON SLAVE可选项，用于指定事件的一种属性。其中，关键字ENABLE表示该事件是活动的，也就是调度器检查事件是否必选调用；关键字DISABLE表示该事件是关闭的，也就是事件的声明存储到目录中，但是调度器不会检查它是否应该调用；关键字DISABLE ON SLAVE表示事件在从机中是关闭的。如果不指定这三个选择中的任意一个，则在一个事件创建之后，它立即变为活动的。
    COMMENT ‘comment’可选，用于定义事件的注释
    DO event_body必选，用于指定事件启动时所要执行的代码。可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。如果包含多条语句，可以使用BEGIN…END复合结构

一些常用的时间间隔设置：

（1）每隔5秒钟执行

ON SCHEDULE EVERY 5 SECOND

（2）每隔1分钟执行

ON SCHEDULE EVERY 1 MINUTE

（3）每天凌晨1点执行

ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)

（4）每个月的第一天凌晨1点执行

ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)

（5）每 3 个月，从现在起一周后开始

ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK

（6）每十二个小时，从现在起三十分钟后开始，并于现在起四个星期后结束

ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK
  • **示例1:**创建名称为event_user的事件，用于每隔5秒钟向数据表tb_user（用户信息表）中插入一条数据。

（1）首先创建tb_user（用户信息表）。

-- 创建用户信息表
CREATE TABLE IF NOT EXISTS tb_user
(id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
name VARCHAR(30) NOT NULL COMMENT '用户姓名',
create_time TIMESTAMP COMMENT '创建时间') COMMENT = '用户信息表';

（2）创建事件。

-- 创建事件
CREATE EVENT IF NOT EXISTS event_user
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
COMMENT '新增用户信息定时任务'
DO INSERT INTO tb_user(name,create_time) VALUES('pan_junbiao的博客',NOW());

执行结果：

在这里插入图片描述

  • **示例2：**创建一个事件，实现每个月的第一天凌晨1点统计一次已经注册的会员人数，并插入到统计表中。

（1）创建名称为p_total的存储过程，用于统计已经注册的会员人数，并插入到统计表tb_total中。

CREATE PROCEDURE p_total()
BEGIN
DECLARE n_total INT default 0;
SELECT COUNT(*) INTO n_total FROM db_database11.tb_user;
INSERT INTO tb_total (userNumber,createtime) VALUES(n_total,NOW());
END;

（2）创建名称为e_autoTotal的事件，用于在每个月的第一天凌晨1点调用存储过程。

CREATE EVENT IF NOT EXISTS e_autoTotal
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO CALL p_total();

# 视图

视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。

参考: MySQL视图

#真是存在的一张表
name    kemu    	 score  
------  ------ 	 	--------
张三      数学           100
李四      语文            60
李四      英语            80
张三      英语            50
张三      语文            70
李四      数学            80
王五      数学           100
王五      英语            20

#由于显示不能直观反映出学生各科目成绩 需要按一下方式显示

name    	语文  	数学  	英语  
-----		------  ------  --------
张三          70     100        50
李四          60      80        80
王五           0     100        20
  • 语法
# 创建
CREATE
    VIEW 'view_name'
    AS
(SELECT * FROM ...);

# 查询
select * from view_name;
  • 示例
# 创建视图
CREATE VIEW `qhg`.`student` AS
(SELECT
  NAME,
  MAX (
    CASE
      kemu
      WHEN '语文'
      THEN score
      ELSE 0
    END
  ) AS '语文',
  MAX (
    CASE
      kemu
      WHEN '数学'
      THEN score
      ELSE 0
    END
  ) AS '数学',
  MAX (
    CASE
      kemu
      WHEN '英语'
      THEN score
      ELSE 0
    END
  ) AS '英语'
FROM
  test
GROUP BY NAME);

# 查询
select * from student;

4.数据库设计

# 数据库设计的三大范式

转自: 关系型数据库设计：三大范式的通俗理解

1NF: 数据表的每一列都是不可分割的原子性数据项

例子:

学号姓名性别家庭信息学校信息
001张三男3人,北京大专,大二

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求

根据第一范式修改后 👇

学号姓名性别人口数户籍学历年纪
001张三男3北京大专大二

2NF:需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关 (主要针对联合主键而言)

例子:

订单号产品号数量单价订单金额下单时间
000120110525020201126
000120252025020201126
0001203100125020201126
000220620100202020201127
0002204120202020201127
00032052255020201125

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成;但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

根据第二范式修改后,拆分为两张表 👇

订单号产品号数量单价
0001201105
0001202520
00012031001
000220620100
0002204120
0003205225
订单号金额下单时间
000125020201126
0002202020201127
5020201125

3NF:确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

例子:

学号姓名性别班主任姓名班主任性别班主任年龄
001张三男陈老师男36
002李四男陈老师男36
003王五男陈老师男36
004赵六男李老师女28
005冯七男李老师女28
006马八男张老师男32

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是"班主任性别"和"班主任年龄"直接依赖的是"班主任姓名"，而不是主键"学号"

根据第三范式修改后,拆分为两张表 👇

学号姓名性别班主任Id
001张三男陈老师
002李四男陈老师
003王五男陈老师
004赵六男李老师
005冯七男李老师
006马八男3
班主任Id班主任姓名班主任性别班主任年龄
1陈老师男36
2李老师女28
3张老师男32

# MYSQL数据库设计规范与原则:

转自: MYSQL数据库设计规范与原则

在三大范式的基础上遵循以下规范👇

							MYSQL数据库设计规范
    1、数据库命名规范
        采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
        命名简洁明确(长度不能超过30个字符);
        例如：user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;
        除非是备份数据库可以加0-9的自然数：user_db_20151210;     
    2、数据库表名命名规范
        采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
        命名简洁明确,多个单词用下划线'_'分隔;
        例如：user_login, user_profile, user_detail, user_role, user_role_relation,
            user_role_right, user_role_right_relation
        表前缀'user_'可以有效的把相同关系的表显示在一起;         
    3、数据库表字段名命名规范
        采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
        命名简洁明确,多个单词用下划线'_'分隔;
        例如：user_login表字段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time;
        每个表中必须有自增主键,add_time(默认系统时间)
        表与表之间的相关联字段名称要求尽可能的相同;    
    4、数据库表字段类型规范
        用尽量少的存储空间来存数一个字段的数据;
        例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);
        IP地址最好使用int类型;
        固定长度的类型最好使用char,例如：邮编;
        能使用tinyint就不要使用smallint,int;
        最好给每个字段一个默认值,最好不能为null;    
    5、数据库表索引规范
        命名简洁明确,例如：user_login表user_name字段的索引应为user_name_index唯一索引;
        为每个表创建一个主键索引;
        为每个表创建合理的索引;
        建立复合索引请慎重;

原则:

						MYSQL数据库设计原则
    1、核心原则
        不在数据库做运算;
        cpu计算务必移至业务层;
        控制列数量(字段少而精,字段数建议在20以内);
        平衡范式与冗余(效率优先；往往牺牲范式)
        拒绝3B(拒绝大sql语句：big sql、拒绝大事物：big transaction、拒绝大批量：big batch);
    2、字段类原则
        用好数值类型(用合适的字段类型节约空间);
        字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);
        避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
        少用text类型(尽量使用varchar代替text字段);
    3、索引类原则
        合理使用索引(改善查询,减慢更新,索引一定不是越多越好);
        字符字段必须建前缀索引;
        不在索引做列运算;
        innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);
        不用外键(由程序保证约束);     
    4、sql类原则
        sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);
        简单的事务;
        避免使用trig/func(触发器、函数不用客户端程序取而代之);
        不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性);
        OR改写为IN(or的效率是n级别);
        OR改写为UNION(mysql的索引合并很弱智);
            select id from t where phone = ’159′ or name = ‘john’;
            =>
            select id from t where phone=’159′
            union
            select id from t where name=’jonh’
        避免负向%;
        慎用count(*);
        limit高效分页(limit越大，效率越低);
        使用union all替代union(union有去重开销);
        少用连接join;
        使用group by;
        请使用同类型比较;
        打散批量更新;       
    5、性能分析工具
        show profile;
        mysqlsla;
        mysqldumpslow;
        explain;
        show slow log;
        show processlist;

5.数据库优化

转自: 性能优化之MySQL 调优篇

浅谈MySQL中优化sql语句查询常用的30种方法

MySQL 调优/优化的 100 个建议

可以从几个方面进行优化:

  1. SQL 及 索引
  2. 数据表结构
  3. 系统配置
  4. 硬件

sql及索引

  • 避免导致全表扫描

    1. 查询时 应首先考虑在where 及 order by 设计的列上建立索引.
2. 应尽量避免 where 子句中使用!= < >操作符 , 否则会导致放弃索引而全表扫描.
3. 避免使用 null 值判断 
4. 避免使用 or 来连接条件, 可以使用 union all 
5. 避免使用like 
6. 慎用in 和 not in 对于连续的值 能用between and 就不用in 
7. 慎用count(*) 和 select * 
...

  • 正确建立索引

    • 不要过度使用索引，评估你的查询。
    • 保证索引简单，不要在同一列上加多个索引。

# 查找需要优化的sql

开启sql慢查询

  • 配置文件开启 (永久)
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log  
long_query_time = 1

重启服务 service mysqld restart

  • 数据库命令行开启 (临时的)
SET GLOBAL slow_query_log='ON'

SET GLOBAL slow_query_log_file='D:\\slow.log'; -- win 

SET GLOBAL slow_query_log_file='/usr/local/mysql/data/slow.log'; -- linux

SET GLOBAL long_query_time=1 ;   -- 单位s  为了测试用先设为 1s 

---
SHOW VARIABLES LIKE '%query%';

Variable_name                 Value        
----------------------------  -------------
binlog_rows_query_log_events  OFF          
ft_query_expansion_limit      20           
have_query_cache              YES          
long_query_time               10.000     
query_alloc_block_size        8192         
query_cache_limit             1048576      
query_cache_min_res_unit      4096         
query_cache_size              1048576      
query_cache_type              OFF          
query_cache_wlock_invalidate  OFF          
query_prealloc_size           8192         
slow_query_log                ON           
slow_query_log_file           D:\slow.log  
-- 此时long_query_time 依旧为10s 须关闭连接重新打开即可修改过来

SELECT SLEEP(2);
看D:\slow.log  文件

# Time: 2020-11-27T07:18:39.172146Z
# User@Host: root[root] @ localhost [::1]  Id:    24
# Query_time: 0.001706  Lock_time: 0.000182 Rows_sent: 13  Rows_examined: 1034
SET timestamp=1606461519;
SHOW VARIABLES LIKE '%query%';

# Time: 2020-11-27T07:21:49.034781Z
# User@Host: root[root] @ localhost [::1]  Id:    24
# Query_time: 2.008180  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1606461709;
SELECT SLEEP(2);

# 已经查出执行慢的sql 如何优化?

查看sql执行计划

转自: Mysql(一)–EXPLAIN的参数解析及简单应用

MySQL — 优化之explain执行计划详解（转）

EXPLAIN命令是查看查询优化器如何决定执行查询的主要方法。

  • 语法:
EXPLAIN select ....
  • 各个参数表示什么?

详见以上两个博客

重点：

type：访问类型，查看SQL到底是以何种类型访问数据的。

key：使用的索引，MySQL用了哪个索引，有时候MySQL用的索引不是最好的，需要force index()。

rows：最大扫描的列数。

extra：重要的额外信息，特别注意损耗性能的两个情况，using filesort和using temporary。

6.锁了解多少? 更详细请看下面正主博客讲的很清楚

转自: Mysql锁机制–乐观锁 & 悲观锁

Mysql–开篇&目录

乐观锁

  • 理解方式一（来自网上其它小伙伴的博客）

乐观锁认为一般情况下数据不会造成冲突，所以在数据进行提交更新时才会对数据的冲突与否进行检测。如果没有冲突那就OK；如果出现冲突了，则返回错误信息并让用户决定如何去做。

  • 理解方式二（来自网上其它小伙伴的博客）

乐观锁的特点是先进行业务操作，不到万不得已不会去拿锁。乐观地认为拿锁多半会是成功的，因此在完成业务操作需要实际更新数据的最后一步再去拿一下锁。

  • 理解方式三
    • 就是 CAS 操作
    • 类似于 SVN、GIt 这些版本管理系统，当修改了某个文件需要提交的时候，它会检查文件的当前版本是否与服务器上的一致，如果一致那就可以直接提交，如果不一致，那就必须先更新服务器上的最新代码然后再提交（也就是先将这个文件的版本更新成和服务器一样的版本）
乐观锁如何实现?

首先说明一点的是：乐观锁在数据库上的实现完全是逻辑的，数据库本身不提供支持，而是需要开发者自己来实现。

常见的做法有两种：版本号控制及时间戳控制。

版本号控制的原理：

  • 为表中加一个 version 字段；
  • 当读取数据时，连同这个 version 字段一起读出；
  • 数据每更新一次就将此值加一；
  • 当提交更新时，判断数据库表中对应记录的当前版本号是否与之前取出来的版本号一致，如果一致则可以直接更新，如果不一致则表示是过期数据需要重试或者做其它操作（PS：这完完全全就是 CAS 的实现逻辑呀~）

至于时间戳控制，其原理和版本号控制差不多，也是在表中添加一个 timestamp 的时间戳字段，然后提交更新时判断数据库中对应记录的当前时间戳是否与之前取出来的时间戳一致，一致就更新，不一致就重试。

悲观锁

select … for update

悲观锁，正如其名，它指的是对数据被外界（包括当前系统的其它事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排它性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

还可以简单理解，就是Java中的 Synchronized 关键字。只要对代码加了 Synchronized 关键字，JVM 底层就能保证其线程安全性。

7.mysql字符集 utf8 和 utf8mb4 区别?

转自: mysql字符集 utf8 和utf8mb4 的区别

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。
可以简单的理解 utf8mb4 是目前最大的一个字符编码,支持任意文字。

为什么mysql有utf8和utf8mb4两种几乎差不多的字符集?

utf8 是 Mysql 中的一种字符集，只支持最长三个字节的 UTF-8字符，也就是 Unicode 中的基本多文本平面。
Mysql 中的 utf8 为什么只支持持最长三个字节的 UTF-8字符呢？我想了一下，可能是因为 Mysql 刚开始开发那会，Unicode 还没有辅助平面这一说呢。那时候，Unicode 委员会还做着 “65535 个字符足够全世界用了”的美梦。Mysql 中的字符串长度算的是字符数而非字节数，对于 CHAR 数据类型来说，需要为字符串保留足够的长。当使用 utf8 字符集时，需要保留的长度就是 utf8 最长字符长度乘以字符串长度，所以这里理所当然的限制了 utf8 最大长度为 3，比如 CHAR(100) Mysql 会保留 300字节长度。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持，我想一个是为了向后兼容性的考虑，还有就是基本多文种平面之外的字符确实很少用到。

要在 Mysql 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持。我觉得，为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。

为什么要使用utf8mb4字符集?

既然utf8应付日常使用完全没有问题，那为什么还要使用utf8mb4呢? 低版本的MySQL支持的utf8编码，最大字符长度为 3 字节，如果遇到 4 字节的字符就会出现错误了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xFFFF，也就是 Unicode 中的基本多文平面（BMP）。也就是说，任何不在基本多文平面的 Unicode字符，都无法使用MySQL原有的 utf8 字符集存储。这些不在BMP中的字符包括哪些呢？最常见的就是Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和一些不常用的汉字，以及任何新增的 Unicode 字符等等。
那么utf8mb4比utf8多了什么的呢?
多了emoji编码支持.
如果实际用途上来看,可以给要用到emoji的库或者说表,设置utf8mb4.
比如评论要支持emoji可以用到。

新建mysql库的排序规则?

utf8_unicode_ci比较准确，utf8_general_ci速度比较快。通常情况下 utf8_general_ci的准确性就够我们用的了，在我看过很多程序源码后，发现它们大多数也用的是utf8_general_ci，所以新建数据 库时一般选用utf8_general_ci就可以了
如果是utf8mb4那么对应的就是 utf8mb4_general_ci utf8mb4_unicode_ci

一个假的猿
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
MYSQL常见面试问题和答案
11-01
MySQL是世界上最受欢迎的关系型数据库...同时，提供的PDF文件如“MySQL性能优化的21个最佳实践”、“MySQL面试专题及答案”等，都是深入学习和复习的好资源。不断实践和应用这些知识，才能在MySQL的世界中游刃有余。
SQL必知必会第十二节挑战题
weixin_54215671的博客
10-21 2595
1．编写SQL语句，返回Customers表中的顾客名称（cust_name）和Orders表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行排序。 （1）等值联结 select cust_name,order_num from customers,orders where customers.cust_id=orders.cust_id order by order_num; （2）使用inner join select cust_name,order_num from
MYSQL常见面试题汇总
一步一个脚印，踏实努力，向着目标前进
07-28 3219
INNER JOIN只返回两个表中匹配的行，而LEFT JOIN返回左表中的所有行和匹配的右表中的行。如何使用INNER JOIN和LEFT JOIN区别连接多个表？其中，offset表示偏移量，count表示每页的数量。如何使用UNION操作合并多个查询结果？
MySQL 触发器简单实例
limeOracle的博客
02-23 93
~~语法~~ CREATETRIGGER<触发器名称> --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.{BEFORE|AFTER} --触发器有执行的时间设置：可以设置为事件发生前或后。{INSERT|UPDATE|DELETE} --同样也能设定触发的事件：它们可以在执行insert、...
MySQL面试题大全，MySQL必刷的那些面试题（2024版）
最新发布
2401_84046677的博客
04-04 993
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
MySQL 面试中常见的问题有哪些？
Trouvailless的博客
09-29 857
候选人：MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。／／可以命中索引// 可以命中索引// 无法命中索引复制代码候选人：这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
MySQL常见面试问题汇总
11-08
MySQL常见面试问题汇总
MySQL常用面试问题
02-17
MySQL常用面试问题
mysql问题，常见的mysql面试问题
01-27
### MySQL常见面试问题详解 #### 1. MySQL简介与特性 ...以上内容涵盖了MySQL面试中常见的几个问题及其解答思路。希望这些知识点能够帮助你更好地理解和掌握MySQL的相关知识，并在面试中表现出色。
mysql常见面试问题以及详解
02-15
MySQL是世界上最流行的关系型数据库管理系统之一，经常在面试中被问到的问题涵盖了多个核心概念，如锁、索引、版本控制、事务和存储引擎。在这篇文章中，我们将深入探讨存储过程和索引这两个主题。 首先，存储过程...
MySQL 精选 60 道面试题（含答案）
热门推荐
hahazz233的博客
06-20 13万+
基础关系型数据库的优点非关系型数据库（NOSQL）的优点Server 层按顺序执行 SQL 的步骤为：根本原因扩展三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。根据叶子节点的内容，索引类型分为主键索引和非主键索引。两个考虑因素：为什么选择 B+ 树：覆盖索引：索引下推：- 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加
常见（MySQL）面试题（含答案）
m0_63592370的博客
07-28 3万+
存储过程是一个预编译的SQL语句，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。可以供外部程序调用，比如java程序存储过程是一个预编译的SQL语句，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。可以供外部程序调用，比如java程序。...
来来来！java字符串数组输入赋值
m0_56169789的博客
07-23 853
正文 下文中截图来源于朋友一个pdf版本的面经，把所以知识点的答案整理了下来，耗费他至少1个月时间，在本文最后部分把这个pdf分享给大家，觉得有用的麻烦点赞关注走一波，谢谢！！！面经中有他的知识点的答案，如下图示例，非常详细（文末有领取方式）！！！ 1.数据结构与算法篇 你是java开发你就用java代码去实现 书籍参考： （0）《图解算法》入门（1）《剑指offer》 剑指Offer_编程题_牛客网（2）《程序员代码面试指南 IT名企算法与数据结构题目最优解》参考左神视频（3）leetcode Lee
SQL必知必会习题练习
Momoko_Hanyu的博客
09-10 6296
《SQL必知必会》习题练习答案第二课 检索数据第三课 排序检索数据 使用的都是MySQL语句，自己留存做笔记用的，不一定对（不过自己测试没问题才会粘过来_(:з」∠)_） 第二课 检索数据 1．编写SQL语句，从Customers表中检索所有的ID（cust_id） SELECT cust_id FROM `customers`; 2.OrderItems表包含了所有已订购的产品（有些已被订购多次）。编写SQL语句，检索并列出已订购产品（prod_id）的清单（不用列每个订单，只列出不同产品的清单）。提示
转 - 数据库面试题目研究
weixin_34174422的博客
08-04 902
一:SQL tuning 类 1.      列举几种表连接方式 Answer：等连接（内连接）、非等连接、自连接、外连接（左、右、全） Or hash join/merge join/nest loop(cluster join)/index join ？？ ORACLE 8i，9i 表连接方法。   一般的相等连接： select * from a, b where a.id ...
MySQL常见面试题汇总（建议收藏！！！）
蔡政洁的博客
08-12 5万+
目录一、索引相关（1）什么是索引?（2）索引是个什么样的数据结构呢?（3）为什么使用索引？（4）Innodb为什么要用自增id作为主键？（5）Hash 索引和 B+ 树索引有什么区别或者说优劣呢?（6）什么是聚簇索引（6）非聚簇索引一定会回表查询吗?（6）聚簇索引和非聚簇索引的区别（6）MyISAM和InnoDB实现B+树索引方式的区别是什么？（6）MySQL中有四种索引类型，可以简单说说吗？（6）覆盖索引是什么？（6）联合索引是什么?为什么需要注意联合索引中的顺序?（6）创建的索引有没有被使用到?或者说怎
MySQL 常见面试题汇总
时光钟摆
10-14 12万+
一、存储引擎 MySQL常见的两种存储引擎：MyISAM与InnoDB 二、字符集及校对规则 字符集指的是一种从二进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。Mysql中每一种字符集都会对应一系列的校对规则。 Mysql采用的是类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定...
【数据库】2、索引、调优、explain（尚硅谷笔记）
努力充实，远方可期
04-26 1332
尚硅谷JAVA研究院 版本v1.1 第 1 章 MySQL 简介 1.什么是 Mysql MySQL 是一个关系型数据库管理系统， 由瑞典 MySQL AB 公司开发， 目前属于 Oracle 公司。 Mysql 是开源的， 可以定制的， 采用了 GPL 协议， 你可以修改源码来开发自己的 Mysql 系统。 MySQL 使用标准的 SQL 数据语言形式。 Mysql 可以允许于多个系统上， 并...
数据库时间类型查询未使用索引
weixin_44974020的博客
10-22 824
数据库时间类型查询未使用索引 问题 : 由于业务需求,目前需要在数据库中根据时间查询数据,数据库内字段类型为datetime; 在sql中采用的是 date_sub函数获取时间 —> date_sub(now(),interval 30 day) ; date_sub(now(),interval 30 day) 获取的时间是varchar类型,无法使用到索引,由于数据量较大,故而影响效率较为严重. 解决 : 原因寻找,类型不同而无法使用到索引,想办法在sql内将varchar转datetim
MySQL数据库面试热点问题解析
在MySQL数据库面试中，经常会遇到各种各样的问题，涉及到数据操作、查询优化和高级特性。以下是一些常见的面试题及解答，旨在帮助理解数据库的核心概念。 1. **多字段排序**： - 题目要求根据部门号(deptid)从高到...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值