MySQL索引学习笔记详解数据库

需求分析

首先，先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname=’css’,如果要拿这条数据的话需要些的sql是 SELECT * FROM award WHERE nickname = ‘css’

一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在nickname上建立索引,那么mysql只需要扫描一行数据及为我们找到这条nickname=’css’的数据,是不是感觉性能提升了好多咧….

MySQL索引作用

• 可以大大提高MySQL的检索速度

索引分类

• 单列索引（主键索引,唯一索引,普通索引），即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引
• 组合索引，即一个索引包含多个列，一个组合索引包含两个或两个以上的列

索引的缺点：

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

2.建立索引会占用磁盘空间的索引文件

3.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
4.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
5.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

索引的优点：

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

一、索引创建

1、单列索引

1-1、普通索引

第一种方式:

CREATE INDEX IndexName ON `TableName`(`字段名`（length）);

第二种方式：

 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));

第三种方式：

CREATE TABLE mytable(   
  
ID INT NOT NULL,    
  
username VARCHAR(16) NOT NULL,   
  
INDEX [indexName] (username(length))   
  
);  

注意：如果是CHAR,VARCHAR,类型,length可以小于字段的实际长度,如果是BLOB和TEXT类型就必须指定长度。

1-2、唯一索引

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

第一种方式:

CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));

第二种方式:

ALTER TABLE TableName ADD UNIQUE (column_list) ；

第三种方式：

// 创建表的时候直接指定 
CREATE TABLE mytable(   
  
ID INT NOT NULL,    
  
username VARCHAR(16) NOT NULL,   
  
UNIQUE [indexName] (username(length))   
  
);  

1-3、主键索引

与唯一索引类似，但不允许有空值

1-4、使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引： 
 
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。 
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。 
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。 
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

1-5、显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 /G 来格式化输出信息。 
 
尝试以下实例: 
mysql> SHOW INDEX FROM table_name; /G 
........

2、组合索引

CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);

二、索引删除

删除索引的mysql格式 :DORP INDEX IndexName ON `TableName`

三、使用索引需要注意的地方

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的.
一般来说,
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)操作时不应该创建索引,这两个操作是互斥的关系

参考链接：

http://www.runoob.com/mysql/mysql-index.html

https://www.cnblogs.com/chenshishuo/p/5030029.html