Nemo的轨迹

work hard, be persistent, and good luck

0%

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
2
3
4
5
6

set profiling = 1

set query_cache_type = 0

set GLOBAL query_cache_size = 0;