说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调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)