mysql 优化的一些小窍门
[ 2009/08/17 18:16 | by selboo ]
* 针对Innodb表,尽量不执行SELECT COUNT(*)语句,因为Innodb表没有类似MyISAM那样的内部计数器来记录表记录总量,执行这个操作将会全表扫描,速度很慢.
* 尽量使用MyISAM表,除非必须使用其他类型,因为MyISAM类型的总体读写效率是相当高的,缺点是表级锁,而不是行/页级锁.
* 善用EXPLAIN来帮助你分析查询优化情况
* 如果需要对一个较大的且并发读写较多的数据表做GROUP BY等统计操作,建议使用摘要表来存储统计信息,定期更新统计表,这可能获得很大的性能改善.
* 查询时如果有ORDER BY分句的话,注意让它的字段顺序和索引字段顺序对应,这样能加快排序速度
* 如果有一个多字段索引,则查询时,必须按照索引顺序来使用,否则该索引不会用到.例如:
索引`idx_`(col1, col2, col3),那么查询SELECT .... FROM ... WHERE col1=1 AND col2=2;使用索引,而查询... WHERE col2=2 AND col3=3;或... WHERE col1=1 AND col3=3;则不使用索引.
* WHERE中的条件如果有恒量类型的(如 `field` = 1),就尽量放在前面,这样能更快的执行过滤.
* 2 个表连接时,连接字段的类型最好一致(包括字段长度),这样的话索引速度快多了.
* 大部分情况下,字符类型的字段索引值需要一部分,例如CREATE INDEX char_idx ON tbl1 ( name(10) );
* 尽量使用最合适的数据类型,能使用ENUM就不使用TINYINT,能使用SMALLINT就不使用MEDIUMINT.这样能节省存储空间,增加数据存储量,提高搜索速度.不要担心这样会对省级产生很大的影响,因为加入从TINYINT类型改变为INT的话,并不会改变原来的数据.
* 如果知道某个表总是频繁使用的话,可以把它放到hot_cache中,用以下方法:
SET GLOBAL hot_cache.key_buffer_size=128*1024;
CACHE INDEX `xxx` IN hot_cache;
* 把拖沓复杂,速度慢的的查询分解成多个简洁明了的查询,这样尽管查询次数多了,但是总体速度和效率却可能反而更高了,而且也减少了锁表的可能.
* 执行查询时,尽量不使用外部函数,因为这样的话就无法使用可能存在的索引,并且无论如何都会极大地降低效率.如:... WHERE `create_time` > UNIX_TIMESTAMP(NOW());这样的查询.可以在程序中把当前的时间取得,然后直接执行构造好了的SQL语句.
* 在索引字段上使用 LIKE 查询时,左边不要使用 '%' 修饰符,这样就可以利用索引,否则无法使用索引.如... `name` LIKE 'yejr%';.
* 如果有可能,多使用存储过程,这大概能获得 22% 的性能提高.
* 如果并发访问量相对最大连接数小多了的话,最好使用永久连接,这样能节省不少连接时的系统资源损耗.
* 定期的在MyISAM表上执行OPTIMIZE TABLE,这能整理随便,提高索引效率.
* 如果你主要按 col1，col2，...顺序检索记录,请在对表大量更改后执行ALTER TABLE ... ORDER BY col1, col2, ...语句,这可以获得更好的性能.
* 对于频繁更改的MyISAM表,应尽量避免更新所有变长字段(VARCHAR、BLOB和TEXT).
* 对于记录总数超过500万的单表,就应该赶紧考虑分表了.分表策略有多种,比如按ID号段,或者按时间切分,等等.
* 创建数据表时尽量指定字段不能为NULL,并且有默认值.
* 使用LOAD DATA,而不是使用大批量的INSERT语句来导入数据.
* 使数据表名和字段名尽可能的短,例如在user表中使用字段名name,而不是user_name.
* 用DELAY_KEY_WRITE = 1选项让MyISAM更快地更新索引,因为在表关闭之前它们不刷新到硬盘上.缺点是如果服务器如果突然被杀掉了,重启之后就必须运行myisamchk修复索引才行.
* 采用复制机制来分摊读数据的负载,把写数据只放在主服务器上,把读平均分摊到各个从服务器上,能大大提高系统负载.
* 尽量使用MyISAM表,除非必须使用其他类型,因为MyISAM类型的总体读写效率是相当高的,缺点是表级锁,而不是行/页级锁.
* 善用EXPLAIN来帮助你分析查询优化情况
* 如果需要对一个较大的且并发读写较多的数据表做GROUP BY等统计操作,建议使用摘要表来存储统计信息,定期更新统计表,这可能获得很大的性能改善.
* 查询时如果有ORDER BY分句的话,注意让它的字段顺序和索引字段顺序对应,这样能加快排序速度
* 如果有一个多字段索引,则查询时,必须按照索引顺序来使用,否则该索引不会用到.例如:
索引`idx_`(col1, col2, col3),那么查询SELECT .... FROM ... WHERE col1=1 AND col2=2;使用索引,而查询... WHERE col2=2 AND col3=3;或... WHERE col1=1 AND col3=3;则不使用索引.
* WHERE中的条件如果有恒量类型的(如 `field` = 1),就尽量放在前面,这样能更快的执行过滤.
* 2 个表连接时,连接字段的类型最好一致(包括字段长度),这样的话索引速度快多了.
* 大部分情况下,字符类型的字段索引值需要一部分,例如CREATE INDEX char_idx ON tbl1 ( name(10) );
* 尽量使用最合适的数据类型,能使用ENUM就不使用TINYINT,能使用SMALLINT就不使用MEDIUMINT.这样能节省存储空间,增加数据存储量,提高搜索速度.不要担心这样会对省级产生很大的影响,因为加入从TINYINT类型改变为INT的话,并不会改变原来的数据.
* 如果知道某个表总是频繁使用的话,可以把它放到hot_cache中,用以下方法:
SET GLOBAL hot_cache.key_buffer_size=128*1024;
CACHE INDEX `xxx` IN hot_cache;
* 把拖沓复杂,速度慢的的查询分解成多个简洁明了的查询,这样尽管查询次数多了,但是总体速度和效率却可能反而更高了,而且也减少了锁表的可能.
* 执行查询时,尽量不使用外部函数,因为这样的话就无法使用可能存在的索引,并且无论如何都会极大地降低效率.如:... WHERE `create_time` > UNIX_TIMESTAMP(NOW());这样的查询.可以在程序中把当前的时间取得,然后直接执行构造好了的SQL语句.
* 在索引字段上使用 LIKE 查询时,左边不要使用 '%' 修饰符,这样就可以利用索引,否则无法使用索引.如... `name` LIKE 'yejr%';.
* 如果有可能,多使用存储过程,这大概能获得 22% 的性能提高.
* 如果并发访问量相对最大连接数小多了的话,最好使用永久连接,这样能节省不少连接时的系统资源损耗.
* 定期的在MyISAM表上执行OPTIMIZE TABLE,这能整理随便,提高索引效率.
* 如果你主要按 col1，col2，...顺序检索记录,请在对表大量更改后执行ALTER TABLE ... ORDER BY col1, col2, ...语句,这可以获得更好的性能.
* 对于频繁更改的MyISAM表,应尽量避免更新所有变长字段(VARCHAR、BLOB和TEXT).
* 对于记录总数超过500万的单表,就应该赶紧考虑分表了.分表策略有多种,比如按ID号段,或者按时间切分,等等.
* 创建数据表时尽量指定字段不能为NULL,并且有默认值.
* 使用LOAD DATA,而不是使用大批量的INSERT语句来导入数据.
* 使数据表名和字段名尽可能的短,例如在user表中使用字段名name,而不是user_name.
* 用DELAY_KEY_WRITE = 1选项让MyISAM更快地更新索引,因为在表关闭之前它们不刷新到硬盘上.缺点是如果服务器如果突然被杀掉了,重启之后就必须运行myisamchk修复索引才行.
* 采用复制机制来分摊读数据的负载,把写数据只放在主服务器上,把读平均分摊到各个从服务器上,能大大提高系统负载.
Mysql数据库基本配置建议
[ 2009/08/04 11:44 | by selboo ]
作者：石展 来源：http://dbaview.cn 转载请注明作者和出处，且不能用于商业用途，违者必究。
如下是数据库安装后的基本配置规范，提取了配置中最最通用的部分，给出数据库最基本的配置要求，且给出的参数是脱离应用，不会由于参数调整引起crash的，请大家回贴多多拍砖！
一.mysql配置文件my.cnf参考:
#####################################################
[mysqld]
#通用配置部分
port
= 端口
socket
= mysql_path/var/mysql.sock
table_cache = 512
thread_cache = 30
max_connections = 500
back_log = 400
max_connect_errors = 1000
thread_concurrency = 8
log-error=ecomdb-err.log
log-slow-queries = ecomdb-slow.log
long_query_time =1
#mysql 4.1以上开启
#log-queries-not-using-indexes
#若是主库，则开启binlog
#log-bin=ecomdb-master-bin
#建议配置部分(若需结合应用寻求更多帮助，直接回贴问石展~~~~)
#若使用innodb请开启如下配置
#innodb_buffer_pool_size = (内存的50%-80%,建议保守设置)
#innodb_additional_mem_pool_size = 100M
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 16M
#######################################################
2.重要配置选项说明:
(1) 最大连接数
Max_connections为允许的并行客户端最大连接数，为避免连接数溢出引起连接拒绝，调大此值至500:
Max_connections = 500
(2) 最大错误连接数
Max_connect_errors为最大错误连接数，默认为10，当由于密码错误等原因引起与数据库和客户端中断超过该数目，则阻塞后面的连接。建议调大为1000:
Max_connect_errors = 1000
(3) 表缓存
table_cache为所有线程打开的表数目的缓存，增大该值可以增加mysqld需要的文件描述符的数量，建议调至512:
table_cache = 512
(4) 线程缓存
thread_cache为数据库端的线程缓存。如果新连接很多，可以增加该变量以提高性能。此参数和应用对数据库的连接数关系较大，一般比数据库的正常连接数稍大即可，可以考虑设置为30:
thread_cache = 30
(5) back_log
当主MySQL线程在短时间内得到许多连接请求时发挥作用。主线程需要花一些时间(尽管很少)来检查连接并启动一个新线程。back_log值说明 MySQL临时停止响应新请求前在短时间内可以堆起多少请求，可以考虑调整为400：
back_log = 400
(6) 慢查询日志
如下配置将时间超过 long_query_time(秒) 的慢查询记录到适当命名的慢查询日志文件中，以方便进行数据库优化，超时时间建议为1秒，此项必须开启。
log-slow-queries = ecomdb-slow.log
long_query_time =1
#mysql 4.1以上开启，可记录未使用索引的sql
#log-queries-not-using-indexes
(7) 二进制日志
对于主库，需开启二进制日志，在灾难恢复及数据库同步时使用：
log-bin=sfdbwsz-master-bin
(8) 错误日志
将数据库严重错误时的信息写入指定文件名的日志文件中，数据库故障时需首先查看这些日志:
log-error=ecomdb-err.log
如下是数据库安装后的基本配置规范，提取了配置中最最通用的部分，给出数据库最基本的配置要求，且给出的参数是脱离应用，不会由于参数调整引起crash的，请大家回贴多多拍砖！
一.mysql配置文件my.cnf参考:
#####################################################
[mysqld]
#通用配置部分
port
= 端口
socket
= mysql_path/var/mysql.sock
table_cache = 512
thread_cache = 30
max_connections = 500
back_log = 400
max_connect_errors = 1000
thread_concurrency = 8
log-error=ecomdb-err.log
log-slow-queries = ecomdb-slow.log
long_query_time =1
#mysql 4.1以上开启
#log-queries-not-using-indexes
#若是主库，则开启binlog
#log-bin=ecomdb-master-bin
#建议配置部分(若需结合应用寻求更多帮助，直接回贴问石展~~~~)
#若使用innodb请开启如下配置
#innodb_buffer_pool_size = (内存的50%-80%,建议保守设置)
#innodb_additional_mem_pool_size = 100M
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 16M
#######################################################
2.重要配置选项说明:
(1) 最大连接数
Max_connections为允许的并行客户端最大连接数，为避免连接数溢出引起连接拒绝，调大此值至500:
Max_connections = 500
(2) 最大错误连接数
Max_connect_errors为最大错误连接数，默认为10，当由于密码错误等原因引起与数据库和客户端中断超过该数目，则阻塞后面的连接。建议调大为1000:
Max_connect_errors = 1000
(3) 表缓存
table_cache为所有线程打开的表数目的缓存，增大该值可以增加mysqld需要的文件描述符的数量，建议调至512:
table_cache = 512
(4) 线程缓存
thread_cache为数据库端的线程缓存。如果新连接很多，可以增加该变量以提高性能。此参数和应用对数据库的连接数关系较大，一般比数据库的正常连接数稍大即可，可以考虑设置为30:
thread_cache = 30
(5) back_log
当主MySQL线程在短时间内得到许多连接请求时发挥作用。主线程需要花一些时间(尽管很少)来检查连接并启动一个新线程。back_log值说明 MySQL临时停止响应新请求前在短时间内可以堆起多少请求，可以考虑调整为400：
back_log = 400
(6) 慢查询日志
如下配置将时间超过 long_query_time(秒) 的慢查询记录到适当命名的慢查询日志文件中，以方便进行数据库优化，超时时间建议为1秒，此项必须开启。
log-slow-queries = ecomdb-slow.log
long_query_time =1
#mysql 4.1以上开启，可记录未使用索引的sql
#log-queries-not-using-indexes
(7) 二进制日志
对于主库，需开启二进制日志，在灾难恢复及数据库同步时使用：
log-bin=sfdbwsz-master-bin
(8) 错误日志
将数据库严重错误时的信息写入指定文件名的日志文件中，数据库故障时需首先查看这些日志:
log-error=ecomdb-err.log
mysql 删除表中的重复记录
[ 2009/07/31 20:53 | by selboo ]
MySQL中漏掉了40%左右的数据，寻找这个错误的代价是花了三天，将所有的代码重新检查了一遍，由于代码不是我写的，看得晕头转向，上苍保佑，我还不算太笨，最终找到了原因，不过接着就碰到另外一个问题，找回的数据中存在重复，而且在无法在重复的健上建立唯一，于是我想了一个最笨的方法
这种算法是最傻也是最没出息的算法，在数量在1W的时候还可以接受，到10W就要两分钟了，到50W就死在那里了，所以告诫那些跟我一样天资不算聪明的人，数据量比较大的时候千万别用这种方法，这种类似冒泡排序的算法复杂度是O(n^2),如果你有10W条记录，你自己乘一下吧
接着又想了另外一个方法，虽然机械了点，不过效率非常之高,分三步走：
10W条记录，在第一步处理师大约为4秒，50w条记录时为19秒左右，再往上就没测了
后面两条语句纯粹是体力活，如果你用的是phpadmin，点点鼠标就可以搞定
SELECT * FROM sub_new WHERE email IN (
SELECT email FROM sub_new
GROUP BY email HAVING count(email) > 1
)
SELECT email FROM sub_new
GROUP BY email HAVING count(email) > 1
)
这种算法是最傻也是最没出息的算法，在数量在1W的时候还可以接受，到10W就要两分钟了，到50W就死在那里了，所以告诫那些跟我一样天资不算聪明的人，数据量比较大的时候千万别用这种方法，这种类似冒泡排序的算法复杂度是O(n^2),如果你有10W条记录，你自己乘一下吧
接着又想了另外一个方法，虽然机械了点，不过效率非常之高,分三步走：
CREATE TABLE tmp AS SELECT * FROM youtable
GROUP BY name
DROP TABLE youtable
ALTER TABLE tmp RENAME youtable
GROUP BY name
DROP TABLE youtable
ALTER TABLE tmp RENAME youtable
10W条记录，在第一步处理师大约为4秒，50w条记录时为19秒左右，再往上就没测了
后面两条语句纯粹是体力活，如果你用的是phpadmin，点点鼠标就可以搞定
Mysql日志分析的好工具–mysqlsla
[ 2009/07/06 10:52 | by selboo ]
从http://hackmysql.com/mysqlsla下载mysqlsla，当前最新版为：2.03
tar zxvf mysqlsla-2.03.tar.gz
cp mysqlsla-2.03/bin/mysqlsla /usr/local/bin
如果提示“Can’t locate DBI.pm”，执行以下命令
yum install perl-DBI perl-DBD-MySQL -y
这时就可以使用mysqlsla了，用法如下
mysqlsla -lt slow slow.log
整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.
格式说明如下:
总查询次数 (queries total), 去重后的sql数量 (unique)
输出报表的内容排序(sorted by)
最重大的慢sql统计信息, 包括 平均执
tar zxvf mysqlsla-2.03.tar.gz
cp mysqlsla-2.03/bin/mysqlsla /usr/local/bin
如果提示“Can’t locate DBI.pm”，执行以下命令
yum install perl-DBI perl-DBD-MySQL -y
这时就可以使用mysqlsla了，用法如下
mysqlsla -lt slow slow.log
整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.
格式说明如下:
总查询次数 (queries total), 去重后的sql数量 (unique)
输出报表的内容排序(sorted by)
最重大的慢sql统计信息, 包括 平均执
MySQL时间字段究竟使用INT还是DateTime
[ 2009/07/06 10:31 | by selboo ]
环境：
Windows XP
PHP Version 5.2.9
MySQL Server 5.1
第一步、创建一个表date_test（非定长、int时间）
CREATE TABLE `test`.`date_test` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` INT NOT NULL ,
`some_content` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
第二步、创建第二个表date_test2（定长、int时间）
CREATE TABLE `test`.`date_test2` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` INT NOT NULL ,
`some_content` CHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
第三步、创建第三个表date_test3（varchar、datetime时间）
CREATE TABLE `test`.`date_test3` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` DATETIME NOT NULL ,
`some_content` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
第四步、创建第四个表date_test3（char、datetime时间）
CREATE TABLE `test`.`date_test4` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` DATETIME NOT NULL ,
`some_content` CHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
ok，现在我们开始做测试，环境是php，先向各个表插入一百万条数据。插入的时候分200次，每次进库5000条。
表一执行记录：页面运行时间: 26.5997889042 秒，插入的时候发现一个有趣的现象：SELECT count( id ) FROM `date_test` WHERE 1 的结果是100w,而直接select * from `date_test`却是1,000,374条结果。（后来看到这是一个可能接近的值，请参看MySQL FAQ 3.11）。
表二执行记录：页面运行时间: 62.3908278942 秒，这次记录是1,000,066条。
表三执行记录：页面运行时间: 30.2576560974 秒，这次的是1,000,224条。
表四执行记录：页面运行时间: 67.5393900871 秒，这次的是：1,000,073条。
现在把四个表的start_time字段一一加上索引。
测试四个表的更新，分别update 100条记录，并记录时间:
表一：页面运行时间: 2.62180089951 秒（非定长，int时间）
表二：页面运行时间: 2.5475358963 秒（定长，int时间）
表三：页面运行时间: 2.45077300072 秒（varchar,datetime时间）
表四：页面运行时间: 2.82798409462 秒（char,datetime时间）
测试四个表的读取，分别select 100条随机记录，以主键id为条件查询，并记录时间：
表一：页面运行时间: 0.382651090622 秒（非定长，int时间）
表二：页面运行时间: 0.542181015015 秒（定长，int时间）
表三：页面运行时间: 0.334048032761 秒（varchar,datetime时间）
表四：页面运行时间: 0.506206989288 秒（char,datetime时间）
测试四个表的读取，分别select 10条随机记录，以star_time为条件查询，并记录时间：
表一：页面运行时间: 30.1972880363 秒（非定长，int时间）
表二：页面运行时间: 65.1926910877 秒（定长，int时间）
表三：页面运行时间: 39.7210869789 秒（varchar,datetime时间）
表四：页面运行时间: 70.4632740021 秒（char,datetime时间）
因为量比较小，所以我们默认即使是微小的变化，也是有意义的。
结论：
大数据量下，如果存在大量的select * from table where 时间>XX这样的查询，在MySQL5.1时使用int换datetime是有意义的。
Windows XP
PHP Version 5.2.9
MySQL Server 5.1
第一步、创建一个表date_test（非定长、int时间）
CREATE TABLE `test`.`date_test` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` INT NOT NULL ,
`some_content` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
第二步、创建第二个表date_test2（定长、int时间）
CREATE TABLE `test`.`date_test2` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` INT NOT NULL ,
`some_content` CHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
第三步、创建第三个表date_test3（varchar、datetime时间）
CREATE TABLE `test`.`date_test3` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` DATETIME NOT NULL ,
`some_content` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
第四步、创建第四个表date_test3（char、datetime时间）
CREATE TABLE `test`.`date_test4` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` DATETIME NOT NULL ,
`some_content` CHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
ok，现在我们开始做测试，环境是php，先向各个表插入一百万条数据。插入的时候分200次，每次进库5000条。
表一执行记录：页面运行时间: 26.5997889042 秒，插入的时候发现一个有趣的现象：SELECT count( id ) FROM `date_test` WHERE 1 的结果是100w,而直接select * from `date_test`却是1,000,374条结果。（后来看到这是一个可能接近的值，请参看MySQL FAQ 3.11）。
表二执行记录：页面运行时间: 62.3908278942 秒，这次记录是1,000,066条。
表三执行记录：页面运行时间: 30.2576560974 秒，这次的是1,000,224条。
表四执行记录：页面运行时间: 67.5393900871 秒，这次的是：1,000,073条。
现在把四个表的start_time字段一一加上索引。
测试四个表的更新，分别update 100条记录，并记录时间:
表一：页面运行时间: 2.62180089951 秒（非定长，int时间）
表二：页面运行时间: 2.5475358963 秒（定长，int时间）
表三：页面运行时间: 2.45077300072 秒（varchar,datetime时间）
表四：页面运行时间: 2.82798409462 秒（char,datetime时间）
测试四个表的读取，分别select 100条随机记录，以主键id为条件查询，并记录时间：
表一：页面运行时间: 0.382651090622 秒（非定长，int时间）
表二：页面运行时间: 0.542181015015 秒（定长，int时间）
表三：页面运行时间: 0.334048032761 秒（varchar,datetime时间）
表四：页面运行时间: 0.506206989288 秒（char,datetime时间）
测试四个表的读取，分别select 10条随机记录，以star_time为条件查询，并记录时间：
表一：页面运行时间: 30.1972880363 秒（非定长，int时间）
表二：页面运行时间: 65.1926910877 秒（定长，int时间）
表三：页面运行时间: 39.7210869789 秒（varchar,datetime时间）
表四：页面运行时间: 70.4632740021 秒（char,datetime时间）
因为量比较小，所以我们默认即使是微小的变化，也是有意义的。
结论：
大数据量下，如果存在大量的select * from table where 时间>XX这样的查询，在MySQL5.1时使用int换datetime是有意义的。
