MySQL索引

一、索引介绍

1.什么是索引---索引是一种帮助系统能更快查找相应的数据

2.索引的作用---加快表的查询速度，还可以对字段排序

1.加快查询速率（利用定位技术）

2.提高查询速度

3.降低数据库的IO成本，排序成本

4.加快表与表之间的连接

5.通过创建唯一性索引保证表数据的唯一性

6.使用分组和排序时，可以大大减少分组和排序时间

3.索引的副作用

1.索引占用额外的磁盘空间

2.更新一个包含索引的表需要花费更多的时间

4.索引工作方式

1.没有索引的情况下，查询数据时，先扫描全表，然后才根据查询条件，定位某行的数据位置；

2.有索引的情况下，会先通过查询条件的字段值找到其索引对应行记录数据所对应的物理位置，

然后根据物理位置直接访问相应的行记录的主句，就像根据书目录中的页码去快速找到对应章节的内容

5.MySQL 的优化 哪些字段/场景适合创建索引，哪些不适合

1、小字段

2、唯一性强的字段

3、更新不频繁，但查询率很高的字段

4、表记录超过300+行

5、主键、外键、唯一键

二、索引分类

普通索引--唯一索引--主键索引--组合索引--全文索引

1.普通索引---最基本的索引类型，没有唯一性之类的限制

        1.直接创建索引

                create index 索引名称 on 表名 (字段名);

        2.修改表方式创建

                alter table 表名 add index 索引名称 (字段);

        3.创建表的时候指定索引

                create table 表名(字段1 数据类型,....index 索引名称 (字段));

2.唯一索引---列的每个值都唯一，允许有空值。添加唯一键将自动创建唯一索引。

        1.直接创建索引

                create unique index 索引名称 on 表名 (字段名);

        2.修改表方式创建

                alter table 表名 add index 索引名称 (字段);

        3.创建表的时候指定索引

                create table 表名(字段1 数据类型,....unique 索引名称 (字段));

3.主键索引---一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引

        1.修改表方式创建

                alter table 表名 add primary key(列名);

        2.创建表的时候指定索引

                create table 表名(字段1 数据类型,....primary key(列名));

4.组合索引

        a.可以是单列上创建的索引，也可以是在多列上创建的索引

        b.满足最左原则，使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效

        1.直接创建索引

                create index 索引名称 on 表名 (字段1,字段2...);

        2.修改表方式创建

                alter table 表名 add index 索引名称 (字段1,字段2...);

        3.创建表的时候指定索引

                CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

5.全文索引（FULLTEXT）---在进行模糊查询的时候使用

        a.全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建

        b.每个表只允许有一个全文索引。

        1.直接创建索引

                create fulltext index 索引名称 on 表名 (字段名);

        2.修改表方式创建

                alter table 表名 add fulltext 索引名称 (字段);

        3.创建表的时候指定索引

                create table 表名(字段1 数据类型,....fulltext  索引名称 (字段));

        4.使用全文索引查询

                SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

6.查看索引

        1.直接查看

                show create table 表名;

                show index from 表名;

                show keys from 表名;

        2.使用explain分析查询语句是否使用索引

                explain select语句;

7.删除索引

        1.直接删除索引

                drop index 索引名 on 表名;

        2.修改表方式删除索引

                alter table 表名 drop index 索引名;

        3.删除主键索引

                alter table 表名 drop primary key;

三、索引总结

        索引分为：

      ① 普通索引 ：针对所有字段，没有特殊的需求/规则

      ② 唯一索引 : 针对唯一性的字段，仅允许出现一次空值

      ③ 组合索引 (多列/多字段组合形式的索引)

      ④ 全文索引（varchar char text）MySQL为了优化对文本内容搜索的一种机制

      ⑤ 主键索引 ：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引

创建索引：

① 在创建表的时候，直接指定index

② alter修改表结构的时候，进行add 添加index

③ 直接创建索引index

PS:主键索引——》直接创建主键即可