[mysql] 索引学习

索引的类型

1. B-Tree 索引
2. Hash 索引
3. 空间（R-Tree）索引
4. 全文（Full-text）索引

索引使用原则

1. 最左前缀原则
   

   定义：最左前缀原则指的的是在 sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。
   
   举例说明：上面给出一个多列索引（username，password，last_login），当三列在where中出现的顺序如（username，password，last_login）、（username，password）、（username）才能用到索引，
   如下面几个顺序（password，last_login）、（passwrod）、（last_login），这三者不从 username 开始，（username,last_login）—- 断层，少了 password，都无法利用到索引。
   
   原理：因为 B+tree 多列索引保存的顺序是按照索引创建的顺序，检索索引时按照此顺序检索

使用索引的优点

1. 可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性。
2. 建立索引可以大大提高检索的数据，以及减少表的检索行数。
3. 在表连接的连接条件可以加速表与表直接的相连。
4. 在分组和排序字句进行数据检索，可以减少查询时间中分组和排序时所消耗的时间（数据库的记录会重新排序）
5. 建立索引，在查询中使用索引可以提高性能。

使用索引的缺点

1. 在创建索引和维护索引会耗费时间，随着数据量的增加而增加。
2. 索引文件会占用物理空间，除了数据表需要占用物理空间之外，每一个索引还会占用一定的物理空间。
3. 当对表的数据进行 INSERT，UPDATE，DELETE 的时候，索引也要动态的维护，这样就会降低数据的维护速度。
   

   建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

选择合适的数据类型做索引

1. 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和 CPU 缓存中都需要更少的空间，处理起来更快。
2. 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在 MySQL 中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储 IP 地址。
3. 尽量避免 NULL ：应该指定列为 NOT NULL，除非你想存储 NULL。在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0、一个特殊的值或者一个空串代替空值。

索引的创建

单列索引

1. 普通索引

   其 sql 格式是 CREATE INDEX IndexName ON TableName(字段名(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(字段名(length))

   第一种方式：CREATE INDEX account_Index ON award(account);

   第二种方式：ALTER TABLE award ADD INDEX account_Index(account)

   如果是 CHAR，VARCHAR 类型，length 可以小于字段的实际长度，如果是 BLOB 和 TEXT 类型就必须指定长度；

2. 唯一索引

   与普通索引类似，但是不同的是唯一索引要求所有的类的值是唯一的，这一点和主键索引一样，但是他允许有空值。

   其 sql 格式是 CREATE UNIQUE INDEX IndexName ON TableName(字段名(length));
   或者 ALTER TABLE TableName ADD UNIQUE (column_list)
   CREATE UNIQUE INDEX account_UNIQUE_Index ON award(account);

3. 主键索引

   不允许有空值，(在 B+TREE 中的 InnoDB 引擎中，主键索引起到了至关重要的地位)

   主键索引建立的规则是 int 优于 varchar，一般在建表的时候创建，最好是与表的其他字段不相关的列或者是业务不相关的列。

   一般会设为 int 而且是 AUTO_INCREMENT 自增类型的

组合索引

一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引是:包含多个字段但是只有索引名称

其 sql 格式是 CREATE INDEX IndexName On TableName(字段名(length),字段名(length),…);

CREATE INDEX nickname_account_createdTime_Index ON award(nickname, account, created_time);

如果你建立了 组合索引(nickname_account_createdTime_Index)

那么他实际包含的是 3 个索引 (nickname) (nickname,account)(nickname,account,created_time)

全文索引

文本字段上 (text) 如果建立的是普通索引，那么只有对文本的字段内容前面的字符进行索引，其字符大小根据索引建立索引时申明的大小来规定。

如果文本中出现多个一样的字符，而且需要查找的话，那么其条件只能是 where column lick ‘%xxxx%’ 这样做会让索引失效这个时候全文索引就祈祷了作用了

ALTER TABLE tablename ADD FULLTEXT(column1, column2)

有了全文索引，就可以用 SELECT 查询命令去检索那些包含着一个或多个给定单词的数据记录了。

SELECT * FROM tablename WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)

这条命令将把 column1 和 column2 字段里有 xxx、sss 和 ddd 的数据记录全部查询出来。

使用索引需要注意的地方

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的.

一般来说

1. 在经常需要搜索的列上，可以加快索引的速度。

2. 主键列上可以确保列的唯一性。

3. 在表与表的而连接条件上加上索引，可以加快连接查询的速度。

4. 在经常需要排序（order by）分组（group by）和 distinct 列上加索引，可以加快排序查询的时间，单独（order by）用不了索引，索引考虑加 where 或加 limit。

5. 在一些 where 之后的 < <= > >= BETWEEN IN 以及某个情况下的 like 建立字段的索引 （B-TREE）

6. like 语句，如果你对 nickname 字段建立了一个索引。当查询的时候的语句是 nickname lick ‘%ABC%’ 那么这个索引讲不会起到作用。而 nickname lick ‘ABC%’ 那么将可以用到索引。

7. 索引不会包含 NULL 列，如果列中包含 NULL 值都将不会被包含在索引中，复合索引中如果有一列含有 NULL 值那么这个组合索引都将失效，一般需要给默认值 0 或者 ’ ’ 字符串。

8. 使用短索引，如果你的一个字段是 Char（32）或者 int（32）在创建索引的时候指定前缀长度，比如前 10 个字符（前提是多数值是唯一的..）那么短索引可以提高查询速度，并且可以减少磁盘的空间，也可以减少 I/0 操作。

9. 不要在列上进行运算，这样会使得 mysql 索引失效，也会进行全表扫描。

10. 选择越小的数据类型越好，因为通常越小的数据类型通常在磁盘，内存、cpu、缓存中 占用的空间很少，处理起来更快。

什么情况下不创建索引

1. 查询中很少使用到的列，不应该创建索引，如果建立了索引然而还会降低 mysql 的性能和增大了空间需求。
2. 很少数据的列也不应该建立索引，比如：一个性别字段 0 或者 1，在查询中，结果集的数据占了表中数据行的比例比较大，mysql 需要扫描的行数很多，增加索引，并不能提高效率。
3. 定义为 text 和 image 和 bit 数据类型的列不应该增加索引。
4. 当表的修改 (UPDATE,INSERT,DELETE) 操作远远大于检索 (SELECT) 操作时不应该创建索引，这两个操作是互斥的关系。