MySQL优化概述

最新推荐文章于 2022-05-16 22:35:07 发布

CHENBT_

最新推荐文章于 2022-05-16 22:35:07 发布

阅读量175

点赞数 1

分类专栏：知识总结

本文链接：https://blog.csdn.net/qq_38481805/article/details/96284949

版权

知识总结专栏收录该内容

10 篇文章 1 订阅

订阅专栏

概述

1、衡量指标

TPS：Transactions Per Second(每秒传输的事物处理的个数)，这个指服务器每秒处理的事物数，支持事物的存储引擎的如InnoDB等特有的一个性能指标。

QPS：Queries Per Second(每秒查询处理量)同时适用与InnoDB和MyISAM引擎。

等待时间：执行SQL等待返回结果之间的等待时间。

TPS = （COM_COMMIT(提交次数) + COM_ROLLBACK（回滚次数））/UPTIME

QPS = QUESTIONS(查询次数)/UPTIMECC

2、常用工具

MySQLSlap是从MySQL5.1.4开始官方提供的压力测试工具。

创建：schema、table、test data;

运行负载测试，可以使用多个并发客户端连接；

测试环境清理（删除创建的数据，表等，断开连接）

3、逻辑架构

4、缓存

数据库的缓存中对执行后的SQL语句会进行优化，优化后的SQL语句也是会进行缓存的，但是对数据是通过命令可以控制其是否缓存。

//数据缓存是否开启

show variables like ‘&query_cache_type%’;

//缓存的大小

Show variable like ‘query_cache_size’;

//设置缓存大小

Set global query_cache_size = (大小);

5、SQL处理层

解释查询
优化

存储引擎

//查看MySQL提供什么引擎

Show engiens;

//查看当前默认的是什么存储引擎

Show variables like ‘%storage_engine%’;

MySIAM

MySQL5.5之前的默认存储引擎

主要由三个文件组成:

frm //存储表结构，任何存储引擎都具备的

myd //数据库文件

myi //索引文件

MySIMA使用的是非聚集索引

特性：

并发性与锁级别：表级锁

支持全文检索

支持数据压缩 myisampack -b test.myi

InnoDB

MySQL5.5以后的默认存储引擎

独立表空间和系统表空间

innodb_file_per_table

ON：独立表空间，（tablename）.ibd

OFF :系统表空间，ibdataX;

当创建的是系统表空间的时候，创建表的时候就不会生成.idb文件，此时的数据和索引就会存储到data目录下。而是独立表空间的时候，创建表的时候就会生成.ibd的文件，这个文件中存的就是数据和索引，存储在data目录下的各自库的目录下。

MySQL5.6以后默认使用系统表空间

系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table 收缩系统文件
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
建议：InnoDB使用独立表空间

特性：

InnoDB是一种事务性存储引擎
完全支持事物的ACID特性
Redo Log和Undo Log

Undo日志记录某数据被修改前的值，可以用来在事务失败时进行rollback；Redo日志记录某数据块被修改后的值，可以用来恢复未写入data file的已成功事务更新的数据。

InnoDB支持行级锁(并发级别更高)
InnoDB适合于大多数的OLAP（联机事物处理）应用

CSV

组成：

数据以文本方式存储在文件中
.cvs存储内容
.csm文件存储表的元数据如表状态和数据量
.frm表结构

特点：

以CVS格式进行文件存储
所有的列都不能为null
不支持索引（不合适大表，不适合在线处理）
可以对数据文件直接进行编辑

业务设计

1、锁

MySQL中额锁机制比较简单，其中最显著的特点是不同的存储引擎支持不同的锁。

MyISAM和Memory采用的是表级锁(table-level locking)

InnoDB存储引擎采用的是行级锁(row-level locking)，也支持表级锁，但是默认情况下是行级所锁。

MySQL中的锁：

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

行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般。

仅从锁的角度来说，表级锁更适合与查询为主，只有少量按索引条件更新数据的应用，如OLAP（联机事物处理）系统；行级锁则更适合于有大量按缩影条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事物处理(OLTP)系统。

MySIAM锁

MySQL的表级锁有两种形式：

表共享读锁 Table Read Lock

表独占写锁 Table Write Lock

共享读锁：

给表加锁： lock table 表名 read

