MySQL实战篇:建立高性能的MySQL技巧数据库

来源:互联网 / 作者:SKY / 2018-05-08 20:07 / 点击:
MySQL是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。由于其体积

MySQL是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。

由于其社区版的性能卓越,搭配 PHP 和 Apache 可组成良好的开发环境。

常用技巧 

MySql实战篇:建立高性能的Mysql技巧

优化的数据类型

优先确认数据类型

在为列选择数据类型时,第一步需要确定合适的大类型:数字,字符串,时间等。下一步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据,只是存储的长度和范围不一样,允许的精度不一样,或者需要的物理空间(磁盘和内存空间)不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。

更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更少的磁盘,内存和CPU缓存,并且处理时需要的CPU周期也更少。但是要确保没有低估需要存储的值得范围。

简单就好

简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比整型比较更复杂。

尽量避免使用NULL

很多表都包含可为NULL(空值)的列,即使应用程序并不需要保存NULL也是如此,这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引,索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理,当可为NULL的列被索引时,每个索引记录需要一个额外的字节。如果计划在列上建索引,就应该避免设计成可为NULL的列。

备注:例如:DATETIME和TIMESTAMP列都可以存储相同类型的数据:时间和日期,精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间,并且会根据时区变化,具有特殊的自动更新能力。另一方面,TIMESTAMP允许的时间范围要小很多,有时候它的特殊能力会成为障碍。

遵循数据库设计的三大范式 

第一范式

确保每列的原子性(强调的是列的原子性,即列不能够再分成其他几列). 如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式. 例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。

第二范式

在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关(一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的部分) 如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式. 例如:订单表(订单编号、产品编号、定购日期、价格、……),"订单编号"为主键,"产品编号"和主键列没有直接的关系,即"产品编号"列不依赖于主键列,应删除该列。

第三范式

在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键,不能存在传递依赖). 如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式. 为了理解第三范式,需要根据Armstrong公里之一定义传递依赖。假设A、B和C是关系R的三个属性,如果A-〉B且B-〉C,则从这些函数依赖中,可以得出A-〉C,如上所述,依赖A-〉C是传递依赖。 例如:订单表(订单编号,定购日期,用户编号,用户姓名,……),初看该表没有问题,满足第二范式,每列都和主键列"订单编号"相关,再细看你会发现"用户姓名"和"用户编号"相关,"用户编号"和"订单编号"又相关,最后经过传递依赖,"用户姓名"也和"订单编号"相关。为了满足第三范式,应去掉"用户姓名"列,放入用户表中。

总结

范式优点:

(1) 范式化的更新操作通常比反范式化要快(2)当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据(3)范式化的表通常更小,占用更小的内存,所以处理速度更快(4)很少有多余的数据,意味着检索列表时更少需要distinct和group by语句时间

范式缺点:

符合范式的schema设计,查询时通常需要关联查询

schema设计简单原则

尽量避免过度设计,例如会导致极其复杂查询的schema设计,或者有很多列的表设计; 

使用小而简单的合适数据类型,除非真是数据模型中有确切的需要,否则应该尽可能地避免使用NULL值 

尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中使用的列;

注意可变长字符串,其在临时表或者排序时可能悲观的按最大长度分配内存 

尽量使用整型标识列 

避免使用mysql已经遗弃的特性,例如指定浮点数的精度(可用decimal代替),或者整数的显示宽度 

小心使用ENUM和SET,尽量避免使用;避免使用BIT;

创建高性能索引

高性能的索引策略

独立的列 我们通常会看到一些查询不当地使用索引,或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的,则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分,也不能是函数的参数。

前缀索引和索引的选择性 有时候需要索引很长的字符列,这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率,但是也会降低索引的选择性。(索引选择性是指不重复的索引值和数据表的记录总数的比值)索引的选择性越高则查询效率越高。

多列索引 一个常见的错误是:为每个列创建独立的索引,或者按照错误的顺序创建索引。但实际上,在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。5.0和之后的版本引入“索引合并”的策略,一定程度上缓解了这个问题。(但没有彻底解决)

索引合并策略有时候是一种优化的结果,但实际上更多时候则说明了表上的索引很糟糕

当出现服务器对多个索引做相交操作的时候,意味着需要一个包含所有相关列的多列索引而不是多个独立的单列索引

阅读延展

1
3