武汉PHP培训
达内武汉民大中心

15827352908

热门课程

武汉PHP培训:深入剖析MySQL配置工作原理

  • 时间:2018-01-11 15:57
  • 发布:武汉PHP培训
  • 来源:互联网

    MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品.MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件.

    MySQL是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性.

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

    MySQL配置工作原理

    MySQL从哪儿获得配置信息:命令行参数和配置文件.类Unix系统中,配置文件一般位于 /etc/my.cnf 或者 /etc/mysql/my.cnf.在启动时,可以通过命令行参数指定配置文件的位置,当然命令行中也可以指定其它参数,服务器会读取配置文件的内容,删除所有注释和换行,然后和命令行选项一起处理.

    任何打算长期使用的配置项都应该写入配置文件,而不是在命令行中指定.一定要清楚的知道MySQL使用的配置文件位置,在修改时不能想当然,比如,修改了/etc/my.cnf的配置项,但MySQL实际并未使用这个配置文件.如果你不知道当前使用的配置文件路径,可以尝试:

    root@msc3:~# which mysqld

    /usr/sbin/mysqld

    root@msc3:~# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'

    Default options are read from the following files in the given order:

    /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

    一个典型的配置文件包含多个部分,每个部分的开头是一个方括号括起来的分段名称.MySQL程序通常读取跟它同名的分段部分,比如,许多客户端程序读取client部分.服务器通常读取mysqld这一段,一定要确认配置项放在了文件正确的分段中,否则配置是不会生效的.

    MySQL每一个配置项均使用小写,单词之间用下划线或者横线隔开,虽然我们常用的分隔符是下划线,但如果在命令行或者配置文件中见到如下配置,你要知道,它们其实是等价的:

    # 配置文件

    max_connections=5000

    max-connections=5000

    # 命令行

    /usr/sbin/mysqld --max_connections=5000

    /usr/sbin/mysqld --max-connections=5000

    配置项可以有多个作用域:全局作用域、会话作用域(每个连接作用不同)、对象作用域.很多会话级配置项跟全局配置相等,可以认为是默认值,如果改变会话级配置项,它只影响改动的当前连接,当连接关闭时,所有的参数变更都会失效.下面有几个示例配置项:

    query-cache-size 全局配置项

    sort-buffer-size 默认全局相同,但每个线程里也可以设置

    join-buffer-size 默认全局,且每个线程也可以设置.但若一个查询中关联多张表,可以为每个关联分配一个关联缓存(join-buffer),所以一个查询可能有多个关联缓冲.

    配置文件中的变量(配置项)有很多(但不是所有)可以在服务器运行时修改,MySQL把这些归为动态配置变量:

    # 设置全局变量,GLOBAL和@@global作用是一样的

    set GLOBAL sort-buffer-size = <value>

    set @@global.sort-buffer-size := <value>

    # 设置会话级变量,下面6种方式作用是一样的

    # 即:没有修饰符、SESSION、LOCAL等修饰符作用是一致的

    set SESSION sort-buffer-size = <value>

    set @@session.sort-buffer-size := <value>

    set @@sort-buffer-size = <value>

    set LOCAL sort-buffer-size = <value>

    set @@ocal.sort-buffer-size := <value>

    set sort-buffer-size = <value>

    # set命令可以同时设置多个变量,但其中只要有一个变量设置失败,所有的变量都未生效

    SET GLOBAL sort-buffer-size = 100, SESSION sort-buffer-size = 1000;

    SET GLOBAL max-connections = 1000, sort-buffer-size = 1000000;

    动态的设置变量,MySQL关闭时这些变量都会失效.如果在服务器运行时修改了变量的全局值,这个值对当前会话和其他任何已经存在的会话都不起效果,这是因为会话的变量值是在连接创建时从全局值初始化而来的.注意,在配置修改后,需要确认是否修改成功.

    你可能注意到,上面的示例中,有些使用"=",有些使用":=".对于set命令本身来说,两种赋值运算符没有任何区别,在命令行中使用任一运算符符,均可以生效.而在其他语句中,赋值运算符必须是":=",因为在非set语句中"="被视为比较运算符.具体可以参考如下示例:

    详细示例可以参考:stackoverflow
