作者:LAMP小白 点击:2094 发布日期:2012-11-12 01:17:00 返回列表
为提高查询效率而挑选数据类型
尽量使用数值操作,少使用字符串操作
如果字符串数据列的可取值是有限的,选用ENUM或SET类型可以在数值操作中受益,这些类型在MYSQL内部是以数值的形式表示的
如果小类型够用,就不要选择大类型
如果INT够用就不必使用BIGINT
如果能选择数据行的存储格式,就应该尽量选择最适用你的存储引擎的格式
对于MYISAM来说,应该选择使用定长数据列而不是变长,因为变长虽然节约空间,但是读写性能不如定长,更不用说修改时产生的磁盘碎片了(玩过碎片整理都知道)
*对于使用变长的数据行,需要定期使用OPTIMIZE TABLE来保持性能
*如果数据表崩溃,定长数据行更容易恢复,因为数据行的长度是固定的,所以每个数据行的开头都出现在数据行长度的整数倍位置
*固定长度数据列虽然速度快,不过会占用更多的磁盘空间(空白值也是如此)
*有时候,就算你想使用定长类型,但是目前还没有一种定长类型能容纳超多255字符的string
对于memory,强制使用定长来存储数据
对于innoDB,因为他的每条数据行都有一个标头,存放着指向有关数据列的指针,变长定长差距不大,从减少IO的角度来说VARCHAR反而更加有利(当然,频繁修改的列还是用定长,因为变长必然会出碎片)
尽量使用NOT NULL
这样MYSQL不必再去检查他的值是否为NULL
考虑使用ENUM数据列
枚举在MYSQL内部会被转为数字,并适用于数字算法,效率远高于字符算法
使用PROCEDURE ANALYSE()获取优化建议
mysql> SELECT * FROM tt1 PROCEDURE ANALYSE()G
*************************** 1. row ***************************
Field_name: mio.tt1.id
Min_value: 0
Max_value: 999
Min_length: 1
Max_length: 3
Empties_or_zeros: 1
Nulls: 0
Avg_value_or_avg_length: 499.5000
Std: 288.6750
Optimal_fieldtype: SMALLINT(3) UNSIGNED NOT NULL //推荐使用smallint
*************************** 2. row ***************************
Field_name: mio.tt1.val
Min_value: 1
Max_value: 1
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 1.0000
Std: 0.0000
Optimal_fieldtype: ENUM('1') NOT NULL //推荐使用枚举
2 rows in set (0.01 sec)
对容易产生碎片的数据表进行整理
对各种引擎都适用的整理办法是这样的
mysqldump db_name table_name > dump.sql
mysql db_name < dump.sql
将数据压缩到BLOG或TEXT中
使用人造索引
最好的例子恐怕就是计数表了:)
尽量避免对BLOG和TEXT进行搜索
把BLOG和TEXT剥离到另外一张表里去
有效加载数据
*批量加载的效率比单行加载的效率高,因为键缓存在每一次输入的记录加载以后都不需刷新
*加载有索引的数据表比加载无索引的数据表快一些
*短而严谨的SQL优于长而偷懒的SQL,因为MYSQL会为他做更多的语法分析
*使用LOAD DATA优于INSERT,因为是批量的
*LOAD DATA优于LOAD DATA LOCAL 因为LOAD DATA LOCAL需要读取文件然后使用网络将数据送至MYSQL
如果只能使用INSERT,那么可以对他们进行分组以减少刷新次数
对于事务引擎:
START TRANSACTION
INSERT INTO ...;
INSERT INTO ...;
COMMIT;
对于非事务引擎:
LOCK TABLES tt1 WRITE;
INSERT INTO ...;
INSERT INTO ...;
UNLOCK TABLES;
对于MYISAM减少索引的另一种策略是使用DELAY_KEY_WRITE,不过如果设置了延迟写入,服务器意外关机将有可能导致索引值的丢失,不过可以给出 --mysql-recover=FORCE这样mysql启动的时候会自动检查索引
在从MYSQL服务器上用--delay-key-write=ALL 选项为所有的myisam数据表启动延迟写入
如果某个MYISAM数据表混合出现INSERT和SELECT操作,可以考虑使用并发插入(CONSURRENT INSERT)功能来提高总体效率
上一篇:MYSQL查询优化程序 下一篇:快递查询API