1. 索引概述
数据库索引（Index）是一种数据结构，其核心概念在于提高数据库的查询效率。具体来说，索引类似于书籍的目录，通过将数据存储在特定的数据结构中，使得数据库能够更快地找到满足查询条件的数据。索引通常是在表的某个列上创建的，这个列被称为索引列。索引列的值会被排序并存储在索引数据结构中，以便快速查找。当执行查询语句时，数据库会首先在索引中查找符合条件的数据行，然后再根据索引中存储的指针（或者地址）找到相应的数据行。
索引的实现通常使用B树和变种的B+树（例如，MySQL常用的索引就是B+树）。索引不仅可以加速数据的检索速度，还可以通过索引列对数据进行排序，降低数据排序的成本，减少CPU的消耗，以及降低数据库IO的成本。
然而，索引并非没有缺点。首先，索引需要占用额外的存储空间。其次，索引的创建和维护（如更新、删除等操作）也会增加数据库的维护开销。因此，在创建索引时需要权衡其带来的性能提升与所需的空间和维护成本。
总的来说，数据库索引是一种优化数据库查询性能的有效手段，通过合理的使用和管理，可以显著提高数据库的查询效率。
2. 索引分类
主键索引：被primary修饰，特点：该索引默认自动创建唯一不能重复
唯一索引：被unique修饰，避免表中列中的值重复，特点：该索引可以有多个
常规索引：快速定位特定的数据，特点：可以有多个
全文索引：被fulltext修饰，全文索引查找文中的关键字，而不是特定的值，特点：可以有多个
聚集索引和二级索引
聚集索引：聚集索引下面悬挂着一行的数据，聚集索引唯一不重复，即主键索引
二级索引：下面悬挂着聚集索引，聚集索引以外的索引都为二级索引
- 故通过二级索引查询得先查询到该索引下悬挂的聚集索引，在通过回表查询查询聚集索引下面悬挂的数据，即二级索引的查询效率没有聚集索引查询效率高
3. 索引语法
- 创建索引
create index 索引名 on 表明(字段名，...);
一个索引可同时关联多个字段
- 查看索引
show index from 表名;
- 删除索引
drop index 索引名 on 表名;
mysql> #创建索引，该索引名为，该索引的字段来自哪个表
mysql> create index idx_user_name on tb_user(name);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> #创建唯一索引
mysql> create unique index idx_user_phone on tb_user(phone);
mysql> #创建联合索引
mysql> create index idx_user_pro_age_sta on tb_user(profession,age,status);mysql> show index;
mysql> #删除索引
mysql> drop index idx_user_ema on tb_user;
4. SQL性能分析
查看执行频次
show global status
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 0 |
| Com_import | 0 |
| Com_insert | 108 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 18 |
| Com_signal | 0 |
| Com_update | 0 |
| Com_xa_end | 0 |
+---------------+-------+
慢查询日志
慢查询日志默认记录查询时长超过10秒的记录，慢查询日志默认是关闭的
mysql> #慢查询日志
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.06 sec)
profile
通过指令select @@profiling 可查询当前profile状态，该状态默认关闭
可通过set profiling = 1；将该查询状态打开
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
通过以下SQL指令可查看每条SQL业务查询所耗费时间
#查看每一条SQL耗时的基本情况
show profiles；
#查看指定SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定SQL语句cpu使用情况
show profile cpu for query query_id;
explain执行计划
只需要查询语句前面加上关键字explain/desc即可
如：explain select 字段名 from 表名 where 查询条件；
mysql> explain select * from tb_user where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
各个字段含义
id：表示查询顺序，id值越大越先执行，id值相同则由上往下逐一执行
select_type：以什么方式查询该表，如simple（不使用子查询、连接表查询、primary（主查询）、union（union中的第二个或者后面的查询语句），subquery（select或where后包含子查询）
type：表示连接类型，性能由好到差：null、system、conset、eq_ref、ref、range、index、all
possiblekey：可能出现的查询索引
key：实际用到的索引
key_len：索引所用字节数，该长度为可能用到的最大长度，并非实际长度
rows：认为必须要执行的查询行数，innodb引擎中是个预
filtered：返回结果的行数占读取总行数的占比
5. 索引使用规则
5.1 最左前缀法则
如果索引为联合索引必须遵循最左前缀法则，即必须从索引最左边字段开始查询，若跳过最左边的索引则该索引直接失效，会进行全表扫描查询，若走了最左边而后面跳过了部门索引则索引可能部分失效
遵循最左前缀法则，则走联合索引
mysql> explain select * from tb_user where profession = '软件工程' and age = age and status = 0;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 47 | const | 4 | 4.76 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.01 sec)
未查询最左端字段，索引失效，全表扫描
mysql> explain select * from tb_user where age = age and status = 0;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 4.76 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
5.2 索引失效情况
1. 查询条件带有运算
mysql> explain select * from tb_user where substring(phone, 10, 2) = 15;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.03 sec)
2. 查询条件的类型和字段类型不符
mysql> explain select * from tb_user where phone = 17799990020;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | idx_user_phone | NULL | NULL | NULL | 21 | 10.00 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
3. 模糊匹配
%前置将失效
mysql> explain select * from tb_user where profession like '%工程';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
%后置可以走索引
mysql> explain select * from tb_user where profession like '软件%';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 47 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
4. or连接
通过指令查询当前表中并没有age索引，此时使用or连接两个条件进行查询，name索引也失效，若要索引生效该字段条件必须都同时拥有索引
mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 21 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 21 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 21 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 1 | profession | A | 14 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 2 | age | A | 19 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 3 | status | A | 21 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.01 sec)
mysql> explain select * from tb_user where name = '妲己' or age = 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | idx_user_name | NULL | NULL | NULL | 21 | 14.29 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5. 数据分布影响（范围查询）
当使用范围查询时，MySQL会进行评估，若走索引更慢则直接进行全表扫描
mysql> explain select * from tb_user where phone >= '17799990010';
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | range | idx_user_phone | idx_user_phone | 46 | NULL | 11 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where phone >= '17799990005';
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | idx_user_phone | NULL | NULL | NULL | 21 | 76.19 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5.3 SQL提示
use index
explain select * from 表名 use index(建议使用的索引名) where 条件;
该语句对SQL进行索引建议，建议SQL使用哪个索引查询数据，MySQL权衡后再决定使用哪个索引查询数据，即该查询方式非强制
mysql> explain select * from tb_user use index(primary) where profession = '软件工程';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 7.14 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
ignore index
explain select * from 表名 ignore index(不建议使用的索引名) where 条件;
该语句不建议SQL使用某个索引
mysql> explain select * from tb_user ignore index(idx_user_pro_age_sta) where profession = '软件工程';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 7.14 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
froce index
explain select * from 表名 force index(强制使用的索引名） where 条件;
该语句强制SQL通过某个索引查询数据
mysql> explain select * from tb_user force index(primary) where profession = '软件工程';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 7.14 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5.4 索引覆盖
索引覆盖
如：查询name、id、gender这三个字段的信息，此时已经将name、gender两个字段建立联合索引（二级索引），而二级索引底下的叶子节点悬挂着聚集索引的信息（即此时悬挂着id 字段的信息）通过一次联合索引返回了name、gender的信息，而底下的索引即要查询的id，此时也直接返回未进行回表查询即该情况称为索引覆盖
mysql> explain select * from tb_user ignore index(idx_user_pro_age_sta) where profession = '软件工程';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 7.14 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
using where / using index：代表直接通过二级索引查询到了对应数据，无需进行回表查询
using index condition：此时需要进行回表查询
null：无需回表查询
5.5 前缀索引
当字段类型为字符串类型（如：长文本，文章，内容有几万字的）此时直接通过该字段类型建立索引会浪费大量的磁盘io，影响查询效率，可以通过前缀索引解决该问题，即用该字段指定的前多少个空间作为索引。
创建前缀索引
create index 索引名 from 表名(关联的索引字段名(要截取该字段的多长的部分作为索引));
mysql> #计算选择性，选择性越高说明查询效率越好
mysql> select count(distinct substring(email, 1, 10)) / count(*) from tb_user;
+----------------------------------------------------+
| count(distinct substring(email, 1, 10)) / count(*) |
+----------------------------------------------------+
| 1.0000 |
+----------------------------------------------------+
1 row in set (0.06 sec)
mysql> select count(distinct substring(email, 1, 5)) / count(*) from tb_user;
+---------------------------------------------------+
| count(distinct substring(email, 1, 5)) / count(*) |
+---------------------------------------------------+
| 0.9524 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> #创建前缀索引
mysql> create index idx_tb_email from tb_user(email(5));
最终效果
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 1 | idx_tb_email | 1 | email | A | 20 | 5 | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
8 rows in set (0.01 sec)
执行流程
先通过前缀索引查询到符合条件的数据，然后进行回表查询将完整的查询数据和条件对比，若符合即完成查询
