mysql1--优化十方面

最新推荐文章于 2024-08-09 12:55:03 发布

shining曙光

最新推荐文章于 2024-08-09 12:55:03 发布

阅读量141

点赞数

分类专栏： 4、数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_35841637/article/details/110007030

版权

4、数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

MYSQL优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式
功能：索引，缓存，分区分表。
架构：（主从复制，读写分离，负载均衡）。
合理SQL：经验、执行计划。
参考 https://www.cnblogs.com/sharpest/p/10390035.html

一、存储引擎

1、InnoDB存储引擎介绍

Mysql版本>=5.5 默认的存储引擎，MySQL推荐使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。
（1）存储格式
索引，索引集中存储，存储于同一个表空间文件中。
数据：记录行。索引：一种检索机制，也需要一定的空间，就相当于一本字典的目录。
（2）数据按照主键顺序存储插入时做排序工作，效率低。
（3）特定功能
事务、外键约束：都是为了维护数据的完整性。
并发性处理：
innodb擅长处理并发的。因为它使用了行级锁定，只该行锁了，其它行没有锁。
行级锁定：row-level locking，实现了行级锁定，在一定情况下，可以选择行级锁来提升并发性。也支持表级锁定，Innodb会自带锁，不需要我们自己设置。
多版本并发控制, MVCC，效果达到无阻塞读操作。
（4）总结：innodb擅长事务、数据的完整性及高并发处理，不擅长快速插入（插入前要排序，消耗时间）和检索。

2.MyISAM存储引擎介绍

MySQL<= 5.5 MySQL默认的存储引擎。
ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统。
擅长与处理，高速读与写。
（1）存储方式
数据和索引分别存储于不同的文件中。
（2）数据的存储顺序为插入顺序（没有经过排序）
插入速度快，空间占用量小。
（3）功能
a.全文索引支持。（mysql>=5.6时innodb 也支持）
b.数据的压缩存储。.MYD文件的压缩存储。
c.并发性：
仅仅支持表级锁定，不支持高并发。
支持并发插入。写操作中的插入操作，不会阻塞读操作（其他操作）
（4）关于Innodb 和myisam的取舍：
Innodb ：数据完整性，并发性处理，擅长更新，删除。
myisam：高速查询及插入。擅长插入和查询（不需要事务）。

3.其他存储引擎

（1）Archive：存档型，仅提供插入和查询操作。非常高效阻塞的插入和查询。
（2）Memory：内存型，数据存储于内存中，存储引擎。缓存型存储引擎。
（3）插件式存储引擎：用C和C++开发的存储引擎。

4.锁的概念

当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能互相影响），通过加锁来处理。
操作方面：
1）读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）
2）写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其他客户端的读，写操作。
3）锁定粒度（范围）：
行级：提升并发性，锁本身开销大
表级：不利于并发性，锁本身开销小。

二、字段类型选择

字段类型应该要满足需求，尽量要满足以下需求。
尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数。

三、范式与逆范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。
第一范式1NF，原子性
第二范式2NF，消除部分依赖
第三范式3NF，消除传递依赖

1、范式

（1）第一范式：具有原子性，确保每列保持原子性。
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。
（2）第二范式：主键列与非主键列遵循完全函数依赖关系，确保表中的每列都和主键相关。
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
（3）第三范式：非主键列之间没有传递函数依赖关系索引，确保每列都和主键列直接相关,而不是间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。
先满足第一范式，再满足第二范式，才能满足第三范式。

2、逆范式

逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能。

四、索引

五、查询缓存query_cache

1、将select的结果，存取起来共二次使用的缓存区域。
2、一旦开启查询缓存，MySQL会将所有可以被缓存的select语句都缓存。如果存在不想使用缓存的SQL执行，则可以使用 SQL_NO_CACHE语法提示达到目的。
3、注意：这里的缓存仅当数据表的记录改变时，缓存才会被删除。而不是依靠过期时间的。

六、分库分表

七、服务器架构介绍

主从复制：

读写分离

负载均衡：

在这里插入图片描述

八、经验

1.对于并发性的SQL少用（不用）多表操作（子查询，联合查询），而是将复杂的SQL拆分多次执行。如果查询很原子（很小），会增加查询缓存的利用率。
2.应尽量避免在 where 子句中对字段进行 null 值判断，
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0
3.避免在 where 子句中使用!=或<>操作符
4.避免在 where 子句中使用 or 来连接条件
5.in 和 not in 也要慎用，否则会导致全表扫描，用between 和exists 代替
6.正确使用like：select id from t where name like ‘%abc%’
7.应尽量避免在 where 子句中对字段进行表达式或函数
8.分页
9.最左前缀

九、慢查询日志的使用

定位执行较慢的查询语句方案。
explain执行计划
在这里插入图片描述
参考：https://www.cnblogs.com/tufujie/p/9413852.html

id

在所有组中，id值越大，优先级越高，越先执；id如果相同，可以认为是一组，从上往下顺序执行；

1、select_type

(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION之后的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

2、table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

3、type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
1.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
2.index: Full Index Scan，index与ALL区别为index类型只遍历索引树
3.range:只检索给定范围的行，使用一个索引来选择行
在这里插入图片描述

4.ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
在这里插入图片描述

5.eq_ref: 类似ref，区别就在使用的索引是唯一索引
在这里插入图片描述

6.const、system:
const用于用常数值比较、PRIMARY KEY或UNIQUE索引
当查询的表总数只有一行的情况下，使用system
在这里插入图片描述
7.NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

4、possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询