行业知识
⑩② 【MySQL索引】详解MySQL`索引`：结构、分类、性能分析、设计及使用规则。
个人简介：Java领域新星创作者；阿里云技术博主、星级博主、专家博主；正在Java学习的路上摸爬滚打，记录学习的过程~
个人主页：.29.的博客
学习社区：进去逛一逛~
MySQL索引
- ⑩② 【MySQL索引】
- 1. 索引
- 2. 索引的结构
- B+树索引
- Hash索引
- 思考题
- 3. 索引的分类
- 4. 创建、查看、删除索引
- 5. SQL性能分析
- SQL执行频率
- 慢查询日志
- SQL性能分析
- - profile详情
- - explain执行计划
- 6. 索引的使用规则
- 7. 索引设计原则
⑩② 【MySQL索引】
1. 索引
索引：
- 什么是索引(index) ?
- 索引(index)是帮助MySQL高效获取数据的数据结构(有序)：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据就是索引。
- 索引的优缺点？
- 优势：
-
- ⚪提高数据检索效率，降低数据库IO成本；
- ⚪通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗；
- 劣势：
-
- ⚪索引列需要占用空间，比无索引结构占用的空间更大。
- ⚪索引虽大大提高了查询效率，但与此同时却降低了更新表的速度，如对表进行INSERT \ UPDATE \ DELETE 时，效率降低。
2. 索引的结构
索引结构：
- MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构。
- ①B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引。
- ②Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
- ③R-tree(空间索引)：空间索引是MyISAM引擎的一个特殊索引类型，只要用于地理空间数据类型，较少使用。
- ④Full-text(全文索引)：是一种通过建立倒排索引，快速匹配文档的方式。类似于ES(Elasticsearch)。
B+树索引
B+Tree索引：
- 特征：
-
- ①每个节点最多可存放4个元素，五个指针。
- ②叶子节点形成链表，存储了树的所有元素。
- ③指针指向当前元素区间内的元素。
- MySQL索引数据结构对经典的B+Tree进行了优化。在原本B+树的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
Hash索引
Hash索引：
- 哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
- 哈希碰撞问题
- 如果出现两个或多个键值映射到同一个槽位上，也就是出现hash碰撞时，可以通过链表解决问题。
- Hash索引特点
-
- ① Hash索引只能用于对等比较（=、in），不支持范围查询（between、>、
- ②无法利用Hash索引完成排序操作
- ③查询效率高，通常只需要一次检索就可以完成，效率通常要高于B+Tree索引
- MySQL数据库中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
思考题
- 为什么InnoDB存储引擎选择使用B+Tree索引结构？
-
- ⚪相对于二叉树，层级更少，搜索效率更高；
- ⚪对于BTree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，会导致性能下降；
- ⚪相对Hash索引，B+Tree索引支持范围匹配和排序操作；
3. 索引的分类
索引分类：
-
①主键索引 —— PRIMARY
-
②唯一索引 —— UNIQUE
-
③常规索引
-
④全文索引 —— FULLTEXT
-
在InnoDB存储引擎中，根据索引的存储形式，又可以分为两种
-
- ⚪聚集索引(Clustered Index)：B+Tree叶子节点下挂载这一行的数据 。
-
- ①如果存在主键，主键索引就是聚集索引。
- ②如果不存在主键，将使用第一个唯一索引**(UNIQUE)作为聚集索引。**
- ③如果表没有主键，也没有合适的唯一索引，InnoDB自动生成一个rowid作为隐藏的聚集索引。
- ⚪二级索引(Secondary Index)：B+Tree叶子节点下挂载这一行的id 。
4. 创建、查看、删除索引
索引操作：
-
创建索引
-
-
-- 使用UNIQUE关键字，创建唯一索引-- 使用FULLTEXT关键字，创建全文索引-- 不指定上述两者，创建常规索引CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名(字段1,字段2...);
-
查看索引
-
-
SHOW INDEX FROM 表名;
-
删除索引
-
-
DROP INDEX 字段名 ON 表名;
-
-- 演示：-- 操作的表tb_usercreate table tb_user(id int primary key auto_increment comment '主键',name varchar(50) not null comment '用户名',phone varchar(11) not null comment '手机号',email varchar(100) comment '邮箱',profession varchar(11) comment '专业',age tinyint unsigned comment '年龄',gender char(1) comment '性别 , 1: 男, 2: 女',status char(1) comment '状态',createtime datetime comment '创建时间') comment '系统用户表';-- 查询索引show index from tb_user;-- 查询所有，竖向显示show index from tb_user \G;-- 1.name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。create index idx_user_name on tb_user(name);-- 2.phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。create unique index idx_user_phone on tb_user(phone);-- 3.为profession、age、status创建联合索引。create index idx_user_pro_age_sta on tb_user(profession,age,status);-- 4.为email建立合适的索引来提升查询效率。create index idx_user_email on tb_user(email);-- 删除索引idx_user_emaildrop index idx_user_email on tb_user;
5. SQL性能分析
SQL执行频率
SQL执行频率：
-
MySQL客户端连接成功后，通过show [session | global] status命令可以提供服务器状态信息。还可以通过show global status like 'Com_______'命令，查看当前数据库的INSERT \ UPDATE \ DELETE \ SELECT的访问频次。
-
查看服务器状态信息
-
-
SHOW [SESSION | GLOBAL] STATUS;
-
查看当前数据库的INSERT \ UPDATE \ DELETE \ SELECT的访问频次
-
-
-- 模糊匹配中有七个下划线'_'SHOW GLOBAL STATUS LIKE 'Com_______';
慢查询日志
慢查询日志：
-
慢查询日志记录了所有执行事件超过指定参数long_query_time，单位：秒，默认10秒的所有SQL语句的日志。
-
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件/etc/my.cnf中配置相应信息：
-
-
# /etc/my.cnf文件内：#开启MySQL慢查询开关slow_query_log=1#设置慢日志的时间为2秒，SQL语句执行超过2秒，被视为慢查询，记录慢查询日志long_query_time=2
-
# 修改MySQL配置文件/etc/my.cnf【Linux环境下】vi /etc/my.cnf# 1. 按i键进行编辑# 2. 寻找合适位置，输入上文给出的配置信息# 3. 按Esc键推出编辑，输入:wq并回车保存退出# 重启mysql服务器systemctl restart mysqld
-
查询慢查询日志是否开启
-
-
-- OFF代表关闭-- ON表示开启SHOW VARIABLES LIKE 'slow_query_log';
-
查看慢查询日志内容[Linux环境下]
-
-
# 慢查询日志保存在：/var/lib/mysql/localhost-slow.log 文件下cat /var/lib/mysql/localhost-slow.log
SQL性能分析
- profile详情
profile详情：
-
show profiles能够在做SQL优化时帮助我们了解时间都耗费在哪里了，通过have_profiling参数，能够看到当前MySQL是否支持查看profile详情。
-
查看MySQL是否支持查看profile详情
-
-
select @@have_profiling;
-
开启profiling (默认profiling是关闭的，可使用set语句在session/global级别开启)
-
-
-- 查看profiling开关是否开启select @@profiling;-- 开启profilingset profiling=1;
-
查看每一条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执行计划:
-
EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
-
查看SELECT语句执行计划（直接在select语句前加上explain / desc）
-
-
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;-- 或DESC SELECT 字段列表 FROM 表名 WHERE 条件;
-
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的值越大越好。
-
6. 索引的使用规则
最左前缀法则：
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。
范围查询：
联合索引中，出现范围查询(>，
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
上一篇：惠普链接服务器怎么连接?
下一篇：宝塔面板怎么上传数据？
