当前位置:首页 > 编程教程 > mysql技术文章 > MySQL性能优化之路---修改配置文件my.cnf

MySQL通过修改配置文件my.cnf优化性能

  • 发布时间:
  • 作者:码农之家
  • 点击:176

这篇文章主要知识点是关于MySQL、性能优化、my.cnf、mysql性能优化之索引优化 的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下电子书

MySQL必知必会
  • 类型:MySQL大小:5.7 MB格式:PDF作者:福塔
立即下载

MySQL性能优化之路---修改配置文件my.cnf

在Apache, PHP, MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分。对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验进行判断,然后设置合理的参数。 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化。

一、服务器硬件对MySQL性能的影响

①磁盘寻道能力(磁盘I/O),以目前高转速SCSI硬盘(7200转/秒)为例,这种硬盘理论上每秒寻道7200次,这是物理特性决定的,没有办法改变。MySQL每秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知。所以,通常认为磁盘I/O是制约MySQL性能的最大因素之一,对于日均访问量在100万PV以上的Discuz!论坛,由于磁盘I/O的制约,MySQL的性能会非常低下!解决这一制约因素可以考虑以下几种解决方案: 使用RAID-0+1磁盘阵列,注意不要尝试使用RAID-5,MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。

②CPU 对于MySQL应用,推荐使用S.M.P.架构的多路对称CPU,例如:可以使用两颗Intel Xeon 3.6GHz的CPU,现在我较推荐用4U的服务器来专门做数据库服务器,不仅仅是针对于mysql。

③物理内存对于一台使用MySQL的Database Server来说,服务器内存建议不要小于2GB,推荐使用4GB以上的物理内存,不过内存对于现在的服务器而言可以说是一个可以忽略的问题,工作中遇到了高端服务器基本上内存都超过了16G。

二、MySQL自身因素当解决了上述服务器硬件制约因素后,让我们看看MySQL自身的优化是如何操作的。 对MySQL自身的优化主要是对其配置文件my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。 由于my.cnf文件的优化设置是与服务器硬件配置息息相关的, 因而我们指定一个假想的服务器硬件环境:CPU: 2颗Intel Xeon 2.4GHz 内存: 4GB DDR 硬盘: SCSI 73GB(很常见的2U服务器 ) 。

下面,我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明:

