Mysql性能优化（详解）

引言

今天，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。关于数据库的性能，这并不只是DBA才需要担心的事，而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构，对操作数据库时（尤其是查表时的SQL语句），我们都需要注意数据操作的性能。这里，我们不会讲过多的SQL语句的优化，而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。

初步

数据库设计三大范式

第一范式（1NF） 列不可再分

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

第二范式（2NF） 属性完全依赖于主键

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键

第三范式（3NF） 任何非主属性不依赖于其它非主属性

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

三大范式总结

  1. 对属性的原子性约束,要求属性具有原子性,不可再分解.
  2. 对记录要求有唯一性,通常设计一个主键来实现,主键不能包含业务逻辑.
  3. 对字段冗余性约束,要求字段没有冗余.

通常在数据库设计过程中，并不会严格遵守三大范式，为了追求查询性能，应当对部分数据做适当的冗余设计。

Mysql基础知识

运行原理

  • 连接器
    连接器负责跟客户端建立连接，获取权限、维持和管理连接
  • 查询缓存
    当执行查询语句的时候，会先去查询缓存中查看结果，之前执行过的sql语句及其结果可能以key-value的形式存储在缓存中，如果能找到则直接返回，如果找不到，就继续执行后续的阶段。
    但是不推荐使用查询缓存，查询缓存失效比较频繁。如果要使用缓存，可以使用redis等Nosql。在Mysql高版本中，已经废弃了查询缓存
  • 分析器
    根据语法规则判断这个sql语句是否满足mysql的语法，如果不符合就会报错“You have an error in your SQL synta”
  • 优化器
    在具体执行SQL语句之前，要先经过优化器的处理，比如索引的选择，多表关联时，决定表的连接顺序等等。
  • 执行器
    负责优化过后的sql语句的执行。
  • 存储引擎
    数据持久化时候的储存方式，决定数据在储存器中的数据结构。

性能监控

查看执行时间

#1. 将profiling 打开 （当前连接有效）
set profiling =1;

#2. 执行查询语句

#3. 查看最近的一条SQL语句的执行过程（具体耗时）
show profile;

#查看执行历史
show profiles;

#再根据历史记录，查看指定SQL语句的执行过程  n是历史SQL语句的序号
show profile for query n

#查询block io次数
show  profile block io for query n

#显示用户cpu时间和系统cpu时间
show profile cpu for query n

上一次查询开销

MySQL中可以通过show status like 'last_query_cost' 来查看查上一个查询的代价，而且它是io_cost和cpu_cost的开销总和，它通常也是我们评价一个查询的执行效率的一个常用指标。
它只能检测比较简单的查询开销，对于包含子查询和union的查询是测试不出来的。

查询连接信息

show processlist;

  • id：表示连接的session id
  • user：表示连接的用户
  • host：表示连接的主机
  • db：表示操作的数据库
  • command：表示当前状态
  • time：表示最近执行sql语句的执行时间
  • state：表示最近sql的执行状态
  • info：表示最近执行的sql语句

Mysql调优

schema与数据类型优化

数据类型优化

  1. 字段类型满足使用的基础上，占用空间越少越好
    应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型。

  2. 尽可能选择满足使用的简单数据类型，比如

    • 整型比字符串操作代价更低；
    • 使用mysql自带日期和时间，而不是使用字符串来存储日期或时间；
    • 使用整形储存IP地址（INET_ATON('192.168.10.1')将IP字符串转换成整型，SELECT INET_NTOA(3232238081)将整型转换成IP字符串）

  3. 尽量避免null
    如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列。

  4. 整数类型
    可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。
    尽量使用满足需求的最小数据类型

  5. 字符串类型

    • char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串
    • varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性
    • text不设置长度，当不知道属性的最大长度时，适合用text
      按照查询速度：char>varchar>text

  6. BLOB和TEXT类型
    MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
    两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。

  7. date、datetime和timestamp
    datetime：8个字节，精确到毫秒
    timestamp：4个字节，精确到秒
    date：3个字节，精确到day 2020-11-05

    • 不要使用字符串类型来存储日期时间数据
    • 日期时间类型通常比字符串占用的存储空间小
    • 日期时间类型在进行查找过滤时可以利用日期来进行比对
    • 日期时间类型还有着丰富的处理函数，可以方便的对时间类型进行日期计算
    • 使用int存储日期时间不如使用timestamp类型

