企业网站微信建设,企业微信营销管理软件,刷推广链接,jsp开源网站mysql索引
1什么是索引#xff1f;
索引说白了就是一种数据结构#xff0c;可以协助快速查询数据#xff0c;以及更新数据库表中的数据#xff0c;更通俗的来说索引其实就是目录#xff0c;通过对数据建立索引形成目录#xff0c;便于去查询数据#xff0c;而mysql索引…mysql索引
1什么是索引
索引说白了就是一种数据结构可以协助快速查询数据以及更新数据库表中的数据更通俗的来说索引其实就是目录通过对数据建立索引形成目录便于去查询数据而mysql索引的实现通常是B树和B树
2索引有哪些优缺点
索引的优点 可以大大加快数据的检索速度这也是创建索引的最主要的原因。 通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能。 索引的缺点 时间方面创建索引和维护索引要耗费时间具体地当对表中的数据进行增加、删除和修改的时候索引也要动态的维护会降低增/改/删的执行效率 空间方面索引需要占物理空间。
3 索引的使用场景
索引通常可以建立在whereorder by语句之后通过建立索引避免全表扫描可以大大的提高查询性能 同时如果要查询的字段都建立过索引那么引擎会直接在索引表中查询而不会访问原始数据否则只要有一个字段没有建立索引就会做全表扫描这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段以增加索引覆盖的几率
4 索引有哪几种类型
主键索引: 数据列不允许重复不允许为NULL一个表只能有一个主键。 唯一索引: 数据列不允许重复允许为NULL值一个表允许多个列创建唯一索引。 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引
普通索引: 基本的索引类型没有唯一性的限制允许为NULL值。 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引 可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引
全文索引 是目前搜索引擎使用的一种关键技术。 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引
5 创建索引的原则
索引虽好但也不是无限制的使用最好符合一下几个原则 1 最左前缀匹配原则组合索引非常重要的原则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的顺序可以任意调整。 2较频繁作为查询条件的字段才去创建索引 3更新频繁字段不适合创建索引 4区分度不高的字段不适合做索引列(如性别男女未知最多也就三种区分度实在太低) --当然也不一定对于一些任务表 我们是可以对任务状态建立索引以此来检索需要处理的任务状态 5尽量的扩展索引不要新建索引。比如表中已经有a的索引现在要加(a,b)的索引那么只需要修改原来的索引即可。
6定义有外键的数据列一定要建立索引。 7对于那些查询中很少涉及的列重复值比较多的列不要建立索引。 8对于定义为text、image和bit的数据类型的列不要建立索引。
6 百万级别或以上的数据如何删除并重建
所以我们想要删除百万数据的时候可以先删除索引然后删除其中无用数据删除完成后重新创建索引
7 什么是最左前缀原则
1最左前缀匹配原则非常重要的原则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的顺序可以任意调整
2 和in可以乱序比如a 1 and b 2 and c 3 建立(a,b,c)索引可以任意顺序mysql的查询优化器会帮你优化成索引可以识别的形式
8 B树和B树的区别
1 对于B树来说 它的数据是存放在主键索引的叶子节点上的而对于B树来说它是将键和值即存放在叶子节点也存放在内部节点 2 B树的叶子节点之间相互连接而B树叶子节点之间相互独立
9 使用B树的好处为啥使用B树不使用B树
很明显 B树他的特点是数据只存在在主键索引的叶子节点的而非叶子节点存放的是索引这也就意味着在相同数据量的情况下B树的非叶子节点可以存放更多的索引树的层级更少io次数也就更少
同时mysql-innodb存储引擎下它的叶子节点之间用双向指针进行连接传统的B树是单向指针这也就意味着它可以快速的进行范围查找也就是B树只需要使用O(logN)时间找到最小的一个节点然后通过链进行O(N)的顺序遍历即可。 而B树则需要对树的每一层进行遍历这会需要更多的内存置换次数因此也就需要花费更多的时间
10 Hash索引和B树所有有什么区别或者说优劣呢?
首先要知道Hash索引和B树索引的底层实现原理 hash索引底层就是hash表进行查找时调用一次hash函数就可以获取到相应的键值之后进行回表查询获得实际数据。 B树底层实现是多路平衡查找树。 对于每一次的查询都是从根节点出发查找到叶子节点方可以获得所查键值然后根据查询判断是否需要回表查询数据。
那么可以看出他们有以下的不同 hash索引进行等值查询更快(一般情况下)但是却无法进行范围查询。 因为在hash索引中经过hash函数建立索引之后索引的顺序与原顺序无法保持一致不能支持范围查询。 而B树的的所有节点皆遵循(左节点小于父节点右节点大于父节点多叉树也类似)天然支持范围。
hash索引不支持使用索引进行排序原理同上。 hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。 hash索引任何时候都避免不了回表查询数据而B树在符合某些条件(聚簇索引覆盖索引等)的时候可以只通过索引完成查询。 hash索引虽然在等值查询上较快但是不稳定。性能不可预测当某个键值存 在大量重复的时候发生hash碰撞此时效率可能极差。而B树的查询效率比较稳定对于所有的查询都是从根节点到叶子节点且树的高度较低。 因此在大多数情况下直接选择B树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。
11 什么是聚簇索引何时使用聚簇索引与非聚簇索引
聚簇索引说白了也就是数据和索引在一块找到索引也就找到了数据 非聚簇索引即数据和索引分开存储索引结构的叶子节点指向了数据的对应行myisam通过key_buffer把索引先缓存到内存中当需要访问数据时通过索引访问数据在内存中直接搜索索引然后通过索引找到磁盘相应数据这也就是为什么索引不在key buffer命中时速度慢的原因 澄清一个概念innodb中在聚簇索引之上创建的索引称之为辅助索引 辅助索引访问数据总是需要二次查找非聚簇索引都是辅助索引像复合索引、前缀索引、唯一索引辅助索引叶子节点存储的不再是行的物理位置而是主键值 何时使用聚簇索引与非聚簇索引
12 非聚簇索引一定会回表查询吗
不一定这涉及到查询语句所要求的字段是否全部命中了索引如果全部命中了索引那么就不必再进行回表查询。 举个简单的例子假设我们在员工表的年龄上建立了索引那么当进行select age from employee where age 20的查询时在索引的叶子节点上已经包含了age信息不会再次进行回表查询。
13 联合索引是什么为什么需要注意联合索引中的顺序
MySQL可以使用多个字段同时建立一个索引叫做联合索引。在联合索引中 如果想要命中索引需要按照建立索引时的字段顺序挨个使用否则无法命中索引。 具体原因为: MySQL使用索引时需要索引有序假设现在建立了nameageschool的联合索引 那么索引的排序为: 先按照name排序如果name相同则按照age排序如果age的值也相等则按照school进行排序。 当进行查询时此时索引仅仅按照name严格有序因此必须首先使用name字段进行等值查询之后对于匹配到的列而言其按照age字段严格有序此时可以使用age字段用做索引查找以此类推。因此在建立联合索引的时候应该注意索引列的顺序 一般情况下将查询需求频繁或者字段选择性高的列放在前面。 此外可以根据特例的查询或者表结构进行单独的调整
