mysql

最新推荐文章于 2024-10-09 22:03:37 发布
最新推荐文章于 2024-10-09 22:03:37 发布
阅读量542 收藏
点赞数
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/celinet/article/details/128435598
版权

1. 官网

MySQL

 

2. 基本架构图

 

3. profile

profile用于性能监控

启动profile

set profiling=1;

 用法

查看最新执行语句的分析结果

show profile;

 

 

查看所有

show profiles;

查看具体某一条

show profile for query 2;

更多用法

 

 4. performance schema

performance schema用于性能监控

mysql高版本中用 performance schema代替profile

https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html

performance schema默认是打开的，会在数据库中产生performance_schema数据库。该数据库主要关注数据库运行过程中的性能相关的数据

performance_schema通过监视server的事件来实现监视server内部运行情况

performance_schema中的事件不进行持久化

用法

--1、哪类的SQL执行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪类SQL的平均响应时间最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪类SQL排序记录数最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪类SQL扫描记录数最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪类SQL使用临时表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪类SQL返回结果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪个表物理IO最多？
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
--8、哪个表逻辑IO最多？
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9、哪个索引访问最多？
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
--10、哪个索引从来没有用过？
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
--11、哪个等待事件消耗时间最多？
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、查看每个阶段的时间消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、查看每个阶段的锁等待情况
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

查看数据库连接

show processlist;

 

5. 数据类型优化

1）越小越好

数据类型占用字节数越小越好；比如，类型字段可以用int，也可以用tinyint；尽量用小的

尽量使用满足需求的最小数据类型

2）简单就好

日期类型用date存储查询时间会更短，用varchar存储查询时间会更长一点

3）尽量避免使用null

可能包含null的列，索引优化上也会比较麻烦；值的比较也会复杂

可以用''代替

4）字符串

varchar

char

 

 5）时间

datetime

Ttimestamp

 

 date

 6） 用枚举代替字符串类型

6. 其他优化

1） 范式

三范式是为了解决数据冗余的问题，但是实际工作中，需要一些适当的冗余来避免联表查询

2）主键

尽量用跟业务无关的主键

3）字符集

 4）存储引擎

默认的存储引擎是innodb

innodb锁的是索引

5）适当拆分

如果一个表字段过多，经常用的字段也就几个，建议把经常用的字段拆出来，减少每次访问的io量 

7. 执行计划

https://dev.mysql.com/doc/refman/8.0/en/explain.html

 1）id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序

id号分为三种情况：

1、如果id相同，那么执行顺序从上到下

2、如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3、id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

2）select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

 3）table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集

4）type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据

效率从最好到最坏依次是

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 

一般情况下，得保证查询至少达到range级别，最好能达到ref

all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。

index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序

range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 

index_subquery：利用索引来关联子查询，不再扫描全表

unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引

index_merge：在查询过程中需要多个索引组合使用，没有模拟出来

ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式

ref：使用了非唯一性索引进行数据的查找

eq_ref ：使用唯一性索引进行数据查找

const：这个表至多有一个匹配行，

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

5）possible_keys

   显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

6）key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

7）key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

8）ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

9）rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

10）extra

包含额外的信息

using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置

using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除

using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找

using where:使用where进行条件过滤

using join buffer:使用连接缓存

impossible where：where语句的结果总是false

8.索引

1）索引结构

B+树（innodb、MyIsam）

memory使用的是hash

hash只能等值查询；消耗内存；

二叉树容易树的层级过深；

平衡树插入效率较低，需要旋转

B树，所有节点都会存储数据，会导致树深度变深，访问一个数据需要的io量和io次数变多

2）主键

innodb通过B+树结构对主键创建索引，然后叶子节点中存储记录。如果没有主键，会选择非空的唯一键。否则，会生成一个6字节的row_id作为主键

3）索引的优点

1、大大减少了服务器需要扫描的数据量

2、帮助服务器避免排序和临时表

3、将随机io变成顺序io

4）索引优化

1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

2. 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询

3. 使用前缀索引

4. 使用索引扫描来排序（order by 的字段排序要一致）

5. union all,in,or都能够使用索引，但是推荐使用in

6. 范围列可以用到索引

7. 强制类型转换会全表扫描

8. 更新十分频繁，数据区分度不高的字段上不宜建立索引

9. 创建索引的列，不允许为null，可能会得到不符合预期的结果

10. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

11. 能使用limit的时候尽量使用limit（limit 1的话，查看一条数据以后不会继续查询了）

12. 单表索引建议控制在5个以内