合理使用设计范式

使用范式，确保数据规范化，数据量较小，操作比较快；
反范式适当增加数据冗余，某些情况下能够减少表的join操作，提高查询速度。
在实际开发过程中很难做到严格意义上的范式或者反范式，一般需要混合使用

主键选择

代理主键：与业务无关的，无任何实际意义的数字序列
自然主键：事物属性中的自然唯一标识，比如身份信息里的身份证
推荐使用代理主键，降低与业务的耦合度，更容易维护。在设计过程中，推荐使用通用代理主键，写sql的时候好写一点。

字符集的选择

  1. 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。
  2. 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。
  3. MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。储存中文最好使用utf-8mb4

存储引擎的选择

mysql默认存储引擎innodb，以下是myisam和innodb两种储存引擎的对比

适当的数据冗余

如果通过join连接，只是为了获取某张表中的一个独立小字段，可以做适当的冗余来减少join表的数量。
阿里设计规范里面有强调，在join连接的时候，尽量不要超过3张以上的表。

适当的拆分

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

查看执行计划

在实际的开发场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。
可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。
执行计划中包含的信息

  • id
    select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序
    id号分为三种情况：
    1. 如果id相同，那么执行顺序从上到下
    2. 如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
    3. id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行
  • select_type
--sample:简单的查询，不包含子查询和union
explain select * from emp;

--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--union:若第二个select出现在union之后，则被标记为union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

--union result:从union表获取结果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;

--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptno in (select distinct deptno from dept);

--DERIVED: from子句中出现的子查询，也叫做派生类，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
 explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
 
--uncacheable union:表示union的查询结果不能被缓存：sql语句未验证

  • table
    对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集

    1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
    2. 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
    3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

  • type
    type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    常见几个：

            ALL：全表扫描ALL最慢
            explain select * from emp;

            const：这个表至多有一个匹配行
            explain select * from emp where empno = 7369;
           
            eq_ref：使用唯一性索引进行数据查找
            explain select * from emp,emp2 where emp.empno = emp2.empno;

            ref：使用了非唯一性索引进行数据的查找
            explain select * from emp e,dept d where e.deptno =d.deptno;
            
            index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
            explain  select empno from emp;

            range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
            explain select * from emp where empno between 7000 and 7500;

  这几种的查询速度： const > eq_ref > ref > range > index > ALL

  • possible_keys
    显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

  • key
    实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

  • ref
    显示索引的哪一列被使用了，如果可能的话，是一个常数

  • rows
    根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

  • extra
    包含额外的信息。

    ```
--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;

--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;

--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;

--using where:使用where进行条件过滤
explain select * from t_user where id = 1;

--using join buffer:使用连接缓存，情况没有模拟出来

--impossible where：where语句的结果总是false
explain select * from emp where empno = 7469;
```

索引优化

为什么需要索引

索引就相当于书本的目录一样，通过对索引的查找，能够快速定位到指定的页面。
索引的优点
1. 大大减少了服务器需要扫描的数据量
2. 帮助服务器避免排序和临时表
3. 将随机io变成顺序io

Mysql中索引的用处
1. 快速查找匹配WHERE子句的行
2. 如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引
3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
4. 当有表连接的时候，从其他表检索行数据
5. 查找特定索引列的min或max值
6. 如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组
7. 在某些情况下，可以优化查询以检索值而无需查询数据行

索引的分类

* 主键索引
* 唯一索引
* 普通索引
* 全文索引
* 组合索引

索引数据结构

不同的引擎对于索引有不同的支持：Innodb和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引。
B+TREE索引结构

以上图中就是
1. B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。
2. B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。
3. B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。
4. MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址（所有称其为非聚集索引）。

索引匹配方式

#1. 全值匹配，和索引中的所有列进行匹配
explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';

#2. 匹配最左前缀，只匹配前面的几列
explain select * from staffs where name = 'July' and age = '23';
explain select * from staffs where name = 'July';

