MySQL调优

Azure0626
198 阅读6分钟

说明：此处的MySQL使用的是Linux下的5.7版本。

一、sql_mode的合理设置

​ sql_mode设置是否允许一些非法操作，比如允许一些非法数据的插入，数据查询等。

​ 在生产环境必须将这个值设置为严格模式，所以开发测试、生产环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

查询sql_mode

#全局的sql_mode
select @@GLOBAL.sql_mode;
#会话级别sql_mode
select @@SESSION.sql_mode;

注意：

​ MySQL5.7默认设置了ONLY_FULL_GROUP_BY.

ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为不在GROUP BY从句中。

其他值：

NO_AUTO_VALUE_ON_ZERO

该值影响自增长列的插入。默认设置下，插入0或者NULL代表生成下一个自增长值。如果用户希望插入的值为0，而这一列又是自增长的，那么去掉该选项。

STRICT_ALL_TABLES,STRICT_TRANS_TABLES

对于支持事务的表，这两种模式是一样的，如果发现某个值确实或者非法，Mysql将抛出错误，语句回停止运行并且回滚。

对于不支持事务的表，这两种模式的效果：

  1. 如果在插入或者修改第一个数据行时就发现某个值非法缺失，那该语句直接抛错，这个和支持事务的数据表行为是一样的。
  2. 如果在插入或者修改第n个（n>1）数据行时才发现错误，那么就会出现下面的情况：
    • 在STRICT_ALL_TABLES模式下，停止语句执行，存在部分更新的问题
    • 在STRICT_TRANS_TABLES模式下，MySQL将继续执行该语句避免“部分更新问题”，对于每个非法值将转换为最接近的合法值。

NO_ZERO_IN_DATE

在严格模式下，不允许日期和月份为零。

NO_ZERO_DATE

设置该值，MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO

在INSERT或者UPDATE过程中，如果数据被零除，则产生错误而不是警告。如果未给出该模式，那么数据被领出时MySQL返回NULL

NO_AUTO_CRATE_USER

禁止GRANT创建密码为空的用户。

NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或者不存在，那么抛出错误，不设置此值时，用默认的存储引擎替代。

如何设置sql_mode

在当前窗口中设置sql_mode

SET GLOBAL sql_mode = 'modes...';
SET SESSION sql_mode = 'modes...';

在/etc/my.cnf中配置sql_mode，永久生效。

[mysqld]
#set the SQL mode to strict
#sql-mode="modes..."
sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

二、MySQL逻辑架构

MySQL的引擎架构属于插件式的架构，可以在不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。

这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

MySQL-1.png

1.连接层

​ 最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。

​ 主要完成一些类似于连接处理、授权认证以及相关的安全的方案。

​ 在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。

​ 同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层

​ 第二层架构主要完成大多数的核心服务功能，比如SQL接口，并完成缓存的查询，SQL的分析和优化以及部分内置函数的执行。

​ 所有跨存储索引的功能也在这一层实现，比如过程、函数等。

​ 在该层，服务器会解析查询并创建响应的内部解析树，并对其完成响应的优化：如确定查询表的顺序，是否利用索引等，最后生成响应的执行操作。

​ 如果是select语句，服务器还要回查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

  1. Management Serveices & Utilities： 系统管理和控制工具

  2. SQL Interface SQL接口：接收用户的SQL命令，并且返回用户需要查询的结果，比如select from就是调用SQL Interface

  3. Parser解析器：SQL命令传递到解析器的时候就会被解析验证和解析。解析顺序：

MySQL-2.png

  1. Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。

  2. Cache和Buffer：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存。

3.引擎层

​ 存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取，主要有MyISAM和InnoDB。

4.存储层

​ 数据存储层，主要是讲数据存储在运行与裸设备的文件系统之上，并完成与存储引擎的交互。

三、查看sql执行周期

查看是否开启计划：

  1. 修改配置文件/etc/my.cnf，查看是否开启计划，新增一行：query_cache_type=1，重启mysql（systemctl restart mysqld）
  2. 再开启查询执行计划（show variables like '%profiling%'; set profiling=1;）
  3. 执行语句两次：select * from mydb.mytbl where id=1;
  4. 显示最近执行的语句：show profiles;
  5. 显示执行计划：show profile cpu,block io for query 【show profiles中显示的语句的Query_ID】;

注意：SQL必须一致，否则不能命中缓存。

