MySQL 索引创建 大数据查询 性能测试 SQL优化 慢查询

介绍

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些
数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

无索引的情况

在 MySQL 中无索引 的情况下，查询会涉及 全表扫描（Full Table Scan），也就是说，MySQL 会扫描表中的每一行，直到找到满足条件的记录。这种情况会导致查询效率非常低，特别是在表数据量较大的时候。

测试

1000万条数据查询，耗时6秒，未添加索引。

添加索引后测试

索引结构

索引是在存储引擎层实现的，不同的存储引警有不同的结构，主要包含以下几种。

• B+Tree索引 最常见的索引类型，大部分引擎都支持 B+树索引
• Hash索引 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询
• R-tree(空间索引) 空间索引是MVISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text(全文索引) 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

索引分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种

聚集索引选取规则

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

回表查询

二级索引(Secondary Index)回表查询是指在 索引查询 后，由于索引本身不包含所有查询需要的字段数据，需要通过索引找到的 主键值 再去查询 数据表 中获取完整数据的过程。

创建索引

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (column_name, ...);

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

索引名称规范

idx-表名-字段名
idx表示index简写

idx_user_name

前缀索引（长字符串时使用）

它允许对列的部分内容（即列的前几个字符）创建索引，而不是对整个列的内容进行索引。这种索引特别适用于存储长度较长的数据字段，比如 VARCHAR 类型的列，在一些情况下，索引整个列可能会浪费存储空间或者降低性能。

#100为提取的前100数字
create index idx_xxx_xxx on table name(column(100));

指定索引查询

explain select * from tb user use index(idx_user_profession) where profession='软件工程',

联合索引注意

如果使用了联合使用那么查询时候字段最左边的列必须存在，否则后面的索引都会失效

SELECT  id,phone,name,sex,password,play_num  FROM user 
WHERE name = '魏子异'

以上语句将会让索引失效

索引失效情况

联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效 >= <=则不会

 EXPLAIN SELECT  id,phone,name,sex,password,play_num  FROM user 
WHERE play_num >= 100

不要在索引列上进行运算操作，索引将失效。

EXPLAIN SELECT  id,phone,name,sex,password,play_num  FROM user 
WHERE  SUBSTRING(phone,10,2) ='15234204153'

字符串类型字段使用时，不加引号，索引将失效。

EXPLAIN SELECT  id,phone,name,sex,password,play_num  FROM user 
WHERE  phone=15234204153

模糊查询如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

#失效
SELECT  id,phone,name,sex,password,play_num  FROM user 
WHERE (phone LIKE '%15234204153%') 

#不失效
SELECT  id,phone,name,sex,password,play_num  FROM user 
WHERE (phone LIKE '15234204153%') 

MySQL 无法快速定位到匹配的起始位置。对于以 % 开头的查询，MySQL 只能进行全表扫描

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

#sex 无索引  则全部索引失效
EXPLAIN SELECT  id,phone,name,sex,password,play_num  FROM user 
WHERE  phone='15234204153' OR sex=10

# 全部条件都有索引 全部生效
EXPLAIN SELECT  id,phone,name,sex,password,play_num  FROM user 
WHERE  phone='15234204153' OR play_num=10