最近看高性能MYSQL,整理的一些注意点:
mysql优化:
1, 整数型索引优于字符型索引
2, 在长度够用的情况下,尽可能用较小字段,能用int,就不用bigint,能用varchar(50),不用varchar(500);这样可以节约更多的资源
3, 即使varchar(1000) 和varchar(100)在存储相同长字符的时候,占用存储空间一样,但因为1000会预留部分磁盘空间,所以会产生更多碎片;
Memory存储引擎(临时表) 不支持动态行,所以varchar(1000)会被内部转换成char(1000),所以varchar(1000)会比varchar(100)占用更多内存;
4, OPTIMIZE TABLE会让表数据重新排列,从而消除碎片
5, 不要建立重复索引,不要建立多余索引(A,B)和(A),(A)是多余索引
6, 如果字符串长度比较长,又需要作为检索条件,可以使用部分索引(A(N)),SELECT COUNT(DISTINCT LEFT(字段,N))/COUNT(*) AS alias from tbname
7, 除非真要保存NULL,否则就定义NOT NULL,default '';
当NULL被处理的时候,每一条记录都需要额外的一个字节
MYSQL内部还需要特殊处理;
还会导致固定大小的索引变成可变大小索引;
8, 尽量不要使用BLOB和TEXT;Memory引擎(临时表)不支持BLOB和TEXT,DB会使用磁盘的MYISAM临时表,导致严重性能开销;
如果无法避免BLOB/TEXT,检索时可以使用left(str, length);right(str, length) ;substring(str, pos) ;substring(str, pos, length) 转换为字符串
9,使用ENUM代替字符串类型; 如果字符串个数是有限的几个,可以存储为ENUM;DB内部会将其存储为整数,被检索时会自动转换为字符串
10, TIMESTAMP 占用4字节,DATETIME占用8字节,TIMESTAMP性能更好
11, 存储IP地址,可以使用函数inet_aton()和inet_ntoa()将IP转换成整型存储,提升性能
12, 索引的时候,要对索引字段使用隔离列; index + 1 = 5 不会使用索引,index = 5 - 1会
13, 使用覆盖索引性能更好EXPLAIN EXTRA:"Using Index"
14, 如果索引太多,会对INSERT,UPDATE,DELETE等有较大影响,因为MYSQL需要维护索引;
15, 如果WHERE有多个范围条件查询,则MYSQL能为第一个范围使用索引,无法为第二个使用
16, Extra出现: Select tables optimized away,最优的,mysql无需查询可以直接读取数据
17, MyISAM会把表的总行数存储,而Innodb需要进行查询;(可以explain验证)
18, IN()和 OR在mysql中不相等,Mysql 对IN() 中数据进行排序,然后用二分法查找数据是否在里面,列表很大的时候OR会慢的多;