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
  • 1

这个会引起表重建

alter table test alter column
  • 1

这个会直接修改.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";
  • 1

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

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

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

建立前缀索引

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

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

4. 覆盖索引

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

select name, sex, age from user;
select name, sex from user where age...;
  • 1
  • 2

对于上面查询来说,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时,插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行
  • 1
  • 2
  • 3
  • 4

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会提前放锁

阅读更多
个人分类: Mysql
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