mysql数据库优化的步骤是怎样的

共计 8340 个字符，预计需要花费 21 分钟才能阅读完成。

这期内容当中丸趣 TV 小编将会给大家带来有关 mysql 数据库优化的步骤是怎样的，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

mysql 数据库优化的步骤

1: 磁盘寻道能力, 以高速硬盘(7200 转 / 秒), 理论上每秒寻道 7200 次. 这是没有办法改变的, 优化的方法是 —- 用多个硬盘, 或者把数据分散存储.

2: 硬盘的读写速度, 这个速度非常的快, 这个更容易解决 – 可以从多个硬盘上并行读写.

3:cpu.cpu 处理内存中的数据, 当有相对内存较小的表时, 这是最常见的限制因素.

4: 内存的限制. 当 cpu 需要超出适合 cpu 缓存的数据时, 缓存的带宽就成了内存的一个瓶颈 — 不过现在内存大的惊人, 一般不会出现这个问题.

第二步: (本人使用的是学校网站的 linux 平台(Linux ADVX.Mandrakesoft.com 2.4.3-19mdk))

1: 调节服务器参数

用 shell mysqld-help 这个命令声厂一张所有 mysql 选项和可配置变量的表. 输出以下信息:

possible variables for option–set-variable(-o) are:

back_log current value:5 // 要求 mysql 能有的连接数量.back_log 指出在 mysql 暂停接受连接的时间内有多少个连接请求可以被存在堆栈中

connect_timeout current value:5 //mysql 服务器在用 bad handshake(不好翻译)应答前等待一个连接的时间

delayed_insert_timeout current value:200 // 一个 insert delayed 在终止前等待 insert 的时间

delayed_insert_limit current value:50 //insert delayed 处理器将检查是否有任何 select 语句未执行, 如果有, 继续前执行这些语句

delayed_queue_size current value:1000 // 为 insert delayed 分配多大的队

flush_time current value:0 // 如果被设置为非 0, 那么每个 flush_time 时间, 所有表都被关闭

interactive_timeout current value:28800 // 服务器在关上它之前在洋交互连接上等待的时间

join_buffer_size current value:131072 // 用与全部连接的缓冲区大小

key_buffer_size current value:1048540 // 用语索引块的缓冲区的大小, 增加它可以更好的处理索引

lower_case_table_names current value:0 //

long_query_time current value:10 // 如果一个查询所用时间大于此时间,slow_queried 计数将增加

max_allowed_packet current value:1048576 // 一个包的大小

max_connections current value:300 // 允许同时连接的数量

max_connect_errors current value:10 // 如果有多于该数量的中断连接, 将阻止进一步的连接, 可以用 flush hosts 来解决

max_delayed_threads current value:15 // 可以启动的处理 insert delayed 的数量

max_heap_table_size current value:16777216 //

max_join_size current value:4294967295 // 允许读取的连接的数量

max_sort_length current value:1024 // 在排序 blob 或者 text 时使用的字节数量

max_tmp_tables current value:32 // 一个连接同时打开的临时表的数量

max_write_lock_count current value:4294967295 // 指定一个值 (通常很小) 来启动 mysqld, 使得在一定数量的 write 锁定之后出现 read 锁定

net_buffer_length current value:16384 // 通信缓冲区的大小 – 在查询时被重置为该大小

query_buffer_size current value:0 // 查询时缓冲区大小

record_buffer current value:131072 // 每个顺序扫描的连接为其扫描的每张表分配的缓冲区的大小

sort_buffer current value:2097116 // 每个进行排序的连接分配的缓冲区的大小

table_cache current value:64 // 为所有连接打开的表的数量

thread_concurrency current value:10 //

tmp_table_size current value:1048576 // 临时表的大小

thread_stack current value:131072 // 每个线程的大小

wait_timeout current value:28800 // 服务器在关闭它 3 之前的一个连接上等待的时间

根据自己的需要配置以上信息会对你帮助.

第三:

1: 如果你在一个数据库中创建大量的表, 那么执行打开, 关闭，创建 (表) 的操作就会很慢. 2:mysql 使用内存

a: 关键字缓存区 (key_buffer_size) 由所有线程共享

b: 每个连接使用一些特定的线程空间. 一个栈 (默认为 64k, 变量 thread_stack), 一个连接缓冲区(变量 net_buffer_length) 和一个结果缓冲区(net_buffer_length). 特定情况下, 连接缓冲区和结果缓冲区被动态扩大到 max_allowed_packet.

c: 所有线程共享一个基存储器

d: 没有内存影射

e: 每个做顺序扫描的请求分配一个读缓冲区(record_buffer)

