MySQL设计和开发规范
// 龟腚,没啥好说的 。。。
索引名称以idx_列名命名,如果多列考虑列名缩写
唯一索引以uk_列名命名
索引占磁盘空间,不要重复的索引,尽量短
只给常用的查询条件加索引
?
?
//索引选择性是不重复的索引值也叫基数(cardinality)表中数据行数的比值,索引选择性=基数/数据行,基数可以通过“show index from 表名”查看。
高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行,唯一索引的选择性最佳,值为1。低cardinality 会导致 mysql 引擎执行计划后全表扫描,而不走索引,这是因为二叉树索引本来最适合的就是点查询,和小范围的range查询,当预估返回的数据量超过一定比例的时候,再根据索引一条一条去查就慢了,反而不如全表扫描快了。Mysql有自己内部自动优化机制,但有些自动优化机制可能不是最优的。这时候就需要人工去干预。
比如长期不优化表,Mysql判断出索引不优,就会不使用索引。
有时候就要人工强制使用真正高效的索引(FORCE INDEX)。
过滤性高的列建索引,取值范围固定的列不建索引
唯一的记录添加唯一索引
?
?
// 知道怎么快速 load 大量数据到 mysql 么?优化点之一先干掉索引,导入后再重建,道理一样一样的不解释,可以说 索引带来了查询效率的提升,他的劣势就是在每次 isnert/update 都需要重新平衡索引Tree带来效率的下降。
频繁更新的列不要建索引
?
?
// 恩,计算了就走不了索引了,把计算移到右边去
不要对索引列运算
?
?
// 索引占空间和影响效率的,so,有长度限制的,对于过长的字符型字段,可以只对其进行前缀索引。
同样过滤效果下,保持索引长度最小
?
?
//最左前缀前缀原则,这是由BTree这种数据结构决定的
合理利用组合索引,注意索引字段先后顺序
?
?
// 注意组合索引和多列索引的区别
多列组合索引,过滤性高的字段最前
?
?
// explain 看执行计划,对于 OLTP 应用来说 出现了 filesort 是不可接受的。
order by 字段建立索引,避免filesort
?
?
//最左前缀前缀原则,简单的说就是联合索引当中不能断了
例如:
索引idx(c1,c2,c3),相当于建立了idx(c1),idx(c1,c2)和idx(c1,c2,c3)三个索引。其它组合是没法走索引的,例如 (c1,c3)、(c2,c3),可以思考、实践下 (c2,c1,c3) 会走索引嘛?
组合索引,不同的排序顺序 ,不能使用索引
?
?
// 其实这本质上还是索引选择性的问题
<>?!= 无法使用索引
?
?
//覆盖索引(covering index),MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后再去查询数据,所以那是相当的快!但是同时也要求所查询的字 段必须被索引所覆盖到,在Explain的时候,输出的Extra信息中如果有“Using Index”,就表示这条查询使用了覆盖索引。
覆盖索引的示例 :
Create index index_name1 on table1(col2,col1,col3).
Select col1,col3 from table1 where col2 = 'value'.
so,建议大家别随便 select * from xxx
覆盖索引
?
?
// like‘%xx%’, 不符合前缀匹配的规则,因此用不上索引字段,只能作全表扫描。
但这也不是绝对的,select id from tb where title like ‘%abcd%’; 如果你这里是用覆盖索引那么是可以走索引的。
注意模糊匹配
?
说明:
1、索引用的好坏直接决定了数据库的性能,更多内容可以参考:
http://my.oschina.net/leejun2005/blog/73912
http://my.oschina.net/leejun2005/blog/134932
http://my.oschina.net/leejun2005/blog/133791
2、最后提下设计数据库时,应当根据当前数据量和增长趋势,结合业务来进行水平/垂直拆分,必要时可以空间换时间。
3、可以了解下常用的 MS 架构,要保证高可用的话可以考虑 MMM 等架构。