目录
一、概述
索引是帮助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 条件;