索引的定义
是对数据库表中一列或多列的值进行排序的一种结构
mysql的索引是存储引擎层而不是在服务器层实现的,所以并没有统一的索引标准
索引好比书的目录,通过目录可以快速搜索到想要查找的内容,要了解索引的利弊,索引利用好了就是一辆"法拉利跑车",用不好就是一辆"破旧的三轮车"
查看表索引:
show index from 表名
InnoDB有两大类索引:
- 聚集索引(clustered index)
- 普通索引(secondary index)
InnoDB 聚集索引的叶子节点存储行记录,因此InnoDB必须要有,且只有一个聚集索引:
(1)如果表定义了PK(PRIMARY KEY),则PK就是聚集索引;
(2)如果表没有定义PK,则第一个not NULL unique列是聚集索引;
(3)否则,InnoDB会创建一个隐藏的rowid作为聚集索引;
画外音:所以PK查询非常快,直接定位行记录
mysql中如果表没有加主键,InnoDB会创建一个隐藏的rowid来当作主键,也就是是否可以通过select _rowid from table查询出来
索引作用
- 优点
1 提高数据检索效率
2 提高聚合函数的效率
3 提高排序效率
4 使用覆盖索引可以避免回表(不懂?)
索引结构
1 二叉树结构
2 B+Tree
3 哈希结构
二叉树结构
特点:
1 每个节点至多有2个子节点
2 有左右序之分,次序不能颠倒,左子树键值永远比右子树的小,且小于根键值
索引类型
1 主键索引和唯一索引
2 覆盖索引 (利用覆盖索引来进行查询操作,避免回表(开发手册索引规约))
3 前缀索引
- 联合索引
联合索引又叫复合索引,是在表中2个或2个以上的列创建的索引,利用索引中的附加列可以缩小检索的段池范围,更快的搜索到数据,联合索引在使用的过程中必须满足最左前缀原则🔥,一般把选择性高的列放在前面,一条索引语句可以只使用索引中的一部分,但必须从最左侧开始⚛️
索引创建的四个不要
- 选择性低的字段不要用索引(如sex,status等)
- 很少查询的列不要使用索引
- 大数据类型字段不要使用索引
- 尽量避免使用Null,应该指定列为NOT NULL
使用不到索引的情况
1 🚹联合索引中第一个查询条件不是最左索引列以及第一个查询条件不是最左前缀列
2 ❇️模糊查询条件列最左以通配符"%"开始(可以考虑放在子查询中)
3 💢查询字段上有索引,但是使用了函数运算
Mysql如何为表字段添加索引
1.添加PRIMARY KEY(主键索引)
ALTER TABLE table name ADD PRIMARY KEY ( `column` )
CREATE INDEX 索引名 ON 表名(version); // version为表字段名
2.添加UNIQUE(唯一索引)
ALTER TABLEtable_name ADD UNIQUE ( `column` )
3.添加INDEX(普通索引)
ALTER TABLE table_name ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
ALTER TABLE table_name ADD FULLTEXT ( `column`)
5.添加多列索引
ALTER TABLE table_name ADD INDEX index_name ( `column1`, `column2`, `column3` )
【推荐】SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是consts最好。
说明:
1) consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
2)ref指的是使用普通的索引(normal index)
问题
1 主键索引和唯一索引的区别是什么?
3 全表扫描有哪些情况
3 当数据表中A、B字段做了组合索引,那么单独使用A或单独使用B会有索引效果吗?(使用like查询如何有索引效果)
评论区