MySQL索引

一、索引

1、数据库中的索引
说明：索引是一种排好序的便于快速查找的数据结构
作用：帮助数据库高效的查询数据
2、四种索引形式
普通索引（index）：仅仅为了提高查询速度
唯一索引（unique）：保存数据唯一性不能重复
主键索引（primary key）：既保存数据唯一又不能为null
全文检索（fulltext）：提取关键字添加索引——用于like
可设置组合索引：给多个字段添加相同的索引
3、索引优缺点
①优点
->提高数据查询的效率，降低数据库的IO成本
->通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗
②缺点
->占用磁盘空间
->大量索引影响SQL语句效率，因为每次插入和修改数据都需要更新索引
③总结
滥用索引反而适得其反，可以通过开启慢查询日志和通过explain语法分析。
4、增加索引&查看索引
-> 需求

编号  -  主键索引
姓名  -  唯一索引
密码  -  普通索引
a和b  -  复合/组合索引
info  -  全文索引

-> 创建索引表

create table stu1(
    id int unsigned auto_increment,
    uname varchar(20) not null comment '用户名',
    pwd char(32) not null comment '密码',
    a varchar(20) not null,
    b varchar(20) not null,
    info text,

    primary key (id),
    unique (uname),
    index (pwd),
    index (a,b),
    fulltext (info)
)engine=myisam charset=utf8;
注：unique unm(uname)则可以将索引名设置为unm，索引名如果不加则默认字段名。

-> 查看索引表

方式1：show index from stu1;

方式2：`show create table stu1;

5、修改添加索引&删除索引
语法 ：
修改表添加索引：alter table 表名 add 索引类型 索引名（字段）
删除表指定索引：drop index 索引名 on 表名
注：如果要修改已经创建的索引，就要先删除索引，然后再修改添加索引。

二、如何选择合适的列创建索引

选择规则：
-> 为常做条件、排序、分组、联合操作的字段建立索引
-> 选择唯一性索引（同值较少）
-> 选择较小的数据列，为较长的字符串使用前缀索引（索引文件更小）

三、索引的使用原则

-> like查询（“%”和“_”开头，注意尾部可以）索引失效
-> or运算都要具有索引否则索引失效
-> where条件字段是字符串类型必须加引号
-> 组合索引字段单独使用，左边生效，右边失效（左原则）

四、前缀索引

前缀索引：提权字段内容前n个字符，添加索引（注：前n个字符必须具备唯一性）
语法：alter table 表名 add 索引类型 索引名（字段（字符数））
示例：alter table stu1 add index info(info(5))

五、全文索引（不支持中文）

1、概念
全文索引：就是提取指定字段内容，添加索引（代替like语句）
2、语法
设置：alter table 表名 add fulltext 索引名(字段);
使用：select * from 表名where match(字段) against(‘内容’ IN BOOLEAN MODE);
参数：
字段可以写多个，如：title,content（注：不加引号）
关键词可以搜索多个，如：‘内容1 ... 内容n’ （注：多个空格隔开，并且在一个引号内容）
注意：‘内容’部分不支持中文，必须是一个连续的数字和英文组成的‘单词’，且不低于四个字节，如果有空格或者其它字符则视为分隔符，如：‘abcd’、‘56789’、‘ab123’

六、索引结构

1、索引结构类型如下

2、示例表

3、MyIsam索引结构
MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

4、InnoDB索引结构
在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

5、聚簇索引和非聚簇索引
非聚簇索引：数据和索引分开存放
聚簇索引：数据和索引一起存放