超详细MySQL数据库优化

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷

1 优化一览图

2 优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是 *** 作数据库即可,而硬优化则是 *** 作服务器硬件及参数设置

21 软优化

211 查询语句优化

1首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息

2例:

显示:

其中会显示索引和查询数据读取数据条数等信息

212 优化子查询

在MySQL中,尽量使用JOIN来代替子查询因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高

213 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的三大注意事项:

214 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

215 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时

216 增加冗余字段

类似于创建中间表,增加冗余也是为了减少连接查询

217 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费

1 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

2 检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:

3 优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁

22 硬优化

221 硬件三件套

1配置多核心和频率高的cpu,多核心可以执行多个线程

2配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度

3配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行 *** 作的能力

222 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能MySQL服务的配置参数都在mycnf或myini,下面列出性能影响较大的几个参数

223 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

224 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了

wikijs 是优秀的开源 wiki 系统，相较于 xwiki ，功能目前性上比 xwiki 不够完善，但也在不断进步。 wiki 写作、分项、权限管理功能还是有的，胜在 UI 设计很漂亮，能满足小团队的基本知识管理需求。

以下工作是在 kubernetes 1230 + helm 3 已经部署好的情况下进行的。部署 kubernetes 可以参考我另外的文章 “kubesphere 多节点集群安装”

我们使用 openebs 作为存储，openebs 默认安装的 local storageclass 在 pod 销毁后自动删除，不适合用于我的 mysql 存储，我们在 local storageclass 基础上稍作修改，创建新的 storageclass，允许 pod 销毁后，pv 内容继续保留，手动决定怎么处理。

我们团队其他项目中也需要使用 postgresql, 为了提高 postgresql 数据库的利用率和统一管理，我们独立部署 postgresql， 并在安装 wikijs 时，配置外部数据库。

我们使用 secret 保存 postgres 用户密码等敏感信息。

使用 configmap 保存数据库初始化脚本，在 数据库创建时，将 configmap 中的数据库初始化脚本挂载到 /docker-entrypoint-initdbd, 容器初始化时会自动执行该脚本。

我们使用 openesb 来提供存储服务。可以通过 创建 pvc 来提供持久化存储。

在前面的步骤准备好各种配置信息和存储后，就可以开始部署 postgresql 服务了。

我们的 kubernetes 没有配置 存储阵列，使用的是 openesb 作为存储，我们使用 deployment 来部署 postgresql 服务。

测试略

adminer 是 php 语言的 数据库管理工具，可以在浏览器上管理数据库，因为我们的数据库部署在 kubernetes ，配套使用基于浏览器的 数据库管理工具，可以更方便的管理，而且不需要把安装在 kubernetes 集群里的数据库暴露出来，提高了数据保密性。

我们使用 configmap 保存 adminer 需要的环境变量。比如通过 ADMINER_DESIGN 设置皮肤，通过 ADMINER_PLUGINS 设置需要加载的插件。

略

我们使用 secret 保存 wikijs 用于连接数据库的用户名密码等敏感信息。

我们使用 configmap 保存 wikijs 的数据库连接信息。

如果 postgresql 数据库里没有创建 wikijs 用户和数据 ，需要手工完成一下工作：

通过 adminer 连接 postgresql 数据库，执行一下 sql 语句，完成数据库和用户的创建、授权

在前面的步骤准备好各种配置信息和存储后，就可以开始部署 wikijs 服务了。

以下是完整的通过 deployment 部署 mysql 数据库和 xwiki 的 yaml 文件，保存为 xwikiyaml 。

直接执行可以创建好 wikijs

1、在SQL数据库管理工具中创建一个新的表格，用于存储需要清洗和处理的数据。

2、将需要清洗和处理的数据导入到中间表中。

3、对导入的数据进行清洗和处理，如去除重复数据、修正格式错误、填充缺失数据等。

4、将处理后的数据导出到目标数据库中，可以使用INSERTINTO语句将数据插入到目标数据库中。

