MySQL数据库优化让数据库读取更快

本文的内容是总结一些MySQL的常见使用技巧，以供没有DbA的团队参考。以下内容以MySQL5.5为准，如无特殊说明，存储引擎以InnoDb为准。

MySQL的特点

了解MySQL的特点有助于更好的使用MySQL，MySQL和其它常见数据库大的不同在于存在存储引擎这个概念，存储引擎负责存储和读取数据。不同的存储引擎具有不同的特点，用户可以根据业务的特点选择适合的存储引擎，甚至是开发一个新的引擎。MySQL的逻辑架构大致如下：

MySQL默认的存储引擎是InnoDb，该存储引擎的主要特点是：

• 支持事务处理

• 支持行级锁

• 数据存储在表空间中，表空间由一些列数据文件组成

• 采用MVVC(多版本并发控制)机制实现高并发

• 表基于主键的聚簇索引建立

• 支持热备份

其它常见存储引擎特点概述：

• MyISAM：老版本MySQL的默认引擎，不支持事务和行级锁，开发者可以手动控制表锁；支持全文索引；崩溃后无法安全恢复；支持压缩表，压缩表数据不可修改，但占用空间较少，可以提高查询性能

• Archive：只支持Insert和Select，批量插入很快，通过全表扫描查询数据

• SCV：把一个SCV文件当做一个表处理

• Memory：数据存储在内存中

还有很多，不再一一列举。

数据类型优化

选择数据类型的原则：

• 选择占用空间小的数据类型

• 选择简单的类型

• 避免不必要的可空列

占用空间小的类型更节省硬件资源，如磁盘、内存和CpU。尽量使用简单的类型，如能用int就不用char，因为后者的排序涉及到字符集的选择，比使用int复杂。可空列使用更多的存储空间，如果在可空列上创建索引，MySQL需要额外的字节做记录。创建表时，默认都是可空，容易被开发者忽视，最好是手动改为不可空，如果要存储的数据确实不会有空值的话。

整型类型

整型类型包括：

• tinyint

• smallint

• mediumint

• int

• bigint

它们分别使用8、16、24、32和64位存储数字，它们可以表示范围的数字，前面可以加unsigned修饰，这样可以让正数的可表示范围提高1倍，但是无法表示负数。另外，为整型指定长度没什么卵用，数据类型定下来，长度也就相应定下来了。

小数类型

• float

• double

• decimal

float和double就是通常意义上的float和double，前者使用32位存储数据，后者使用64位存储数据，和整型一样，为它们指定长度没什么卵用。

decimal类型比较复杂，支持精确计算，占用的空间也大，decimal使用每4个字节表示9个数字，如decimal(18,9)表示数字长度是18，其中小数位9个数字，整数部分9个数字，加上小数点本身，共占用9个字节。考虑到decimal占用空间较多，以及精度计算很复杂，数据量大的时候可以考虑用bigint代替之，可以在持久化和读取前对真实数据进行一些缩放操作。

字符串类型

• varchar

• char

• varbinary

• binary

• blob

• text

• 枚举

varchar类型数据实际占用空间等于字符串的长度加上1个或2个用来记录字符串长度的字节（当row-format没有被设置为fixed时），varchar很节省空间。当表中某列字符串类型的数据长度差别较大时适合使用varchar。

char的实际占用空间是固定的，当表中字符串数据的长度相差无几或很短时适合使用chart类型。

与varchar和char对应的有varbinary和binary，后者存储的是二进制字符串，和前者相比，后者大小写敏感，不用考虑编码方式，执行比较操作时更快。

需要注意的是：虽然varchar(5)和varchar(200)在存储“hello”这个字符串时使用相同的存储空间，但并不意味着将varchar的长度设置太大不会影响性能，实际上，MySQL的某些内部计算，比如创建内存临时表时（某些查询会导致MySQL自动创建临时表），会分配固定大小的空间存放数据。

blob使用二进制字符串保存大文本，text使用字符保存大文本，InnoDb会使用专门的外部存储区来存放此类数据，数据行内仅存放指向他们的指针，此类数据不宜创建索引（要创建也只能正对字符串前缀创建），不过也不会有人这么干。

如果某列字符串大量重复且内容有限，可使用枚举代替，MySQL处理枚举时维护了一个“数字-字符串”表，使用枚举可以减少很多存储空间。

时间类型

• year

• date

• time

• datetime

• timestamp

datetime存储范围是1001到9999，精确到秒。timestamp存储1970年1月1日午夜以来的秒数，可以表示到2038年。占用4个字节，是datetime占用空间的一半。timestamp表示的时间和时区有关，另外timestamp列还有个特性，执行insert或update语句时，MySQL会自动更新第一个类型为timestamp的列的数据为当前时间。很多表中都有设计有一列叫做UpdateTime，这个列使用timestamp倒是挺合适的，会自动更新，前提是系统不会使用到2038年。

主键类型的选择

尽可能使用整型，整型占用空间少，还可以设置为自动增长。尤其别使用GUID，MD5等哈希值字符串作为主键，这类字符串随机性很大，由于InnoDb主键默认是聚簇索引列，所以导致数据存储太分散。另外，InnoDb的二级索引列中默认包含主键列，如果主键太长，也会使得二级索引很占空间。

特殊类型的数据

存储Ip最好使用32位无符号整型，MySQL提供了函数inet_aton()和inet_ntoa()进行Ip地址的数字表示和字符串表示之间的转换。

索引优化

InnoDb使用b+树实现索引，举个例子，假设有个people，建表语句如下

CREATE table `people` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(5) NOT NULL,
  `Age` tinyint(4) NOT NULL,
  `Number` char(5) NOT NULL COMMENT '编号',
  pRIMARY KEY (`Id`),
  KEY `i_name_age_number` (`Name`,`Age`,`Number`)
) ENGINE=InnoDb AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

插入数据：

它的索引结构大致是这样的：

也就是说，索引列的顺序很重要，如果两行数据的Name列相同，则用Age列比较大小，如果Age列相同，则用Number列比较大小。先用第一列排序，然后是第二列，最后是第三列。

查询的

本文题目：MySQL数据库优化让数据库读取更快
链接地址：/news43/292093.html

成都网站建设公司_创新互联，为您提供移动网站建设、用户体验、ChatGPT、虚拟主机、域名注册、网站建设

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联