本教程引自黑马程序员MySQL视频:https://www.bilibili.com/video/BV1zJ411M7TB
文章目录
第一章:索引
1.1 概述
MySQL官方定义为:索引是帮助MySQL高效获取数据得数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法得数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
1.2 优势和劣势
优势:
- 类似于书籍得目录,提高数据检索得效率,降低数据库得IO成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗
劣势:
- 索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的
- 虽然大大提高了查询效率,同时却也降低了更新表的速度,如对表进行INSERT、UPDATE、DELETE。因为更新表时,MySQL不仅要保存数据,还要调整更新数据导致的索引结构的变化(数据变了,二叉树结构也随之会变化)。
1.3 索引结构
索引是在MySQL的存储引擎层中实现的,而不是在服务器层实现的。索引每种存储引擎的索引可能都不一样,也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4中索引:
- BTree索引:最常见的索引类型,大部分索引都支持B树索引。
- Hash索引:只有Memory引擎支持,使用场景简单。
- R-Tree索引:空间索引是MylSAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少,不做特别介绍。
- full-text索引:全文索引也是MylSAM引擎的一个特殊索引类型,InnoDB从MySQL5.6版本开始支持全文索引。
本章主要介绍BTree索引,也是最常用的索引。
索引 | InnoDB引擎 | MyISAM引擎 | Memory引擎 |
---|---|---|---|
BTree索引 | 支持 | 支持 | 支持 |
Hash索引 | 不支持 | 不支持 | 支持 |
R-Tree索引 | 不支持 | 支持 | 不支持 |
full-text索引 | 5.6版本后支持 | 支持 | 不支持 |
我们平常所说的索引,如果没有特别指明,都是指B+树(多路搜索树,不一定是二叉的)结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引,统称为索引。
1.3.1 BTree结构
BTree又叫平衡搜索树,一颗m叉的Tree特性如下:
- 树中每个节点最多包含m个孩子。
- 除根结点和叶子节点外,每个节点至少有[cell(m/2)](除以2向上取整)个孩子。
- 若根节点不是叶子节点,则至少有两个孩子。
- 所有的叶子节点都在同一层。
- 每个非叶子节点由n个key与n+1个指针组成,其中[cell(m/2)-1]<=n<=m-1
下图是以5(m=5)叉为例
以上就是通过一系列字母构建成的BTree树,BTree树和二叉树相比,查询效率更高,因为对于相同的数据量来说BTree树的层次机构更小,所以搜索更快。
注:这块不易理解,如果看不懂可以去看视频的第7节课程,笔记开头有课程地址。
1.3.2 B+Tree结构
B+Tree是BTree的变种,与BTree的区别为
- n叉B+Tree最多含有n个key,BTree最多含有n-1个key。
- B+Tree的叶子节点保存所有的key信息,依key大小顺序排列
- 所有的非叶子节点都可以看作是key的索引部分。
由于B+Tree只有叶子节点保存key信息,查询任何key都要走到叶子节点,索引B+Tree的查询效率更加稳定。
1.3.3 MySQL中的B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
1.4 索引分类
- 单值索引:一个索引只能包含单个列,一个表可以有多个单列索引。
- 唯一索引:索引列的值必须唯一,单允许有空值。
- 复合索引:一个索引包含多个列
1.5 索引语法
创建表的时候可以同时创建索引,索引也可以在创建表后进行创建
准备环境:
create database demo_01 default charset=utf8mb4;
use demo_01;
create table city(
city_id int(11) not null auto_increment,
city_name varchar(50) not null,
country_id int(11) not null,
primary key (city_id)
) engine=InnoDB default charset=utf8;
create table country(
country_id int(11) not null auto_increment,
country_name varchar(50) not null,
primary key (country_id )
) engine=InnoDB default charset=utf8;
insert into city(city_id,city_name,country_id) values(1,'西安',1);
insert into city(city_id,city_name,country_id) values(2,'NewYork',2);
insert into city(city_id,city_name,country_id) values(3,'北京',1);
insert into city(city_id,city_name,country_id) values(4,'上海',1);
insert into country (country_id,country_name) values (1,'China');
insert into country (country_id,country_name) values (2,'America');
insert into country (country_id,country_name) values (3,'Japan');
insert into country (country_id,country_name) values (4,'UK');
1.5.1 创建索引
语法
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [USING index_type] on tbl_name(index_col_name...)
对city表里的city_name创建索引
create index idx_city_name on city (city_name);
1.5.2 查看索引
#表格形式显示
show index from city;
1.5.3 删除索引
语法
DROP INDEX index_name ON tbl_name;
drop index idx_city_name on city;
ALTER命令
1. 添加一个主键,意味着索引值必须是唯一的,且不能为null
alter table tbl_name add primary key(column_list);
2. 创建唯一索引,表示创建的索引的值必须是唯一的(null除外,null可能会出现多次)
alter table tbl_name add unique index_name(column_list);
3. 表示添加普通索引,索引值可以出现多次
alter table tbl_name add index index_name(column_list)
4.表示创建全文索引
alter table tbl_name add fulltext index_name(column_list);
1.6 索引设计原则
为了提高索引的使用效率,创建索引的时候应该尽量最受一些原则。
- 对查询频率高,且数据量大的表创建索引
- 索引字段的选择,应当从where字句的条件中提取,如果where字句中的组合比较多,那么应当挑选最常用、过滤效果最好的列组合。
- 使用唯一索引,区分度越高,使用索引的效率越高。
- 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率。另外索引过多的话,MySQL也会犯选择困难症,虽然最终仍然会找到一个可用的索引,但也提高了选择的代价。
- 使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的IO效率也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的IO效率。
- 利用最左前缀,N个组合而成的组合索引,那么相当于创建了N个索引,如果查询时where子句中使用了组成该索引的前几个字段,那么这条查询SQL可以利用组合索引来提升查询效率。
创建复合索引:
CREATE INDEX idx_name_email_status ON
tb_seller(name,email,status);
就相当于
对name创建了索引;
对name,email创建了索引;
对name,email,status创建了索引;
第二章:视图
2.1 概述
视图(View)是一种虚拟存在的表。视图并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的。通俗的讲,视图就是一条SELECT语句执行后返回的结果集。
视图相对于普通的表的优势主要包括以下几项:
- 简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件。
- 安全:使用视图的用户智能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。
- 数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决。
2.2 创建和修改视图
创建语法:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
修改语法:
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
选项说明:
WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件
LOCAL:只要满足本视图的条件就可以更新。
CASCADED:必须满足所有针对该视图的所有视图的条件才可以更新,默认值。
示例:
create view view_city_country as select c.*,t.country_name from city c,country t where c.country_id=t.country_id;
2.3 查看视图
show tables; 不仅可以查看表,也可以查看视图。或者在sql工具里查看。
2.4 删除视图
语法:
DROP VIEW [IF EXISTS] view_name [,view_name]...[RESTRICT | CASCADE]
示例:
drop view city_country_view;
第三章 存储过程和函数
3.1 存储过程和函数概述
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值,而存储过程没有。
3.2 创建存储过程
语法
CREATE PROCEDURE proc_name ([proc_param[,...]])
begin
--sql语句
end;
示例:
#将mysql默认的分隔符;替换为$
delimiter $
create procedure proc_test1()
begin
select 'Hello MySQL';
end$
#将mysql默认的分隔符改回;
delimiter ;
3.3 调用存储过程
call procedure_name();
3.4 查看存储过程
#方式1:查看数据库中所有的存储过程
select name from mysql.proc where db='demo_01';
#方式2:查询存储过程状态信息
show procedure status;
#方式3:查询某个存储过程的定义
show create procedure demo_01.proc_test1;
3.5 删除存储过程
语法
DROP PROCEDURE [IF EXISTS] proc_name;
第四章:触发器
4.1 简述
触发器是与表有关的数据库对象,指在insert/update/delete之前或之后,触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。
触发器类型 | NEW和OLD的使用 |
---|---|
INSERT 型触发器 | NEW表示将要或者已经新增的数据 |
UPDATE 型触发器 | OLD表示修改之前的数据,NEW表示将要或已经修改后的数据 |
DELETE 型触发器 | OLD表示将要或者已经删除的数据 |
4.2 创建触发器
语法结构:
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] --代表行级触发器
begin
trigger_stmt;
end;
示例:
通过触发器记录emp表的数据变更日志,包含增加、修改、删除;
首先增加emp表和一张日志表emp_logs:
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` decimal(18,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `emp_logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`operation` varchar(2) DEFAULT NULL,
`operate_time` datetime DEFAULT NULL,
`operate_id` int(11) DEFAULT NULL,
`operate_params` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
创建触发器:
delimiter $
create trigger emp_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs(id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,',name:',new.name,',age:',new.age,',salary:',new.salary,')'));
end$
DELIMITER ;
然后往emp表插入一条数据测试一下,然后查看日志表有没有记录:
insert into emp(name,age,salary) values('张三',28,30000);
4.3 查看和删除触发器
-
查看触发器:
- show triggers; 删除触发器:
- drop trigger 触发器名称;