MySQL以及优化

winks3

已于 2024-10-21 09:44:20 修改

阅读量1.1k

点赞数 17

文章标签： mysql 数据库

于 2024-09-26 21:38:11 首次发布

本文链接：https://blog.csdn.net/winks3/article/details/142565164

版权

一、慢查询日志（慢日志）

什么是慢日志

慢查询日志，就是查询花费大量时间的日志，是指mysql记录所有执行超过long_query_time参数（默认十秒，但是我们实际应用三秒就已经够久了）设定的时间阈值的SQL语句的日志，以帮助开发者分析和优化数据库查询性能，默认关闭，需要手动开启

如何定位慢SQL

简单版：show profil，显示关于服务器线程执行的详细信息，包括每个线程所执行的每个语句的执行时间、I/O 操作、上下文切换等；虽然能帮我们查找慢SQL，但开启会增加性能负担

详细版：performance_schema，虽然详细但是耗费资源多，不推荐

开启慢日志

先通过slow_query_log查看慢日志是否开启，开启的话应该是ON，没有开启就是OFF；

启用慢查询日志

set global slow_query_log='ON';

当然还有一些配置可以配置持久化或者一次性的慢日志

-- 持久性，通过配置文件设置
[mysqld]
log_output=FILE,TABLE
slow_query_log=ON
long_query_time=0.001
slow_query_log_file = /usr/local/mysql/mysql-8.0/logs/slow_query.log

#一次性的，通过命令设置, long_query_time 是时间阈值。为方便测试，此处认为超过0.001s的就属于慢查询
mysql> SET GLOBAL log_output = 'FILE,TABLE';
mysql> set GLOBAL slow_query_log=ON;
mysql> SET GLOBAL long_query_time = 0.001;
mysql> SET GLOBAL slow_query_log_file = '/usr/local/mysql/mysql-8.0/logs/slow_query.log';

日志的输出格式

MySQL的日志输出格式有两种，一种是TABLE,一种是FILE，推荐使用TABLE

TABLE：慢日志以表格的形式存储在MySQL数据库中的`slow_log`表中。记录了每条查询的详细信息，便于查询和分析。

TABLE的输出样式是这样的：

FILE：慢日志以文本文件的形式存储在磁盘上，记录了每条查询的详细信息，好处是方便日志的传输和存储，可以通过文件操作实现更复杂的查询和分析。

查看当前日志输出格式：

show variables like '%log_output%';

指定日志输出格式：

set global log_output=’FILE’;
set global log_output=’TABLE’;
set global log_output=’FILE,TABLE’;

如何对MySQL进行优化

首先要发现问题，1先查看skywalking，查看哪个接口比较耗时，因为我们的接口通常里面跟SQL相关，2通过XXL-job定时监控我们的日志，我们去查看XXL-job中记录的比较慢的日志，3开启慢日志，查看哪条SQL执行比较慢（关注long_query_time，默认十秒，实际开发中三秒其实就够久了）；先查看是否加索引或者索引是否失效了，加索引的话查看type级别，最好能达到index级别；

二、MySql Explain优化命令使用（重点）

小知识：清空表的两种操作

truncate table 表名：清空表的所有内容，包括自增的id，使自增的id从0开始，并且数据不可以通过日志恢复，建议谨慎使用！

delete from 表名：清空表的内容，但是如果有自增的字段，不会从0开始，而是接着上一次的自增id开始，可以通过日志文件恢复数据；

Explain是一个用来获取SQL语句执行计划的命令，帮助我们理解查询的过程以及后续如何去优化；

这里面比较重要的是type，它表示访问表所用的访问类型，一下是对它的值的一些解释

const：表示通过索引只能匹配到一行数据。explain select * from student where id = 1688

eq_ref：表示使用等值连接（比如使用主键或者唯一索引连接表）

explain SELECT * FROM student s1 JOIN student s2 ON s1.id = s2.id WHERE s1.age = 25

上边的例子意思是s1和s2这两张表有关联，我们根据两张表的id关联起来去查询，这样我们查询第二张表的age的时候其实比我们单独去查询第二张表的age速度要快；这里面s1和s2的id是等值连接的；

ref：使用非唯一索引查找，可以返回一行或多行数据；explain select * from student where name = '张68'

range：表示使用索引进行范围查找，例如使用比较符（>, <, BETWEEN）或IN操作符；

explain select * from student where age < 1688

index：表示全索引扫描，也就是说用了某一个索引的全部, 通常发生在查询使用索引覆盖的情况下

什么是索引覆盖：可以直接从索引中读取所有需要的数据，即查询的数据就是索引的数据，而不需要回表查询（即访问表中的数据行），从而提高了查询性能。比如用户信息表的用户手机号和密码，我们把用户手机号当做索引，这样查询的时候也能直接拿到手机号

explain select count(*) from student ；explain select sum(age) from student

