进阶-2.索引

1.索引概述

索引(index)是帮助mysql高效获取数据的数据结构（有序）。在数据库之外，数据库系统还维护着满足特定查询算法的数据结构，这些数据结构以某种方式执行数据，这样就可以在这些数据结构实现高级查找算法，这种数据结构就是索引。

索引优缺点

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也要占用空间的
通过索引列对数据进行排序，降低数据排列的成本，降低CPU的消耗	索引大大提高了查询效率，也降低了表更新速度，如对表进行INSERT、UPDATE、DELETE时，效率降低

2.索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

索引结构	描述
B+Tree索引	最常见的索引，大部分引擎支持B+Tree索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配的查询才有效，不支持范围查询
R-Tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型
Full-Text(全文索引)	是一种通过快速建立倒排索引，快速匹配文档的方式

2.1 二叉树

2.2 B-Tree

动画演示
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

2.3 B+Tree

以一个最大度数max-degree为4（4阶）为例

2.4 Hash

哈希索引就是采用一定的Hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或者多个）键值，映射再同一个槽位，出现hash碰撞，通过链表

3.索引分类

InnoDB存储引擎，索引存储形式分类
聚集索引
二级索引

4.索引语法

• 创建索引

CREATE [ UNIQUE| FULLTEXT ] INDEX index_name ON TABLE_NAME (index_col_name,…);

• 查看索引

SHOW INDEX FROM table_name;

• 删除索引

DROP INDEX index_name ON table_name;

5.SQL性能分析

5.1 SQL执行频率

MySQL客户端连接成功后，通过SHOW [SESSION|GLOBAL] STATUS命令可以提供服务器状态信息，通过
如下指定可以查看当前数据库INSERT、UPDATE、DELETE、SELECT的访问频次；

SHOW GLOBAL STATUS LIKE ‘Com ______’;

show global status like 'com_______'

5.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位秒。默认10S）所有SQL语句的日志。
MySQL的慢查询日志默认没有开始，需要在MySQL的配置文件（/etc/my.cnf）中配置：

查看慢日志查询是否开启

show variables like 'slow_query_log'

vi /etc/my.cnf

#开启慢日志查询开关
slow_query_log=1

#设置慢日志的时间为2s。SQL语句执行时间超过2s，就会视为慢查询，记录慢查询日志
long_query_time=2

设置完毕，通过以下指令重启MYSQL服务进行测试，查看慢日志记录的信息/var/lib/mysql/localhost-slow.log

systemctl restart mysql

5.3 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;

5.4 explain执行计划

6.索引使用

验证索引效率

在未建立索引之前，执行如下SQL语句，查看SQL的耗时

SELECT * FROM tb_sku where sn=‘10000000031450001’;

使用规则

6.1 最左前缀法则

6.2 范围查询

6.3 索引列运算

6.4 字符串不加引号

隐式类型转换

6.5 模糊查询

6.6 or连接的条件

6.7 数据分布情况

6.8 SQL提示

mysql自动优化，使用哪个索引

6.9覆盖索引

6.10 前缀索引

6.11 单列索引与联合索引

6.12 索引的设计原则