mysql索引知识点总结

冷不防 2022-05-27 12:45 332阅读 0赞

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行个正确的’create index’，查询速度就可能提高百倍千倍，这可真有诱惑力。可是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。

1、**什么是索引？**

**答：**索引是针对数据库中的数据所建立的目录。创建索引的目的是为了优化数据库的查询速度。

**2.使用索引的好处与坏处？**

**答：**好处：大大加快了查询的速度

坏处：降低了增、删、改的速度，增大了磁盘资源的消耗（索引文件甚至可能比数据文件还大）。

**3.索引的使用原则？**

**答：**1.  不过度使用索引

2. 较频繁的作为查询条件的字段应该添加索引

3. 数据分布比较离散的字段适合添加索引，数据分布比较集中的字段不适合添加索引（如性别）

4. 更新非常频繁的字段不适合添加索引.

5. 不会出现在where子句中的字段不适合添加索引。

**4.根据索引的对象，索引可分为哪几种？**

**答：**普通索引（index）、唯一索引（unique）、主键索引（primary key）、全文索引（fulltext）。

**5.根据索引的存储类型，索引可分为哪几种？**

**答：** hash索引，B+Tree索引。

**6. ****Hash索引和BTree索引依赖的存储引擎是什么？**

**答：**BTree索引依赖的存储引擎是myisam或innodb，Hash索引依赖的引擎是memory。

**7. ****BTree索引依赖的存储引擎是myisam或innodb，有什么区别吗？**

**答：**使用myisam引擎，一张表对应三张保存文件（结构文件、数据文件、索引文件），也就是说使用myisam引擎，数据文件和索引文件是分开的，我们称之为非聚集索引。

使用innodb引擎，一张表对应两张保存文件（结构文件、数据索引文件），也就是说使用innodb引擎，数据文件和索引文件是绑定在一起的，我们称之为聚集索引。

Hash索引的查找效率理论上为O(1)，查询速度特别快，但是它不支持范围查找。

B+Tree数据结构的特点：数据只存放在叶子节点里面。叶子节点之间是以单向链表进行连接的，所以支持范围查找。

**8. ****hash索引和BTree索引的时间复杂度是多少？**

**答：**BTree索引的时间复杂度为O(log2N)，Hash索引的时间复杂度理论上为O(1)。

**9. ****为什么说Hash索引的时间复杂度理论上为O(1)呢？**

**答：**所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引理论上可以一次定位，其效率很高。之所以说它是理论上，是因为计算出的哈希值可能重复，如果哈希值重复，就没有办法一次准确定位，甚至当哈希值大量重复且数据量特别大时，哈希索引效率会没有BTree索引的效率高。

**10. ****Hash索引的底层数据结构是什么？**

**答：**数组\+链表，即哈希表。类似于Hashmap的底层。

**11. ****BTree索引的底层数据结构是什么？**

**答：**B+Tree。B+Tree本质也是一棵树，但是这棵树很特别，它的数据只存在叶子节点中，叶子节点之间通过链表进行连接，所以B+Tree索引支持范围查找，B+Tree这种树可以控制树的深度，官方建议3-5层最佳。

**为什么索引选择使用B+Tree数据结构，而不选择二叉查找树、红黑树？**

**答：**判断一个索引好坏的重要依据是I/O，即读写硬盘的次数。二叉查找树无法控制树的深度，即无法控制IO的次数，极端情况下，甚至是线性的。红黑树相对于二叉查找树而言，树的深度会更平衡一些，但是随着数据量的增多，可以无限加深的。而B+Tree是可以控制树的深度的，官方推荐3——5层为最佳。

**为什么选择自增id做主键？**

**答：**其实在实际项目中，主键可选择自增id，也可以选择uuid，也可以选择id card做主键。像在商品表中，商品的id往往会选用当前时间的毫秒值+n位随机数的方式。但是很多情况下，在业务允许的情况下，我们更偏向于使用自增id做主键，为什么呢？这是从创建索引的角度来考虑的，当使用自增id做主键时，当对id做索引时，那么无论是查询效率，还是插入效率，都相对较高。从插入层面看，自增id做索引时，索引在B+Tree数据结构中的位置是规律的，甚至硬盘存储的空间是连续的；而如果使用uuid做主键索引，那么插入时的节点位置是杂乱无章的，效率会很低。从查询层面来讲，因为自增id做主键索引的节点位置是规律的，查询效率相比较而言也会更高。

**有人会问一个问题，如果对姓名做索引，那么，mic和jams这两个名字，是如果判断谁在左，谁在右呢（即在B+Tree树中的位置是如何决定的）？**

