【MySQL-索引：分类，语法，性能分析，使用，设计原则】

时间：2023-02-19 12:58:39

零、本文纲要

  • 一、索引概述
  • 二、索引分类
  • 三、索引语法
  • 四、SQL性能分析
  • 五、索引使用
  • 六、索引设计原则

tips：Ctrl + F快速定位到所需内容阅读吧。

一、索引概述

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。
1、索引特点

  • 优势
    ① 提高检索效率，降低IO成本；
    ② 降低排序成本，减少CPU消耗。
  • 劣势
    ① 索引占用空间，增加空间消耗；
    ② 增加SELECT语句效率，但是INSERT\DELETE\UPDATE效率降低。

2、索引结构 数据结构​

  • ① 二叉树 二叉树在理想情况下确实是不错的数据结构，但是插入顺序影响下，可能​​​转化为链表​​。

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

② 红黑树

红黑树相比于二叉搜索树虽然解决了转化为链表的问题，但是随着数据量的提升，​​二叉树层数不断增加​​也会降低搜索效率。

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

③ B-Tree

B-Tree，B树是一种​​多叉路衡查找树​​，相对于二叉树，B树每个节点可以有多个分支，即多叉。

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

④ B+Tree（InnoDB默认）

​​B+Tree​​ 与 ​​B-Tree​​相比：

Ⅰ 所有的数据都会出现在叶子节点；

Ⅱ 叶子节点形成一个单向链表；

Ⅲ 非叶子节点仅仅起到索引数据作用。

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

MySQL优化的B+Tree

在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针（​​环形链表​​），就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

⑤ Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了​​​hash冲突​​​（也称为hash碰撞），可以通过​​链表​​来解决。

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

  • 特点
    ① Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）；
    ② 无法利用索引完成排序操作；
    ③ 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引。

二、索引分类

  • 1、按类型分类
    ① 主键索引
    ​​PRIMARY​​：针对于表中主键创建的索引，默认自动创建，只能有一个；
    ② 唯一索引
    ​​UNIQUE​​：避免同一个表中某数据列中的值重复，同一个表可以有多个；
    ③ 常规索引
    快速定位特定数据，同一个表可以有多个；
    ④ 全文索引
    ​​FULLTEXT​​：全文索引查找的是文本中的关键词，而不是比较索引中的值，同一个表可以有多个。
  • 2、按存储形式分类
    ① 聚集索引
    ​​Clustered Index​​：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（必须有,而且只有一个）；
    ② 二级索引
    ​​Secondary Index​​：（又称：非聚集索引、辅助索引），将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（可以存在多个）。

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

  • 聚集索引选取规则
    ① 如果存在主键，​​主键索引​​就是聚集索引;
    ② 如果不存在主键，将使用​​第一个唯一（UNIQUE）索引​​作为聚集索引;
    ③ 如果表没有主键，或没有合适的唯一索引，则InnoDB会​​自动生成一个rowid​​作为隐藏的聚集索引。

三、索引语法

  • 1、创建索引
    MySQL官方关于创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;
  • 2、查看索引


SHOW INDEX FROM table_name ;
  • 3、删除索引


DROP INDEX index_name ON table_name ;

简单练习

  • ① 创建测试表


create table tb_user
(
id         int auto_increment comment '主键'
primary key,
name       varchar(50)      not null comment '用户名',
phone      varchar(11)      not null comment '手机号',
email      varchar(100)     null comment '邮箱',
profession varchar(11)      null comment '专业',
age        tinyint unsigned null comment '年龄',
gender     char             null comment '性别 , 1: 男, 2: 女',
status     char             null comment '状态',
createtime datetime         null comment '创建时间',
)
comment '系统用户表';
  • ② 插入数据


INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31,'2', '0', '2001-01-30 00:00:00');
  • ③ 操作索引
    索引命名：idx​​(index)​​_user​​(table_name)​​_name​​(column_name)​​。

Ⅰ 创建常规索引
此处​​name​​因为实际场景中​​不是唯一​​的，可以重复，所以我们创建常规索引；


create index idx_user_name on tb_user(name);

Ⅱ 查看索引


show index from tb_user;

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

查看索引.png

Ⅲ 创建唯一索引
此处​​phone​​实际场景中是唯一的，所以我们创建唯一索引；


create unique index idx_user_phone on tb_user(phone);

Ⅳ 创建联合索引


create index idx_user_pro_age_sta on tb_user(profession,age,status);

四、SQL性能分析

  • 1、SQL执行频率

使用SQL来显示状态：​​SHOW STATUS Statement​​。
​​SHOW [GLOBAL | SESSION] STATUS​​提供服务器状态信息，该语句有一个变量​​Com_xxx​​是专门用来指示每个 xxx 语句的执行次数。


show global status like 'com_______';
-- 此处com后面有7个“_”下划线
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 25    |
| Com_import    | 0     |
| Com_insert    | 38    |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 355   |
| Com_signal    | 0     |
| Com_update    | 26    |
| Com_xa_end    | 0     |
+---------------+-------+
  • 2、慢查询日志


mysql> show variables like "slow_query_log";
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
-- 默认是关闭的

我们可以通过修改对应配置文件开启，位置​​/etc/my.cnf​​。


vim /etc/my.cnf
# 插入下方数据
# 1表示开启，0表示关闭
slow_query_log=1
# 慢查询的设定时间10s，可以根据实际需求调整
long_query_time=10

查看慢SQL日志，位置​​/var/lib/mysql/localhost-slow.log​​。


cat /var/lib/mysql/localhost-slow.log
  • 3、profile

