1.索引列上不能使用表达式或者函数
2.前缀索引和索引列的选择性
索引的最大宽度767个字节,前缀索引就是取字符串的一部分来建立索引,
3.联合索引如何选择索引列的先后顺序
1.经常会被使用到的列优先,但是如果经常使用到的都是状态列这种过滤性很差的列就不适合了。这种列就算建立了索引 mysql优化器不见得会使用这种索引。
2 .优先选择选择性高的列
3.宽度小的列有限,在满足前面两个的基础上,宽度越小,一页中包含的索引数据就越多。IO就越小
我们在做索引优化的时候通常最关注的就是where后面的条件。
4.覆盖索引
如果我们查询的字段都建立了索引,那么这就叫覆盖索引,当查询的时候 它不需要读取表的数据,只需要读取索引就完成了查询,mysql会极大的减少数据的访问量,索引比较小,缓存中就可能保存更多的索引数据,那这个效率是很高的.可将将随机的IO变成顺序的IO操作。
使用v覆盖索引的局限:
5.如何使用索引来优化我们的查询
rental表的结构:
由于innodb数据的逻辑顺序和主键一致,所以可以用主键来进行排序 order by id
使用二级索引来排序:我们注意到这个表有一个联合索引(rental_date,inventory_id,customer_id),这就是使用了二级索引来进行排序
当索引列的升降序方向与sql语句里面的排序方向不一致的时候,会出现文件排序
联合索引最左边的列使用范围查找 则索引排序失效:
如果我们想要在一个很长的字符串上查找,就只能使用前缀索引,但是这样会使得索引的键值 可选择性变得很差,这种情况就可以使用hash索引,但是有的存储引擎不支持hash索引,
所以我们还可以使用B树索引来模拟hash索引,
比如我们想要在title这个列上建立索引,但是又不想使用前缀索引,我们就来模拟hash索引,
第一步是在表中增加一列:
第二步:填充这一列的值
第三步,在新建的这个列上加上一个索引:
使用这个索引:
这里为什么除了在索引列上过滤还要在title列上做过滤呢?主要是为了防止hash冲突。先通过title_md5把数据转化到内存中,再通过title过滤。
这种做法的局限性就是只能处理全值匹配的查询,
5.利用索引来优化锁
利用索引在存储引擎层过滤掉我们所不需要的行,减少锁带来的开销,另外索引加快数据的处理速度
演示:索引是如何来优化锁的
先删除这个索引:
并没有使用到任何索引:
先开启一个事务,
再开启一个排他锁
然后再另外的失去中开启事务查询,加上排它锁,然后就阻塞了。 只要两行数据 他却把整个表锁住了
然后我们加上索引:
这样就不会阻塞了
6.索引的优化和维护