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 mysqldor ‘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';
1844
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
