MySql进阶学习笔记(版本5.7.30)
一、mysql 的架构介绍
1.mysql简介
概述
MySql是一个关系型数据库管理系统,由瑞典MySql AB公司开发,目前属于Oracle公司
MySQL是一种关联数据库管理系统,将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
Mysql是开源的,所以不需要支付额外的费用。Mysql是可以定制的,采用GPL协议,你可以修改源码来开发自己的MySql系统
Mysql支持大型的数据库。可以拥有上千万条记录的大型数据库。MySQL使用标准的SQL数据语言形式,MySQL可以运行在多个系统上,并且支持多种语言
2.Linux安装
1.检查是否有安装记录,使用命令rpm -qa | grep mariadb
查看,若存在则使用rpm -e --nodeps [查询出的文件名]
删除即可,然后检查是否存在libaio-0.3.109-13.el7.x86_64
和net-tools-2.0-0.25.20131004git.el7.x86_64
文件,使用命令``rpm -qa | grep libaio和
rpm -qa | grep net-tools`,不存在需要手动安装
2.官网下载安装包
3.上传到需要安装的服务器上,使用tar -xvf mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
命令解压,解压成功后会有以下目录
4.根据上图顺序使用rpm -ivh [安装包名]
安装,安装完后需要使用mysqld --initialize --user=mysql
命令进行初始化,查看日志cat /var/log/mysqld.log
获取初始化密码
注:初始化时若报
-initialize specified but the data directory has files in it. Aborting
时,是由于存在data目录,可以通过查看/etc/my.cnf
文件下的datadir所指向的目录,使用rm -rf [目录名]
删除文件即可,慎用此命令;初始化密码也有可能会有特殊字符登录不进去,可以在/etc/my.cnf
下添加skip-grant-tables
,后重启服务登录可以直接回车登录(只有root权限用户可执行此操作),
5.查看systemctl status mysqld
并重启mysql服务systemctl start mysqld
or service mysqld restart
6.第一次登录会提示修改密码,修改密码方式如下
- update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’;
- alter user ‘root’@‘localhost’ identified by ‘123456’;
- set password for ‘root’@‘localhost’=password(‘123456’);
修改后都要使用flush privileges
命令刷新权限
3.mysql配置文件
第一步修改配置文件
vim /etc/my.cnf
,在最后加上中文字符集配置character_set_server=utf8
,重启mysql服务即可systemctl restart mysqld
or ‘service mysqld restart’第二 步修改数据库字符集,查看数据库字符集命令
show create database 数据库名
,修改命令alter database 数据库名 character set 'utf8'
第三部修改表字符集,查看数据库表字符集命令
show create table 数据库表名
,修改命令alter table 数据库表名 convert to character set 'utf8'
-
用户与权限设置(所有用户信息都可在mysql.user表中查看)
#创建用户 #语法:create user 用户名 identified by 密码 create user zhangsan identified by '123456' #授权语法 # grant 权限1,权限2,...权限n on 数据库.表名 to 用户名@用户地址 【identified by 密码】 ##没有此用户是会创建一个新用户 ## eg 授予用户对mydb库下的mytab表增删改的权限 grant insert,delete,update mydb.mytab to zhangsan@localhost 【identified by '123456'】 ## eg 授予用户对mydb库下所有表表增删改的权限 grant insert,delete,update mydb.* to zhangsan@localhost【 identified by '123456'】 ## 授予用户所有权限 grant all privileges on *.* to zhangsan@localhost【 identified by '123456'】
-
其他配置
create table mytb12 ( id int primary key auto_increment not null, name varhcar(22), age int, dept int ) insert into mytab12(name,age,dept) value('战三1',30,101),('战三3',34,102),('战三2',36,101),('战三6',24,10); ##查询每个机构年龄最大的人 ###错误语句(5.5版本可以查出数据,但数据是错误的) select name,dept,max(age) from mytb12 group by dept;#group by使用原则,select后只能放函数和group by后的字段 ## 正确语句 select * from mytab12 m inner join (select dept,max(age) from mytb12 group by dept) ab on ab.dept = m.dept and m.age = ab.age;
解决上面兼容问题,可以通过设置sql_mode模式
概述
- 通过设置
sql_mode
, 可以完成不同严格程度的数据校验,有效地保障数据完整性。 - 通过设置
sql_model
为宽松模式,来保证大多数 sql 符合标准的 sql 语法,这样应用在不同数据库之间进行迁移时,则不需要对业务 sql 进行较大的修改。 - 在不同数据库之间进行数据迁移之前,通过设置
sql_mode
可以使 MySQL 上的数据更方便地迁移到目标数据库中。
查看
使用
show variables like 'sql_mode'
orselect @@sql_mode
查看系统变量sql_mode
sql_mode常用取值
ONLY_FULL_GROUP_BY
:对于GROUP BY
聚合操作,如果在SELECT
中的列没有在GROUP BY
中出现,那么这个 SQL 是不合法的;NO_AUTO_VALUE_ON_ZERO
:该值影响自增长列的插入。默认设置下,插入0
或NULL
代表生成下一个自增长值。如果用户希望插入的值为0
,而该列又是自增长的,那么这个选项就有用了;STRICT_TRANS_TABLES
:在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制;NO_ZERO_IN_DATE
:在严格模式下,不允许日期和月份为零;NO_ZERO_DATE
:设置该值,MySQL 数据库不允许插入零日期,插入零日期会抛出错误而不是警告;ERROR_FOR_DIVISION_BY_ZERO
:在INSERT
或UPDATE
过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时 MySQL 返回NULL
;NO_AUTO_CREATE_USER
:禁止GRANT
创建密码为空的用户;NO_ENGINE_SUBSTITUTION
:如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常;PIPES_AS_CONCAT
:将||
视为字符串的连接操作符而非或运算符,这和 Oracle 数据库是一样的,也和字符串的拼接函数concat
相类似;ANSI_QUOTES
:启用ANSI_QUOTES
后,不能用双引号来引用字符串,因为它被解释为识别符;
设置
## 会话级别设置,多个值用逗号 set session sql_mode = 'NO_ZERO_DATE,ANSI_QUOTES' ## 全局级别设置 session global sql_mode = 'NO_ZERO_DATE,ANSI_QUOTES' ## 以上两种配置在服务器重启后都会重置为默认值,可以修改/etc/my.cnf文件永久生效,在配置文件中加入 ## sql_mode='值'即可,修改后需要重启服务器'systemctl restart mysqld'
启用mysql缓存配置
修改/etc/my.cnf,新增一行
query_cache_type=1
重启mysql,查看系统变量profiling
是否开启,若没有则需要开启set profiling = 1
,开启后可以通过show profiles
查看语句执行计划,也可通过show profile 【block io,cpu,memory,swaps,context switches,source 】 for query [query_id]
查看sql执行计划详情
- 通过设置
4.mysql逻辑架构介绍
5.mysql存储引擎
5.mysql存储引擎
MyISAM和InnoDb
对比项 | InnoDB | MyISAM |
---|---|---|
64TB | 256TB | |
外键 | 支持 | 不支持 |
事务 | 支持 | 不支持 |
行表锁 | 行锁,操作数据是只锁某一行不对其他行有影响,适合高并发的操作 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 |
缓存 | 不仅缓存索引,还要缓存真实数据 | 只缓存索引,不缓存真实数据 |
关注点 | 并发写、事务、更大资源 | 节省资源、消耗少、简单业务、适用删改少,增读多的操作 |
默认安装 | 是 | 是 |
自定义表默认使用 | 是 | 否 |
自带系统表使用 | 否 | 是 |
二、索引优化分析
1.性能下降SQL慢(执行时间长、等待时间长)
- 数据过多(单表数据超过五百万货单库数据超过五千万)—》分库分表
- 关联表太多,太多join --》SQL优化
- 没有充分利用索引–》索引建立
- 服务器调优及各个参数设置–》调整my.cnf
2.常见通用的join查询
3.索引简介
概述
一种用来快速定位目标数据的一种数据结构,**优点:**提高数据库的检索效率,降低数据库的IO成本,通过索引列队数据进行排序,降低数据排序的成本,降低了CPU消耗。**缺点:**降低了更新表的效率,如对标进行CUD操作时,mysql不仅要保存数据,好需要保存一下索引文件每次更新添加了索引的字段,索引本质上也是存放在一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引过多也会占用更大的空间
索引结构
BTree索引
B+Tree索引
时间复杂度
数据结构 | 查找 | 插入 | 删除 | 遍历 |
---|---|---|---|---|
数组 | O(N) | O(1) | O(N) | – |
有序数组 | O(logN) | O(N) | O(N) | O(N) |
链表 | O(N) | O(1) | O(N) | – |
有序链表 | O(N) | O(N) | O(N) | O(N) |
二叉树(一般情况) | O(logN) | O(logN) | O(logN) | O(N) |
二叉树(最坏情况) | O(N) | O(N) | O(N) | O(N) |
平衡树(一般和最坏) | O(logN) | O(logN) | O(logN) | O(N) |
哈希表 | O(1) | O(1) | O(1) | – |
B-Tree和B+Tree的区别
B-Tree:
- 所有键值分布在整个树中
- 任何关键字出现且只出现在一个节点中
- 搜索有可能在非叶子节点结束
- 在关键字全集内做一次查找,性能逼近二分查找算法
B+Tree:
- 所有关键字存储在叶子节点,非叶子节点不存储真正的data
- 为所有叶子节点增加了一个链指针
- IO次数相对于B-Tree更少
索引分类
- 单值索引:一个索引只包含单列,一个表可以有多个单列索引。语法:
create index idx_name on tablename(字段名)
- 唯一索引:索引列必须唯一,但允许有空值。语法:
create unique index 索引名 on 表名(字段名)
- 主键索引:设定为主键后数据库会自动建立索引,innodb为聚簇索引
- 复合索引:一个索引列包含多个列。语法:
create 【unique】index 索引名 on 表名(字段1,字段2...)
基本语法
创建
create 【unique】index 【索引名】on 表名(字段名)
or
alter table 表名 add primary key(字段名);//创建主键索引,表示该字段值必须唯一且不能为空
or
alter table 表名 add unique 索引名(列名);//创建唯一索引
or
alter table 表名 add index 索引名(列名);//创建普通索引
or
alter table 表名 add fulltext 索引名(列名);//创建索引索引
删除
dorp index 【索引名】 on 表名
查看
show index from 表名\G
索引创建条件
- 主键自动那个建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 单键/组合索引选择问题,组合索引性价比更高
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或分组字段
不需要建索引的情况
- 表记录太少
- 经常增删改的表或字段
- where条件里用不到的字段不创建索引
- 过滤性不好的字段不合适建索引
4.性能分析
explain
介绍
使用explain关键字可以模拟优化器执行SQL查询语句,从而知道msyql是如何处理sql语句的,分析查询语句或表结构的性能瓶颈。
注意:1. explain 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
- explain不考虑过重cache
- explain不能显示mysql在执行查询是所做的优化工作
- 部分统计信息是估算的,并非精确值
- explain只能解释select操作,其他操作要重写为select后查看执行计划
能干嘛
- 标的读取顺序
- 哪些索引可以使用
- 数据读取操作的操作类型
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被物理查询
怎么玩
-
语法
explain +sql语句
-
各字段解释
-
id: 表示一趟独立的查询,一个sql中查询的趟数越少越好,通过id大小可以看出执行顺序,id越大,执行优先级越高,若id相同,顺序由上至下
-
select_type:
- simple: 简单的select查询,查询中不包含子查询或UNION
- primary: 查询中若包含任何复杂的字部分,最外层查询则被标记位primary
- derived: 在from列表中包含的子查询被标记位derived,mysql会递归执行这些子查询,把结果放在临时表里
- subquery:在select或where列表中包含了子查询
- dependent subquery:在select或where列表中包含了子查询,子查询基于外层
- uncacheable subquery :不可用缓存的子查询
- union:若第二个select出现在union后,则被标记位union,若union包含在from自居的子查询中,最外层select将被标记位derived
- union result : 从union表获取结果的select
-
table: 显示这一行所用的表,可以是实际表,也可以是表的别名,还可以为虚拟表
-
partitions: 代表的分区表中的命中情况,非分区表中,该项为null
-
type: 显示查询语句所用的查询类型,一般得保证查询至少达到range,最好能达到ref
- system: 表中只有一行记录,这是const类型特列,一般不会出现
- const: MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量
- eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
- ref: 非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值得行,然而,他可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
- range: 只检索给定范围的行,使用一个索引来选择行
- index: Full Index Scan,index与ALL区别为index类型只遍历索引树
- all: 全表扫描,将遍历全表以找到匹配的行
- index_merge: 在查询过程中需要多个索引组合使用,通常出现在有or的关键字的sql中
- ref_or_null: 对于某个字段急需要关联查询,也需要null值得情况下,查询器会
ALL < index < range< index_merge<ef_or_null < ref < eq_ref < const < system ,从左到右,性能越好
-
passible_keys: 显示在这张表中可能用到的索引,一个或多个,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定在查询中用到
-
key: 此字段是 MySQL 在当前查询时所真正使用到的索引
-
key_len: 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)
-
rows: 扫描出的行数(估算的行数)
-
extra: 执行情况的描述和说明
- using filesort:当查询中包含order by操作且无法利用索引完成的排序操作,出现此描述是建议在排序字段建立索引(需要优化)
- using temporary:表示mysql需要使用临时表来存储结果集,常见于排序和分组查询(需要优化)
- using index:该值表示相应的select操作中是用来覆盖索引,避免访问了表的数据行,效率不错。如果同时出现using where,表明索引被用来执行索引键值得查询,若没有,表明索引用来读取数据而非执行查找动作
- using where:不用读取表中的所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤
- using join buffer:该值强调了在获取连接条件的时没有使用索引,并且需要连接缓冲区来存储中间结果。若出现该值,则需要注意是否要根据具体情况可能需要添加索引来改进性能(需要优化)
- impossible where:该值强调了where语句会导致没有符合条件的行
- select tables optimized away:该值意味着经通过使用索引,优化器可能仅从聚合函数结果中返回一行
- no tables used:查询语句中使用from dual或不包含任何from子句
-
三、查询截取分析
-
批量新增50w条数据
#部分表 create table dept( id int(11) not null auto_increment primary key , deptName varchar(30) default null, address varchar(40) default null, ceo int null ) engine = innodb auto_increment=1 default charset =utf8; #员工表 create table emp( id int(11) not null auto_increment primary key , emp_no int(11) not null , name varchar(30) default null, age int(3) default null, dep_id int(11) default null )engine = innodb auto_increment=1 default charset =utf8; ##开启bin_log_trust_function_creators #查看 show variables like 'bin_log_trust_function_creators' #开启 set global log_bin_trust_function_creators = 1 #创建一个生成随机字母的字符串 create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) default 'abcdefghijklmnopzrstuvwxyzABCDEFGHIJKLMNOPZRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1)); set i = i+1; end while; return return_str; end #创建一个生成随机数的函数 create function rand_num(from_num int,to_num int) returns int(11) begin declare i int default 0; set i = floor(from_num+rand()*(to_num-from_num+1)); return i; end; #调用实现批量新增 call insert_dept(10000); call insert_emp(10000,500000) #创建删除索引的存储过程 create procedure drop_index_proc(dbname varchar(200),tablename varchar(200)) begin declare done int default 0; declare ct int default 0; declare _index varchar(20) default ''; #声明游标 declare _cur cursor for select INDEX_NAME from information_schema.STATISTICS where TABLE_SCHEMA=dbname and TABLE_NAME=tablename and INDEX_NAME <> 'primary' and SEQ_IN_INDEX = 1; declare continue handler for not found set done=2; #打开游标 open _cur; fetch _cur into _index; while _index <> '' do #拼接sql字符串 set @str = concat('drop index ',_index,' on ',tablename); #预编译sql字符串 prepare sql_str from @str; #执行sql execute sql_str; deallocate prepare sql_str; set _index = ''; #获取下一个游标 fetch _cur into _index; end while; #关闭游标 close _cur; end;
1.查询优化
- 查询索引列顺序尽量与创建顺序保持一致,最佳左前缀法,索引是分层次的,第一个索引未用到时后面索引也不会用到
- 尽量全职匹配,即where条件后的字段建立复合索引
- 范围查询字段后的索引字段会失效,建索引时需要把范围查询字段放在最后
- 条件中包含不等于
!=
或者<>
或者is not null
或者like '%abc'
会使索引失效 - 过滤条件字段类型与之类型不匹配时,mysql会默认进行转换,导致索引失效
select * from a where a.name(varchar)=123(int)
- 不要在索引列上做任何操作(函数、计算、自动或者手动的类型转换),否则会导致索引失效而转向全表扫描
假设create index on mytab(a,b,c)
where语句 | 索引是否被使用 |
---|---|
where a = 3 | Y,使用索引a |
where a = 3 and b=5 | Y,使用索引a,b |
where a = 3 and b=5 and c= 4 | Y,使用索引a,b,c |
where b=3 或者 where b=3 and c = 4 或者 where c = 4 | N |
where a=3 and c=5 | 使用索引a。未使用c,因为b中断了 |
where a=3 and b> 4 and c=5 | 使用索引a,b。未使用c,c不能再范围查询后,b中断了 |
where a is null and b is not null | is null支持索引,但是is not null不支持,所以只是用了a索引 |
where a <> 3 或者 where a != 3 | 不能使用索引 |
where abs(a) = 3 | 不能使用索引 |
where a = 3 and b like ‘aa%’ and c=4 | Y,使用索引a,b,c |
where a = 3 and b like ‘%aa’ and c =4 | 只使用了a索引 |
where a = 3 and b like ‘%aa%’ and c =3 | 只使用了a索引 |
where a = 3 and b like ‘a%cc%’ and c = 3 | Y,使用索引a,b,c |
排序优化(尽量避免使用filesort方式排序)
- order by字段需要建立索引
- 无过滤不索引
- order by 字段顺序应与索引顺序一致
- 排序规则要保持一致,要么全部为asc,要么全部为desc
- 若避免不了filesort,可以根据具体需求选择单路排序与双路排序
分组优化
- 字段顺序应与索引顺序一致
*最后使用覆盖索引(尽量查询具体字段,不要使用select )
2.慢查询日志
是什么
MySQL的慢查询日志是MySQL提供的一种日志记录,用来记录MySQL中响应时间超过阀值得语句,具体只运行时间超过long_query_time值得SQL,则会被记录到慢查询日志中,long_query_time的默认值为10(单位秒),且默认慢查询日志是关闭的
怎么用
##查看是否开启
show variables like '%slow_query_log%';
##开启慢查询日志
set global slow_query_log=1;
##查看慢查询默认时间
show variables like '%long_query_time%';
##修改慢查询日志阀值
set long_query_time = 0.1;
show processlist (查看进程列表,可以通过kill [id]命令删除查询长的的进程)
5.全局查询日志
#开启全局查询日志(切记在生产环境启用此功能)
##查看是否开启
show variables like '%general_log%';
##开启全局查询日志
set global general_log = 1;
##设置以表格形式存储SQL,此项会将记录存储到mysql.general_log表中
set global log_output ='table';