官方文档：​​SHOW PROFILE Statement​​SELECT @@profiling;查看profiling属性，默认关闭；
SET profiling = 1;开启profiling；
mysql> SHOW PROFILES;查看PROFILES详情，就是几个语句执行情况；
SHOW PROFILE FOR QUERY 1;查看具体某一个语句的执行情况；
SHOW PROFILE CPU FOR QUERY 2;带CPU使用情况的查询。


# 查看profiling属性，默认关闭
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
mysql> SET profiling = 1;
mysql> DROP TABLE IF EXISTS t1;
mysql> CREATE TABLE T1 (id INT);
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating table       | 0.000056 | 0.000028 |   0.000028 |
| After create         | 0.011363 | 0.000217 |   0.001571 |
| query end            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+
  • 4、Explain执行计划


五、索引使用

  • 1、最左前缀法则

最左前缀法则指的是查询从​​联合索引​​的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效​​(后面的字段索引失效)​​。


mysql> explain select * from tb_user where profession = '软件工程';
mysql> explain select * from tb_user where profession = '软件工程' and status = 0;

【MySQL-索引：分类，语法，性能分析，使用，设计原则】


  • 2、范围查询

以下场景由于使用了​​(>,<)​​范围查询，导致该索引后面的索引失效了。实际使用中像​​age>30​​等价于​​age>=31​​，所以我们可以替换一下。


mysql> explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
mysql> explain select * from tb_user where profession = '软件工程' and age >= 31 and status = '0';

【MySQL-索引：分类，语法，性能分析，使用，设计原则】


  • 3、索引失效场景

以下案例关注| ​​possible_keys​​ | ​​key​​ |两列，​​possible_keys​​列为可能使用到的索引，​​key​​列是实际使用到的索引。

  • ① 索引列运算

索引列上有计算或者使用函数，导致索引失效。


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 |   24 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql>  explain select * from tb_user where id + 5 = 25;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |   24 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
  • ② 字符串不加引号

字符串不加引号，可以理解为MySQL底层做了数据类型转换，导致索引失效。


mysql> explain select * from tb_user where phone = '17799990015';
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | const | idx_user_phone | idx_user_phone | 46      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+

mysql> explain select * from tb_user where phone = 17799990015;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| 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 |   24 |    10.00 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
  • ③ 模糊查询

在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字
前面加了%，索引将会失效。


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 |   24 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

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 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
  • ④ or条件连接

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


mysql> explain select * from tb_user where id = 10 or age = 23;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |   24 |    13.75 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
  • ⑤ 数据分布影响

MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。


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 |   24 |    79.17 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+

mysql> explain select * from tb_user where phone >= '17799990015';
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| 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 |    9 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+

注意：​​is null​​、​​is not null​​​是否走索引，也是根据实际效率来决定的。

  • 4、SQL提示


mysql> explain select * from tb_user where profession = '软件工程';
mysql> create index idx_user_pro on tb_user(profession);
mysql> explain select * from tb_user where profession = '软件工程';

【MySQL-索引：分类，语法，性能分析，使用，设计原则】


SQL提示，是优化数据库的一个重要手段，就是在SQL语句中加入一些​​人为的提示​​来达到优化操作的目的。

  • ① use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）；
  • ② ignore index ： 忽略指定的索引；
  • ③ force index ： 强制使用索引。


mysql> explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
mysql> explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
mysql> explain select * from tb_user force index(idx_user_pro_age_sta) where profession = '软件工程';

【MySQL-索引：分类，语法，性能分析，使用，设计原则】


  • 5、覆盖索引

覆盖索引是指​​查询使用了索引​​，并且需要返回的列，在该索引中已经​​全部能够找到​​。


mysql> explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
mysql> explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0';
mysql> explain select id,profession,age, status, name from tb_user where profession = '软 件工程' and age = 31 and status = '0';
mysql> explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

① Extra
​​| Using where; Using index |​​：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据；
​​| Using index condition |​​：查找使用了索引，但是需要回表查询数据。

  • 6、前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大​​节约索引空间​​，从而​​提高索引效率​​。
语法：​​create index idx_xxxx on table_name(column(n)) ;​​ ① 前缀长度
全列选择性：


SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

某一长度前缀的选择性：


SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

使用如下：


select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

【MySQL-索引：分类，语法，性能分析，使用，设计原则】


② 前缀索引的查询流程

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

前缀索引的查询流程

Ⅰ 根据sql语句的搜索项​​截取前缀​​；
Ⅱ 拿着前缀在​​辅助索引内查找​​匹配项；
Ⅲ 从辅助索引​​拿到主键id​​；
Ⅳ 根据主键id在​​聚集索引​​内找到对应​​row行数据​​；
Ⅴ 辅助索引链表位置处​​继续向后确认​​，有则重复Ⅱ-Ⅴ；
Ⅵ 返回结果集。

  • 7、单列索引与联合索引

在业务场景中，如果存在​​多个查询条件​​，考虑针对于查询字段建立索引时，建议​​建立联合索引​​，而非单列索引。
注意：如果MySQL未按预期执行，参考SQL提示，做适当调整。

【MySQL-索引：分类，语法，性能分析，使用，设计原则】

单列索引与联合索引


六、索引设计原则

  • 1、针对于数据量较大，且查询比较频繁的表建立索引；
  • 2、针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引；
  • 3、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高；
  • 4、如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引；
  • 5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率；
  • 6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率；
  • 7、如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

七、结尾

以上即为MySQL索引的部分内容，感谢阅读。




标签：

相关文章