MySQL基础与MySQL高级特性,性能优化

推荐原创

Seattle小小瓜 2023-03-03 22:52:08 博主文章分类：分类 ©著作权

文章标签 存储过程数据 MySQL 文章分类 MySQL 数据库 聊一聊WuKong编辑器

©著作权归作者所有：来自51CTO博客作者Seattle小小瓜的原创作品，请联系作者获取转载授权，否则将追究法律责任

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存储过程条件语句

例：

MySQL基础与MySQL高级特性,性能优化_存储过程

-- 需求：编写存储过程，如果用户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字段，这为分页查询带来很多便利

上一篇：「MySQL高级篇」MySQL索引原理，设计原则

下一篇：SQL笔记--MySQL高级操作

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