f: 所有联结均有一遍完成并且大多数联结甚至可以不用一个临时表完成. 最临时的表是基于内存的 (heap) 表

g: 排序请求分配一个排序缓冲区和 2 个临时表

h: 所有语法分析和计算都在一个本地存储器完成

i: 每个索引文件只被打开一次, 并且数据文件为每个并发运行的线程打开一次

j: 对每个 blob 列的表，一个缓冲区动态的被扩大以便读入 blob 值

k: 所有正在使用的表的表处理器被保存在一个缓冲器中并且作为一个 fifo 管理.

l: 一个 mysqladmin flush-tables 命令关闭所有不在使用的表并且在当前执行的线程结束时标记所有在使用的表准备关闭

3:mysql 锁定表

mysql 中所有锁定不会成为死锁. wirte 锁定: mysql 的锁定原理:a: 如果表没有锁定, 那么锁定;b 否则, 把锁定请求放入写锁定队列中

read 锁定: mysql 的锁定原理:a: 如果表没有锁定, 那么锁定;b 否则, 把锁定请求放入读锁定队列中

有时候会在一个表中进行很多的 select,insert 操作, 可以在一个临时表中插入行并且偶尔用临时表的记录更新真正的表

a: 用 low_priority 属性给一个特定的 insert,update 或者 delete 较低的优先级

b:max_write_lock_count 指定一个值 (通常很小) 来启动 mysqld, 使得在一定数量的 write 锁定之后出现 read 锁定

c: 通过使用 set sql_low_priority_updates= 1 可以从一个特定的线程指定所有的更改应该由较低的优先级完成

d: 用 high_priority 指定一个 select

e: 如果使用 insert….select…. 出现问题, 使用 myisam 表 —— 因为它支持因为它支持并发的 select 和 insert

4: 最基本的优化是使数据在硬盘上占据的空间最小. 如果索引做在最小的列上, 那么索引也最小. 实现方法:

a: 使用尽可能小的数据类型

b: 如果可能，声明表列为 NOT NULL.

c: 如果有可能使用变成的数据类型, 如 varchar(但是速度会受一定的影响)

d: 每个表应该有尽可能短的主索引 e: 创建确实需要的索引

f: 如果一个索引在头几个字符上有唯一的前缀, 那么仅仅索引这个前缀 —-mysql 支持在一个字符列的一部分上的索引

g: 如果一个表经常被扫描, 那么试图拆分它为更多的表

第四步

1: 索引的使用, 索引的重要性就不说了, 功能也不说了, 只说怎么做. 首先要明确所有的 mysql 索引 (primary,unique,index) 在 b 树中有存储. 索引主要用语:

a: 快速找到 where 指定条件的记录 b: 执行联结时, 从其他表检索行 c: 对特定的索引列找出 max()和 min()值

d：如果排序或者分组在一个可用键的最前面加前缀，排序或分组一个表

e：一个查询可能被用来优化检索值，而不用访问数据文件．如果某些表的列是数字型并且正好是某个列的前缀，为了更快，值可以从索引树中取出

2：存储或者更新数据的查询速度 grant 的执行会稍稍的减低效率．

mysql 的函数应该被高度的优化．可以用 benchmark（loop_count,expression)来找出是否查询有问题

select 的查询速度：如果想要让一个 select．．．where．．．更快，我能想到的只有建立索引．可以在一个表上运行 myisamchk--analyze 来更好的优化查询．可以用 myisamchk--sort-index--sort-records=1来设置用一个索引排序一个索引和数据．

3：mysql 优化 where 子句

3.1：删除不必要的括号：

（（a AND b) AND c OR (((a AND b) AND (a AND d)))) (a AND b AND c) OR (a AND b AND c AND d)

3.2: 使用常数

（ab 5 AND b=c AND a=5

3.3: 删除常数条件

（b =5 AND b=5) OR (b=6 AND 5=5) OR (b=100 AND 2=3) b=5 OR b=6

3.4: 索引使用的常数表达式仅计算一次

3.5：在一个表中，没有一个 where 的 count(*)直接从表中检索信息

3.6: 所有常数的表在查询中在任何其他表之前读出

3.7: 对外联结表最好联结组合是尝试了所有可能性找到的

3.8：如果有一个 order by 字句和一个不同的 group by 子句或者 order by 或者 group by 包含不是来自联结的第一个表的列，那么创建一个临时表

3.9: 如果使用了 sql_small_result，那么 msyql 使用在内存中的一个表

3.10: 每个表的索引给查询并且使用跨越少于30%的行的索引．

3.11 在每个记录输出前，跳过不匹配 having 子句的行

4：优化 left join

