索引分类:
1、按存储结构:
B+Tree索引
Hash索引
2、按应用层次:
主键索引(聚簇索引):索引列中的值必须是唯一的(不允许有空值、重复值)
id int auto_increment primary key 自动创建主键索引
普通索引:MySQL中基本索引类型,没有什么限制(允许有空值、重复值)
create index 索引名 on 表 (列名,);创建表之后在创建索引 (推荐方式创建)
唯一索引:索引列中的值必须是唯一的(允许有空值)
create unique index 索引名 on 表名(列名 desc,列名)
全文索引:对文本的内容进行分词,进行搜索()。Mysql5.6之后MyISAM、InnoDB引擎支持。
只能在文本类型 char、varchar、text 类型字段上创建全文索引。
主要是为了解决对文本(长字段)模糊查询效率低的问题。 where name like "%word%"
create fulltext index 索引名 on 表名(列名)
复合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并()。
顾名思义:最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
联合索引遵守"最左前缀"原则,即在查询条件中使用了联合索引的第一个字段,索引才会被使用。
因此,在联合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找,则无法使用索引。
create index 索引名 on 表名(索引字段,索引字段,...)
-- 联合索引字段 username,phone,create_date
-- 1. 可以
select * from t_user where username = 'admin';
-- 2. 可以
select * from t_user where username = 'admin' and phone = '123456';
-- 3. 可以
select * from t_user where username = 'admin' and phone = '123456' and create_date = '2019-07-18 17:04:12';
-- 4. 不可以
select * from t_user where phone = '123456';
最左前缀原则:当创建(a,b,c)联合索引时,相当于创建了(a)单列索引,(a,b)联合索引以及(a,b,c)联合索引。
想要索引生效的话,只能使用(a)和(a,b)和(a,b,c)三种组合;当然,测试过(a,c)组合也可以。(b,c)就不可以。
如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:如(a,c)只有a索引才会生效。
覆盖索引:SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。
3、数据行的物理顺序与列值的逻辑顺序相同:
聚集索引
非聚集索引
聚集索引与非聚集索引的区别:
(1)一个表中只能拥有一个聚集索引,
而非聚集索引一个表可以存在多个。
(2)聚集索引,索引中键值的逻辑顺序决定了表中相应行的物理顺序;
非聚集索引,索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
(3)索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。
而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。
(4)聚集索引:物理存储按照索引排序;
非聚集索引:物理存储不按照索引排序;
4、索引语法:
创建索引:CREATE [UNIQUE|FULLTEXT|SPATIAL]INDEX_TYPE INDEX index_name ON tbl_name(column_name,...)
查看索引:show index from table_name;
删除索引:DROP INDEX index_name ON tbl_name;
添加索引:
1). alter table tb_name add primary key(column_list);
该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL
2). alter table tb_name add unique index_name(column_list);
这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)
3). alter table tb_name add index index_name(column_list);
添加普通索引, 索引值可以出现多次。
4). alter table tb_name add fulltext index_name(column_list);
该语句指定了索引为FULLTEXT, 用于全文索引
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。