山东济南网站开发,怀柔谁会网站开发,做阿胶上什么网站比较好,东莞网上销售网站建设1. 索引的代价
在了解索引的代价之前#xff0c;需要再次回顾一下索引的数据结构B树 如上图#xff0c;是一颗b树#xff0c;关于b树的定义可以参见B树#xff0c;这里只说一些重点#xff0c;浅蓝色的块我们称之为一个磁盘块#xff0c;可以看到每个磁盘块包含几个数据…1. 索引的代价
在了解索引的代价之前需要再次回顾一下索引的数据结构B树 如上图是一颗b树关于b树的定义可以参见B树这里只说一些重点浅蓝色的块我们称之为一个磁盘块可以看到每个磁盘块包含几个数据项深蓝色所示和指针黄色所示如磁盘块1包含数据项17和35包含指针P1、P2、P3P1表示小于17的磁盘块P2表示在17和35之间的磁盘块P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据只存储指引搜索方向的数据项如17、35并不真实存在于数据表中。
1.1 b树的查找过程
如图所示如果要查找数据项29那么首先会把磁盘块1由磁盘加载到内存此时发生一次IO在内存中用二分查找确定29在17和35之间锁定磁盘块1的P2指针内存时间因为非常短相比磁盘的IO可以忽略不计通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存发生第二次IO29在26和30之间锁定磁盘块3的P2指针通过指针加载磁盘块8到内存发生第三次IO同时内存中做二分查找找到29结束查询总计三次IO。真实的情况是3层的b树可以表示上百万的数据如果上百万的数据查找只需要三次IO性能提高将是巨大的如果没有索引每个数据项都要发生一次IO那么总共需要百万次的IO显然成本非常非常高。
空间上的代价
从索引的数据结构可以得出每建立一个索引都要为它建立一棵B树每一棵B树的每一个节点都是一个数据页一个页默认会占用16KB的存储空间一棵很大的B树由许多数据页组成那可是很大的一片存储空间呢。
时间上的代价
每次对表中的数据进行增、删、改操作时都需要去修改各个B树索引。而且我们讲过B树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录还是内节点中的记录也就是不论是用户记录还是目录项记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏所以存储引擎需要额外的时间进行一些记录移位页面分裂、页面回收啥的操作来维护好节点和记录的排序。
1.2 什么时候要使用索引
只要使用到索引都是有代价的因此我们不能盲目的使用索引
主键自动建立主键索引频繁作为查询条件在WHERE查询中与其他表关联的字段外键关系建立索引作为排序的列要建立索引排序字段通过索引去访问会大大提高排序速度高并发条件下倾向组合索引查询中统计或者分组的字段或者用于聚合函数的列可以建立索引例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
1.3 什么时候尽量不要建立索引
表记录太少全表扫描也很快没有必要经常增删改的字段上不要建立索引有大量重复且分布均匀的数据的列不建立索引
2. 多列索引
在上面的例子中都是以单个列作为索引的
而多列索引指的是组合索引组合多个列创建一个索引很多人对多列索引理解不够常见的就是为每一个列创建独立的索引或者按照错误的顺序创建组合索引。
在多个列上建立单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新的版本引入了一种“索引合并”index merge的策略一定程度上可以使用表上的多个单列索引来定位指定的行。
在MySQL’更早的版本中只能使用其中某一个单列索引。但在MySQL5.0和更新的版本中查询能够同时使用多个单列索引进行扫描并将结果进行合并。 该特新主要应用于以下三种场景
对OR语句求并集如查询SELECT * FROM TB1 WHERE c1“xxx” OR c2“xxx时如果c1和c2列上分别有索引可以按照c1和c2条件进行查询再将查询结果合并union操作得到最终结果对AND语句求交集如查询SELECT * FROM TB1 WHERE c1“xxx” AND c2”xxx时如果c1和c2列上分别有索引可以按照c1和c2条件进行查询再将查询结果取交集intersect操作得到最终结果对AND和OR组合语句求结果
2.1 索引适用的查询 全值匹配 如果我们的搜索条件中的列和索引列一致的话这种情况就称为全值匹配 匹配左边的列 其实在我们的搜索语句中也可以不用包含全部联合索引中的列只包含左边的就行 匹配列前缀 对于字符串类型的索引列来说我们只匹配它的前缀也是可以快速定位记录的 匹配范围值 b树的所有记录都是按照索引列的值从小到大的顺序排好序的所以这极大的方便我们查找索引列的值在某个范围内的记录不过在使用联合进行范围查找的时候需要注意如果对多个列同时进行范围查找的话只有对索引最左边的那个列进行范围查找的时候才能用到B树索引。
2.2 使用联合索引进行排序注意事项
对于联合索引有个问题需要注意ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出
2.3 不可以使用索引进行排序的几种情况
2.3.1 ASC、DESC混用
对于使用联合索引进行排序的场景我们要求各个排序列的排序顺序是一致的也就是要么各个列都是ASC规则排序要么都是DESC规则排序。
2.3.2 排序列包含非同一个索引的列
有时候用来排序的多个列不是一个索引里的这种情况也不能使用索引进行排序
2.3.3 排序列使用了复杂的表达式
要想使用索引进行排序操作必须保证索引列是以单独列的形式出现而不是修饰过的形式
2.4 多列索引分组
如果没有索引的话分组过程全部需要在内存里实现而如果有了索引的话恰巧这个分组顺序又和我们的B树中的索引列的顺序是一致的而我们的B树索引又是按照索引列排好序的这不正好么所以可以直接使用B树索引进行分组。
2.5 回表的代价
查询优化器会事先对表中的记录计算一些统计数据然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数需要回表的记录数越多就越倾向于使用全表扫描反之倾向于使用二级索引 回表的方式。一般情况下限制查询获取较少的记录数会让优化器更倾向于选择使用二级索引 回表的方式进行查询因为回表的记录越少性能提升就越高
2.6 覆盖索引
为了彻底告别回表操作带来的性能损耗我们建议最好在查询列表里只包含索引列。
2.6.1 覆盖索引是非常有用的工具能够极大的提高性能
索引条目通常远远小于数据行操作索引如果只需要读取索引那么MySQL就会极大的减少数据访问量 因为索引是按照列值顺序存储的至少单个页内如此所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多
一些存储引擎如MyISAM的内存中只缓存索引数据则依赖操作系统来缓存因此访问数据需要一次系统调用。这会导致严重的性能问题
由于InnoDB的聚簇索引覆盖索引对于InnoDB表特别有用。由于InnoDB的二级索引在叶子结点中保存了行的主键值如果二级主键能够覆盖查询则可以便面对主键索引的二次查询
注意不是所有的索引都可以成为覆盖索引覆盖索引必须要存储索引列的值索引哈希索引、空间索引、全文索引的都不存储索引列的值所以MySQL只能使用B-Tree索引做覆盖索引
3. 如何挑选索引
3.1 只为用于搜索、排序或分组的列创建索引
只为出现在WHERE子句中的列、连接子句中的连接列或者出现在ORDER BY或GROUP BY子句中的列创建索引。而出现在查询列表中的列就没必要建立索引了
3.2 考虑列的基数
列的基数指的是某一列中不重复数据的个数比方说某个列包含值2, 5, 8, 2, 5, 8, 2, 5, 8虽然有9条记录但该列的基数却是3。也就是说在记录行数一定的情况下列的基数越大该列中的值越分散列的基数越小该列中的值越集中。这个列的基数指标非常重要直接影响我们是否能有效的利用索引。假设某个列的基数为1也就是所有记录在该列中的值都一样那为该列建立索引是没有用的因为所有值都一样就无法排序无法进行快速查找了 而且如果某个建立了二级索引的列的重复值特别多那么使用这个二级索引查出的记录还可能要做回表操作这样性能损耗就更大了。所以结论就是最好为那些列的基数大的列建立索引为基数太小列的建立索引效果可能不好。
3.3 索引列的类型尽量小
如果我们想要对某个整数列建立索引的话在表示的整数范围允许的情况下尽量让索引列使用较小的类型比如我们能使用INT就不要使用BIGINT能使用MEDIUMINT就不要使用INT 这是因为
数据类型越小在查询时进行的比较操作越快这是CPU层次的东东
数据类型越小索引占用的存储空间就越少在一个数据页内就可以放下更多的记录从而减少磁盘I/O带来的性能损耗也就意味着可以把更多的数据页缓存在内存中从而加快读写效率。
3.4 索引字符串值的前缀
只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置但是能定位到相应前缀所在的位置然后根据前缀相同的记录的主键值回表查询完整的字符串值再对比就好了。这样只在B树中存储字符串的前几个字符的编码既节约空间又减少了字符串的比较时间还大概能解决排序的问题
优点这样可以大大节约索引的空间从而提高索引的效率。缺点使用前缀索引会降低索引的选择性而且无法使用前缀索引做ORDER BY和GROUP BY也无法使用前缀索引做覆盖扫描
3.4.1 如何选则前缀索引长度
选择前缀的索引的原则是要选择足够的长度保证索引较高的选择性前缀索引的选择性应该接近于索引的整个列但同时又不能太长。
可以根据前缀的基数应该接近于完整列的基数来确定基数的长度我们可以通过截取不同长度的字符和完整列进行比较找到合适的长度 另外一个办法就是计算完整列的选择性并使用前缀的选择性接近完整列的选择性
3.5 让索引列在比较表达式中单独出现
如果索引列在比较表达式中不是以单独列的形式出现而是以某个表达式或者函数调用形式出现的话是用不到索引的
3.6 主键插入顺序
如果我们插入的记录的主键值是依次增大的话那我们每插满一个数据页就换到下一个数据页继续插而如果我们插入的主键值忽大忽小的话这就比较麻烦了。如果数据页已经满了啊再插进来咋办呢我们需要把当前页面分裂成两个页面把本页中的一些记录移动到新创建的这个页中
