高效mysql的习惯(程序员版本)
高效的mysql
- 一定要有主键
- 如果没有主键:
- 数据多次读写后可能更离散,有更多随机I/O
- MySQL复制环境中,如果选择RBR模式,没有主键的update需要读全表,导致复制延迟
- 如果没有主键:
- 好的主键特点:
- 没有业务用途
- 数值呈连续增长,最好是自增
- 坚决不能选用CHAR/UUID等类型
- 关于数据长度
- 够用前提下,越短越好
- 消耗更少的存储空间
- 需要进行排序时,消耗更少的内存空间
- 例如用INT UNSIGNED存储IPV4地址,不用CHAR(15)类型
- 案例:11个字符长度的数值,bigint vs char(120) vs char(11),1万条记录,Logical_read:111 vs 1170 vs 224
- 适当使用TEXT/BLOB类型
- data page默认16KB
- 每行长度超过8KB时,就需要分裂data page
- 产生更多离散I/O
- 案例:一个100G的表拆分成4个表后,总大小仅25G
- 每个表增加create_time、update_time两个字段
- 分别表示写入时间以及最后更新时间
- 业务上可能用不到,但是对日常运维管理则非常有用
- 可以用来判断哪些是可以归档的老数据,定期进行归档
- 用来做自定义的差异备份也很方便
- 索引很重要
- InnoDB行锁是基于索引实现
- 如果没有索引,将会是灾难性的
- 读取时,全表扫描
- 修改时,全表记录锁
- 索引设计
- 基数(cardinality)低的字段一般没必要建立单列索引
- 字符型字段上建索引时优先采用部分索引(prefix index)
- 5.6.9之后,optimizer能识别到普通索引同时存储主键值,无需显式定义加上主键列(Index Extensions)
- 优先多列联合索引(multi column index),少用单列索引
- 怎么算是好SQL
- 所有WHERE条件都加上引号
- 避免潜在的类型隐式转换风险
- 避免个别条件失效时SQL语法错误
- 不SELECT *
- 减少不必要的I/O
- 提高可以利用覆盖索引的几率
- 避免SQL注入风险
- 所有用户输入值都要做过滤
- 利用PREPARE做预处理
- 利用SQL_MODE做限制
- LIKE查询时,不要用%通配符最左前导(无法使用索引)
- 能UNION ALL就不要UNION(UNION需要去重,会产生临时表)
- SQL中最好不要有运算
- WHERE子句中,不要有函数
- 关于join
- 满足业务需求前提下尽量用inner join,让优化器自动选择驱动表
- 有时候优化器选择的驱动表未必是最优的,可以尝试手动调整
- 最后的排序字段如果不在驱动表中,则会有filesort
- eg:
- UPDATE t SET c2 = ? AND c3 = ? WHERE c1 = ‘?’ -> UPDATE t SET c2 = ? , c3 = ? WHERE c1 = ‘?’
- UPDATE t SET c2 = ?WHERE c1 = ‘?’ -> UPDATE t SET c2 = ? WHERE c1 = ‘?’
- SELECT * FROM t WHERE c1 = ‘?’ -> SELECT c2,c3 FROM t WHERE c1 = ‘?’
- SELECT c2,c3 FROM t WHERE c4 like ‘%???%’ -> SELECT c2,c3 FROM t WHERE c1 >= ? AND c1 <= ? AND c4 like ‘%???%’
- SELECT c2,c3 FROM t WHERE date(c1) = ‘2016-10-15’ -> SELECT c2,c3 FROM t WHERE c1 >= ‘2016-10-15’ AND c1 < ‘2016-10-16’
- SELECT c2,c3 FROM t WHERE char_col = int_value -> SELECT c2,c3 FROM t WHERE char_col = ’int_value’
- 关于EXPLAIN
- 关键业务SQL上线前,都要EXPLAIN确认其执行计划
- 或提前分析slow query log,防患未然
- EXPLAIN中如果有Using temporary、Using filesort、或type=ALL时,尽量想办法进行优化
- 存储引擎
- 抛弃MyISAM,InnoDB为王
- 适当的场景下可以采用TokuDB
- 误区:MEMORY可不见得就快
以上参照了2016杭州云栖大会分享
本文来自博客园,作者:追梦1819,转载请注明原文链接:https://www.cnblogs.com/yanfei1819/p/9947117.html