在 mysql 中 a left join b 按以下方式实现

a：表 b 依赖于表 a

b：表 a 依赖于所有用在 left join 条件的表（除了 b）

c：所有 left join 条件被移到 where 子句中

d：进行所有的联结优化，除了一个表总是在所有他依赖的表后读取．如果有一个循环依赖，那么将发生错误

e：进行所有的标准的 where 优化 f：如果在 a 中有一行匹配 where 子句，但是在 b 中没有任何匹配 left join 条件，那么，在 b 中生成的所有设置为NULL的一行

g：如果使用 left join 来找出某些表中不存在的行并且在 where 部分有 column_name IS NULL 测试(column_name 为 NOT NULL 列)．那么，mysql 在它已经找到了匹配 left join 条件的一行后，将停止在更多的行后寻找

5：优化 limit

a：如果用 limit 只选择一行，当 mysql 需要扫描整个表时，它的作用相当于索引

b：如果使用 limit#与 order by，mysql 如果找到了第#行，将结束排序，而不会排序正个表

c：当结合 limit#和 distinct 时，mysql 如果找到了第#行，将停止

d：只要 mysql 已经发送了第一个#行到客户，mysql 将放弃查询

e：limit 0 一直会很快的返回一个空集合．

f：临时表的大小使用 limit#计算需要多少空间来解决查询

6：优化 insert

插入一条记录的是由以下构成：

a: 连接（3）

b: 发送查询给服务器（2）

c: 分析查询（2）

d: 插入记录（1* 记录大小）

e：插入索引（1* 索引）

f：关闭（1）

以上数字可以看成和总时间成比例

改善插入速度的一些方法：

6.1：如果同时从一个连接插入许多行，使用多个值的 insert，这比用多个语句要快

6.2：如果从不同连接插入很多行，使用 insert delayed 语句速度更快

6.3: 用 myisam，如果在表中没有删除的行，能在 select：s 正在运行的同时插入行

6.4: 当从一个文本文件装载一个表时，用 load data infile．这个通常比 insert 快 20 倍

6.5: 可以锁定表然后插入--主要的速度差别是在所有 insert 语句完成后，索引缓冲区仅被存入到硬盘一次．一般与有不同的 insert 语句那样多次存入要快．如果能用一个单个语句插入所有的行，锁定就不需要．锁定也降低连接的整体时间．但是对某些线程最大等待时间将上升．例如：

thread 1 does 1000 inserts

thread 2,3 and 4 does 1 insert

thread 5 does 1000 inserts

如果不使用锁定，2，3，4将在1和5之前完成．如果使用锁定，2，3，4，将可能在1和5之后完成．但是整体时间应该快40%．因为 insert，update，delete 操作在 mysql 中是很快的，通过为多于大约5次连续不断的插入或更新一行的东西加锁，将获得更好的整体性能．如果做很多一行的插入，可以做一个 lock tables，偶尔随后做一个 unlock tables（大约每1000行）以允许另外的线程存取表．这仍然将导致获得好的性能．load data infile 对装载数据仍然是很快的．

为了对 load data infile 和 insert 得到一些更快的速度，扩大关键字缓冲区．

7优化 update 的速度

它的速度依赖于被更新数据的大小和被更新索引的数量

使 update 更快的另一个方法是推迟修改，然后一行一行的做很多修改．如果锁定表，做一行一行的很多修改比一次做一个快

8优化 delete 速度

删除一个记录的时间与索引数量成正比．为了更快的删除记录，可以增加索引缓存的大小 从一个表删除所有行比删除这个表的大部分要快的多

第五步

1：选择一种表类型 1.1 静态 myisam

这种格式是最简单且最安全的格式，它是磁盘格式中最快的．速度来自于数据能在磁盘上被找到的难易程度．当锁定有一个索引和静态格式的东西是，它很简单，只是行长度乘以数量．而且在扫描一张表时，每次用磁盘读取来读入常数个记录是很容易的．安全性来源于如果当写入一个静态 myisam 文件时导致计算机 down 掉，myisamchk 很容易指出每行在哪里开始和结束，因此，它通常能收回所有记录，除了部分被写入的记录．在 mysql 中所有索引总能被重建

1.2 动态 myisam

这种格式每一行必须有一个头说明它有多长．当一个记录在更改期间变长时，它可以在多于一个位置上结束．能使用 optimize tablename 或 myisamchk 整理一张表．如果在同一个表中有像某些 varchar 或者 blob 列那样存取/改变的静态数据，将动态列移入另外一个表以避免碎片．

1.2.1 压缩 myisam，用可选的 myisampack 工具生成

1.2.2 内存

