mysql索引

（今天学习数据库优化的时候学习mysql的索引知识，在这里记录一下。）

1.什么是mysql的索引？

       索引是一种通过特定的算法（具体算法暂时还不了解），来将数据表中的数据分类，从而提高查询效率的一种数据结构。

例如：给图书馆的书进行分类管理，方便查找。

2.索引的2种数据结构

     2.1  B+tree索引

当为表的某个字段创建索引的时候，mysql会根据算法将该字段的所有数据，按照这个结构分类存放。

 

这是innodb引擎默认使用的数据结构

B+tree一种数据结构，通过一定算法将数据像一棵树一样存放。

 

通过一定的算法，将mysql表中数据分磁盘存放，假如查询id=65这个字段

1. 进入磁盘一 65>28 所以找到p2指针
2. 进入磁盘三 65大于36，小于79，找到p2指针
3. 进入磁盘八  找到65

可以看到三次磁盘IO操作就搞定了，如果没有使用索引，因为mysql是随机IO就不知道查询多少次，但是肯定是3次的好多倍。由此可以看出索引能大大提高查询效率

 

可以在图中看到最下边的磁盘都会有指针相连接，这是为了优化范围查询

比如：查询3-17之间的数据，只需要一次走根节点下来找到3，后面的就只需要根据最后的指针直接找到，而不需要去重复走根节点

     2.2 hash索引

hash索引数据结构就是按照hash算法，精确的存放数据，查询数据的时候就按照相同的算法，再去查询一次就能找到，等值查询，效率非常高。

 但是它也存在缺点：

           只能等值查询不能范围查询

           容易发生hash碰撞（两个主键算出的hash值相等）

3.索引的分类

3.1单列索引

3.2主键索引

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

主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的

建立方式:

1.建表的时候primary key(列名)

2.建好表后:

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)

还有一个create primary key……大家记一个写法就可以了

3.3普通索引

1.2.1.2普通索引

这个是最基本的索引

语句:

1.建表的时候可以index(列名来指定)

2.建好表后:

 ALTER TABLE `table_name` ADD INDEX index_name (`column`);

3.4唯一索引

唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值，可以保证数据的唯一性

语句:

1.建表的时候:unique index(列名)

2.建好表后:

ALTER TABLE `table_name` ADD UNIQUE index(`column`);

3.5组合索引

多个列一起组成索引:

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

 

例如:

CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`); (另外一种写法，所有的索引都有的写法，这里顺便了解一下)

nickname_account_createdTime_Index:索引名

award：表名

如果你建立了 组合索引(nickname_account_createdTime_Index) 那么他实际包含的是3个索引 (nickname) (nickname,account)(nickname,account,created_time)

在使用查询的时候遵循mysql组合索引的"最左前缀",下面我们来分析一下 什么是最左前缀:索引where时的条件要按照建立索引的时候字段的排序方式

1、不按索引最左列开始查询（多列索引） 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where `c2` = `aaa` and `c3`=`sss` 不能使用索引，因为最左边的C1没用到

2、查询中某个列有范围查询，则其右边的所有列都无法使用查询（多列查询）

Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 该查询只会使用索引中的前两列,因为like是范围查询，导致右边的索引失效

3、不能跳过某个字段来进行查询,这样利用不到索引,比如我的sql 是 

explain select * from `award` where nickname > 'rSUQFzpkDz3R' and account = 'DYxJoqZq2rd7' and created_time = 1449567822; 那么这时候他使用不到其组合索引.

因为我的索引是 (nickname, account, created_time),如果第一个字段出现范围符号的查找,那么将不会用到索引,如果我是第二个或者第三个字段使用范围符号的查找,那么他会利用索引,利用的索引是(nickname),

因为上面说了建立组合索引(nickname, account, created_time), 会出现三个索引

3.6全文索引

文本字段上(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的数据记录全部查询出来

 

在MySQL5.6之前，支持全文搜索只用MyISAM引擎。

3.7空间索引

4.删除索引

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

5.索引的优点（大大的增加检索效率）

5.1 大大的减少存储引擎要扫描的数据量

5.2可以在有些时候帮助减少排序时间

5.3可以把随机io改为顺序io

5.索引的缺点

1.会降低维护效率（每次更新又要算一次），因为索引是通过一定的算法，算出一个值后分类放在某个位子，那么每次更新又需要去算一次

2.索引占物理空间，算出了数据结构，总得有个地方放吧

6.索引建立的建议

1.查询中很少使用的的列不要建立索引（不然会降低mysql）的性能和加大内存空间

2.在同样的列不要加太多的索引，不然mysql查询优化器会纠结不知道选哪种索引，会耗费时间

3.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,当查询的数据达到半数以上的时候，mysql会认为索引并不能提高效率，会全表扫描

4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系