MySQL索引优化 笔记
少取字段，建立合理的索引
表优化：
1 定长与变长分离
如果都是定长 查询比较快 因为每一行的字节都是固定的 fixed
2 常用字段和不常用字段要分离
用户表 常用 放主表
个人介绍不常用 还比较长 可以单放一张表
3 在1对多 需要关联统计的字段上，分析字段的查询场景，查询频率低的字段单拆出来
添加冗余字段添加速度 和三范式相反
比如 论坛 每个栏目 今日发帖数量
正常要关联栏目表和帖子表 非常耗资源
可以在栏目表上添加数量字段 每次发帖+1
优化无非2个方式 : 空间换时间（现在内存大了 常用） 时间换空间
列选择原则：
1 字段优先级
整型>data,,time>enum,char>varchar>blob，text
整型： 定长 没有字符集 国家地区差异
如:tinyint
如果是字符串要考虑字符集和校对规则char（1）
order by排序 tinyint快
time 定长 考虑时区不方便 用时间戳
enum 内部也是整型 起约束左右 多了一个转换个过程
char 定长 要考虑字符集和校对规则
varchar 不定长 查的慢
blob，text 更慢 不发用null 排序只能在磁盘
char(1) 3字节
enum('男'，‘女’） 内部转成数字 多了一个转换过程
tinyint 1字节 // 1 ,2 ,3
2 够用就行 不要慷慨
年龄 tinyint unsigned not null 255岁够了
int 浪费3个字节
varchar（300）比 varchar（10） 速度慢
3 尽量避免用null
null 不利于索引 在磁盘上占据空间更大 5.7后优化了 但查询仍不便
索引优化策略
btree索引 原理：
查数据 1 2 3 4 5 6 7 正常id=7 要查7次
4
2 6
1 3 5 7
先在索引数上跑，找到位置了 在指向表中行的信息
hash索引
只能在memory表(内存表 关机就没了)使用，查询速度O(1) 一下就找到
原理：
hash($id){
...
return address
}
通过函数算出个地址， 存磁盘 ，查的时候直接找地址
缺点：
1.hash(4) hash(94)地址一样，通过拉链算法 4和5不一定挨着 有很多空洞
2 无法对范围查询进行优化 查精准的虽然快 where id >=4 就慢了
3 无法利用前缀索引 btree中 通过索引查询helloworld hello也能， hash不行
4 排序也无法优化
5 必须回行 通过索引拿到数据位置，必须回到表中取数据
联合索引
用的更多 和顺序有关
index（a,b,c） 为例
可以看做3个木板过桥
】a b c 【
= 相当于 走完了 like 走一半
列：
where ： a =3 走完了
a=3 & b= 4 ok
a=3 & b=4 & c= 5 ok
a=3 && b like 'hi%' c不能用
b=3 or c=4 没走a no
a=3 and b like '%word' a能 b不能 b前面不确定
索引的左前缀原则
索引除了提高查询速度 还能提高分组和排序的速度！
要分组先排序
explan 查看索引使用情况
聚簇索引和非聚簇索引
myisam引擎和innodb引擎 都是btree索引
myisam引擎： 非聚簇索引 数据是一个单独文件news.myd
索引是一个单独文件new.myi 是相互独立的
主索引和次索引都指向物理行（磁盘 位置） 相互没关系
innodb引擎: 数据在索引叶子下面
主索引文件上，直接存放该行数据，成为聚簇索引 次索引指向对主键的引用
1.主键索引 既存储索引值，又在叶子中存储行的数据
2.如果没有主键primary key，则会Unique key做主键
3.如果没有unique，则系统生成一个内部的rowid做主键
4.像innodb中，主键的索引结构中，既存储了主键值，又存储了行数据，这种结构称为聚簇索引
聚簇索引
优势：根据主键查询条目比较少时，不用回行（数据就在主键节点下）
劣势：如果碰到不规则数据插入时，造成频繁的页分裂
索引覆盖
在建立一个intex inm（id,name）索引的表中
查询select name form user where id = 2;
不需要回行，因为在索引中找到id=2 时，在旁边就能找到name值，不需要去数据库在找了，这种查询速度很快，这就是索引覆盖 Extra ：using index
如果要查其他的字段则需要回行
如何设计理想的索引：
1 查询频繁的
2 区分度高的 如果100w用户 按性别男女区分各50w 区分度就低
3 长度小的 越大相当于目录越大
4 尽量能覆盖常用查询字段
列：
这种字段要建立索引 根据左前缀原则 重复太多
方法1：
倒序排列：
moc.udiab.www//:ptth 可以通过cn，com，net等建索引
方法2：
伪哈希技巧：
crc32是一种哈希算法，能把字符串算为32位整数
crc32（‘http://www.baidu,com’）用这个当索引
多列索引
多列索引的考虑因素：
列的查询频率
列的区分度
列的查询顺序
（要结合实际业务场景）
商城业务看 顾客一般先选大分类-小分类-品牌
index(cat_id,price)和dex(cat_id,brand_id,shop_price)
索引与排序
索引不光能提高查询速度 也能提高排序和分组的速度
1 覆盖索引 直接在索引上查询时 就是有顺序的using index
2 先取出数据 形成临时表做filesort（文件排序）
我们的争取目标--取出来的数据本身就是有序的
重复索引和冗余索引
重复索引：在同一个列或者顺序相同的几个列 建立了多个索引 可以重叠 别完全一样 不可以
冗余索引：是指2个索引所覆盖的列有重叠,但顺序不一样 比较常见 可以
根据文章标签查id 根据id查标签 建立2个索引
index arttag(artid,tag) index tagart(tag,artid)
修复表
索引碎片与维护 在长期的数据更改过程中，索引文件和数据文件，都将长生空洞，形成碎片
不可避免
nop操作（不产生对数据实际影响的操作）来修改表
修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对齐，比较耗资源不能频繁修复
