MySQL面试热点与MySQL高级特性,性能优化
一、MySQL基础
1.1范式
-
三范式
- 一范式：原子性，每列不可再拆分
- 二范式：不产生局部依赖，每列完全依赖于主键，一张表只描述一件事情
- 三范式：不产生传递依赖，所有的列都直接依赖于主键，使用外键关联，外键都来源于其他表的主键
-
反三范式
- 反3NF：为了提高数据库的性能，增加冗余字段，以便提高查询性能
1.2select语句执行顺序
-
编写顺序
- select distinct 查询字段
- from 表名
- join 表名
- on 连接条件
- where 查询条件
- group by 分组字段
- having 分组后条件
- order by 排序条件
- limit 查询起始位置，查询条数
-
编写顺序和执行顺序图
- [图片上传失败...(image-536c7a-1566564358945)]
1.3内连接
1.4外连接
1.5全连接
1.6级联操作
二、存储引擎
2.1查看存储引擎
- 查看所有存储引擎：
show engines; - 查看当前存储引擎：
show variables like '%storage_engine%';
2.2修改存储引擎
- 修改存在表的存储引擎：alter table test1 ENGINE = innoDB ;show create table test1
2.3MyISA与InnoDB区别
- 主外键
- MyISAM : 不支持
- InnoDB: 支持
- 事务
- MyISAM:不支持
- InnoDB:支持
- 行表锁
- MyISAM 表锁 操作一条记录也会锁住整个表 不适合高并发的操作
- InnoDB 行锁 操作时,只锁某一行,不对其它行有影响 适合高并发的操作
- 缓存
- MyISAM : 只缓存索引,不缓存数据
- InnoDB:不仅缓存索引,还要缓存真实数据,对内存要求比较高,而且内存大小对性能有决定性的影响
- 关注点
- MyISAM性能
- InnoDB:事务
- 存储引擎选择
- [图片上传失败...(image-36bf5-1566564358945)]
三、存储过程、函数、触发器
3.1存储过程、函数介绍
存储过程和函数区别：存储过程没有返回值，而函数必须有；存储过程的参数可以使用IN，OUT，INOUT类型，而函数的参数只能是IN类型。
-
存储过程特点
- 能完成较复杂的判断和运算，而且处理逻辑都封装在数据库端，调用者不需要自己处理业务逻辑，一旦逻辑发生变化，只需要修改存储过程即可，而对调用者程序完全没有影响。
- 可编程性强，灵活
- SQL编程的代码可重复使用
- 执行速度相对快一些
- 减少网络之间数据传输，节省开销
3.2创建存储过程
-- 创建存储过程 DELIMITER $$ CREATE PROCEDURE testa() BEGIN SELECT * FROM student WHERE id=2; END $$ -- 调用存储过程 call testa();
3.3存储过程/函数的查、删
-
查
-- 查看存储过程或者函数的状态 SHOW PROCEDURE STATUS LIKE 'testa'; -- 查看存储过程或者函数的定义 SHOW CREATE PROCEDURE testa -
删
-- 删除存储过程 DROP PROCEDURE testa1; -- 删除函数 DROP FUNCTION testa1;
3.4存储过程的变量
-
需求： 编写存储过程，使用变量取id=2的用户名;变量my_uname
DELIMITER $$ CREATE PROCEDURE testa3() BEGIN -- 1.变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用 -- 2.变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能指定默认值、字符集和排序规则等 DECLARE my_uname VARCHAR(32) DEFAULT ''; -- 3.变量可以通过set来赋值，也可以通过select into的方式赋值 SET my_uname='itheima'; SELECT NAME INTO my_uname FROM student WHERE id=2; -- 4.变量需要返回，可以使用select语句,如：select 变量名 SELECT my_uname; END $$ CALL testa3(); -
变量作用域
- 变量是有作用域的，作用范围在begin与end块之间，end结束变量的作用范围即结束。
- .需要多个块之间传递值，可以使用全局变量，即放在所有代码块之前。
- 传参变量是全局，可以在多个块之间起作用
3.5存储过程传入参数 IN型
-
例
-- 需求：编写存储过程，传入id，返回该用户的name DELIMITER $$ CREATE PROCEDURE getName(my_uid INT) BEGIN DECLARE my_uname VARCHAR(32) DEFAULT ''; SELECT NAME INTO my_uname FROM student WHERE id=my_uid; SELECT my_uname; END; $$ CALL getName(2);- 传入参数：类型为IN,表示该参数的值必须在调用存储过程时指定，如果不显式指定为IN，那么默认就是IN类型。
- IN类型参数一般只用于传入，在调用存储过程中一般不作修改和返回
- 如果调用存储过程中需要修改和返回值，可以使用OUT类型参数
3.6存储过程传出参数OUT型
-
例
-- 需求：调用存储过程时，传入uid返回该用户的uname DELIMITER $$ CREATE PROCEDURE getName22(IN my_uid INT,OUT my_uname VARCHAR(32)) BEGIN SELECT NAME INTO my_uname FROM student WHERE id=my_uid; SELECT my_uname; END; $$ -- 指定传入参数变量 SET @uname:=''; CALL getName22(2,@uname); -- 起别名 SELECT @uname AS myName;- 传出参数：在调用存储过程中，可以改变其值，并可返回
- OUT是传出参数，不能用于传入参数值
- 调用存储过程时，OUT参数也需要指定，但必须是变量，不能是常量
- 如果既需要传入，同时又需要传出，则可以使用INOUT类型参数
3.7存储过程可变参数INOUT型
-
例：
-- 需求：调用存储过程时，参数my_uid和my_uname,既是传入，也是传出参数 DELIMITER $$ CREATE PROCEDURE getName3(INOUT my_uid INT,INOUT my_uname VARCHAR(32)) BEGIN SET my_uid=2; SET my_uname='hxf3'; SELECT id,NAME INTO my_uid,my_uname FROM student WHERE id=my_uid; SELECT my_uid,my_uname; END; $$ SET @uname:=''; SET @uid:=0; CALL getName3(@uid,@uname); SELECT @uname AS myName;- 可变变量INOUT：调用时可传入值，在调用过程中，可修改其值，同时也可返回值。
- INOUT调用时传入的是变量，而不是常量
3.8存储过程条件语句
-
例：
-- 需求：编写存储过程，如果用户uid是偶数则就给出uname,其它情况只返回uid DELIMITER $$ CREATE PROCEDURE getName44(IN my_uid INT ) BEGIN DECLARE my_uname VARCHAR(32) DEFAULT ''; IF(my_uid%2=0) THEN SELECT NAME INTO my_uname FROM student WHERE id=my_uid; SELECT my_uname; ELSE SELECT my_uid; END IF; END; $$ CALL getName44(1); CALL getName44(2);- 条件语句最基本的结构：if() then …else …end if;
3.9存储过程循环语句
-
while循环
-
例：
-- 需求：使用循环语句，向表users(uid)中插入10条uid连续的记录。 DELIMITER $$ CREATE PROCEDURE insertdata() BEGIN DECLARE i INT DEFAULT 0; WHILE(i< 10) DO BEGIN SELECT i; SET i=i+1; INSERT INTO users(NAME , address) VALUES("孙悟空" , "广州"); END ; END WHILE; END; $$ CALL insertdata();- while语句最基本的结构：while() do…end while;
- while判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式
-
-
repeat循环语句
-
例：
-- 需求：使用repeat循环向表users插入10条uid连续的记录 DELIMITER $$ CREATE PROCEDURE insertdata2() BEGIN DECLARE i INT DEFAULT 100; REPEAT BEGIN SELECT i; SET i=i+1; INSERT INTO users(NAME) VALUES('黑马'); END ; UNTIL i >= 110 END REPEAT; END; $$ CALL insertdata3();- repeat语句最基本的结构：repeat…until …end REPEAT;
- until判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式只有当until语句为真时，循环结束。
-
3.10光标（游标）基本使用
在存储过程和函数中，可以使用光标（有时也称为游标）对结果集进行循环的处理
-
基本使用
- 申明光标：cursor for
- 打开光标：open
- 移动光标：fetch
- 关闭光标：close
-
例：
-- 编写存储过程，使用光标，把id为偶数的记录逐一更新用户名。 DELIMITER $$ CREATE PROCEDURE testcursor() BEGIN -- 控制光标循环结束标记 DECLARE stopflag INT DEFAULT 0; DECLARE my_uname VARCHAR(20); -- cursor for 申明光标 DECLARE uname_cur CURSOR FOR SELECT NAME FROM student WHERE id%2=0 ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; OPEN uname_cur; -- 打开游标 FETCH uname_cur INTO my_uname; -- 游标向前走一步，取出一条记录放到变量my_uname中。 WHILE( stopflag=0 ) DO -- 如果游标还没有到结尾，就继续 BEGIN UPDATE student SET NAME=CONCAT(my_uname,'_cur') WHERE NAME=my_uname; -- 游标向前走一步，取出一条记录放到变量my_uname中。 FETCH uname_cur INTO my_uname; END ; END WHILE; CLOSE uname_cur; END; $$ DELIMITER ; -- 调用 CALL testcursor()- 注意:变量，条件，处理程序，光标，都是通过DECLARE定义的，它们之间是有先后顺序要求的，变量和条件必须在最前面声明，然后才能是光标的申明，最后才可以是处理程序的申明。
3.11函数的定义
-
例：
-- 需求：编写函数，传入一个用户uid，返回用户的name DELIMITER $$ CREATE FUNCTION getFName1(my_uid INT) RETURNS VARCHAR(32) READS SQL DATA # READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。 BEGIN DECLARE my_uname VARCHAR(32) DEFAULT ''; SELECT NAME INTO my_uname FROM student WHERE id=my_uid; RETURN my_uname; END; $$ SELECT getFName1(3);- 创建函数使用create FUNCTION 函数名（参数 ） RETURNS 返回类型
- 函数体放在begin和end之间
- Return指定函数的返回值
- 函数调用:SELECT getuname()
3.12触发器
定义：触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合、触发器这种特性可以协助应用在数据库端确定数据的完整性。
-
例：
-- 出于审计目的，当有人往表users插入一条记录时，把插入的uid,uname,插入动作和操作时间记录下来。 create table oplog( id int primary key auto_increment, uid int, name varchar(32), action varchar(10), optime date ) -- 触发器 DELIMITER $$ CREATE TRIGGER tr_users_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO oplog(uid,name,ACTION,optime) VALUES(NEW.id,NEW.name,'insert',NOW()); END; $$ -- 插入数据测试 insert into users (NAME, address) values ("李泽源", "深圳")- 创建触发器使用create TRIGGER 触发器名
- 什么时候触发？After INSERT ON users,除了after还有before,是在对表操作之前(BEFORE)或者之后(AFTER)触发动作的。
- 对什么操作事件触发？after INSERT ON users ，操作事件包括insert,UPDATE,DELETE
- 对什么表触发？after INSERT ON users
- 影响的范围？For EACH ROW
3.13事件调度器
定义：事件调度器是MySQL中提供的可做定时操作处理，或者周期操作处理的一个对象。
查看是否开启事件调度支持: show variables like '%event_scheduler%';
开启事件调度支持：set global event_scheduler =on;
-
例：
DELIMITER $$ -- 创建事件调度器 CREATE EVENT IF NOT EXISTS event_hello -- 事件调度计划 ON SCHEDULE EVERY 3 SECOND -- 调度计划执行完成之后是否还保留 ON COMPLETION PRESERVE -- 执行事件调度 DO BEGIN INSERT INTO users(NAME , address) VALUES('王五','广州'); END$$ DELIMITER ; -
常用事件调度计划
- On schedule at ‘2016-12-12 04:00:00’ 在 2016-12-12 04:00:00执行一次
- on schedule every 1 scond 每秒执行一次
- on schedule every 1 day starts ‘2016-12-12 20:20:20’ 每天在20：20：20执行一次
- on schedule every 1 minute starts ‘2016-12-12 9:00:00’ ends ‘2016-12-12 11:00:00’
四、锁
4.1锁分类
- 按操作分
- 读锁（共享锁）
- 写锁（排他锁）
- 按粒度分
- 表锁
- 行锁
- 页锁
4.2表锁
- 偏向MyISAM存储引擎,开销小, 加锁快, 无死锁,锁定粒度大, 发生锁冲突的概率最高, 并发最底,整张表就只能一个人使用
- 查看表锁
show open tables; - 对表加锁
lock table user read, user write; - 对表解锁
unlock tables - 查询表锁查询、等待次数
show status like 'table%';- Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数
- Table_locks_waited:出现表级锁定争用而发生等待的次数
4.3行锁
- 偏向InnoDB存储引擎,开销大, 加锁慢, 会出现死锁;锁定粒度最小, 发生锁冲突的概率最底,并发度也最高。
4.4事务
- 事务是一批操作，要么同时成功，要么同时失败!
-
start transaction开启事务 -
commit提交事务、 -
rollback回滚事务 -
savepoint 名字设置回滚点 -
rollback to 名字回到回滚点
-
- 取消自动提交
SHOW VARIABLES LIKE '%commit%';SELECT @@autocommit;SET autocommit = 0;
- 事务四大特性(ACID)
- 原子性
- 一致性
- 隔离性
- 持久性
- 四种隔离级别和可能出现的问题
- 隔离级别
- 读未提交(read uncommitted)：所有的事务都可以读到其他事务未提交的执行结果。容易出现脏读。
- 读已提交(read committed)：一个事务只能读到已经提交事务的执行结果。容易出现不可重复读(虚读)。
- 可重复读(repeatable read)：MySQL默认。一个事务前后几次，会得到同样的结果。容易出现幻读。
- 可串行化(serializable)：最高隔离级别。每个读操作上共享锁，写操作上排他锁，容易出现超时，阻塞现象。
- 脏读、不可重复读(虚读)、幻读
- 脏读：一个事务读到其他事务未提交的执行结果。
- 不可重复读（虚读）：同一个事务前后相同的查询语句所读取的结果不同
- 幻读：幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
- 隔离级别
- 手动加行级锁
- 在查询之后添加
for update，其它操作会被阻塞,直到锁定的行提交commit; -
show status like 'innodb_row_lock%';查看行锁的使用信息
- 在查询之后添加
4.5悲观锁、乐观锁
-
悲观锁
- 定义：就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改。整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制。事实上关系数据库中的行锁，表锁不论是读写锁都是悲观锁。
-
乐观锁
定义：顾名思义，就是很乐观，每次自己操作数据的时候认为没有人回来修改它，所以不去加锁。但是在更新的时候会去判断在此期间数据有没有被修改。需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。
-
实现方式
-
版本号
就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段每次更新把这个字段加1
读取数据的时候把version读出来，更新的时候比较version
如果还是开始读取的version就可以更新了
-
如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到
一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。
-
时间戳
- 原理和版本号相同，只是标识的字段不一样。
-
-
悲观锁,乐观锁使用前提
- 对于读操作远多于写操作的时候，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁
- 如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。
五、数据库优化
5.1MySQL优化综合性技术
- 表的设计合理化（符合3NF，有时也要进行反三范式操作）
- 添加适当索引
- 分表技术（水平分割、垂直分割）
- 主从复制，读写分离
- 存储过程（模块化编程，可以提高速度）
- 对MySQL配置优化（配置最大并发数my.ini，调整缓存大小）
- 系统应用优化等
- 服务器的硬件优化
5.2索引分类
- 单值索引
- 一个索引只包含单个列,一个表可以有多个单值索引,一般来说, 一个表建立索引不要超过5个
- 唯一索引
- 索引列的值必须唯一,但允许有空值
- 复合索引
- 一个索引包含多个列
- 全文索引
- MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。
5.3MySQL语句正确使用索引
- 全值匹配（最好）
- 如：建立了三个索引，查询时最好都使用上三个索引
- 最佳左前缀法则
- 如果索引有多列,要遵守最左前缀法则,指的就是从索引的最左列开始 并且不跳过索引中的列
- 计算、函数、类型转换会导致索引失效
- 范围条件查询，右边索引失效
- 使用不等于(!=或者<>)时无法使用索引
- or引起索引失效
- like引起索引失效
5.4大批量数据分页操作优化
-
没有优化的查询
- SELECT * FROM logs1 LIMIT 500000,10;
-
优化
-
使用子查询优化方式1
select * from logs1 e inner join (SELECT id from logs1 limit 500000 ,10 ) et on e.id = et.id -
使用子查询优化方式2
select * from logs1 where id >=(SELECT id from logs1 limit 500000 , 1) limit 10 -
使用id限定优化
- 记录上一页最大的id号 使用范围查询,限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利
-
