数据类型选择方面的几个原则:
1,更小通常更好,选择能正确表示数据的最小类型。
2,简单就好,用简单类型优于用复杂类型。
3,避免NULL,尽量定义字段为not null。性能提升很小。
字符串的选型:
当最大长度远远大于平均长度时,并很少发生更新时,选择VARCHAR。
CHAR适合保存用户密码的MD5哈希值。
MySql存储的最小时间单位是秒,但是可以用毫秒进行临时计算。
InnoDB有一个特别功能叫做自适应哈希索引,当InnoDB注意到一些索引值被很频繁的访问的时候,它就会在BTree的顶端为这些值建立内存索引。
索引包含了来自于表中的一列或者多列的值,如果索引了多列数据,列的顺序非常重要,因为Mysql只能高效的搜索所以你的最左前缀。
聚集索引:不是一种单独的索引类型,而是一种存储数据的方式。当表有聚集索引的时候,它的数据行实际保存在索引的叶子页中。每个表只能有一个聚集索引。
使用时注意设置前缀索引。索引选择要注意不重复的索引值。
索引策略总结:
检查最常用的查询,不要不调查就随便建索引。为了找到建索引的平衡,应该测试和剖析。第一检查的就是响应时间,要为耗时很长的查询添加索引;然后就是检查导致最大负载的查询,添加索引;最好要把系统的cpu、内存和磁盘瓶颈考虑进去。
在任何可能的地方,都要试着扩展索引,而不是新增索引。通常维护一个多列索引要比维护多个单列索引容易。如果不知道查询的分布,就要尽可能的使索引变得更有选择性,因为高选择性的索引通常更有好处。
在冗余方面:
通常需要额外的索引、冗余的字段,或者甚至缓存表和汇总表来加速读取。这为写入查询和维护工作增加了工作量,但这仍然是设计高性能查询的常见技巧:用极大的加速读取来弥补较慢的写入的代价。(但是也为读写增加了开发复杂性)
InnoDB存储引擎的一些tip:
“
事务性:支持事务和四种事务隔离级别。
外键:在Mysql 5.0中唯一的支持外键的存储引擎。
行级锁:锁设定于行一级,不会向上传递并且也不会阻塞选择——标准选择根本不会设定任何锁,有很好的并发特性。
多版本:InnoDB使用多版本并发控制,默认情况下不会选择读取陈旧数据。
按主键聚集:所有InnoDB表都是按主键聚集的。
所有索引包含主键列:索引按照主键引用行,因此,如果不把主键维持的很短,索引就会很大。
优化的缓存:InnoDB把数据和内存缓存在缓冲区池里。会自动构建哈希索引来加快行读取。
未压缩的索引:索引没有使用前缀压缩,因此可能会比MyISAM表的索引大很多。
数据装载缓慢:在MySql5.0中,InnoDB不会特别优化数据加载。它一次构建一行的索引,而不是按照排序进行构建。导致加载缓慢。
阻塞AUTO_INCREMENT:在MySql5.1之前的版本,InnoDB使用了表级锁来产生每个新的auto_increment值。
没有缓存的count(*)值:和MyISAM表或Memory表不同,InnoDB表不会把表的行数保存在表中,这意味着没有where子句的count(*)查询不会被优化掉,并且需要全表或索引扫描。
”
查询优化:
在下面的应用场景中,在应用程序端进行联接效率更高:可以缓存早期查询的大量数据;使用了多个MyISAM表;数据分布在不同的服务器上;对于大表使用in()替换联接;一个联接引用了同一个表很多次。
查询缓存:Mysql对查询缓存的实现就是将查询文本作为key,将结果集作为value的一个map。因此,查询语句的任何一点变化都会导致缓存不命中,书写一致的查询语句就成了mysql操作的要点。另外就是查询缓存不缓存任何包含不确定函数的查询,比如NOW()。换句话说,用户自定义的函数,变量等等都不会被缓存。(原理是这样的,服务器会执行一次不区分大小写的检查来验证查询是否以SEL打头)。另外值得说明的是,结果太大也不会被缓存的。建立缓存也是有开销的,比如内存分配等,开启了缓存后查询就会检查,这也是性能开销,因此开启与否要根据情况仔细斟酌。