MySQL：进阶巩固-索引

---

一、概述

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

优点

• 提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点

• 索引列也是要占用空间的（解决办法：加硬盘）
• 索引大大提高了查询的效率，同时也降低了更新表的速度，如：对表进行INSERT、UPDATE、DELETE时，效率降低。

二、分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复，当为某个字段增加唯一约束时，会自动添加唯一索引	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

三、使用

3.1 创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name...);

CREATE INDEX idx_emp_name ON tb_emp(name);
CREATE UNIQUE INDEX idx_emp_phone ON tb_emp(phone);
CREATE INDEX idx_pro_age_sta ON tb_emp(profession, age, status);

3.2 查看索引

SHOW INDEX FROM table_name;

3.3 删除索引

DROP INDEX index_name ON table_name;

DROP INDEX id_emp_name ON tb_emp;

四、SQL性能分析

4.1 查看数据库的操作次数（CRUD）

SHOW GLOBAL STATUS LIKE 'Com_______';

4.2 慢查询日志

记录SQL执行时间大于设置时间的SQL语句，默认设置时长10s.

查看慢查询是否开启，默认是没有开启的

SHOW VARIABLES LIKE 'slow_query_log';

开启慢查询日志，修改配置文件（/etc/my.cnf），配置完成之后需要重新启动MySQL服务，慢查询日志的存放位置（/var/lib/mysql/localhost-slow.log）

# 开启MySQL慢查询日志
slow_query_log=1
# 设置慢查询日志时长，这里的2表示2s，超过2s就会被记录
long_query_time=2

重启MySQL服务

systemctl restart mysqld

4.3 profile详情

分析SQL耗时具体详情

查看MySQL是否支持profile

SELECT @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启proiling

SET profiling=1;

查看每一条SQL的耗时情况

SHOW PROFILES;

查看query_id各阶段耗时

SHOW PROFILE FOR QUERY query_id;

查看query_id CPU使用情况

SHOW PROFILE cpu FOR QUERY query_id;

4.4 explain执行计划

EXPLAIN 或者 DESC命令获取MySQL如何执行SELECT语句信息，包括在SELECT语句执行过程中表如何连接和连接顺序。

-- 直接在SELECT语句之前增加EXPLAIN或者DESC
EXPLAIN SELECT 字段列表 FFROM 表名 WHERE 条件;

五、索引使用