Mysql高手养成——第一章：索引知识，浅尝性能分析

📣 📣 📣 📢📢📢
我是小冷 侧重后端的全栈工程师，有关技术问题需要讨论交流的直接私信即可
⏩当前专栏：mysql高手养成系列- 第一章 索引与浅尝性能分析
✏️高质量技术专栏专栏链接: 微服务，数据结构，netty，单点登录，SSM ，SpringCloudAlibaba

Mysql高手养成——第一章：索引知识，浅尝性能分析

前言
和很多高级开发前辈交流的时候，多数人认为数据库是新人工程师经验获取最多的技术，他们认为数据库经验是能决定一个程序员的下限的。
所以相对扎实的数据库基础加上生产环境多变的需求 将 历练自己成为一个数据库高玩，在技术提升的道路上 保证下限提升上线是最稳妥的做法。
于是乎 新的专栏开始了 和小冷来一起稳固自己的楼盘 为之后万丈高楼平地起打下殷实基础
参考资料 ：
黑马 mysql：https://www.bilibili.com/video/BV1Kr4y1i7ru
冰河大佬的 《mysql技术大全》
《高性能MySQL（第3版）》

存储引擎

我们在使用mysql的时候 就可以发现 在5.5之后 创建表的默认结构语句会带上engine = innodb 这个是默认的存储引擎。

innodb

是兼顾高性能和高可靠性的通用存储引擎

特点：

• DML操作，遵循ACID模型 ，支持事务，行级锁，主外键约束
• innodb引擎的每张表都有对应的表空间文件 存储表结构 （frm，sdi），数据和索引

MYSQL三种引擎的对比

我们如何去决定需要什么引擎呢？

• innodb 是默认存储引擎如果在并发的情况下对数据一致性有要求，除了查询和插入之外还有很多更新和删除的操作，那么可以优先选择innodb
• MyISAM 如果是以读和插入操作为主，更新和删除操作少，且对事务和并发的要求不是很高，那么可以选择这个引擎
• MEMORY：将所有的数据保存在内存中，访问速度快，通常用于临时表的缓存，对表的大小有限制，且无法保证数据安全性

索引

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

图解

优点：

提高检索效率，降低数据库IO成本，通过索引可以对数据进行排序，降低排序成本减少cpu资源的消耗

缺点：

索引也是占用空间的，索引大大提高了查询效率，但是降低了更新表的速度，对表的增删改效率会变低

存储引擎支持的索引类型

B+ Tree（多路平衡查找树）

为什么innodb要使用b+树来作为索引结构

• 相比于二叉树层级少，效率更高
• 对于b树 无论是叶子结点还是非叶子节点都会存储数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
• 相对hash索引 b+树允许排位匹配且天然支持排序

Hash结构

才用hash算法将键和值转换成心的hash值 映射到对应的位置存在hash表中，如果两个或者多个映射到同一个位置，就会产生hash冲突，可以通过链表来解决

特点

• hash索引只能对等比较，不支持范围查询
• 无法利用索引完成排序操作
• 查询效率高

索引分类

在innodb中 索引的存储形式，可以分为以下两种

• 聚集索引(Clustered Index)：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，必须有且只有一个
• 二级索引(Secondary Index)：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个

聚集索引选取规则:

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

如果我们需要查找一个人 比如 arm

• 先查询二级索引 拿到arm的id值
• 返回去查找聚集索引下的表数据
• 回显，这个过程我们成为回表查询

索引语法

创建索引

create index index_name on table_name(index_col_name)
1

查看索引

show index from table_name
1

删除索引

drop index index_name on table_name
1

SQL性能分析

查看sql频次

show global status like 'com%'
1

可以查看sql的执行次数，来根据结果分析数据库进行优化

慢查询日志

在sql中 慢查询日志是默认不开启的 我们需要自己设置，如果需要可以去修改mysql的配置文件，当有sql执行耗时超过我们设置的时间，我们就会在慢查询日志定位到这些sql

profile

show profiles 可以在sql优化的时候让我们了解时间的消耗都在那里。通过 have_profiling参数，可以看到mysql当前支不支持profile操作

select @@have_profiling
1

默认的profiling 是关闭的 我们需要通过设置来开启

set profiling = 1
1

查看一系列业务sql操作的耗时情况

查看耗时基本情况
show PROFILE

查看指定queryid的sql语句各个阶段的耗时情况
show  profile for query query_id

查看指定query_id的sql cpu 使用情况
show profile cpu for query query_id
1
2
3
4
5
6
7
8

explain sql执行计划分析

id 查询序列号，用来表示多表查询的时候操作表的顺序 （id相同执行顺序从上到下 id 不同值越大越先执行）

如果有用到子查询 就会出现 id 不同的情况 优先执行子查询的sql

最左前缀原则

他主要对于联合索引有约束，最左前缀是查询从索引的最左列开始，并且不跳过索引中的列，否则索引会失效

我们假设有数据表设置了一个三个字段的联合索引：

• profession
• age
• status

从下图执行的两个sql我们可以发现 如果去掉后面的条件，依然会走索引查询，但是不可以跳过中间字段

我们可以这么查 1 2 3 
也可以 1 2 
但是不可以 1 3 
也不可以 2 3
最左前缀法则 必须包含最左边的列 否则不会走索引 ，也不可以跳过列 否则索引会失效
1
2
3
4
5

索引失效情况

1. 不要再索引列上进行函数运算 否则会导致索引失效
2. 字符串类型没有加引号 索引也会失效
3. 用模糊查询的时候 如果是尾部模糊那么索引不会失效 但是头部模糊会失效（如果是数字类型 模糊就会失效）
4. 用or 分开的条件，一侧有索引一侧没有索引会导致索引失效，如两侧都用有索引 那么会生效
5. 数据分布的情况 如果mysql评估索引比全表慢，那么就不会使用索引