本文详细介绍了MySQL索引的原理、类型及其优缺点，重点讨论了B+Tree索引和Hash索引。内容涵盖InnoDB存储引擎为何选择B+Tree，索引分类，以及索引在SQL查询中的应用和优化。此外，还探讨了插入数据、主键设计原则、SQL性能分析（如慢查询日志、EXPLAIN执行计划）以及各种SQL语句的优化策略，旨在提升数据库性能。
文章目录
索引概述
介绍
索引（index）是帮助MySQL 的 （ ）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
优缺点
索引结构
二叉树
B-Tree（多路平衡查找树）
以一颗最大度数（max-degree）为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针)：
树的度数指的是一个节点的子节点个数。
插入 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 数据为例。
B+Tree
以一颗最大度数（max-degree）为4（4阶）的b+tree为例：
插入 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 数据为例。
相对于B-Tree区别:
- 所有的数据都会出现在叶子节点
- 叶子节点形成一个单向链表
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
Hash索引特点
- Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
存储引擎支持
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
为什么InnoDB存储引擎选择使用B+tree索引结构?
- 相对于二叉树，层级更少，搜索效率高；
- 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一
页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的
高度，导致性能降低； - 相对Hash索引，B+tree支持范围匹配及排序操作；
索引分类
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
聚集索引选取规则:
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
InnoDB主键索引的B+tree高度为多高呢?
假设:
一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6
个字节的空间，主键即使为bigint，占用字节数为8。
高度为2：
n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170
1171* 16 = 18736
高度为3：
1171 * 1171 * 16 = 21939856
索引语法
创建索引
查看索引
删除索引
SQL性能分析
SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 19 |
| Com_delete | 0 |
| Com_insert | 16 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 102 |
| Com_signal | 0 |
| Com_update | 16 |
| Com_xa_end | 0 |
+---------------+-------+
慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 开启Mysql慢日志查询开关
show_query_log=1
# 设置慢日志的时间为2秒， 语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。
profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
select @@have_profiling;
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：
set profiling=1;
执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：
# 查看每一条SQL的耗时基本情况
show profiles;
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的sql语句CPU的使用情况
show profile cpu for query query_id;
explain执行计划
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：
# 直接在select语句之前加上关键字explain / desc
explain select * from stu;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | stu | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
EXPLAIN 执行计划各字段含义：
- Id
select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。 - select_type
表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等 - type
表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。 - possible_key
显示可能应用在这张表上的索引，一个或多个。 - Key
实际使用的索引，如果为NULL，则没有使用索引。 - Key_len
表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。 - rows
MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。 - filtered
表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。
索引使用
最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。
范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
索引列运算
不要在索引列上进行运算操作， 索引将失效。
字符串不加引号
字符串类型字段使用时，不加引号， 索引将失效。
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
or连接的条件
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少 select *。
using index condition ：查找使用了索引，但是需要回表查询数据
using where; using index ：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
前缀索引
当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
- 语法
- 前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
- 前缀索引查询流程
单列索引与联合索引
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
SQL优化
插入数据
insert优化
- 手动提交事务
- 主键顺序插入
- 大批量插入数据
主键优化
- 数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。
- 页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。
- 页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
主键设计原则
满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
- 业务操作时，避免对主键的修改。
order by优化
using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。Using index通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高
总结：
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC)
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小，sort_buffer_size(默认256)。
groupby优化
- 在分组操作时，可以通过索引来提高效率。
- 分组操作时，索引的使用也是满足最左前缀法则的
limit优化
一个常见又非常头疼的问题就是limit2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010 的记录，其他记录丢弃，查询排序的代价非常大。
优化思路:一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
count优化
- MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
- InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
count的几种用法:
-
count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。
-
用法：count（*）、count（主键）、count（字段）、count（1）
-
count（主键）
- InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)
-
count（字段）
- 没有notnull约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
- 有notnull约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
-
count(1)
- InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
-
count（*）
- InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
按照效率排序的话，count(字段)<count(主键id)<count(1)≈count()，所以尽量使用count()。
update优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。
扫一扫
404
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
