1.索引的类型

(1) 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引

(2) 索引的分类

  • 单列索引：

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

唯一索引：索引列中的值必须是唯一的，但是允许为空值，

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

主键索引：是一种特殊的唯一索引，不允许有空值。

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；
  • 组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

表示使用col1的前4个字符和col2的前3个字符作为索引

  • 全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；
  • 空间索引：一般使用不到。空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

ps：删除索引

DROP INDEX index_name ON table

2.索引的实现原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等。

2.1 哈希索引

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

2.2 全文索引

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    my_text TEXT,
    FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

修改索引

//创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);

全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

2.3 BTree和B+Tree索引

2.3.1 BTree索引

BTree是平衡搜索多叉树，设树的度为2d（d>1），高度为h，那么BTree要满足以一下条件：

  • 每个叶子结点的高度一样，等于h；
  • 每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；
  • 叶子结点指针都为null；
  • 非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

2.3.2 B+Tree索引

B+Tree索引是BTree索引的一个变种，设d为树的度数，h为树的高度，B+Tree索引和BTree索引的不同主要在于：

  • B+Tree中的非叶子结点不存储数据，只存储键值；
  • B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
  • B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

B+Tree对比BTree的优点：

(1) B+Tree的非叶节点中不存储data，就可以存储更多的key,这样减少寻址的时间，速度更快。

(2)下面的第一张图是B树，第二张是B+树，大量数据查询的时候，尤其像主键索引查询的时候，大量相邻的id查询，如果是B树需要回溯加大遍历的时间，而B+树可以直接遍历叶子节点所形成的链表结构，加快遍历速度。 (3)磁盘存储连续的数据性能更高，也不会产生磁盘碎片，对磁盘性能有很大提升。

Mysql 查询不重复的user_id mysql查询不重复的列_Mysql 查询不重复的user_id


Mysql 查询不重复的user_id mysql查询不重复的列_主键_02

3. 聚簇索引和非聚簇索引

分析了MySQL的索引结构的实现原理，然后我们来看看具体的存储引擎怎么实现索引结构的，MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序
非聚簇索引的解释是:索引顺序与数据物理排列顺序无关

我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

3.1 MyISAM存储引擎采用的是非聚簇索引

非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

非聚簇索引的数据表和索引表是分开存储的

非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响

Mysql 查询不重复的user_id mysql查询不重复的列_数据_03

3.2 InnoDB的聚簇索引

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的数据和主键索引存储在一起。

聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。

Mysql 查询不重复的user_id mysql查询不重复的列_数据_04

4.使用索引的策略

4.1 什么时候要使用索引？

（1）主键自动建立唯一索引；
（2）经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
（3）作为排序的列要建立索引；
（4）查询中与其他表关联的字段，外键关系建立索引
（5）高并发条件下倾向组合索引；
（6）用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

4.2 什么时候不要使用索引？

（1）经常增删改的列不要建立索引；
（2）有大量重复的列不建立索引；
（3）表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

4.3 索引失效的原因

（1）在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
（2）在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
（3）LIKE操作中，’%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
（4）在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
（5）在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）
（6）在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
（7）字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email=‘99999’。
（8）在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
（9）如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
（10）尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；