程序员社区

mysql--索引知识点整理

索引知识点整理

  • 索引分类
    • 主键索引
    • 单值索引
    • 唯一索引
    • 复合索引
    • 主键索引和普通索引的区别
    • 删除索引
    • 查看索引
    • 聚簇索引和非聚簇索引
    • 哪些情况下需要创建索引
    • 哪些情况下不适合创建索引
    • 性能分析
      • Explain 性能分析
        • EXPLAIN中的字段详解:加粗的字段表示的是重要的字段
          • id: select 查询的序列号,包含一组数字,表示查询中执行 select 子句或操作表的顺序
          • select_type :代表查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
          • table:表示的是 这个数据是基于哪张表的
          • `type`:表示的是查询的访问类型 一般的类型 结果值从最好到最坏依次是 `system >const> eq_ref >ref >range >index >all` 一般来说,得保证查询至少达到 `range 级别`,`最好能达到 ref`
          • possible_keys 显示可能应用在这张表中的索引,一个或多个 查询涉及到的字段上若存在索引,则该索引将被列出,但不一 定被查询实际使用
          • `key 实际使用的索引 如果为NULL,则没有使用索引`,查询中若使用了覆盖索引,则该索引仅出现在key列表中
          • key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好,key_len 显示的值为索引字段最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得的,不是通过表内检索出的。
          • ref 显示索引的哪一列被使用了,如果可能的话,是一个常数 哪些列或常量被用于查找索引列上的值
          • rows 显示 MySQL 认为它执行查询时必须检查的行数,越少越好
          • Extra 其他的额外重要的信息
      • 覆盖索引
  • innodb引擎下索引失效的情况
    • 一个独立索引的失效情况
    • 多个独立索引失效的情况
      • SELECT SQL_NO_CACHE ...不使用缓存查询,为了测试sql语句的效率,有时候要不用缓存来查询。
      • 文章
    • 两表优化方法
    • JOIN语句的优化
  • 总结

索引分类

主键索引

  • ​ 设置主键后数据库会自动建立索引

概念:设定表的某个(或某几个)字段为主键后数据库自动建立索引,innodb为聚簇索引.主键索引是唯一索引的特定类型,值要存在且不能是null

聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的记录数据

注意:如果主键是由多个字段构成,就称为复合主键(或者联合主键)

创建表时创建索引:

CREATE TABLE 表名(
...
PRIMARY KEY([字段名],[字段名]...)
)

通过alter方式添加主键索引

ALTER TABLE 表名 add PRIMARY KEY 表名([字段名],[字段名]...); 
-- 删除建主键索引:
ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引

  • 一个索引只包含单个列,一个表可以有多个单列索引

创建表时,创建索引:

CREATE TABLE 表名
(
..
KEY [索引名](字段名),
KEY [索引名](字段名)
)
#这里如果不写索引名,索引名就和字段名一致

在这里插入图片描述
创建完表后,单独创建索引名

-- 创建单值索引 
CREATE INDEX 索引名 ON 表名(列名); 
-- 删除索引: 
DROP INDEX 索引名;

通过alter的方式创建索引

ALTER TABLE 表名 ADD INDEX [索引名] (字段)
#如果不写索引名,索引名就和字段名一致

唯一索引

  • 索引列的值必须唯一,允许为 null,一张表可以有多个唯一索引

创建表时,创建索引:

CREATE TABLE 表名(
...
UNIQUE [索引名] (字段名),
UNIQUE [索引名] (字段名)
)
#不写索引名就和字段名一致

在这里插入图片描述
单独创建索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名); 
-- 删除索引 
DROP INDEX 索引名 ON 表名;

alter方式创建索引:

ALTER TABLE 表名 ADD UNIQUE INDEX [索引名] (字段);

复合索引

  • ​ 即一个索引包含多个列,在数据库操作期间,复合索引比单值索引所需要的开销更小(对于相同的多个列建索引),当表的行数远大于索引列的数目时可以使用复合索引

创建表时,创建复合索引

如果不写索引名,索引名就与第一个字段名一致

CREATE TABLE 表名(
...
KEY[索引名](字段名,..字段名)
)

单独创建符合索引

-- 创建复合索引
CREATE INDEX 索引名 ON 表名(1,2...); 
-- 删除索引: 
DROP INDEX 索引名 ON 表名;

alter方式创建索引

