Mysql-索引(一)

最新推荐文章于 2023-07-28 17:01:07 发布

cs_willis

最新推荐文章于 2023-07-28 17:01:07 发布

阅读量300

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/cs_willis/article/details/127127878

版权

一、索引的优点

·很大程度上减少服务器扫描的数据量
·很大程度上避免服务器排序和临时表
·将随机IO变成顺序IO

二、索引的用处

使用索引列可以快速查找Where条件的行数据
假如存在多个索引，mysql优化器会使用最少行的索引。
假如存在多个索引，则优化器可以使用索引的任何最左前缀来查找行数据。
当表有连接的时候，从其他表检索行数据。
索引树是有排序机制的，所有能很快找到min或max值。
如果排序或分组时在可以用索引的最左前缀上完成的，则对表进行排序和分组。
在某些情况下，可以优化查询以检索值而无需查询数据行。

三、索引的分类

主键索引：PRIMARY KEY
由一个或多个列组成，用于唯一性标识数据表中的某一条记录。唯一且非空。一般建议使用数据表的自增唯一主键来作为主键索引使用。
方式1：创建表的时候指定主键
方式2：ALTER TABLE table_nameADD PRIMARY KEY (column )
唯一索引：UNIQUE
值唯一，不可重复，允许有空值。当数据表没有创建索引时，mysql会为该表的唯一键自动创建索引。
方式1：ALTER TABLE table_name ADD UNIQUE [indexName] (column)
全文索引：FULLTEXT
一般是给 varchar、char、text 类型创建的索引，一般用的极少。可用于 MyISAM 表，mysql5.6 之后也可用于 innodb 表，用于在一篇文章中，检索文本信息的，针对较大的数据，生成全文索引很耗时和空间。
方式1：ALTER TABLE table_nameADD FULLTEXT (column )
普通索引：INDEX
普通列的索引，没有任何限制。
方式1：ALTER TABLE table_name ADD INDEX index_name ( column )
联合索引
多个常用的字段组合创建索引。为了提高mysql效率可建立组合索引，遵循”最左前缀“原则。
方式1：ALTER TABLE table_name ADD INDEX (col1,col2,col3);
注意：联合索引第一个字段用范围查询不会走索引：MySQL内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如全表扫描。

四、Explain 工具介绍

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 语句，分析你的查询语句或结构的性能瓶颈。
在 SELECT 语句之前加上 EXPLAIN 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不会执行这条 SQL。注意：如果FROM 包含子查询，仍会执行该子查询将结果放入临时表中。

explain 两个变种
1）explain extended：会在 explain 基础上额外提供一些查询优化的信息。紧随其后通过show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，row * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中 id 值比当前表 id 值小的表）
2）explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。
explain 中的列
1）id 列的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的顺序是按 select 出现的顺序增长的，id 列越大执行优先级越高，id 相同则从上往下执行，id 为 null 最后执行。
2）select_type 列表示队形行是简单还是复杂的查询
simple：简单查询不包含子查询和 union
primary：复杂查询中最外层的 select
subquery：包含在 select 中的子查询（不在 from 子句里）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表
union：在 union 中的第二个和随后的 select
3）table ：表示正在访问哪个表
当 from 子句中有子查询时，table 列是格式，表示当前查询依赖 id = N 的查询，于是先执行 id=N 的查询。
当有 union 时，union result 的 table 列的值为 <union 1, 2>，1和2表示，参与 union 的 select 行 id
4）type 列：这一列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
5）possible_keys：这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 null 的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询。
如果该列是null，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果。
6）key：这一列显示 MySQL 实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 null。如果想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。
7）key_len：这一列显示了 MySQL 在索引里使用的字节数，通过这个值可以算出具体使用了索引的哪些列。
8）ref：这一列显示了key列记录的索引中，表查找值所用到的列或常量
9）rows：这一列是MySQL估计要读取并检测的行数，注意这个不是结果集里的行数
10）extra：这一行展示的额外信息。