[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking
#避免MySQL的外部锁定,减少出错几率增强稳定性。
skip-name-resolve
#禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!
back_log = 384
#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。
key_buffer_size = 256M
#key_buffer_size指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意:该参数值设置的过大反而会是服务器整体效率降低!
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小。注意:该参数对应的分配内存是每连接独占,如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。所以,对于内存在4GB左右的服务器推荐设置为6-8M。
read_buffer_size = 4M
#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。
join_buffer_size = 8M
#联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
#指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察,如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲;Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。
tmp_table_size = 256M
max_connections = 768
#指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示,则需要增大该参数值。
max_connect_errors = 10000000
wait_timeout = 10
#指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。
thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量*2,在本例中,服务器有2颗物理CPU,而每颗物理CPU又支持H.T超线程,所以实际取值为4*2=8
skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接!
table_cache=1024
#物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=4M
#默认为2M
innodb_flush_log_at_trx_commit=1
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1
innodb_log_buffer_size=2M
#默认为1M
innodb_thread_concurrency=8
#你的服务器CPU有几个就设置为几,建议用默认一般为8
key_buffer_size=256M
#默认为218,调到128最佳
tmp_table_size=64M
#默认为16M,调到64-256最挂
read_buffer_size=4M
#默认为64K
read_rnd_buffer_size=16M
#默认为256K
sort_buffer_size=32M
#默认为256K
thread_cache_size=120
#默认为60
query_cache_size=32M
※值得注意的是:

很多情况需要具体情况具体分析

一、如果Key_reads太大,则应该把my.cnf中Key_buffer_size变大,保持Key_reads/Key_read_requests至少1/100以上,越小越好。

二、如果Qcache_lowmem_prunes很大,就要增加Query_cache_size的值。

mysql性能优化之索引优化

  作为免费又高效的数据库,mysql基本是首选。良好的安全连接,自带查询解析、sql语句优化,使用读写锁(细化到行)、事物隔离和多版本并发控制提高并发,完备的事务日志记录,强大的存储引擎提供高效查询(表记录可达百万级),如果是InnoDB,还可在崩溃后进行完整的恢复,优点非常多。即使有这么多优点,仍依赖人去做点优化,看书后写个总结巩固下,有错请指正。

  完整的mysql优化需要很深的功底,大公司甚至有专门写mysql内核的,sql优化攻城狮,mysql服务器的优化,各种参数常量设定,查询语句优化,主从复制,软硬件升级,容灾备份,sql编程,需要的不是一星半点的知识与时间来掌握,作为一名像俺这样的菜鸟开发,强吃这么多消化不了也没意义:没地儿用啊,况且还有运维和dba,还不如把手头的业务写好,也就是写好点的sql,而且很多sql语句优化跟索引还是有很大关系的。

  首先,mysql的查询流程大致是:mysql客户端通过协议与mysql服务器建立连接,发送查询语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析,有一系列预处理,比如检查语句是否写正确了,然后是查询优化(比如是否使用索引扫描,如果是一个不可能的条件,则提前终止),生成查询计划,然后查询引擎启动,开始执行查询,从底层存储引擎调用API获取数据,最后返回给客户端。怎么存数据、怎么取数据,都与存储引擎有关。然后,mysql默认使用的BTREE索引,并且一个大方向是,无论怎么折腾sql,至少在目前来说,mysql最多只用到表中的一个索引。

  mysql通过存储引擎取数据,自然跟存储引擎有很大关系,不同的存储引擎索引也不一样,如MyISAM的全文索引,即便索引叫一个名字内部组织方式也不尽相同,最常用的当然就是InnoDB了(还有完全兼容mysql的MariaDB,它的默引擎是XtraDB,跟InnoDB很像),这里写的是InnoDB引擎。而索引的实现也跟存储引擎,按照实现方式分,InnoDB的索引目前只有两种:BTREE索引和HASH索引。通常我们说的索引不出意外指的就是B树索引,InnoDB的BTREE索引,实际是用B+树实现的,因为在查看表索引时,mysql一律打印BTREE,所以简称为B树索引。至于B树与B+树的区别,原谅的俺数据结构没好好学,也是需要补的地方。

  使用了BTREE索引,意味着所有的索引是按顺序排列存储的(升序),mysql就是这么干的,mysl中的BTREE索引抽象结构如下图(参考高性能mysql)。

mysql性能优化之索引优化

  结构中,每一层节点均从左往右从小到大排列,key1 < key2 < ... < keyN,对于小于key1或在[key1,key2)或其他的值的节点,在进入叶子节点查找,是一个范围分布,同时,同一层节点之间可直接访问,因为他们之间有指针指向联系(MyISAM的BTREE索引没有)。每次搜索是一个区间搜索,有的话就找到了,没有的话就是空。索引能加快访问速度,因为有了它无需全表扫描数据(不总是这样),根据查找的值,跟节点中的值比较,通常使用二分查找,对于排好序的数值来说,平均速度几乎是最快的。

  val指向了哪里,对于InnoDB,它指向的就是表数据,因为InnoDB的表数据本身就是索引文件,这是与MyISAM索引的显著区别,MyISAM的索引指向的是表数据的地址(val指向的是类似于0x7DFF..之类)。比如对于InnoDB一个主键索引来说,可能是这样

      mysql性能优化之索引优化  

  InnoDB的索引节点val值直接指向表数据,即它的叶子节点就是表数据,它们连在一起,表记录行没有再单独放在其他地方,叶子节点(数据)之间可访问。

  前面在BTREE的抽象结构中,索引值的节点是放在页中的,这里有两个需注意的问题:

  1. 叶子页、页中的值(上上图),即所谓的页是啥,俺加了个节点注释,即这里的页最小可近似当做是单个节点。我们知道计算机的存储空间是一块一块的,通常一块用完了再用另一块,如果上一块只剩余5kb,但这里刚好要申请8kb的空间,就得在一个新的块上申请这个空间,然后以后的申请又接在这个8kb后面,只要这个块的空间足够,那么上一块的5kb通常就成了所谓的“碎片”,电脑用多了会有很多这样零散的碎片空间,因此有碎片整理。在mysql中,这里的页可理解为块存储空间,即索引的树节点是存放在页中的,每一页(称为逻辑页)有固定大小,InnoDB目前是16kb,一页用完了,当继续插入表生成新的索引节点时,就去新的页中存储这个节点,再有新的节点就继续放在这个新的页的节点后面。

  2. 页分裂问题,一页总要被存满,然后新开一页继续,这种行为被称作页分裂。何时开辟新的页,mysql规定了一个分裂因子,达到页存储空间的15/16则存到下一页。页分裂的存在可能极大影响性能维护索引的性能。通常提倡的是,设定一个无意义的整数自增索引,有利于索引存储

    mysql性能优化之索引优化

  如果非自增或不是整数索引,如非自增整数、类似MD5的字符串,以他们作为索引值时,因为待插入的下一条数据的值不一定比上一条大,甚至比当前页所有值都小,需要跑到前几页去比较而找到合适位置,InnoDB无法简单的把新行插入到上一行后面,而找到并插入索引后,可能导致该页达到分裂因子阀值,需要页分裂,进一步导致后面所有的索引页的分裂和排序,数据量小也许没什么问题,数据量大的话可能会浪费大量时间,产生许多碎片。

    mysql性能优化之索引优化

  主键总是唯一且非空,InnoDB自动对它建立了索引(primary key),对于非主键字段上建立的索引,又称辅助索引,索引排列也是顺序排列,只是它还附带一个本条记录的主键值的数据域,不是指向本数据行的指针,在使用辅助索引查找时,先找到对应这一列的索引值,再根据索引节点上的另一个数据域---主键值,来查找该行记录,即每次查找实际经过查找了两次。额外的数据域存储主键值的好处是,当页分裂发生时,无需修改数据域的值,因为即使页分裂,该行的主键值是不变的,而地址就变了。比如name字段的索引简示如下 

    mysql性能优化之索引优化  

   包含一列的索引称为单列索引,多列的称为复合索引,因为BTREE索引是顺序排列的,所以比较适合范围查询,但是在复合索引中,还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。

  比如有这样一张表

create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年龄',
    pos varchar(20) not null default '' comment '职位',
    add_time timestamp not null default current_timestamp comment '入职时间'
  ) charset utf8 comment '员工记录表';

  添加三列的复合索引

