高性能MySQL总结-第三章: 架构优化和索引

只是一些 tips, 用于记忆和参照使用.
万事不是绝对,只适用与大多数场合.

3.1 数据类型

数据类型越小越好:
比如 char 对比 varchar, 使用了更少的磁盘空间、内存和CPU缓存.
越简单越好
int 比 字符串 好,datatime 比 字符串好, 整数比 ip地址字符串好.
尽量避免 NULL
可空查询会把索引变得更复杂.

timestamp 使用的空间 只有 datetime 的一半,

3.1.1 整数
1. 选择合适的位数长度.
2. 可选属性 unsigned.

3.1.2 实数
float 和 double 对于 decimal 来说,更小,精准度更高,范围更大,但运算时,这两个会造成丢失精度.

3.1.3 字符串
1. varchar 会清除末尾空格, char 不会.
2. varchar 需要使用1或2个字节来存储实际长度. 当 <= 255 时,用1个字节.
3. varchar(n) n 不同时,虽然保存相同的字符串占用的空间一样, 但 n 越小消耗的内存越小.
4. blob,text 类型会按照 max_sort_length 前若干个字节进行排序.
5. enum 代替字符串,用法等同于 java, 实际也是数字.可以

select e+0,e from enum_test

来查看具体的数值.

3.1.4 日期和时间
1. mysql 最细精度为秒.
2. datetime 实际是 YYYYMMDDHHMMSS 整数, 与时区无关.
3. timestamp 只能表示从 1970 - 2038, 只占用 4 字节.
4. from_unixtime() 和 unix_timestamp()
5. timestamp 依赖时区, MySQL 服务器 ,操作系统,客户端连接都有时区设置.

3.1.6 标识符
1.  最好使用整数做标识符.
2. 字符串做标识符会比较慢, 在压缩的 MyISAM 引擎下要慢6倍.
3. 性能上 int > uuid > hashcode

3.1.7 特殊数据
使用无符号整数保存 ip地址 , inet_aton() inet_ntoa()

3.2 索引基础
1. column 的类型因为 Hibernate 等 ORM 框架使得对开发人员透明,推荐不要自动生成表,要写script .
2. Column 创建的顺序也很重要.
3. 索引多列数据,MySQL 只能搜索索引最左前缀.

3.2.1 索引类型
1. B-Tree 索引. 只能全索引或索引最左前缀.
2. Hash 索引. 需要自己维护 Hash code 列. 可以有个 trigger 来 insert 和 update hash 索引列.
3. 空间索引 R-Tree, MyISAM 支持 R-Tree.
4. 全文索引.

3.3 索引策略

3.3.1 隔离列
"隔离"列意味着索引列不是表达式的一部分,也没有位于函数中.

3.3.2 前缀索引
如果一列数据太长,用hash索引未必是最好的选择.
可以使用前缀索引. 及取前 N 个长度的字符作为索引.
N 的选择原则1:

-- 全列的选择性
select count(distinct city)/count(*) from sakila.city; 
-- N长度前缀的选择性
select count(distinct left(city,N))/count(*) from sakila.city;
-- create index sql
alter table sakila.city add key (city(7))

3.4 索引研究

3.4.1 多种过滤条件
in 语句可以引用索引.
一个索引从左开始,直至第一个范围查询,比如: ">" 和 "<" 这类,还有 between.
为了最左前缀匹配,可以把 sex 这种放在索引最左边,即使没有根据sex 去查询,也可以把  sex in ('male','female') 放在 sql 里.
应该把范围条件放在索引的最右边,比如 age.
大量的in组合也会有问题, 比如  a in (1,2,3) and b in (4,5,6) and c in (7,8,9)
那么就认为这是  3 * 3 * 3 种组合.如果上千,那么会非常慢.

3.4.2 避免多个范围条件
避免这样的查询

select * from customer where age > ? and salary > ?

无法为两个范围条件使用索引,这种情况可以根据业务逻辑将其中一列转换为可以用 in 查找,
比如 salary 大于 1w 小于 2w 的会有另外一列保存为 1, 可以创建一个 (salary_mapping_column,age) 这样的索引.

3.4.3 优化排序
1. 为低选择性列排序加索引, 例如 (sex,rating).可以优化下面这个 SQL, 有 order by 和 limit 没有索引会很慢.

select * from profiles where sex='M' order by rating limit 10

2. 即使有索引,这样也会很慢

select * from profiles where sex='M' order by rating limit 10000,10

这样的 SQL 可以优化为:

select * from profiles  inner join(
select pk from profiles where x.sex ='M' order by rating limit 100000, 10
) as x using(pk)

3.7 加速 alter table

3.7.1 只修改 .frm
移除列的 auto_increment
添加\移除或更改 ENUM 和 SET 常量.

3.8 对存储引擎说明

3.8.1 MyISAM
表锁, 不支持自动数据恢复, 只有索引被缓存在内存中, 紧密存储.

3.8.2 Memory
表锁, 不支持动态行,哈希索引是默认索引类型, 没有索引统计, 重启后丢失数据.

3.8.3 InnoDB
事务性, 外键, 行级锁, 多版本, 按主键聚集, 所有索引包含主键列, 优化缓存, 未压缩的索引, 数据装载缓慢, 阻塞 AUTO_INCREMENT, 没有缓存 count(*)