索引数据结构:B+树
B+树查找过程
比如查找29, 第一次磁盘IO加载磁盘块1到内存中,在内存二分查找确定P2; 第二次磁盘IO加载磁盘块3到内存中; 第三次磁盘IO加载磁盘块8到内存中,二分查找29; 总计三次磁盘IO。 三层的B+树可以表示上百万数据,性能提高巨大。
建立索引的原则
- 最左前缀匹配原则。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
- =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。
- 选择区分度高的列最为索引
- 索引列不能参与计算
- 尽量扩展索引而不是新建,比如原来有a的索引,加(a,b)索引,只需要修改原来的索引即可。
索引失效的情况
- 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
- 对于多列索引,不是使用的第一部分,则不会使用索引
- like查询是以%开头
- 数据类型转换
例如:select * from sunyang where id='123'; 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
-
如果mysql估计使用全表扫描要比使用索引快,则不使用索引
- 列于列对比
某个表中,有两列(id和c_id)都建了单独索引,下面这种查询条件不会走索引
select * from test where id=c_id;
这种情况会被认为还不如走全表扫描。
- 存在null值条件
如果列是可空的不会创建索引
- not条件
<>、NOT、not exits等条件索引定位比较困难,倾向全表扫描
- 条件上包含函数或者加减乘除等运算
select * from test where upper(name)='SUNYANG';