ALTER TABLE 表名 ADD INDEX [索引名] (字段,...字段)

主键索引和普通索引的区别

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述


删除索引

删除所有非主键索引

ALTER TABLE 表名 DROP INDEX 索引名
或者
DROP INDEX 索引名 ON 表名

删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY 

查看索引

SHOW INDEX FROM table_name\G;

聚簇索引和非聚簇索引

  • 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储 在一起
  • 聚簇索引的好处: 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不不用从多个数据块中提取数据,所以节省了大量的 io 操作
  • 聚簇索引的限制: 对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引,而 Myisam 并不支持聚簇索引。由于数据物理存储排序方式只能有一种,所以每个 Mysql 的表只能有一个聚簇索引 一般情况下就是 该表的主键为了充分利用聚簇索引的聚簇的特性,所以 innodb 表的主键列尽量选用有序的顺序 id,而不建议用 无序的 id,比如 uuid这种

哪些情况下需要创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其它表关联的字段,外键关系建立索引
  4. 频繁更新的字段不适合创建索引,因为每次更新不单单是更新了记录还会更新索引
  5. Where条件里用不到的字段不创建索引
  6. 单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
  7. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
  8. 查询中统计或者分组字段

哪些情况下不适合创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. where条件里面用不到的字段
  4. 过滤性不好的—sex=0,表示男,sex=1,表示女,那么不论查询0还是1,都会查出百分之50左右的数据量

性能分析

Explain 性能分析

  • 概念:使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的。分 析你的查询语句或是表结构的性能瓶颈
  • 用法:Explain+SQL 语句

EXPLAIN中的字段详解:加粗的字段表示的是重要的字段

在这里插入图片描述


id: select 查询的序列号,包含一组数字,表示查询中执行 select 子句或操作表的顺序

id就是每一个select语句执行的顺序

  • id 相同 执行顺序由上至下

例如下面: 先执行子查询,再执行外面的查询
在这里插入图片描述

  • id 不同 如果是子查询,id 的序号会递增,id 值越大优先级越高,越先被执行,括号里面的先被执行

在这里插入图片描述

  • id中 既有相同也有不同 ,id 如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id 值越大,优先级越高,越先执行

在这里插入图片描述

衍生虚表: DERIVED


select_type :代表查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询

在这里插入图片描述


table:表示的是 这个数据是基于哪张表的

type:表示的是查询的访问类型 一般的类型 结果值从最好到最坏依次是 system >const> eq_ref >ref >range >index >all 一般来说,得保证查询至少达到 range 级别最好能达到 ref
  • system 表只有一行记录(等于系统表),这是 const 类型的特列
  • const 表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引因为只匹配一行数据,所以很快 如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量,例如总裁办只对应一个总裁

在这里插入图片描述

  • eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描

在这里插入图片描述

  • ref 非唯一性索引扫描,返回匹配某个单独值的所有行.本质上也是一种索引访问,它返回所有匹配某个单独值的行, 然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体

在这里插入图片描述

  • range 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而 结束语另一点,不用扫描全部索引

在这里插入图片描述

  • index index与all的区别为index类型只遍历索引树,这通常比all快,因为索引文件通常比数据文件小(也就是说虽然all和index都是读全表,但index是从索引中读取,而all是从硬盘中读取的)
explain select id from t1; //id是主键索引
  • all 全表扫描将遍历全表以找到匹配的行
explain select * from t1;

possible_keys 显示可能应用在这张表中的索引,一个或多个 查询涉及到的字段上若存在索引,则该索引将被列出,但不一 定被查询实际使用
key 实际使用的索引 如果为NULL,则没有使用索引,查询中若使用了覆盖索引,则该索引仅出现在key列表中

覆盖索引: 查询的字段和复合索引里面字段的个数和顺序一致

#复合索引
create index idx_col1_col2 on t2(col1,col2);
#查询
explain select col1,col2 form t2;
key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好,key_len 显示的值为索引字段最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得的,不是通过表内检索出的。

ref 显示索引的哪一列被使用了,如果可能的话,是一个常数 哪些列或常量被用于查找索引列上的值
rows 显示 MySQL 认为它执行查询时必须检查的行数,越少越好

Extra 其他的额外重要的信息
  • Using filesort:说明 mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取 MySQL中无法利用索引 完成的排序操作称为“文件排序” (效率低需要优化)

