MySQL中的索引_mysql的索引

一、含义

MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。索引的本质是数据结构。
我们自己可以理解为：
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈稀索引(hash index)等

1、索引的优缺点

（1）优点

①类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本
②通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

（2）缺点

①实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
②虽然索引大大提高了查询速度,同时却会降低更新表的速度, INSERT如对表进行、 UPDATE和 IDELETE，因为更新表时, Mysq不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
③索引只是提高效率的一个因素,如果你的 MYSQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句

2、索引的基本语法

（1）创建
其中加unique表示创建的是唯一索引

#CREATE[UNIQUE] INDEX 索引名 ON 表名(字段名(length))
CREATE[UNIQUE] INDEX index_name ON mytable(columnname(length))
#ALTER table 表名 ADD [UNIQUE INDEX [索引名] ON(字段名(length))
ALTER table mytable ADD [UNIQUE INDEX [index_name] ON(columnname(length))
1
2
3
4

如果是CHAR, VARCHAR类型, length可以小于字段实际长度；如果是BLOB和TEXT类型,必须指定 length
使用alter命令

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

（2）删除

DROP INDEXindexname] ON mytable;
1

（3）查看

SHOW INDEX FROM table_ name
1

3、索引的分类

（1）普通索引

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
它有以下几种创建方式：
（1）直接创建索引

CREATE INDEX index_name ON table_name(column_name)
1

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))
1

（3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)
1
2
3
4
5
6
7
8

（2）唯一索引

唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
它有以下几种创建方式：
（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))
1

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
1

（3）创建表的时候直接指定

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);
1
2
3
4
5
6
7

（3）主键索引

主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。一般是在建表的时候同时创建主键索引：

#随表一起建索引：
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);  

#单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
#删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY ;  
1
2
3
4
5
6
7
8
9
10
11

补充：主键索引与唯一索引的区别：

1. 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

2. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

3. 唯一性索引列允许空值，而主键列不允许为空值。

4. 主键索引在创建时，已经默认为非空值+ 唯一索引了。

5. 一个表最多只能创建一个主键索引，但可以创建多个唯一索引。

6. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

7. 主键可以被其他表引用为外键，而唯一索引不能。

（4）单列索引

单列索引即一个索引只包含单个列，一个表可以有多个单列索引。

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)  
);
#随表一起建立的索引 索引名同 列名(customer_name)
#单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name); 
#删除索引：
DROP INDEX idx_customer_name ;
1
2
3
4
5
6
7
8
9
10

（4）组合索引

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

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 
1

（5）全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。InnoDB在mysql5.6之后支持了全文索引
（1）创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
); 
1
2
3
4
5
6
7
8

（2）修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)
1

（3）直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)
1

4、索引的结构

索引的结构有四类：
Btree索引、Hash索引、full-text全文索引、R-Tree索引
今天我们主要讲一下Btree索引的检索原理

【初始化介绍】
一颗b+树,浅蓝色的块我们称之为一个磁盘块可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),
如磁盘块1包含数据项17和35,包含指针P1、P2、P3,
P1表示小于17的磁盘块,P2表示在17和35间的磁盘块,P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99
非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。
【查找过程】
如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次O,在内存中用二分查找确定29在17和35之间,锁定磁
盘块1的P2指针,内存时间因为非常短(相比磁盘的O)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存
,发生第二次1O,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次1,同时内存中做二分查找找
到23,结束查询,总计三次。
（1）MyISAM: MyISAM中主键索引和辅助索引采用的都是B+树作为索引存储结构，叶子节点存储的都是数据地址，即索引和数据是分开存储的也叫非聚集索引
（2）InnoDB: INNODB的主键索引和辅助索引采用的是B+树作为索引存储结构，在主键索引的叶子节点存放的是主键及所有数据，辅助索引的叶子节点存储的是关键字和主键值，查询其他数据还需要借助主键索引，INNODB的数据和索引存放在一起，也把INNODB叫做聚集索引.这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

二、哪些情况需要创建索引

①主键自动建立唯一索引。
②频繁作为查询条件的字段应该创建索引。
③查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
④查询中与其他表关联的字段，外键关系建立索引。
⑤查询中统计或者分组的字段。

三、哪些情况不需要创建索引

1.表记录太少
2.经常增删改的表
为什么？
提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERT、 UPDATE和 IDELETE。因为更新表时, MYSQL不仅要保存数据,还要保存一下索引文件
3.数据重复且分布平均的表字段。
注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。例如性别字段，只有男女，不适合建立索引

四、索引失效

1.全值匹配我最爱（最佳使用索引）
建立索引所使用的字段与查询所使用的字段完全相符且符合最左前缀法则。

2.最佳左前缀法则：
如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。

3.在索引列上做任何操作(计算、函数、(动or手动)类型转换),会导致索引失效而转向全表扫描。

4.存储引擎不能使用索引中范围条件右边的列。

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少 select*。

6. mysql在使用!=或者<>的时候无法使用索引会导致全表扫描。

7. is null, is not null也无法使用索引。
8. like%要写在字符的右边比如（‘name%’），如果字符串以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。
   

面试问题:解决like%字符串%时索引不被使用的方法
利用覆盖索引解决（所谓覆盖索引就是建的索引和所查字段一致）
注意：所建索引的字段与查询所用的字段有一个或全部相符，索引都可以使用，但查询的字段中有不存在于所建索引中的字段时，索引会失效（主键字段除外）。如下
举例：

#建立索引
CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);

1
2
3

9.字符串不加单引号索引会失效

10.少用or用它来连接时会索引失效

总结：
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难!

一般性建议：
对于单键索引,尽量选择针对当前 query过滤性更好的索引
在选择组合索引的时候,当前 Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
在选择组合索引的时候,尽量选择可以能够包含当前 query中的 where字句中更多字段的索引
尽可能通过分析统计信息和调整 query的写法来达到选择合适索引的目的