[MySQL索引]1.MySQL索引的基本使用

索引作用

作用：当表中的数据达到几十万甚至几百万的时候，SQL查询所花费的时间很长，导致业务超时出错，此时需要索引来加速SQL查询

索引也是需要存储成索引文件的，因此对索引的操作涉及磁盘I/O，如果索引创建过多，使用不当，也会造成SQL查询时进行大量无用的磁盘I/O操作，降低SQL的查询效率，因此掌握良好的索引创建原则是非常重要的。

索引分类

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

• 物理：

  • 聚集索引
  • 非聚集索引

• 逻辑：

  • 普通索引：没有任何限制，可以给任何类型的字段创建普通索引（创建新表&已创建的表，数量是不限的,但是不建议创建过多索引，一张表的一次SQL查询只能用一个索引）

  • 唯一性索引：自动创建，使用UNIQUE修饰的字段，值不能重复，主键索引就隶属于唯一性索引

  • 主键索引：使用PRIMARY KEY修饰的字段会自动创建索引

    • 如果没有加主键，MyISAM不会自动为表添加一列主键，因为MyISAM中数据和索引是分开存放的，有没有索引数据都可以正常存储；
    • 如果没有加主键，InnoDB会默认创建一个整型字段作为主键，因为InnoDB中数据和索 引是存储在一个文件中(xxx.ibd)的，必须创建索引树，然后在索引树下存数据，没有索引树就无法存储数据

  • 单列索引：在一个字段上创建索引

  • 多列索引：在表的多个字段上创建索引（uid+cid,多列索引必须使用到第一个列，才能用到多列索引，否则索引用不上，比如利用uid作过滤条件可以用到多列索引，只用到cid作过滤条件则用不到多列索引）

  • 全文索引（MyISAM支持）：使用FULLTEXT参数可以设置全文索引，只支持CHAR、VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型，可以提高查询速度

    文本的搜索很少直接在数据库中搜索，所以实际上线上项目需要支持专门的搜索功能，给后台服务器添加专门的搜索引擎支持快速高效的搜索, 比如elasticsearch 简称es

    C++开源搜索引擎---->workflow

---

索引的创建和删除

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

CREATE TABLE index1
(
    name VARCHAR(20),
    sex ENUM(‘m’,'w'),
    INDEX(id, name)	// 对指定字段上的数据进行排序,先按照id，如果相同,则按照name
);

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

CREATE [UNIQUE] 索引名 ON 表名 (属性名(length) [ASC|DESC])
// 在字符串类型的字段上创建建索引时，一般会通过length指定利用一定长度的字符串来建立索引，往往不需要使用完整的字符串，这也是一种优化策略

CREATE index name_age_idx on student(name,age); // 创建多列索引

CREATE index nameindex on student(name);  // 在student表上的name字段添加索引

可以看到我们成功在student数据库表上添加了索引nameindex

• 删除索引

DROP INDEX 索引名 ON 表名

• 查看表的索引字段信息（包括使用的索引类型B+树索引，哈希索引）

show indexes from 表名;

---

注意：我们在数据库表的字段上添加索引，索引并不一定能够用上，MySQL Server会进行一个优化，如果通过索引找到的数据量比较接近直接扫描表得到的数据量，那就不使用索引，因为读取索引文件需要进行磁盘I/O，然后还要扫描索引树，如果数据取不完，还是要到数据库表上进行搜索

还有一点要注意：

作为过滤条件的字段，如果在过滤条件中涉及到了类型强转，那么就不会使用在该字段上添加的索引

CREATE INDEX pwdindex on t_user(password); // 先在t_user表的password字段上添加索引

select * from t_user where password=1000000; // 然后使用该索引字段作为过滤条件进行查询

我们会发现这个查询的时间好像有一点久…，使用explain查看以下语句的执行过程：

我们发现虽然我们在password字段上添加了索引，但是查询的时候并没有使用，这是为什么呢?（原因在于我们查询的时候，过滤条件中给字符串类型的password传的是整型的1000000，所以涉及到数据类型的强制转换，而如果在过滤条件中某字段涉及到了类型强转，那么就不会使用在该字段上添加的索引）

select * from t_user where password='1000000';// 我们避免类型强转，再执行一次查询

---

关于索引优化：

• 经常作为where条件过滤的字段考虑添加索引

• 为字符串列创建索引字段时，尽量规定索引的长度，而不能让索引值的长度key_len过长

• 索引字段涉及类型强转、mysql函数调用、表达式计算等,索引就用不上了

---

索引的执行过程

使用explain查看SQL语句的执行计划，也可以帮助我们去分析索引的执行

explain结果字段分析