前面一章，说的是数据类型设计，以及表设计的一些稍微值得注意的地方。
这一章，说索引，简单的基础知识就跳过，说一些稍微值得注意的地方。
inodb和mysiam都是使用b+tree，不过innodb叶子节点存储了行数据，myisam叶子节点只是存储了行数据的指针，行数据另外独立聚合存储。详情看前面的文章myisam和innodb的差异。
只有memory存储引擎显示支持hash索引，innodb是自适应hash索引。
一、前缀索引
select count(distinct left(city, 3)) / count(*) from city_demo;
如果区分度达到0.031，基本就可以使用前缀索引。
前缀索引的缺点，无法使用前缀索引做order by 或group by排序，以及覆盖索引，因为前缀索引保存的是列的一部分的值。
二、索引合并
比如：
SELECT * from article where id = 3035108785 or title = '妹纸不爱你，是因为你没有这样的发型';
这样的or查询，在之前的版本中，会进行全表扫描，mysql5.0之后，有一个索引合并的机制，也就是上面的or查询，会使用两个索引，id聚簇索引得到一部分数据，title二级索引得到一部分数据，然后进行合并得到最后的数据。
explain SELECT * from article where id = 3035108785 or title = '妹纸不爱你，是因为你没有这样的发型';
possible_keys | key
PRIMARY,title | PRIMARY,title
索引合并有三个变种：or，and以及or和and的共同作用。
三、聚簇索引
inodb的聚簇索引的更新代码很高
因为聚簇索引下有行数据，有行数据的移动，可能会导致页分裂。
聚簇索引的每一个叶子节点，都包含了，主键值，事务id，多版本控制回滚段指针（并发控制的两种方式以及innodb的多版本并发控制），以及所有的列。
不要用uuid作为主键，uuid随机性，导致顺序写变成了随机写，聚簇索引在b+tree的调整代价很高，导致页分裂，select的局部性原理失效。
做过测试，uuid作为主键和自增id作为主键，
image.png
结果：时间长，而且索引大，一部分原因是uuid占用空间大，一部分是页分裂导致的碎片。
四、使用索引扫描做排序
列顺序和索引顺序一样，且排序方向（正序或倒序）一直的时候，索引才能做排序。
如果一个列指定了常量，则可以不满足最左前缀要求，比如：
where a = '常量' order by b desc。
五、冗余和重复索引
索引太多，会导致insert插入慢。
六、案例
1、比如，sex字段，如果每次查询几乎都会用sexguolv，则sex作为顺序最前的索引列，而当不用sex过滤的时候，使用，sex in ('m', 'f')这种形式，使其满足最左前缀原则。
索引设计和查询应该结合起来优化。
2、范围查询列放到索引顺序的最后面，比如，age字段，放到前面则使其他列使用不到索引。
3、in条件的组合，不能太多，比如：where a in () and b in () and c in()，虽然会使用索引，但是其实是3中in条件相乘的结果并集，如果in 太多了，则并集很多，性能会受到影响。
explain 的 type 为range，对于in查询，也是显示type为range，但是其实in是多个等值的查询，所以type为range的并不一定是范围查询。对于范围查询后面的字段是不能使用索引的，但是等值查询不会。
- 对于有些，索引和查询都无法很好解决性能的情况，应该从设计上入手，设计是最优先和最有效的。比如：age 和 最近登录时间last_online作为过滤条件，age是范围，last_online也是范围，两个范围是无法很好使用索引的。那么可以增加一个列，active列，每次登录，则active列设为1，定时器轮询，7天之后未曾登录，则active设为0.
5、优化排序。
file sort对小数据量是很快的，但是数据量多了，则需要依赖索引。
对于排序和分页的查询，比如 where order by a limit 1000000, 10;
一个是限制用户翻页的数量。
另一个是，禁止跳页，只允许往下翻下一页，利用前面一页的条件，查询下一页，比如，前一页是id = 10，则下一页是，id > 10 limit 10。
另一个方式是：使用延迟关联，使用覆盖索引查询到主键，之后再查询数据。
比如：
select * from article a inner
join (select id from article order by title
limit 100000, 10) b on a.id = b.id;
show index from table, cordinality显示的是存储引擎估计索引列有多少个不同的取值。
网友评论