mysql数据库之索引

索引概念

mysql索引的概念
1、索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于c语言的链表通过指针指向数据记录的内存地址）。

2、使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。

3、索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。

4、索引是表中一列或者若干列值排序的方法。

5、建立索引的目的是加快对表中记录的查找或排序。

一、创建索引。

1、直接创建索引（主键索引无法使用此方式）   （create  index）
2、修改表结构方式添加索引                 （alter update）
3、创建表结构时创建索引                  （create  table）

 创建一个表，以便后面实验使用

use school;
create table class(
id int(10),
name varchar(10),
cardid varchar(10),
phone varchar(18),
address varchar(50),
remark text);

desc class;

insert into class values ('1','zhangsan','111111','123123','北京','this is vip');
insert into class values ('2','lisi','222222','234234','上海','this is vip');
insert into class values ('3','wangwu','333333','345345','广州','this is common');
insert into class values ('4','zhaoliu','444444','456456','南京','this is common');

select * from class;

直接创建索引和查询索引。

CREATE INDEX 索引名 ON 表名 (列名[(length)]);
#(列名(length))：length是可选项，下同。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾。

例：
create index name_index on class (name);
create index address_index on class (address(2));

 查询索引

show create table class;
show index from class\G
show keys from class;

修改表方式创建

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

例：
alter table class add index id_index (id);
show keys from class\G;

 创建表的时候指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));

例：
create table test(
id int(10),
name varchar(20),
index id_index (id));

show keys from test\G;

二、索引分类。

1.按照索引数据的存储方式可以将索引分为B树索引、位图索引、函数索引、簇索引、反序索引等。

2.按照索引列的唯一性又可以分为唯一索引和非唯一索引。

3.按照索引列的个数索引可以分为单列索引和复合索引。

总的来说，有：普通索引、唯一索引（允许有空值）、主键索引、组合索引、全文索引。

三、创建索引有哪些注意事项？

1、经常与其他表进行连接的表，在连接字段上应该建立索引；

2、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

3、字段的数值有唯一性的限制，适合创建索引；

4、经常 GROUP BY 和 ORDER BY 的列，适合创建索引；

5、UPDATE、DELETE 的 WHERE 条件列；

6、DISTINCT 字段需要创建索引。

四、索引失效的场景有哪些 ？

1、如果查询条件中带有or，除非所有的查询条件都建有索引，否则索引会失效；

2、模糊查询like关键字不可以以%开头；

3、如果列类型是字符串，那么，在查询条件中需要将数据用引号引用起来，否则不走索引；

4、在索引列上参与计算会导致索引失效；

5、在where中使用不到的字段，不要设置索引；

6、数据量小的表最好不要使用索引。