SQL学习笔记11--sql的索引index

目录

索引index：

作用：

 索引的创建：

存储引擎

主要分类：

索引的数据结构

mysql索引系统：

索引分类：

  主键索引

  唯一索引

 普通索引

 全文索引-- MyISAM 支持，InnoDB 在5.6之后执行

 组合索引

重点名词：

  回表

 覆盖索引

 最左前缀

  索引下推

索引维护

---

索引index：

  索引是为了加快对数据的搜索速度而设立的。 索引的是方案中的一个数据库对象，与表独立存放。

作用：

   在数据库中用来加速对表的查询，通过使用快速路径访问fang方法快速定位数据，减少磁盘的I/O；

   sql的索引是非显示索引。也就是索引创建之后，在用户撤销它之前不会在用到该索引的名字，但是索引在用户查询的时候会起作用。

 索引的创建：

 1 自动：当在表上定义一个primary key或者unique 约束条件的时候，Oracle数据库自动创建一个对应的唯一索引。

  2 手动： 用户可以创建一个索引用来查询加速。

开发中使用索引的要点：

– 1. 索引改善检索操作的性能，但降低数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。

– 2. 索引数据可能要占用大量的存储空间。

– 3. 并非所有的数据都适合于索引。唯一性不好的数据（如省）从索引的到的好处不比具有更多可能值的数据（如姓名）从索引得到的好处多。

– 4. 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能是索引的备选。

– 5. 可以在索引中定义多个列（如省加城市），这样的索引只在以省加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处

 

  创建索引：

   create index  index_name on table(column[,column]....);

  删除索引：

   drop index index_name;  

存储引擎

   索引存储在文件系统中，索引的存储文件形式和存储引擎有关。

主要分类：

innoDB：存储在磁盘

   聚簇索引，数据和文件存储在一起 。 

   .frm 存放的是表结构

   .ibd:存放的是数据文件和数据文件

   注意： mysql的innodb存储引擎默认情况下会把所有的数据都存放在表空间中，不会为每一个单独的表保存一个数据文件，如                 果需要将每一个表单独使用文件使用文件保存，设置如下属性：
                  set global innodb_file_per_table=on;

MyISAM：存储在磁盘

  非聚簇索引，   数据和索引单独一个文件，
           .frm 存放表结构
           .MYI 存放索引数据
           .MYD 存放实际数据

memory：存储在内存。

索引的数据结构

  hash 二叉树 avl平衡树 红黑树 B树 B+树 

  

mysql索引系统：

索引分类：

  主键索引

     主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。

  唯一索引

     索引列的所有值都只出现一次，且必须唯一，值可以为空。

 普通索引

    基本的索引类型，值可以为空，没有唯一性的限制。（覆盖索引）

 全文索引-- MyISAM 支持，InnoDB 在5.6之后执行

    全文索引的索引类型是FULLTEXT。全文索引可以在varchar，char，text类型的列上创建。

 组合索引

   多列值组成一个suoy索引，专门用于组合索引（最左匹配）

 

重点名词：

  回表

    如果创建索引的键值是其他的字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，叫做回表。innoDB才有回表，MyISAM是没有的。

 覆盖索引

   就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖
  https://www.cnblogs.com/happyflyingpig/p/7662881.html

 最左前缀

   mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

• 如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
• 如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；
•   https://www.cnblogs.com/frankltf/p/10368509.html

  索引下推

    可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

     https://www.cnblogs.com/kkbill/p/11354685.html

索引维护

索引在插入新的值的时候，为了维护索引的有序性，必须要维护，

在维护索引的时候需要需要分以下集中情况：

– 1、如果插入一个比较大的值，直接插入即可，几乎没有成本

– 2、如果插入的是中间的某一个值，需要逻辑上移动后续的元素，空出位置

– 3、如果需要插入的数据页满了，就需要单独申请一个新的数据页，然后移动部分数据过去，叫做页分裂，此时性能会受影响同时空 间的使用率也会降

低，除了页分裂之外还包含页合并

▪ 尽量使用自增主键作为索引