MySQL索引学习

1.什么是索引

索引是一种特殊的文件，包含对数据表里所有记录的引用指针

索引是一种数据机构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常是使用B树及B+树

索引相当于目录，为了方便查找书中的内容，通过对内容建立索引形成目录。是一个文件，需要占据一定的物理空间

2.索引的优缺点

优点：

• 可以大大加快数据的检索速度
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点：

• 时间方面：创建索引和维护索引需要耗费时间。当对表中的数据进行增加、删除和修改时，索引也要动态的维护，会降低增删改的执行效率
• 空间：索引需要占据物理空间

3.索引有几种类型

（1）主键索引：数据列不允许重复，不允许为NULL，一张数据表只能有一个主键

（2）唯一索引：数据列不允许重复，允许为NULL值，一张表允许多个列创建唯一索引

• 通过alter table table_name add unique(column); 创建唯一索引
• 通过alter table table_name add unique(column1,column2); 创建唯一组合索引

（3）普通索引：基本的索引类型，没有唯一性的限制，允许为NULL

• 通过alter table table_name add index index_name(column); 创建普通索引
• 通过alter table table_name add index index_name(column1,column2,column3); 创建组合索引

（4）全文索引：目前搜索引擎使用的一种技术

• 通过alter table table_name add fulltext(column); 创建全文索引

4.索引的数据结构（B树，Hash）

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等
（1）B树索引
B树索引是MySQL数据库中使用最频繁的索引类型，基本所有索引引擎都支持B树索引

（2）哈希索引
在MySQL中使用哈希索引时，主要通过Hash算法，将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置。如果发生Hash碰撞，则在对应Hash键下以链表形式存储

4.索引的基本原理

索引用来快速的寻找哪些具有特定值的记录。如果没有索引。一般来说执行查询时遍历整张表

索引的原理很简单，就是把无序的数据变成有序的查询
（1）把创建了索引的列的内容进行排序

（2）对排序结果生成倒排表

（3）在倒排表内容上评上数据地址链

（4）在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

5.索引设计的原则

（1）适合索引的列是出现在where子句中的列，或者连接子句中指定的列

（2）基数较小的类，索引效果较差，因此无需在此列建立索引

（3）使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样可以节省大量索引空间

（4）不要过度索引，索引需要额外的磁盘空间，并降低操作的性能，在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就越长，所以只保持需要的索引利于查询即可

6.创建索引的原则

（1）较频繁作为查询条件的字段才会创建索引

（2）更新频繁字段不适合创建索引

（3）若是不能有效区分数据的列不适合做索引列

（4）尽量的扩展索引，不要新建索引

（5）定义有外键的数据列一定要建立索引

（6）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引

（7）对定义为text、image和bit的数据类型的列不要建立索引

7.创建索引时需要注意的问题

• 非空字段：应该指定列为NOT NULL。在MySQL中，含有空值的列很难及进行查询优化，因为它们时索引，索引的统计信息以及比较运算更加负责。可以使用0、一个特殊的值或者一个空串来代替空值
• 取值离散大的字段：变量各个取值之间爱的差异程度较大的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多，字段的离散程度高
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多，一次IO操作获取的数据越大效率越高

8.使用索引查询一定能提高查询的性能吗

通常，通过索引查询数据比全表扫描要快

• 索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改
• 基于一个范围的索引，一般索引返回结果集小于表中记录数的30%
• 基于非唯一性索引的检索

9.百万级别或以上的数据如何删除

关于索引：由于所以需要额外的维护成本，因为索引文件时单独存在的文件，所以当我们对数据的增删改都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增删改的执行效率。所以，在我们删除数据库百万级别数据时，查询MySQL官方手册得知删除数据的速度和创建的索引数量时成反比的
（1）先删除索引

（2）删除其中无用的数据

（3）删除完成后重新创建索引

10.B树和B+树的区别

• 在B树中，可以将键和值存放在内部节点和叶子节点；B+树中，内部节点都是键，没有值，叶子节点同时存放键和值
• B树的叶子节点各自相互独立；B+树的叶子节点有一条链相连

11.使用B树的好处

B树可以在内部节点同时存放键和值，因此将频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率

12.使用B+树的好处

由于B+树的内部节点只存放键，不存放值。因此，一次读取，可以在内存中获取更多的键，有利于更快的缩小查找范围。B+树的叶节点有一条链相连。因此当需要进行一次全数据遍历时，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O（N）的顺序遍历即可，而B树则需要对树的每一层进行遍历，这回需要更多的内存置换次数

13.Hash索引和B+树索引有什么区别

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现的是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获取所查键值，然后根据查询判断是否回表查询数据

hash索引进行等值查询更快，但无法进行范围查询
原因：在hash索引中经过hash函数建立索引后，索引的顺序与原顺序无法保持一致，不能支持范围查询。B+树的所有节点都遵循左节点小于父节点，右节点大于父节点

hash索引不支持使用索引进行排序
hash索引不支持模糊查询
hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件的时候可以只通过索引完成查询
hash索引虽然在等值查询上较快，但不稳定，性能不可预测。当某个键值存在大量重复时，发生hash碰撞，此时效率可能极差，而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低

在大多数情况下，选择B+树索引可以获取稳定且较好的查询速度，而不需要使用hash索引

13.数据库为什么使用B+树而不是B树

• B树只适合随机检索，B+树同时支持随机检索和顺序检索
• B+树空间利用率更高，客家少IO次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。B+树的内部节点并没有指向关键字具体信息的指针，只是作为索引使用，其内部节点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也越多，因此IOC读写次数就会降低。IO读写次数是影响索引效率的最大因素
• B+树的查询效率更稳定，B+树搜索有可能会在非叶子节点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当
• B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题，B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以整棵树的遍历。在数据库中基于范围的查询时非常频繁的，但B树不支持这样的操作
• 增删文件时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，可以很好的提高增删效率

14.什么时数据库事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的几倍呢单位i，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行

15.事务的4大特性

• 原子性：事务是最小的执行单位，不允许分割，事务的原子性确保动作要么全部完成，要么完全不起作用
• 一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的
• 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
• 持久性：一个事务被提交之后，它对数据库中数据的改变是持久代的，即使数据库发生故障也不应该对其有任何影响

16.什么是脏读？幻读？不可重复读》

• 脏读：某个事物已经更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的
• 幻读：在一个事务的两次查询中数据笔数不一致
• 不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据

17.什么是事务的隔离级别？MySQL的默认隔离级别是什么

SQL标准定义了4个隔离级别：

• READ-UNCOMMITTED：读取未提交，最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
• READ-COMMITTED：读取已提交，允许读取并发事务已经提交的数据，可以组织脏读，但是幻读或不可重复读仍有可能发生
• REPEATABLE-READ：可重复读，对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
• SERIALIZABLE：可串行化，最高的隔离级别，完全服从ACID的隔离级别。所有的事务一次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以阻止脏读、幻读和不可重复读

MySQL默认采用的是REPEATABLE-READ隔离级别

18.为什么要进行数据库的优化

• 系统的吞吐量瓶颈往往在数据库的访问速度上
• 随着应用程序的运行，数据库中的数据会越来越多，处理时间也会变得很慢
• 数据是存放在磁盘上的，读写速度无法和内存相比

优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度

19.数据库结构优化

（1）将字段很多的表分解成多个表
当一个表的数据量很大时，会由于使用频率低的字段的存在而变得很慢

（2）增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率
通过建立中间表，将需要通过联合查询的数据插入到中间中，然后将原来的联合查询改为对中间表的查询

（3）增加冗余字段
合理的加入冗余字段可以提高查询速度