MySql高级
配置文件
配置文件所在位置
/etc/mysql/my.cnf设置字符集为UTF-8
[client] default-character-set=utf8 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci查询引擎
show engines;
逻辑架构
架构图
逻辑四层
连接层
1.当客户端访问Mysql服务器的时候,要建立TCP连接 2.经过三次握手成功建立连接后,服务器会对TCP传输过来的账号数据进行获取. 3.对账号密码进行认证 4.在权限表中对账号的权限进行认证 5.TCP分配专门的线程做与客户端的交互服务层
SQL接口 1.接受用户的SQL命令 2.返回用户需要查询的结果 Parser解析器 1.对用户SQL语法进行解析 2.将SQL语法分解成数据结构 3.进行语法优化 4.讲数据结果传给后续步骤 Optimizer查询优化器 1.确定使用什么索引进行查询 2.生成SQL语句的执行计划 Caches&Buffers查询缓存组件 1.缓存一条SQL语句的返回结果引擎层
1.插拔式的存储引擎 2.负责数据的存储和提取 3.通过API与存储引擎进行通信存储层
1.数据库所有的数据都是存储在文件系统上面的 2.存储层与存储引擎进行交互小结
1.连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端 2.SQL层(服务层)：对 SQL 语句进行查询处理,与数据库文件的存储方式无关 3.存储引擎层：与数据库文件打交道，负责数据的存储和读取
工作流程
1.建立连接 MySQL 客户端通过协议与 MySQL 服务器建连接，发送查询语句。 2.查询缓存 ===>如果在查询缓存中发现了相同的SQL语句,会将缓存的结果直接返回 ===>因为查询缓存的命中率不高,所以在8版本去掉了 3.解析器 ===>对SQL语句进行语法解析,判断用户的SQL是否满足SQL语法 ===>如果用户语法正确,会生对应的语法树 4.优化器 ===>确定SQL的执行路径,是全文检索还是索引检索 ===>优化器可以分为逻辑查询和物理查询优化 ===>生成执行计划 5.执行器 ===>根据执行计划调用存储引擎接口 ===>将满足条件的结果集返回
存储引擎
查看存储引擎
查看MySQl支持的引擎
show engines;查看默认的存储引擎
show variables like "%storage_engine";
引擎分类
1.InnoDB ==> mysql 5.5 版本之后默认使用InnoDB引擎 ==> 用来处理短期事务,可以确保事务的完整提交和回滚 ==> 除了增查操作,还需要更新和删除操作,优先选择 InnoDB 存储引擎。 2.MyISAM ==> mysql 5.5 版本之前默认使用MyISAM引擎 ==> 提供了全文索引,压缩,空间函数等功能 ==> 不支持事务和行级锁,奔溃后无法进行安全恢复 ==> 数据文件结构：- .frm 存储定义表 - .MYD 存储数据 - .MYI 存储索引 3.Archive ==> 只支持添加和查询操作 4.Blackhole ==> 没有实现任何存储机制,丢弃所有插入的数据,不做任何保存 ==> 但服务器会记录日志,用于复制数据到备库，或者简单地记录到日志。 5.CSV ==> 不支持索引 ==> 存储的数据可以直接放在操作系统中,可以直接用文本编辑器或者Excel读取 6.Memory ==> 如果需要快速地访问数据，且这些数据不会被修改，重启以后丢失也没有关系，那么使用 Memory 表是非常有用。 ==> Memory 表至少比 MyISAM 表要快一个数量级。InnoDB与MyISAM对比
对比项 MyISAM InnoDB 外键 不支持 支持 事务 不支持 支持 行表锁 支持表锁
即使操作一条记录也会锁住整个表，
不适合高并发操作支持行锁
操作时只锁某一行，不对其它行有影响，
适合高并发操作缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，
对内存要求较高，
而且内存大小对性能有决定性的影响系统提供预创建数据库表
给用户使用是 否 关注点 性能：节省资源、消耗少、简单业务 事务：并发写、事务、需要更大资源 默认安装 是 是 默认使用 5.5版本前 5.5版本开始
索引语法
概念
索引类型 索引特点 单列索引 即一个索引是只根据一个字段创建的，里面只包含单个列，
一个表可以有多个单值索引（也叫单列索引）联合索引 即一个索引包含多个列（也叫多值索引、多列索引） 唯一索引 索引列的值必须唯一，但允许有空值，空值可以有多个 主键索引 设定为主键后数据库会自动建立索引，InnoDB 为聚簇索引 基本操作语法
创建单值索引
create index 索引名 on 字段所在表(字段);创建唯一索引
create unique index 索引名 on 字段所在表(字段);联合索引
create index 索引名 on 字段所在表(字段1,字段2);删除索引
drop index 要删除的索引名 on 索引所在的表;
创建索引的情况
需要创建索引的情况
1.主键自动建立唯一索引 2.频繁作为查询条件的字段应该创建索引 3.表之间关联查询的情况 -涉及的表不能大于3张以上 -给大表的关联字段创建索引 -关联字段在各个表中的类型要一致 4.因为一条SQL语句只能用到一个索引,所以多个字段查询的应创建组合索引而不是单列索引 5.查询中排序的字段 6.查询中统计和分组的字段下列情况创建索引效果更好
1.字段的数值有唯一性限制 2.类型小（字节宽度小）的字段不要创建索引的情况
1.数据库表中记录数量小于 300 万条时,创建索引也不会让搜索速度有明显提升。 2.经常增删改的表,建立索引是为了提高查询的速度.建立索引会降低更新表的速度,因为更新表要重排索引,重排索引要全表扫描,所以非常的耗时间 3.条件,排序,分组查询都用不到的字段 4.无法排序的字段 5.不要创建重复的索引
索引原理
数据页
1.InnoDb引擎最小的存储单位是页,不是所有页都是数据页,只有存放原始数据的页称为数据页 2.一页默认大小为16KB,假设一条记录占1KB,那一个数据页大概能存16条记录一个数据库需要很多数据页来存放数据
数据页内部
主键排序
数据页内部是根据主键来排序的,当我们在数据页中查找数据时是根据二分法来查找的数据页编号
一个数据库有很多数据页,每个数据页都有自己的唯一编号,相当于这个数据页的地址
数据页如何便于查找
1.排序 ==> 根据主键值对所有数据页的数据进行排序 2.双向链表 ==> 将所有的数据页组成双向链表
数据页设定目录
目录
为了能够更快速的查找到数据,我们可以给数据页设置目录第一层目录
1.如何设置? ==> 将一个数据页中最小主键值和数据页编号组成一个目录页 ==> 目录页因为主键值,所以目录页中的记录是有序的 2.搜索 ==> 假设目录页中有3条记录,依次最小主键为1,11,21,这样就有三个区间[1,11),[11,21),[21, ...) 情景: 1.搜索主键值为11的数据 2.11在[11,21)中,因为[11,21)区间对应的数据页码为2 3.进入页码为2的数据页,在数据页内部根据二分法进行主键的查找 小贴士💖 1.不管是目录页还是数据页都是页,默认大小都是16KB 2.一个主键值8字节,一个页码8个字节,一个目录页大概能存1024条记录第二层目录
1.如何设置？ ==> 为了更进一步的查找，我们可以给目录页再设置目录页，多层级的目录页 2.搜索 用户搜索的主键值是：60。现在 13 号目录页中包含的区间： - [1,51) - [51,150) - [150,...) 所以 60 会落在 [51,150) 区间，所以继续查找页码为 8 的目录页： - [51,81) - [81,100) - [100,...) 所以 60 会落在 [51,81) 区间，所以继续查找页码为 5 的数据页，根据主键执行二分法查找。
树型结构
1.示意图
2.对接概念
1.B+Tree 上图其实就是B+Tree的结构,多层目录页和最终的数据页组成,每页,无论是目录页还是数据页都是一个节点 2.聚簇索引 聚簇索引就是将主键作为索引值,且树形结构中存在数据页[原始数据]3.B+Tree 能够存储的记录数量
1.当结构只有一层的时候 ==> 只有一个节点,并且这个节点是根节点,并且这个节点是数据页 ==> 一个数据页默认大小16kb,假设一条记录占1kb,那一个数据页能存储16条数据 2.当结构有4层时 ==> 根节点是目录页,目录页一条记录占16B(主键8B+数据也页码8B),所以一页目录页16kb/16b=1024 ==> 最后一层一定为数据页,数据页一页大概能存16条数据,那四层能存: 1024*1024*1024*16≈170亿4.B+Tree 层次对性能的影响
1.根节点数据常驻在内存中 2.其他层的数据常驻于磁盘 3.访问第一层下一层的节点会进行一次IO,访问硬盘 4.所以层数越少,IO次数就越少,性能就越好5.BTree 为什么高瘦？
1.BTree 的每个节点都是数据页 2.数据页因为存的都是数据,所以条数很少,按照每条记录1kb,大概只能存16条记录 3.而B+Tree因为节点有目录页,目录页一条记录由『主键8B+页码8B』组成,一条记录16B,一页存1024条,所以宽 4.因为BTree一个节点容纳条数少,所以数据量大的话,只能一层一层向下分,导致深度增加 5.对于查询来说,深度增加一层,就会导致IO次数+1,所以BTree的性能不如B+Tree6.BTree 为什么每个节点都存原始数据？
因为BTree是将所有的主键排序,父节点出现过的主键不会在子节点中出现,所以每个主键只能出现在一个节点中,而每个节点都有对应的数据,所以每个节点都要存储数据,每个节点都是数据页7.BTree 和 B+Tree 的区别总结
1.BTree节点没有目录页,全是数据页 2.BTree节点存储主键值,原始数据地址值,数据页页码引用 3.BTree每个主键值只能在一个节点上出现,所以每个节点都需要保存原始数据 4.BTree每个节点都要保存原始数据,所以一个节点保存的记录时很少,导致保存的子节点引用也就很少 5.BTree的层数要比B+Tree多得多8.有数据表和索引表之分吗？
这要看索引表具体指的是哪种类型的索引。 - 聚簇索引：那就没有额外的数据表，数据本身就是按照 B+Tree 的形式组成了聚簇索引——以主键作为索引值。 - 非聚簇索引：非聚簇索引相对于聚簇索引来说可以称之为是另外一张表。
B+Tree 和 BTree图示
BTree
B+Tree
非主键字段创建索引
1.不同数据类型的搜索方式
#数值类型:用这个数值字段本身值作为索引 select emp_id,emp_name,emp_age from t_emp where emp_age=20; #字符串类型:用这个字符串字段本身值作为索引 select emp_id,emp_name,emp_age from t_emp where emp_name='tom'; #较长的字符串类型:用字符串开头一部分值作为索引 select emp_id,emp_name,emp_age from t_emp where emp_decs like "I come from UK%";2.单列索引
一个索引只包含单列字段,一个表可以有多个单列索引 存储结构 当前节点页码值+一条记录(索引值[索引字段对应的值],字段所在的主键)3.多列索引(组合索引)
顾名思义,多个字段值组成的索引,叫多列索引.4.回表
我们以非聚簇索引只能查找到记录对应的主键值,所以我们任要回到聚簇索引中再查一遍,这个过程叫做回表操作,也就是我们根据非聚簇索引列的值查询一条完整的用户原始数据记录需要用到两颗B+树 为什么要进行回表操作,直接把原始数据放到非聚簇索引的叶子节点不行吗? 1.直接放是可以,但是太占用地方了,那每颗B+树都要把用户原始数据拷一边,太占用空间了 2.会导致数据不一致,在保存两条数据的情况下,我们执行uodate操作,不能只改一个,两个都要改,浪费性能.
索引的分类
Mysql索引包括普通索引,唯一索引,全文索引,多列和单列索引以及空间索引 - "功能逻辑" 普通索引、唯一索引、主键索引和全文索引。 - "物理实现" 聚簇索引,非聚簇索引 - "字段个数" 单列和多列索引1.普通索引
任何数据类型都可以创建普通索引,普通索引允许出现相同的索引内容,允许Null值2.唯一索引
在这个字段中的内容唯一,我们可以根据这个字段创建唯一索引,不可以出现相同的数据内容,但可以为Null值3.主键索引
其实就是特殊的唯一索引,增加一个不能为Null的限制4.单列索引
在单个字段上面创建的索引,可以是普通索引,唯一索引,主键索引,也可以是全文索引,一个表可以有多个单列索引5.多列索引
多个字段创建组合索引,在实际查询中只会最左字段的索引6.全文索引
目前是搜索引擎使用的关键技术,利用了分词技术,按照一定算法智能筛选出我们想要的结果,允许列中存在重复值和Null值,全文索引只能创建在char,varchar,text类型和其系列类型上,查询数据量很大的字符串类型的字段的时候,使用全文索引可以加快查询速度,全文索引技术也叫倒排索引技术. - 自然语言的全文索引 计算数据查询的相关度,相关度是基于关键词的个数和关键词在文档中出现的次数,在搜索记录中出现次数越少的词,匹配度就越高,反而一个词在50%的搜索记录中都存在,那自然语言搜索不会搜索这类词语 - 布尔全文索引 可以自定义搜索的词语的相关性 随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr、elasticSearch等专门的搜索引擎所替代。7.空间索引
空间索引建立在空间数据类型上,可以提高系统获取空间数据的效率,Mysql中空间数据类型包括geometry[几何]、point[坐标点]、linestring[线]和polygon[多边形]等,目前只有MyISAM支持空间索引,索引的字段不能Null.
SQL优化
慢查询日志
在实际的开发过程中,我们需要尽量准确的将查询消耗时间比较久的SQL语句找到,然后再针对性的对其建立索引,再使用explain命令查看这个sql语句的执行详情,查看有没有用到索引,有没有做全表扫描等,对由此分析出的结果,我们进行优化.1.日志开启,设置阈值
#默认情况下slow_query_log的值为OFF SHOW VARIABLES LIKE '%slow_query_log%'; #开启慢日志 set global slow_query_log=1; #慢查询日志阈值[超过这个时间的sql将被记录在日志中] SHOW VARIABLES LIKE '%long_query_time%'; SHOW GLOBAL VARIABLES LIKE 'long_query_time'; SET GLOBAL long_query_time=0.1;#如果希望修改后永久生效，那就修改my.cnf文件，[mysqld]下增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下四行配置进my.cnf文件 slow_query_log=1 slow_query_log_file=/var/lib/mysql/atguigu-slow.log long_query_time=3 log_output=FILE2.查看慢查询记录
#查看系统中有多少条慢查询记录 SHOW GLOBAL STATUS LIKE '%Slow_queries%'; #查看日志信息内容 #去我们日志存放路径下,用cat命令查看其内容3.使用mysqldumpslow命令导出
#得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log #得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log #得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log #另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more-a: 不将数字抽象成N，字符串抽象成S -s: 是表示按照何种方式排序； c: 访问次数 l: 锁定时间 r: 返回记录 t: 查询时间 al:平均锁定时间 ar:平均返回记录数 at:平均查询时间 -t: 即为返回前面多少条的数据； -g: 后边搭配一个正则匹配模式，大小写不敏感的；
索引失效
最左原则
1.创建索引
CREATE INDEX idx_age_deptid_name ON emp(age, deptid, NAME);-查询 age 索引生效 -查询 age 和 deptId 索引生效 -查询 age、deptId 和 name 索引生效 -查询 deptId、name 和 age 索引生效,但原本遵循最左原则是不生效的,但SQL的优化器调整了解析树,使得查询字段符合了索引的顺序,使得索引生效 -仅查询 deptId 索引失效,违背最左原则2.为什么MySQL 会如此执着于『最左』字段？
1.因为在B+Tree创建的时候,需要对索引值进行排序,如果我们指定的是联合索引,那就要涉及到多个字段的排序,排序的时候肯定是优先根据最左字段进行排序,当最左字段值有相同数据时再对后一个字段进行排序,以此类推. 2.在实际的查询中如果查询条件没有提供最左字段,那么联合索引将不会生效,因为我们首先要根据最左字段通过二分法进行查找.3.结论
要遵循最左原则，查询字段中至少要有索引中的最左字段作为过滤条件存在。而且就最左原则本身来说，它要求索引最左字段在查询顺序中也最左。只不过只要最左字段出现，优化器会帮我们调整到查询顺序中的最左。而且还有一个要求是：中间不能断。中间一旦断开，后面的就都无法使用索引了。 口诀：带头大哥不能死，中间兄弟不能断
索引失效的其他情况
1.范围查询
- 注意点1：创建索引时应该把容易做范围查询的字段往后放 - 注意点2：实际编写 SQL 语句时，做范围查询的字段参照索引中的顺序，也是往后放2.where中使用函数
3.不等于
所有不等于操作都会导致索引失效： - != - <> - not4.like
like 条件把 % 放左边不生效,其他都生效5.类型转换
#我们给查询条件传入的参数和原本的类型不一致。但是这种情况不是必然会导致索引失效。 where name=123;
小结
1.对于单列索引,我们尽量来选择过滤性较好的字段来创建索引,比如手机号,一下能过滤掉很数据 2.对于组合索引,我们尽量使过滤性较好的字段位置靠前,这样可以提前过滤掉很多数据 3.对于组合索引出现了范围查询的时候,我们尽量将范围字段放到最后 4.我们要尽量避免索引失效的情况 5.创建索引字段的时候,我们尽量选择经常查询的字段,并字段值很少改动.因为字段值一改,索引也要跟着修改 6.当一个特殊的查询场景包含多个字段的时候,创建组合索引比单列索引查询快. 7.大部分情况下,一条SQL语句只能用到一个索引,所以索引并不是创建的越多越好.
关联查询优化
小表驱动大表 - 小表：相对来说记录较少的表 - 大表：相对来说记录较多的表 驱动方式识别 - left join：左边驱动右边（此时把小表放在左边） - right join：右边驱动左边（此时把小表放在右边） 加索引的方式 - 通常建议在大表（被驱动）的关联字段上加索引，效率提升更明显。
子查询优化
在实际开发中，能够不用子查询就尽量不用。
排序分组优化
排序
- order by 子句需要配合 limit 子句才能让索引生效。 - 如果涉及多个字段的排序，那么这些字段排序的方向要一致（要升序都升序，要降序都降序），否则索引失效。分组
能写在 where 限定的条件就不要写在 having 中,实现尽早过滤
视图
是什么
1.视图是一张虚表,本身不具有数据 2.视图表建立在已有的基表上 3.视图创建和删除不会影响基表 4.视图数据的更改,基表数据会响应更改,反之亦然.
为什么
1.帮助我们使用表的一部分 2.提供给用户部分的数据 3.保护敏感字段
视图操作
创建视图
CREATE VIEW 视图名称 AS 查询语句查看视图详情信息
SHOW CREATE VIEW 视图名称;删除视图
DROP VIEW IF EXISTS 视图名称;更新,删除视图数据
将 from 后的表名换成视图名
总结
优点
1. 简化操作 经常查询的操作,创建为一个视图,这样的话以后只用对视图进行操作即可,不用再去关心视图对应的基表的结构,表和表的关联关系,简化开发过程. 2. 减少数据冗余 视图本身不存储数据,只存储查询语句,用过查询语句获取结果集,不占用存储的资源,减少数据冗余. 3. 保护数据安全 用户对数据的访问限制在视图上,视图再通过查询语句获取基表的结果集,避免了用户直接访问基表,保障了数据的安全性. 4. 适应多变的需求 业务需求发生变化,需要改动基表的结构,工作量比较大,可以使用视图减少改动量不足
1.如果基表的结构变了，我们就需要及相关的视图进行相应的维护. 2.实际项目中，如果视图过多，会导致数据库维护成本的问题.
主从复制
是什么
主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库
为什么
1.读写分离。 2.数据备份。 3.具有高可用性。 4.提高数据库的吞吐量
搭建步骤
主机配置文件
#[必须]主服务器唯一ID server-id=1 #[必须]启用二进制日志,指名路径。比如：自己本地的路径/log/mysqlbin log-bin=atguigu-bin #[可选] 0（默认）表示读写（主机），1表示只读（从机） read-only=0 #[可选]设置日志文件保留的时长，单位是秒 binlog_expire_logs_seconds=6000 #[可选]控制单个二进制日志大小。此参数的最大和默认值是1GB max_binlog_size=200M #[可选]设置不要复制的数据库 binlog-ignore-db=test #[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave binlog-do-db=需要复制的主数据库名字 #[可选]设置binlog格式 binlog_format=STATEMENT从机配置文件
#[必须]从服务器唯一ID server-id=2 #[可选]启用中继日志 relay-log=mysql-relay重启Mysql服务
注意：主从机都关闭防火墙,或开放端口号 systemctl stop firewalld.service #CentOS 7主机:建立账户并授权
#在主机MySQL里执行授权主从复制的命令 GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'从机器数据库IP' IDENTIFIED BY 'abc123'; #5.5,5.7主机查看同步状态
show master status;从机:配置需要复制的主机
#客户端执行命令 CHANGE MASTER TO MASTER_HOST='主机的IP地址', MASTER_USER='主机用户名', MASTER_PASSWORD='主机用户名的密码', MASTER_LOG_FILE='mysql-bin.具体数字', MASTER_LOG_POS=具体值;从机启动slave同步
#客户端执行命令 START SLAVE;从机查看同步状态
#客户端执行命令 SHOW SLAVE STATUS\G;
MyCat
垂直拆分-分库
是什么
1.将数据库中的表按照业务逻辑拆分到不同的数据库中. 2.垂直拆分:对表做分类
为什么
1.不同业务逻辑的表在相同的数据库中,会加大数据库的压力. 2.垂直拆分数据表,降数据库的压力.
水平拆分-分表
是什么
1.按照字段,将一个表拆分为多个表 2.水平拆分:按照字段将表分散到各个库中
为什么
1.当一张表的数据达到千万时,查询一次花费的时间会很久,分表可以缩短查询时间. 2.Mysql具有锁机制,当对表或者行进行操作时,其他用户就不能操作表或行,分表可以加大可操作性
浙公网安备 33010602011771号