当Query中包含ORDER BY操作,而且无法利用索引完成排序操作的时候,MySQL Query Optimizer不得不选择相应的排序算法来实现
在这里插入图片描述

  • Using temporary : 使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by和分组查询 group by(使用中间表 效率十分的低 需要进行优化)
    在这里插入图片描述
    在这里插入图片描述

Using index for group-by:数据访问和Using index一样,所需数据只须要读取索引,当Query中使用GROUP BY或DISTINCT 子句时,如果分组字段也在索引中,Extra中的信息就会是Using index for group-by

Using where:如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,就会出现Using where信息

在这里插入图片描述

  • Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,如果同时出现 using where,表明索引被用来执行索引键值的查找;如果没有同时出现 using where,表明索引只是用来读取数据而非利用索引执行查找

所需数据只须在index即可全部获得,不需要再到表中查找数据

在这里插入图片描述

覆盖索引

覆盖索引: 就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列表要被所建索引覆盖

如果要使用覆盖索引,注意select列表中只取出需要的列,不可select * ,如果将所有字段一起做所有会导致索引文件过大,查询性能下降

  • Using where 表明使用了 where 过滤
  • Using join buffer 使用了连接缓存
  • impossible where where 子句的值总是 false,不能用来获取任何元组 一般都是where条件出问题

innodb引擎下索引失效的情况

一个独立索引的失效情况

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
where后面要跟着一个索引字段,这里的deptid是索引列

#少于总数百分之五十---使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid IS NULL;
#大于总数百分之五十--不使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid IS NOT NULL;

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述


多个独立索引失效的情况

  • 使用or的时候,有一侧的索引失效,整个查询的索引就失效
  • 范围之后,全失效

SELECT SQL_NO_CACHE …不使用缓存查询,为了测试sql语句的效率,有时候要不用缓存来查询。

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE dep_id <=1 OR age >30;

这里age大于30的数据多余百分之50,age的索引会失效,整个查询索引也会因此失效

我们可以使用union all或者 union来代替,union去除重复结果,union all不去除

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE dep_id <=1 
UNION 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age >30

执行两条sql查询语句,dep_Id的索引有效,age的索引失效,但是不会让整体都失效,部分有效


  • 全值匹配
#给emp的age,dep_id,name字段加上联合索引(3个字段作为一个索引)
CREATE INDEX ide_age_depid_name ON emp(age,deptid,`name`);

下面三个查询都使用到了所有,分别使用了一个,两个和三个索引进行查询

#给emp的age,dep_id,name字段加上联合索引(3个字段作为一个索引)
CREATE INDEX ide_age_depid_name ON emp(age,deptid,`name`);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age =30

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND deptid=4

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND deptid=4 AND emp.name="abc"

结论:

查询的字段按照顺序再索引中都可以匹配到,查询时联合索引的字段都使用了

如果在查询中需要匹配多个字段的条件,可以把这几个字段做个联合索引,效率要比在每个字段上加索引高

注意:

在所有索引字段都使用的前提下,使用联合索引时,SQL中查询字段的顺序,跟使用索引中字段的顺序,没有关系,优化器会在不影响SQL执行结果的前提下,给你自动地优化


EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND deptid=4 AND emp.name="abc"
或者
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE  deptid=4 AND age =30 AND emp.name="abc"

  • 最左前缀原则和中间不可断

使用联合索引的字段在做查询时,需要遵守最左前缀原则与中间不可断

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

文章

https://blog.csdn.net/shassd/article/details/109698150


两表优化方法

由左连接的特性决定,LEFT JOIN条件用于确定如何从右表,搜索行,左边一定都有,所以右边是我们的关键点,需要建立索引

RIGHT JOIN的索引建立在左表上,因为右边一定都有


JOIN语句的优化

  • 减少Join语句中的NestedLoop的循环总次数,“永远用小结果集驱动大的结果集”,例如左连接,左边的表的所有记录一定都会被查询出来,那么适合找小结果集充当
  • 优先优化NestedLoop的内层循环
  • 保证Jion语句中被驱动表上Join条件字段已经被索引
  • 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,可以将JoinBuffer设置大一点

总结

索引作用:查找和排序

赞(0) 打赏
未经允许不得转载:IDEA激活码 » mysql--索引知识点整理

相关推荐

  • 暂无文章

一个分享Java & Python知识的社区