MySQL学习笔记整理（二）

发表于 | 分类于

前言

       之前已经了解过mysql的基本原理，重点是我们该从哪些方面去改善数据库性能，或者从设计开始怎么样去合适的设计，可以使用哪些工具和方法去帮助我们监测数据库，并根据在数据库原理的理解基础上，对不同的业务需求不同的软硬件环境，尽量做出最优的优化策略。下面就看下有哪些优化方法和适用场景，可以根据实际选用其中一部分进行优化，而且优化是一个整体的，所以不是改变一个方法就能大幅提升性能，也没有确定的套路。本文在看了传智mysql优化课程之后记录的学习笔记。

mysql优化

对mysql优化时一个综合性的技术，主要包括：

  1. 表的设计合理化(符合3NF)

  2. 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]

  3. 分表技术(水平分割、垂直分割)
  4. 读写[写: update/delete/add]分离
  5. 存储过程 [模块化编程，可以提高速度]
  6. 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
  7. mysql服务器硬件升级
  8. 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

范式

表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF

       1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF。

       2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现。

       3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF:

需要修改成以下，消除依赖：

       反3NF : 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

案例 ：

show status

       查看mysql配置参数。我的运行环境是Mac，mysql5.7.18，如下图：

从图中可知，有356个变量，其中我们需要熟悉一部分，知道它代表的意义，可用下面指令查具体的：

注意：这里结束符非常重要，区分中英文符号，是结束一条命令的标志。。。。。存储过程中还要改。因为存储过程中就有分号。

这里放两个链接：mysql 命令大全，mysql较为重要的状态变量。

       那我们如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)，比如首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete.. / 当前连接)。实现如下：

show status

  1. 常用的:
    show status like ‘uptime’ ;
    show stauts like ‘com_select’ show stauts like ‘com_insert’ …类推 update delete

  2. show [session|global] status like …. 如果你不写 [session|global] 默认是session 会话，指取出当前窗口的执行，如果你想看所有(从mysql 启动到现在，则应该 global)

  3. show status like ‘connections’;

  4. show status like ‘slow_queries’;//显示慢查询次数

压力测试脚本生成

       如何去定位慢查询呢，我们想还原一个场景，那我们就要构建一个大表(400 万)，我们采用存储过程构建，默认情况下，mysql认为10秒才是一个慢查询.所以我们要修改mysql的慢查询时间.使用以下命令：

show variables like ‘long_query_time’ ; //可以显示当前慢查询时间

set long_query_time=1 ;//可以修改慢查询时间

       而且我们构建的大表中记录是有要求的,为了满足真实性，我们采取了随机生成的方法 ，使记录不同从而使测试效果和真实的相差不大。存储过程源码我放在了GitHub上，地址如下，无耻的求个star或者fork：wustzoujing/BetterMysqlTestDemo；简要的记录下我的执行过程；
首先写了两个随机函数，产生指定长度的两个字符串或者数字。其中rand_string()执行结果如下：

       上图可以知道：其中dual是亚元表，就是空表，已经随机生成了6位字符串。接下来看看存储过程执行成功如下：

       最后我们调用存储过程，执行几分钟后，看下结果，已经插入成功。

慢查询

这时我们已经有慢查询的次数了，就是插入数据这条：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show variables like 'long_query_time'
    -> ;
    
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

       然后我们需要开启慢查询日志（不同操作系统不同设置方式和文件路径，百度应该有很多），然后重启mysql，上面就会记录着我们的慢查询语句是什么。如下图，这是我的结果。

小结

       这里我们主要是形成测试样本，利用慢查询工具，这只是一个例子，还有很多命令方法工具帮助我们来找出问题，可以参考手册来。下篇博客讲讲发现问题了，我们如何优化。

说明

       文中出现的图片，文字描述有些来自互联网，但是出处无法考究，如果侵犯您的相关权益，请联系我，核实后我会马上加上转载说明。谢谢！！！