高性能mysql(第二版)学习笔记之架构优化和索引

发布时间:2019-12-11编辑:脚本学堂
本文介绍下,高性能mysql(第二版)的学习笔记,本节学习架构优化与索引。有需要的朋友参考下。

先来看下,mysql中的字段类型。

1,tinyint,smallint,mediuint,int,bigint,分别需要1,2,3,4,8个字节,他们的存储范围为-2(n-1)方~2(n-1)方-1,这里的N是所需存储空间的位数
整数类型可以选择unsigned,表示不允许出现负数,大致把正上限提高一倍。unsigned tinyint 保存的范围为0~255

2,varchar使用额外的1到2字节来存储键的长度,如果列的长度小于或等于255,则使用1字节,否则使用2字节.varchar(10),括号中的10表示的是10个字符,具体占用的字节数要根据字符类型确定。

3.blob和text分别以二进制和字符的形式保存大量数据,他们的唯一区别是blob保存的是二进制数据,没有字符集和排序规则,但是text有字符集和排序规则

他们不会按照字符串的完整长度进行排序,而只是按照max_sort_length规定前若干个字节排序,如果只按开始的几个字符排序,就可以减少max_soft_length的值或使用order by substring(column,length)

4.datetime所占8个字节保存的范围为1001~9999

timestamp所占4个字节保存的范围为自1970-1-1开始以来的时间戳1970~2038,提供的转换函数有from_unixtime()和unix_timestamp(),timestamp默认是not null

5.mysql把bit当成字符串类型,而不是数字类型。然而,如果以数字的方式取出来,结果就是该二进制字符串转换出来的数字。小心使用,最后能避免使用该类型

6.如果保存UUID值,就应该移除其中的短横线,更好的办法是使用UHEX()把UUID值转化为16字节的数字,并把它保存在BINARY(16)列中。具体操作方式见mysql存储UUID文章

7.索引类型

b-tree又叫做平衡多路查找树

b-tree就是btree,b-tree和b+tree的区别

b树的键只出现一次,可能在叶节点,也有可能出现在非叶节点,而b+树所有的键值一定会在叶节点中出现,并且在非叶节点中也可能出现,并且叶子节点的数据从大到小顺利排列

b+树叶节点有双向链接,而b树没有

B树的查询效率与键在树中的位置有关,最大时间复杂度与B+树相同(在叶结点的时候),最小时间复杂度为1(在根结点的时候)。而B+树的时间复杂度对某建成的树是固定的

哈希索引

在mysql中只有memory存储引擎支持显式的哈希索引,是默认的存储引擎。

哈希值的长度不会依赖于索引的列,tinyint列的哈希索引和大型字符列的哈希索引大小一样,所以查询速度很快

哈希索引的局限性

a.不能按哈希索引排序,不支持部分键值匹配

b.只支持=,in,<=>等相等比较,对范围查询没有作用

c.有数据碰撞

innodb存储引擎有自适应哈希索引功能,自动控制,不需要配置

空间索引(r-tree)

全文索引:myisam引擎特有 ft_min_word_len = 1 这个参数可以配置全文索引字符长度,默认是4

8.高性能索引策略

索引的选择性就是不重复的索引值和表中所有行的比值。对应blob,text,和很长的varchar列,就必须定义前缀索引。

如果定义前缀索引的办法

a.判断全列的选择性

select count(distinct city)/count(*) from city

b.计算不同前缀长度的选择率

select count(distinct left(city,3))/count(*) as sel3,

count(distinct left(city,4))/count(*) as sel4,

....

from city

c.把a计算的比率和b计算的比率比较,选择一个最合适的。

聚集索引的优缺点

优点:

a.把相关的数据放在一起

b.数据访问快,索引和数据在同一颗树中

缺点:

a.插入速度严重依赖于主键顺序,更新主键列代价高

b.第二索引访问需要两次索引查询,而不是一次

关于auto_increment锁 ??
9.覆盖索引

包含了所有满足查询需要的数据的索引叫覆盖索引。当发生索引覆盖查询是在explian的extra列会显示"using index"

10.排序与索引

mysql有两种产生排序结果的方式:文件排序(filesort)或者扫描有序索引

11.索引和锁的关系

innodb只有在访问行的时候才锁定他们,并且索引能减少innodb访问的行数,从而减少锁定。如果在extra中出现了"using where"表示mysql引擎返回行后在服务器中使用了where过来条件

在一般情况下都要试着扩展索引,而不是新增索引。通常维护一个多列索引比较维护多个单列索引容易的多。

12.索引和表的维护

对于myisam引擎表,check table  repair table

innodb引擎表,alter table

更新统计信息

对于myisam表,analyze table 索引信息保存在磁盘上,执行的时候会锁表

对于innodb表,analyze table 在第一打开表时随机估计,统计信息不够准确,少执行

索引和数据碎片

索引碎片:如果叶子页面没有顺序紧密的排在一起,就认为碎片了

数据碎片:包括行碎片(数据行被存储在多个片段中就是产生碎片),和内部行碎片

修复办法

对应myisam表:optimize table

对于innodb表:alter table

13.加速alter table
修改默认值
alter table film alter column rental_duration set default 5 ,这个命令只修改.frm文件并且没有改动表

移出掉列的auto_increment属性
在mysql5.5中对应非聚集索引的添加或者删除不会引起表的重建,uk+not null也会导致重建。