目录
2)系统管理和控制工具(Management Services & Utilities)。
(3)存储引擎层(Pluggable Storage Engines)。
[2]、通用查询日志(General query log)。
[5]、ib_logfile0文件、ib_logfile1文件。
8)Mrg_MyISAM存储引擎,合并MyISAM表存储引擎。
1)在使用不等于“!=”、“<>”的时候,无法使用索引会导致全表扫描。
1)当b表的数据量小于a表的数据量时,用in优于exists。
2)当a表的数据量小于b表的数据量时,用exists优于in。
11、使用order by排序时用上索引,order by排序关键字优化。
(1)order by子句尽量使用Using index方式排序,避免使用Using filesort方式排序。
1)使用order by时,不写select *,写查询需要的具体字段。
3)提高max_length_for_sort_data的大小。
1、MySQL数据库逻辑架构。
MySQL数据库逻辑架构图
(1)网络连接层。
客户端连接器(Client Connectors)。
提供与MySQL服务器建立连接的支持。最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端实现的类似tcp/ip的通信。
目前几乎支持所有主流的服务端编程技术,例如常见的 Java、.NET、PHP、Python、C等,它们通过各自API技术与MySQL建立连接。
同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
(2)服务层(MySQL Server)。
服务层是MySQL Server的核心,主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存和缓冲池。
1)连接池(Connection Pool)。
负责存储和管理客户端与数据库的连接,一个线程负责管理一个连接。
提供一些类似于连接处理、授权认证、及相关的安全方案,为通过认证安全接入的客户端提供线程。
2)系统管理和控制工具(Management Services & Utilities)。
例如:备份恢复、安全管理、集群管理等
3)SQL接口(SQL Interface)。
用于接受客户端发送的各种SQL命令,并且返回用户需要查询的结果。
例如:DML、DDL、存储过程、视图、触发器等。
4)解析器(Parser)。
解析器将请求的SQL解析生成一个“解析树”,然后根据MySQL的语法规则检查解析树是否合法。
5)查询优化器(Optimizer)。
当“解析树”通过解析器语法检查后,将交给优化器将其转化成执行计划,然后与存储引擎交互。
6)缓存和缓冲池(Cache&Buffer)。
缓存机制是由一系列小缓存组成的。例如:表缓存,记录缓存,权限缓存,引擎缓存等。
如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。
第二层架构主要完成大多数的核心服务功能,例如SQL接口,并且完成缓存的查询,SQL的分析和优化及部分内置函数的执行。
所有跨存储引擎的功能也在这一层实现,如过程、函数等。在该层,服务器会解析查询并且创建相应的内部解析树,并且对其完成相应的优化。
例如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存。
如果缓存空间足够大,这样在解决大量读操作的环境环境中能够很好的提升系统的性能。
(3)存储引擎层(Pluggable Storage Engines)。
存储引擎负责MySQL中数据的存储和提取,与底层系统文件进行交互。MySQL存储引擎是插件式的,服务器中的查询执行引擎通过API接口与存储引擎进行通信,接口屏蔽了不同存储引擎之间的差异,不同的存储引擎具有的功能不同,可以根据需要进行选取。最常见的存储引擎是InnoDB和MyISAM。
(4)系统文件层(File System)。
系统文件层负责将数据、日志存储在文件系统上,并且完成与存储引擎的交互,是文件的物理存储层。
主要包含日志文件、数据文件、配置文件、pid 文件、socket 文件等。
1)日志文件。
[1]、错误日志(Error log)。
log-error错误日志记录严重的警告信息、错误信息、启动与关闭详细信息。
#查看错误日志存放路径等。
show variables like '%log_error%'
[2]、通用查询日志(General query log)。
log查询日志记录一般的SQL查询语句。可以与慢查询日志结合使用,分析查询慢的SQL语句。
#查看是否开启记录日志,通用查询日志存放路径。
show variables like '%general%';
[3]、二进制日志(binary log)。
记录对MySQL数据库执行更改操作的SQL语句,并且记录了SQL语句的发生时间、执行时长。
不记录select、show等不修改数据库记录的SQL语句。
log-bin二进制日志主要用于数据库恢复和主从复制。
#查看是否开启记录日志,binlog日志存放路径等。
show variables like '%log_bin%';
#查看binlog配置参数。
show variables like '%binlog%';
#查看有哪些binlog日志文件。
show binary logs;
[4]、慢查询日志(Slow query log)
记录所有执行时间超时的查询SQL语句。
#查看是否开启,慢查询日志存放路径。
show variables like '%slow_query%';
# 查看配置的SQL语句执行超时的时间。默认设置是10秒。
show variables like '%long_query_time%';
2)配置文件。
用于存放MySQL所有的配置信息文件,比如my.cnf、my.ini等。
window系统是my.ini配置文件,Linux系统是my.cnf配置文件。
3)数据文件。
[1]、MYD格式文件。
MyISAM存储引擎专用,存放表数据。存放MyISAM表的data数据,每一张表都会有一个.MYD文件。
[2]、MYI格式文件。
MyISAM存储引擎专用,存放表索引。存放MyISAM表的索引相关信息,每一张 MyISAM表对应一个.MYI文件。
[3]、ibd文件、IBDATA文件。
存放InnoDB 的数据文件,包括索引。
InnoDB 存储引擎有两种表空间方式:独享表空间和共享表空间。
独享表空间使用.ibd文件来存放数据,且每一张InnoDB表对应一个.ibd文件。
共享表空间使用.ibdata文件,所有表共同使用一个或多个.ibdata文件。
[4]、ibdata1文件。
系统表空间数据文件,存储表元数据、Undo日志等。
[5]、ib_logfile0文件、ib_logfile1文件。
Redo log日志文件。
[6]、frm格式文件。
存放表的结构。存储与表相关的meta元数据信息,包括表结构的定义信息等,每一张表都会有一个frm文件。
mysql5.x有frm格式文件,mysql8.x没有frm格式文件。
MySQL8.0删除了原来的frm文件,MySQL8.0重新设计数据词典后,引入了SDI(Serialized Dictionary Information),统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中,可以使用ibd2sdi工具查看表结构信息。
[7]、db.opt文件。
记录这个库的默认使用的字符集、校验规则。
mysql5.x有db.opt文件,mysql8.x没有db.opt文件。
4)pid文件。
存放mysql的进程id。pid文件是mysqld应用程序在Linux环境下的一个进程文件。
5)socket文件。
socket文件是在Linux环境下的文件。在Linux环境下,用户客户端可以使用Unix Socket连接MySQL,不使用TCP/IP网络连接。
2、存储引擎介绍。
(1)查看存储引擎。
-- 查看mysql版本号。
SELECT VERSION();
-- 查看mysql支持的存储引擎。
show engines;
-- 查看当前默认的存储引擎。
show variables like '%storage_engine%';
(2)MyISAM与InnoDB的对比。
对比项 |
MyISAM |
InnoDB |
主外键 |
不支持 |
支持 |
事务 |
不支持 |
支持 |
行表锁 |
表锁,操作一条记录会锁住整个表,不适合高并发操作。 |
行锁,操作一条记录锁住某一行,不对其它行有影响。 适合高并发的操作。 |
缓存 |
只缓存索引,不缓存真实数据。 |
缓存索引和真实数据。 |
表空间 |
小 |
大 |
关注点 |
性能 |
事务 |
(3)几种存储引擎的对比。
特点 |
InnoDB |
MyISAM |
Memory |
MERGE |
NDB |
Archive |
存储限制 |
64TB |
有 |
有 |
没有 |
有 |
没有 |
事务安全 |
支持 |
支持 |
||||
锁机制 |
行锁(适合高并发) |
表锁 |
表锁 |
表锁 |
行锁 |
行锁 |
B树索引 |
支持 |
支持 |
支持 |
支持 |
支持 |
|
哈希索引 |
支持 |
支持 |
||||
全文索引 |
5.6版本之后支持 |
支持 |
||||
集群索引 |
支持 |
|||||
数据索引 |
支持 |
支持 |
支持 |
|||
索引缓存 |
支持 |
支持 |
支持 |
支持 |
支持 |
|
数据可压缩 |
支持 |
支持 |
||||
磁盘空间使用 |
高 |
低 |
N/A |
低 |
低 |
|
内存使用 |
高 |
低 |
中等 |
低 |
高 |
低 |
批量插入速度 |
相对低 |
高 |
高 |
高 |
高 |
非常高 |
支持外键 |
支持 |
(4)MySQL各种存储引擎介绍。
1)InnoDB存储引擎。
[1]将数据存储在表空间中,表空间由一系列的数据文件组成,由InnoDB管理。
[2]支持每个表的数据和索引存放在单独文件中innodb_file_per_table。
[3]支持事务,采用MVCC来控制并发,并实现标准的4个事务隔离级别,支持外键。
[4]索引基于聚簇索引建立,对于主键查询有较高性能。
[5]数据文件的平台无关性,支持数据在不同的架构平台移植。
[6]能够通过一些工具支持真正的热备。如XtraBackup等。
[7]内部进行自身优化如采取可预测性预读,能够自动在内存中创建hash索引等。
2)MyISAM存储引擎。
[1]MySQL5.1中默认,不支持事务和行级锁。
[2]提供大量特性如全文索引、空间函数、压缩、延迟更新等。
[3]数据库故障后,安全恢复性差。
[4]对于只读数据可以忍受故障恢复,MyISAM依然非常适用。
[5]日志服务器的场景也比较适用,只需插入和数据读取操作。
[6]不支持单表一个文件,会将所有的数据和索引内容分别存在两个文件中。
[7]MyISAM对整张表加锁而不是对行,所以不适用写操作比较多的场景。
[8]支持索引缓存不支持数据缓存。
3)Archive存档、归档存储引擎。
[1]只支持insert和select操作。
[2]缓存所有的写数据并进行压缩存储,支持行级锁但不支持事务。
[3]适合高速插入和数据压缩,减少IO操作,适用于日志记录和归档服务器。
4)Blackhole黑洞存储引擎。
[1]没有实现任何存储机制,会将插入的数据进行丢弃,但会存储二进制日志。
[2]会在一些特殊需要的复制架构的环境中使用。
5)CSV文件格式存储引擎。
[1]可以打开CSV文件存储的数据,可以将存储的数据导出,并使用Excel打开。
[2]可以作为一种数据交换的机制,同样经常使用。
6)Memory内存存储引擎。
[1]将数据在内存中缓存,不消耗IO。
[2]存储数据速度较快但不会被保留,一般作为临时表的存储被使用。
7)Federated存储引擎。
[1]能够访问远程服务器上的数据的存储引擎。
[2]能够建立一个连接连到远程服务器。
8)Mrg_MyISAM存储引擎,合并MyISAM表存储引擎。
[1]将多个MyISAM表合并为一个。
[2]本身并不存储数据,数据存在MyISAM表中间。
9)NDB集群引擎。
MySQL Cluster,MySQL集群专用。
3、SQL性能下降原因。
(1)查询语句写的烂。
(2)索引失效。
(3)关联查询太多join连接,设计缺陷或者不得已不合理的需求。
(4)服务器调优及各个参数设置,缓冲、线程数设置不当。
导致mysql性能下降,sql执行时间长,等待时间长。
4、SQL语句执行顺序。
执行顺序 |
SQL |
备注 |
7 |
SELECT |
从虚拟表中选择出我们需要的内容。 |
8 |
DISTINCT <select_list> |
对进行distinct操作的列增加一个唯一索引,用来移除相同的行。 |
1 |
FROM <left_table> |
执行一个笛卡尔乘积。 |
3 |
<join_type> JOIN <right_table> |
如果是outer join【left outer join、right outer join、full outer join】那么这一步就将添加外部行。 |
2 |
ON <join_condition> |
on筛选器筛选出满足on逻辑表达式的行。 |
4 |
WHERE <where_condition> |
对上一步产生的虚拟表使用用where筛选器,生成新的虚拟表。 |
5 |
GROUP BY <group_by_list> |
开始使用select中的别名,后面的语句中都可以使用。 |
6 |
HAVING <having_condition> |
group by与having之间可以执行avg,sum....等函数。 |
9 |
ORDER BY <order_by_condition> |
对虚拟表中的内容按照指定的列进行排序,生成一个游标。 |
10 |
LIMIT <limit_number> |
从上一步得到的虚拟表中选出从指定位置开始的指定几行数据。 |
笛卡尔乘积:包含两个集合中任意取出两个元素构成的组合的集合。
假设集合A={a, b},集合B={0, 1, 2},则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
MySQL多表查询的笛卡尔积原理。
(1)首先确定数据要用到哪些表。A、B表。
(2)其次将多个表先通过笛卡尔乘积变成一个表。AxB。
(3)然后根据两个表的关系去掉不符合逻辑的数据。on a.x= b.x。
(4)最后当做是一个虚拟表加上条件筛选出数据即可。where。
5、7种join连接查询。
(1)7种join连接查询。
名称 |
图示 |
查询sql |
内连接查询 |
select <select_list> from tableA A inner join tableB B on A.key = B.key |
|
左连接查询 |
select <select_list> from tableA A left join tableB B on A.key = B.key |
|
右连接查询 |
select <select_list> from tableA A right join tableB B on A.key = B.key |
|
查询a表独有的记录。 |
select <select_list> from tableA A left join tableB B on A.key = B.key where B.key is null |
|
查询b表独有的记录。 |
select <select_list> from tableA A right join tableB B on A.key = B.key where A.key is null |
|
全连接查询 |
select <select_list> from tableA A full outer join tableB B on A.key = B.key |
|
查询a、b独有的记录。 |
select <select_list> from tableA A full outer join tableB B on A.key = B.key where A.key is null or B.key is null |
(2)7种join连接查询示例。
select * from tbl_dept;
select * from tbl_emp;
select * from tbl_dept, tbl_emp;
-- 内连接查询
select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
-- 左连接查询
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
-- 右连接查询
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
-- 左连接查询,查询a表独有的记录。
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
-- 右连接查询,查询b表独有的记录。
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
-- 全连接查询(mysql不支持full outer join全连接查询)
select * from tbl_emp a full outer join tbl_dept b on a.deptId = b.id;
-- 全连接查询1,查询a、b所有的记录。(union去重)
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
union
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
-- 全连接查询2,查询a、b所有的记录。(union all不去重)
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
union all
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
-- 全连接查询3,查询a、b独有的记录。
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
union
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
6、索引。
(1)索引。
索引Index是帮助mysql高效获取数据的数据结构。
提高查询效率,类比字典。
排好序的快速查找数据结构,着重于查找和排序两种功能。
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
索引用于提高查询和排序效率。
为了加快Col2的查找,可以维护一个二叉树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,可以运用二叉查找在一定的复杂度内获取到相应数据,快速检索出符合条件的记录。
平常所说的索引,如果没有特别指明,都是指B树结构组织的索引,多路搜索树,并不一定是二叉树。
其中聚焦索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称为索引。
除了B+树索引外,还有哈希索引hash index等。
(2)索引优势、劣势。
优势;提高数据检索的效率,降低数据库的IO成本。通过索引列队数据进行排序,降低数据排序的成本,降低CPU的消耗。
劣势:索引是一张表,保存了主键与索引字段,指向实体表的记录,索引占用空间。索引降低更新表的速度,对表进行insert、update、delete时,mysql不仅需要保存数据,还要保存更新索引信息。
(3)索引分类。
1)单值索引。
一个索引只包含单个列字段,一个表可以有多个单列索引。
建议一张表的索引数量不要超过5个。
2)唯一索引。
索引列字段的值必须唯一,充许NULL空值。
3)复合索引。
一个索引包含多个列字段。
4)基本语法。
-- 创建索引1
CREATE [UNIQUE] INDEX indexName ON tableName(columnName);
-- 创建索引2
ALTER TABLE tableName ADD [UNIQUE] INDEX [indexName](columnName);
-- 删除索引
DROP INDEX [indexName] ON tableName;
-- 查看表有哪些索引
SHOW INDEX FROM tableName;
-- 创建主键索引。索引值必须唯一,不能为NULL。
ALTER TABLE tableName ADD PRIMARY KEY(column_list);
-- 创建唯一索引。索引值必须唯一,索引的字段可以有NULL值,可以有多条记录有NULL值。
ALTER TABLE tableName ADD UNIQUE indexName(column_list);
-- 创建普通索引。索引的字段值可以出现多次。
ALTER TABLE tableName ADD INDEX indexName(column_list);
-- 创建全文索引。
ALTER TABLE tableName ADD FULLTEXT indexName(column_list);
(4)索引结构、检索原理。
1)BTree索引。
[1]初始化介绍。
一颗b+树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),
如磁盘块1包含数据项17和35,包含指针P1、P2、P3,
P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。
[2]查找过程。
如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。
真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
2)Hash索引
3)full-text全文索引
4)R-Tree索引
(5)适合创建索引的场景。
1)主键自动建立唯一索引。
2)频繁作为查询条件的字段应该创建索引。为最经常查询、最经常排序的字段建立索引。
3)查询中与其他表关联的字段,外键关系建立索引。
4)where条件里面用不到的字段不创建索引。
5)单键索引和组合索引,在高并发情况下倾向创建组合索引。
6)查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。
7)查询中统计或分组字段。
(6)不适合创建索引的场景。
1)表中记录数量太少,官方支持单表500~800万数据,数据量达到300万时就要注意。
2)经常增删改的表。
3)频繁更新的字段不适合建立索引。
索引提高了查询速度,在insert、update、delete时,mysql不仅需要保存数据,还要保存更新索引信息,降低了更新表的速度。
4)数据重复并且分布平均的表字段。如果某个数据列中包含许多重复的内容,建立索引没有太大的实际效果。例如:为sex性别字段建立索引。
索引的选择性:指建立索引的字段中不同值的记录数与表中总记录数量的比。
一个索引的选择性越接近1,索引的效率就越高。
7、explain。
(1)explain查看执行计划。
使用explain关键字可以模拟优化器执行sql查询语句,从而知道mysql是如何处理sql语句的,分析查询语句或者是表结构的性能瓶颈。
(2)explain的作用。
1)表的读取顺序。id。
2)数据读取操作的操作类型。select_type。
3)哪些索引可以使用。possible_keys。
4)哪些索引被实际使用。key。
5)表之间的引用。
6)每张表有多少行被优化器查询。rows。
(3)explain分析的各字段解释。
1)id。
select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
[1] id都相同,都是一样的。执行顺序由上至下。
[2] id不同,id都不一样。子查询的id序号会递增,id值越大优先级越高,越先被执行。
[3] id有相同的,也有不相同的。id相同的是一组,从上往下顺序执行。在所有组中,id值越大,优先级越高,越先执行。
2)select_type。
查询的类型,主要用于区分普通查询、联合查询、子查询等的复杂查询。
[1]SIMPLE。
简单的select查询,查询中不包含子查询或者UNION。
[2]PRIMARY。
查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY,最后面被加载的那个。
[3]SUBQUERY。
在SELECT或WHERE列表中包含了子查询。
[4]DERIVED。
在from列表中包含的子查询被标记为DERIVED衍生,mysql会递归执行这些子查询,把结果放在临时表里。
table列显示的derived是衍生出的临时表,derived2,从id为2那行记录衍生出的临时表。
[5]UNION。
如果第2个select查询语句出现在UNION的后面,则被标记为UNION。
如果UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED。
[6]UNION RESULT。
从UNION表获取结果的SELECT。两个UNION合并的结果集。
3)table。
显示这一行的数据与哪一张表有关。
4)type。
访问类型排列。显示查询使用了何种类型。
type从最好的到最差的简化排序依次是:
system > const > eq_ref > ref > range > index > ALL
type从最好的到最差的详细排序依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般保证查询至少达到range级别,最好能达到ref即可。
[1] system。
表只有一行记录,相当于系统表,const类型的特例。
[2] const。
索引1次就找到了记录,只匹配1行数据。const用于比较primary key、unique索引。
例如:在where条件后面查询主键,mysql会将该查询转换成一个常量。
[3] eq_ref。
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
[4] ref。
非唯一性索引扫描,返回匹配某个单独值的所有行。可能会找到多个符合条件的行,属于查找和扫描的混合体。
[5] range。
只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。
一般是在where语句中使用了between、<、>、in等的查询。
范围扫描索引只需要开始于索引的某一点,结束于另一点,不用扫描全部索引。
[6] index。
Full Index Scan,index与ALL区别为index类型只遍历索引树,通常比ALL快,索引文件通常比数据文件小。虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读取的。
[7] all。
Full Table Scan,将遍历全表以找到匹配的行。