#3. 匹配列前缀 可以匹配某一列的值的开头部分
explain select * from staffs where name like 'J%';

#4. 匹配范围值 可以查找某一个范围的数据
explain select * from staffs where name > 'Mary';

#5. 精确匹配某一列并范围匹配另外一列，可以查询第一列的全部和第二列的部分，有了范围匹配后，范围后面的列将无法再匹配
explain select * from staffs where name = 'July' and age > 25;

#6. 只访问索引的查询，查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引
explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

常见技术名词

以下sql示例的表：id主键、name普通列索引

  1. 回表（use where）
    在B+TREE中，主键为PK，聚集索引，叶子节点存储行记录，即组成（主键，行记录）

    通过普通索引进行查找的时候：select * from t where name='lisi';
    先通过普通索引定位到主键值id=5，再通过聚集索引（主键索引）定位到行记录；

  2. 索引覆盖（use index）
    在查询的时候，需要的结果列，存在于被查询的索引列中
    select id,name from t where name='shenjian';

  3. 索引下推（use index condition）
    假设已有联合索引 index(name,age)
    SELECT * from user where name like '陈%' and age=20
    如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

  4. 聚簇索引
    聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。比如Primary Key，即主键索引

  5. 非聚簇索引
    索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

  6. 嵌套循环连接
    Mysql中的join算法Nested-Loop Join（嵌套循环连接），Nested-Loop Join在mysql里面有三种实现方式

Simple Nested-Loop Join

如下图，r为驱动表，s为匹配表，可以看到从r中分别取出r1、r2、......、rn去匹配s表的左右列，然后再合并数据，对s表进行了rn次访问，对数据库开销大

Index Nested-Loop Join

索引嵌套循环连接，它要求非驱动表s上有索引，可能通过索引来减少比较，加速查询
在查询时，驱动表（r）会根据关联字段的索引进行查找，挡在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。
如果非驱动表（s）的关联健是主键的话，性能会非常高，如果不是主键，要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢。

Block Nested-Loop Join

如果有索引，会选取第二种方式进行join，但如果join列没有索引，就会采用Block Nested-Loop Join。可以看到中间有个join buffer缓冲区，是将驱动表的所有join相关的列都先缓存到join buffer中，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表（s）的访问频率。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

优化小细节

  1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层
  2. 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询
  3. union all,in,or都能够使用索引，但是推荐使用in
  4. B+TREE中范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列
  5. 强制类型转换会全表扫描
  6. 更新十分频繁，数据区分度不高的字段上不宜建立索引
  7. 创建索引的列，不允许为null，可能会得到不符合预期的结果
  8. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致（阿里设计规范）
  9. 能使用limit的时候尽量使用limit
  10. 单表索引建议控制在5个以内
  11. 单索引字段数不允许超过5个（组合索引）
  12. 创建索引的时候应该避免以下错误概念
    • 索引越多越好
    • 过早优化，在不了解系统的情况下进行优化
  13. 子查询的优化最重要的优化建议是尽可能使用关联查询代替
  14. 如果不是确实需要mysql消除重复行，尽量使用union all 而不是union，因为使用union关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高

分区表优化

分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

select操作

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

insert操作

当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表

delete操作

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

update操作

当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作

分区表使用场景

  1. 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
  2. 分区表的数据更容易维护
  3. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
  4. 可以使用分区表来避免某些特殊的瓶颈（innodb的单个索引的互斥访问）
    分区表类型
    1. 范围分区
      根据列值在给定范围内将行分配给分区
    CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
    1. 列表分区
      类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择
    CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT

)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)

);
    1. 列分区
      mysql从5.5开始支持column分区，可以认为i是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式
    2. hash分区
      基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含mysql中有效的、产生非负整数值的任何表达式
    CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
    1. key分区
      类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值
    CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
    1. 子分区

分区表使用注意问题
1. null值会使分区过滤无效
2. 分区列和索引列不匹配，会导致查询无法进行分区过滤
3. 选择分区的成本可能很高
4. 打开并锁住所有底层表的成本可能很高
5. 维护分区的成本可能很高

服务器参数优化

通用参数

#数据文件存放的目录
datadir=/var/lib/mysql

#mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接
socket=/var/lib/mysql/mysql.sock

#PID存放位置
pid_file=/var/lib/mysql/mysql.pid

#mysql端口
port=3306

#mysql存储引擎
default_storage_engine=InnoDB

#当忘记mysql的用户名密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql
skip-grant-tables

字符设置参数

#客户端数据的字符集
character_set_client

#mysql处理客户端发来的信息时，会把这些数据转换成连接的字符集格式
character_set_connection

#mysql发送给客户端的结果集所用的字符集
character_set_results

#数据库默认的字符集
character_set_database

#mysql server的默认字符集
character_set_server

connection参数

#mysql的最大连接数，如果数据库的并发连接请求比较大，应该调高该值，操作系统相关设置也需要更改
max_connections

#限制每个用户的连接个数
max_user_connections

#mysql能够暂存的连接数量，当mysql的线程在一个很短时间内得到非常多的连接请求时，就会起作用，如果mysql的连接数量达到max_connections时，新的请求会被存储在堆栈中，以等待某一个连接释放资源，如果等待连接的数量超过back_log,则不再接受连接资源
back_log

#mysql在关闭一个非交互的连接之前需要等待的时长(默认8小时)
wait_timeout

#关闭一个交互连接之前需要等待的秒数（交互连接，控制台那种）
interactive_timeout

log参数

#指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行中发生任何严重错误时的相关信息
log-error=/var/lib/mysql/mysql.err

#指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句
log_bin

#指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在日志中（主从复制）
binlog_do_db

#指定不将更新记录到二进制日志log_bin的数据库
binlog_ignore_db

#指定多少次写日志后同步磁盘，默认值1
sync_binlog

#是否开启查询日志记录
general_log

#指定查询日志文件名，用于记录所有的查询语句
general_log_file

#是否开启慢查询日志记录（值1即开启）
slow_query_log=1

#指定慢查询日志文件名称，用于记录耗时比较长的查询语句
slow_query_log_file=slow-query.log

#设置慢查询的时间，超过这个时间的查询语句才会记录日志，单位秒
long_query_time=2

#是否将管理语句写入慢查询日志
log_slow_admin_statements

INNODB参数

#该参数指定大小的内存来缓冲数据和索引，最大可以设置为物理内存的80%,默认128m，即134217728
innodb_buffer_pool_size=134217728

#主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0，1，2
#值为0：提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
#值为1：提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
#值为2：提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。
#总结：
#0 由innodb异步地将redo log buffer刷新到磁盘
#1 提交事务时，立即将redo log写入磁盘，保证持久性（最安全，默认）
#2 提交事务时，把redo log写入os cache，由操作系统自己选择时机将其持久化到硬盘（性能高）
innodb_flush_log_at_trx_commit=1

#binlog的刷盘规则（非innodb独有，其它引擎也有，这里因为和上一个参数雷同，所以放在这个地方一起解释）
#值为0：当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。(**性能最好，默认值**)
#值为1：如果设置为1，则每提交1次事务，就会刷一次盘，最多就丢失一次数据，数据完整性最好
#值为n：当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
sync_binlog=1

#设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍
innodb_thread_concurrency

#此参数确定日志文件所用的内存大小，以M为单位
innodb_log_buffer_size

#以循环方式将日志文件写到多个文件中，默认1,开启状态
innodb_log_files_in_group

#mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区
read_buffer_size

#mysql随机读的缓冲区大小
read_rnd_buffer_size

#此参数确定为每张表分配一个新的文件，默认on
innodb_file_per_table

redolog和binlog之二阶段提交

Innodb数据更新流程

1. 可以使用binlog替代redolog进行数据恢复吗？

不可以
Innodb利用wal技术进行数据恢复，write ahead logging技术依赖于物理日志进行数据恢复，binlog不是物理日志是逻辑日志，因此无法使用

2. 可以只使用redolog而不使用binlog吗？

不可以
redolog是循环写，写到末尾要回到开头继续写，这样的日志无法保留历史记录，无法进行数据复制。

posted @ 2021-06-02 15:22  心若向阳花自开  阅读(700)  评论(0)  编辑  收藏  举报