MYSQL--my.cnf配置中文详解
找出最耗资源的sql语句进行优化
2007/08/13 00:00
一. 启动参数优化
修改 my.cnf (或者my.ini)，加入/修改以下几行
#设定缓存的连接数,节省连接时的开销
back_log = 64
#禁用文件系统外部锁
external-locking = 0
#禁用BDB,如果你确实不需要的话,innodb也是如此
skip-bdb
#索引缓冲,如果是专用的数据库服务器,可以设置高达服务器内存的一半,如果不是专用的,还是设置得低一点
key_buffer = 512M
#缓存数据表数量,如果内存较大,可以设置稍微高一点,否则还是设置低一点
#设置这个参数可以参见系统状态中的 open_tables(表示当前打开的数据表总数) 和 opened_tables(表示所有打开的数据表总数)
table_cache = 128
#禁用dns解析,如果你的授权信息中采用dns授权方式了,就不能启用该选项
skip-name-resolve
#记录慢查询和没有使用索引的查询,便于帮助分析问题所在
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes
其他参数诸如 sort_buffer_size,net_buffer_length,read_buffer_size,read_rnd_buffer_size,myisam_sort_buffer_size,thread_cache_size,query_cache_size,max_binlog_cache_size 等请查询MySQL手册,然后做出合适的调整.
二. 其他小TIPS
针对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; 则不使用索引.
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 修复索引才行.
采用复制机制来分摊读数据的负载,把写数据只放在主服务器上,把读平均分摊到各个从服务器上,能大大提高系统负载.
修改 my.cnf (或者my.ini)，加入/修改以下几行
引用
#设定缓存的连接数,节省连接时的开销
back_log = 64
#禁用文件系统外部锁
external-locking = 0
#禁用BDB,如果你确实不需要的话,innodb也是如此
skip-bdb
#索引缓冲,如果是专用的数据库服务器,可以设置高达服务器内存的一半,如果不是专用的,还是设置得低一点
key_buffer = 512M
#缓存数据表数量,如果内存较大,可以设置稍微高一点,否则还是设置低一点
#设置这个参数可以参见系统状态中的 open_tables(表示当前打开的数据表总数) 和 opened_tables(表示所有打开的数据表总数)
table_cache = 128
#禁用dns解析,如果你的授权信息中采用dns授权方式了,就不能启用该选项
skip-name-resolve
#记录慢查询和没有使用索引的查询,便于帮助分析问题所在
long_query_time = 1
log-slow-queries = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes
其他参数诸如 sort_buffer_size,net_buffer_length,read_buffer_size,read_rnd_buffer_size,myisam_sort_buffer_size,thread_cache_size,query_cache_size,max_binlog_cache_size 等请查询MySQL手册,然后做出合适的调整.
二. 其他小TIPS
引用
针对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; 则不使用索引.
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 修复索引才行.
采用复制机制来分摊读数据的负载,把写数据只放在主服务器上,把读平均分摊到各个从服务器上,能大大提高系统负载.
