mysql学习笔记(3)-其他性能优化方法

1. 索引管理优化

1)整合DDL语句

在将索引添加到MySQL表的过程中，一个需要注意的管理问题就是DDL语句是阻塞的。把多条ALTER语句整合成一条SQL语句是一种简单的优化改进。例如，如果需要添加一个新的索引、修改一个索引以及添加新的一列，可以把在同一张表上运行的语句整合成为一条SQL：

ALTER TABLE test

ADD INDEX (username)，

DROP INDEX name，

ADD INDEX name (last_name，first_name)，

ADD COLUMN last_visit DATE NULL；

2）去除重复索引

重复的索引主要有两个影响：

1.所有DML语句都会运行的更慢，应为要做额外的工作来保持数据和索引的一致性

2.数据库的磁盘占用量将会更大

一些简单的情况会导致重复索引。例如：

1. MySQL并不要求主键列也被索引。

2.当一个给定索引最左边的部分被包含在其他索引中时也会产生重复索引，例如：INDEX name1 (last_name)和INDEX name2 (last_name, first_name)

3)删除不用的索引

除了重复索引没有被使用到之外，还有其他索引可能没有被用到。这些索引一样会影响性能。

2.索引列的改进

1）数据类型

1. BIGINT和INT

在AUTO_INCREMENT列上把数据类型自从BIGINT改成INT UNSIGNED是最有效的模式改进方法之一。这样做不仅针对主键的容量减小，所有定义为BIGINT的外检都可以改成INT，节省下来的空间能够显著地减少在高度规范化的数据库中索引需要的存储空间。

2. DATETIME和TIMESTAMP

如果存储的日期或者时间可以是一个纪元值（1970年1月1日到现在的秒数），那么TIMESTAMP列支持所有必须的值。一个DATETIME数据类型占8字节，而TIMESTAMP占用4字节。缺点是TIMESTAMP的默认值为0，且不支持NULL值。这个列类型适合存储总是有值存在的时间或日期。

3.ENUM

使用ENUM有三个优点

・一个ENUM列用隐含的检查限制提供了额外的数据完整性支持

・仅用1字节存储255个不同的值

・更具有可读性，每一列为自身提供有意义的说明

4. NULL 和 NOT NULL

除非确定一个列能包含一个未知的值，否则最好定义成NOT NULL。当列被定义在一个索引中时，那么此列可以占用更小的空间，并简化索引的处理过程，因为不需要其他NULL条件。

5. 隐含的变换

当为表连接选择一个索引数据类型时， 一定要确保数据类型相同。隐含的类型转换将带来不必要的开销。对于整数类型的列要确保SIGNED和UNSIGNED类型时统一的。

2) 列的类型

1. IP地址

一个IPv4地址可以被定义成INT UNSIGNED数据类型，只占用4字节。通常情况下定义为VARCHAR（15）则平均占用12字节。利用INET_ATON()和INET_NTOA()函数可以管理IP地址再字符串和数字值之间的转换。

这个技术只适用于IPv4。随着IPv6广泛采用，一定要将128比特的整数以BINARY（16）的类型存储，而不是VARCHAR。

2.MD5

用CHAR（32）来存储MD5是一个常见的技巧。如果用VARCHAR（32）则对每个值的长度都需要花费额外不必要的开销。这个16进制的值可以使用UNHEX()和HEX()函数来存储在BINARY(16)数据类型中更为高效。可以让每行占用的存储空间从32字节减少到16字节。