如果对数据库表进行insert，update，delete这个时候，缓存会失效！

如：select * from mydb.mytbl where id=2 和 select * from mydb.mytbl where id>1 and id<3 虽然查询结果一致，但并没有命中缓存。

MySQL的查询流程

​ 首先，Mysql客户端通过协议与MySQL服务器连接，发送查询语句，先检查缓存，如果命中，直接返回结果，否则进行语句解析，也就是说，在解析查询之前，服务器回显查询缓存，它存储SELECT语句以及相应的查询结果集。

​ 如果某个查询结果已经位于缓存中，服务器就不会在对查询进行解析，优化，以及执行。它仅仅讲缓存中的结果返回给用户即可，这将大大提高系统的性能。

​ 语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。

​ MySQL的解析器将使用MySQL语法规则验证和解析查询。

​ 预处理器根据一些MySQL语法规则验证和解析查询。

​ 查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。

​ 一条查询可以由很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

​ 然后，MySQL默认使用的B+TREE索引，并且一个大致方向是：无论怎么写SQL，至少在目前来说，MySQL至少用到表中的一个索引。

四、数据结构

BTree索引

​ 也就是B树，平衡树。

​ B树通过重新组织节点，降低树的高度，并且减少IO读写次数来提升效率。

​ 关键字集合分布在整颗树种，即叶子节点和非叶子节点都存放数据。

B+Tree索引

​ B+树是B树的升级版本，区别是所有数据只出现在叶子节点种，即只有叶子节点存放数据，非叶子节点只是叶子节点种数据的键值和指针。

​ 所有的叶子节点中包含了全部数据信息，以及指向这些数据记录的指针，叶子节点之间通过指针连接，并且叶子节点本身依靠键值的大小自小而大的顺序连接。

​ 由于B+树的非叶子节点不存储数据，因此每个节点可以存储更多的信息。

比较

​ 首先，B+树的查找和B树一样，起始于根节点，自顶向下遍历树。

​ 不同的是，B+树中间节点不存储数据，只有键值和指针，而B树每个节点要存储的键值和实际数据，这就意味着同样的大小的磁盘块B+树可以容纳更多节点元素，在相同的数据量下，B+树更加矮胖，IO操作更少。

​ 现代操作系统中，磁盘的存储结构使用的是B+树机制，MySQL的InnoDB引擎的存储方式也是B+树机制。

五、存储引擎

查看存储引擎：show engines;

查看默认的存储引擎：show variables like '%storage_engine%';

InnoDB存储引擎

​ 大于等于5.5之后，默认采用InnoDB引擎。

​ InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期事务。可以确保事务的完整提交和回滚。

​ 除了增加和查询外，还需要更新，删除操作，那么，应该有限选择InnoDB存储引擎。

​ 除非有非常特别的原因需要使用其他引擎，否则应该有限考虑InnoDB引擎。

MyISAM存储引擎

​ MyISAM提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

​ 5.5之前的默认存储引擎。

Archive存储引擎

​ Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。

​ Archive表适合日志和数据采集（档案）类应用。

​ 根据英文的测试结论来看，Archive表比AyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

Blackhole引擎

​ blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。

​ 但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单的记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

CSV引擎

​ CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。

​ CSV引擎可以作为一种数据交换的机制，非常有用。

​ CSV存储的数据直接可以在操作系统里，用文本编辑器，或者execel读取。

Memory引擎

​ 如果需要快速的访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表非常有用。

​ Memory表至少比MyISAM表要快一个数量级。

Federated引擎

​ Fegerated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，默认是禁用的。

InnoDB和MyISAM

对比项MyISAMInnoDB
外键不支持支持
事务不支持支持
行表锁表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存只缓存索引，不缓存真实数据不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
自带系统表使用YN
关注点性能：节省资源、消耗少、简单业务事务：并发写、事务、更大资源
默认安装YY
默认使用NY

索引失效条件？

1.计算函数导致索引失效；

2.范围条件导致右边的列索引失效；

3.不等于（!=或者<>）导致索引失效

4.like以通配符%开头索引失效；

5.类型转换导致索引失效

6.发生类型转换，索引失效。

优化建议？

1.对于单值索引，尽量选择过滤性更好的索引

2.在选择组合索引的时候，过滤性最好的字段在索引字段

3.选择组合索引时，尽量包含where中更多字段的索引

4.组合索引出现范围查询，尽量把这个字

avatar
文章
阅读
粉丝