MySQL学习与优化_学习mysql的平台-CSDN博客

本文链接：https://blog.csdn.net/aeaiesb/article/details/111991192

数据库是开发人员必备的工具，项目中运行的软件平台都需要依赖于数据库的存储，数据库存储着业务系统的关键信息，包含基础的组织、人员及业务流程信息等，因此在平台运转过程中，数据库的响应速率直接影响平台的响应速度及用户的友好体验。

通过本次学习，可以了解Mysql数据的相关介绍，如具体的实际应用场景有哪些？在真实的项目中如何应用？项目中如何实现Mysql的主从热备？最后总结了在工作中常用的性能优化方式，从SQL语句优化，到索引优化，再到配置等相关参数优化，进行记录沉淀。

1名词解释

在学习mysql之前首先需要了解相关知识点，为什么要用Mysql，以及项目中如何应用，本文将介绍mysql的实际应用情况，对其进行简要的概述说明。

1.1Mysql简介

MySQL：是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件之一。关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。

1.2应用场景

目前MySQL用户已达千万级别，其中不乏企业级用户，可以说是目前最为流行的开源数据库管理系统软件。任何产品都不可能是万能的，也不可能适用于所有应用场景。那么MySQL到底在什么场景下适用，在什么场景下不适用呢？

1、Web网站系统

Web站点，是MySQL最大的客户群，在项目中被广泛应用，也是MySQL发展史上最为重要的支撑力量，MySQL之所以能成为Web站点开发者们最青睐的数据库管理系统，是因为MySQL数据库的安装配置非常简单，使用过程中的维护也不像很多大型商业数据库管理系统那么复杂，而且性能出色。还有一个非常重要的原因就是MySQL开放源代码，完全可以免费使用。

2、日志记录系统

MySQL数据库的插入和查询性能都非常高效，如果设计地较好，在使用MyISAM存储引擎时，两者可以做到互不锁定，达到很高的并发性能。因此，对需要大量插入和查询日志记录的系统来说，MySQL是非常不错的选择，比如处理用户的登录日志、操作日志等，都是非常适合的应用场景。

2存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。存储引擎主要有以下几种：InnoDB存储引擎、MyIsam存储引擎、Memory存储引擎，下面会将这几种数据Mysql存储引擎分别介绍说明。

2.1InnoDB

InnoDB是5.5版本后默认的存储引擎。事务安全性存储引擎，更加注重数据的完整性和安全性。InnoDB存储引擎特性如下：

1.支持自动增长列，方法是auto_increment；

2.支持事务，默认事务隔离级别为可重复度，锁粒度为行级锁，支持高并发；

3.支持外键约束，外键约束降低了表的查询速度，增加了表的耦合度；

4.支持在线热备份；

5.支持缓冲管理，通过缓冲池，将索引和数据缓存起来，加快查询速度；

2.2MyISAM

MyISAM是5.5版本前默认的存储引擎。擅长处理高速读写操作，但是不支持事务。MyISAM存储引擎特性如下：

1.数据存储为插入顺序，不经过排序，插入速度快，空间占用小；

2.支持全文索引；

3.数据可压缩存储，.MYD 数据文件可以使用myisamPack tableName 命令进行压缩，压缩后要修复索引，且压缩后的表只读；

4.支持表级锁，不支持高并发；

5.可以将数据文件和索引文件存放在不同目录。

2.3MEMORY

MEMORY数据存储在内存中，服务重启后数据消失。MEMORY存储引擎特性如下：

1.不支持BLOB和TEXT数据类型；

2.支持自增列和对可包含NULL值列的索引；

3.表数据存储在内存中，不会真正入库；

4.不需要memeory表的数据时，需要释放memeory表使用的内存，delete from 或truncate table或者删除表。

3主从配置

读写分离在业务复杂的系统中，如果有一句sql语句需要锁表，导致暂时不能使用读的服务，则很影响运行中的业务，使用主从复制，让主库负责写操作，从库负责读操作，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

做数据的热备架构的扩展，业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能，下图为Mysql主从热备的架构图：

3.1主库配置

在项目中应用了数据库的主从热备机制，下文将详细说明在项目中具体的搭建过程，模拟真实的业务场景。首先提前准备好两台服务器，并提前准备好mysql环境。

找到主数据库的配置文件my.cnf，一般来说该文件存放在/etc/my.cnf,找到该文件，在[mysqld]部分插入如下：

重启mysql服务命令如下：

在主机上建立账户并授权slave：

使得配置生效：

查询master的状态命令：

3.2从库配置

找到从数据库的配置文件my.cnf，一般来说该文件存放在/etc/my.cnf,找到该文件，在[mysqld]部分插入如下：

重启mysql服务命令：

这里要根据上面主服务器的状态填写，不要直接用下面的SQL，需根据实际值修改。

查看主从复制是否配置成功：

