【MySql数据库】索引相关知识总结

索引是什么？

         索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结构。索引可以提高查询的速度。索引有两种存储类型，B型树索引和 哈希索引。InnoDB和MyISAM存储引擎支持BTREE索引，MEMORY存储引擎只会HASH索引和BTREE索引，默认为前者。

         注：索引可以提高查询的速度

为什么要索引？

         为了快速找到某一个数据

        1、InnoDB(在这个引擎中支持 事务)

                      .frm表中储存的是表的数据结构，.ibd具体数据+索引。。通过索引就能直接找到数据。。聚簇型索引

        2、MyIsan（支持事务）

                      .frm存放的是表的数据结构 .MYD存放的是数据文件 .myi 索引。。。索引到最后地址，然后还需要通过地址从数据文件里再去找数据。

索引优点：

        1.通过创建唯一索引，可以保证数据库每一行数据的唯一性

        2.可以大大提高查询速度

        3.可以加速表与表的连接

        4.可以显著的减少查询中分组和排序的时间。

索引缺点：

        1.创建索引和维护索引需要时间，而且数据量越大时间越长

        2.创建索引需要占据磁盘的空间，如果有大量的索引，可能比数据文件更快达到最大文件尺寸

        3.当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

索引的分类？

        索引是建立在某一个 或者 某几个属性上（一个，多个）：

        1、单列索引：索引一个属性

        2、联合索引：一次索引多个属性，只有使用了索引的第一个字段时才会触发索引，如果没有使用索引的第一个字段，那么久不起作用。

 

        3、普通索引：不附加任何限制条件，可以创建在任何数据类型上。

 

        4、全文索引（FULLTEXT）：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

        5、主键索引：建立在主键上的索引就叫做主键索引。（如果没有设置主键就以行号作为索引）InnoDB 所有的表都一定具有主键索引，这就是为什么通过主键去查找数据会特别的快。

        6、唯一索引：被unique修饰的 建立的索引叫做唯一索引

        7、空间索引：使用SPATIAL参数可以设置索引为空间索引，空间索引只建立在空间数据上，MySql空间数据类型有GEOMETRY，POINT，LINESTRING，POLYGON，目前只有MyISAM存储引擎支持空间索引，而且索引字段不能为空。

 

索引的设计原则，索引的优化：

          口诀：索引小而少，多用的地方多索，不用的删除

          1、选择唯一性索引

                   使用唯一字段

          2、位经常需要排序、分组和联合操作的字段建立索引

                   order by，group by，distinct，union等操作的字段，实际上是建立了一颗多路树

          3、为常作为查询条件的字段建立索引

          4、限制索引的数目

                  每个索引都需要占用磁盘空间，索引越多西药的空间就越大

          5、尽量使用数据量少的索引

          6、尽量使用前缀来索引

          7、删除不在使用或者很少使用的索引

索引效率的问题：

         多路树O( logm（N）)

索引的问题：

1、MYSQL中索引、主键和唯一性的区别是什么？

          索引建立在一个或者多个字段上，建立索引后，表中的数据就按照索引的一定规则排列，来提高查询速度。

          主键是表中数据的唯一标识，不同的记录的主键值不同。建立主键时系统自动建立唯一性索引。

          唯一性也是建立在表中一个或者几个字段上。目的是为了对于不同记录具有唯一性的字段的值不同。

2、建立索引以后，导入大量数据为什么会很慢？

         对已经建立了索引的表中插入数据时，插入一条数据就要对记录按索引排序，解决办法是，在没有任何索引的情况下插入数据，然后建立索引。

 

索引怎么使用？

 

（1）创建表 定义属性的时候

 

 

              创建普通索引：直接在后面加INDEX(id)

              创建唯一性索引：（建立一个名为ak的唯一索引）在后面加UNIQUE INDEX ak(id ASC)

              创建全文索引：char varchar text上使用

 

              创建单列索引：表的单个字段建立索引

 

                                         （索引长度比值长度短，这样可以提高查询速度，可以不用查询全部信息，而只查询其前面的若干字符信息）

             创建多列索引：

 

           注：多列索引，只有使用了索引的第一个字段时才会触发索引，如果没有使用索引的第一个字段，那么久不起作用。

 

（2）表已经创建好

 

 

                    创建普通索引：CREATE INDEX index_id ON 表名(id);

（3）ALTER TABLE 语句创建索引

                    ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名(属性名 [(长度)] [ASC|DESC]);

 

删除索引：

                    DROP INDEX 索引名 ON 表名；

查询索引：

              使用 SHOW INDEX  FROM  表名来查看当前表中是否有索引。

判断是否使用了索引：

         先建立索引

         然后使用   EXPLAIN  就可以查询是否用了  索引。

         查询结果如下，key是使用了的索引，possible_keys是可能用到的索引。

 

注：联合索引中遵循最左前缀原则

         group by分组单个属性的时候可以用联合索引的索引分组。

         order by的时候只能用单个属性的索引来排序。

索引进行优化：

  索引建立的7大原则。

  （1） 定义好的联合索引

  （2） 以下情况没有用到索引退化成了全表查询，避免出现以下情况：

          （1）like '%丽'

          （2）函数：where avg(source) = sum(source)

          （3）or的前后有一个没有用到索引

          （4）运算符   age + 5 < 6

          （5）类型强转

  （3）优化配置

            .ini   .cnf

            innodb_buffer_pool_size = 8M  数据和索引的缓存大小 ，可以直接从缓存中拿数据和索引

            key_buffer_size = 8M  myISAN的索引缓存  ，可以直接从缓存中拿索引

 

      

          索引的底层结构：MyISAM InnoDB使用的都是多路树 B+树 B*树

          路数 = 节点的数据量

          非叶子节点中存放的是关键字，叶子结点存放数据（叶子结点用单链表串联起来）

          因为数据都是在叶子结点存放，所以查询效率是平均的。

B*树结构：

 

InnoDB MyISAM对比：

 InnoDB：

             聚簇型：叶子结点里面存放的是数据

             主键索引树可以找到所有的属性(主键索引效率最高，在B+树中是排序好了的)

             辅助索引最后找到的是当前属性和主键，如果还想找到别的属性的值，还要去主键索引上查一次。

 

MyISAM：

             如果创建在自己的索引上查找自己，则需要找一次。

             非聚簇型：叶子结点中存放的是地址

             索引树查到最后 拿到的是地址，再通过地址找到其属性，所以主键索引和辅助索引效果一样。

 

为什么使用B+树： 

          （1） 和其他数据结构对比：

                    链表  数组  查找的效率O(n)

                    二叉查找树：O(log2N) 极端情况下会退化成链表

                    AVL树：O(log2N)   但是旋转次数太多

                    红黑树：O(log2N)  但是一个节点只有一个数据

                    B+树：O(logmN)一个节点有多个数据，名字叫做一页  4K大小，（对树的高度有限制，不能大于三层 4K^3大小）

                    因为索引都保存在文件中，但是文件IO(耗时大)，所以一次尽可能加载多个数据，所以用B+树。

                    数据库的数据读取都是需要进行代价巨大的磁盘IO操作，因此，更快地缩小范围和更少的读取次数是数据库需要关注的重点。

           （2） 为什么不用B树？

                   1、B树 ：

 

                               （1）路数 比数据量大一个，子节点数据量比  父节点里存放的数据要多一个。

                               （2）叶子节点和非叶子节点都存放数据

                               （3）非叶子节点存放的即是数据也是关键字

                               （4）B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

                   2、B树有的数据查找的会 快，有的慢

                   3、B树不适合排序。

                   4、B树控制层数不容易。