本文详细探讨了MySQL的索引，包括为什么需要索引、各种类型的索引及其优缺点，重点阐述了B+Tree作为MySQL主要索引数据结构的原因。此外，还介绍了InnoDB存储引擎的特性和主键的重要性，以及索引最佳实践和优化技巧，帮助读者全面理解MySQL性能优化。
目录
问题？为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
1.索引
索引是一个数据结构，能帮msyql高效的获取排好序的数据
为什么需要索引
如果没有索引的话“select * from a where a.col2=9;”是逐行去寻找，数据存储在磁盘上，有可能不是顺序存储(随机存储，每条信息之间隔了很长时间才插入)，这样会产生大量的I/O，而索引数据结构是树结构，比如二叉树存储是kv形式，key是索引字段的值，而value是索引所在行的磁盘文件地址
索引的类型有哪些？mysql使用的是哪种索引数据结构
- 二叉树
- 红黑树
- Hash表
- B-Tree
二叉树问题: 如果id自增列使用二叉树作为索引，索引也是存在磁盘，那么还是会和没加索引一样进行相同次数的磁盘交互。
红黑树问题：红黑树能解决二叉树单边增长的问题，但是在大数据量情况下，树的高度不可控，也不是很理想
B-Tree:将很多索引放在一个大的磁盘空间，即使有几百万、上千万条索引也只有有限的层数。非叶子节点也有data，
B+Tree: B-Tree的变种，mysql底层用的就是B+Tree，B+Tree将B-Tree的非叶子节点的数据挪到了叶子节点。B+Tree相邻两个叶子节点之间有指针，而B-Tree是没有指针的。B+Tree 一个节点16KB,以索引存储以bight=8B来计算，磁盘地址6B，一共三层，能够存放千万个索引。只用进行三次磁盘I/O。
B+Tree在叶子节点有双向指针，可以进行范围查找
Hash结构：对索引的key进行一次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+树索引更高效
仅能满足 "=" , "IN" , 不支持范围查询
hash冲突问题
MyIsam与InnoDB存储在哪里？以什么样形式存储
存储在mysql安装目录下的data文件下
myisam索引(非聚集)
InnoDB 索引(聚集)
- 表数据文件本身就是按B+Tree组织的一个索引结构文件
- 聚集索引-叶节点包含了完整的数据记录
问题？为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
InnoDB在存储的时候，在ibd文件里必须按B+Tree组织，如果有主键就可以用自带主键去构建B+Tree 。如果这张表不建主键，会从这张表里第一列开始选择一列数据，这列数据都不相等的，用这列来组织B+Tree,如果选不到会建一个隐藏列，这个隐藏列会维护唯一的ID。
整形会比长串字符串小很多，数据是存在磁盘中，索引占用的空间越小，能够节约硬盘空间。
如果插入不是自增的，那么会导致索引进行分裂，做一个平衡。那么就没有自增往后添加新索引效率高。
聚集索引与非聚集索引，哪种速度会快一点？
聚集索引，因为非聚集索引要跨文件去查询。
为什么非主键索引结构叶子节点存储的是主键值？
一致性和节省存储空间
复合索引
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
索引最左前缀原理，从左到右排好序比较大小，还是用B+Tree数据结构
为什么最左前缀原理，只能有最左边的才能走索引？
因为索引是排好序的，先按照最左边的name排序，name相同再按照age排序，如果查找直接使用age、position的话，那么就相当于全表扫描。
2.索引最佳实践
1.全值匹配
2.最左前缀法则
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引实现而转向全表扫描
索引数据结构中查找不到做完操作的索引列
4.存储引擎不能使用索引中范围条件右边的列
因为不能保证范围查找完后，下一列是有序的
5.尽可能不使用select *
6.mysql在使用 != 或者<> ,not in ,not exists的时候无法使用索引会导致全表扫描
<小于、>大于、<=、>=这些，msyql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
7.is null ,is not null一般情况下也无法使用索引
8.like以通配符开头('$abc...') mysql索引失效会变成全表扫描 （借助搜索引擎）
在业务系统中，使用mysql存储涉及到like默许查询，并且'%'开头，在select 字段尽可能的加上索引，这样就会在索引树上进行查找。
9.字符串不加单引号索引失效
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
