索引
什么是索引
- 索引相当于一本书的目录，为了缩小扫描范围而存在、提高查询效率的一种数据结构
- 一张表的一个字段可以添加索引，多个字段也可以联合起来添加索引
- 在任何数据库中，主键都会自动添加索引，另外在MySQL中，一个字段如果有 UNIQUE 约束，也会自动添加索引
- 优点
- 提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本
- 缺点
- 索引列也要占用空间 -- （空间便宜）
- 降低更新表的速度，对表进行 DML操作时效率低 -- （DML语句比例小，主要是查询)
- 在任何数据库中，任意一张表的任意一条记录在硬盘上都有一个硬盘的物理存储编号
- 在MySQL中，索引是一个单独的对象，在不同的存储引擎中以不同的形式存在，无论何种引擎，索引都以 数 的形式存储，结构是B+Tree
- 在InnoDB存储引擎中，索引存储在一个逻辑名称叫做 tablespace 的表空间中
- 在MyISAM存储引擎中，索引存储在 .MYI 文件中
- 在MEMORY存储引擎中，索引被存储在内存中
索引的结构
- B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
- 相对于二叉树，层级更少，搜索效率更高
- 对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，导致一页中存储的键值减少，指针也减少，要同样保存大量数据，只能增加树的高度，导致性能降低
- MySQL对B+Tree作了优化，叶子节点间不再是单向链表而是双向链表
- Hash索引：底层数据结构由哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
- R-Tree
- Full-text
索引的分类
- 主键索引
- 关键字：PRIMARY KEY
- 针对表中主键创建的索引，默认自动创建，只能有一个
- 唯一索引
- 关键字：UNIQUE
- 避免同一表中某列的数据重复，可以有多个
- 常规索引
- 用于快速定位数据，可以有多个
- 全文索引
- 关键字：FULLTEXT
- 全文索引查找的是文本中关键词，而不是索引的值，可以有多个
InnoDB存储引擎中，索引又分一下两种
- 聚集索引
- 必须有且只有一个
- 将数据存储与索引放在了一块，索引结构的叶子节点保存了数据
- 如果存着主键，主键索引就是聚集索引
- 如果不存在主键，使用第一个唯一 UNIQUE 索引作为聚集索引
- 如果没有主键也没有唯一索引，InnoDB会自动生成一个 rowid 作为隐藏的聚集索引
- 叶子节点存放每一条完整记录
- 二级索引
- 将数据和索引分来存储，索引结构的叶子节点关联的是对应的主键，可以存在多个
- 叶子节点存放每一条记录的聚集索引列的值
- 查询二级索引时，先通过二级索引的叶子节点获取聚集索引的值，再通过聚集索引查询完整记录 => 也就是说，通过二级索引查询记录必然需要通过聚集索引查询 -- 这个过程称为：回表查询
索引的语法
-
创建索引
-
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name, ... );
-
-
查看索引
-
SHOW INDEX FROM table_name;
-
-
删除索引
-
DROP INDEX index_name ON table_name;
-
案例
name字段为姓名字段，可能会重复，创建常规索引
-
CREATE INDEX idx_user_name ON tb_user(name);
phone手机号字段，非空且唯一，创建唯一索引
-
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
为profession、age、status创建联合索引
-
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, statue); -
联合索引会按字段序编号
为email建立合适的索引 -- 常规索引
-
CREATE INDEX idx_user_email ON tb_user(email);
删除email索引
-
DROP INDEX idx_user_email ON tb_user;
SQL性能分析
-
SQL执行频率
-
通过 SHOW [SESSION|GLOBAL] STATUS 命令可以提供服务器状态信息，通过以下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次
-
SHOW GLOBAL STATUS LIKE 'Com_______'; //模糊查询七个下划线，涵盖基本语句
-
-
慢查询日志
-
用于定位哪些SQL语句执行效率比较低，从而对其进行优化
-
用于记录所有执行速度超过预设指定时间（默认10秒）的所有SQL语句的日志
-
慢查询日志默认未开启，需要在MySQL配置文件（/etc/my.cnf）中进行配置
-
查询是否开启慢查询
-
SHOW VARIABLES LIKE 'slow_query_log';
-
-
/etc/my.cnf文件的配置信息
- slow_query_log = 1 //开启MySQL慢日志查询开关
- long_query_time = 2 //设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询记入慢日志
-
-
profile详情
-
show profile 能够在做SQL优化时帮助我们了解时间都耗费在哪里，通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
-
SELECT @@have_profiling;
-
-
查看profile是否开启
-
SELECT @@profiling;
-
-
开启profile
-
SET profiling = 1;
-
-
查看之前执行的SQL语句耗时情况
-
SHOW PROFILES;
-
-
查看指定query_id的SQL语句的耗时情况
-
SHOW PROFILE FOR QUERY 140;
-
-
-
explain 执行计划
-
在 SELECT 语句前加 EXPLAIN 关键字，获取MySQL如何执行SELECT语句的信息，包括SELECT执行中表的连接和执行顺序
-
EXPLAIN SELECT * FROM tb_user;
-