这种格式对小型/中型表很有用．对拷贝/创建一个常用的查找表到洋 heap 表有可能加快多个表联结，用同样数据可能要快好几倍时间．

select tablename.a,tablename2.a from tablename,tablanem2,tablename3 where

tablaneme.a=tablename2.a and tablename2.a=tablename3.a and tablename2.c!=0;

为了加速它，可以用 tablename2 和 tablename3 的联结创建一个临时表，因为用相同列（tablename1.a）查找．

CREATE TEMPORARY TABLE test TYPE=HEAP

SELECT

tablename2.a as a2,tablename3.a as a3

FROM

tablenam2,tablename3

WHERE

tablename2.a=tablename3.a and c=0;

SELECT tablename.a,test.a3 from tablename,test where tablename.a=test.a1;

SELECT tablename.a,test,a3,from tablename,test where tablename.a=test.a1 and ….;

1.3 静态表的特点

1.3.1 默认格式．用在表不包含 varchar，blob，text 列的时候

1.3.2 所有的 char，numeric 和 decimal 列填充到列宽度

1.3.3 非常快

1.3.4 容易缓冲

1.3.5 容易在 down 后重建，因为记录位于固定的位置

1.3.6 不必被重新组织（用 myisamchk），除非是一个巨量的记录被删除并且优化存储大小

1.3.7 通常比动态表需要更多的存储空间

1.4 动态表的特点

1.4.1 如果表包含任何 varchar，blob，text 列，使用该格式

1.4.2 所有字符串列是动态的

1.4.3 每个记录前置一个位．

1.4.4 通常比定长表需要更多的磁盘空间

1.4.5 每个记录仅仅使用所需要的空间，如果一个记录变的很大，它按需要被分成很多段，这导致了记录碎片

1.4.6 如果用超过行长度的信息更新行，行被分段．

1.4.7 在系统 down 掉以后不好重建表，因为一个记录可以是多段

1.4.8 对动态尺寸记录的期望行长度是3+（number of columns+7）/8+(number of char columns)+packed size of numeric columns+length of strings +(number of NULL columns+7)/8

对每个连接有6个字节的惩罚．无论何时更改引起记录的变大，都有一个动态记录被连接．每个新连接至少有20个字节，因此下一个变大将可能在同一个连接中．如果不是，将有另外一个连接．可以用 myisamchk -恶毒检查有多少连接．所有连接可以用 myisamchk - r 删除．

1.5 压缩表的特点

1.5.1 一张用 myisampack 实用程序制作的只读表．

1.5.2 解压缩代码存在于所有 mysql 分发中，以便使没有 myisampack 的连接也能读取用 myisampack 压缩的表

1.5.3 占据很小的磁盘空间

1.5.4 每个记录被单独压缩．一个记录的头是一个定长的（1～～3个字节）这取决于表的最大记录．每列以不同的方式被压缩．一些常用的压缩类型是：

a: 通常对每列有一张不同的哈夫曼表 b: 后缀空白压缩 c: 前缀空白压缩 d: 用值0的数字使用1位存储

e: 如果整数列的值有一个小范围，列使用最小的可能类型来存储．例如：如果所有的值在0到255之间，一个 bigint 可以作为一个 tinyint 存储

g: 如果列仅有可能值的一个小集合，列类型被转换到 enum h: 列可以使用上面的压缩方法的组合

1.5.5 能处理定长或动态长度的记录，去不能处理 blob 或者 text 列 1.5.6 能用 myisamchk 解压缩

mysql 能支持不同的索引类型，但一般的类型是 isam，这是一个B树索引并且能粗略的为索引文件计算大小为(key_length+4)*0.67，在所有的键上的总和．

字符串索引是空白压缩的。如果第一个索引是一个字符串，它可将压缩前缀如果字符串列有很多尾部空白或是一个总部能甬道全长的 varchar 列，空白压缩使索引文件更小．如果很多字符串有相同的前缀．

1.6 内存表的特点

mysql 内部的 heap 表使用每偶溢出去的100%动态哈希并且没有与删除有关的问题．只能通过使用在堆表中的一个索引来用等式存取东西（通常用＇=＇操作符）

堆表的缺点是：

1.6.1 想要同时使用的所有堆表需要足够的额外内存

1.6.2 不能在索引的一个部分搜索

1.6.3 不能按顺序搜索下一个条目（即，使用这个索引做一个 order by）

1.6.4mysql 不能算出在2个值之间大概有多少行．这被优化器使用是用来决定使用哪个索引的，但是在另一个方面甚至不需要磁盘寻道

上述就是丸趣 TV 小编为大家分享的 mysql 数据库优化的步骤是怎样的了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。