Mysql索引

主键索引

• 创建主键索引

对有主键的数据进行插入时，即使插入乱序，表也会按主键的键值进行排序，这样方便引入页内目录，主键自动形成主键索引

create table 表名(id int primary key);

create table 表名(id int,  primary key(id));

• 查看主键索引

show index from 表名\G;

• 删除主键

alter table 表名 drop primary key;

• 添加主键

alter table 表名 add primary key(id);

普通索引

• 创建普通索引

create table 表名(id int primary key, index(xxx));

alter table 表名 add index(xxx); --对xxx键创建索引, 对已存在的表进行修改

create index xxx重命名 on 表名(xxx);

• 删除普通索引

alter table 表名 drop index xxx;

• 如果存在唯一键，默认也会为唯一键构建普通索引

create table 表名(id int primary key, name varchar(20), unique(name));

全文索引

对文章大量文字检索时，会分词并统计建立全文索引，MySql可以实现全文索引，但存储引擎必须时MyISAM，且默认不支持中文。若要使用中文检索，可以使用coreseek插件。

create  table 表名(id int primary key, title VARCHAR(50), body TEXT, FULLTEXT(title, body)) engine=MyISAM;

• 查找

select * from 表名 where body like "%xxx%"; -- 搜索body包含xxx，在左前面使用explain 关键字可以查看检索过程，逐次查找

select * from 表名 where match(title,body) against ('xxx'); --会使用索引查找

Mysql数据交互的基本单位是16kb——Mysql的page。可以通过以下命令查看：

show global status like 'innodb_page_size';

索引原理

数据库对数据的访问是在内存中进行的，但会定期将数据刷新到磁盘。

数据默认是以链表进行组织，而索引的本质是在每一个page添加了目录，并且为每个page也添加了目录（以page内的最小键值为参考）。若数据增多，多个目录一起也可以建立一个目录，构成B+树。查找时，不用将无效的数据加载入内存了，当然增加和删除数据时，会增加一定的负担。

• 为何是B+树？保证叶子节点全部相连，便于查找；节点不存储数据，可以存更多的key，减少IO操作次数

InnoDB（聚簇索引）和MyISAM（非聚簇索引）的区别：

• InnoDB是将数据和索引放在一起的；MyISAM将数据和Page分离，叶子节点只存储数据的地址。

• MyISAM建立的主键索引和辅助（普通）索引没区别，只不过普通索引允许键值的重复，本质还是因为叶子节点只存储地址；InnoDB构建的普通索引没有所有条目数据，只保存了对应的主键值，查找时需要先找到对应的主键值，再根据键值找数据，进行两边索引，即回表查询。