MySQL性能调优

一、范式

        范式就是数据表设计的结构所符合的某种彼标准级别。

1、第一范式

        第一范式强调数据表的原子性，所有属性都不可再分，单一属性的列的数据结构为基本数据类型构成。

2、第二范式

        满足第一范式的条件下，实体的属性完全依赖于主关键字。

3、第三范式

        要求一个数据库表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

4、反范式化设计

        为了性能和读取效率而适当的违反对数据库设计范式的要求，对于查询操作可以更少的表关联，以空间换时间。

二、字段数据类型

整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间，也就是1、2、3、4、8个字节。

实数类型：DECIMAL、FLOAT、DOUBLE

字符串类型：VARCHAR、CHAR、BLOB、TEXT、ENUM

日期时间类型：datetime 存储日期范围：1001年~9999年、timestamp 存储日期范围：1970年~2038年,并且跟时区有关系

        整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂，所以当可以使用整型和字符串的时候优先使用整型，使用MySQL自有的类型存储时间而不是字符串。

        如果不是精确计算推荐使用float和double，占用更少的空间，decimal类型用于存储精确的小数，本质上MySQL是以字符串形式存放的，所以CPU不支持对decimal的直接计算，而float和double浮点型运算会更快。也可以把小数乘以10的倍数，用bigint，来进行精确计算，避免使用decimal

        VARCHAR类型用于存储可变长字符串，它比定长类型更节省空间，越短越节省空间。CHAR类型是定长的，MySQL根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格，CHAR值会根据需要采用空格进行填充以方便比较。

        BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

三、索引

        索引（Index）是帮助MySQL高效获取数据的数据结构。

3.1、结构

1、二叉树

所有节点的子树，最多为2，是二叉树。有左右之分。

两个不同的二叉树：

2、二叉查找(搜索)树

• 左子树的所有的值小于根节点的值

• 右子树的所有的值大于或等于根节点的值

• 左、右子树满足以上两点

这是一个不平衡的二叉查找树

3、平衡二叉树（AVL-树）

        它的左右两个子树的高度差（平衡因子）的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。使得树的高度最低，因为树查找的效率决定于树的高度。

4、B+树

B+ 树是从平衡二叉查找树演化而来，是一个多叉查找平衡树

可见

1、相同节点数量的情况下，B+树高度远低于平衡二叉树；

2、非叶子节点只保存索引信息和下一层节点的指针信息，不保存实际数据记录；

3、每个叶子页（LeafPage）存储了实际的数据，叶子节点由小到大（有序）串联在一起，叶子页中的数据也是排好序的；

4、相邻的叶子节点之间用指针相连。

5、MySQL与B+树

        为了提高效率，要尽量减少磁盘I/O的次数，为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页，预读的长度为页的整数倍。如果是一个页存放一个B+树的节点，可以存放很多的数据。

3.2、B+树索引

1、聚集索引

        InnoDB中使用了聚集索引，将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。

2、二级索引

        聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。需要使用其它列时就会建立二级索引，每个索引都是一个B+树。

        叶子节点并不包含行记录的全部数据，每个叶子节点中的索引行中包含了一个书签( bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

回表 

        当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。

回表的记录越多，效率越低。

        如果把完整的用户记录放到叶子节点是可以不用回表，但是太占空间，相当于每建立一棵B+树都需要把所有的记录再都拷贝一遍。而且每次对数据的变化要在所有包含数据的索引中全部都修改一次，性能也非常低下。

3、复合索引

        将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引。

index(a,b)在索引构建上，包含了两个意思：

1、先把各个记录按照a列进行排序。

2、在记录的a列相同的情况下，采用b列进行排序。

注意

1、 一个索引就是一个B+树，索引让查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度 。

2、一个select查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引。

3.3、索引优化

        索引的数据类型也是要尽量小，能用int就不用bigint，可以节省空间，cpu比较操作也快。

        索引应选择重复性较少的列，比如性别会有很多重复，不适合作索引。

        SELECT count(DISTINCT name)/count(*) FROM user;      越大越好

         索引的顺序，一般将索引效率高的放在前面。

三星索引

满足的条件如下：

• 索引将相关的记录放到一起则获得一星 （比重27%）

• 如果索引中的数据顺序和查找中存在的排列顺序一致则获得二星（排序星） （比重27%）

• 如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星） （比重50%）

3.4、索引使用策略

1、不在索引列上做任何操作

EXPLAIN SELECT * FROM pms_attr WHERE attr_id + 1 = 5;

索引失效了 

EXPLAIN SELECT * FROM pms_attr WHERE attr_id = 4;

2、尽量全值匹配

建立了联合索引列后，如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配。

3、范围条件放最后

针对联合索引，有范围查询会导致后面的列全部失效，无法充分利用这个联合索引。

4、不等于

不等于(!= 或者<>)无法使用索引会导致全表扫描。

5、null/not null

        is not null容易导致索引失效，is null则会区分被检索的列是否为null，如果是null则会走ref类型的索引访问，如果不为null，也是全表扫描。

6、like

like以通配符开头('%abc...')，mysql索引失效会变成全表扫描的操作。

7、按主键顺序插入行

最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

8、优化limit分页

在偏移量非常大的时候，可以采用

select * from user where id > 67 order by id limit 10;

每次查询只需要使用上次查询出的数据中的id来获取接下来的数据即可。