mysql怎么设置优化 mysql如何优化sql

linux 下怎么优化mysql占用内存？

修改mysql配置文件，优化缓存大小和连接数连接方式，优化sql语句 ,记得mysql好像是有工具可以查看最占用资源的sql语句，找到他，优化他。

10年积累的成都做网站、网站制作经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站制作后付款的网站建设流程，更有兴化免费网站建设让你可以放心的选择与我们合作。

安装好mysql后，配制文件应该在/usr/local/mysql/share/mysql目录中，配制文件有几个，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境，当然需要有不同的配制文件了。

一般的情况下，my-medium.cnf这个配制文件就能满足我们的大多需要；一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了，使用mysqladmin variables extended-status _u root _p 可以看到目前的参数，有3个配置参数是最重要的，即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只对MyISAM表起作用，

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。一般我们设为16M,实际上稍微大一点的站点　这个数字是远远不够的，通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例 key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql，以下的状态值都是本人通过phpmyadmin获得的实例分析:

这个服务器已经运行了20天

key_buffer_size _ 128M

key_read_requests _ 650759289

key_reads - 79112

比例接近1:8000 健康状况非常好

mysql 性能优化参数配置

mysql -u root -p 回车输入密码进入mysql

show processlist;

查看连接数，可以发现有很多连接处于sleep状态，这些其实是暂时没有用的，所以可以kill掉

show variables like "max_connections";

查看最大连接数，应该是与上面查询到的连接数相同，才会出现too many connections的情况

set GLOBAL max_connections=1000;

修改最大连接数，但是这不是一劳永逸的方法，应该要让它自动杀死那些sleep的进程。

show global variables like 'wait_timeout';

这个数值指的是mysql在关闭一个非交互的连接之前要等待的秒数，默认是28800s

set global wait_timeout=300;

修改这个数值，这里可以随意，最好控制在几分钟内

set global interactive_timeout=500;

修改这个数值，表示mysql在关闭一个连接之前要等待的秒数，至此可以让mysql自动关闭那些没用的连接，但要注意的是，正在使用的连接到了时间也会被关闭，因此这个时间值要合适

SHOW VARIABLES LIKE '%table_open_cache%';

查看

show global status like 'Open%tables';

MySQL数据库优化（七）

为了能最小化磁盘I/O MyISAM 存储引擎采用了很多数据库系统使用的一种策略它采用一种机制将最经常访问的表保存在内存区块中

对索引区块来说它维护着一个叫索引缓存(索引缓冲)的结构体这个结构体中放著许多那些最常使用的索引区块的缓冲区块对数据区块来说 MySQL没有使用特定的缓存它依靠操作系统的本地文件系统缓存

本章首先描述了 MyISAM 索引缓存的基本操作然后讨论在MySQL 中所做的改进它提高了索引缓存性能同时能更好地控制缓存操作

线程之间不再是串行地访问索引缓存多个线程可以并行地访问索引缓存可以设置多个索引缓存同时也能指定数据表索引到特定的缓存中

索引缓存机制对 ISAM 表同样适用不过这种有效性正在减弱自从MySQL 开始 MyISAM 表类型引进之后 ISAM 就不再建议使用了 MySQL 更是延续了这个趋势 ISAM 类型默认被禁用了

可以通过系统变量 key_buffer_size 来控制索引缓存区块的大小如果这个值大小为那么就不使用缓存当这个值小得于不足以分配区块缓冲的最小数量( )时也不会使用缓存

当索引缓存无法操作时索引文件就只通过操作系统提供的本地文件系统缓冲来访问(换言之表索引区块采用的访问策略和数据区块的一致)

一个索引区块在 MyISAM 索引文件中是一个连续访问的单元通常这个索引区块的大小和B树索引节点大小一样(索引在磁盘中是以B树结构来表示的这个树的底部时叶子节点叶子节点之上则是非叶子节点)

在索引缓存结构中所有的区块大小都是一样的这个值可能等于大于或小于表的索引区块大小通常这两个值是不一样的

