mysql创建索引面试题,程序员：每天三道面试题(MySQL索引分类与优缺点）（三十）...

程序员：每天三道面试题(MySQL索引分类与优缺点)(三十)

作者:PHPYuan 时间:2018-08-23 03:41:27

1、MySQL中索引的作用？和它的优点缺点是什么？

索引就一种特殊的查询表，数据库的搜索可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

2、MySQL中,什么样的字段适合建索引

1、经常被 查询的字段

2、不为空且字段值不重复

3、字段的值不经常被修改

3、MySQL索引类型有哪些？

逻辑上：

Single column 单行索引

Concatenated 多行索引

Unique 唯一索引

NonUnique 非唯一索引

Function-based函数索引

Domain 域索引

物理上：

Partitioned 分区索引

NonPartitioned 非分区索引

B-tree：

Normal 正常型B树

Rever Key 反转型B树

Bitmap 位图索引

知识扩展:

１．索引：

索引在数据库开发中起着非常重要的作用,通过在表字段中建立索引可以优化查询,确保数据的唯一性。并且可以对任何全文索引字段中大量文本的搜索进行优化，在MySQL中主要有4类索引：主键索引(PRIMARY　KEY)、唯一索引(UNIQUE)、常规索引(INDEX)、和全文索引(FULLTEXT)。

１)主键索引(PRIMARY　KEY)

主键索引是关系数据库中最常见的索引类型，它主要作用是确定数据表里一条特定的数据记录的位置。数据表会根据主键的唯一性来唯一标识每条记录，任意两条记录里的主键字段不允许是内容，这样的话可以加快寻址定位时的速度。最好为每张数据表指定一个主键，但一张表只能指定一个主键，而且主键的值不能为空，不过可以有多个候选索引。

eg:

CREATE TABLE cats(

cid INT NOT NULL PRIMARY KEY,

catname VARCHAR NOT NULL

)

CREATE TABLE books(

bid INT UNSIGNED NOT NULL PRIMARY KEY,

cid INT NOT NULL ,

bookname VARCHAR NOT NULL,

detail TEXT NOT NULL,

price DOUBLE NOT NULL

....

)

在上面的例子中，创建图书分类表cats 时，声明个整型字段cid,设置其属性为NOT NULL和AUTO INCREMENT,并在字段后使用PRIMARY KEY设置该字段为主键索引。在创建图书信息表boks时，声明的整型字段bid也设置相同的属性，而且使用另种从句的方 式将其设置为主键索引。并在books表中声明个cid的字段，用于保存cas表中设置为主键的cid,这样就为两张表建立起了一种关联关系， 通过SQL语句可以将两张表合在起使用。 另外，主键索引还常常与外键索引构成参照完整性约束，防止出现数据不致。在删除条记录之前， 必须检查在其他数据表里是否存在对这条记录的引用。

2、唯一 索引( UNIQUE )

唯索引与主键索引一样，都可以防止创建重复的值。但是，不同之处在于，每张数据表中只能有一个主键索引，但可以有多个唯索引。 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引时就应该使用关键字UNIQUE把它定义为一个唯一 索引。 这样，在有新记录插入时，就会自动检查新记录的这个字段的值是否已经在某个现有记录的这个字段里出现过了，如果是，MySOL将拒绝插入这条新记录。其实，创建唯一索引的目的往往不是为了提高访问速度， 而只是为 了避免数据出现重复。

例如，在创建图书类别表cats时，为类别名字段catname使用关键字UNIQUE将其定义为一个唯一索引， 避免插入数据时出现重复的类别名称。如下所示。

eg:

CREATE TABLE cats(

cid INT NOT NULL PRIMARY KEY,

catname VARCHAR(15) NOT NULL UNIQUE

)

3、常规索引( INDEX)

常规索引技术是关系数据查询中最重要的技术，在提升数据库的性能，索引优化是必须要考虑的。因为它能使我们的数据库得到最大性能方面的提升。如果没有索引的数据表，就没有排序的数据集合，要查询数据，就需要进行全面扫描、有索引的表是一张在索引列上排序了的数据表，可以通过索引快速定位记录、住MySQL数据表中，数据行保存在数据文作中，索引保存在索引文件中。而 InnoDB数据表把数据与索引放在同一个文件中。

常规索引也存在缺点，比如，多占用磁盘空间，而且还会减慢在素引数据列上的插入，删除、和修 改操作，它们也需要按照索引列上的排序格式执行，因此索引应该创建在投索、排序，分组等操作所涉及的数据列上。也就是在where子句，关联检索中的from子句，orderby 成group by子句中出现过的数据列最适合用来创建这种索引。个人认为不要创建太多索引，因为索引是会消耗系统资源的，适可而止。

常规索引可以使用关健宇KEY或INDEX随表一同创建，KEY通常是INDEX的同义词。也可以在创建表之后使用CREATE INDEX或ALTER TABLE命令来创建这三个办法里的索引，描述语

法是完全一样的。例如，在创建购物车表carts时,随表一同为uid和bid创建个名为 ind的索引。

eg：

CREATE TABLE carts(

cid INT NOT NULL PRIMARY KEY ,

uid INT NOT NULL

bit INT NOT NULL,

num INT NOT NULL,

KEY ind (uid，bid)

)

注：如果没有设置索引名，系统会根据第一个索引列的名称自动选一个(个人建义使用

‘表名_列表’作为索引名)。

4、全文索引(FULLTEXT)

从MSOL3.2323版本开始支持全文索引和搜索，使用户能够在不使用模式匹配操作的前提下去搜索单词或者短语。全文索引在MySOL中是一个FULLTEXT类型索引，但FULLTEXT索引只能用 于MyISAM表，井且只能在CHAR VARCHAR或TEXT类型的列上创建，也允许创建在一个道多个数据列上。这是种特殊的索引，它会把在某张数据表的某个数据列里出现过的所有单调生成为份 清单。

创建全文家引与创建其他类型的索引很相似，就好比刚刚我们修改前面的图书信息表books.为dctul字教增加全文家引，如下

虽然创建全文家引非常类似于创建比他类型的索叫，但基于全文家引的获取查询却有所不同。当基于全文家引获取数据时，在SELECT 语句中面安使用MATCH()和AGANSOIA()个特殊的MySQL函数。MATCH()函数负责列举将对它进行搜索的个或者多个数据列， 而AGAINST()负责给出搜索字符串。比如，我们需要在数据表bok的deail字段中搜索字符串"hello", SELECT语句如下

全文索引

SELECT book name, price FROM books WHERE MATCH(detail AGAINST('hello');

该查询列出在detail 字段中出现“hello”的记录，以相关性从高到低的顺序排序。

前几天小编确实走不开，应各位的需要，我把关于MySQL的索引整理了一下，有什么错误望指出，每个人掌握的知识力度不一样，希望路过的大佬不喜勿喷。

mysql