在同一个session中给表加读锁，那么就不能再对表中的数据进行更改，会报错，但是可以进行查询；在当前会话中对其他的表进行查询，也是会报错滴。在另外一个session中对已经加读锁的表进行修改，会等待，直到锁释放，就会执行，查询是可以正常执行的；

需要注意的是：lock table 表名 as 别名 read; 使用别名，lock table 表名 read是不起作用的

独占写锁

给表加锁： lock table 表名 write

加锁的当前会话，是可以进行CURD操作的

在其他的会话当中是要等待的

总结：

对MyISAM表的读锁操作，不会阻塞其他用户对同一个表的读操作，但会阻塞同一个表的写操作；
对MySIAM的读锁操作，不会阻塞当前session对表的读，在当前session对其他表进行修改会报错；
一个session使用LOCK TABLE命令给表加了读锁，这个session可以查询锁定表的记录，但更新或访问其他表都会显示错误；
另外一个session可以查询当前锁定表中的记录，但更新就会出现锁等待。
对MyISAM表的写锁操作，则会阻塞其他用户对同一表的读和写操作；
对MyISAM表的写锁操作，当前session可以对本表作CRUD操作，但对其他表操作会报错；

对于读锁，可以理解为锁的是当前会话和当前表，对当前表可以进行多线程的读，多会话的读，但是除此之外，当前会话中对其他表的操作是报错的，对当前锁定表的读以外的操作也是报错的；在另外的会话中对当前读锁锁定的表进行增删改操作是等待的，当前对其他表的操作是没有影响的。对于当前锁定的表，如果在当前会话使用表的别名进行查询，也是会报错的。

对于写锁，当前会话是可以对锁定表进行CURD操作的，但是其他的会话对锁定的表的CURD操作都会等待；写锁也相当于锁定的是当前的session和表，在当前的session中对其他的表进行操作，都是会报错的；

InnoDB锁

InnoDB引擎支持行锁

行锁：

共享锁，又称读锁，当一个事物对某几行上行锁时，允许其他事物对这几行进行读操作，但不允许进行行写操作，也不允许其他事物给这几行上排他锁，允许上读锁；

排他锁，又称写锁，当一个事物对几行上写锁时，不允许其他事物写，

行读锁： select * from 表 where 条件 lock in share mode;

排他锁：select * from 表 where 条件 for update；

注意：开启一个新事物的时候会释放表锁

2、事物

目前为止，MySQL数据库只有InnoDB支持事物。5.6以后，默认数据库的存储引擎就是InnoDB。

查看某张表的存储引擎：show create table 表名；

修改表的存储引擎：建立时：create table … type=InnoDB: alter table table_name type=InnoDB;

事物的特性（ACID）

事物的4个属性：原子性，一致性，持久性，隔离性。

事物的并发问题

脏读：事物A读取了事物B更新的数据，然后B回滚操作，那么A读到的数据就是脏数据；

不可重复读：事物A多次读取同一数据，事物B在事物A多次读取的过程中，对数据做了更新并提交，导致事物A多次读取同一数据时，结果不一致：

幻读：事物在操作过程中，进行两次查询，第二次查询的结果包含了第一次查询未出现的数据或者是比第一次查询的数据少了某个数据。

不可重复读和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或者删除。解决不可重复读的问题只需要宿主满足条件的行，解决幻读问题需要锁住表。

隔离性级别

Read uncommited:未提交读

Read uncommited：已提交读

Repeatable read：可重复读

Serializable:串行化

事物隔离级别为可重复读时，如果有索引(包括主键索引)的时候，以索引列为条件更新数据，会存在间隙锁，行锁，页锁的问题，从而锁住一些行，如果没有索引，更新数据时会锁住整张表。实例中体现出的是锁的条件中如果是有索引的，那么锁住的就是有索引的相应行，如果条件没有索引，那么将会锁住整张表。

事物隔离级别为串行化时，读写数据会锁住整张表。

隔离级别越高，越能保证数据的完整性和一致性，但对并发性能的影响也越大，对于多应用程序，可以优先考虑把数据库系统的隔离级别设置为Read committed,他能够避免脏读，也具有较好的并发性能。

事物的语法

开启事物：

begin ;

start transaction;

begin work;

事物回滚：rollback

事物提交：commit

还原点：savepoint

rollback to savepoint s1; 此时数据库的数据回滚到还原点s1执行之前的一个操作。数据库的状态是s1之前的一个操作完整执行完，到s1这个回滚点记录处。

逻辑设计