**答：**这个问题，其实没必要深究。它到底是根据什么来进行排序，数据库的底层肯定会存在一个比较器来实现这个比较完排序的功能，具体怎么实现的，它可能根据姓名的ASCII码，也可能根据Hash值。我们做应用层开发的没有必须如此深究，因为没有意义。

**对姓名做索引，如果姓名重复了呢？怎么存的？取得时候，又是怎么取的？**

**答：**如果姓名为Lizhe的字段有好多个，那么当你使用索引查询Lizhe的时候，查出来的肯定是一个List链表，这个链表中包含所有字段为Lizhe的数据。至于它存的时候如何解决姓名冲突问题的，我猜测，肯定是将姓名一致的数据存在一张链表中，才会在查询的时候可以查到一张链表，具体细节如何，没有深入研究。

**为什么Hash索引不能支持范围查找，而B+Tree索引就能支持呢？它的原理是什么？**

**答：**Hash索引的底层存储结构是哈希表，即数组+链表的形式，只支持等于查询，而对于范围查询，select \* from 表名 where id > 5;是不支持的；而B+Tree索引底层存储结构是B+Tree，这棵树有一个很大的特点就是，它的数据只存储在叶子节点中，而叶子节点之间是以单向链表的方式连接在一起的，右边节点存的数据肯定比左边节点存的数据大，所以支持范围查找。

**12. ****Hash索引的缺点有哪些？（官方解释）**

**答：**Hash索引仅仅能满足等于索引，不能进行范围索引。

Hash索引无法被用来避免数据的排序操作。

Hash索引不能利用部分索引键查询。

Hash索引遇到大量哈希值重复的问题时，查询效率并一定比BTree索引高。

**个人总结：**我认为不选择Hash索引的原因主要有两个：一是Hash索引不支持范围查找；二是Hash索引，在大量Hash值冲突的情况下，它的查询效率会很低，相对于B+Tree索引的查询优势就当然无存了。

实际中，我们不用Hash索引，Hash索引依赖的引擎是memory，但现在mysql最常使用的引擎是myisam和innodb，甚至越来越只倾向于innodb，默认即为innodb，而innodb只支持BTree索引。所以，我们用BTree索引。

**13. ****常见的操作索引的命令有哪些？（B+Tree索引）**

**答：**(这里不全，用到去查)

查看一张表上所有索引

show index from 表名

添加索引

alter table 表名 add index/unique/fulltext \[索引名\] (列名);

alter table 表名 add primary key (列名);//不要加索引名，因为主键只有一个

删除索引

删除非主键索引：alter table 表名 drop index 索引名；

删除主键索引：alter table 表名 drop primary key;

**例子：**

创建一张表member

create table member(

id int,

email varchar(30),

tel varchar(20),

intro text

) engine myisam set character utf8;

给tel字段加一个普通索引

alter table member add index tel (tel);

给email字段加一个唯一索引

alter table member add unique (email);

给intro字段加一个全文索引

alter table member add fulltext (intro);

给id字段加一个主键索引

alter table member add primary key (id);

删掉intro索引

Alter table member drop index intro;

删掉tel索引

Alter table member drop index tel;

删掉email索引

Alter table member drop index email;

删掉主键索引

Alter table member drop primary key;

**14. ****全文检索的命令用法**

**答：**match(全文索引名) against (“keyword”);

**16.关于全文索引的停止词**

**答：**全文索引不会针对非常频繁的词创建索引，如this,is,you,my等，这种次叫停止词。

15. **为什么mysql中的全文检索在默认情况下，对于中文的意义不大？**

**答：**mysql中的全文检索是针对英文的，因为英文都通过空格或标点符号将一个个的单词拆开来了，方便对英文单词进行全文索引；而对于中文，mysql无法有效地区分中文单词，所以无法做全文检索。

16. **案例：设有新闻表15列，10列上有索引，共500W行数据，如何快速导入？**

**答：**（1）把空表的索引全部删除

（2）导入数据

（3）数据导入完毕后，集中建索引

**Mysql数据库的优化技术**

**对mysql优化时一个综合性的技术，主要包括**

> a: 表的设计合理化(符合3NF)
> 
> b: 添加适当索引(index) \[四种: 普通索引、主键索引、唯一索引unique、全文索引\]
> 
> c: 分表技术(水平分割、垂直分割)
> 
> d: 读写\[写: update/delete/add\]分离
> 
> e: 存储过程 \[模块化编程，可以提高速度\]
> 
> f: 对mysql配置优化 \[配置最大并发数my.ini, 调整缓存大小 \]
> 
> g: mysql服务器硬件升级
> 
> h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)