mysql(六)mysql相关知识

最新推荐文章于 2024-01-07 16:18:00 发布

码农-文若书生

最新推荐文章于 2024-01-07 16:18:00 发布

阅读量254

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 sql mysql修改字符集 sql优化

本文链接：https://blog.csdn.net/u011943534/article/details/122483136

版权

mysql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

一、MYSQL分层结构

在这里插入图片描述
简单来看为下面这样：

二、SQL执行流程

在这里插入图片描述

SQL的语法：

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT < limit_number >

解析顺序：

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

二、SQL优化

官网优化方法：

https://dev.mysql.com/doc/refman/5.7/en/optimization.html

1、索引

索引的弊端：

索引本身占用硬盘空间
少量数据、频繁修改的字段、很少使用的字段
降低增删改的效率

索引的好处：

提高查询效率,降低与磁盘的IO
降低CPU使用率

B+树：
B+树是应文件系统所需而产生的一种B树的变形树(文件的目录一级一级索引,只有最底层的叶子节点(文件)保存数据.),非叶子节点只保存索引,不保存实际的数据,数据都保存在叶子节点中.这不就是文件系统文件的查找吗?我们就举个文件查找的例子:有3个文件夹,a,b,c, a包含b,b包含c,一个文件yang.c, a,b,c就是索引(存储在非叶子节点), a,b,c只是要找到的yang.c的key,而实际的数据yang.c存储在叶子节点上.
所有的非叶子节点都可以看成索引部分。
B+树的性质(下面提到的都是和B树不相同的性质)

非叶子节点的子树指针与关键字个数相同;
非叶子节点的子树指针p[i],指向关键字值属于[k[i],k[i+1]]的子树.(B树是开区间,也就是说B树不允许关键字重复,B+树允许重复)；
为所有叶子节点增加一个链指针.
所有关键字都在叶子节点出现(稠密索引). (且链表中的关键字恰好是有序的);
非叶子节点相当于是叶子节点的索引(稀疏索引),叶子节点相当于是存储(关键字)数据的数据层.
更适合于文件系统;

看下图:
在这里插入图片描述

2、存储引擎

查询存储引擎

show engines

在这里插入图片描述
指定某个表的存储引擎，在建表的时候添加engine

