怎么给mysql做优化 mysql优化

mysql优化

1，sql的编译顺序

创新互联建站专注于福海企业网站建设,响应式网站建设,电子商务商城网站建设。福海网站建设公司,为福海等地区提供建站服务。全流程定制设计，专业设计，全程项目跟踪，创新互联建站专业和态度为您提供的服务

sql 编译顺序 from… on… join… where… order by… group by… having… select…

2，查看sql语句性能:

explain 查询sql语句

3，优化

(1). 最佳作前缀，使用索引顺序(按编译顺序)与定义索引时顺序一致，若该字段有跳过、反序，该字段及后面字段索引失效

(2). where条件中一切不是=的操作大概率会使索引失效，包括in、!=、、is null、计算、函数等等

(3). 查询字段与条件字段不一致时使用子查询，避免临时表出现

(4). 若用了复合索引，尽量使用全部索引字段

(5). 能不查询多字段时，尽量使用索引覆盖

(6). 使用like模糊查询时，按关键字左匹配，即‘x%’，若使用’%x%’，索引失效

(7). or会使全部索引失效

(8). 尽量不要导致类型转换，否则索引失效

(9). 使用order by时，根据表中数据量调整单路还是双路查询，也可以调整buffer区大小：如set_max_length_for_sort_data = 1024 (单位byte)

(10). 避免使用select *…

(11). 分页偏移量大时，尽量使用子查询 select * from tab where id=(select id from tab limit 100000,1) limit 100;

mysql 如何优化无索引查询

mysql优化无索引查询：SQL CREATE TABLE test_tab (id INT,name VARCHAR(10),age INT,val VARCHAR(10)。

1、对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。

2、应尽量避免在 where子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。

3、应尽量避免在 where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。

运行mysql安装文件：

按 Next，然后选择安装方式，有 "Typical（默认）"、"Complete（完全）"、"Custom（用户自定义）"，选择第二个选项 "Custom"，下一步, MySQL Server (mysql服务器), Developer Components (开发者部分), Debug Symbols (调试符号), Server data files (服务器数据文件) 默认。

改变安装路径；原路径是"C:\Program Files\MySQL\MySQL Server 5.5\"，也可以修改为："E:\Program Files\MySQL Server 5.5\"。

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

北大青鸟java培训：mysql数据库的优化方法？

我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，河北IT培训就一起来了解一下mysql服务器数据库的优化方法。

为什么要了解索引真实案例案例一：大学有段时间学习爬虫，爬取了知乎300w用户答题数据，存储到mysql数据中。

那时不了解索引，一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右，完全满足不了正常的使用。

案例二：近线上应用的数据库频频出现多条慢sql风险提示，而工作以来，对数据库优化方面所知甚少。

例如一个用户数据页面需要执行很多次数据库查询，性能很慢，通过增加超时时间勉强可以访问，但是性能上需要优化。

索引的优点合适的索引，可以大大减小mysql服务器扫描的数据量，避免内存排序和临时表，提高应用程序的查询性能。

索引的类型mysql数据中有多种索引类型，primarykey，unique，normal，但底层存储的数据结构都是BTREE;有些存储引擎还提供hash索引，全文索引。

BTREE是常见的优化要面对的索引结构，都是基于BTREE的讨论。

B-TREE查询数据简单暴力的方式是遍历所有记录;如果数据不重复，就可以通过组织成一颗排序二叉树，通过二分查找算法来查询，大大提高查询性能。

而BTREE是一种更强大的排序树，支持多个分支，高度更低，数据的插入、删除、更新更快。

现代数据库的索引文件和文件系统的文件块都被组织成BTREE。

btree的每个节点都包含有key，data和只想子节点指针。

btree有度的概念d=1。

假设btree的度为d，则每个内部节点可以有n=[d+1，2d+1)个key，n+1个子节点指针。

树的大高度为h=Logb[(N+1)/2]。

索引和文件系统中，B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小)，且树的度非常大。

这样磁盘I/O的次数，就等于树的高度h。

假设b=100，一百万个节点的树，h将只有3层。

即，只有3次磁盘I/O就可以查找完毕，性能非常高。

索引查询建立索引后，合适的查询语句才能大发挥索引的优势。

另外，由于查询优化器可以解析客户端的sql语句，会调整sql的查询语句的条件顺序去匹配合适的索引。

超详细MySQL数据库优化

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

1. 优化一览图

2. 优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

2.1 软优化

2.1.1 查询语句优化

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.

2.例:

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者MySQL数据库索引一文,介绍比较详细,此处记录使用索引的三大注意事项:

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段

类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1. 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

2. 检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:

3. 优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

1.配置多核心和频率高的cpu,多核心可以执行多个线程.

2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

2.2.3 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

怎么进行mysql数据库优化？

有八个方面可以对mysql进行优化：

1、选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2. 使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

3、使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。

4、事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

名称栏目：怎么给mysql做优化 mysql优化
分享URL：http://myzitong.com/article/hpcphc.html