13. 单索引字段数不允许超过5个（组合索引）

小表join大表

5）索引监控

索引使用情况

show status like 'Handler_read%';

 9. 优化

1）查询慢的原因

  2）优化器的优化类型

mysql本身有优化器，join时，使用straight_join 可以强制使用自己写的顺序进行join 

 3）查看sql时间成本

show status like 'last_query_cost';

10. 分区表

根据自定义规则将表分成多个文件进行存储

https://dev.mysql.com/doc/refman/8.0/en/partitioning-types.html

1）应用场景

 

2）分类

 

RANGE Partitioning

 

LIST Partitioning  

Hash分区  

 

 3）在使用分区表的时候需要注意的问题

 

 4）分区表的限制

11. 服务器参数设置

 1）通用

 

2）字符

 

 3）连接

4）日志

5）缓存

 

 6）INNODB

 

12. log

1）redo log

redo log用于保证数据持久化 

 

2）undo log

用于保证原子性（回滚）

3）binlog

 

 

4） 数据更新流程

 

 

 

13. 锁

1）MyIsam表锁

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock。  

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！ 

2）innodb锁

  • 事务

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

  • 并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来以下问题

脏读： 一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读” 

不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读” 

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

  •  InnoDB的行锁模式及加锁方法

共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

lock in share mode

排他锁（x）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

for update

mysql5.7之后，引入MTS技术，主从复制时不存在复制延迟问题

 

 

