MySQL 数据库优化的具体方法说明
程序员文章站
2023-12-09 14:05:45
以下的文章主要描述的是mysql数据库简单实用优化的具体方法的实现，中包括如何定期的表进行分析与检查， 以及如何正确对表进行定期的优化，以下就是具体方案的描述，希望在你今后...
以下的文章主要描述的是mysql数据库简单实用优化的具体方法的实现，中包括如何定期的表进行分析与检查， 以及如何正确对表进行定期的优化，以下就是具体方案的描述，希望在你今后的学习中会有所帮助。
1、定期分析表和检查表
分析表的语法如下：
以上语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得sql能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。这对于myisam，dbd和innodb表有作用。
例如分析一个数据表
检查表的语法如下：
检查表的作用是检查一个或多个表是否有错误，check table 对myisam 和 innodb表有作用，对于myisam表，关键字统计数据被更新
check table 也可以检查视图是否有错误，比如在视图定义中被引用的表不存在。
2. 定期优化表
优化表的语法如下：
如果删除了表的一大部分，或者如果已经对含有可变长度行的表(含有 varchar、blob或text列的表)进行更多更改，则应使用optimize table命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但optimize table 命令只对myisam、 bdb 和innodb表起作用。
例如： optimize table table_name
注意： analyze、check、optimize执行期间将对表进行锁定，因此一定注意要在mysql数据库不繁忙的时候执行相关的操作。
常用的sql优化
我们在开发的时候常常用到的sql语句，无非是insert、groupby等等。对于这些sql语句，我们怎么进行优化？
1. 大批量插入数据
当用load命令导入数据的时候，适当的设置可以提高导入的速度。
对于myisam存储引擎的表，可以通过如下方式快速的导入大量的数据
alter table tb1_name disable keys;
loading the data
alter table tb1_name enable keys;
disable keys 和 enable keys 用来打开或者关闭myisam表非唯一索引的更新。在导入大量的数据到一个非空的myisam表时，通过设置这两个命令，可以提高导入的效率。
对于导入大量的数据到一个空的myisam表时，默认就是先导入数据然后才创建索引的，索引不用进行设置。
对于innodb类型的表，这种方式不能提高导入数据的效率，但也有几种针对innodb类型的表进行优化的方式。
1. 因为innodb类型的表式按照主键的顺序保存的，所以将导入的数据按照主键的顺序排序，可以有效提高导入数据的效率。
2. 在导入数据前执行 set unique_checks=0，关闭唯一性校验，在导入结束后执行set unique_checks=1，恢复唯一性校验，可以提高导入的效率。
3. 如果应用使用自动提交的方式，建议在导入前执行set autocommit=0，关闭自动提交，导入结束后执行set autocommit=1，打开自动提交，也可以提高导入效率。
优化insert语句
当进行数据insert的时候，可以考虑采用以下几种方式进行优化
1. 如果同时从一个客户插入很多行，尽量使用多个值表的insert语句，这种方式将大大缩短客户端与mysql数据库的链接、关闭等消耗，使得效率比分开执行的单个insert语句快.
例如：
insert into test values(1,2)
insert into test values(3,4)
insert into test values(5,6)
将上面三句改为:insert into test values(1,2),(3,4),(5,6)......
2. 如果从不同客户插入很多行，能通过使用insert delayed 语句得到更高的速度。
delayed 的含义是让insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多；low_priority刚好相反，在所有其他用户对表的读写完后才进行插入。
3. 将索引文件和数据文件分在不同的磁盘上存放
4. 如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对于myisam表使用。
5. 当从一个文本文件中装载一个表时，使用load data infile。 这通常比使用很多insert语句快20倍左右。
下面是mysql 性能优化的一些其它需要注意的地方，大家结合和，充分发挥mysql的性能。
mysql优化全攻略-相关数据库命令
mysql 数据库中my.ini的优化 2g内存针对站多 抗压型的设置
1、定期分析表和检查表
分析表的语法如下：
复制代码 代码如下:
analyze [local | no_write_to_binlog] table tb1_name[, tbl_name]...
以上语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得sql能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。这对于myisam，dbd和innodb表有作用。
例如分析一个数据表
复制代码 代码如下:
analyze table table_name
检查表的语法如下：
复制代码 代码如下:
check table tb1_name[,tbl_name]...[option]...option = {quick | fast | medium | extended | changed}
检查表的作用是检查一个或多个表是否有错误，check table 对myisam 和 innodb表有作用，对于myisam表，关键字统计数据被更新
check table 也可以检查视图是否有错误，比如在视图定义中被引用的表不存在。
2. 定期优化表
优化表的语法如下：
复制代码 代码如下:
optimize [local | no_write_to_binlog] table tb1_name [,tbl_name]...
如果删除了表的一大部分，或者如果已经对含有可变长度行的表(含有 varchar、blob或text列的表)进行更多更改，则应使用optimize table命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但optimize table 命令只对myisam、 bdb 和innodb表起作用。
例如： optimize table table_name
注意： analyze、check、optimize执行期间将对表进行锁定，因此一定注意要在mysql数据库不繁忙的时候执行相关的操作。
常用的sql优化
我们在开发的时候常常用到的sql语句，无非是insert、groupby等等。对于这些sql语句，我们怎么进行优化？
1. 大批量插入数据
当用load命令导入数据的时候，适当的设置可以提高导入的速度。
对于myisam存储引擎的表，可以通过如下方式快速的导入大量的数据
复制代码 代码如下:
alter table tb1_name disable keys;
loading the data
alter table tb1_name enable keys;
disable keys 和 enable keys 用来打开或者关闭myisam表非唯一索引的更新。在导入大量的数据到一个非空的myisam表时，通过设置这两个命令，可以提高导入的效率。
对于导入大量的数据到一个空的myisam表时，默认就是先导入数据然后才创建索引的，索引不用进行设置。
复制代码 代码如下:
load data infile '/home/mysql/text_txt' into table text
对于innodb类型的表，这种方式不能提高导入数据的效率，但也有几种针对innodb类型的表进行优化的方式。
1. 因为innodb类型的表式按照主键的顺序保存的，所以将导入的数据按照主键的顺序排序，可以有效提高导入数据的效率。
2. 在导入数据前执行 set unique_checks=0，关闭唯一性校验，在导入结束后执行set unique_checks=1，恢复唯一性校验，可以提高导入的效率。
3. 如果应用使用自动提交的方式，建议在导入前执行set autocommit=0，关闭自动提交，导入结束后执行set autocommit=1，打开自动提交，也可以提高导入效率。
优化insert语句
当进行数据insert的时候，可以考虑采用以下几种方式进行优化
1. 如果同时从一个客户插入很多行，尽量使用多个值表的insert语句，这种方式将大大缩短客户端与mysql数据库的链接、关闭等消耗，使得效率比分开执行的单个insert语句快.
例如：
复制代码 代码如下:
insert into test values(1,2)
insert into test values(3,4)
insert into test values(5,6)
将上面三句改为:insert into test values(1,2),(3,4),(5,6)......
2. 如果从不同客户插入很多行，能通过使用insert delayed 语句得到更高的速度。
delayed 的含义是让insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多；low_priority刚好相反，在所有其他用户对表的读写完后才进行插入。
3. 将索引文件和数据文件分在不同的磁盘上存放
4. 如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对于myisam表使用。
5. 当从一个文本文件中装载一个表时，使用load data infile。 这通常比使用很多insert语句快20倍左右。
下面是mysql 性能优化的一些其它需要注意的地方，大家结合和，充分发挥mysql的性能。
mysql优化全攻略-相关数据库命令
mysql 数据库中my.ini的优化 2g内存针对站多 抗压型的设置