1 阅读程序，说明它们的功能

use master

go

create database jxgl on (name=jxgldata1,filename=’c:\jxgl_datamdf’, Size=5mb,maxsize=100mb,filegrowth=5mb)

go

创建一个名字为 jxgl 的数据库，数据文件存储在 c:\jxgl_datamdf

文件初始的大小为5mb

随着数据量的增加，如果文件空间不足了，会自动增长，每次增长的幅度为5MB

最大增长到 100MB

2 阅读程序，说明它们的功能

use jxgl

go

select from student

检索 jxgl 数据库下面的 student 表的 所有数据。

3 阅读程序，说明它们的功能

use jxgl

go

select sno,avg(grade) from sc group by sno

检索 jxgl 数据库下面的 sc 表

按 sno 分组，求 grade 的平均值

4 阅读程序，说明它们的功能

use library

go

update readcatgory set 借书数量=15，借书期限=借书期限+5 where 种类名称=‘一类读者’

go

更新 library 数据库下面的 readcatgory 表

将所有 种类名称 为 一类读者 的数据， 将 借书数量 更新为 15， 借书期限 在原有的基础上，增加5。

5 阅读程序，说明它们的功能

use library

go

select 出版社,count(出版社) as 图书数量 from books group by 出版社 having count(出版社)>10

go

检索 library 数据库下的 books 表

按出版社分组， 查询每个出版社的出版的图书总数量

如果这个出版社出版的图书总数量在10本以下（含10本），那么不显示。

六、设计题。（共20分）

现有关系数据库如下：

数据库名：学籍管理数据库

学生表(学号 char(6)，姓名 char(8)，性别 char(2)，出生日期 datetime)

课程表(课程编号 char(2)，课程名称 char(20)，任课教师 char(10))

选课表(学号 char(6)，课程编号 char(2)，成绩 tinyint)

用SQL语言实现下列功能的sql语句代码：

1 创建数据表“选课表”代码。（4分）

选课表(学号 char(6)，课程编号 char(2)，成绩 tinyint)

要求使用：外键(学号)，非空（学号、课程编号）。-------(提示词：foreign key,references)

CRETAE TABLE 选课表 (

学号 char(6) NOT NULL,

课程编号 char(2) NOT NULL,

成绩 tinyint,

FOREIGN KEY (学号) REFERENCES 学生表,

FOREIGN KEY (课程编号) REFERENCES 课程表

);

2 从选课表中查询每位学生选课的最高分、最低分、平均分。（4分）

要求：分别使用函数max(),min(),avg()。

SELECT

学号,

MAX(成绩) AS 最高分,

MAX(成绩) AS 最低分,

MAX(成绩) AS 平均分

FROM

选课表

GROUP BY

学号

3 创建视图“学生选课信息视图”代码。（4分）

学生选课信息视图(姓名, 课程编号, 成绩)

CREATE VIEW 学生选课信息视图

AS

SELECT

学生表姓名,

选课表课程编号,

选课表成绩

FROM

学生表 JOIN 选课表 ON (学生表学号 = 选课表学号)

4 请用联接查询实现查询选修过“李明”老师讲授课程的所有学生姓名（4分）

SELECT

学生表姓名

FROM

课程表, 选课表, 学生表

WHERE

学生表学号 = 选课表学号

AND 课程表课程编号 = 选课表课程编号

AND 课程表任课教师 = '李明'

5 请用嵌套查询实现查询选修过“李明”老师讲授课程的所有学生姓名（4分）

SELECT

学生表姓名

FROM

学生表

WHERE

学生表学号 IN

( SELECT

学号

FROM

选课表

WHERE

课程编号 IN

(

SELECT

课程编号

FROM

课程表

WHERE

任课教师 = '李明'

)

)

以上就是关于超详细MySQL数据库优化全部的内容，包括:超详细MySQL数据库优化、kubernetes上部署 wiki.js、sql如何建立中间表并接入数据清洗数据库等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！