CelineT
关注
Linux-安装MySQL（详细教程）
xhmico的博客
06-09 11万+
本文的主要内容是在 Linux 上安装 MySQL，以下内容是源于 B站 - MySQL数据库入门到精通 整理而来。MySQL是一种关系型数据库管理系统，所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。官网：https://www.mysql.com/ 进入官网之后点击 进入页面这里选择 进入页面，选择 根据你服务器
mysql全配置解析
沉淀、分享、成长，让自己和他人都能有所收获！
11-28 2万+
【代码】mysql全配置解析。
Python连接MySQL数据库(简单便捷)
好好读书
04-25 12万+
Python连接MySQL数据库(简单便捷) Pycharm，Anaconda，MySQL 5.5，spyder 快快学习吧
Mysql部署
weixin_51720711的博客
03-21 4506
一、环境 主机名 IP地址 操作系统 rabbitmq版本 mysql1 192.168.10.180 Centos 7 Mysql5.7 mysql2 192.168.10.181 Centos 7 Mysql5.7 二、单机部署 1、下载mysql安装包 下载地址：https://downloads.mysql.com/archives/community/ [root@mysql1 opt]# wget https://cdn.mysql.com/archives/mysql-
mysql C语言连接
m0_51641706的博客
04-21 3633
文章目录库安装接口介绍mysql_init()mysql_real_connect()mysql_query()获取select结果的接口mysql_store_result()mysql_num_rows()mysql_num_fields()mysql_fetch_fields()mysql_fetch_row()mysql_close 库安装 C语言连接首先要安装connector，去mysql官网找找即可。 不想找的话，这是链接 这里下载C API 这里为了用纯C，就下载难用的那个库了。 根据O
MySQL命令行连接
m0_53188792的博客
12-23 7353
命令行连接mysql
windows 安装 MySQL 绿色版
桃李不言下自成蹊
09-25 4万+
.\mysqld.exe --initialize --console .\mysqld.exe -install net start mysql .\mysql.exe -uroot -p ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
mysql自定义函数
weixin_43219844的博客
04-10 6747
mysql自定义函数 1.创建自定义函数 DELIMITER $$ DROP FUNCTION IF EXISTS genPerson$$ CREATE FUNCTION genPerson(name varchar(20)) RETURNS varchar(50) BEGIN DECLARE str VARCHAR(50) DEFAULT ''; SET @tableName=name; SET str=CONCAT('create table ', @tableName,'(id
MySQL Blob类型
JngKang的博客
10-20 6058
MySQL中，Blob是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。 插入Blob类型的数据必须使用PreparedStatement，因为Blob类型的数据无法使用字符串拼接写的。 MySQL的四种Blob类型(除了在存储的最大信息量上不同外，他们是等同的)。 类型 大小（单位：字节） TinyBlod 最大255 Blod 最大65K MediumBlob 最大16M LongBlob 最大4G 实际使用中根据需要存入
MySQL 8.1.0安装
m0_75111244的博客
08-05 5532
点击链接点击链接。
mysql jdbc连接驱动jar包（适用于mysql5.7版本，亲测可用）
08-17
MySQL JDBC连接驱动是Java应用程序与MySQL数据库之间通信的桥梁，使得开发者可以使用Java语言来执行SQL语句，处理数据库中的数据。本驱动适用于MySQL 5.7版本，并且经过实际测试，确认可用。这个jar包是完全免费的，...
mysql-connector-java-8.0.27
12-24
MySQL Connector/J 8.0.27 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个实现了JDBC（Java Database Connectivity）接口的驱动程序，允许Java开发者在应用中无缝地访问MySQL数据库。MySQL Connector/J使得...
mysql驱动jar 文件适用MySQL5.7
01-18
MySQL驱动jar文件是Java应用程序与MySQL数据库之间通信的关键组件，主要功能是提供Java Database Connectivity (JDBC) API，使得Java开发者能够通过编写Java代码来执行SQL语句，从而操作MySQL数据库。标题提到的...
MySQL安装配置教程（超级详细、保姆级）
热门推荐
Soloversion的博客
03-26 42万+
一、 下载MySQL Mysql官网下载地址https://downloads.mysql.com/archives/installer/ 1. 选择想要安装的版本，本篇文章选择的是5.7.31版本，下面的那个文件，点击Download下载 二、 安装MySQL 1. 选择设置类型 双击运行mysql-installer-community-5.7.31.0.msi 这里选择是自定义安装，所以直接选择“Custom”，点击“Next” “Developer Default”是开发者默认
Java连接MySQL数据库的超级详细步骤（Windows）
firstlt0217的博客
05-19 11万+
1. 数据准备 1. 下载JDK 下载Java开发工具包JDK，下载地址：https://www.oracle.com/java/technologies/javase-jdk14-downloads.html，下载待用。 2. 下载Mysql Java 连接 MySQL 需要驱动包，下载地址为：http://dev.mysql.com/downloads/connector/j/。 解压后得到 jar 库文件，待用，最终会将红框文件导入相应项目文件中。 3. 下载MySQL 下载地址：https:/
基于微信小程序的生鲜订购小程序系统(SpringBoot+Vue+Uniapp+MySQL)
源码好优多
10-06 1806
开发者不再需要手动配置大量的 XML 文件或繁琐的注解，框架提供了默认的配置，根据项目的依赖关系和约定，自动完成配置。系统测试主要是为了避免用户在使用时发生问题，增强用户体验感，为了不影响用户的使用，我们需要从多角度、多思路去考虑系统可能遇到的问题，通过不同的模拟场景来发现缺陷并解决问题。添加用户时，必填项不填，检验系统是否有非空检验；登录功能测试方案：当需要登入该系统时，通过账户密码等功能点进行验证，用户在输入时需要输入与数据库内存储的数据匹配的内容，当其中某项输入错误时系统将提示输入错误。
MySQL 联合索引底层存储结构及索引查找过程解读
lizi88888的博客
10-09 382
大家好，我是 Lorin ，联合索引（Composite Index）又称复合索引，它包括两个或更多列。与单列索引不同，联合索引可以覆盖多个列，这有助于加速复杂查询和过滤条件的检索。联合索引的列顺序非常重要，因为查询优化器会按照索引列的顺序执行搜索。本文将从联合索引基本概念、底层存储结构、索引查找过程、实践建议几个方面图文并茂进行详细介绍。
深入了解 MySQL 中的 JSON_CONTAINS
学习,分享一直在路上
10-05 976
函数用于检查一个 JSON 文档中是否包含另一个 JSON 文档，
MySQL数据的增删改查
最新发布
2301_79985750的博客
10-09 933
基于比较运算符和逻辑运算符，可以对复杂的条件进行过滤，从而达到要求，其中，区间查询，模糊查询以及null查询都是条件查询。限制查询结果集的条数，通过分页查询可以有效的控制一次查询出的结果集中的记录条数，可以有效的减少数据库的压力，同时对用户也比较友好。例如，查姓唐同学的成绩，%代表唐后面所有字符都被选中，类似还有%唐，%唐%，分别表示唐前面的字符被选中，唐左右的字符被选中。这是一个非常危险的操作，如果数据量太大，会消耗大量的内存和CPU资源，可能导致数据库服务器的性能下降。，特别是在多表查询的情况下。
MySQL 5.1.58 安装教程
2. 创建用户组和用户：`groupadd mysql` 创建名为mysql的用户组，`useradd -g mysql mysql -s /sbin/nologin` 添加一个名为mysql的用户并将其添加到mysql组，且指定不登录 shell。 3. 解压MySQL源码包：`tar xzf ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值