alter table staffs add index idx_nap(name, age, pos);

  在BTREE索引的使用上,以下几种情况可以用到该索引或索引的一部分(使用explain简单查看使用情况):

  1. 全值匹配

  如select * from staffs where name = 'July' and age = '23' and pos = 'dev' ,key字段显示使用了idx_nap索引

mysql性能优化之索引优化

  2. 匹配最左列,对于复合索引来说,不总是匹配所有字段列,但是可以匹配索引中靠左的列

  如select * from staffs where name = 'July' and age = '23',key字段显示用到了索引,注意,key_len字段(表示本次语句使用的索引长度)数值比上一条小了,意思是它并未使用全部索引列(通常这个长度可估摸着用了哪些索引列,埋个坑),事实上只用到了name和age列

mysql性能优化之索引优化

  再试试select * from staffs where name = 'July',它也用了索引,key_len值更小,实际只用到了索引中的name列

mysql性能优化之索引优化

  3. 匹配列前缀,即一个索引中列的前一部分,主要用在模糊匹配,如select * fromstaffs where name like 'J%',explain信息的key字段表示使用了索引,但是mysql的B树索引不能非列前缀的模糊匹配,如select * from staffs where name like '%y' 或者 like '%u%',据说是由于底层存储引擎的API限制

mysql性能优化之索引优化

  4. 匹配范围,如select * from staffs where name > 'Mary',但俺在测试时发现>可以,>=却不行,至少在字符串列上不行(测试mysql版本5.5.12),然而在时间类型(timestamp)上却可以,不测试下还真不能确定说就用到了索引==

