Mysql（8）——索引初步及explain

最新推荐文章于 2025-03-26 21:40:17 发布

Dutkig

最新推荐文章于 2025-03-26 21:40:17 发布

阅读量807

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/qq_44824574/article/details/123585955

版权

Mysql 专栏收录该内容

17 篇文章

订阅专栏

Mysql索引

当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。
由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及磁盘I/O操作。如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，因此掌握良好的索引创建原则非常重要！

索引分类

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度！，其本质也是一种数据结构。

索引的优点：提高查询效率
索引的缺点： 索引并非越多越好，他也是需要被存储成为索引文件的，过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O造成CPU负荷太重。

分类：
物理上(聚集索引&非聚集索引)
逻辑上（如下：)

普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引(创建新表&已创建表，数量是不限的，但并不意味着可以一直添加，一张表的一次sql查询只能用一个索引，比如where a=1 and b=‘M’只能用二者其一的索引)
唯一性索引：使用UNIQUE修饰的字段，会自动创建，主键索引就隶属于唯一性索引
主键索引：使用Primary Key修饰的字段会自动创建索引 (MyISAM在没有主键的情况下不会自动添加，InnoDB在没有主键的情况下，会默认添加一个整形列作为主键，因为InnoDB的数据和索引存放在一个文件，必须建立索引树，然后在索引树上存放数据)
单列索引：在一个字段上创建索引
多列索引：在表的多个字段上创建索引 (uid+cid，多列索引必须使用到第一个列，才能用到多列索引，否则索引用不上)
全文索引：使用FULLTEXT参数可以设置全文索引，只支持CHAR，VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度(线上项目支持专门的搜索功能，给后台服务器增加专门的搜索引擎支持快速高校的搜索elasticsearch 简称es C++开源的搜索引擎搜狗的workflow)

索引创建和删除

创建表的时候指定索引字段：

CREATE TABLE index1(id INT,
   name VARCHAR(20),
   sex ENUM('male', 'female'),
   INDEX(id,name));#多列索引

查索引名：show create table\G

在已经创建的表上添加索引：

CREATE [UNIQUE] INDEX 索引名 ON 表名（属性名（length） [ASC | DESC]);

删除索引:

DROP INDEX 索引名 ON 表名;

索引优化建议：

经常作为where条件过滤的字段考虑添加索引
字符串列创建索引时，尽量规定索引的长度，能区分即可，而不能让索引值的长度key_len过长
索引字段如果涉及类型强转、mysql函数调用、表达式计算等，索引就用不上了：（比如你给passwd列设置索引，在where过滤条件中写的是where passwd = 10000;但是其实passwd是varchar类型，这样索引就用不上了，还是会整表搜索，改成where passwd = '10000’后搜索时间明显缩短）

（注意：面试过程中回答索引优化问题时：结合实际，不要教条主义，要说曾经建立了。。。。表，发现了。。。问题，于是做了。。。优化）

问题： 给字段加索引，将该字段作为过滤条件，在搜索时一定会使用索引吗？

其实不是，mysql也会优化，如果使用索引搜索到的数据占据了所有数据的百分之七八十的时候，这时就不会使用索引，因为此时使用索引，首先需要读取索引文件花费磁盘IO，还要扫描索引树，还不如整表扫描。
总而言之，mysql会比较用或不用的数据量的大小，因为毕竟使用索引的操作步骤还是比较复杂的。

使用explain查看sql的执行计划

使用explain查看sql的执行计划，分析索引的执行过程，mysql的user权限表，示例如下：

mysql> explain select Host,User from user where Host='%'\G
*************************** 1. row ***************************
     id: 1
select_type: SIMPLE
    table: user
 partitions: NULL
    type: ref
possible_keys: PRIMARY
    key: PRIMARY
  key_len: 180
    ref: const
    rows: 1
  filtered: 100.00
   Extra: Using index
1 row in set, 1 warning (0.00 sec)

可以看到使用了主键索引，共扫描1行，Using index表示直接从索引树上查询到结果，不需要回表。

mysql> explain select Host,User from user where User='root'\G
*************************** 1. row ***************************
     id: 1
select_type: SIMPLE
    table: user
 partitions: NULL
    type: index
possible_keys: NULL
    key: PRIMARY
  key_len: 276
    ref: NULL
    rows: 4
  filtered: 25.00
   Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where User='root'\G
*************************** 1. row ***************************
     id: 1
     select_type: SIMPLE
    table: user
 partitions: NULL
    type: ALL
possible_keys: NULL
    key: NULL
  key_len: NULL
    ref: NULL
    rows: 4
  filtered: 25.00
   Extra: Using where
1 row in set, 1 warning (0.00 sec)