杭州网站建设哪家权威,高端网站登录入口,爱站网反链分析,济南网站建设免费索引
mysql索引#xff1a;
在MySQL中#xff0c;索引是存储引擎实现的#xff0c;所以没有统一的索引标准#xff0c;不同存储引擎的索引工作方式也不一样#xff0c;也不是所有的存储引擎都支持所有类型的索引即使是多个存储引擎都支持同一种类型的索引#xff0c;他…索引
mysql索引
在MySQL中索引是存储引擎实现的所以没有统一的索引标准不同存储引擎的索引工作方式也不一样也不是所有的存储引擎都支持所有类型的索引即使是多个存储引擎都支持同一种类型的索引他们的底层实现也有可能不一样
mysql 主要的索引类型有
普通索引允许在定义索引的列中插入重复值和空值唯一索引允许空值但列值必须唯一主键索引不允许空值一般在建表的时候就建立了主键索引组合索引索引列组合的值必须唯一全文索引允许索引列有空值和重复值效率高于like的模糊查询但是精度有问题一般不用
索引的优点原因
减少了需要扫描的数据量大大加快了数据的检索速度;索引能帮助服务器避免排序和临时表索引可以把随机i/o变为顺序i/o创建唯一性索引保证数据库表中每一行数据的唯一性;加速表和表之间的连接;
缺点
索引需要占物理空间。当对表中的数据进行增加、删除和修改的时候索引也要动态的维护降低了数据的维护速度创建和维护索引的时间会随着数据量的增加而增加一般来说一个表的索引不要超过6个对于很小的表全表扫描可能更高效
为什么要使用联合索引
减少开销建一个联合索引(a,b,c)实际相当于建了(a),(a,b),(a,b,c)三个索引而每多一个索引都会增加写操作的开销和磁盘空间的开销所以使用联合索引会大大的减少开销效率高索引列越多通过索引筛选出的数据越少前提是索引可以生效
常见错误为每个列创建独立的索引
在多个列独立的创建单列索引大多数情况并不能提高查询性能mysql引入了一中索引合并的策略如果查询能够使用两个单列索引进行扫描并将结果合并如果出现了这种情况就说明表的索引建的很糟糕应该建立一个由相关列的多列索引对多个索引进行合并操作时需要耗费大量的cpu和内存资源并且优化器不会把这些操作计算到查询成本中优化器只关心随机页面读取这样会导致查询的成本被低估甚至不如做全表扫描
组合索引的最左原则 在检索数据时从联合索引的最左边开始匹配组合索引的第一个字段必须出现在查询组句中这个索引才会被用到 重复值最少的放在最前面一次排除的数据就越多可提高效率
前缀索引
当给某一个列字段添加索引时如果该列字段的字符串值很长时那么我们创建的索引则会很大且很慢这个时候如果以索引列开始的部分字符串来建立索引那么就可以节约索引空间从而提高索引效率此时这种类型的索引就叫“前缀索引**”**索引值重复性越低查询效率就越高使用前缀索引时要尽可能降低重复的索引值否则可能会增加查询的时间
表中数据越大索引对性能的影响愈发重要 mysql中 索引是存储引擎层实现的所以不同的存储引擎对索引的实现也不一样常见的数据格式有 b树 innodb和myIsam 存储引擎都支持b树索引索引的目的就是提高查询效率而树型结构的查找效率很高例如二叉查找树理想情况下每次查找都是在做二分查找因为内存的易失性通常数据都会存储在外部设备也就是硬盘中但和内存相比从硬盘中读取数据的速度会非常缓慢所以减少对硬盘的访问次数可以显著提高效率而b-树就是一棵多叉的查找树它的一个节点可以存储多个键值和数据相对于平衡二叉树来说因为每个节点都存储了更多的键值和数据所以每个节点就能拥有更多的子节点树的高度会降低很多也就是减少了磁盘的读取次数查找效率会更高一般树的节点最多拥有几个子节点就被称为几阶b树又因为从磁盘中读取数据是按照磁盘块来读取的并不是一条条的读取也就是说每次从磁盘中读取到的数据大小其实是相同的如果能把尽可能多的数据放在一个磁盘块中一次就能读取更多数据也就能减少读取磁盘的次数b树就是对b-树的进一步优化b树的非叶子节点是不存储数据的只存储键值所以一次能读取的数据更多一个节点能存储的数据也会更多树的阶数也就更大对磁盘的读取会更少效率也会更高 哈希索引 哈希索引是基于散列表实现的主要用于memory存储引擎哈希索引是使用索引列的值来计算hashcode值然后再hashcode值对应位置存储所在行数据的物理位置访问时根据hashcode值进行精确匹配效率极高但是精确匹配就需要匹配索引所有列的查询才有效只支持等值比较不支持范围查询而且也不是按照索引值顺序排列的所以不能用于排序也无法区间快速查找哈希索引只包含哈希值和行指针没有真正的数据仍然需要读取对应的行 全文索引 只能用于innodb和myisaminnodb也是再5.5以后才支持的全文索引对于文本大对象或者较大的char类型数据可以使用全文索引但是全文索引会很浪费时间和空间全文索引查找的不是索引中的值而是文本中的关键字类似于搜索引擎做的事而不是简单的where条件匹配全文索引也只适用于match against 操作在相同的列上建立不同类型的索引不会冲突
b树和b-树的区别
b树因为非叶子节点没有数据所以查询一个元素必须要从根节点访问到叶子节点而每一个叶子节点高度是相同的所以数据的查询效率相等而b-树有可能在查找途中结束b树的数据会在叶子节点用双向链表连接起来而且数据是按照顺序排列的所以b树分组查找和去重会很高效myisam和innodb在b树索引实现的不同点myisamb树索引叶子节点并没有存储数据而是存储的数据地址非聚簇索引
innodb自适应哈希索引
当innodb发现某些索引值被非常频繁的访问时就会在原有的b树索引上在内存中再构建一个哈希索引这让b树索引具备了一些哈希索引的优势可以实现非常快速查找这个过程是自动的用户无法进行控制 但是可以手动关闭这个特性
innodb 聚簇索引和非聚簇索引
聚簇索引将数据存储与索引放在一起索引的每一个叶子节点保存了主键值、事务id、用于事务和mvcc的回滚指针、以及所有的剩余列如果主键是一个前缀索引也会包含完整的主键列inndob 使用的聚簇索引聚簇索引默认是主键如果表中没有主键会自动选择一个主键innodb会选择一个唯一且非空的索引代替如果没有这样的索引会定义一个隐式的主键来作为索引所以聚簇索引是由唯一性的会将主键组织到一颗b树中行数据就存储在叶子节点主键以外的列构建的 B 树索引称为非聚集索引 innodb 非聚簇索引叶子节点存储的不是行数据而是该列对应的主键需要根据主键进行二次查找好处是innodb在移动行时不需要更新二级索引中的主键指针
聚簇索引的优缺点
优点 可相关联的数据保存在一起能够减少从磁盘读取的数据页应为索引和数据保存在一起所以访问速度更快使用索引覆盖的查询能够直接使用页节点中的主键值 缺点 插入速度严重依赖于插入顺序按照顺序插入是最快的方式如果不是最好用optimize table 命令重新组织以下表当然最好避免随机写入更新聚簇索引的代价会很高会把被更新的行移动到新的位置插入新行时如果页已满就会造成页分裂来容纳改行导致表占用更多的空间聚簇索引会使全表扫描变慢尤其是行比较稀疏或者由于页分裂导致数据存储不连续的时候二级索引可能会很大并且二级索引需要查找两次
mylsam 非聚簇索引
将行数据与索引分开存储索引结构的叶子节点指向了数据对应的地址mylsam 使用的就是非聚簇索引无论是主键索引的b树还是非主键索引的b树叶子节点都指向真正的数据索引树也是独立的
innodb主键
Innodb主键是聚簇索引其他的二级索引中必须包含主键列如果主键很大的话其他的所有索引都会很大所以如果表上的索引较多主键应该尽可能的小主键如果是自增列可以保证是按照顺序写入最好避免随机写入会使得聚簇索引很糟糕 例如使用uuid作为主键是非常差的选择不仅插入时间会变长索引也会变得更大而且会导致更多的也分裂和碎片不按顺序写新的主键值不一定比之前的大所以不能直接的插到索引的最后而是需要找到合适的位置并且分配空间会增加很多额外工作 但是对于高并发的表顺序的主键上界会成为热点因为所有的插入都发生在这里会导致资源竞争如果没有定义主键innodb会选择一个唯一的非空索引替代如果也没有这样的索引会隐式定义一个主键做聚簇索引这样的缺点是依赖一个单点的自增值可能会导致非常高的锁竞争
索引失效场景
使用索引是没有匹配“最左匹配原则”在使用索引前使用了范围查找使用or关键字用不了索引索引列有运算或者函数运算like以%开头产生类型转换如果MySQL预计使用全表扫描要比使用索引快则不使用索引
索引覆盖
也就是通过索引直接获取列数据不需要再读取行只扫描索引不需要回表好处有 索引条目通常小于数据行大小只读取索引可以减小数据访问量索引按照列值顺序存储返回查询比随机从磁盘读取要快的多覆盖索引对innodb特别有用如果二级索引可以覆盖查询就不用再对主键索引二次查询 不是所有的索引都能称为覆盖索引覆盖索引必须存储列的值所以只有B 树索引可以想要覆盖索引就不要使用like不要使用索引以外的列
innodb只有再访问行的时候才会加锁索引可以减少访问的行数从而减少锁的数量但是如果索引不能有效过滤掉无效的行再数据返回给服务器层后mysql才能使用where字句这时就无法避免的会锁定行
重复索引 重复索引是指在相同列上按照相同顺序建立的相同类型的索引应该避免创建重复索引发现之后应该立即移除 mysql允许在相同的列上创建多个相同索引虽然会抛出警告但并不会阻止 MySQL需要单独维护重复的索引优化器在优化查询的时候也需要逐个评估会影响性能和浪费磁盘
冗余索引
例如创建了A,B索引在去创建A索引就是冗余索引还有就是创建了A索引再去创建Aid索引也是冗余索引大多数情况下都不需要冗余索引应该扩展已有的索引而不是创建新的索引但是有的时候扩展索引会导致索引变得很大从而影响其他使用该索引的性能
至于未使用的索引完全是累赘建议删除
索引使用的注意事项
在有更多不同值的列上创建索引会更好尽可能的去重用索引而不是建立大量的索引尽可能把范围查询放到索引列的后面对于范围查询没办法使用到范围列后面的索引
limit数据量过大: 尽可能的使用索引覆盖扫描而不是查询所有列对于偏移量很大的时候效率提升很明显 随着偏移量的增加需要花费大量的时间来扫描需要丢弃的数据缓存和提前计算是仅有的策略 更好的办法当然是减少用户能够翻到的页数大量的翻页没有意义
维护表
目的找到并修复损坏的表维护准确的索引统计信息减少碎片有可能因为硬件或者其他问题导致表或索引损坏会导致很多莫名其妙的问题如果遇到了不应该出现的问题可以试试 CHECK TABLE 来检查表是否发生了损坏并可以使用REPAIR TABLE来修复损坏的表如果存储引擎向优化器提供的扫描行数信息不准确或者执行计划太复杂导致无法准确的获取各个阶段的匹配行数优化器就没办法通过索引的形象来估算扫描行数可以通过 analyze table 来重新生成统计信息b树索引会产生碎片化可以通过optimize table 命令重新组织以下表
