mysql数据库优化技术
此文章主要向大家介绍的是MySQL数据库优化 其中还包括MySQL数据库的性能优化 常用的SQL语句的优化以及MySQL数据库对INSERT语句进行优化的实际操作方案的描述 望你会有所收获
MySQL InnoDB 的性能问题讨论
MySQL性能优化
InnoDB delete from xxx速度暴慢原因
推荐圈子: mysql研究
更多相关推荐 定期分析表和检查表
分析表的语法如下
引用
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb _name[ tbl_name]
以上语句用于分析和存储表的关键字分布 分析的结果将可以使得系统得到准确的统计信息 使得SQL能够生成正确的执行计划 如果用户感觉实际执行计划并不是预期的执行计划 执行一次分析表可能会解决问题 在分析期间 使用一个读取锁定对表进行锁定 这对于MyISAM DBD和InnoDB表有作用
例如分析一个数据表
引用
*** yze table table_name
检查表的语法如下
引用
CHECK TABLE tb _name[ tbl_name] [option] option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
检查表的作用是检查一个或多个表是否有错误 CHECK TABLE 对MyISAM 和 InnoDB表有作用 对于MyISAM表 关键字统计数据被更新
CHECK TABLE 也可以检查视图是否有错误 比如在视图定义中被引用的表不存在
定期优化表
MySQL数据库优化表的语法如下
引用
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb _name [ tbl_name]
如果删除了表的一大部分 或者如果已经对含有可变长度行的表(含有 VARCHAR BLOB或TEXT列的败宏表)进行更多更银枯亩改 则应使用OPTIMIZE TABLE命令来进行表优化 这个命令可以将表中的空间碎片进行合并 并且可以消除由于删除或者更新造成的空间浪费 但OPTIMIZE TABLE 命令只对MyISAM BDB 和InnoDB表起作用
例如 optimize table table_name
锋森注意 *** yze check optimize执行期间将对表进行锁定 因此一定注意要在数据库不繁忙的时候执行相关的操作
常用的SQL优化
我们在开发的时候常常用到的SQL语句 无非是INSERT GROUPBY等等 对于这些SQL语句 我们怎么进行优化？
大批量插入数据
当用load命令导入数据的时候 适当的设置可以提高导入的速度
对于MyISAM存储引擎的表 可以通过如下方式快速的导入大量的数据
引用
ALTER TABLE tb _name DISABLE KEYS;
loading the data
ALTER TABLE tb _name ENABLE KEYS;
DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭MyISAM表非唯一索引的更新 在导入大量的数据到一个非空的MyISAM表时 通过设置这两个命令 可以提高导入的效率
对于导入大量的数据到一个空的MyISAM表时 默认就是先导入数据然后才创建索引的 索引不用进行设置
引用
load data infile /home/mysql/text_txt into table text
对于InnoDB类型的表 这种方式不能提高导入数据的效率 但也有几种针对InnoDB类型的表进行MySQL数据库优化的方式
因为InnoDB类型的表式按照主键的顺序保存的 所以将导入的数据按照主键的顺序排序 可以有效提高导入数据的效率
在导入数据前执行 SET UNIQUE_CHECKS= 关闭唯一性校验 在导入结束后执行SET UNIQUE_CHECKS= 恢复唯一性校验 可以提高导入的效率
如果应用使用自动提交的方式 建议在导入前执行SET AUTOMIT= 关闭自动提交 导入结束后执行SET AUTOMIT= 打开自动提交 也可以提高导入效率
MySQL数据库优化INSERT语句
当进行数据INSERT的时候 可以考虑采用以下几种方式进行优化
如果同时从一个客户插入很多行 尽量使用多个值表的INSERT语句 这种方式将大大缩短客户端与数据库的链接 关闭等消耗 使得效率比分开执行的单个INSERT语句快
例如
insert into test values( )
insert into test values( )
insert into test values( )
将上面三句改为:insert into test values( ) ( ) ( )
如果从不同客户插入很多行 能通过使用INSERT DELAYED 语句得到更高的速度
DELAYED 的含义是让INSERT 语句马上执行 其实数据都被放在内存的队列中 并没有真正写入磁盘 这比每条语句分别插入要快得多 LOW_PRIORITY刚好相反 在所有其他用户对表的读写完后才进行插入
将索引文件和数据文件分在不同的磁盘上存放
如果进行批量插入 可以增加bulk_insert_buffer_size变量值的方法来提高速度 但是 这只能对于MyISAM表使用
当从一个文本文件中装载一个表时 使用LOAD DATA INFILE 这通常比使用很多insert语句快 倍左右
lishixin/Article/program/MySQL/201311/29324
‘贰’ 怎么进行mysql数据库优化(mysql数据库优化的几种方法)
主要从以下角度思考优化方向：1，Mysql配置优化主要对查询缓存，mysql数据库连接时缓卖长，开启慢查询日志(开启后还要分析sql)等方面进行优化2
Myslq语句优化3
Mysql索引优化主要是需要注意索引数量和索引失效情况，重复索引橡哪桐4
Mysql引擎优化innodb引擎注重于事务，能保证数据一致性myisam引擎梁坦可以进行全文检索，但不是事务安全当初在黑马程序员学过，还用实例进行优化学习
‘叁’ 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 的缓存中
mysql>CACHEINDEXt t t INhot_cache; + + + + + |Table|Op|Msg_type|Msg_text| + + + + + |test t |assign_to_keycache|status|OK| |test t |assign_to_keycache|status|OK| |test t |assign_to_keycache|status|OK| + + + + +
注意 如果服务器编译支持存 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 的索引节点(区块)
mysql>LOADINDEXINTOCACHEt t IGNORELEAVES; + + + + + |Table|Op|Msg_type|Msg_text| + + + + + |test t |preload_keys|status|OK| |test t |preload_keys|status|OK| + + + + +
增加修饰语 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>SETGLOBALcold_cache key_buffer_size= * * ;
如果设定索引缓存的结构体变量组件变量 key_buffer_size 或 key_cache_block_size 任何一个的值和它当前的值不一样 服务器就会清空原来的缓存 在新的变量值基础上重建缓存 如果缓存中有任何的 脏 索引块 服务器会先把它们保存起来然后才重建缓存 重新设定其他的索引缓存变量并不会重建缓存
lishixin/Article/program/Oracle/201311/16615
‘肆’ mysql数据库如何优化，优化了哪些功能
mysql的优化大的有两方面：
1、配置优化
配置的优化其实包含两个方面的：操作系统内核的优化和mysql配置文件的优化
1)系统内核的优化对专用的mysql服务器来说，无非是内存实用、连接数、超时处理、TCP处理等方面的优化，根据自己的硬件配置来进行优化，这里不多讲；
2)mysql配置的优化，一般来说包含：IO处理的常用参数、最大连接数设置、缓存使用参数的设置、慢日志的参数的设置、innodb相关参数的设置等，如果有主从关系在设置主从同步的相关参数即可，网上的相关配置文件很多，大同小异，常用的设置大多修改这些差不多就够用了。
2、sql语句的优化
1、 尽量稍作计算
Mysql的作用是用来存取数据的，不是做计算的，做计算的话可以用其他方法去实现，mysql做计算是很耗资源的。
2.尽量少 join
MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。
3.尽量少排序
排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。
对于MySQL来说，减少排序有多种办法，比如：
通过利用索引来排序的方式进行优化
减少参与排序的记录条数
非必要不对数据进行排序
‘伍’ MySQL数据库性能优化有哪些技巧
1.存储引擎的选择如果数据表需要事务处理，应该考虑使用InnoDB，因为它完全符合ACID特性。如果不需要事务处理，使用默认存储引擎MyISAM是比较明智的。并且不要尝试同时使用这两个存储引擎。思考一下：在一个事务处理中，一些数据表使用InnoDB，而其余的使用MyISAM.结果呢？整个subject将被取消，只有那些在事务处理中的被带回到原始状态，其余的被提交的数据转存，这将导致整个数据库的冲突。然而存在一个简单的方法可以同时利用两个存储引擎的优势。目前大多数MySQL套件中包括InnoDB、编译器和链表，但如果你选择MyISAM，你仍然可以单独下载InnoDB，并把它作为一个插件。很简单的方法，不是吗？
2.计数问题如果数据表采用的存储引擎支持事务处理（如InnoDB），你就不应使用COUNT（*）计算数据表中的行数。这是因为在产品类数据库使用COUNT（*），最多返回一个近似值，因为在某个特定时间，总有一些事务处理正在运行。如果使用COUNT（*）显然会产生bug，出现这种错误结果。
3.反复测试查询查询最棘手的问题并不是无论怎样小心总会出现错误，并导致bug出现。恰恰相反，问题是在大多数情况下bug出现时，应用程序或数据库已经上线。的确不存在针对该问题切实可行的解决方法，除非将测试样本在应用程序或数据库上运行。任何数据库查询只有经过上千个记录的大量样本测试，才能被认可。
4.避免全表扫描通常情况下，如果MySQL（或者其他关系数据库模型）需要在数据表中搜索或扫描任意特定记录时，就会用到全表扫描。此外，通常最简单的方法是使用索引表，以解决全表扫描引起的低效能问题。然而，正如我们在随后的问题中看到的，这存在错误部分。
5.使用“EXPLAIN”进行查询当需要调试时，EXPLAIN是一个很好的命令，下面将对EXPLAIN进行深入探讨。