范式设计：

第一范式：数据库中的所有字段都具有单一属性；

单一属性的列是由基本数据类型锁所构成的

设计出来的表都是简单的二维表

当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：要求表中只有一个业务属性，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系。

要求表中的所有列都必须依赖于主键，而不能有任何一列与主键没有关系。也就是说一个表只能描述一件事情；

第三范式：表中的每一列只与主键直接相关，而不是间接相关。每个非键属性只依赖于主键，不依赖其他属性。

反范式化设计

所谓反范式化设计就是为了性能和读取效率适当的对数据库设计范式的要求进行违反，允许存在少量冗余，就是用空间换取时间的一种做法。

范式化设计

优点：

可以尽量减少数据冗余；
范式化的更新操作比反范式化的更快
范式化的表通常比反范式化的表更小

缺点：

对于查询需要多个表进行关联
更难进行索引优化

反范式化设计

优点：

可以减少表的关联
可以更好的进行索引优化

缺点：

存在数据冗余及数据库优化异常（需要去关注所有有需要修改的相关信息的表）
对数据的修改需要更多的成本（需要去关注所有有需要修改的相关信息的表）

物理设计

数据库，表，字段的命名遵守可读性原则；
长名原则（尽可能少使用或者不是用缩写）
查询多就用MyISAM，需要事物就用InnoDB
当一个列可以选选择多个数据类型时
1. 有限考虑数字类型
2. 其次是日期，时间类型
3. 最后是字符类型
4. 对于相同级别的数字类型，应该有限选择占用空间小的数据类型

查询以及索引

1、慢查询

慢查询定义及其作用：

慢查询日志，就是查询慢的日志。是指MySQL记录所哟执行超过long_query_time参数设定时间阈值的SQL语句的日志。

默认情况下慢查询日志是关闭的。

slow_query_log 启动停止慢查询日志

slow_query_log_file 指定慢查询日志存放路径(默认和数据文件放在一起)

long_query_time指定慢查询SQL执行时间的阈值（单秒：秒）

long_queryies_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方【参数：FILE,TABLE】最好是放在file中，不要方在table中

慢查询分析工具【mysqldumpslow】

汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。

语法：

mysqldumpslow -s r -t 10 slow-mysql.log

-s order (c,t,l,r,at,al,ar) 输出顺序

c:总次数

t:总时间

l:锁的时间

r:总数据行

at,al,ar:t,l,r平均数【例如： at = 总时间/总次数】

-t top 指定取前几条作为结果输出

工具二

pt-query-digest --explain h=127.0.0.1, u=root,p=password slow-mysql.log

2、索引

索引是帮助，MySQL高效的获取数据的数据结构。

索引是一种数据结构。

索引分类

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引；
唯一索引：索引列的值必须唯一，允许有NULL值；主键索引是可以由null值的
复合索引：即一个索引包含多个列；
聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行；
非聚簇索引：不是聚簇索引，就是聚簇索引；

3、执行计划

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。分析查询语句或者表结构的性能瓶颈。

执行计划的作用

知道表的读取顺序
数据读取操作的操作类型
那些索引可以使用
那些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

执行计划表的每个字段的作用

explain select * from dept;

id：可以知道select语句的加载顺序;获取select子句的操作表顺序。

ID相同时，是从上到下的依次执行的

ID值不相同，ID值越大的越先执行

ID值即有相同的，也有不相同的，是以上两种情况的组合，当然还是ID值大的先执行，当ID值相等时，就按照从上往下的顺序执行。

select_type:

类型	描述
Simple	简单的select查询，查询中不包含子查询或者UNION
Primary	查询中若包含任何复杂的子部分，最外层查询则被标记为primary
Subquery	在select或者where列表中包含子查询
Derived	在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放在临时表里
Union	若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层的select将被标记为derived
Union result	从union表获取结结果的select

table： 显示这一行的数据是出自那张表的

type：显示的访问类型，结果从好到坏依次是：system>const>eq_ref>ref>>fulltext>ref_or_null>index_merge>snique_subquery>index_subquery>range>index>ALL

