MySQL索引的分类
我们根据对以列属性生成的索引大致分为两类:
单列索引 :以该表的单个列,生成的索引树,就称为该表的单列索引
组合索引:以该表的多个列组合,一起生成的索引树,就称为该表的组合索引
单列索引和组合索引具体细的划分:
主键索引(单):以该表主键生成的索引树,就称为该表的主键索引
唯一索引(单):以该表唯一列生成的索引树,就称为该表的唯一索引
普通索引(单):以该表的普通列(非主键,非唯一列)生成的索引树,就称为该表的普通索引
全文索引(单,双):全文搜索,可以单列也可以多列一起使用
前缀索引(单):前缀索引一般只能用于普通索引当中,以最左匹配为原则
组合索引(双):是用多个列组合构建的索引
# 修改表
alter table {table_name} add primary key ( `column` ); #主键索引
alter table {table_name} add unique index {index_name}({column_name}); # 唯一索引
alter table {table_name} add index {index_name}({column_name}); # 普通索引
alter table {table_name} ADD FULLTEXT index remark_fulltext(remark); #全文索引
alter table {table_name} add key(column_name(prefix_length)); #前缀索引
alter table {table_name} add index group_index(`tel`,`email`); #组合索引
全文索引有自己的语法格式,使用 match 和 against 关键字,比如
select * from {table_name} where match(remark) against('xxx');
注意: match() 函数中指定的列必须和全文索引中指定的列完全相同,否则就会报错,无法使用全文索引,这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引,请单独为该列创建全文索引。
前缀索引坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。
索引底层和组合索引具体看我的博客:https://blog.csdn.net/qq_39291929/article/details/100132979问题1:给一个行字符串加索引,请问可以建立那些索引?答:普通索引、前缀索引、全文索引
主键索引和普通索引的区别
1.主键索引索引着数据,然而普通索引索引着主键ID值(这是在innodb中,但是如果是myisam中,主键索引和普通索引是没有区别的都是直接索引着数据)
2.当你查询用的是where id=x 时,那只需要扫描一遍主键索引,然后拿到相应数据,但是如果是查询的普通索引的话,那么会先扫描一次普通索引,拿到主键值,然后再去扫主键索引,拿到所需要的数据,这个过程叫做回表
mysql存储引擎MyISAM 与 InnoDB区别?
MyISAM
1、不支持事务
2、只支持表级别锁
3、保存数据库表中表的具体行数(不需要全盘扫描)
4、只适合插入不频繁,查询非常频繁查询
5、不支持外键
InnoDB
1、支持事务
2、支持行级别锁(必须建立索引基础上)
3、支持外键
为什么MylSAM查询效率比InnoDB效率高?
1、innodb寻址要映射到块,再到行,MYISAM记录的直接是文件的OFFSET,定位比INNODB要快
2、INNODB还需要维护MVCC一致;需要去检查和维护