mysql 实践上总结的一些技巧.
平时看到的各种建议,很容易就采坑.高手是有意识的避免坑,所以记录下来.
建议
避免使用 select *, 尤其是在代码里面.
利用索引+排序代替 max,min
避免一次别拿太多数据;
避免一次性提交大量数据;
避免一次只更新一条数据(在需要更新大量数据下)
建表时,primary key 建议使用auto_increatment 而不是使用跟业务相关的字段.
auto_increment 列不建议 update. 在不同存储引擎下面会有不同表现.除非你很清楚之间的区别.
InnoDB 中,如是自定义主键,尽可能让主键短小.因为所有的索引最后都是跟主键关联起来,主键太长会使得辅助索引也变大.
LOAD TABLE FROM MASTER操作对InnoDB是不起作⽤的: 可以先改成Myisam表,导入数据后改成Innodb
在千万级别量级面前, 增/删字段,增/删索引,增/删大量数据,都要认为是一个棘手问题. 例如对 Innodb ,drop 会比 Delete 搞笑( delete 是一条条删的 )
联合索引优于单索引.
联合索引需要注意字段顺序,一般考虑点是: 第一业务,使用频率高的字段靠前. 第二数据本身: 尽量让可以穷举的字段靠前(我们可以在 where 条件中故意填充使其命中)
Cardinality:索引基数,很关键的⼀个参数,平均数值组=索引基数/表总数据⾏,平均数值组越接近 1就越有可能利⽤索引
在 varchar 和 text 字段建立索引,不建议使用整个字段,最好用 left(n) 指定前 n 个字符. (最左索引了解一下)
MyIsam 支持全文索引
字符长度,很短,长度固定,长度相近可以使用 char 类型. 最大长度远大于平均长度,使用 varchar.二者区别可以回去翻书.
索引断层: 看看能不能通过穷举补上去.
like 不一定走索引
单独的order by 走不了索引.mysql 在 V4.1之后, select 出数据之后,会在sort_buffer_size中排序
where满⾜最左前缀且order by中的列是该多列索引的⼦集时(相对顺序跟索引一致),能⽤索引来满⾜order by
索引隔离: where clo+1 = 100 这样的方式走不了索引,能计算的提前计算 where clo = 99
in 尽量少用,如果要用,里面最好是常量.
OR 走不了索引,是在没办法的情况下,可以考虑用UNION All ,虽然返回的数据没有去重
更新远远大于查询的列,不要创建索引
数据量少(10w) 的没必要建索引,扫描全表即可
对 Innodb 类型, count 需要带上 where 条件
与事务无关的操作踢出去,减少锁资源占用
在不破坏一致性前提下,使用多个短事务代替长事务
尽量少用触发器: 占用 cpu 资源,触发器的 sql 不会同步到从库(需要再从库建立相同的触发器)
使用 mysqldump 恢复数据的时候,可以先 disable keys:索引可以后面一次性建立
多表连接,每张表都设置别名: 可读性和避免混淆
大量数据迁移: 先导数据再建索引.
大量数据删除: 不是 delete, 是 drop. 同时最好的做法是 rename, 看看会不会有问题,有问题还能恢复.
性能相关
mysql 中一些重要的变量
key_buffer_size: 索引块缓存区⼤⼩, 针对MyISAM 能承受的最⼤值,反⽽会使mysql变得不稳定
sort_buffer_size 这是索引在排序缓冲区⼤⼩,若排序数据⼤⼩超过该值,则创建临时⽂件,注意 和myisam_sort_buffer_size的区别
read_and_buffer_size 当排序后按排序后的顺序读取⾏时,则通过该缓冲区读取⾏,避免搜索硬盘.将该变量设置为较⼤的值可以⼤⼤改进ORDER BY的性能
join_buffer_size⽤于表间关联(join)的缓存⼤⼩
tmp_table_size缓存表的⼤⼩
table_cache允许 MySQL 打开的表的最⼤个数,并且这些都cache在内存中
delay_key_write针对My 但不up索引,将索引存在内存⾥,当表关闭时,将内存索引,写到磁盘
在测试的时候,为了明确看到查询性能,需要关闭 profiling 和 query cache
1 |
|