system>const>eq_ref>ref>range>index>ALL(全表扫描)

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现；
const：表示通过索引一次就找到了；const用户比较primary key或者unique索引，，因为只匹配一行数据，索引和快，如果主键置于where列表中，MySQL就能将该查询转换成一个常量。
eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描；
ref：非唯一性索引扫描，返回匹配某个单独值的所有行；返回的结果是单独行或者所有行，只要条件中不是唯一索引或者主键索引，type就是ref；
range：只检查给定范围的行，使用一个索引来检查行。key列显示了使用哪个索引，一般就是在where语句出现了between，<,>,in 等的查询。这种扫描要比全表扫描好，开始索引的某一点，结束于扫描的另一点，不用扫扫描全部索引。
index：当查询的结果全为索引类的时候。也是全表扫描的一种，它会扫描整个索引文件，只是没有扫描数据文件；
all：将全表扫描以找到的行；

key：实际使用的索引。如果null，则没有使用索引。

查询中若使用了覆盖索引，则改索引和查询的select字段重叠

possible_key:

注意：有哪些列适合建索引？

相对来说唯一的列

经常用来查询显示的列

经常用来关联的列 where 条件中用到的列以及join on用到的列

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

key_len显示的值为索引字段的最大长度，并非使劲使用长度，及key_len是根据表定义计算而得，不是通过表内检索出的。

key_len表示索引是使用的字节数
根据这个值，可以判断索引使用的情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。
char和varchar跟字符编码也有密切关系
latin1占用1个字节，gbk占用两个字节，utf8占用3个字节，(不同编码占用的存储空间不同)
字符类型，索引字段为char，+ 允许为null

这个表中的name字段的长度是char类型的，长度定义的是10，字符集编码还是utf8，所以key_len就是30；

字符类型，索引字段为char，+ 不可为null

key_len中的31中的那个1就是允许为null的情况

varchar 不允许为null utf8 name 定义10

表中的2是varchar类型的要用两个字节来记录当前字段的实际长度

varchar 允许为null utf8 name 定义10

表中的3是还包括一个null的情况

数值类型

如果出现查询出来的结果比表中的数大1，表中的字段设计时允许为null，那么多出来的那个1就是为null的情况

整数/浮点数/时间类型的索引长度

NOT NULL=字段本身的字段长度

NULL=字段本身的字段长度+1(因为需要有是否为空的标记，这个标记需要占用1个字节)

datetime类型在5.6中字段长度是5个字节，datetime类型在5.5中字段长度是8个字节

总结

变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。

而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间。

复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

ref:

显示索引的那一列被使用了，也有可能是一个常数。那些了或者常数被用于查找索引列上的值。

rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数；

Extra：包含不适合在其他列中显示的但是十分主要的额外信息

值	描述
Using filesort	说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“中文排序” 使用了文件排序，没有使用索引排序
Using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by
Using index	是否使用了覆盖索引
Using where	表明使用过了where过滤
Using join buffer	使用了链接缓存
Impossible where	Where子句的值总是false。不能用来获取任何元组

索引覆盖：

理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

优化实战

策略一：尽量全职匹配：如果是联合索引，在设置条件时，输入的值越多，查询的速度越快

策略二：最佳左前缀法则：如果索引了多列，要最受最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

索引的创建顺序：name,age,pos,在查询时如果没有遵守最佳左前缀法则，就会导致索引失效也就是说where条件中直接有age = 9 and pos 等于什么，索引就会失效；

策略三：不在索引列上左任何操（计算，函数，自动或者手动类型转换），会导致索引失效而转向全表扫描。

策略四：范围条件放在最后

如果使用了范围查询，会导致范围查询后面的索引失效，但是本身的索引是没有失效的。

策略五：覆盖索引尽量用

策略六：不等于要慎用

策略七：null/not对索引有影响

策略八： like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

select * from test where name like ‘%Juily%’;

语句中的%两边都有，索引是失效的，也就是说%写在后面是不会失效的，写到前面就失效了

如果索引失效，也可以使用覆盖索引来解决

策略八：字符类型不加引号索引失效

策略九：On改ONION效率高

insert语句优化

提交前关闭自动提交
尽量使用批量insert语句
可以使用MyISAM存储引擎

CHENBT_

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL优化概述

概述1、衡量指标TPS：Transactions Per Second(每秒传输的事物处理的个数)，这个指服务器每秒处理的事物数，支持事物的存储引擎的如InnoDB等特有的一个性能指标。QPS：Queries Per Second(每秒查询处理量)同时适用与InnoDB和MyISAM引擎。等待时间：执行SQL等待返回结果之间的等待时间。TPS = （COM_COMMIT(...
复制链接

扫一扫