一、存储引擎
1、在创建表时,指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ]
2.查询当前数据库支持的存储引擎
show engines
3.存储引擎特点
InnoDB:
- 介绍:兼顾高可靠性和高性能的通用存储引擎,在MYSQL5.5之后,InnoDB是默认是MYSQL存储引擎
-
特点
1)DML操作遵循ACID模型,支持事务
2)行级锁,提高并发访问性能;
3)支持外键FOREIGN KEY约束,保证数据的完整性和正确性
- 文件
MyISAM
- 介绍:MyISAM是MySQL早期的默认存储引擎
-
1)不支持事务,不支持外键2)支持表锁,不支持行锁3)访问速度快
-
文件
-
1)xxx.sdi存储表结构信息2)xxx.MYD存储数据3)xxx.MYI存储索引
Memory
- 介绍:Memory引擎的表数据时存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为 临时表或缓存使用。
- 特点 1)内存存放,hash索引
- 文件:xxx.sdi:存储表结构信息
存储引擎选择:
对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
二、索引--一种数据结构,高效获取数据
1、优缺点:
2.索引结构
1)B树和B+树
- B-树(多路平衡查找树)
- B+:
- 与B树区别:1)所有节点都会出现在叶子节点,非叶子节点仅仅是索引的作用 2) 叶子节点形成双向链表
- mysql优化+树:增加了一个指向相邻叶子结点的链表指针,就形成了带有顺序指针的B+树
2)Hash
利用一定的哈希算法,将键值换算成新的hash值,映射到对应的hash值,然后存储在hash表中
hash冲突可采用链表解决
特点:
- hash只支持等值匹配,不支持范围查询
- 无法利用索引完成排序(因为无序)
- 查询效率高,不出现hash碰撞时,通常只需要一次检索就可以了,效率通常高于B+tree索引
存储引擎支持:
- 为什么innodb存储引擎选择使用B+树索引结构?
1)顺序搜索时二叉树查询效率很低,相对于二叉树,层级更少,搜索效率高
2)对于B树,无论是叶子结点还是非叶子节点,都会保存数据,这样会导致一页存储的键值更少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低,而对于B+树,保存同样数据,层级更少,
3)而对于hash索引只支持等值匹配,不支持范围匹配。
3.索引分类
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一索引作为聚集索引
- 如果无主键,或没有合适的唯一索引,则innodb会自动生成一个rowid作为隐藏的聚集索引
4.索引语法
- 创建索引
-- create [unique|fulltext] index index_name on table_name(index_col_name...);
create index idx_user_name on tb_user(name) ;
创建联合索引
create unique index idx_user_phone on tb_user(phone) ;
- 查看索引
-- show index from table_name;
- 删除索引
-- drop index index_name on table_name;
5.SQL性能分析:
- SQL执行频率:增删改查在数据库中所占的比例,show[session|global] status,如果是select也就是查询频率高的话,就要优化了。
show global status like 'com_______' ;
- 慢查询日志
-
慢查询日志记录了所有执行时间超过指定参数( long_query_time ,单位:秒,默认 10 秒)的所有SQL 语句的日志。MySQL 的慢查询日志默认没有开启,我们可以查看一下系统变量 slow_query_log。
show variables like 'slow_query_log';
- profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling
SELECT @@have_profiling ;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
- explain执行计划
explain select *from tb_user where id=1;
explain执行计划各个字段含义:
id:sql语句当中表的执行顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)
type:表示连接类型,性能由好到差依次是null、system、const、eq_ref、ref、range、index、all。
possible_key:可能用到的索引
key:实际用到的索引
key_len:表示索引中使用的字节数,该值为索引字段的最大可能长度,并非实际使用长度,在不损失精度的条件下,长度越小越好
rows:mysql认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值
filtered:表示返回结果的行数占需读取行数的百分比, filtered 的值越大越好。
6、索引使用
- 最左前缀法则:
- 范围查询
联合索引中,出现范围查询(>,<)范围查询右侧的列索引失效,所以尽量用>=
- 索引失效一
sql提示
explain select *
from tb_user use index (idx_user_pro)
where profession='软件工程';
2). ignore index : 忽略指定的索引。
explain select *
from tb_user ignore index (idx_user_pro_age_sta)
where profession='软件工程';
3). force index : 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工
程';
覆盖索引:
前缀索引
create index idx_email_5 on tb_user(email(5));
单列索引和联合索引:
索引设计原则:
1).针对于数据量大,查询频繁的表建立索引
2)针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索
6)要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
7)如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
7、SQL优化
- insert优化:1)批量插入 2)手动提交事务3)主键顺序插入
如果一次性需要插入大批量数据(比如: 几百万的记录),使用insert语句插入性能较低,此时可以使
-- 客户端连接服务端时,加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;
- 主键优化
- order by 优化
-
Using filesort : 通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区 sortbuffer 中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。Using index : 通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index ,不需要额外排序,操作效率高。对于以上的两种排序方式, Using index 的性能高,而 Using filesort 的性能低,我们在优化排序操作时,尽量要优化为 Using index 。
-
A. 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。B. 尽量使用覆盖索引。C. 多字段排序 , 一个升序一个降序,此时需要注意联合索引在创建时的规则( ASC/DESC )。D. 如果不可避免的出现 filesort ,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size( 默认 256k)
1)在分组操作时可以通过索引来提高效率
2)分组操作时,索引的使用也是满足最左前缀法则
- group by 优化:
-
limit优化
- 例:limit 2000000,10 查询代价大:
- 通过覆盖索引和子查询来实现:
select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
- count优化
-
MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高; 但是如果是带条件的 count , MyISAM 也慢。InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。如果说要大幅度提升 InnoDB 表的 count 效率,主要的优化思路:自己计数 ( 可以借助于 redis 这样的数据库进行, 但是如果是带条件的 count 又比较麻烦了 ) 。
-
count几种用法:count(*)、count(主键)、count(字段)、count(数字)
- update优化 (避免行锁升级为表锁)
- 一定要根据索引字段进行更新,
否则,InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。
7.视图/存储过程/触发器
-
视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的。 即视图只保存了查询的SQL逻辑,不保存查询结果。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。
-
创建视图:
-
-- 创建视图 create [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded|local] check option] create or replace view stu_v_1 as select id,name from student where id<=10;
- 查询视图
-- 查看创建视图语句:SHOW CREATE VIEW 视图名称;
-- 查看视图数据:SELECT * FROM 视图名称 ...... ;
select *from stu_v_1;
- 修改视图:
-- 方式一:CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH[ CASCADED | LOCAL ] CHECK OPTION ]
-- 方式二:ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |LOCAL ] CHECK OPTION ]
create or replace view stu_v_1 as select id,name,no from student where id<=10;
alter view stu_v_1 as select id,name from student where id<=10;
- 删除视图
-- DROP VIEW [IF EXISTS] 视图名称 [,视图名称] .
drop view if exists stu_v_1;
- 检查选项:
当使用 WITH CHECK OPTION 子句创建视图时, MySQL 会通过视图检查正在更改的每个行,例如 插入,更新,删除,以使其符合视图的定义。 MySQL 允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql 提供了两个选项: CASCADED 和 LOCAL ,默认值为 CASCADED 。
- 视图的更新:视图中的行和基础表中的行必须存在一对一的关系,如果视图包含以下任何一项,则该视图不可更新
-
A. 聚合函数或窗口函数( SUM() 、 MIN() 、 MAX() 、 COUNT() 等)B. DISTINCTC. GROUP BYD. HAVINGE. UNION 或者 UNION ALL
- 视图的作用
1)简单:简化用户对数据的理解,也可以简化他们的操作,被经常使用的查询可以定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。
2)安全:数据库可以授权,但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
3)数据独立:可帮助用户屏蔽真实表结构变化带来的影响
-
存储过程-->将sql语言层面的代码封装与重用
- 特点:1)封装,复用2)可以接收参数返回数据3)减少网络交互,效率提升
- 基本语法:
1)创建存储过程
create procedure p1()
begin
select count(*) from student;
end;
2)调用存储过程:call p1();
3)查看存储过程
select *
from information_schema.ROUTINES
where ROUTINE_SCHEMA='itheima';
show create procedure p1;
4)删除存储过程:
drop procedure if exists p1;
- 变量
1)系统变量:是MySQL服务器提供,不是用户定义的,属于服务器层面。分为全局变量(GLOBAL)、会话 变量(SESSION)。
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方
式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名;
2)设置系统变量:
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
注:
如果没有指定SESSION/GLOBAL,默认是SESSION,会话变量。
A. 全局变量(GLOBAL): 全局变量针对于所有的会话。
B. 会话变量(SESSION): 会话变量针对于单个会话,在另外一个会话窗口就不生效了。
mysql服务重新启动之后,所设置的全局参数会失效,要想不失效,可以在 /etc/my.cnf 中配置。
-- 赋值SET @var_name = expr [, @var_name = expr] ... ;
-- SET @var_name := expr [, @var_name := expr] ... ;
set @myname='itcast';
set @myage:=10;
set @mygender:='男',@myhobby:='java';
select @myage,@myage,@mygender,@myhobby;
-- SELECT @var_name := expr [, @var_name := expr] ... ;
select @mycolor:='red';
-- SELECT 字段名 INTO @var_name FROM 表名;
select count(*) into @mycount from tb_user;
select @mycolor,@mycount;
-- 注意: 用户定义的变量无需对其进行声明或初始化,只不过获取到的值为NULL
select @abc;
DECLARE 变量名 变量类型 [DEFAULT ... ] ;
create procedure p2()
begin
declare stu_count int default 0;
set stu_count:=1;
select count(*) into stu_count from student;
select stu_count;
end;
call p2;
-
if判断
- 参数:
IN :该类参数作为输入,也就是需要调用时传入值OUT:该类参数作为输出,也就是该参数可以作为返回值INOUT:既可以作为输入参数,也可以作为输出参数
create procedure p4(in score int, out result varchar(10))
.......
call p4(90,@result);
select @result;
-- 将传入的200分制的分数,进行换算,换算成百分制,然后返回
create procedure p5(inout score double)
begin
set score:=score*0.5;
end;
set @score=78;
call p5(@score);
select @score;
-
循环
- while 符合条件进行循环
- repeat满足条件时推出循环
-- 先执行一次逻辑,然后判定UNTIL条件是否满足,如果满足,则退出。如果不满足,则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;
create procedure p8(in n int )
begin
declare total int default 0;
repeat
set total:=total+n;
set n:=n-1;
until n<=0
end repeat;
select total;
end;
- loop循环
-
1). 介绍LOOP 实现简单的循环,如果不在 SQL 逻辑中增加退出循环的条件,可以用其来实现简单的死循环。LOOP 可以配合以下两个语句使用:LEAVE :配合循环使用,退出循环。ITERATE :必须用在循环中,作用是跳过当前循环剩下的语句,直接进入下一次循环。
create procedure p10(in n int)
begin
declare total int default 0;
sum:
loop
if n <= 0 then
leave sum;
end if;
if n%2= 1 then
set n:=n-1;
iterate sum;
end if;
set total=total+n;
end loop sum;
select total;
end;
-
游标:
- 游标是用来存储查询结果集的数据类型,在存储过程和函数中,可以使用游标对结果进行循环的处理。游标的使用包括游标的声明、open、fetch和close
-
声明游标:DECLARE 游标名称 CURSOR FOR 查询语句 ;开启游标:OPEN 游标名称 ;使用游标:FETCH 游标名称 INTO 变量 [, 变量 ] ;关闭游标:CLOSE 游标名称 ;
create
definer = root@localhost procedure p11(IN uage int)
begin
declare u_name varchar(100);
declare u_pro varchar(100);
-- A:声明游标,存储查询结果集:DECLARE 游标名称 CURSOR FOR 查询语句 ; 1
declare u_cursor cursor for select name, profession
from tb_user
where age <= uage;
-- 声明一个条件处理程序,当满足条件状态码02000时退出,关闭游标
declare exit handler for sqlstate '02000' close u_cursor;
drop table if exists tb_user_pro;
-- B:创建表结构
create table if not exists tb_user_pro
(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
-- C:开启游标
open u_cursor;
-- D:获取游标中的记录
while true do
fetch u_cursor into u_name,u_pro;
insert into tb_user_pro values (null,u_name,u_pro);
end while;
-- F:关闭游标
close u_cursor;
end;
-
条件处理程序
DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;
handler_action 的取值:
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
condition_value 的取值:
SQLSTATE sqlstate_value: 状态码,如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写
-
存储函数:存储函数是有返回值的存储过程,存储函数的参数只能是in类型的。具体语法如下:
create function 存储函数名称([参数列表]) returns type[characteristic] begin --sql语句 return...; end;
characteristic说明:
-
deterministic:相同的输入参数总是产生相同的结果
-
no sql :不包括sql语句。
-
reads sql data:包含读取数据的语句,但不包含写入数据的语句。
-
触发器:
- 语法:
- 创建触发器
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;
查看:show triggers;
删除;drop trigger[schema_name.]trigger_name;-- 如果没有指定schema_name默认为当前数据库。
-- 插入数据触发器
create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
(null,'insert',now(),new.id,concat('插入的数据内容为:id=',new.id,',name=',new.name,',phone',new.phone,',email',new.email,',profession',new.profession));
end;
-- 查看
show triggers ;
-- 删除
drop trigger tb_user_insert_trigger;
- 表级锁:
表级锁
元级锁
意向锁:
- 意向共享锁(IS):select ... lock in share mode,与表锁共享锁(read)兼容,与表锁排它锁(write)互斥
- 意向排它锁(IX):insert、update、delete、select...for update,与表锁共享锁(read)、表锁排它锁(write)都互斥,意向锁之间不互斥
-
可查看意向锁和行锁 的加锁情况:
-
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
意向锁主要解决了加了行锁和表锁的冲突问题
- 行级锁:
- 索引上的等值查询(唯一索引),给不存在的记录加锁时, 优化为间隙锁 。
- 索引上的等值查询(非唯一普通索引),向右遍历时最后一个值不满足查询需求时,next-key
- lock 退化为间隙锁。
- 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止
九、 innodb引擎:
1:逻辑存储结构
2:内存架构:
- 内存结构
1)缓冲池:
- free page:空闲page,未被使用。
- clean page:被使用page,数据没有修改过
- dirty page:脏页,被使用page,数据被修改过,页中数据和磁盘数据不一致。
2)Change Buffer:
3.磁盘结构:
1):系统表空间
2):File-Per-Table Tablespaces:独立表空间,如果开启了innodb_file_per_table开关 ,则每个表的文件表空间包含单个InnoDB表的数据和索引 ,并存储在文件系统上的单个数据文件中
3):通用表空间:需要通过 CREATE TABLESPACE 语法创建通用表空间,在创建表时,可以指定该表空间。
-- 创建表空间
CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;
create tablespace ts_itheima add datafile 'myitheima.ibd' engine=innodb;
-- 创建表时指定
CREATE TABLE xxx ... TABLESPACE ts_name;
4.后台线程
1)Master Thread:核心后台线程,还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性,还包括脏页的刷新,合并插入缓存,undo页的回收
2)io thread:
5、事务原理:
1)事务:是一组操作的集合,他是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
2)特性:
-
redolog 事务的持久性:
重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性。 该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中,后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。
顺序磁盘io性能大于随机磁盘io -- WAL先写日志
-
undolog 原子性
6.MVCC
- 三个隐式字段
- undolodg版本链
-
ReadView (读视图)是 快照读 SQL 执行时 MVCC 提取数据的依据,记录并维护系统当前活跃的事务(未提交的)id 。
可重复读