MySQL优化细节分析

一、数据类型优化

1. 为什么要尽量避免NULL？

因为对于NULL列，MySQL更难优化，因为NULL使得索引，索引统计和值都更加复杂

• 可为null的列会使用更多的存储空间
• 当null列被索引时，每一个索引记录需要1个额外的字节，在MyISAM可能还会导致固定大小的索引变为可变大小的索引

2. 整数类型指定长度

int（10），大多数情况下是没有意义的，对于存储和计算来说，int(1)和int(10)是相同的

3. 实数类型

mysql中实数类型分为float，double和decimal，如何选择呢？

float:浮点型，含字节数为4，32bit，数值范围为-3.4E38~3.4E38（7个有效位）
double:双精度实型，含字节数为8，64bit数值范围-1.7E308~1.7E308（15个有效位）
decimal:数字型，128bit，不存在精度损失，常用于银行帐目计算。（28个有效位）

float，double:

他们是不准确的，意思是，存储和计算可能会丢失精度，丢失精度的原因：
MySQL官方文档有一句话：数据准确度取决于分配给数据类型存储的长度
由于计算机是以二进制存储的，所以浮点数也是转化为二进制存储的，例如，将131072.32转成二进制后的数据为：
100000000000000000.0101000111101011100001010001111010111000010100011111…
假如使用float类型存储，float只能存储32位，所以32位之后的全部截断，因此精度肯定会丢失
假如使用double类型，double只能存储64位，也肯定会丢失精度

注意：有一个误区，对于float(M,D)，丢失精度与D值有关
这是不准确的，我们知道数据准确度取决于分配给数据类型存储的长度，所以假如M位很大，整数转化为二进制都超出了最大位数，连整数都会丢失精度，何况小数位？

decimal

解决丢失精度的问题，decimal就可以准确的存储，它的解决之道也就是增大存储空间，并且decimal是通过字符串形式来存储，计算时会转换为double进行计算，所以使用decimal，会产生更大的空间消耗和计算开销

还有一种解决办法：假设我们要存储财务数据精确到万分之一的话，可以把所有金额乘以一百万，然后结果存储到bigint中，这样可以同时避免浮点数计算不精确和decimal精确计算代价高的问题！

更多：http://yongxiong.leanote.com/post/mysql_float_double_decimal

4. varchar和char

区别：

1. 空间和时间上
   由于可以变长，所以更节省空间，但是由于存储更加复杂，所以速度没有char快，但是varchar会额外多1-2个字节来记录字符串长度，>255 用2个，<=255用1个
2. 从碎片角度上
   使用CHAR字符型时，由于存储空间都是一次性分配的。为此某个字段的内容，其都是存储在一起的。单从这个角度来讲，其不存在碎片的困扰。而可变长度的字符数据类型，其存储的长度是可变的。当其更改前后数据长度不一致时，就不可避免的会出现碎片的问题。故使用可变长度的字符型数据时，数据库管理员要时不时的对碎片进行整理。如执行数据库导出导入作业，来消除碎片。
3. 空格角度上
   char会对检索的值进行trim()处理，取出末尾的空格，而varchar会保留空格

如何选择

1. 假设字段的值的长度比较平均，使用char
2. 字段的长度比较小时，使用char，因为varchar还要额外的字节记录长度
3. 列的更新很少时，使用varchar，因为碎片不是问题了

注意

varchar（100）和varchar（10）是否相同？
在硬盘存储来说，都一样，但是对于内存来说，varchar（100）会分配100的内存，所以内存消耗更大，所以不要以为分配多页无所谓！

更多：http://blog.csdn.net/imzoer/article/details/8435540

5. DATETIME 和 TIMESTAMP

DATETIME可以存储大范围的值，从1001年到9999年，精度为秒，使用8个字节
TIMESTAMP只能存储1970-1-1到2038年，使用4个字节

建议：一般使用TIMESTAMP，因为更节省空间

6. Alter table的问题

MySQL执行Alter table修改表结构的方法，是用新的结构创建一个表，从旧的表查出所有数据插入新表，然后删除新表。
这样的问题，数据量大的时候，非常慢！可能造成服务中断

有两种解决方法：
1. 使用备份表，在备份表中进行Alter table操作，然后进行主备切换。
2. 影子拷贝

那么有没有什么方式来改善Alter table呢？
表的列的默认值存在表的.frm文件中，所以我们可以通过修改.frm文件，这样就可以避免表重建了。

例如：

alter table test modify column

这个会引起表重建

alter table test alter column

这个会直接修改.frm文件，而不涉及表数据，所以很快

二、索引优化

1. 索引的分类

按数据结构分

B-tree索引，哈希索引、空间数据索引、全文索引等

按特点分

主键索此 ( PRIMARY )
唯一索引 ( UNIQUE )
普通索引 ( INDEX )
全文索引（FULLTEXT , MYISAM 及 MySQL 5.6 以上的 Innodb ）

2. B-Tree索引，哈希索引的特点

B-Tree索引：

InnoDb和MyISAM不同，具体可以看MySQL索引背后的数据结构及算法原理
它是有序的，所以可以用于排序，也适合范围查找，例如查找所所有以I到K开头的名字

哈希索引

