MySQL性能优化策略

最新推荐文章于 2024-07-09 16:29:06 发布

_LittleFish

最新推荐文章于 2024-07-09 16:29:06 发布

阅读量627

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_34414876/article/details/87282916

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

在我认为数据的性能优化基于以下三个层面，

1：表结构优化

2：索引优化

3：查询优化

一、表结构优化

在设计表的时候除了基于业务逻辑的规范进行设计以外，对表的结构，例如字段的规划要有良好的选择。

1：更小的通常更好：

一般情况下，应该尽量使用可以正确存储数据的最小数据类型，通常更快，因为它们占用的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少；

首先我们要知道MySQL的字段类型以及它们各自占用的字节数，以便于做出最优的选择

数值数据类型：

类型	大小	范围（有符号）	范围（无符号 UNSIGNED）
TINYINT	1 字节	[-128,127] 即： [-2^7,2^7-1] (下同) 1个字节8位因为有符号所以符号位占一位所以是2^(8-1) 或者用补码的思想可以	[0,2^8-1]
SMALLINT	2 字节	[-2^15,2^15-1]	[0,2^15-1]
MEDIUMINT	3 字节	[-2^23,2^23-1]	[0,2^24-1]
INT	4 字节	[-2^31,2^31-1]	[0,2^32-1]
BIGINT	8 字节	[-2^63,2^63-1]	[0,2^64-1]
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
DECIMAL	对DECIMAL(M,D) 如果M>D ,M+2 否则，D+2	取决于M N	取决于M N

float , double ,decimal的区别

Float

Float又称之为单精度类型：系统提供4个字节用来存储数据，但是能表示的数据范围比整型大的多，大概是10^38；只能保证大概7个左右的精度（如果数据在7位数以内，那么基本是准确的，但是如果超过7位数，那么就是不准确的）

Double

Double又称之为双精度：系统用8个字节来存储数据，表示的范围更大，10^308次方，但是精度也只有15位左右

Decimal定点数：系统自动根据存储的数据来分配存储空间，每大概9个数就会分配四个字节来进行存储，同时小数和整数部分是分开的。

Decimal

是定点数类型：能够保证数据精确的小数（小数部分可能不精确，超出长度会四舍五入），整数部分一定精确

Decimal(M,D)：M表示总长度，最大值不能超过65，D代表小数部分长度，最长不能超过30。

总结：

float，double等非标准类型，在DB中保存的是近似值，而Decimal则以字符串的形式保存数值。

在日常开发中浮点数大部分是针对金额，在我接触的开发中一般是用分作为单位，用整型数据(int等)进行存储，这样可以最大程度的避免不精确而导致的金额误差，一笔订单几分钱，上万笔就不是一个小数目了！

字符串类型：

类型	大小	用途
CHAR	0~255字节	定长字符串
VARCHAR	0~255字节	变长字符串
TINYBLOB	0~255字节	不超过255个字符的二进制字符串
TINYTEXT	0~255字节	短文本字符串
BLOB	0~65535字节	二进制形式的长文本数据
TEXT	0~65535字节	长文本数据
MEDIUMBLOB	0~16777215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0~16777215字节	中等长度文本数据
LOGNGBLOB	0~4294967295字节	二进制形式的极大文本数据
LONGTEXT	0~4294967295字节	极大文本数据

CHAR 和 VARCHAR的区别

VARCHAR类型用于存储变长字符串，它会删除末尾的所有空格，它比定长字符串更省空间，因为它仅使用必要的空间（越短的字符串占用越少），VARCHAR会用1或2个额外字节记录字符串长度，VARCHAR节省了空间，因此对性能是有帮助的，但是因为行是变长的，所以每次UPDATE时会重新计算字符串长度，会比定长多做额外工作。因此不是说VARCHAR就一定能提升性能，同样要分使用场景，如果字符串的最大长度比平均长度大很多，且列的更新很少时则适合用VARCHAR。注意InnoDB会把过长的VARCHAR存储为BLOB，通常人们喜欢将IP地址存储为VARCHAR型，其实IP地址是32位二进制表示的,你可以转为十进制表示，因而不是字符串，用小数是将它分为四段便于阅读。