当必须访问来自任何表的索引区块时服务器首先检查在索引缓存中是否有可用的缓冲区块如果有服务器就访问缓存中的数据而非磁盘就是说它直接存取缓存而不是存取磁盘否则服务器选择一个(多个)包含其它不同表索引区块的缓存缓冲区块将它的内容替换成请求表的索引区块的拷贝一旦新的索引区块在缓存中了索引数据就可以存取了

当发生被选中要替换的区块内容修改了的情况时这个区块就被认为脏了那么在替换之前它的内容就必须先刷新到它指向的标索引

通常服务器遵循LRU(最近最少使用)策略当要选择替换的区块时它选择最近最少使用的索引区块为了想要让选择变得更容易索引缓存模块会维护一个包含所有使用区块特别的队列(LRU链) 当一个区块被访问了就把它放到队列的最后位置当区块要被替换时在队列开始位置的区块就是最近最少使用的它就是第一候选删除对象

共享访问索引缓存

在MySQL 以前访问索引缓存是串行的两个线程不能并行地访问索引缓存缓冲服务器处理一个访问索引区块的请求只能等它之前的请求处理完结果新的请求所需的索引区块就不在任何索引缓存环冲区块中因为其他线程把包含这个索引区块的缓冲给更新了

从MySQL 开始服务器支持共享方式访问索引缓存

没有正在被更新的缓冲可以被多个线程访问

缓冲正被更新时需要使用这个缓冲的线程只能等到更新完成之后

多个线程可以初始化需要替换缓存区块的请求只要它们不干扰别的线程(也就是它们请求不同的索引区块因此不同的缓存区块被替换)

共享方式访问索引缓存令服务器明显改善了吞吐量

多重索引缓存

共享访问索引缓存改善了性能却不能完全消除线程间的冲突它们仍然争抢控制管理存取索引缓存缓冲的结构为了更进一步减少索引缓存存取冲突 MySQL 提供了多重索引缓存特性这能将不同的表索引指定到不同的索引缓存

当有多个索引缓存服务器在处理指定的 MyISAM 表查询时必须知道该使用哪个默认地所有的 MyISAM 表索引都缓存在默认的索引缓存中想要指定到特定的缓存中可以使用 CACHE INDEX 语句

如下语句所示指定表的索 t t 和 t 引缓存到名为 hot_cache 的缓存中

注意如果服务器编译支持存 ISAM 储引擎了那么 ISAM 表也使用索引缓存机制不过 ISAM 表索引只能使用默认的索引缓存而不能自定义

CACHE INDEX 语句中用到的索引缓存是根据用 SET GLOBAL 语句的参数设定的值或者服务器启动参数指定的值创建的如下 mysql SET GLOBAL keycache key_buffer_size= * ;想要删除索引缓存只需设置它的大小为 mysql SET GLOBAL keycache key_buffer_size= ;索引缓存变量是一个结构体变量由名字和组件构成例如 keycache key_buffer_size keycache 就是缓存名 key_buffer_size 是缓存组件默认地表索引在服务器启动时指定到主(默认的)索引缓存中当一个索引缓存被删掉后指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去对一个繁忙的系统来说我们建议以下三条策略来使用索引缓存热缓存占用 %的总缓存空间用于繁重搜索但很少更新的表冷缓存占用 %的总缓存空间用于中等强度更新的表如临时表冷缓存占用 %的总缓存空间作为默认的缓存用于所有其他表使用三个缓存的一个原因是好处在于存取一个缓存结构时不会阻止对其他缓存的访问访问一个表索引的查询不会跟指定到其他缓存的查询竞争性能提高还表现在以下几点原因热缓存只用于检索记录因此它的内容总是不需要变化所以无论什么时候一个索引区块需要从磁盘中引入被选中要替换的缓存区块的内容总是要先被刷新索引被指向热缓存中后如果没有需要扫描全部索引的查询那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中在临时表里必须频繁执行一个更新操作是相当快的如果要被更新的节点已经在缓存中了它无需先从磁盘中读取出来当临时表的索引大小和冷缓存大小一样时那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的

中点插入策略

默认地 MySQL 的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块不过它也支持更完善的方法叫做中点插入策略

