Mysql索引的创建与设计原则

1，索引的分类：

索引包括普通索引，唯一性索引，全文索引，单列索引，多列索引和空间索引；

从功能逻辑分：普通索引，唯一性索引，主键索引，全文索引。

按照物理实现方式：聚簇索引和非聚簇索引。

按照作用字段个数：单列索引和联合索引

1，全文索引 ：不加任何限制条件，只是用于提高查询速度。不要求非空唯一。

2，唯一性索引：使用unique参数可以设置为唯一索引，允许为空，一张数据表可以有多个唯一性索引。

3， 主键索引：特殊的唯一索引，不能为空，一张表只能有一个主键索引。

4，单列索引：在单个字段上创建索引，一个表可以有多个点列索引

5，多列索引：多个字段组合创建的索引， 遵循最左前缀集合。

6，全文索引：使用参数fulltext可以设置索引为全文索引，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。分为 自然语言的全文检索和布尔的全文检索。

7，空间索引：使用参数spatial。

2，创建索引

创建索引的三种方式：

1，创建表时创建索引

隐式的方式创建索引。在声明有主键约束，唯一性约束，外键约束的字段上，会自动的添加相关的索引。

create table dept(
id  int primary key auto_increment, //主键索引
name varchar(20) unique,//唯一索引
constraint e_d_id foreign key(did) references dept(dept_id),//外键索引
)

显示方式的创建

创建普通索引

create table dept(
    id  int ,
    name varchar(20) ,
    info varchar(20),
  #声明索引
    index index_name (name)，
    unique index uk_c(info)  #唯一性索引   单列索引
)

删除主键索引：

alter table 表名 drop primary key;

创建联合索引

create table dept(
    id  int ,
    name varchar(20) ,
    info varchar(20),
    #声明索引
    index index_name_info (name,info)
)

创建全文索引

只为char, varchar和text列创建索引，对整个列进行，不支持局部索引。

create table dept(
    id  int ,
    name varchar(20) ,
    info varchar(20),
    #50代表只使用前50个进行构建索引
    fullText index futxt_info(info(50))
    )

创建全文索引之后 使用match+against方式查询，提高查询效率

select * from dept where match (info) against('查询字符串')

如果需要全文索引 的事大量数据，先添加数据再创建索引。

通过命令查看索引

方式1，

show create table dept；--depet代表表名

方式2，

show index from dept；--depet代表表名

2，通过alter table创建索引

表已经创建成功添加索引

1，altert table .....add....

Alter table 表名 add index id_cmm (name),

Alter table 表名 add unique uk_name(name),

Alter table 表名 add index nul_id_name(id,name)

3，通过create index创建索引

create index....... on .........

create index id_name on emp(name);

create unique index id_name on emp(name);

3，删除索引

1，alter table

alter table 表名 drop index 索引名

注意：添加auto_increment 约束字段的唯一索引不能删除

2，drop index

drop index 索引名 on 表名

删除字段之后，索引相应删除。

联合索引，如果删除的列为索引的组成部分，则该列也会从索引中删除，如果组成索引的列全部被删除，相应的索引也会被删除。

7，索引的新特性

1，降序索引

create table dept(
    id  int ,
    id2 int,
    index index_id_id1(id ASC,id2 DESC )
)
​
Explain select * from dept order by a, b desc limit 5; 

2，隐藏索引

1，为什么使用隐藏索引

数据量大时，删除索引容易出错，所以使用隐藏索引，将待删除的索引设置为隐藏索引，确认是否出错，没有出错则可以删除索引。这种方式叫做软删除。

可以验证删除索引之后性能是否有影响，可以使用隐藏索引。

注意：主键不能设置为隐藏索引，当表中没有主键时，表中第一个唯一非空索引会成为隐式主键。

2，创建隐藏索引

create table dept(
    id  int ,
    name varchar(20) ,
    info varchar(20),
    con  varchar(20),
    #创建隐式索引 
    index idx_name (name) invisible
    )

alter table dept add unique index uk_name(name) invisible,

create index idx_name on dept (name) 

3，修改索引的可见性

alter table dept alter index idx_name invisible #由可见修改为不可见

如果一个索引长期隐藏不如删除，影响性能

4，隐藏索引对查询优化可见

mysql> select @@optimizer_switch \G

使隐藏索引有效

use_invisible_indexes=on;

8，索引的设计原则

1，索引设计的不合理或者缺少索引都会影响性能。 ​ ​ ​

1,适合创建索引的11种情况

1，字段的数值有唯一性限制

某个字段是唯一性的，可以直接创建唯一性索引

业务上具有唯一特性的字段，即使是组合字段也必须建成唯一索引。

2，频繁作为where查询条件的字段

3，经常group by 和order by 的列

4， 在UPDATE ,DELETE 的where 条件列

如果进行更新时，更新的字段是非索引字段，提升的效率会更加的明显，这是因为非索引你字段更新不需要对索引进行维护

5，DISTINCT 字段需要创建索引

6，多表join链接操作时，创建索引注意事项

首先链接的表尽量不要超过三张。

其次对where条件创建索引

最后对用于连接的字段创建索引 并且该字段在多张表中的类型必须一致。

7，使用列的类型小的创建索引

类型的大小指的是数据范围的大小

8，使用字符串的前缀创建索引

截取字段前面一部分内容建立索引

怎么计算不同长度的选择性？

1，先看一下字段在全部数据中的选择度

select count(distinct address) /count(*) fropm 表名

2，通过不同的长度去计算，与全表的选择性对比

count(distinct left(列名，索引长度)) / count(*)

e.g

select count(distinct left(address，12 ))/count(*) as sub from emp;

注意：

在varchar字段上简历索引时，必须指定索引的长度，没必要对全字段进行索引。

9，区分度高（散列性）高的列适合作为索引

列的基数指的是某一列中不重复数据的个数。最好为基数大的列建立索引

在记录行数一定的情况下，列的基数越大，该列中的值越分散。

10，使用最频繁的列放到联合索引的最左侧

11,在多个字段都要创建索引的形况下，联合索引优于单值索引

注意：索引数目不是越多越好 建议单张不超过六个

因为每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大

索引会影响 insert delete update 的性能，因为表中的数据更改的同事索引也会进行调整和更新，会造成负担

优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

2，不适合创建索引的情况

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

2，数据量小的表最好不要使用索引

3， 有大量重复数据的列不要建立索引

数据重复度高于10% 的时候，不需要对这个字段使用索引

4， 避免对经常更新的表创建过多的索引

5，不建议无序的值作为索引

6，删除不在使用或者很少使用的索引

7，不要定义冗余或者重复的索引

索引可以提高查询效率。也会降低插入和更新的速度并占用磁盘空间

 

转载请注明出处