数据库索引

目录

什么是索引：

数据库的存储引擎：

MyISM存储引擎：

 InnoDB存储引擎

索引的分类：

---

什么是索引：

索引是帮助Sql高效获取数据的排好序的数据结构

数据库的存储引擎：

数据库的存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制，索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

1. MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。
2. 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。
3. 存储引擎是MySQL数据库中的组件，负责执行实际的数据I/O操作。
4. MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储。
    

MySQL常用的存储引擎：

• MyISAM
• InnoDB

注意：一个表只能使用一个存储引擎，一个库中不同的表可以使用不同的存储引擎

MyISM存储引擎：

1.MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的。

2.访问速度快，对事务的完整性没有要求

3.MyISAM在磁盘上存储成三个文件，文件名和表名相同，但扩展名不同分别为

1 .frm文件存储表结构的定义

2.数据文件的扩展名为.MTD(MYData)

3.索引文件的扩展名是.MYI(MYINDEX)

 

 MyISAM存储引擎索引实现

 InnoDB存储引擎

1.  InnoDB存储引擎支持事务
2. InnoDB存储引擎数据存储是两个文件

.frm文件存储表结构的定义

.ibd文件存储的是索引和数据

 InnoDB存储引擎索引结构如下：

InnoDB索引实现（聚集）一般所说的主键索引

•  表数据文件本身就是按B+树组织的一个索引文件
• 聚集索引-叶子节点包含了完整的数据记录

InnoDB的一般索引（非聚集索引）：

非叶子节点数据存储的格式和聚集索引一样，但是叶子阶段存储的数据，是改数据所在行的主键，通过非聚集索引查找到改行数据的主键，根据查找到的主键，然后通过聚集索引（主键索引）查找改行数据信息（就是所谓的回表）。

索引的分类：

1.主键索引：

•      主键索引是一种唯一性索引，但它必须指定PRIMARY KEY（主键），主键是惟一的，不能有重复值，和空值

2.唯一索引

• 索引列的所有值都只能出现一次，即必须是唯一，不能有重复值，其值可以为空

3.普通索引

• 基本的索引类型，可以为空，没有唯一性的限制

4.全文索引 （MyISAM存储引擎支持全年索引，

innodb 从1.2.x 开始才增加了全文索引支持。而MySQL5.6版本中innodb的版本才升级到 1.2.x。mysql5.5版本的innodb不支持全文索引）

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

5.组和索引

• 多列值组成索引，专门用于组和索引。

6.前缀索引

• 那么为什么不对整个字段建立索引呢？一般来说使用前缀索引，可能都是因为整个字段的数据量太大，没有必要针对整个字段建立索引，前缀索引仅仅是选择一个字段的部分字符作为索引，这样一方面可以节约索引空间，另一方面则可以提高索引效率。

 问题1：为什么建议InnoDB表必须建一个主键，并且推荐使用整形的自增主键？

答：因为InnoDB存储引擎它的表数据文件本身就是按B+树组织的一个索引文件，这样就要求存储的数据要有一列的数据是不会有重复值的，不建主键，数据库就会扫描每列看那一列不会有重复值依次列为主键去构建数据的存储，如果每列都有重复值，数据库会增加一个六位的隐藏列（row_id）依次列为主键去构建数据的存储并且这一列是数据库帮助维护他的唯一性。所以不设置主键，这样就会很浪费数据库资源。

推荐使用整形，因为整形数据好比较，

自增：因为B+树是一种排好序的数据结构（节点值从左到右是一次递增的），用自增方便每次插入到叶子节点链的后面，对于B+树的分裂来说更加方便。如果不用自增的话，有可能插入到叶子节点的中间位置，对于B+树的分裂来说不太方便。主要影响数据写入表的性能

问题2：联合索引的最左前缀原则

最左匹配原则：在通过联合索引检索数据时，从索引中最左边的列开始，一直向右匹配，如果遇到范围查找（<,>,between,like等），就停止右边的匹配。

如：假如对字段 (a, b, c) 建立联合索引，现在有这样一条查询语句：

where a > xxx and b=yyy and c=zzz
where a like 'xxx%' and b=yyy and c=zzz

在这个条件语句中，只有a用到了索引，后面的b,c就不会用到索引。这就是“如果遇到范围查询(>、<、between、like等)，就停止后边的匹配。”的意思。

问题3：什么是索引下推？

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，是为了减少回表操作，提高查询效率，

如：执行语句

SELECT * FROM user WHERE name LIKE '张%' AND age = 10;

说明：表以name 和age建立联合索引

在MySQL 5.6之前，存储引擎根据通过联合索引找到name like '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。

 可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。

 而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到，由于联合索引中包含列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描

 可以看到只回表了一次。

• ICP可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表。

• 对于 InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少回表此时，从而减少 I/O 操作。对于的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

• 在虚拟生成列上创建的二级索引不支持 ICP。InnoDB 支持虚拟生成列的二级索引。

• 引用了子查询的条件不能下推；

• 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数

问题4：索引覆盖或（覆盖索引）是什么？

在查询数据的时候，在非聚簇索引的叶子节点中包含了查询的所有

信息（字段），通过索引能直接得到查询的信息，不需要在进行回表，这叫索引覆盖。

如：下面是一个表以 name字段建立的索引结构，执行语句 

select id from table where name='ma';

执行这个语句只查询 主键 id，而在索引的非叶子节点总存储的就有id，因此可以从索引中直接获取到id，不需要在进行回表了，这叫索引覆盖。

问题五：数据库的五中约束？

问题六：索引失效的常见原因

（当表中的所有列都是索引列时，无论怎么查询都会走索引（除了主键外所有的字段在一个索引中结构中））

如：建立表abc 字段有 （id 主键，a,b,c）a,b,c建立联合索引，

执行下面语句都是走索引的

select * from table where a='1' and b='1' and c='1';

select * from table where b='1' and c='1';

select * from table where c='1';

 但是若创建表abcd 字段有 （id 主键，a,b,c,d）a,b,c建立联合索引，d字段单独建立索引

再执行上面的语句就不会走索引了

问题六:什么是聚集索引和非聚集索引

聚集索引：数据和索引是存储在一起的，即在索引的叶子节点中包含了完整的数据记录，聚集索引如innDB存储引擎的主键索引。

非聚集索引：索引和数据是分开存储的，如 MyISAM存储引擎的索引，和InnoDB存储引擎的一般索引。

InnoDB介绍
支持事务，支持4个事务隔离级别
MySQL从5.5.5版本开始，默认的存储引擎为InnoDB
读写阻塞与事务隔离级别相关
能非常高效的缓存索引和数据
表与主键以簇的方式存储 BTREE
支持分区、表空间，类似oracle数据库
支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
对硬件资源要求还是比较高的场合
行级锁定，但是全表扫描仍然会是表级锁定，如
update table set a=1 where user like ‘%zhang%’;
 

数据库：show profile for query?

Explain工具介绍？

 mysql默认的隔离级别 repeatable read（可重复读）oracle默认read commit（读已提交）

数据库中的锁：