CREATE TABLE `role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL COMMENT '角色名称',
  `role_create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `role_update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`) USING BTREE,
  UNIQUE KEY `role_name` (`role_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

默认存储引擎为InnoDB

MyISAM ：
data存的是数据地址。索引是索引，数据是数据。如下图。
在这里插入图片描述
InnoDB：
data存的是数据本身。索引也是数据。如下图。

索引的分类：

单值索引
唯一索引
复合索引

创建索引的语句：

alter table 表名 索引类型 索引名(字段)

删除索引语句

drop index 索引名 on table 表名

3、执行计划

查询执行计划

explain sql

在这里插入图片描述

id:编号
select_type:查询类型
table:表
possible_keys:预测用到的索引
key:实际使用的索引
key_len:实际使用索引的长度
ref:表之间的引用
rows：估算出找到所需的记录所需要读取的行数
extra:额外的信息

ID值一样，从大至小顺序执行；
在这里插入图片描述
ID值不一致，ID值越大越优先查询

3.1、id

ID值一样，从大至小顺序执行；
ID值不一致，ID值越大越优先查。

3.2、select_type

SELECT类型,可以为以下任何一种:

SIMPLE:简单SELECT(不使用UNION或子查询)
PRIMARY:最外面的SELECT
UNION:UNION中的第二个或后面的SELECT语句
DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT:UNION 的结果
SUBQUERY:子查询中的第一个SELECT
DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
DERIVED:导出表的SELECT(FROM子句的子查询)

3.3、table

输出的行所引用的表

3.4、type

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:

system:表仅有一行(=系统表)。这是const联接类型的一个特例。
const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
index_merge:该联接类型表示使用了索引合并优化方法。
unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range:只检索给定范围的行,使用一个索引来选择行。
index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。

3.5、possible_keys

指出MySQL能使用哪个索引在该表中找到行

3.6、key

显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。

3.7、key_len

显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。

3.8、rows

显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。

3.9、ref

显示使用哪个列或常数与key一起从表中选择行。

3.10、filtered

显示了通过条件过滤出的行数的百分比估计值。

3.11、extra

该列包含MySQL解决查询的详细信息

Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。（需要优化）（一般order by的排序字段没索引会出现）
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。(不需要回表查询)
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。(需要优化) （一般在用group by 时查询的字段不是group by字段会出现）
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。（需要回表查询）
Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

一般情况下type没有用索引或者extra中出现useing filesort或 using temporary就需要检查SQL了，可能存在优化空间。

4、SQL注意事项

多表关联查询时用小表驱动大表，小表在前。
一般情况下，左连接，给右表连接字段加索引，右连接，给左表连接字段加索引。
复合索引不要跨列或无序使用，会造成索引失效。
复合索引尽量全索引匹配。
不要在索引上进行任何操作，比如类型转换，算数运算，会造成索引失效。
复合索引左边的索引页不能使用!=， <>， is null， is not null，否则右侧的索引都会失效。
like前面带百分号会引起索引失效，如果使用索引覆盖(所有索引字段都在select的条件中)会挽回一些。
尽量不要使用类型转换。
尽量不要使用or,否则索引失效。
exists和in。如果主查询数据量大，使用in，如果子查询的数据集大，使用exists。
order by。Using filesort有两种算法：双路排序(第一次扫描排序字段，第二次扫描其他字段)、单路排序(只读一次把所有数据也读出来)。排序在buffer缓冲区中排序，如果buffer大小不够会一段段排。Mysql4.1前默认使用双路排序，后默认使用单路排序。可以调大buffer大小：set max_length_for_sort_data=4096,如果buffer太小，mysql会自动切换为双路排序。
避免使用select *。
保证排序字段一致性(全升或全降)。

三、SQL分析方法

1、慢日志

show variables like '%slow_query_log%'

在这里插入图片描述

临时开启：

set global slow_query_log=1

永久开启：
/etc/my.conf的[msqld]中追加：

slow_query_log=1
slow_query_log_file=/var/lib/mysql/logs/slow.log

慢SQL的默认时间阈值

show variables like '%long_query_time%'

在这里插入图片描述
临时修改：

set global long_query_time=2;

永久开启：
/etc/my.conf的[msqld]中追加：

long_query_time=2

查询超过阈值的SQL：

show global status like ‘%slow_queries%’

在这里插入图片描述
慢SQL记录在变量slow_query_log_file对应的路径日志中
通过mysqldumpslow工具

以下是一些例子：
获取返回记录最多个3个SQL

mysqldumpslow -s r -t 3 <日志文件路径>

获取访问次数最多的3个SQL

mysqldumpslow -s c -t 3 <日志文件路径>

按照时间排序，前10跳包含left join 查询语句的sql

mysqldumpslow -s t -t 10 \g “left join” <日志文件路径>

2、Profile

通过show variables like ‘%profi%’查看profile是否开启
在这里插入图片描述
如果没有开启，将它开启

set profiling = 1

profiles会记录SQL语句，

show profiles

在这里插入图片描述
只能看到总共消费的时间，不能看到各个硬件的消耗时间。

通过show profile all for query <query id号> 来精确查询某个SQL的耗时
在这里插入图片描述

3、全局日志

查询全局日志是否开启

show variables like ‘%general_log%’

在这里插入图片描述
开启全局日志

set global general_log=1

开启后再查询
在这里插入图片描述
开启后会记录所有SQL，会记录在general_log_file中，

如果要记录到表中，添加一个配置

set global log_output = 'table'

日志记录在mysql.general_log中
在这里插入图片描述
如果要改回日志文件：set global log_output = 'file'

四、锁

按操作类型分为读锁、写锁：

读锁（共享锁）：对于同一数据，多个读操作可以同时进行，互不干扰。
写锁（互斥锁）：对于当前写操作，没有完毕，则无法进行其他的读写操作。

按操作范围分为表锁、行锁、页锁。

表锁：一次性对一整张表加锁，如MyISAM存储引擎使用表锁，开销小，加锁块，无死锁，单锁的范围大，容易发生锁冲突，并发度低。
行锁：一次性对一条数据加锁，如InnoDB引擎使用行锁，容易出现死锁，锁的范围小，不容易冲突，并发度高。
行锁的特殊情况：间隙锁。值在范围内，但不存在。
页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

高并发用InnoDB，否则可以用MyISAM

四、字符集配置

Windows下修改my.ini，linux下修改my.cnf
在[client]中添加default-character-set=utf8，在[mysql]下添加default-character-set=utf8，在[mysqld]下添加character-set-server=utf8，collation-server=utf8_general_ci，init_connect='SET NAMES utf8'。

[client]

# pipe=

# socket=MYSQL

port=3306

default-character-set=utf8

[mysql]
no-beep

default-character-set=utf8

[mysqld]
......
character-set-server=utf8
collation-server=utf8_general_ci
init_connect='SET NAMES utf8'
......

查询字符编码：
在这里插入图片描述

码农-文若书生

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql(六)mysql相关知识

文章目录一、MYSQL分层结构二、SQL执行流程二、SQL优化1、索引2、存储引擎3、执行计划3.1、id3.2、select_type3.3、table3.4、type3.5、possible_keys3.6、key3.7、key_len3.8、rows3.9、ref3.10、filtered3.11、extra4、SQL注意事项三、SQL分析方法1、慢日志2、Profile3、全局日志四、锁四、字符集配置一、MYSQL分层结构简单来看为下面这样：二、SQL执行流程SQL的语法：SELE
复制链接

扫一扫