all：表示全表扫描，即没有使用索引，需要遍历整个表进行查询。 explain select * from student

一般调优到index即可，调优先看有没有用到索引，再看看是否索引失效了（几种情况），

三、MySQL

三大范式

第一范式：

1NF 原子性，列或者字段不能再分，要求属性具有原子性，不可再分解；

第二范式：

2NF 唯一标识。即每个表只描述一种实体，每个记录都有唯一标识，不存在部分依赖关系。每个表必须有一个主键，并且每个非主键字段都要完全依赖于主键。

(非主键字段必须依赖于主键字段)，主要是解决行的冗余。

每一行数据有唯一的主键

2. 非主键字段必须依赖于主键字段

第三范式：

3NF 直接性，字段不依赖于非主键字段。（非主键字段不依赖于其它非主键字段）主要是解决列的冗余

常见操作

MySQL函数

字符串函数：

CONCAT(str1, str2, ...)：连接两个或多个字符串

LOWER(str)：将字符串转换为小写

UPPER(str)：将字符串转换为大写

SUBSTRING(str, pos, len)：从字符串中提取子字符串

REPLACE(str, find_str, replace_str)：替换字符串中的子字符串

LENGTH(str)：返回字符串的长度（以字节为单位）

日期和时间函数：

NOW()：返回当前日期和时间；

CURDATE()：返回当前日期；

CURTIME()：返回当前时间；

DATEDIFF(date1, date2)：计算两个日期之间的天数差

条件函数：

IF(condition, true_value, false_value)：根据条件返回不同的值

CASE WHEN condition THEN result [WHEN condition THEN result ...] ELSE result END：多条件判断

聚合函数：

COUNT(column)：返回指定列的行数

SUM(column)：返回指定列的总和

AVG(column)：返回指定列的平均值

MAX(column)：返回指定列的最大值

MIN(column)：返回指定列的最小值

什么是聚集索引和非聚集索引

聚集索引：其实就是主键索引，索引跟数据聚集在一起，innodb天生支持聚集索引

非聚集索引：非主键索引，索引不跟数据在一起，查数据的时候需要回表，myisam是非聚集索引

连接查询

建表须知

SQL关键字

1.分页

MySQL 的分页关键词 limit

SELECT * FROM student limit 100,6; 查询学生表中数据，跳过100条，从第101条开始显示，取6 条

倒序

select * from user order by id desc limit 0 6

2.分组

MySQL 的分组关键字：group by

SELECT sex , count(*) FROM student group by sex

3. 去重

去重关键字：distinct

select distinct name FROM student;/

SQL执行顺序

from...left join...on...where...group by...having..select...avg()/sum()...order by...asc/desc...limit...

from: 需要从哪个数据表检索数据

where: 过滤表中数据的条件

group by: 如何将上面过滤出的数据分组算结果

order by : 按照什么样的顺序来查看返回的数据

存储引擎

MyISAM 存储引擎与 InnoDB 引擎区别：

1.事务支持：MyISAM 不支持事务处理，而 InnoDB 支持事务处理

2.锁定机制（锁的粒度）：MyISAM是表锁而InnoDB是行锁

3.外键支持：MyISAM 不支持外键约束，而 InnoDB 支持外键约束

4.并发性能：，InnoDB 并发性能要优于 MyISAM，读多点的用MyISAM

事务特性(ACID)

原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。

一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态，即一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还是5000，这就是事务的一致性。

隔离性：即一个事务执行之前和执行之后都必须处于一致性状态。

持久性：事务一旦结束,数据就持久到数据库。

undo_log AC redo_log D（这两个表分别为了解决什么特性）

redo_log D

隔离级别机制（是为了解决隔离性的）

读未提交：可以读取到其他事务未提交的数据，这就是脏读

读已提交（针对update或delete）：事务处理期间，其他事务修改了数据，那么同一个事务再次查询得到的数据可能不一致，导致不可重复读的问题

可重复读（针对insert，MySQL默认的隔离级别机制）：虽然解决了不可重复读的问题，但是还有一个极端情况就是幻读；

幻读就是事务B新增了符合事务A读取的数据，原本事务A读取10条，结果事务B又加入了5条符合事务A读取的数据，这就会导致事务A读取到15条数据，与原来自己要读取的数据量不一致，这就是幻读；

InnoDB 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了幻读问题；

MVCC是什么：MVCC 通过为每个事务生成的数据记录创建多个版本，允许多个事务同时访问同一数据的不同版本，而不会相互干扰。这种方式可以显著减少锁的使用，提高系统的并发性能

比如事务A和事务B并发操作，事务A开始读取读取表中的数据，此时事务B开始更新表的数据，但是B创建了一个新的数据版本，事务A哪怕再去读取数据，读取的也是事务B更新数据前的版本，B提交的时候是更新的版本，而A读取的是未更新的版本；

