最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

MySQL优化总结

XAMPP新闻 中文小张 108浏览 0评论

1.存储引擎的选择(MyISAM和Innodb)

存储引擎:MySQL中的数据、索引以及其他对象是如何存储的,是一套文件系统的实现。

5.1之前默认存储引擎是MyISAM,5.1之后默认存储引擎是Innodb。

功能差异
区别 MyISAM Innodb
文件格式 数据和索引是分别存储的,数据.MYD,索引.MYI 数据和索引是集中存储的,.ibd
文件能否移动 能,一张表就对应.frm、MYD、MYI3个文件 否,因为关联的还有data下的其它文件
记录存储顺序 按记录插入顺序保存 按主键大小有序插入
空间碎片(删除记录并flush table 表名之后,表文件大小不变) 产生。定时整理:使用命令optimize table 表名实现 不产生
事务 不支持 支持
外键 不支持 支持
锁支持 表级锁定 行级锁定、表级锁定,锁定力度小并发能力高
选择依据

MyISAM引擎设计简单,数据以紧密格式存储,所以某些读取场景下性能很好。

如果没有特别的需求,使用默认的Innodb即可。

MyISAM:以读写插入为主的应用程序,比如博客系统、新闻门户网站。

Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高,支持事务和外键保证数据完整性。比如OA自动化办公系统。

官网建议

官方建议使用Innodb,上面只是告诉大家,数据引擎是可以选择,不过大多数情况还是不要选为妙

2.字段设计

数据库设计3大范式
  • 第一范式(确保每列保持原子性)
  • 第二范式(确保表中的每列都和主键相关)
  • 第三范式(确保每列都和主键列直接相关,而不是间接相关)

通常建议使用范式化设计,因为范式化通常会使得执行操作更快。但这并不是绝对的,范式化也是有缺点的,通常需要关联查询,不仅代价昂贵,也可能使一些索引策略无效。

所以,我们有时需要混同范式化和反范式化,比如一个更新频率低的字段可以冗余在表中,避免关联查询

单表字段不宜过多

建议最多30个以内
字段越多,会导致性能下降,并且增加开发难度(一眼望不尽的字段,我们这些开发仔会顿时傻掉的)

使用小而简单的合适数据类型

a.字符串类型

固定长度使用char,非定长使用varchar,并分配合适且足够的空间

char在查询时,会把末尾的空格去掉;

b.小数类型

一般情况可以使用float或double,占用空间小,但存储可能会损失精度

decimal可存储精确小数,存储财务数据或经度要求高时使用decimal

c.时间日期

datetime:

  • 范围:1001年~9999年
  • 存储:8个字节存储,以YYYYMMDDHHMMSS的格式存储
  • 时区:与时区无关

timestamp:

  • 范围:1970年~2038年
  • 存储:4个字节存储,存储以UTC格式保存,与UNIX时间戳相同
  • 时区:存储时对当前的时区进行转换,检索时再转换回当前的时区

1.通常尽量使用timestamp,因为它占用空间小,并且会自动进行时区转换,无需关心地区时差

2.datetime和timestamp只能存储最小颗粒度是秒,可以使用BIGINT类型存储微秒级别的时间戳

d.大数据 blob和text
blob和text是为存储很大的数据的而设计的字符串数据类型,但通常建议避免使用

MySQL会把每个blob和text当做独立的对象处理,存储引擎存储时会做特殊处理,当值太大,innoDB使用专门的外部存储区域进行存储,行内存储指针,然后在外部存储实际的值。这些都会导致严重的性能开销

尽量将列设置为NOT NULL

a.可为NULL的列占用更多的存储空间
b.可为NULL的列,在使用索引和值比较时,mySQL需要做特殊的处理,损耗一定的性能

建议:通常最好指定列为NOT NULL,除非真的需要存储NULL值

尽量使用整型做主键

a.整数类型通常是标识列最好的选择,因为它们很快并且可以使用AUTO_INCREMENT

b.应该避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢

c.对于完全”随机”的字符串也需要多加注意。例如:MD5(),SHAI()或者UUID()产生的字符串。这些函数生成的新值也任意分布在很大空间内,这会导致INSERT和一些SELECT语句很缓慢

索引

使用索引为什么快
  • 索引相对于数据本身,数据量小
  • 索引是有序的,可以快速确定数据位置
  • InnoDB的表示索引组织表,表数据的分布按照主键排序

就好比书的目录,想要找到某一个内容,直接看目录便可找到对应的页

索引的存储结构

a.B+树(具体的结构就不说了,自己去了解)
b.哈希(键值对的结构)

MySQL中的主键索引用的是B+树结构,非主键索引可以选择B+树或者哈希

通常建议使用B+树索引
因为哈希索引缺点比较多:
1.无法用于排序
2.无法用于范围查询
3.数据量大时,可能会出现大量哈希碰撞,导致效率低下

索引的类型

按作用分类:

1.主键索引:不解释,都知道

2.普通索引:没有特殊限制,允许重复的值

3.唯一索引:不允许有重复的值,速度比普通索引略快

4.全文索引:用作全文搜索匹配,但基本用不上,只能索引英文单词,而且操作代价很大

按数据存储结构分类:

1.聚簇索引

定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。

主键索引是聚簇索引,数据的存储顺序是和主键的顺序相同的

2.非聚簇索引

定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。

聚簇索引以外的索引都是非聚集索引,细分为普通索引、唯一索引、全文索引,它们也被称为二级索引。

转载请注明:XAMPP中文组官网 » MySQL优化总结