当看到Slave_IO_State:Waiting for master ot send event、Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常。如果出现Slave_IO_Running：NO的情况，通过以下命令查找auto文件，并将该文件删除，后会重新生成新的UUID。

3.3效果演示

通过在主库添加一条数据，添加成功后，可以在主库查看到该条数据，同时切换到从库，也可以查看该条数据，实现了基本的主从热备。

4性能优化

对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，数据库性能至关重要，因此要对mysql数据库的优化操作有所了解，本文主要总结了mysql数据库中常见的优化操作，接下来进行详细介绍。

4.1语句分析

使用 EXPLAIN 关键字可以知道MySQL是如何处理SQL语句的。这可以帮助分析查询语句或是表结构的性能瓶颈。EXPLAIN的查询结果还会告诉你索引主键被如何利用，数据表如何被搜索和排序等。

EXPLAIN出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

type：连接类型可划分为几种类型执行效率分别为：

1.const>eq_ref>ref>range>index>all；

2.const:查询索引字段，并且表中最多只有一行匹配（只有主键查询只匹配一行才会是const，有些情况唯一索引匹配一行会是ref）；

3.eq_ref主键或者唯一索引；

4.ref非唯一索引（主键也是唯一索引）；

5.range索引的范围查询；

6.index (type=index extra = using index 代表索引覆盖，即不需要回表)；

7.all全表扫描（通常没有建索引的列）。

extra额外信息说明，常见参数如下：

1.using temporary(组合查询返回的数据量太大需要建立一个临时表存储数据,出现这个sql应该优化)；

2.using where (where查询条件)；

3.using index(判断是否仅使用索引查询，使用索引树并且不需要回表查询)；

4.using filesort(order by 太占内存，使用文件排序)。

4.2SQL优化

SELECT语句务必指明字段名称

使用SELECT *会增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新，因此要求直接在select后面接上字段名，在实际应用中，需要什么字段就查什么字段，避免一些无用字段，如下图所示，通过使用select * 和select 具体字段的查询对比，可以明显看出查询的效率：

注意：

1.尽量用union all代替union；

2.尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描；

3.如果是多表关联查询，则将数据量较大的表作为SQL查询的主体表，且关联查询时建议使用InnerJoin进行关联；

4.尽量避免使用in和not in，会导致引擎走全表扫描，对于区间查询建议使用BETWEEN AND；

5.尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描；

6.尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描；

7.查询时尽量不要在where子句中使用!=或<>操作符，相关条件可使用<、<=、 =、>、>=、BETWEEN AND；

8.order by条件要与where中条件一致，否则order by不会利用索引进行排序。

4.3索引优化

所谓索引就是为特定的mysql字段进行一些特定的算法排序，比如二叉树的算法和哈希算法，哈希算法是通过建立特征值，根据特征值快速查找，并且mysql默认的就是二叉树算法BTREE，通过BTREE算法建立索引的字段。

数据库添加索引会提升查询的效率，但是不要盲目的给表建太多索引，因为索引本身的存储也要占用存储空间，一旦更新操作频繁反而降低新性能。

什么时候适用索引：

1) 字段数值有唯一限制，比如ID；

2) 频繁作为WHERE查询条件的字段，尤其在数据表大的情况下；

3) 经常要用于排序(order by)，分组(group by)的列，因为索引已经排序好了.

添加索引可以使用Navicat设计表选择索引列进行添加。

5项目应用

通过以上学习，了解了Mysql的基本概念、常用的存储引擎、主从热备以及常用的优化方式。接下来通过介绍在实际项目中如何进行优化、配置参数是如何进行优化的，从而提升运行的性能。

5.1常用命令

总结了在项目工作开展中常用的Mysql操作命令，通过这些命令，可以对数据库的运行情况、线程堵塞情、况数据库连接数等相关参数进行监控，如下所示：

5.2配置优化

Mysql配置文件的优化可以有很多方向，主要对三个比较重要的优化方面（数据库读写、缓存分区、连接时长等）进行优化说明总结。

5.2.1读写优化

打开 vim /etc/my.cnf配置文件添加如下配置：

5.2.2缓存分区

打开 vim /etc/my.cnf配置文件添加如下配置：

5.2.3连接时长

打开 vim /etc/my.cnf配置文件添加如下配置：

6心得体会

通过对Mysql的学习收获了很多知识，同时也有很多感悟与认知，现将自己在工作中存在的不足及改进措施进行总结，再不断努力提升自己。

6.1学习总结

通过对Mysql的学习，学到了很多有用的知识，这些知识在以后的工作中都会被广泛应用，首先了解了Mysql的相关概念以及mysql的应用场景，文中介绍了Mysql的几种引擎，同时也学习到项目中如何搭建Mysql的主从热备，最后，学习了常见的优化方式，从Sql编写角度、索引角度进行性能优化，对后续的工作开展会有很大帮助。