只有Memory索引支持哈希索引，哈希索引是一次性定位，并且Memory引擎支持非唯一哈希索引，当多个列的哈希值相同时，索引将会以拉链法解决冲突
哈希索引只支持等值比较查询，例如=、in、<=>

对于InnoDB，它有一个特殊的功能叫“自适应哈希索引”，当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引中再创建一个哈希索引，这样可以让B-Tree也有哈希索引的一下优点例如，哈希查找，这样的速度很快！

注意
B-Tree索引和哈希索引的索引匹配是重点，看书理解

3. 当需要索引很长的字符列时

当需要索引很长的字符列时，索引会变得很大很慢，可以怎么解决？
有两种方法：

自定义哈希索引

例如有下面这样的查询：

select * from url where url = "http://www.baidu.com";

如果在url列中建索引，这样索引存储的内容会很大，导致性能会比较低，可以这样解决：
删除原来的url索引，新增一个列url_crc，使用CRC32函数做哈希值，并在该列中新建一个索引，于是原来的查询可以变成这样：

select * from url where url = "http://www.baidu.com" and             url_crc = CRC32("http://www.baidu.com");

这样，索引的值为哈希值，存储在b-tree中，这样的情况下，索引查找的性能会提高很多！
缺点是要维护哈希列

建立前缀索引

前缀索引建立的诀窍是，要选择足够长的前缀，以保证较高的索引选择性，同时又不能太长。
假设我们分析到该列，前缀为7的时候最理想，那么建立语句是：

create index ix_test on table(column(7)); 

4. 覆盖索引

如果一个索引包含了所有查询的字段的值，那么就称该索引为“覆盖索引”，例如有一个索引为：ix_name_sex_age

select name, sex, age from user;select name, sex from user where age...;

对于上面查询来说，ix_name_sex_age是一个覆盖索引，使用explain，看到Extra列出现 Using Index，则说明是覆盖索引

具体的原理：MySQL之高效覆盖索引

5. 使用索引扫描来做排序

http://blog.csdn.net/u013298300/article/details/52415751

6. 索引什么时候会生效？

1. 全值匹配，例如（a,b,c），where b = ? and a = ? and c = ?
2. 使用了最左列，例如（a,b,c），where a= ? and c = ?
3. 匹配某一列的开头部分（模糊查询），where b like ‘J %’
4. 精确查询某一列并模糊查询另一列开头，where a = ? and b like ‘J %’

7. 索引什么时候会失效？

1. 聚集函数和like一起使用，不能查询到任何记录
2. like什么时候，索引会失效？
   like ‘l%’ 时生效
   like ‘%l’ 时失效
   即（当%在前面时会失效）
3. 使用or,会失效
4. 使用组合索引的时候，没有使用到最左列会失效
   例如（A,B,C），使用（B，C）或者单独使用B或C，都不会生效的
5. 随着表的增长，where条件出来的数据太多，大于15%，使得索引失效
6. 查询的数量是大表的大部分，应该是30％以上，会失效
7. where列使用函数，函数内使用的字段是索引，会失效
   例如id是索引列，where round(id) = 1会失效
   解决方法：建立函数索引
   create index index1 on table(round(id))
8. 隐式转换导致索引失效
   例如字段score是int列的，而where中，使用 ‘100’ 来查询，这时索引是不生效的（在oracle失效，在MySQL仍然可以生效）
9. 对索引列进行计算，索引失效
   select * from people where score - 1 = 90，这时score会失效

三、InnoDB

1. InnoDB的主键问题

（1）InnoDB表是否一定要有主键？

因为InnoDB的数据文件本身要按主键来走聚集索引，所以InnoDB要求表必须有主键（MyISAM可以没有）；
如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)

（2）为什么最好要用自增的列作为主键？

InnoDB中的索引是B+ Tree树的形式，数据文件会通过主键来走聚集索引（聚集索引就是主键索引），这样的特点是：
1. 所有数据记录都会在B+Tree中的叶子节点中
2. 叶子节点中的数据记录是有序的

因此，使用了自增的列作为主键后：
当每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页（MySQL使用磁盘的磁盘预读原理，将一个叶子节点的大小设置为磁盘的一个页大小，这样每次检索一个节点，就只需要一次的IO操作）

如果使用非自增的列作为主键后：
由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

2. InnoDB解决幻读的问题

InnoDB是通过MVCC机制来解决幻读的问题，简单来说就是，通过gap锁来锁住记录行及其上下区间。
但是有一个问题：select * from table
这个sql是，快照读，不加锁，是如何保证在一个事务中，多次执行返回的结果是一样的？

个人理解：
不存在gap间隙锁的影响，保证可重复读并且不出现幻读的是通过MVCC中的每条记录中的创建版本号和删除版本号来实现的！

SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。INSERT时，保存当前事务版本号为行的创建版本号DELETE时，保存当前事务版本号为行的删除版本号UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

3. Two-Phase Locking(二阶段锁)

二阶段锁 ： 锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下面，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。

但是有一点，在RR级别上：
delete from table where id = 10
当id列没有索引时，此时会走聚集索引，并且会将所有记录和记录之间的gap都加上锁，如果按照二阶段锁的思想，如果一个事务执行此语句，将所有记录都锁住，并且不commit，岂不是整张表都锁死了？
MySQL对这个进行了优化：semi-consistent read参数
semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