mysql从入门到劝退
一、索引篇
- 索引是帮助MySQL高效获取数据的排好序的数据结构。
- mysql底层索引数据结构示例,如下图(B+Tree)
聚集索引示例图
二级索引(非聚集索引)示例图
其特点:
表数据文件本身就是按B+Tree组织的一个索引结构文件
非叶子节点不存储数据,只存储索引(冗余),只为存储更改的数据。
聚集索引-叶节点包含了完整的数据记录。
二级索引-叶节点数据为聚集索引。
叶子节点用指针连接,提高区间访问的性能。
-
延伸讨论
为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?
答:减少B+树的树平衡计算
为什么非主键索引结构叶子节点存储的是主键值?
答:一致性和节省存储空间 -
索引优化
4.1. 我们谈到索引优化,必不可少的工具就是Explain,一般我们先看是否是否走了索引,走了索引之后,看索引的级别,其级别优先级为:system > const > eq_ref > ref > range > index > ALL
一般来说,得保证查询达到range级别,最好达到ref
4.2. **Using filesort:**将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一般
也是要考虑使用索引来优化的。
4.3. 最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
4.4. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
4.5. 存储引擎不能使用索引中范围条件右边的列。
4.6. 尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句。
4.7. mysql在使用不等于(!=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。
4.8. is null,is not null 一般情况下也无法使用索引。
4.9. 字符串不加单引号索引失效。
4.10. 少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估
是否使用索引,详见范围查询优化。