Mysql调优

Mysql调优
第一步，从初期的一个需求规划，也就是对表的设计就开始了，先从底层开始说吧，mysql底层的页的大小是16kb，假如，我有一张表对单行数据量就有16kb，那么这张表就只能存储1条数据了，这是非常恐怖的。mysql是从磁盘读取数据到内存的，是以磁盘块为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，不是按需读取。以InnoDB存储引擎来说，它使用页作为数据读取单位，页是其磁盘管理的最小单位，默认大小是16kb。系统的一个磁盘块的存储空间往往没有这么大，所以InnoDB每次申请磁盘空间时都会是多个地址连续磁盘块来达到页的大小16KB。在查询数据时一个页中的每条数据都能定位数据记录的位置，这会减少磁盘 I/O 的次数，提高查询效率。InnoDB存储引擎在设计时是将根节点常驻内存的，力求达到树的深度不超过 3，也就是说I/O不超过3次。树形结构的数据可以让系统高效的找到数据所在的磁盘块，这里就可以说一下这个b树和b+树了，B树的结构是每个节点中有key也有value，而每一个页的存储空间是16kb，如果数据较大时将会导致一页能存储数据量的数量很小。B+Tree的结构是将所有数据记录节点按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的key 值数量，降低B+Tree的高度。所以通过mysql的底层存储的原理和数据结构，我们在设计表的时候，尽量减少单行数据的大小，字段的宽度设得尽可能小，尽可能不用text、Blob、Clob等类型，它会增加存储空间的占用，读取速度较慢。能用数字型字段就不要设计为字符型，因为字符型锁占的存储空间更大，比如，性别这个字段不用男女进行存储，改为0/1的方式，这样不仅可以控制数据量的大小，增加了同一高度下的B+树容纳的数据量，还能提高检索速度。尽量使用varchar/nvarchar代替char/nchar，因为变长字段空间小，可以节省存储空间。不在数据库中存储图片、文件等大数据，可以通过第三方云存储，存放图片或者文件地址。金额用decimal，注意长度和精度。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小树分开存储。尽可能不要给数据库留null值，尤其是时间、整数等类型，可以在建表的时候就给非空设置，NULL的列会使用更多的存储空间，在Mysql中也需要特殊处理，为NULL的列会使索引统计和值比较都更复杂，当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

第二步，就是建索引，当对表中的数据进⾏增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度，所以不是所有字段都适合创建索引的，比如要频繁更改数据不建议使⽤索引，又或者对于关于区分度不高的字段，比如性别，状态字段，就不适合创建索引，只有几种取值的字段，建了索引，数据库也不一定会用，只会白白增加索引维护的额外开销，因为索引也是需要存储的，所以插入和更新的写入操作，需要插入和更新这个字段的索引的。一般需要查询排序，分组和联合操作的字段适合建⽴索引，另外索引多，数据更新表越慢，所以尽量使⽤字段值不重复的字段创建索引。