个人简介：Java领域新星创作者；阿里云技术博主、星级博主、专家博主；正在Java学习的路上摸爬滚打，记录学习的过程~
个人主页：.29.的博客
学习社区：进去逛一逛~
⑩② 【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_user create table tb_user( id int primary key auto_increment ***ment '主键', name varchar(50) not null ***ment '用户名', phone varchar(11) not null ***ment '手机号', email varchar(100) ***ment '邮箱', profession varchar(11) ***ment '专业', age tinyint unsigned ***ment '年龄', gender char(1) ***ment '性别 , 1: 男, 2: 女', status char(1) ***ment '状态', createtime datetime ***ment '创建时间' ) ***ment '系统用户表'; -- 查询索引 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_email drop index idx_user_email on tb_user;
5. SQL性能分析
🚀SQL执行频率
SQL执行频率：
-
MySQL客户端连接成功后，通过
show [session | global] status命令可以提供服务器状态信息。还可以通过show global status like '***_______'命令，查看当前数据库的INSERT \ UPDATE \ DELETE \ SELECT的访问频次。 -
🚀查看服务器状态信息
-
-
SHOW [SESSION | GLOBAL] STATUS;
-
-
🚀查看当前数据库的INSERT \ UPDATE \ DELETE \ SELECT的访问频次
-
-
-- 模糊匹配中有七个下划线'_' SHOW GLOBAL STATUS LIKE '***_______';
-
🚀慢查询日志
慢查询日志：
-
慢查询日志记录了所有执行事件超过指定参数
long_query_time，单位：秒，默认10秒的所有SQL语句的日志。 -
🚀MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件
/etc/my.***f中配置相应信息： -
-
# /etc/my.***f文件内： #开启MySQL慢查询开关 slow_query_log=1 #设置慢日志的时间为2秒，SQL语句执行超过2秒，被视为慢查询，记录慢查询日志 long_query_time=2 -
# 修改MySQL配置文件/etc/my.***f【Linux环境下】 vi /etc/my.***f # 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; -- 开启profiling set 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. 索引的使用规则
最左前缀法则：
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。
范围查询：
联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效。
索引列运算：
不要再索引列上进行运算操作(max() avg() count()等)，否则索引会失效
字符串不加引号：
字符串类型字段使用时，若不加引号''，索引失效
模糊查询：
如果仅仅是字符串尾部模糊匹配，索引不会失效。如果是字符串头部模糊匹配，索引失效。
使用or来连接条件：
用or分割开的条件，如果or前的条件中的列有索引，而or后面的列中没有索引，那么涉及的索引都不会被用到。即or连接的条件都需建立索引才能使得索引生效。
数据分布影响：
如果MySQL评估使用索引比全表更慢，则不使用索引。
SQL提示：
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
-
use index()：建议查询时使用指定索引 -
-
SELECT 字段列表 FROM 表名 [USE INDEX(索引名)] WHERE 条件;
-
-
ignore index()：建议查询时忽略指定索引 -
-
SELECT 字段列表 FROM 表名 [IGNORE INDEX(索引名)] WHERE 条件;
-
-
force index()：查询时强制使用指定索引 -
-
SELECT 字段列表 FROM 表名 [FORCE INDEX(索引名)] WHERE 条件;
-
覆盖索引 ：
== 应当尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），从而减少 select * 的使用。 ==
-
explain查看查询计划时，最后一个字段
Extra的显示： -
-
using index condition：查找使用了索引，但是需要回表查询数据。 -
using where；using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。
-
-
思考题：
-
答：为id、password字段创建联合索引，这样就实现了覆盖索引，且不需要回表查询，效率高。
前缀索引：
当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率 。
-
🚀选取部分前缀建立索引
-
-
CREATE INDEX 索引名 ON 表名(column(前缀长度)); -
前缀长度：
-
-
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
-
-- 计算tb_user表 字段email 的选择性 select count(distinct email) / count(*) from tb_user; -- 原本基础上，设置前缀长度为5，计算选择性 select count(distinct substring(email,1,5)) / count(*) from tb_user;
-
-
单列索引 和 联合索引：
- 单列索引：一个索引只包含单个列
- 联合索引：一个索引包含了多个列
- 在业务场景中，如果存在多个查询条件，考虑针对查询字段检索引时，建议建立联合索引，而非单列索引。
7. 索引设计原则
-
1.针对于数据量较大，且查询比较频繁的表建立索引。
-
2.针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
-
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
-
4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
-
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
-
6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
-
7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
