最上层是一些客户端和链接服务,主要完成一些小类似于连接处理、授权认证、 及相关的安全方案。服务器也会为安全接入的每个用户端验证它所具有的操作权 限。
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。
在创建表时,指定存储引擎(默认InnoDB)
create table 表名(
字段1 字段1类型 [comment 字段1注释],
...
字段2 字段2类型 [comment 字段2注释],
)engine = innodb [comment 表注释];
查看当前数据库支持的存储引擎
show engines;
在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。
索引(index)是帮助MySQ==L高效获取数据==的==数据结构(有序)==。在数据结之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的IO成本 | 索引列也是要占用空间的 |
通过索引对数据进行排序,降低数据排序的成本,降低CPU的消耗 | 索引大大提高了查询效率,同时也降低更新表的速度,如对表进行insert、update、delete时,效率降低 |
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种:
索引结构 | 描述 |
---|---|
B+Tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据结构,通常使用较少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES |
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+tree索引 | 支持 | 支持 | 支持 |
Hash索引 | 不支持 | 不支持 | 支持 |
R-tree索引 | 不支持 | 支持 | 不支持 |
Full-text | 5.6版本之后支持 | 支持 | 不支持 |
==我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。==
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加 一个指向相邻叶子结点的链表指针,就形成了带有顺序指针的B+Tree,提高区 间访问的性能。
思考:
为什么InnoDB存储引擎选择使用B+tree索引结构?
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clustered Index) | 将数据存储与索引放到了一块,索引结构的叶子结点保存了行数据 | 必须有,而且只有一个 |
二级索引(Secondary Index) | 将数据与索引分开存储,索引结构的叶子结点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
创建索引
create [UNIQUE|FULLTEXT] index index_name on table_name (index_col_name,...);
查看索引
show index from table_name;
删除索引
drop index index_name on table_name;
# 开启mysql慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,sql语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
select @@have_profiling;
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling;
set profiling=1;
执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
explain执行计划
验证索引效率
在未建立索引之前,执行如下SQL语句,查看SQL的耗时
select * from tb_sku where sn = '1000000003410008';
针对字段创建索引
create index idx_sku_sn on tb_sku(sn);
然后再次执行相同的SQL语句,再次查看SQL的耗时
select * from tb_sku where sn = '1000000003410008';
最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。
如果跳跃某一列,==索引将部分失效(后面的字段索引失效)==
范围查询
联合索引中,出现范围查询(>,<),==范围查询右侧的列索引会失效==
索引列运算
不要在索引列上进行运算操作,==索引将失效==
字符串不加引号
字符串类型字段使用时,不加引号,==索引将失效==
模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
or连接的条件
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有 索引,那么涉及的索引都不会被用到
数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引
SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的
explain select * from 表名 [use index|ignore index|force index] where 条件;
覆盖索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少select *
前缀索引
当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
create
index idx_xxxx on table_name(column(n));
单列索引与联合索引
思考:
一张表,有四个字段(id,username,password,status),由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方案:
select id,username,password from tb_user where username='ithui';
建立username和password的联合索引
insert优化
# 插入条数在500-1000条
insert
into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');
主键乱序插入:8 1 9 21 13 12 4 89 7
主键顺序插入:1 2 3 4 5 6 7 8 9
一个常见又非常头疼的问题就是limit 20000000,10,此时需要MySQL排序前20000010记录,仅仅返回20000000-20000010的记录,其他记录丢弃,查询拍讯的代价非常大
优化思路:一般分页查询时,通过创建覆盖索引能够比较 好地提高性能,可以通过覆盖索引加子查询形式进行优化
例:
explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
优化思路:自己计数
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。
视图(view)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态产生的。
创建
create [or replace] view 视图名称[(列名列表)] as select 语句 [with[cascaded | local] check option]
查询
# 查看创建视图语句
show create view 视图名称;
# 查看视图数据
select * from 视图名称...;
修改
# 方式一:
create [or replace] view 视图名称[(列名表名)] as select 语句 [with[cascaded | local] check option]
# 方式二:
alter view 视图名称[(列名列表)] as select 语句 [with[cascaded | local] check option]
删除
drop view [if exists] 视图名称 [,视图名称]...
视图的检查选项
当使用with check option子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如插入,更新,删除,以使其故何视图定义。MySQL允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql提供了两个选项:cascaded和local,默认值为cascaded
视图更新
要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项,则该视图不可更新:
作业
介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
存储过程思想上很简单,就是数据库SQL语言层面的代码封装与重用
特点
封装,复用
可以接收参数,也可以返回数据
减少网络交互,效率提升
创建
create procedure 存储过程名称([参数列表])
begin
--sql语句
end;
调用
call 名称([参数]);
查看
select * from information_schema.routines where routine_schema='xxx';--查询指定数据的存储过程及状态信息
show create procedure 存储过程名称;--查询某个存储过程的定义
删除
drop procedure [if exists] 存储过程名称;
==注意:在命令行中,执行创建存储过程的SQL时,需要通过关键字delimiter指定SQL语句的结束。==
变量
==系统变量==是MySQL服务器提供,不是用户自定义的,属于服务器层面。分为全局变量(==GLOBAL==)、会话变量(==SESSION==)。
==用户自定义变量==是用户根据需要自己定义的变量,用户变量不用提前声明,在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。
==局部变量==是根据需要定义的在局部生效的变量,访问之前,需要declare声明。可用作存储过程内的局部变量和输入参数,局部变量的范围是在其内声明的begin...end块。
declare 变量名 变量类型[default ...];
变量类型就是数据库字段类型:int、bigint、char、varchar、date、time等set @var_name=expr[,@var_name=expr]...;
set @var_name:=expr[,@var_name:=expr]...;
select @var_name:=expr[,@var_name:=expr]...;
select 字段名 into @var_name from 表名;
show [session|global] variables;--查看所有系统变量
show [session|global] variables
like
'...'--可以通过like模糊匹配方式查找变量
select @@[session|global] 系统变量名;--查看指定变量的值
if
语法:
if 条件1 then
...
elseif 条件2 then --可选
...
else --可选
...
end if;
参数
类型 | 含义 | 备注 |
---|---|---|
in | 该类参数作为输入,也就是需要调用时传入值 | 默认 |
out | 该类参数作为输出,也就是该参数可以作为返回值 | |
inout | 既可以作为输入参数,也可以作为输出参数 |
case
case case_value
when when_value1 then statement_list1 [when when_value2 then statement_list2]...
[else statement_list]
end
case;
while
while循环是有条件的循环控制语句。满足条件后,再执行循环体中的SQL语句。具体语法为:
#先判定条件,如果条件为true,则执行逻辑,否则,不执行逻辑
where 条件 do
SQL逻辑...
end while;
repeat
repeat是有条件的循环控制语句,当满足条件的时候退出循环。具体语法为:
repeat
SQL逻辑...
until 条件
end repeat;
loop
loop实现简单的循环,如果不在SQL逻辑中增加退出循环的条件,可以用其来实现简单的死循环。loop可以配合一下两个语句使用:
[begin_label:] loop
SQL逻辑...
end loop [end_label];
leave label;--退出指定标记的循环体
iterate label;--直接进入下一次循环
游标
游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用优表对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch和close,其语法分别如下
declare 游标名称 cursor
for 查询语句;
fetch 游标名称 into 变量[,变量];
条件处理程序
条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相对应的处理步骤。具体语法为:
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说明:
- determinstic:相同的输入参数总是产生相同的结果
- no sql:不包含sql语句
- reads sql data:包含读取数据的语句,但不包含写入数据的语句
触发器类型 | new和old |
---|---|
insert型触发器 | new表示将要或者已经新增的数据 |
update型触发器 | old表示修改之前的数据,new表示将要或已经修改后的数据 |
delete型触发器 | old表示将要或者已经删除的数据 |
语法
create
trigger trigger_name
before/after
insert/update/delete
on tbl_name for
each
row
--行级触发器
begin
trigger_stmt;
end;
drop
trigger [schema_name]trigger_name; 如果没有指定schema_name,默认为当前数据库
介绍
全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML的写语句,DDL语句,已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全裤的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。
演示
特点
数据库中加全局锁,是一个比较重的操作,存在一下问题:
1、如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆
2、如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟
在InnoDB引擎中,我们可用在备份时加行参数--single-transaction参数来完成不加锁的一致性数据备份
mysqldump --single-transaction -uroot -pLI1944575687 itcast > itcast.sql
表级锁,每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高,并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中
对于表级锁,主要分为以下三类:
表锁
对于表锁,分为两类:
语法:
==读锁不会阻塞其他客户端的读,但是会阻塞写。写锁即会阻塞其他客户端的读,又会阻塞其他客户端的写==
元数据锁(meta data lock,MDL)
MDL加锁过程是系统自动控制,无需显示使用,在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候 ,不可以对元数据进行写入操作。
在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候,加MDL读锁(共享);当对表结构进行变更操作的时候,加MDL写锁(排他)
对应SQL | 锁类型 | 说明 |
---|---|---|
lock tables xxx read/write | SHARED_READ_ONLY / SHARED_NO_READ_WRITE | |
select、select...lock in share mode | SHARED_READ | 与SHARED_READ、SHARED_WRITE兼容,与EXCLUSIVE互斥 |
Insert、update、delete、select...for update | SHARED_WRITE | 与SHARED_READ、SHARED_WRITE兼容,与EXCLUSIVE互斥 |
alter table... | EXCLUSIVE | 与其他的MDL都互斥 |
查看元数据锁:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
意向锁
为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查
可以通过以下SQL,查看意向锁及行锁的加锁情况:
select object_schema,object_name,index_name,lock_mode,lock_data from performance_schema.data_locks;
介绍
行级锁,每次擦欧总锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用子InnoDB存储引擎中
InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。对于行级锁,主要分为以下三类:
行锁
InnoDB实现了以下两种类型的行锁:
S(共享锁) | X(排他锁) | |
---|---|---|
S(共享锁) | 兼容 | 冲突 |
X(排他锁) | 冲突 | 冲突 |
SQL | 行锁类型 | 说明 |
---|---|---|
insert... | 排他锁 | 自动级锁 |
update... | 排他锁 | 自动加锁 |
delete... | 排他锁 | 自动加锁 |
select(正常) | 不加任何锁 | |
select...lock in share mode | 共享锁 | 需要手动在select之后加lock in share mode |
select...for update | 排他锁 | 需要手动在select之后加for update |
行锁-演示
默认情况下,InnoDB在REPEATABLE READ事务隔离级别运行,InnoDB使用next-key锁进行搜索和索引扫描,以防止幻读。
可以通过以下SQL,查看意向锁及行锁的加锁情况:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
间隙锁/临键锁-演示
默认情况下,InnoDB在REPEATABLE READ事务隔离级别运行,InnoDB使用next-key锁进行搜索和索引扫描,以防止幻读。
注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存,一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁
MySQL5.5版本开始,默认使用InnoDB存储引擎,它擅长事务处理,具有崩溃恢复特性,在日常开发中使用非常广泛。下面是InnoDB架构图,右侧为磁盘结构。
隐藏字段含义DB_TRX_ID最近修改事务ID,记录插入这条记录或者最后一次修改该记录的事务IDDB_ROLL_PTR回滚指针,指向这条记录的上一个版本,用于配合undo log,指向上一个版本DB_ROW_ID隐藏主键,如果表结构没有指定主键,将会生成该隐藏字段
不同事务或相同事务对同一条记录进行修改,会导致该记录的undo log生成一条记录版本链表,链表的头部时最新的旧记录,链表尾部时最早的旧记录
不同的隔离级别,生产ReadView的时机不同:
MySQL数据库安装完成后,自带了以下四个数据库,具体作用如下:
数据库 | 含义 |
---|---|
mysql | 存储MySQL服务器正常运行所需要的各种信息(时区、主从、用户、权限等) |
information_schema | 提供了访问数据库元数据的各种表和视图,包含数据库、表、字段类型及访问权限等 |
performance_schema | 为MySQL服务器运行时状态提供了一个底层监控功能,主要用于收集数据库服务器性能参数 |
sys | 包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图 |
mysql
该mysql不指mysql服务,而是指mysql的客户端工具
语法:
mysql [options] [database]
选项:
-u,--uert=name #指定用户名
-p,--password[=name] #指定密码
-h,--host=name #指定服务器IP或域名
-p,--port=port #指定连接端口
-e,--execute=name #执行SQL语句并退出
-e选项可以在MySQL客户端执行SQL语句,而不用连接到MySQL数据库再执行,对于一些批处理脚本,这种方式尤其方便
示例:
mysql -urrot -p123456 db01 -e "select * from stu";
mysqladmin
mysqladmin是一个执行管理操作的客户端程序,可以用它来检查服务器的配置和当前的状态、创建并删除数据库等。
通过帮助文档查看选项:
mysqladmin --help
示例:
mysqladmin -uroot -p12345 drop 'test01';
mysqladmin -uroot -p12345 version;
mysqlbinlog
由于服务器生成的二进制日志文件以二进制格式保存,所以如果想要检查这些文本的文本格式,就会使用到mysqlbinlog日志管理工具
语法:
mysqlbinlog [options] log-files1 log-files2...
选项:
-d,--database=name #指定数据库名称,只列出指定的数据库相关操作
-o,--offset=# #忽略掉日志中的前n行命令
-r,--result-file=name #将输出的文本格式日志输出到指定文件
-s,--short-form #显示简单格式,省略掉一些信息
--start-datatime=date1 --stop-datetime=date2 #指定日期间隔内的所有日志
--start-position=pos1 --stop-position=pos2 #指定位置间隔内的所有日志
mysqlshow
mysqlshow客户端对象查找工具,用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引
语法:
mysqlshow [options] [db_name[table_name[col_name]]]
选项:
-count #显示数据库及表的统计信息(数据库,表均可以不指定)
-i #显示指定数据库或者指定表的状态信息
示例:
#查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p123 --count
#查询test库中每个表中的字段数,及行数
mysqlshow -uroot -p123 test --count
#查询test哭中book表的详细情况
mysqlshow -uroot -p123 test book --count
mysqldump
mysqldump客户端工具用来备份数据或在不同数据库之间进行数据迁移。备份内容包含创建表,及插入表的SQL语句
语法:
mysqldump [options] db_name[tables]
mysqldump [options] --database/ -B db1[db2 db3..]
mysqldump [options] --all -databases/-A
连接选项:
-u,--user=name #指定用户名
-p,--password[=name] #指定密码
-h,--host=name #指定服务器ip或域名
-p,port=# #指定连接端口
输出选项:
--add-drop-database #在每个数据库创建语句前加上drop database语句
--add-drop-table #在每个表创建语句前加上drop table语句,默认开启;不开启(--skip-add-drop-table)
-n,--no-create-db #不包含数据库的创建语句
-t,--no-create-info #不包含数据表的创建语句
-d,--no-data #不包含数据
-T,--tab=name #自动生成两个文件:一个.sql文件,创建表结构的语句;一个.txt文件,数据文件
mysqlimport/source
mysqlimport是客户端数据导入工具,用来导入mysqldump加- T参数后导出的文本文件
语法:
mysqlimport [options] db_name textfile1 [textfile2...]
示例:
mysqlimport -uroot -p1234 test /tmp/city.txt
如果需要导入sql文件,可以使用mysql中的source指令:
语法:
source/root/xxx.sql