高性能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也会导致重建。
您可能感兴趣的文章：
影响MySQL性能的查询类型有哪些
mysql数据库性能优化技巧
高性能mysql(第二版)学习笔记之架构优化和索引
高性能mysql学习笔记总结
高性能mysql(第二版)之查询性能优化
mysql索引优化提升数据库性能
想系统学习GO语言(Golang
Mysql索引分类与优化
mysql性能优化之索引优化
mysql 索引优化之btree、hash与rtree