在表中建立索引,然后在索引中找到符合查询条件的索引值。索引占用磁盘空间,并且降低增删改速度,如果非常频繁地更新,则需要限制索引数量,在表较大时再建立索引。
需要索引:
1、主键自动建立唯一索引
2、频繁作为查询条件的字段,作为范围条件的
3、查询中与其它表关联的字段,在连接操作中使用的
4、查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
5、查询中统计或者分组字段
6、单值/组合索引的选择问题:在高并发下倾向创建组合索引
不需要索引:
1、数据少
2、经常增删改,频繁更新的字段
3、数据重复且分布平均的字段
索引优点
1、加快检索,降低IO成本,不需要全表扫描;
2、创建唯一性索引,保证数据库表中每一行数据的唯一性;
3、加速表和表之间连接;
4、order by、group by减少分组和排序的时间。
索引缺点
1、索引占用磁盘空间。
2、对表中的数据进行增删改,索引也要动态维护。
四种索引:
普通索引、唯一索引、主键索引、聚集索引
1、普通索引
最基本的索引类型,没有唯一性限制。CREATE INDEX name ON tablename;
2、唯一索引
唯一索引不允许任何两行具有相同索引值。
对某个列建立UNIQUE索引后,插入新记录时自动检查新纪录在该列上是否取了重复值,CREATE UNIQUE INDEX name ON tablename ;
3、主键索引
为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。要求主键中的每个值都唯一。
4、聚簇索引
行的物理顺序与索引键值的逻辑顺序相同,数据在物理上按顺序排在数据页上,重复值也排在一起,在范围检查或使用group by或orderby查询时,一旦找到范围中第一个键值的行,后续行在物理上连在一起不必进一步搜索,避免了大范围扫描,大大提高查询速度。一个表只能包含一个聚簇索引。
聚簇索引提供更快速度,适用于很少对基表增删改操作的情况。Innodb聚簇索引包含整行的数据,所以索引就是数据本身,索引即数据。在创建主键约束时,会自动为表生成唯一聚簇索引。聚簇索引和主键同义。
另外:
5、多列索引
可以区分其中一列可能有相同值的行。可以建立为普通索引或者是唯一索引。
6、非聚簇索引/辅助索引
数据库表中记录的物理顺序与索引顺序可以不相同。一个表中只能有一个聚簇索引,但表中的每一列都可以有自己的非聚簇索引。非聚簇索引其实就是普通索引,不存储全部数据而只存储聚簇索引的值,一般为主键id。
非聚簇索引查找数据时,通过回表使用在非聚簇索引中找到的对应的主键id到主键索引中查找数据,找到后返回。
MySQL主键索引是聚簇索引,索引查询找到索引列和主键聚簇索引,若不包含索引列则根据主键聚簇索引二次回表查询,若包含索引列则根据索引列。即使没有声明主键也会默认根据一个非空列生成聚簇索引。
InnoDB存储引擎使用B+树索引。计算机的最小存储单元是块(block)默认4k大小,读取数据是一块一块读取的,MySQL以页(page)存储默认大小16KB。
B树和B+树
平衡二叉树很高,查找数据时进行多次磁盘IO效率低。
B树:
B树每个节点存储了更多的键key和数据,并且每个节点有更多子节点,查找数据磁盘IO次数少效率高。
B+树:
平衡的多叉树,非叶子节点不再存储数据,叶子节点都有双向的指针形成双向链表。3层的b+树可以表示上百万的数据。如果B+树一个节点可以存储1000个键值,那么3层B+树可以存1000×1000×1000=10 亿个数据。一般根节点是常驻内存的,所以一般查找10亿数据只需要 2 次磁盘 IO。B+树能够保持数据稳定有序,插入修改稳定的对数时间复杂度,元素自底向上插入。
B和B+区别:
B+树非叶子节点上不存数据,只存键值,而B树存键值和数据。在页固定大小情况下,B+树存储更多键值,树的阶数更大,树就更矮更胖,磁盘IO次数更少。
B+树索引的所有数据存在叶子节点,而且顺序排列。叶子节点间通过单向链表连接,可以找到所有数据。
InnoDB和MYISAM区别:
Innodb数据和索引在一起,索引即数据,而MYISAM分开存储。
Innodb索引有主次,区分聚簇索引和非聚簇索引,而MYISAM不区分主次。
尽量使用覆盖索引,即只访问索引的查询,多列索引左前缀法则。
导致索引失效的情况:
使用不等于(!= 或者<>)导致全表扫描,判断空也无法使用索引,like 通配符开头(’%abc)导致全表扫描,or会索引失效
参考:
https://blog.csdn.net/Lucky_Qxk/article/details/123185859
很多文章相互矛盾,前后冲突,概念定义常有不同,本文中如有错误望请指出改正。
Jarvis 24.03.22