mysql性能优化之索引优化

  出于好奇测了下整型字段的索引(idx_cn(count, name),count为整型),发现整型受限制少很多,下面的都能用到索引,连前模糊匹配的都行

select * from indexTest1 where count > '10'
  select * from indexTest1 where count >= '10'
  select * from indexTest1 where count > '10%'
  select * from indexTest1 where count >= '10%'
  select * from indexTest1 where count > '%10%'
  select * from indexTest1 where count >= '%10%'

  5. 精确匹配一列并范围匹配右侧相邻列,即前一列是固定值,后一列是范围值,它用了name与age两个列的索引(key_len推测)

  如select * from staffs where name = 'July' and age > 25

mysql性能优化之索引优化

  6. 只访问索引的查询,比如staffs表的情况,索引建立在(name,age,pos)上面,前面一直是读取的全部列,如果我们用到了哪些列的索引,查询时也只查这些列的数据,就是只访问索引的查询,如

select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev'
  select name,age from staffs where name = July and age > 25

  第一句用到了全部索引列,第二句只用了索引前两列,select的字段就最多只能是这两列,这种查询情况的索引,mysql称为覆盖索引,就是索引包含(覆盖)了查询的全部字段。是不是用到了索引查询,在explain中需要看最后一个Extra列的信息,Using index表明使用了覆盖索引,同时Using where表明也使用了where过滤

