一、选择优化的数据类型
(1)更小的更好
选择不会超过范围的最小类型,因为它占用更小的内存、CPU缓存和处理周期、磁盘。
例如DATETIME和TIMESAMP列都可以存储相同类型数据:时间和日期,精确到秒,但是TIMESAMP只使用DATETIME一半的存储空间。
(2)简单就好
简单类型减少CPU处理周期。例如整形比字符操作代价更低,因为字符集的排序规则比整形复杂
(3)尽量避免NULL
最好指定列为NOT NULL,除非真的需要存储NULL值。如果查询包含NULL的列查询更难优化,因为可为NULL的列使得索引、索引统计、值比较都更复杂,而且可为NULL的列使用更多存储空间。当可为NULL的列被索引时,每个索引记录需要一个额为的字节。
1.1、整数类型
如果存储整数可以用TINYINT、SMALLINT、MEDIUMINT、INT、GIGINT,分别使用16、24、32、64为存储空间,一般整数计算都采用64位的BIGINT整数,MySQL可指定整数宽度例如INT(11)但是没意义的,直接用BIGINT即可。
1.2、实数类型
实数是带小数部分的数字。浮点FLOAT、DOUBLE和DEDCIMAL类型都可以指定精度,因为需要额外的空间和计算开销,所以尽量只在对小数进行精确计算时才使用DECIMAl(例如财务数据)
案例:DECIMAL(18,9):小数点两边各存储9个数字,一共9个字节,小数点前后各4个字节,小数点占一个字节。
1.3、字符串类型
(1)VARCHAR类型用于存储可变长字符串,VARCHAR比定长类型CHAR更节省空间,因为它只使用必要空间(越短字符串使用越少空间)
案例:使用VArCHAR(5)和VArCHAR(200)存储‘hello’的空间开销是一样的,那使用更短的(5)列有什么优势吗?
答:优势很大,更长的列会消耗更多内存,因为MySQL会分配固定大小内存块来保存内部值,通常只分配真正需要空间大小最好。
(2)BLOB和TEXT类型
这俩都是为了鵆很大数据而设计的字符串数据类型,分别采用二进制和字符方式存储。实际中尽量避免使用,性能不好
1.4、日期和时间类型
TIMESAMP只使用DATETIME一半的存储空间,开销更小,更常用。
DATETIME保存更大范围值,从1001年到9999年,把日期和时间封装到YYYYMMDDHHMMSS的整数中,展示时通常以“2019-01-01 22:23:08”这样标准显示;
TIMESAMP保存从1970年1月1日以来的秒数,也是整数存储,保准时间显示。
二、缓存表和汇总表
有时候保存同一张表中衍生的冗余数据也可以提高性能,例如“统计数据”。
缓存表:用于存储那些比较简单地从其他表获取(但是每次获取的速度比较慢)的数据表;
汇总表:保存的是使用GROUP BY语句聚合数据的表
2.1、汇总表
案例:计算前24小时发送的消息数、查询最活跃用户、常见标签,这几种典型的例子。
以计算前24小时发送的消息数作为案例:
(1)可以每小时生成一张汇总表,这样一条简单的查询就可以做到了,比实时维护计数器高效,就是不严格计数;
(2)也可以以汇总表为基础,严格计数,把前23个完整的小时统计表中的计数全部加起来,最后再加上开始阶段和不完整的小时内计数,假设统计叫msg_per_hr。以下来自《高性能MySQL》案例:
不管哪种方法——不严格的计数或者 通过小范围查询填满间隙的严格计数,都比计算message表所有行有效,因为实时计算是很昂贵的操作,因为要扫描表中大部分数据。
2.2、缓存表(中间表)
缓存表和汇总表方式相反,对其优化搜索和检索查询语句很有效,这些查询通常需要特殊表和索引结构。
案例:需要很多不同的索引组合来家属各种类型的查询,这时需要建立一张只包含主表中部分列的缓存表,可以对缓存表中使用不同存储引擎,例如如果主表使用InnoDB缓存表则使用MyISAM作为存储引擎会得到更小的索引占用空间,并且可以做全文搜索。
2.3、计数器表
在表中保存计数器,更新计数器时可能碰到并发问题。计数器的应用比如缓存一个用户的朋友数、文件下载次数等。可创建一张独立表,可避免查询缓存失效
三、加快ALTER TABLE操作的速度
alter table对大表是大问题。修改表时内部一般是用新的结构创建爱你一个空表,从旧表中查出所有数据插入新表,然后删除旧表。
这种操作会花费很多时间,如果内存不足表又大尤其如此。
3.1、只修改.frm文件
但不是所有都会引起重建,列的默认值实际存在表的.frm文件中,所以可以直接修改这个文件而不需要修改表本身,通过ALTER COLUMN操作来改变列的默认值:
3.2、快速创建MyISAM索引
(1)如果表索引是MyISAM,快速更新表有个常用技巧:心啊禁用索引、载入数据、然后重新启用索引(索引会引起查询快,增删慢):
因为构建索引的工作被延迟到数据完全载入以后,这时候可以通过排序来构建索引了,这样做会快很多,并且使得索引树碎片更少、更紧凑(但是对唯一索引无效)。
(2)现在版的InnoDB版本中
先删除所有非唯一索引,然后增加新的列,最后重新创建删除掉的索引。
上一篇:https://blog.csdn.net/RuiKe1400360107/article/details/103727285
下一篇:https://blog.csdn.net/RuiKe1400360107/article/details/103783635
参考资料:《高性能MySQL 第三版》
### 若对你有帮助的话,欢迎点赞!评论!转发!谢谢!