使用中点插入策略时 LRU链就被分割成两半一个热子链一个温子链两半分割的点不是固定的不过缓存管理系统会注意不让温子链部分太短总是至少包括全部缓存区块的 key_cache_division_limit 比率 key_cache_division_limit 是缓存结构体变量的组件部分因此它是每个缓存都可以设置这个参数值

当一个索引区块从表中读入缓存时它首先放在温子链的末尾当达到一定的点击率(访问这个区块)后它就提升到热子链中去目前要提升一个区块的点击率( )对每个区块来说都是一样的将来我们会让点击率依靠B树中对应的索引区块节点的级别包含非叶子节点的索引区块所要求的提升点击率就低一点包含叶子节点的B索引树的区块的值就高点

提升起来的区块首先放在热子链的末尾这个区块在热子链内一直循环如果这个区块在该子链开头位置停留时间足够长了它就会被降级回温子链这个时间是由索引缓存结构体变量的组件 key_cache_age_threshold 值来决定的

这个阀值是这么描述的一个索引缓存包含了 N 个区块热子链开头的区块在低于 N*key_cache_age_threshold/ 次访问后就被移动到温子链的开头位置它又首先成为被删除的候选对象因为要被替换的区块还是从温子链的开头位置开始的

中点插入策略就能在缓存中总能保持更有价值的区块如果更喜欢采用LRU策略只需让 key_cache_division_limit 的值低于默认值

中点插入策略能帮助改善在执行需要有效扫描索引它会将所有对应到B树中高级别的有价值的节点推出的查询时的性能为了避免这样就必须设定 key_cache_division_limit 远远低于以采用中点插入策略则在扫描索引操作时那些有价值的频繁点击的节点就会保留在热子链中了

索引预载入

如果索引缓存中有足够的区块用来保存全部索引或者至少足够保存全部非叶子节点那么在使用前就载入索引缓存就很有意义了将索引区块以十分有效的方法预载入索引缓存缓冲从磁盘中顺序地读取索引区块

没有预载入查询所需的索引区块仍然需要被放到缓存中去虽然索引区块要保留在缓存中因为有足够的缓冲它们可以从磁盘中随机读取到而非顺序地

想要预载入缓存可以使用 LOAD INDEX INTO CACHE 语句如下语句预载入了表 t 和 t 的索引节点(区块)

增加修饰语 IGNORE LEAVES 就只预载入非叶子节点的索引区块因此上述语句加载了 t 的全部索引区块但是只加载 t 的非叶子节点区块

如果使用 CACHE INDEX 语句将索引指向一个索引缓存将索引区块预先放到那个缓存中去否则索引区块只会加载到默认的缓存中去

索引缓存大小

MySQL 引进了对每个索引缓存的新变量 key_cache_block_size 这个变量可以指定每个索引缓存的区块大小用它就可以来调整索引文件I/O操作的性能

当读缓冲的大小和本地操作系统的I/O缓冲大小一样时就达到了I/O操作的最高性能了但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能读比较大的叶子节点时服务器会读进来很多不必要的数据这大大阻碍了读其他叶子节点

目前还不能控制数据表的索引区块大小这个大小在服务器创建索引文件 ` MYI 时已经设定好了它根据数据表的索引大小的定义而定在很多时候它设置成和I/O缓冲大小一样在将来可以改变它的值并且会全面采用变量 key_cache_block_size

重建索引缓存

索引缓存可以通过修改其参数值在任何时候重建它例如

mysql SET GLOBAL cold_cache key_buffer_size= * * ;

如果设定索引缓存的结构体变量组件变量 key_buffer_size 或 key_cache_block_size 任何一个的值和它当前的值不一样服务器就会清空原来的缓存在新的变量值基础上重建缓存如果缓存中有任何的脏索引块服务器会先把它们保存起来然后才重建缓存重新设定其他的索引缓存变量并不会重建缓存

lishixinzhi/Article/program/Oracle/201311/16615

网站题目：mysql怎么设置优化 mysql如何优化sql
转载源于：http://hbruida.cn/article/doedeic.html

mysql怎么设置优化 mysql如何优化sql

linux 下怎么优化mysql占用内存？

mysql 性能优化参数配置

MySQL数据库优化（七）

其他资讯