串行化：什么问题都解决了，类似于redis排队解决问题，但是相应的效率会非常低

四、索引

索引存储

在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能。 空间换时间

优点是加快查询速率，缺点是占用内存空间，影响增删改；

索引的分类：

(1) 普通索引：最基本的索引，它没有任何限制。（值可重复）

(2) 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。（会员表里面的手机号，身份证号）

(3) 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 来约束。

(4) 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。（手机号和密码）例子：登录手机号和密码，订单头memerId（会员id）和状态，商品表：分类和状态

(5) 全文索引（不常用了）：老版本 MySQL 自带的全文索引只能用于数据库引擎为MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 1支持全文索引。默认 MySQL不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

ElasticSearch做搜索引擎库

索引的底层原理

Mysql 目前提供了以下 4 种:

常使用的就B+Tree，它是BTree的plus版本，B+Tree 查询效率更稳定

这是BTree：

这是B+Tree：

我们可以很清晰的看到他们的区别，首先左边子节点小于中间节点，而中间节点小于右边子节点

B树和B+树区别：

存储数据的位置:
B树: 数据既存储在所有节点中（叶子节点和非叶子节点都有数据）
B+树: 所有的数据记录都存储在叶子节点中，非叶子节点仅包含索引信息。叶子节点包含了完整的数据和索引键。
叶子节点之间的链接:
B树: 叶子节点之间没有链接。
B+树: 叶子节点之间通过指针相互链接，形成一个链表或循环链表，这使得范围查询和遍历变得高效。

如何避免索引失效（重点，超级ssssssuper）

(1) 范围条件查询

(2) 索引列上操作（使用函数、计算等）导致索引失效

（3）字符串不加引号, 造成索引失效，我来告诉你为什么，比如这里面的123，其实数据库不会把他当做123，而是看成他们对应的阿斯克码值，

（4）尽量使用索引覆盖,避免 select *, 这样能提高查询效率

如果索引列完全包含查询列, 那么查询的时候把要查的列写出来, 不使用 select *

通俗讲: 、

索引覆盖: 通过索引就能找到你要的资料信息

回表：通过索引不能完全拿出你要的信息，需要通过数据表再次查询一次才能获取到.

explain select age,name,id,tel  from tb where age=22 and  name ='张三'

(5) or 关键字连接

只要有一个or条件没有索引就全表扫描，如果一定要 or 查用询, 可以考虑下 or 连接的条件列都加索引, 这样就不会失效了.

(6)使用 !=

（7）like以通配符开头('%abc...')导致索引失效

遵循的是最左匹配原则

%在要查询的字前边就会失效，但是如果比如是张%，这样就不会失效，最左匹配原则优先匹配张这个字，所以不失效

如果不能计算但是我们有些场景需要计算，比如入库需要商品的过期时间，我们可以通过派生列解决，通过生产时间加上保质期派生出过期时间；

（8）排序列包含非同一个索引的列

用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序

数据库锁(高薪常问)

行锁和表锁，针对锁粒度划分的，一般分为：行锁、表锁、库锁

行锁和表锁区别就是

表锁：

开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

行锁：

开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

悲观锁和乐观锁

（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。

关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

（2）乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

我们开发人员自己控制的，通过运用版本号，我们库存表inventory表里用到，

乐观锁适用于多读的应用类型，这样可以提高吞吐量

优化索引

索引的设计需要遵循一些已有的原则, 这样便于提升索引的使用效率, 更高效的使用索引.

对查询频次较高, 且数据量比较大的表, 建立索引.
索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果 where 子句中的组合比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
使用唯一索引, 区分度越高, 使用索引的效率越高，能建唯一索引就建唯一索引，或者普通索引
索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维护效率. 不是越多越好（每张表最多三个）
使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率.
如果 where 后有多个条件经常被用到, 建议建立复合索引, 复合索引需要遵循最左前缀法则, N 个列组合而成的复合索引, 相当于创建了 N 个索引.

复合索引命名规则 index _ 表名 _ 列名 1 _ 列名 2 _ 列名 3

Sql 语句调优

根据业务场景建立复合索引只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率.
多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.
where 条件字段上需要建立索引, 但 Where 条件上不要使用运算函数，以免索引失效.
排序字段上, 因为排序效率低, 添加索引能提高查询效率.
优化 order by 语句: 在使用 order by 语句时, 不要使用 select *, select 后面要查有索引的列, 如果一条 sql 语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升序或同时用降序.
优化 group by 语句: 在我们对某一个字段进行分组的时候, Mysql 默认就进行了排序,但是排序并不是我们业务所需的, 额外的排序会降低效率. 所以在用的时候可以禁止排序, 使用 order by null 禁用.
select age, count(*) from emp group by age order by null
尽量避免子查询, 可以将子查询优化为 join 多表连接查询.