精华内容
下载资源
问答
  • Mysql索引详解

    千次阅读 多人点赞 2019-06-29 21:58:33
    索引的优缺点)1、索引产生的意义2、索引的优缺点二、索引的分类三、B树-数据库索引原理1、B树(平衡多路查找树)2、B+树3、B+树的优势四、聚合索引(InNoDB存储引擎)与非聚合索引(MyISAM存储引擎)1、聚合索引2...

    一、为什么需要索引?(索引的优缺点)

    1、索引产生的意义

    没有索引行不行?答案是肯定的,可以不使用索引,在数据库中将数据整齐的排列在磁盘阵列中,当获取数据的时候只需要逐个搜索,并返回结果,但是 如果开发的应用有几百上千万甚至亿级别的数据,那么不深入了解索引的原理, 写出来程序就根本跑不动,光查一个数据库就得好几天,因此就需要索引,能够快速的查找的需要的数据。

    2、索引的优缺点

    • 优点

    1、极大地加速了索引过程,减少IO次数
    2、创建唯一索引,保证了数据库表中的唯一性
    3、加速了表与表之间的连接
    4、针对分组和排序检索时,能够显著减少查询查询中的分组和排序时间

    • 缺点

    1、索引表占据物理空间
    2、数据表中的数据增加、修改、删除的同时需要去动态维护索引表,降低了数据的维护速度

    二、索引的分类

    1、唯一索引:表上一个字段或者多个字段的组合建立的索引,这些字段组合起来能够确定唯一,允许存在空值(只允许存在一条空值)
    2、非唯一索引:表上一个字段或者多个字段的组合建立的索引,可以重复,不需要唯一
    3、主键索引:(主索引)根据主键pk_clolum(length)建立索引,不允许重复,不允许空值;
    4、聚合索引:表中记录的物理顺序与键值的索引顺序相同
    5、非聚合索引:表中记录的物理顺序与键值的索引顺序无关
    6、全文索引:在某个字段设置全文索引后,根据特定语法查找满足条件的字段;

    1、全文搜索在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB,而之前的版本只支持 MyISAM 表。
    2、目前只有char、varchar,text 列上可以创建全文索引。
    3、 like “value%” 可以使用索引,但是对于 like “%value%” 这样的方式,执行全表查询

    7、普通索引:用表中的普通列构建的索引,没有任何限制
    8、组合索引:用多个列组合 构建的索引,但是在使用过程中有诸多规则,遵循最左前缀原则,顺序至关重要
    9、Hash索引(Memory存储引擎)是通过索引列的值计算出hashCode,之后在相应的物理位置存取索引列的值,由于hashCode的唯一性,因此Hash索引不能进行范围查找或者是顺序查找。

    三、B树-数据库索引原理

    转载自博客:https://blog.csdn.net/endlu/article/details/51720299
    2019.8.26补充:https://www.cnblogs.com/nullzx/p/8729425.html(关于B树与b+树更通俗)

    1、B树(平衡多路查找树)

    特性:
    关键字个数 = 儿子节点个数-1、每一个关键字按照升序排序、非叶子结点也存储数据、根节点至少2个子女,非叶结点至少ceil(m/2)个子女

    • 树中每个结点最多含有m个孩子(m>=2);
    • 除根结点和叶子结点外,其它每个结点至少有[ceil(m / 2)]个孩子(其中ceil(x)是一个取上限的函数);
    • 若根结点不是叶子结点,则至少有2个孩子(特殊情况:没有孩子的根结点,即根结点为叶子结点,整棵树只有一个根节点);
    • 所有叶子结点都出现在同一层,叶子结点不包含任何关键字信息(可以看做是外部接点或查询失败的接点,实际上这些结点不存在,指向这些结点的指针都为null);
    • 每个非终端结点中包含有n个关键字信息: (P1,K1,P2,K2,P3,…,Kn,Pn+1)。其中:
      a) Ki (i=1…n)为关键字,且关键字按顺序升序排序K(i-1)< Ki。
      b) Pi为指向子树根的接点,且指针P(i)指向子树种所有结点的关键字均小于Ki,但都大于K(i-1)。
      c) 关键字的个数n必须满足: [ceil(m / 2)-1]<= n <= m-1。

    在这里插入图片描述

    2、B树插入删除

    实例引用:https://www.cnblogs.com/nullzx/p/8729425.html

    2.1、插入

    1)根据要插入的key的值,找到叶子结点并插入。
    2)判断当前结点key的个数是否小于等于m-1,若满足则结束,否则进行第3步。
    3)以结点中间的key为中心分裂成左右两部分,然后将这个中间的key插入到父结点中,这个key的左子树指向分裂后的左半部分,这个key的右子支指向分裂后的右半部分,然后将当前结点指向父结点,继续进行第3步。(核心思想:找到中间值,提升为父节点,之后分裂为左右)

    2.2、删除

    1 ) 找到下一个记录,之后用下一个记录代替当前记录的位置
    2 ) 当前节点个数大于ceil(m/2)-1,直接删除
    3 ) 当前节点个数大于ceil(m/2)-1,跟兄弟节点借一个,前提是兄弟节点个数大于ceil(m/2)-1,兄弟节点借的节点上移,父节点ke下移
    4 )兄弟节点个数小于等于ceil(m/2)-1,父节点下移之后与兄弟节点合并

    3、B+树

    实例引用:https://www.cnblogs.com/nullzx/p/8729425.html

    B+树特有:
    1.有n棵子树的结点中含有n个关键字; (而B树是n棵子树有n-1个关键字)
    2.所有的叶子结点中包含了全部关键字的信息,及指向含有这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大的顺序链接。(而B树的叶子节点并没有包括全部需要查找的信息)
    3.所有的非终端结点可以看成是索引部分,结点中仅含有其子树根结点中最大(或最小)关键字。 (而B 树的非终节点也包含需要查找的有效信息)
    4.关键字按照从小到大的顺序排列

    在这里插入图片描述

    3.1 B+树插入

    1. 根节点,直接插入就可以了
    2. 叶子结点:由于在B+树中叶子结点实际保存了了数据,因此在插入B+树后如果当前节点小于m-1,直接插入;否则就将当前节点分解,左叶子结点包含前ceil(m/2)个,后ceil(m/2)+1记录到右节点,将ceil(m/2)记录的key进到父节点(父节为索引类型)
    3. 索引节点:若当前结点key的个数小于等于m-1,则插入结束。否则,分裂左右,左前(m-1)/2个节点,右为后边的节点,之后选择(m-1)/2插入到父节点

    3.2 B+树删除

    4、B+树的优势

    1、查找:B+树磁盘读写次数更低
    因为B+树非叶子结点只是相当于一个索引,会将所有关键字具体信息都存储叶子结点,也就导致B+树能够存储更加多的关键字数量,构造的树更加矮胖,一次性读入内存的关键字数量增加,IO读写次数减少

    1、 在索引查找的时候,B树的搜索方式是首先找到一个搜索下界,也就是满足条件的最低值,之后通过中序遍历的方式查找,而B+树在查找到搜索下界后,直接通过链指针开始查找关键字信息
    2、范围查找:B树从二叉树的下限一直中序遍历,知道查找到二叉树的上线,而B+树知道二叉树下限后,直接链表查找
    2、B+tree的查询效率更加稳定
    由于非终结点不存储数据,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

    5、B树与B+树的差别

    引用自:https://blog.csdn.net/q936889811/article/details/79780307
    1、B树的同一键不会出现多次,可能在叶子节点上也可能在非叶子节点上; b+树的键一定会出现在叶子节点上,同时也可能在非叶子节点上重复出现。
    简单的说,b+树的非叶子节点存储的都是键值,键值对应的具体数据都存储在叶子节点上。
    2、b数据的每个节点存储的是真是数据,会导致每个节点的存储的数据量变小,所以整个b树的高度会相对变高。随着数据量的变大,维护代价也增加; b+树的非叶子节点只存储的是键值,相对而言,一个非叶子节点存储的记录个数要比b树多的多。
    3、b+树是横向扩展,随着数据增加,会变成一个矮胖子,b树是纵向扩展,最终树的高度越来越高(高瘦子)。
    4、b树的查询效率与键在b树的位置有关系,在叶子及诶单的时候最大复杂度与b+树相同;b+树复杂度对某个建成的树是固定的
    5、 b树的键的位置不固定并且整个树结构中只出现一次,增删改查操作复杂度逐渐加;b+树中非叶子节点对于叶子节点来说就像一个索引,增删改的时候只要找到键值(索引)的位置,再一层层的向下找即可,只有在遇到一个节点存储满了会对b+树分裂。
    6、 b树种所有的数据都只存储一份;b+树除存储了所有数据的叶子节点外,还有之存储键值数据的非叶子节点。所以,b+树比b树会多占存储空间,多占的空间就是b+树的非叶子节点的所有空间。

    四、聚合索引与非聚合索引

    在这里插入图片描述

    聚合索引与非聚合索引是一种存储方式,而不是一种单独的索引类型

    前提概念:
    1、按照索引的键是否为主键来分为“主索引”和“辅助索引”,使用主键键值建立的索引称为“主索引”,其它的称为“辅助索引”
    2、聚簇索引的辅助索引的叶子节点的data存储的是主键的值,主索引的叶子节点的data存储的是数据本身,也就是说数据和索引存储在一起,并且索引查询到的地方就是数据(data)本身,那么索引的顺序和数据本身的顺序就是相同的;
    而非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址,也就是说索引和数据并不是存储在一起的,数据的顺序和索引的顺序并没有任何关系,也就是索引顺序与数据物理排列顺序无关。
    3、两者相同点就是通过B+树结构来构造B树索引

    1、聚合索引(InnoDB存储引擎需要)

    定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。

    1、选择B+树作为存储结构
    2、聚合索引必须有主键,没有主键就会选择Unique key作为主键,没有Unique则会在系统内部生成rowid作为主键,因此主键不宜过长,使得辅助索引过大,主键如果不是单调的就会造成B+Tree频繁的分裂调整
    3、在InnoDb上会选择自增字段作为主键,是为了维持B+树的分裂特性,顺序添加到当前索引的后续位置,当达到最大就会分裂产生新的页,也不需要移动原有的顺序
    4、聚合索引主索引和辅助索引,主索引叶子结点存储键值对应的数据本身辅助索引叶子结点存储主键键值
    5、由于辅助索引存储的是主键键值,因此按照辅助索引搜索的时候需要检索两遍,第一遍找到对应的主键,第二遍在主索引到达叶子结点中找到数据

    2、非聚合索引(MyIsam)

    定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。

    1、选择B+树作为存储结构
    2、非聚合索引有主索引和辅助索引(两个索引几乎一样),但是主索引不允许为空,现在就需要考虑在索引分类中介绍的非聚合索引的概念,由于物理顺序与索引顺序不同,因此每一个叶子节点存储的是指向键值对应的数据的物理地址(数据记录的地址)
    3、非聚簇索引的数据表和索引表是分开存储的
    4、获取数据的方式是首先根据B+树获取索引,取出对应数据记录的地址,之后再去读取相应的数据记录
    5、只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)
    6、辅助索引的意义:如果给出的查询条件不是主键,此时就使用辅助索引,并且使用辅助索引不需要使用主索引

    1、使用聚合索引的场景:

    • 某列包含小数目的不同值
    • 排序和范围索引(主键递增)
      2、使用非聚合索引的场景:
    • 某列包含大数目的不同值(因为在叶子节点不需要去保存数据,只需要保存地址)
    • 频繁更新的列,因为非聚集索引添加记录时,不会引起数据顺序的重组

    3、InNoDB与MyISAM异同

    博客(自己还未阅读):https://www.cnblogs.com/y-rong/p/8110596.html

    1、InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text (InNoDB从1.2.X版本开始支持全文搜索的技术)等索引,不支持 Hash 索引,但是给了又有一个特殊的解释:InnoDB存储引擎 是支持hash索引的,不过,我们必须启用,hash索引的创建由InnoDB存储引擎引擎自动优化创建,是数据库自身创建并使用,DBA(数据库管理员)无法干预;
    2、MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
    3、Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
    4、MyISAM引擎不支持外键,InnoDB支持外键
    5、MyISAM引擎的表在大量高并发的读写下会经常出现表损坏的情况
    6、对于count()查询来说MyISAM更有优势,MyISAM直接通过计数器获取。InnoDB需要通过扫描全部数据,虽然InNoDB存储引擎是支持行级别所,InNoDB是行级别锁,是where对他主键是有效,非主键的都会锁全表的
    7、MyISAM引擎的表的查询、更新、插入的效率要比InnoDB高,如果你的数据量是百万级别的,并且没有任何的事务处理,那么用MyISAM是性能最好的选择。并且MyISAM可以节省很多内存,因为MyISAM索引文件是与数据文件分开放置,并且索引是有压缩,内存使用率提高不少
    8、平台承载的大部分项目是读多写少的项目,MyISAM读性能比InNoDB强很多

    4、InNoDB存储引擎内幕略读

    1、版本功能

    在这里插入图片描述

    2、关键特性

    (1)插入缓存:正常插入自增ID速度非常快,但是如果使用UUID(防止注入式攻击,当我们使用主键自增的时候,需要删除一个东西的时候,一般都是id=?。这样的话我就可以在url中修改这个id的值,这样可能就被人删除了其他东西,UUID这个就是给主键id加上一层锁,使它不暴露给用户)者中随机插入(非连续)的值,此时需要离散的访问非聚集索引页,不是直接插入索引页,而是判断非索引也是否在缓冲池中,若在,则直接插入,若没在则放入Insert Buffer中,在Insert Buffer 中合并后插入到索引页,提高了插入性能

    InsertBuffer使用条件

    1. 索引是辅助索引
      2、索引不是唯一的

    (2)两次写
    避免在处理事务时候发生宕机,虽然说事务操作可以日志恢复,但是如果宕机的时候完全删除当前页,如果,doubleWrite,一部分为内存中doublewrite buffer ,大小为2M,另一部分物理磁盘表空间中连续的128个页。缓冲池的脏页刷新的时候,首先会刷新到doublewrite buffer中,之后才会写入到各个表空间文件,遇到宕机,只需要从共享空间doublewrite中找到该页的副本恢复,之后重做日志
    (3)自适应hash索引
    InNoDB会监控对表上的各索引页查询,如果观察到建立hash索引可以带来速度提升,则建立hash索引,自动根据访问评率和模式来自动为某些热点也建立索引
    (4)异步io
    AIO可以将多个IO请求进行IO Merge,当一个IO请求发出后,可以立即发出另一个IO请求,当所有IO请求发送完毕,等待IO操作完成

    3、全文索引

    使用倒排索引来实现,两种表现形式:
    (1)inverted file index{单词,所在文档id}
    (2)full inverted index{单词,( 单词所在文档,在具体文档中的位置)}

    4、锁

    1、Recored Lock:当个行记录上锁
    2、Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
    3.Next-Key Lock:锁定一个范围,并且锁定记录本身
    4、InNoDB存储影青通过通过Next-Key Locking 来避免幻读问题

    五、组合索引(覆盖索引)

    基于多个字段创建的索引就是组合索引。

    组合索引规则:
    1、最左原则:索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。(电话簿中利用姓名查找人,姓和名分别是不同的列,知道姓电话簿有用,知道姓知道名电话簿有用,知道名不知道姓电话簿无用)

    补充:
    key_len:EXPLAIN执行计划中有一列 key_len用于表示本次查询中,所选择的索引长度有多少字节,通常我们可借此判断联合索引有多少列被选择了。

    1、index Key:索引数据范围

    • 下边界
      MySQL利用=、>=、> 来确定下边界(first key),利用最左原则,首先判断第一个索引键值在where条件中是否存在,如果存在,则判断比较符号,如果为(=,>=)中的一种,加入下边界的界定,然后继续判断下一个索引键,如果存在且是(>),则将该键值加入到下边界的界定,停止匹配下一个索引键;如果不存在,直接停止下边界匹配。
    • 上边界
      上边界(last key)和下边界(first key)类似,首先判断是否是否是(=,<=)中的一种,如果是,加入界定,继续下一个索引键值匹配,如果是(<),加入界定,停止匹配

    2、Index Filter :用于过滤索引查询范围中不满足查询条件的记录

    Index Filter的提取规则:同样从索引列的第一列开始,检查其在where条件中是否存在:若存在并且where条件仅为 =,则跳过第一列继续检查索引下一列,下一索引列采取与索引第一列同样的提取规则;若where条件为 >=、>、<、<= 其中的几种,则跳过索引第一列,将其余where条件中索引相关列全部加入到Index Filter之中;若索引第一列的where条件包含 =、>=、>、<、<= 之外的条件,则将此条件以及其余where条件中索引相关列全部加入到Index Filter之中;若第一列不包含查询条件,则将所有索引相关条件均加入到Index Filter之中。

    以上index Key和Index Filter都是通过索引列来实现的,而Table Filter是针对非索引列

    3、Table Filter :无法使用索引过滤,使用表过滤

    提取规则:所有不属于索引列的查询条件,均归为Table Filter之中。

    where索引过程:

    图片转载自:http://www.fordba.com/spend-10-min-to-understand-how-mysql-use-index.html

    在这里插入图片描述

    六、索引失效

    1、在where后使用or,导致索引失效(尽量少用or)
    2、使用like ,like查询是以%开头,以%结尾不会失效
    3、不符合最左原则
    4、如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
    5、 使用in导致索引失效
    6、使用mysql内部函数导致索引失效,可能会导致索引失效。
    7、如果MySQL估计使用索引比全表扫描更慢,则不使用索引

    七、索引Demo

    CREATE TABLE table_name[col_name data type] [unique|fulltext][index|key][index_name](col_name[length])[asc|desc]
    
    
    • unique|fulltext为可选参数,分别表示唯一索引、全文索引
    • index和key为同义词,两者作用相同,用来指定创建索引
    • col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择
    • index_name指定索引的名称,为可选参数,如果不指定,默认col_name为索引值
    • length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度
    • asc或desc指定升序或降序的索引值存储
    • 创建数据库并添加数据
    CREATE TABLE `students` (
      `stud_id` int(11) NOT NULL,
      `name` varchar(50) NOT NULL,
      `email` varchar(50) NOT NULL,
      `phone` varchar(30) NOT NULL,
      `create_date` date DEFAULT NULL,
      `content` text NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    INSERT INTO `students` (`stud_id`, `name`, `email`, `phone`, `create_date`, `content`) VALUES
    (1, 'admin', 'student1@gmail.com', '18729902095', '1983-06-25', 'i am robin'),
    (2, 'root', '74298110186@qq.com', '2', '1983-12-25', 'i am xiaoluo'),
    (3, '110', '7429811086@qq.com', '3dsad', '2017-04-28', 'i am lili');
    

    1、创建普通索引

    • 查看索引show INDEX from students
    • 创建索引方式

    1.直接创建:CREATE INDEX index_name ON student(name);
    2、修改表结构方式创建索引:alter TABLE students ADD INDEX index_name2(name);
    3、创建表的时候建立索引:添加:index index_name3(name)
    创建后的索引:
    在这里插入图片描述
    查看是否通过索引能够获取数据:EXPLAIN SELECT name FROMstudentsWHERE name='admin'
    通过索引获取数据:
    在这里插入图片描述

    -删除索引: DROP INDEX index_name ON table_name;
                        alter table表名drop index 索引名;

    2、创建唯一索引(Index前边添加unique)

    1、创建邮箱索引:

    CREATE UNIQUE INDEX index_email on students(email);
    ALTER TABLE students ADD UNIQUE INDEX index_mail2(email);
    创建表的时候添加:UNIQUE index_name_unique(email)
    

    3、创建主键索引

    主键索引是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引:
    1、创建邮箱索引:

    ALTER TABLE students ADD PRIMARY KEY(stud_id);
    创建表的时候添加:primary key(‘stud_id’)

    4、创建全文索引

    1、创建全文索引
    创建表的时候添加:fulltext(content)
    CREATE FULLTEXT INDEX index_content on students(content);
    ALTER table students ADD FULLTEXT INDEX index_content2(content);
    2、show index from students;
    在这里插入图片描述
    3、全文索引查询:

    SELECT * FROM `students` WHERE MATCH(`content`) against('robin')
    

    在这里插入图片描述
    4、建立一个联合索引
    添加另外一个全文索引:ALTER TABLE students ADD FULLTEXT INDEX email_content(email,content);
    联合全文索引:SELECT * FROMstudentsWHERE MATCH(email,content) against('student robin')
    在这里插入图片描述

    八、引用博客

    【1】实战demo:https://blog.csdn.net/u010648555/article/details/81102957
    【2】B树与B+树:https://blog.csdn.net/endlu/article/details/51720299
    【3】聚合索引与非聚合索引概念:https://blog.csdn.net/tongdanping/article/details/79878302
    【4】InNoDB与MyISAM差别:https://www.cnblogs.com/y-rong/p/8110596.html
    【5】组合索引:http://www.fordba.com/spend-10-min-to-understand-how-mysql-use-index.html
    【6】Mysql如何利用索引,通过组合索引:http://hedengcheng.com/?p=577
    图片:http://www.fordba.com/spend-10-min-to-understand-how-mysql-use-index.html
    【7】索引使用规则;https://www.cnblogs.com/duanxz/p/5244703.html

    展开全文
  • mysql索引详解

    千次阅读 2018-08-01 20:23:29
    首先Mysql的基本存储结构是页(记录都存在页里边):     各个数据页可以组成一个双向链表 而每个数据页中的记录又可以组成一个单向链表 每个数据页都会为存储在它里边儿的记录生成一个页目录,在通过主键...

    首先Mysql的基本存储结构是(记录都存在页里边):

    Java编程——数据库两大神器:索引和锁

     

    Java编程——数据库两大神器:索引和锁

     

    • 各个数据页可以组成一个双向链表
    • 每个数据页中的记录又可以组成一个单向链表
    • 每个数据页都会为存储在它里边儿的记录生成一个页目录,在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录
    • 其他列(非主键)作为搜索条件:只能从最小记录开始依次遍历单链表中的每条记录

    所以说,如果我们写select * from user where username = 'Java3y'这样没有进行任何优化的sql语句,默认会这样做:

    • 定位到记录所在的页
    • 需要遍历双向链表,找到所在的页
    • 从所在的页内中查找相应的记录
    • 由于不是根据主键查询,只能遍历所在页的单链表了

    很明显,在数据量很大的情况下这样查找会很慢

    1.2索引提高检索速度

    索引做了些什么可以让我们查询加快速度呢?

    其实就是将无序的数据变成有序(相对)

    Java编程——数据库两大神器:索引和锁

     

    要找到id为8的记录简要步骤:

    Java编程——数据库两大神器:索引和锁

     

    很明显的是:没有用索引我们是需要遍历双向链表来定位对应的页,现在通过**“目录”**就可以很快地定位到对应的页上了!

    其实底层结构就是B+树,B+树作为树的一种实现,能够让我们很快地查找出对应的记录。

    参考资料:

    • Mysql索引

    1.3索引降低增删改的速度

    B+树平衡树的一种。

    平衡树:它是一棵空树或它的左右两个子树的高度差的绝对值不超过1,并且左右两个子树都是一棵平衡二叉树。

    如果一棵普通的树在极端的情况下,是能退化成链表的(树的优点就不复存在了)

    Java编程——数据库两大神器:索引和锁

     

    B+树是平衡树的一种,是不会退化成链表的,树的高度都是相对比较低的(基本符合矮矮胖胖(均衡)的结构)【这样一来我们检索的时间复杂度就是O(logn)】!从上一节的图我们也可以看见,建立索引实际上就是建立一颗B+树。

    • B+树是一颗平衡树,如果我们对这颗树增删改的话,那肯定会破坏它的原有结构
    • 要维持平衡树,就必须做额外的工作。正因为这些额外的工作开销,导致索引会降低增删改的速度

    B+树删除和修改具体可参考:

    • www.cnblogs.com/wade-luffy/…

    1.4哈希索引

    除了B+树之外,还有一种常见的是哈希索引。

    哈希索引采用的结构和hashMap一样。数组+链表的形式。如果hash冲突大,性能也就越来越差。

    • 本质上就是把键值换算成新的哈希值,根据这个哈希值来定位

    Java编程——数据库两大神器:索引和锁

     

    看起来哈希索引很牛逼啊,但其实哈希索引有好几个局限(根据他本质的原理可得):

    • 哈希索引是无序的所以不能用于排序order by,group by 后面
    • 由于存的不是索引值,而是映射而成的hash值,所以也不支持范围查找,前缀匹配和联合索引的最左匹配原则(要全匹配)。

    1.5InnoDB支持哈希索引吗?

    主流的还是使用B+树索引比较多,对于哈希索引,InnoDB引擎会根据索引值使用的频繁内部自动的在B+Tree索引上创建哈希索引,用户无法控制或者配置,不过可以关闭该优化特性。

    1.6聚集和非聚集索引

    简单概括:

    • 聚集索引就是以主键创建的索引
    • 非聚集索引就是以非主键创建的索引

    区别:

    • 聚集索引在叶子节点存储的是表中的数据
    • 非聚集索引在叶子节点存储的是主键和索引列
    • 使用非聚集索引查询出数据时,拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

    非聚集索引也叫做二级索引

    非聚集索引在建立的时候也未必是单列的,可以多个列来创建索引。

    • 此时就涉及到了哪个列会走索引,哪个列不走索引的问题了(最左匹配原则-->后面有说)
    • 创建多个单列(非聚集)索引的时候,会生成多个索引树(所以过多创建索引会占用磁盘空间)

     

    在创建多列索引中也涉及到了一种特殊的索引-->覆盖索引

    • 我们前面知道了,如果不是聚集索引,叶子节点存储的是主键+列值
    • 最终还是要“回表”,也就是要通过主键查找一次。这样就会比较慢
    • 覆盖索引就是把要查询出的列和索引是对应的,不做回表操作!

    比如说:

    • 现在我创建了索引(username,age),在查询数据的时候:select username , age from user where username = 'Java3y' and age = 20。
    • 很明显地知道,我们上边的查询是走索引的,并且,要查询出的列在叶子节点都存在!所以,就不用回表了~
    • 所以,能使用覆盖索引就尽量使用吧~

    1.7索引最左匹配原则

    最左匹配原则

    • 索引可以简单如一个列(a),也可以复杂如多个列(a, b, c, d),即联合索引
    • 如果是联合索引,那么key也由多个列组成,同时,索引只能用于查找key是否存在(相等),遇到范围查询(>、<)等就不能进一步匹配了,后续退化为线性查找。
    • 因此,列的排列顺序决定了可命中索引的列数

    例子:

    • 如有索引(a, b, c, d),查询条件a = 1 and b = 2 and c > 3 and d = 4,则会在每个节点依次命中a、b、c,无法命中d。(很简单:索引命中只能是相等的情况,不能是范围匹配)

    1.8=、in自动优化顺序

    不需要考虑=、in等的顺序,mysql会自动优化这些条件的顺序,以匹配尽可能多的索引列。

    例子:

    • 如有索引(a, b, c, d),查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的,MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4,依次命中a、b、c。

    1.9索引总结

    索引在数据库中是一个非常重要的知识点!上面谈的其实就是索引最基本的东西,要创建出好的索引要顾及到很多的方面:

    • 1,最左前缀匹配原则。这是非常重要、非常重要、非常重要(重要的事情说三遍)的原则,MySQL会一直向右匹配直到遇到范围查询(>,<)就停止匹配,比如: a = 1 AND b = 2 AND c > 3 AND d = 4,如果建立 (a,b,c,d)顺序的索引,c,d是用不到索引的,如果建立(a,b,d,c)的索引,则都可以用到,a,b,d的顺序可以任意调整。
    • 3,尽量选择区分度高的列作为索引,区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率,比率越大我们扫描的记录数就越少。
    • 4,索引列不能参与计算,尽量保持列“干净”。比如,FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引,原因很简单,B+树中存储的都是数据表中的字段值,但是进行检索时,需要把所有元素都应用函数才能比较,显然这样的代价太大。所以语句要写成 : create_time = UNIX_TIMESTAMP('2016-06-06')。
    • 5,尽可能的扩展索引,不要新建立索引。比如表中已经有了a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
    • 6,单个多列组合索引和多个单列索引的检索查询效果不同,因为在执行SQL时,MySQL只能使用一个索引,会从多个单列索引中选择一个限制最为严格的索引。

    根据上面这些原则,我们来修改开篇的慢查询:

    
    SELECT 
    count(*) AS count 
    FROM trade_bASe AS a
    WHERE 
    a.trade_status = 7 
    AND a.create_time > '2015-09-01' 
    AND a.booking_source = '2'

    根据这条SQL,应该建立的索引是:trade_status, booking_source,create_time的联合索引;其中,trade_status、booking_source的顺序可以颠倒,而且 create_time 的区间查询放到后面。这就是利用了索引的最左匹配原则。

    索引的优化方法

    1,尽量使用组合索引,因为即使查询语句中有多个单列索引,但是优化器会只选择一个它认为最优的索引路径查询。

    2,索引不会包含有NULL值的列:只要列中包含有NULL值,都将不会被包含在索引中,组合索引中只要有一列有NULL值,那么这一列对于此条组合索引就是无效的。所以我们在数据库设计时,不要让索引字段的默认值为NULL。

    3,对于指比较长的列可以创建前缀索引,但是前缀索引无序无法用做orderby 和groupby和覆盖扫描。:假设,如果有一个数据类型为CHAR(255)的city列,在前10个字符内,绝大部分数据的值是唯一的,那么就不要对整个列进行索引,创建alter table t_demo add key(city(10));字段长度为10的city索引。

    4,对于指比较长的列如url还可以使用伪哈希索引的方式。增加一列url对应的hash整型值。维护该列可以使用触发器实现。注意:因为会出现hash冲突,所以根据整型hash列查询的时候都要and 具体值。如:select id from urlTable where url_crc=CRC32(“www.baidu.com”) and url="www.baidu.com"

    5,LIKE语句操作:LIKE "%aaaaa%"不会使用索引,但是LIKE "aaa%"可以使用索引。结合B+树查找规则理解

    6,不要在索引列上进行运算:在建立索引的原则中,提到了索引列不能进行运算。

    7,使用覆盖索引,索引包含需要查询的字段的值。

    8,尽可能将需要做范围(>,<)查询的列放在索引后面,因为优化器会放弃使用索引。注意:in 和 between 是可以使用索引的,它们相当于等值查询。

    最优的索引符合三星系统原则
    1.索引将相关记录放在一起获得一星
    2.索引中数据顺序和查询中的排序一致则获得二星
    3.覆盖索引获得三星

    Explain命令

    explain select * from t_user;

    列值的含义

    possable_key:可能会用到的索引。

    key:表示使用到的索引

    展开全文
  • MYSQL索引详解

    万次阅读 多人点赞 2018-06-11 01:12:22
    本文优化并补充了大佬的文章:https://www.cnblogs.com/chenshishuo/p/5030029.html索引的定义(索引别称index,key,键)在关系数据库中,索引是对表中一列或多列的值进行排序的一种存储结构,它是表中一列或多列的...

    本文优化并补充了大佬的文章:https://www.cnblogs.com/chenshishuo/p/5030029.html

    索引的定义(索引别称index,key,键)

    在关系数据库中,索引是对表中一列或多列的值进行排序的一种存储结构,它是表中一列或多列的值的集合,而且其中包含了对应表中记录的引用指针。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。

    要注意的是,索引也是表的组成部分,建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立索引。

    举个例子:首先,先假设有一张表,表有10W个记录,其中有一条记录我们已知a='1',如果想要拿到对应记录的话,需要的sql语句是 SELECT * FROM xxx WHERE a='1'.

    一般情况下,对于查询语句,在没有建立索引的时候,mysql会进行全表扫描,而且不扫描完10W个记录不会停止,如果我在nickname上建立索引,那么mysql相当于只扫描nickname这一列即可,而且因为这一列已排好序,找到对应结果或结果集可以直接返回。

    mysql的索引分为单列索引(全文索引,主键索引,唯一索引,普通索引)和组合索引。
    单列索引:一个索引只包含一个列,一个表可以有多个单列索引。
    组合索引:一个组合索引包含两个或两个以上的列,


    (一)索引的创建

    1.单列索引

    1-1)    普通索引(这个是最基本的索引)

    建表时:INDEX IndexName(`字段名`(length)) 

    建表后:CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 

    或ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length)

    注意:如果字段数据是CHAR,VARCHAR类型,可以指定length,其值小于字段的实际长度,如果是BLOB和TEXT类型就必须指定length。

    这个length的用处是什么?

    有时候需要在长文本字段上建立索引,但这种索引会增加索引的存储空间以及降低索引的效率,这时就可以用到length,创建索引时用到length的索引,我们叫做前缀索引,前缀索引是选择字段数据的前n个字符作为索引,这样可以大大节约索引空间,从而提高索引效率。

    此处展示的语句用于创建一个索引,索引使用字段数据的前10个字符。
    CREATE INDEX part_of_name ON customer (name(10));

    使用字段数据的一部分创建索引可以使索引文件大大减小,从而节省了大量的磁盘空间,有可能提高INSERT操作的速度。

    前缀索引是一种能使索引更小,更快的有效办法,但是MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖扫描。

    这里又引出了一个新概念,覆盖扫描!

    如果一个索引(如:组合索引)中包含所有要查询的字段的值,那么就称之为覆盖索引,如:

    SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;
    因为要查询的字段(user_name, city, age)都包含在组合索引的索引列中,所以就使用了覆盖索引查询,查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引,覆盖索引可以极大的提高访问性能。


    1-2)    唯一索引,要求字段所有的值是唯一的,这一点和主键索引一样,但是允许有空值。

    建表时:UNIQUE INDEX IndexName(`字段名`(length)) 

    建表后:CREATE UNIQUE  INDEX IndexName ON `TableName`(`字段名`(length)) 

    或ALTER TABLE TableName ADD UNIQUE  INDEX IndexName(`字段名`(length))


    1-3)    主键索引,不允许有空值

    一般在建表的时候自动创建,主键一般会设为 int 而且是 AUTO_INCREMENT自增类型的


    1-4)全文索引
    假设字段的数据类型是长文本,文本字段上(text等)建立了普通索引,我们需要查找关键字的话,那么其条件只能是where column like '%xxxx%' ,但是,这样做就会让索引失效,这时就需要全文索引了。

    建表时:FULLTEXT INDEX IndexName(`字段名`(length)) 

    建表后:CREATE FULLTEXT  INDEX IndexName ON `TableName`(`字段名`(length)) 

    或ALTER TABLE TableName ADD FULLTEXT  INDEX IndexName(`字段名`(length))

    使用:
    SELECT * FROM TableName
    WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)
    这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

    下面我们来举个例子:

    假设有一个书籍表,结构如下,文章内容字段的数据类型是text

    文章id文章标题文章内容
    1超级塞亚人 我是超级塞亚人我喜欢吃苹果,我不是天朝的人,也不是地球人
    2天朝大国我大天朝威武,我大天朝13亿人,我大天朝
    3我喜欢游泳游泳有很多好方法
    4动画片我儿子喜欢看动画片,尤其是七龙珠,因为里面有塞亚人,而且塞亚人喜欢吃苹果,他们不是地球人
    5运动我喜欢运动,喜欢跑步,喜欢游泳,喜欢健身,喜欢xxoo
    6打炮我是一个二战的老兵,这是我的回忆录,我最幸福的时光就是在天朝吃着苹果打炮
    7.......... ..........
    8.......... ..........
    9.......... ..........

    我想在茫茫多书籍的内容里搜索关键词,如果用%xxx%搜索,那效率就太低了。

    我们在文章内容字段上建立全文索引,下面是索引文件

    关键词文章id(引用指针)
    塞亚人1,4
    苹果1,4,6
    天朝1,2,6
    地球1,4
    游泳3,5
    七龙珠4
    喜欢1,4,5,6

    那么当我想搜索  “塞亚人”的时候,这个索引文件直接告诉我在文章id为1和4的文章里有这个词。

    可是这些关键词是如何提取出来的呢?这就是要提到一个新概念,“分词”!分词就是提取关键词,但是MYSQL的FULLTEXT对分词不够智能,对中文也不是很支持,所以我们一般不用全文索引。取而代之的是:

    coreseek=sphinx+mmesg 这个程序就可以解决这个问题的啦。

    sphinx就是索引程序。

    mmseg就是分词程序。

    国内有人修改了sphinx源码,内建和mmseg配合,整合到一起就是coreseek啦(中文版sphinx)!


    2.组合索引

    假设字段a,b都有索引,我们的查询条件是a=1,b=2查询过程是mysql会先挑选出符合a=1的结果集,再在这些结果集中挑选b=2的结果集,但是mysql并不会在查询a,b时都用到索引,只会用其中一个,这和我们的预期不一样,所以,我们要使用组合索引

    建表时:INDEX IndexName(`字段名`(length),`字段名`(length),........) 

    建表后:CREATE INDEX IndexName ON `TableName`(`字段名`(length),`字段名`(length),........) 

    或ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length),`字段名`(length),........) 


    (二)索引的删除

    DORP INDEX IndexName ON `TableName`

    (三)索引失效的情况 

     1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

      要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引

      2.使用查询的时候遵循mysql组合索引的"最左前缀"规则,假设现在有组合索引(a,b,c),查询语句就只能是a=1或a=1

    and b=1或a=1 and b=1 and c=1。这里有两点需要注意①a=1 and b=1和b=1 and a=1一样,没有区别,都会使用索引②组合索引(a,b,c)的最左前缀是a;组合索引(c,b,a)的最左前缀是c,最左前缀和表字段顺序无关

    在组合索引中,如果where查询条件中某个列使用了范围查询(不管%在哪),则其右边的所有列都无法使用索引优化查询


      3.like查询以%开头

      4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

      5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

            6.索引列不能是表达式的一部分,也不能作为函数的参数,否则无法使用索引查询。下面是例子:


    1
    SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');



    
    展开全文
  • MySQL索引详解大全

    2021-02-01 15:28:01
    索引由数据库中一列或多列组合而成,其作用是提高对表中数据的查询速度索引的优点是可以提高检索数据的速度索引的缺点是创建和维护索引需要耗费时间索引可以提高查询速度,会减慢写入速度1.普通索引2.唯一索引3.全文...
  • MySQL索引详解

    千次阅读 2019-12-28 12:25:59
    MySQL索引详解 一. 索引简介 索引:帮助MySQL高效查询数据的一种有序的数据结构。 如果没有索引,查询某行数据,只能进行全表扫描。这时,需要频繁地进行磁盘IO,性能很差。 索引一般是一个key-value结构,...

    MySQL索引详解

    一. 索引简介

    1. 索引:帮助MySQL高效查询数据的一种有序的数据结构。

    在这里插入图片描述

    1. 如果没有索引,查询某行数据,只能进行全表扫描。这时,需要频繁地进行磁盘I/O,性能很差。索引的基本思想,就是减少一次查询所产生的磁盘I/O次数,提升查询效率。

    2. 索引一般是一个key-value结构,key是索引值。

      对于聚集索引(如InnoDB的主键索引),value是该行的所有数据。

      对于非聚集索引(如MyISAM索引),value是该行数据所在的磁盘块的指针。

    二. 常用的索引数据结构

    1. 二叉树(非平衡二叉树)

      弊端:无法保证平衡性。极端情况下,可能退化成链表。此时,查询约等于全表扫描。

    2. 红黑树(平衡二叉树)

      1. 优势:平衡树,不会退化成链表,相较于非平衡二叉树,查询效率有一定提升。
      2. 弊端:当数据量较大时,树的高度不可控,可能导致磁盘IO次数较多,效率下降。
      3. 优化思路:让一个树的节点存储更多的索引元素,从而降低树的高度。
    3. B树

    在这里插入图片描述

    1. 特性:

      1. 树的每个节点,存储多个索引元素,同时存储索引对应的数据。
      2. 叶节点具有相同的深度,叶节点的指针为空。
      3. 所有索引元素不重复。
      4. 节点中的数据索引从左到右递增排列。
    2. 弊端:

      1. 树的所有节点(包括叶子节点和非叶子节点)都同时存储索引和数据,导致每个索引元素所占空间较大。当树的节点空间一定时,每个节点保存的索引元素数量就较少,最终导致树的高度较高。

      2. 树的每个节点的大小是固定的,一般为一页(Page)16KB。可通过命令查看:

        show global status like ‘Innodb_page_size’;

    3. 优化思路:尽可能减少每个索引元素所占的空间大小,使得每个树节点可以存储更多的索引元素,从而减小树的高度。

    4. B+树
      在这里插入图片描述

      1. 特性:
        1. 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引。
        2. 叶子节点包含所有索引字段和对应的数据。
        3. 节点中的数据索引从左到右递增排列。
        4. 叶子节点用双向指针连接,提高区间访问的性能。
      2. 优势:
        1. 树高度较矮,针对大多数的表,2~4层即可满足需求。
        2. 区间访问性能较好。
    5. Hash
      在这里插入图片描述

      1. 特性:对索引值进行hash,映射成对应数据行所在的磁盘文件指针。
      2. 弊端:
        1. 不支持范围查询。
        2. 不支持模糊查询。
        3. 不支持排序。
        4. 哈希冲突问题。
      3. 适用场景:大量等值查询时。

    三. 存储引擎下的索引实现

    存储引擎的粒度是表级别的。同一个数据库下的不同表,可以使用不同的存储引擎。

    1. MyISAM引擎

    在这里插入图片描述

    1. MyISAM为聚集索引,索引文件和数据文件分离,索引数据保存的是对应行所在的磁盘文件指针。

    2. MyISAM使用3个文件保存数据:

      1. .frm:保存表的定义、结构等元信息。
      2. .MYD:保存表中的所有数据行。
      3. .MYI:保存表中的所有索引字段。
    3. InnoDB引擎

    在这里插入图片描述

    在这里插入图片描述

    1. 在 InnoDB 中,表都是根据主键的顺序,以索引的形式存放的,这种存储方式的表称为索引组织表
    2. InnoDB 的主键索引为聚集索引,索引的叶子节点保存的是该行的所有数据。
    3. InnoDB 使用2个文件保存数据:
      1. .frm:保存表的定义、结构等元信息。
      2. .ibd:同时保存InnoDB的数据和索引。
    4. 对于主键索引,叶子节点的内容是所在行的所有数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。
    5. 对于非主键索引,叶子节点的内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。
    6. 基于非主键索引的查询时,需要根据查询到的主键值,再去主键索引查询一次记录,这个过程称为回表。回表会导致多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

    四. 联合索引

    在这里插入图片描述

    1. 联合索引的所有列,按照从左到右的顺序构成一个节点,保存在B+树中。
    2. 联合索引的最左前缀原则:联合索引是按照索引列的顺序,从第一列开始进行排序的。如果查询条件跳过了第一列,那么其实是无序的,就无法走索引,只能全表扫描。

    五. 相关问题

    1. 为什么 InnoDB 表必须有主键,且推荐整型的自增主键?

      ** InnoDB 的表数据文件(.ibd),就是按照B+树结构,根据主键索引组织起来的一个索引结构文件,因此一定要有一个主键。如果用户没有自定义主键,InnoDB会选择一列唯一索引作为主键。如果没有唯一索引,InnoDB 会为每行数据生成一个唯一的整型自增数值rowId(隐藏列),作为主键来组织整个索引文件。**

      使用整型主键,索引查询时,比较效率较高。且整型字段所占空间较小。

      使用自增主键,大部分的插入操作,都是在叶子节点链表上的addLast,不会涉及到节点的页分裂和整棵树的平衡操作,插入效率很高。

    2. 为什么 InnoDB 的的非主键索引,存储的是主键索引的值,而不是像主键索引一样直接存储数据?

      1. 数据一致性角度:如果数据在多个索引处维护,那么就存在数据一致性问题。插入一条记录时,需要在每个索引树上都插入一遍,就涉及到了分布式事务的问题。
      2. 存储空间角度:如果所有索引树都保存数据,会造成大量的空间浪费。
    3. 在 InnoDB 引擎下,重建主键索引,无论是新增还是删除,都会导致整张表进行重建。可以使用 alter table T engine=InnoDB 来重建主键索引。

    展开全文
  • Mysql索引详解及基本用法

    千次阅读 2018-09-05 21:11:13
    MySQL索引的概念&nbsp; &nbsp; 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快...
  • Mysql覆盖索引详解

    2020-12-16 10:53:17
    使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询 注意 1、覆盖索引也并不适用于任意的索引类型,索引必须存储列的值 2...
  • MySQL 索引详解

    千次阅读 2021-10-22 17:28:17
    目录引言一、数据库索引1. 索引的概念2. 索引的作用3. 索引的分类4. 创建索引的原则依据 引言 在企业信息化的过程中,数据库中表的数据量越来越大,性能会急剧下降,创建索引对于保持良好的性能非常关键。 索引是对...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 34,944
精华内容 13,977
关键字:

mysql索引详解

mysql 订阅