mysql性能优化之索引优化

  7. 前缀索引

  区别于列前缀(类似like 'J%'形式的模糊匹配)和最左列索引(顺序取索引中靠左的列的查询),它只取某列的一部分作为索引。通常在说InnoDB跟MyISAM的区别时,一个明显的区别是:MyISAM支持全文索引,而InnoDB不行,甚至对于text、blob这种超长的字符串或二进制数据时,MyISAM会取前多少个字符作为索引,InnoDb的前缀索引跟这个类似,某些列,一般是字符串类型,很长,全部作为索引大大增加存储空间,索引也需要维护,对于长字符串,又想作为索引列,一个可取的办法就是取前一部分(前缀),代表一整列作为索引串,问题是:如何确保这个前缀能代表或大致代表这一列?所以mysql中有个概念是索引的选择性,是指索引中不重复的值的数目(也称基数)与整个表该列记录总数(#T)的比值,比如一个列表(1,2,2,3),总数是4,不重复值数目为3,选择性为3/4,因此选择性范围是[1/#T, 1],这个值越大,表示列中不重复值越多,越适合作为前缀索引,唯一索引(UNIQUE KEY)的选择性是1。

  比如有一列a varchar(255),以它作前缀索引,比如以7个测试,逐个增加看看选择性值增长到那个数基本不变,就表示可以代表整列了,再结合这个长度的索引列是否存储数据太多,做个权衡,基本就行了。但如果这个选择性本来就小的可怜还是算了

select count(distinct left(a, 7))/count(*) as non_repeat from tab;

  定好一个前缀数目,如9,添加索引时可以这样

alter table tab add index idx_pn(name(9)) --单独前缀索引
  alter table tab add index idx_cpn(count, name(9)) --复合前缀索引

  以上为常见的使用索引的方式,有这么些情况不能用或不能全用,有的就是上面情况的反例,以key(a, b, c)为例

  1. 跳过列,where a = 1 and c = 3,最多用到索引列a;where b = 2 and c = 3,一个也用不到,必须从最左列开始

  2. 前面是范围查询,where a = 1 and b > 2 and c = 3,最多用到 a, b两个索引列;

  3. 顺序颠倒,where c = 3 and b = 2 and a = 1,一个也用不到;

  4. 索引列上使用了表达式,如where substr(a, 1, 3) = 'hhh',where a = a + 1,表达式是一大忌讳,再简单mysql也不认。有时数据量不是大到严重影响速度时,一般可以先查出来,比如先查所有有订单记录的数据,再在程序中去筛选以'cp1001'开头的订单,而不是写sql过滤它;

  5. 模糊匹配时,尽量写 where a like 'J%',字符串放在左边,这样才可能用得到a列索引,甚至可能还用不到,当然这得看数据类型,最好测试一下。

  排序对索引的影响

  order by是经常用的语句,排序也遵循最左前缀列的原则,比如key(a, b),下面语句可以用到(测试为妙)

select * from tab where a > 1 order by b
  select * from tab where a > 1 and b > '2015-12-01 00:00:00' order by b
  select * from tab order by a, b

  以下情况用不到

  1. 非最左列,select * from tab order by b;

  2. 不按索引列顺序来的,select * from tab where b > '2015-12-01 00:00:00' order by a;

  3. 多列排序,但列的顺序方向不一致,select * from tab a asc, b desc。

  聚簇索引与覆盖索引

  前面说到,mysql索引从结构上只有两类,BTREE与HASH,覆盖索引只是在查询时,要查询的列刚好与使用的索引列完全一致,mysql直接扫描索引,然后就可返回数据,大大提高效率,因为不需再去原表查询、过滤,这种形式下的索引称作覆盖索引,比如key(a,b),查询时select a,b from tab where a = 1 and b > 2,本质原因:BTREE索引存储了原表数据。

  聚簇索引也不是单独的索引,前面简要写到,BTREE索引会把数据放在索引中,即索引的叶子页中,包括主键,主键是跟表数据紧挨着放在一起的,因为表数据只有一份,一列键值要跟每一行数据都紧挨在一起,所以一张表只有一个聚簇索引,对于mysql来说,就是主键列,它是默认的。

  聚簇索引将表数据组织到了一起(参考前面主键索引简略图),插入时严重依赖主键顺序,最好是连续自增,否则面临频繁页分裂问题,移动许多数据。

  哈希索引

  简要说下,类似于数据结构中简单实现的HASH表(散列表)一样,当我们在mysql中用哈希索引时,也是对索引列计算一个散列值(类似md5、sha1、crc32),然后对这个散列值以顺序(默认升序)排列,同时记录该散列值对应数据表中某行的指针,当然这只是简略模拟图

      mysql性能优化之索引优化

  比如对姓名列建立hash索引,生成hash值按顺序排列,但是顺序排列的hash值并不对应表中记录,从地址指针可反应出来,而且,hash索引可能建立在两列或者更多列上,取得是多列数据后的hash值,它不存储表中数据。它先计算列数据的hash值,与索引中的hash值比较,找到了然后比对列数据是否相等,可能涉及其他列条件,然后返回数据。hash当然会有冲突,即碰撞,除非有很多冲突,一般hash索引效率很高,否则hash维护成本较高,因此哈希索引通常用在选择性较高的列上面。哈希索引的结构决定了它的特点:

  1. hash索引只是hash值顺序排列,跟表数据没有关系,无法应用于order by;

  2. hash索引是对它的所有列计算哈希值,因此在查询时,必须带上所有列,比如有(a, b)哈希索引,查询时必须 where a = 1 and b = 2,少任何一个不行;

  3. hash索引只能用于比较查询 = 或 IN,其他范围查询无效,本质还是因不存储表数据;

  4. 一旦出现碰撞,hash索引必须遍历所有的hash值,将地址所指向数据一一比较,直到找到所有符合条件的行。

  填坑

  前面提到通过explain的key_len字段,可大致估计出用了哪些列,索引列的长度跟索引列的数据类型直接相关,一般,我们说int是4字节,bigint8字节,char是1字节,考虑到建表时要指定字符集,比如utf8,还跟选的字符集有关(==!),在utf8下边,一个char是3字节,但是知道这些仍不能说key_len就是将用到的索引列的数据类型代表字节数一加不就完啦?事实总有点区别,测试方法比较机械(以下基于mysql 5.5.2)

  建表,加索引,int型

--测试表
  create table keyLenTest1(
    id int primary key auto_increment,
    typeKey int default 0 ,
    add_time timestamp not null default current_timestamp
  ) charset utf8
  --添加索引
  alter table keyLenTest1 add index idx_k(typeKey);

  可知int型索引默认长度为5,在4字节基础上+1

mysql性能优化之索引优化

  char型

 --改为char型,1个字符
  alter table keyLenTest1 modify typeKey char(1);

mysql性能优化之索引优化

--改为char型,2个字符
  alter table keyLenTest1 modify typeKey char(2);

mysql性能优化之索引优化

  可知,char型初始是4字节(3+1 bytes),后续按照3字节递增

  varchar型

--改为varchar型,1个字符
  alter table keyLenTest1 modify typeKey varchar(1);

mysql性能优化之索引优化

--改为varchar型,2个字符
  alter table keyLenTest1 modify typeKey varchar(2);

mysql性能优化之索引优化

  可知,varchar型,1个字符时,key_len为6,以后以3字节递增

  所以,如果一个语句用到了int、char、varchar,key_len如何计算以及用了哪些索引列应该很清楚了。

  如果想了解的更详细点,explain各字段意义,索引的更多细节,除了explain,还有show profiles、慢查询日志等(没细看),推荐看高性能mysql,毕竟俺写的太肤浅。

以上就是本次给大家分享的关于java的全部知识点内容总结,大家还可以在下方相关文章里找到相关文章进一步学习,感谢大家的阅读和支持。

您可能感兴趣的文章:

  • MySQL性能优化总结
  • MySQL 5.7增强版Semisync Replication性能优化知识点总结
  • MySQL性能优化 相关电子书
    学习笔记
    网友NO.146389

    MySQL DBA教程:Mysql性能优化之缓存参数优化

    数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO。本文先从 MySQL 数据库IO相关参数(缓存参数)的角度来进行IO优化: 一、query_cache_size/query_cache_type (global) Query cache 作用于整个 MySQL Instance,主要用来缓存 MySQL 中的 ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当我们打开了 Query Cache 功能,MySQL在接受到一条select语句的请求后,如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache,或者已经显式申明需要使用Query Cache),MySQL 会直接根据预先设定好的HASH算法将接受到的select语句以字符串……

    网友NO.961651

    mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

    此配置是老男孩生产线上使用的配置,在培训的时候,他给的,我在这里,对各参数添加了中文说明 这配置已经优化的不错了,如果你的mysql没有什么特殊情况的话,可以直接使用该配置参数 MYSQL服务器my.cnf配置文档详解 硬件:内存16G [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /usr/local/mysql datadir = /data/3306/data open_files_limit = 10240 back_log = 600 #在MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。 max_connections = 3000 #MySQL允许最大的进程连接数,如果经常出现Too Many Connections的错误提示,则需要增大此值。 max_connect_errors = 6000 #设置每个主机的连接……

    网友NO.679982

    mysql千万级数据分页查询性能优化

    mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下。 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒select * from order limit 100, 20 0.016秒select * from order limit 1000, 20 0.047秒select * from order limit 10000, 20 0.094秒 我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为40w看下 select * from order limit 400000, 20 3.229秒 再看我们取最后一页记录的时间 select * from order limit 800000, 20 37.44秒 显然这种时间是无法忍受的。 从中我们也能总结出两件事情: 1)limit语句的查询时间与起始记录的位置成正比 2)mysql的limit语句……

    <
    1
    >

    Copyright 2018-2020 www.xz577.com 码农之家

    投诉 / 推广 / 赞助:QQ:520161757