CHAR是定长的，每个列无论字符串长度大小都会分配固定存储空间，CHAR适合存储字符很短，或者所有值接近一个长度时，如存储MD5码、SESSION_ID等，对于经常变更的数据CHAR也比VARCHAR好，再一个是对于非常短的列，CHAR比VARCHAR更节省空间，例如使用CHAR(1)和VARCHAR(1)来存储Y或N时，CHAR需要一个字符，而VARCHAR需要两个，因为还有一个长度字节

BLOB和TEXT区别

它们都是为存储大数据而设计的，BLOB是采用二进制/TEXT采用字符存储。MySQL不能将BLOB和TEXT列全部进行索引（只能做前面某长度的索引，因此在查询SQL时不能用LIKE前模糊匹配，那样就走不到索引了），也不能使用这些索引进行排序。

技巧：使用枚举代替字符串类型，枚举可以把一些重复的字符串存储成一个预定义的集合，MySQL在存储枚举时非常紧凑，MySQL在列中保存值为枚举中的位置整数。枚举最不好的是字符串是固定的，添加或删除必须使用ALTER TABLE。因此对于未来会改变的字符串，使用枚举不是一个好主意，除非能接受在枚举末尾添加元素，由于枚举有一个映射转换过程，所以枚举虽然能减少存储空间，但是也会增加一些额外开销

日期和时间类型：

类型	大小（字节）	范围	格式	用途
DATE	3	1000-01-01~9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59~838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00~2037年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

DATETIME和TIMESTAMP的区别

DATETIME：这个类型能保存1001到9999年，精度为秒，与时区无关，使用8个字节存储，存储格式封装为YYYYMMDDHHMMSS的整数，因此它是一种可排序的类型，显示时以ANSI标准定义的日期和时间表示方法显示。

TIMESTAMP：它保存了从1970年1月1日午夜以来的秒数，也就是常说的时间戳，它使用4个字节来存储，依赖于时区。除了特殊情况外，通常我们应该尽量使用TIMESTAMP，因为它比DATETIME更省空间，现实中有些人喜欢使用int来存储时间戳，这并不会带来任何收益，用整数保存时间戳不方便，因为取出来之后还需要程序转换下，所以不推荐这样做

2：简单就好

简单的数据类型操作通常需要更少的CPU周期。

例如，整型比字符操作代价更小，因为字符集和校对规则（排序规则）使它比整型更复杂。

此外MySQL中有时间的数值类型，就不需要用时间戳的形式进行存储（除非在特定的业务内）

3：尽量避免null

null列使得索引统计和值比较都更复杂，null列会使用更多的存储空间，在MySQL里也需要特殊处理，当索引列是NULL，每个索引记录需要一个额外的字符。

例如：定义一个数据id为INT型，由上图我们可以知道INT型的存储空间为4个字节，如果设置为not null 存储空间依旧为4个字节，如果设置为null存储空间就会变成4+1即5个字节

如果索引的字段是null的，索引的存储空间也会+1个字节

二、索引优化

在MySQL中，索引是在存储引擎层而不是服务器层实现的。

B-tree索引

MyISAM使用前缀压缩技术使得索引更小，但是InnoDB则按照原数据格式进行存储。

MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行

hash索引

对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同的键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

例如 select name from test where name='Tom';

先计算'TOM'的哈希值,使用该值在索引中找到对应的指针取出数值与之比较。

InnoDB支持哈希索引是自适应的，会根据表的使用情况去自动生成哈希索引，但是不支持人为地去生成哈希索引

在MySQL中只有MEMORY引擎显式支持哈希索引

hash索引的限制：

1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

具体索引优化可以参考https://blog.csdn.net/u011504963/article/details/79727849