优势
可以提高检索效率,降低IO成本—检索
通过索引进行排序,降低排序成本,减低CPU消耗—排序
劣势
单列索引
组合索引
全文索引
只有在MyISAM引擎和InnoDB(5.6以后)才能使用,而且只能在char ,varchar,text 类型字段上使用全文索引。 fulltext
优先级最高,不会执行其他索引
存储引擎 决定执1个索引
空间索引
创建索引
CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;
CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);
CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;
DROP INDEX index_name ON table
SHOW INDEX FROM table_name
索引存储结构
B+树只在叶节点储存,而且数据都在一行,有指针指向,有顺序,索引列使用 order by
主键索引
数据和索引在一起的就是聚集索引,叶节点包含了完整的数据记录, InnoDB 的数据本身要按主键聚集。
InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以
唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。
辅助索引
InnoDB 的辅助索引 data 域存储相应记录主键的值,而不是不是全部数据,找到主键在进行主键索引
select * from user where name='Alice' 回表查询 检索两次 非主键索引 --- pk---索引--->数据
select id,name from user where name='Alice' 不需要回表 在辅助索引树上就可以查询到了 覆盖索引
(多用组合索引)
哪些情况需要创建索引?
哪些情况不需要创建索引
为什么要使用组合索引
mysql创建组合索引的规则是:先会对组合索引的最左边的,也就是第1个name字段的数据进行排序,在第1个字段的排序基础上,然后再对第二个的cid字段排序。其实就相当于实现了类似order by name cid的排序规则。
节省mysql索引存储空间以及提升搜索性能,可建多组合索引(能使多组合索引就不使单列索引)
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')
创建了组合索引(col1 , col1 col2, col1 col2 col3 三个索引)
一颗索引树上创建3个索引:省空间
组合索引遵循最左前缀原则:
1、前缀索引 like a%
2、从左向右匹配直到遇到范围查询 > < between like 停止
建多组合索引 (a,b,c,d)当执行where a=1 and b=1 and c>3 and d=1
到c>3停了 所以d 用不到索引了,如何解决?删除索引重新构建,调整索引顺序。
create table 'tablename' (id int primary key , a int , b int ,c int,d int);
alter table 'tablename' add index index_com(a,b,c,d);
drop index idx_com on "tablename" ;
create index idx_com on 'tablename'(a,b,d,c)
MySQL 提供了 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划分析, 并输出 SELECT 执行的
详细信息,查看该SQL语句有没有使用上了索引,有没有做全表扫描,了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及运用SQL语句时哪种策略预计会被优化器采用等。以供针对性优化。
explain select * from user;
参数:
类型 | 含义 |
---|---|
system | 表只有一行 |
const | 表最多只有一行匹配,通用用于主键或者唯一索引比较时 |
eq_ref | 每次与之前的表合并行都只在该表读取一行,这是除了system,const之外最好的一种,特点是使用=,而且索引的所有部分都参与join且索引是主键或非空唯一键的索引 |
ref | 如果每次只匹配少数行,那就是比较好的一种,使用=或<=>,可以是左覆盖索引或非主键或非唯一键 |
fulltext | 全文搜索 |
ref_or_null | 与ref类似,但包括NULL |
index_merge | 表示出现了索引合并优化(包括交集,并集以及交集之间的并集),但不包括跨表和全文索引。这个比较复杂,目前的理解是合并单表的范围索引扫描(如果成本估算比普通的range要更优的话 |
unique_subquery | 在in子查询中,就是value in (select…)把形如“select unique_key_column”的子查询替换。不一定in子句中使用子查询就是低效的! |
index_subquery | 同上,但把形如”select non_unique_key_column“的子查询替换 |
range | 常数值的范围 |
index | a.当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index);b.以索引顺序从索引中查找数据行的全表扫描(无 Using Index);c.如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思;d.如单独出现,则是用读索引来代替读行,但不用于查找 |
all | 全表扫描 |
部分内容来着开课吧。
本文地址:https://blog.csdn.net/ClarkMOSS/article/details/107622409
如对本文有疑问, 点击进行留言回复!!
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
网友评论