武汉PHP培训
    // @exp 表示用户变量,上面的示例均是系统变量

    // 错误

    set @user = 123456;

    set @group = select GROUP from USER where User = @user;

    select * from USER where GROUP = @group;

    // 正确

    SET @user := 123456;

    SELECT @group := `group` FROM user WHERE user = @user;

    SELECT * FROM user WHERE `group` = @group;

    有一些配置使用了不同的单位,比如table-cache变量指定表可以被缓存的数量,而不是表可以被缓存的字节数.而key-buffer-size则是以字节为单位.

    还有一些配置可以指定后缀单位,比如1M=1024*1024字节,但需要注意的是,这只能在配置文件或者作为命令行参数时有效.当使用SQL的SET命令时,必须使用数字值1048576或者1024*1024这样的表达式,但在配置文件中不能使用表达式.

    配置MySQL

    们常常动态的修改配置,但请务必小心,因为它们可能导致数据库做大量耗时的工作,从而影响数据库的整体性能.比如从缓存中刷新脏块,不同的刷新方式对I/O的影响差别很大(后文会具体说明).最好把一些好的习惯作为规范合并到工作流程中去,就比如:

    1:不要通过配置项的名称来推断一个变量的作用

    不要通过配置项的名称来推断一个变量的作用,因为它可能跟你想象的完全不一样.比如:

    read-buffer-size:当MySQL需要顺序读取数据时,如无法使用索引,其将进行全表扫描或者全索引扫描.这时,MySQL按照数据的存储顺序依次读取数据块,每次读取的数据块首先会暂存在缓存中,当缓存空间被写满或者全部数据读取结束后,再将缓存中的数据返回给上层调用者,以提高效率.

    read-rnd-buffer-size:和顺序读取相对应,当MySQL进行非顺序读取(随机读取)数据块的时候,会利用这个缓冲区暂存读取的数据.比如:根据索引信息读取表数据、根据排序后的结果集与表进行Join等等.总的来说,就是当数据块的读取需要满足一定的顺序的情况下,MySQL 就需要产生随机读取,进而使用到read-rnd-buffer-size参数所设置的内存缓冲区.

    这两个配置都是在扫描MyISAM表时有效,且MySQL会为每个线程分配内存.对于前者,MySQL只会在查询需要使用时才会为该缓存分配内存,并且一次性分配该参数指定大小的全部内存,而后者同样是需要时才分配内存,但只分配需要的内存大小而不是参数指定的数值,max-read-rnd-buffer-size(实际上没有这个配置项)这个名字更能表达这个变量的实际含义.

    2:不要轻易在全局修改会话级别的配置

    对于某些会话级别的设置,不要轻易的在全局增加它们的值,除非你确认这样做是对的.比如:sort-buffer-size,该参数控制排序操作的缓存大小,MySQL只会在查询需要做排序操作时才会为该缓冲分配内存,一旦需要排序,就会一次性分配指定大小的内存,即使是非常小的排序操作.因此在配置文件中应该配置的小一些,然后在某些查询需要排序时,再在连接中把它调大.比如:

    SET @@seession.sort-buffer-size := <value>

    -- 执行查询的sql

    SET @@seession.sort-buffer-size := DEFAULT #恢复默认值

    # 可以将类似的代码封装在函数中方便使用.

    3:配置变量时,并不是值越大越好

    配置变量时,并不是值越大越好,而且如果设置的值太高,可能更容易导致内存问题.在修改完成后,应该通过监控来确认变量的修改对服务器整体性能的影响.

    4:规范注释,版本控制

    在配置文件中写好注释,可能会节省自己和同事大量的工作,一个更好的习惯是把配置文件置于版本控制之下.

    说完了好习惯,再来说说不好的习惯.

    1:根据一些"比率"来调优

    一个经典的按"比率"调优的经验法则是,缓存的命中率应该高于某个百分比,如果命中率过低,则应该增加缓存的大小.这是非常错误的意见,大家可以仔细思考一下:缓存的命中率跟缓存大小有必然联系吗?(分母变大,值就变大了?)除非确实是缓存太小了.关于MyISAM键缓冲命中率,下文会详细说明.

    2:随便使用调优脚本

    尽量不要使用调优脚本!不同的业务场景、不同的硬件环境对MySQL的性能要求是不一样的.比如有些业务对数据的完整性要求较高,那么就一定要保证数据不丢失,出现故障后可恢复数据,而有些业务却对数据的完整性要求没那么高,但对性能要求更高.因此,即使是同一个变量,在这两个不同场景下,其配置的值也应该是不同的.那你还能放心的使用网上找到的脚本吗 ?

    总结

    以上是对Java开发大型互联网-深入剖析MySQL配置工作原理,分享给大家,希望大家可以了解什么Java开发大型互联网-深入剖析MySQL配置工作原理

    本篇文章是由武汉PHP培训为您呈现,希望给您带来更多更好的文章,喜欢的朋友们可以添加微信公众号.

更多武汉PHP培训相关咨询,请扫描下方二维码

武汉PHP培训
上一篇:武汉PHP培训:Java研发大学毕业后为何找不到理想的工作
下一篇:武汉PHP培训:Java线程池的工作原理,好处和注意事项

PHP 优化写法之字符串

CodeIgniter的优缺点

PHP高性能框架之一Yii

选择城市和中心
贵州省

广西省

海南省

有位老师想和您聊一聊