mysql数据库-索引基础篇

1.索引介绍

1. 1.索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），他们包含着对数据表里所有记录的引用指针。是一种数据结构是一种数据库系统中排序的数据结构，以协助快速查询，更新数据库中表数据，一般实现通常使用B树及B+树。（索引相当于目录，通过对内容建立索引，方便查找。它占用物理空间，以空间换时间）
   1.2. 索引的优点：大大加快数据的检索速度，使用索引可以在查询的过程中使用优化隐藏器，提高系统性能
2. 3. 索引的缺点：创建索引和维护索引需要时间，对表中的数据进行增加，删除和修改时，索引也要动态维护，会降低增删改的效率。还会占用物理空间
      2.索引使用场景
      EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈。
      使用方式：EXPLAIN + SQL语句
      执行结果包含信息
      id：查询的序列号，包含一组数字，表示查询的执行子句或者操作表的顺序 ，id越大优先级越高，先被执行
      select_type: 表示查询的类型，主要是区别普通查询，联合查询，子查询等复杂查询。
      SIMPLE 简单的查询，不含子查询或者UNION
      PRIMARY 查询中包含任何复杂的子部分。最外层查询被标记为PRIMARY
      SUBQUERY 在select或者where列中包含子查询
      DERIVED 在from列表中包含子查询被标记DERIVED(衍生)，mysql会递归执行这些子查询，把结果放在临时表
      UNION 若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层 select将被标记为：DERIVED
      UNION RESULT 从UNION 表获取结果的select
      table：执行的表
      type: 查询使用的类型,最好到最差（system > const > eq_ref > ref > range > index > all）
      system：只有一行记录（系统表），这是从const类型的特例，平时不会出现，可以忽略
      const：通过索引一次找到了，const用于比较 primary key 或者unique索引，因为只匹配一行数据，所以快，将主键置于where列表中，MYSQl就能查询转换一个常量。首先进行子查询得到一个结果的临时表，子查询条件为id=1是常量，所以typeconst，id为1相当于只查询一天记录，所以type为system。
      eq_ref 唯一性索引扫描。对于每个索引键，表中只有一条记录与之匹配。一般为主键或唯一索引扫描
      ref 非唯一性索引扫描。返回匹配某个单独值的所有行，本质是一种索引访问，返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的集合。
      range 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where 语句中出现between，<,>，in等查询，这种范围扫描要索引比全表扫描要好，因为它只需要开始于索引的某一点吗，而结束于另一点，不用扫描全部索引。
      index Full index Scan.index与all的区别就是index类型只遍历索引树，这通常比all要快，因为索引文件通常比数据文件小。虽然all和index都是全表扫描，但是index是从索引读取，而all是从磁盘读取。
      possible_keys: 显示可能应用到这张表的索引，有一个或者多个；查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询实际用到
      key:实际使用的索引。如果该值为null,则没有使用索引；如果查询中使用了覆盖索引则该索引出现在key列表中。
      覆盖索引：如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。select后面的字段都具备索引，提高查询效率，提前顺序，个数都要一致。如果使用覆盖索引，一定要注意select列表中只取需要的列，不可使用select ，因为如果将所以字段一起做索引会导致索引文件过大，降低查询效率。
      ken_len 表中索引中使用的字节数，可以通过该列计算查询中使用索引的长度，在不损失的精度性情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度。并非实际使用长度，即key_len是根据表计算定义而得，不是通过表内检索出。
      ref 显示索引的那一列被使用了。如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值；
      rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数
      Extra （前三是最重要的指标）（1）Using filesort: 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，msql中无法利用索引完成的排序操作称为“文件排序”；（2）Using temporary 使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常出现在order by排序和group by分组查询中；（3）Using index:表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率比较高。如果同时出现Using where,表明索引被用来执行索引键值的查找，如果没有同时出现Using where，表明引用来读取而feud执行查找动作；（4）Using where:表示查询使用了where条件；（5）Using join buffer:表示查询使用了连接缓存; (6)impossile where: where子查询的值总是false,不能用来获取任何元素；（7）select tables optimized away: 在没有group by 子句的情况下，基于索引优化min,max操作或者对于MyISAM存储优化count（）操作，不必到执行阶段在进行计算，查询执行计划生成的阶段即完成优化；（8）ditinct: 优化ditinct操作，在找到第一匹配的元素即停止找到同样的动作；
      order by 将查询结果按照某字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内存排序，最后合并排序结果），这个操作很影响性能，因为需要将查询涉及到的所有数据都从硬盘读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序。但是我们对该字段建立索引，alter table 表名 add index（字段名），那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而如果分页，那么只用索引表某个范围内的索引对应的数据，而不用像上述那取出所以数据进行排序再返回某个范围内的数据。
      join 对join 语句匹配关系（on）涉及的字段建立所以能提高效率。
      3.索引的类型
      主键索引：数据列不能重复，不能为null，一个表只能有一个主键。
      唯一索引: 数据列不能重复，可以为null，一个表有多个列可以创建唯一索引。
      alter table 表名 add UNIQUE(列名);创建唯一索引
      alter table 表名 add UNIQUE(列名1，列名2);创建唯一组合索引。
      普通索引：基本的索引类型，，没有唯一要求，可以为null。
      alter table 表名 add index index_name(列名);创建普通索引
      alter table 表名 add index index_name(列名1，列名2…)创建组合索引
      全文索引：搜索引擎使用的一种关键技术ALTER TABLE 表名 ADD FULLTEXT (列名);创建全文索引。
      3.索引的数据结构（b树，hash）
      索引的数据结构和具体存储引擎的实现有关，在mysql中使用比较多的索引有hash索引，b+数索引等。而我们经常使用的InnoDB存储引擎默认索引实现为:B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快，。其余大部分场景，建议BTree索引。
      3.1 B树索引
      mysql通过存储引擎数据，按照实现方式分，InnoDB的索引类型目前只有两种：BTREE(B树)索引和hash索引。B树索引是mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持Btree索引，通常我们说的索引不出意外指的B树索引实际上使用B+树实现的）
      查询方式：主键索引区：PI（关联保存的时数据的地址）按主键查询
      普通索引区：si(关联的id地址，然后再到达上面的地址)。所以按主键查询，速度最快。
      3.2 B+Tree性质：1.n颗树子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。2.所有叶子结点包含了全部关键字的信息，及指向这些关键字记录的指针且叶子结点本身依关键字的大小自小而大的顺序链接。3.所以的非终端结点可以看成索引部分，结点中仅含其子树中的最大或最小关键字。4.B+树中，数据对象的插入和删除仅在叶节点上进行。5.B+树有两个头指针，一个是树的根节点，一个是最小关键码的叶节点。
      3.3 哈希索引：类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法，平方取中法，折叠发，除数取余法，随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（不同关键字Hash值相同），则在对应Hash键下以链式形式存储。
      3.4索引的基本原理: 索引用来快速的寻找那些具有特定值的记录，如果没有索引。一般来说执行查询时遍历整张表。
      索引就是把无序的数据变成有序的查询1.把创建了索引的列的内容进行排序2.对排序结果生成倒排表3.在倒排表内容上拼上数据地址链4.在查询时先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
      索引算法：Btree算法是最常用的mysql数据库索引算法，也是默认算法，因为它不仅可以被用在=，>,>=,<,<=和between这些比较操作符。而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，则会使用索引。一个以通配符开头，或者没有使用常量，则不会使用索引。
      hash算法：Hash索引只能用于对等比较，=，<=>操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到叶节点这样多次IO访问，所以检索效率远高于BTree索引。
      3.5索引的设计原则：1.适合索引的列说过是出现在where子句中的列，或者链接子句中指定的列。2.基数较小的类，索引效果差，没有必要在此列建立索引3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。4.不要过度索引，索引需要额外的磁盘空间，并降低写操作的性能，在修改表内容时，索引会进行更新甚至重构，索引列越多这个时间就会越长，所以只保持索引有利于查询即可。
      4.索引的创建原则
      4.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顺序可以任意调整。mysql的查询优化器会帮你优化成索引可以识别的形式。
      4.2较频繁作为查询条件的字段才去创建索引
      4.3更新频繁的字段不是和创建索引
      4.4若是不能有效区分数据的列不适合做索引列（如性别，男女未知，最多三列，识别度低）
      4.5尽量扩展索引，不要新建索引，比如表中已有a的索引，扩展为（a,b）
      4.6定义有外键的数据列一定要建立索引
      4.7对于那些查询中很少涉及的列，重复值比较多的列不要建立列
      4.8对于定义text,image和bit的数据类型的列不要建立索引。
      5.索引的创建方式
      5.1执行create table 时创建 FULLTEXT KEY(列名)
      5.2使用ALTER TABLE命令去增加索引，普通索引，唯一索引unique，主键索引PRIMARY KEY
      ALTER TABLE 表名 ADD index 索引名(列名)；
      5.3使用CREATE INDEX 命令，只能创建普通索引和UNIQUE索引，不能创建PRIMARY KEY索引。
      CREATE INDEX 索引名 on 表名（列名）
      删除索引，普通索引，唯一索引，全文索引。alter table 表名 drop key 索引名
      删除主键索引：alter table 表名 drop primary key (因为主键只有一个)这里值得注意的是如果主键自增长，那么不能直接执行此操作（自增长依赖主键索引），需要取消自增长再删除
      6.索引的注意事项
      非空字段：应该指定列NOT NULL 除非需要存储NULL,空值的列很难进行查询优化，它们使索引，索引统计信息以及比较运算更加复杂，应该用0，或者特殊值或空串替代空值。
      取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面。可以通过count（函数查看字段的差异值）返回值越大说明字段的唯一值越多字段的离散程度越高。
      索引字段越小越好：数据库的数据存储以叶为单位一页存储的数据越多IO操作获取的数据越大效率越高。
      通常通过索引查询数据比全表扫描要快，不过也要付出代价，索引需要空间存储，也需要定期维护，每当表中增减或索引列被修改时，索引本身也会被修改，意味着每条记录的INSERT,DELETE,UPDAYE将为此多付出4,5次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询（INDEX RANGE SCAN)适用于两种情况，基于一个范围的检索，一般查询返回结果小于表中记录数的30%。基于非唯一性索引的检索。
      大数据的删除：删除数据的速度和创建的索引数量成正比，所以我们要删除百万数据的时候可以先删除索引，然后删除其中无用数据，再重新创建索引也非常快，与之前直接删除绝对快很多，更别说万一删除中断，一切删除会回滚。