mysql数据库怎么调优
如何对mysql服务器进行调优
①.、选取最适用的字段属性
另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值.
对于某些文本字段,例如"省份"或者"性别",我们可以将它们定义为ENUM类型.因为在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多.这样,我们又可以提高数据库的性能.
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易.但是,有些情况下,子查询可以被更有效率的连接（JOIN）.. 替代.例如,假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成：
SELECT * FROM customerinfo
如果使用连接（JOIN）.. 来完成这个查询工作,速度将会快很多.尤其是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询如下：
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
连接（JOIN）.. 之所以更有效率一些,是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作.
SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
SELECT Name, Supplier FROM product
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询,但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的.更多的时候是需要用到一系列的语句来完成某种工作.但是在这种情况下,当这个语句块中的某一条语句运行出错的时候,整个语句块的操作就会变得不确定起来.设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况：第一个表中成功更新后,数据库突然出现意外状况,造成第二个表中的操作没有完成,这样,就会造成数据的不完整,甚至会破坏数据库中的数据.要避免这种情况,就应该使用事务,它的作用是：要么语句块中每条语句都操作成功,要么都失败.换句话说,就是可以保持数据库中数据的一致性和完整性.事物以BEGIN 关键字开始,COMMIT关键字结束.在这之间的一条SQL操作失败,那么,ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态.
BEGIN;
UPDATE inventory SET Quantity=11
WHERE item='book';
COMMIT;
事务的另一个重要作用是当多个用户同时使用相同的数据源时,它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不被其它的用户所干扰.
尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很大的应用系统中.由于在事务执行的过程中,数据库将会被锁定,所以呢其它的用户请求只能暂时等待直到该事务结束.如果一个数据库系统只有少数几个用户
来使用,事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟.
其实,有些情况下我们可以通过锁定表的方法来获得更好的性能.下面的例子就用锁定表的方法来完成前面一个例子中事务的功能.
LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem='book';
...
UNLOCK TABLES
这里,我们用一个 SELECT 语句取出初始数据,通过一些计算,用 UPDATE 语句将新值更新到表中.包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前,不会有其它的访问来对 inventory 进行插入、更新或者删除的操作.
锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性.这个时候我们就可以使用外键.例如,外键可以保证每一条销售记录都指向某一个存在的客户.今天这一节,外键可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID,任何一条没有合法CustomerID的记录都不会被更新或插入到 salesinfo中.
CREATE TABLE customerinfo
(
CustomerID INT NOT NULL ,
PRIMARY KEY ( CustomerID )
) TYPE = INNODB;
CREATE TABLE salesinfo
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENCES customerinfo
(CustomerID) ON DELETECASCADE
注意例子中的参数"ON DELETE CASCADE".该参数保证当 customerinfo 表中的一条客户记录被删除的时候,salesinfo 表中所有与该客户相关的记录也会被自动删除.如果要在 MySQL 中使用外键,一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB类型.该类型不是 MySQL 表的默认类型.定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB.如例中所示.
索引是提高数据库性能的常用方法,它可以令数据库服务器以比没有索引快得多的速度检索特定的行,尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候,性能提高更为明显.那该对哪些字段建立索引呢?一般说来,索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上.尽量不要对数据库中某个含有大量重复的值的字段建立索引.对于一个ENUM类型的字段来说,出现大量重复值是很有可能的情况,例如 customerinfo中的"province".. 字段,在这样的字段上建立索引将不会有什么帮助；相反,还有可能降低数据库的性能.我们在创建表的时候可以同时创建合适的索引,也可以使用ALTER TABLE或CREATE INDEX在以后创建索引.此外,MySQL
例如,在一个DATE类型的字段上使用YEAE()函数时,将会使索引不能发挥应有的作用.所以,下面的两个查询虽然返回的结果一样,但后者要比前者快得多.
同样的情形也会发生在对数值型字段进行计算的时候：
上面的两个查询也是返回相同的结果,但后面的查询将比前面的一个快很多.第三,在搜索字符型字段时,我们有时会使用 LIKE 关键字和通配符,这种做法虽然简单,但却也是以牺牲系统性能为代价的.例如下面的查询将会比较表中的每一条记录.
SELECT * FROM books
WHERE name like "MySQL%"
但是如果换用下面的查询,返回的结果一样,但速度就要快上很多：
WHERE name="MySQL"and name"MySQM"
最后,应该注意避免在查询中让MySQL进行自动类型转换,因为转换过程也会使索引变得不起作用.
MySQL深分页调优实战
商品评论系统数据量为十亿量级,所以呢对评论数据库做分库分表,单表的评论数据在百万级别.
每个商品的所有评论都是放在一个库的一张表里,确保作为用户在分页查询一个商品的评论时,一般都是直接从一个库的一张表里执行分页查询语句即可.
热门商品销量多达上百万,商品评论可能多达几十万条.有些用户就喜欢看商品评论,他就喜欢不停对某个热门商品评论不断进行分页,一页一页翻,有时候还会用上分页跳转功能,就是直接输入自己要跳到第几页.
这就涉及针对一个商品几十万评论的深分页问题.
简化后的对评论表进行分页查询的SQL：
比如用户选择了查看某个商品的评论,所以呢必须限定 Product_id ,同时还选了只看好评,所以 is_good_commit 也要限定,
评论表最核心的索引 index_product_id ,所以正常肯定走这索引：
但本案例不是这样,因为
这俩条件不是一个联合索引,所以会出现大量回表,耗时严重.
所以呢对该案例,一般采取如下方式改造分页查询语句：
该SQL的执行计划就会彻底改变其执行方式.
通常先执行括号里的子查询,子查询反而会使用PRIMARY聚簇索引,按聚簇索引id值的倒序方向进行扫描,扫描过程中就把符合
的数据筛选出来.
所以本案例,反而是优化成这种方式来执行分页,更合适,他只有一个扫描【聚簇索引】筛选符合你分页所有数据的成本：
SQL调优没有银弹：
不同场景,要具体情况具体分析,到底慢在哪儿,再针对性优化.
mysql 参数调优（11）之innodb_buffer_pool_instances设置多个缓冲池实例
pool_instances 可以设置为cpu核心数,它的作用是：
①.）对于缓冲池在数千兆字节范围内的系统,通过减少争用不同线程对缓存页面进行读写的争用,将缓冲池划分为多个单独的实例可以提高并发性.可以类比为 java中的 ThreadLocal 线程本地变量 就是为每个线程维护一个buffer pool实例,这样就不用去争用同一个实例了.相当于减少高并发下mysql对INNODB_BUFFER缓冲池的争用.
mysql调优技巧 增加线程缓存大小
增加线程缓存大小
MySQL提供了许多与连接线程相关的服务器变量：
线程缓存大小由thread_cache_size系统变量决定.默认值为0（无缓存）,这将导致为每个新连接设置一个线程,并在连接终止时需要处理该线程.如果希望服务器每秒接收数百个连接请求,那么应该将thread_cache_size设置的足够高,以便大多数新连接可以使用缓存线程.可以在服务器启动或运行时设置max_connections的值.
还应该监视缓存中的线程数（Threads_cached）以及创建了多少个线程,因为无法从缓存中获取线程（Threads_created）.关于后者,如果Threads_created继续以每分钟多于几个线程的增加,请考虑增加thread_cache_size的值.
使用MySQL show status命令显示MySQL的变量和状态信息.这里有几个例子：
Monyog线程缓存监测
Monyog提供了一个监控线程缓存的屏幕,名为"线程".与MySQL线程相关的服务器变量映射到以下Monyog指标：
Monyog线程屏幕还包括"线程缓存命中率"指标.这是一个提示线程缓存命中率的指标.如果值较低,则应该考虑增加线程缓存.在状态栏以百分比形式显示该值；它的值越接近100％越好.
如果这些指标的值等于或超过指定值,则可以将每一个指标配置为发出警告和/或严重警报
MySQL性能调优 – 你必须了解的15个重要变量
前言:
①DEFAULT_STORAGE_ENGINE为什么?简而言之,因为InnoDB是MySQL(包括Percona Server和MariaDB)最好的存储引擎 – 它支持事务,高并发,有着非常好的性能表现(当配置正确时).这里有详细的版本介绍为什么
这个是InnoDB最重要变量.实际上,如果你的主要存储引擎是InnoDB,那么对于你,这个变量对于MySQL是最重要的.
基本上,innodb_buffer_pool_size指定了MySQL应该分配给InnoDB缓冲池多少内存,InnoDB缓冲池用来存储缓存的数据,二级索引,脏数据(已经被更改但没有刷新到硬盘的数据)以及各种内部结构如自适应哈希索引.
当然,如果你有大量的大事务更改,那么,更改比默认innodb日志缓冲大小更大的值会对你的性能有一定的提高,但是你使用的是autocommit,或者你的事务更改小于几k,那还是保持默认的值吧.
默认下,innodb_flush_log_at_trx_commit设置为1表示InnoDB在每次事务提交后立即刷新同步数据到硬盘.如果你使用autocommit,那么你的每一个INSERT, UPDATE或DELETE语句都是一个事务提交.
同步是一个昂贵的操作(特别是当你没有写回缓存时),因为它涉及对硬盘的实际同步物理写入.所以如果可能,并不建议使用默认值.
* 0表示刷新到硬盘,但不同步(提交事务时没有实际的IO操作)
已经有大量的文档写到sync_binlog,以及它和innodb_flush_log_at_trx_commit的关系,下面我们来简单的介绍下：
a) 如果你的服务器没有设置从服务器,而且你不做备份,那么设置sync_binlog=0将对性能有好处.
b) 如果你有从服务器并且做备份,但你不介意当主服务器崩溃时在二进制日志丢失一些事件,那么为了更好的性能还是设置为sync_binlog=0.
c) 如果你有从服务器并且备份,你非常在意从服务器的一致性,以及能及时恢复到一个时间点(通过使用最新的一致性备份和二进制日志将数据库恢复到特定时间点的能力),那么你应该设置innodb_flush_log_at_trx_commit=1,并且需要认真考虑使用sync_binlog=1.
将innodb_flush_method设置为O_DIRECT以避免双重缓冲.唯一一种情况你不应该使用O_DIRECT是当你操作系统不支持时.但如果你运行的是Linux,使用O_DIRECT来激活直接IO.
简单地说,设置为innodb_flush_method=O_DIRECT.
你设置后观察会觉得性能提高不大,但在大多数高负载情况下,它应该会有不错的表现.
对了,不要指望这个设置能减少你单个查询的响应时间.这个是在高并发负载的服务器上才看得出区别.比如多个线程同时做许多事情.
InnoDB有一种方法来控制并行执行的线程数 – 我们称为并发控制机制.大部分是由innodb_thread_concurrency值来控制的.如果设置为0,并发控制就关闭了,所以呢InnoDB会立即处理所有进来的请求(尽可能多的).
下面介绍如何更改这个变量,在mysql命令行提示符执行：
这一项不得不提及,因为仍然有很多人没有添加这一项.你应该添加skip_name_resolve来避免连接时DNS解析.
大多数情况下你更改这个会没有什么感觉,因为大多数情况下DNS服务器解析会非常快.不过当DNS服务器失败时,它会出现在你服务器上出现"unauthenticated connections" ,而就是为什么所有的请求都突然开始慢下来了.
所以不要等到这种事情发生才更改.现在添加这个变量并且避免基于主机名的授权.
①.0.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX
* innodb_io_capacity：用来当刷新脏数据时,控制MySQL每秒执行的写IO量.
* innodb_io_capacity_max: 在压力下,控制当刷新脏数据时MySQL每秒执行的写IO量
首先,这与读取无关 – SELECT查询执行的操作.对于读操作,MySQL会尽最大可能处理并返回结果.至于写操作,MySQL在后台会循环刷新,在每一个循环会检查有多少数据需要刷新,并且不会用超过innodb_io_capacity指定的数来做刷新操作.这也包括更改缓冲区合并（在它们刷新到磁盘之前,更改缓冲区是辅助脏页存储的关键）.
第二,我需要解释一下什么叫"在压力下",MySQL中称为"紧急情况",是当MySQL在后台刷新时,它需要刷新一些数据为了让新的写操作进来.然后,MySQL会用到innodb_io_capacity_max.
那么,应该设置innodb_io_capacity和innodb_io_capacity_max为什么呢?
①.1.INNODB_STATS_ON_METADATA
两件事：
第一,它实际上没有在关闭时复制缓冲池内容到文件,仅仅是复制表空间ID和页面ID – 足够的信息来定位硬盘上的页面了.然后它就能以大量的顺序读非常快速的加载那些页面,而不是需要成千上万的小随机读.
第二,启动时是在后台加载内容,因为MySQL不需要等到缓冲池内容加载完成再开始接受请求(所以看起来不会有什么影响).
如果你运行着一个大量SELECT查询的MySQL服务器(并且已经尽可能优化),那么自适应哈希索引将下你的下一个瓶颈.自适应哈希索引是InnoDB内部维护的动态索引,可以提高最常用的查询模式的性能.这个特性可以重启服务器关闭,不过默认下在mysql的所有版本开启.
这个技术非常复杂,在大多数情况下它会对大多数类型的查询直到加速的作用.不过,当你有太多的查询往数据库,在某一个点上它会花过多的时间等待AHI锁和闩锁.
如果人认为查询缓存效果很好,肯定应该使用它.好吧,有时候是有用的.不过这个只在你在低负载时有用,特别是在低负载下大多数是读取,小量写或者没有.
如果你的MySQL服务器高负载动作,建议设置query_cache_size=0和query_cache_type=OFF,并重启服务器生效.那样Mysql就会停止在所有的查询使用查询缓存互斥锁.
表缓存用来存放目前已打开表的列表,当每一个表打开或关闭互斥体就被锁定 – 即使这是一个隐式临时表.使用多个分区绝对减少了潜在的争用.
欢迎做Java的工程师朋友们私信我资料免费获取免费的Java架构学习资料（里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料）
其中覆盖了互联网的方方面面,期间碰到各种产品各种场景下的各种问题,很值得大家借鉴和学习,扩展自己的技术广度和知识面.
mysql 参数调优(2)之设置重做日志文件的大小 innodb_log_file_size
我们知道redo log包括 buffer和log file的部分,这里的innodb_log_file_size是配置log file的大小的.
由于事务日志相当于一个写缓冲,而小日志文件会很快的被写满,这时候就需要频繁地刷新到硬盘,速度就慢了.如果产生大量的写操作,MySQL可能就不能足够快地刷新数据,那么写性能将会降低.
大的日志文件,另一方面,在刷新操作发生之前给你足够的空间来使用.反过来允许InnoDB填充更多的页面.对于崩溃恢复 – 大的重做日志意味着在服务器启动前更多的数据需要读取,更多的更改需要重做,这就是为什么崩溃恢复慢了.
最后,让我们来谈谈如何找出重做日志的正确大小.
+--------------------------------------+
①. row in set (0.00 sec)
好了,下面是步骤：
①.、在my.cnf更改innodb_log_file_size
以上就是通能百科网小编为大家整理的mysql数据库怎么调优相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用，就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！
