15.索引-分类
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的事文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clustered Index) | 将数据存储与索引放到了一块,索引结构的叶子结点保存了行数据 | 必须有,而且只有一个 |
二级索引(Secondary Index) | 将数据与索引分开存储,索引结构的叶子结点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一(
UNIQUE
)索引作为聚集索引 - 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
16.索引-思考题
17.索引-语法
-
创建索引
CRETE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);
-
查看索引
SHOW INDEX FROM table_name;
-
删除索引
DROP INDEX index_name ON table_name;
创建数据
create table tb_user(
id int primary key,
name varchar(10),
phone char(11) unique,
email varchar(30) unique,
profession varchar(15),
age int,
gender char(1),
status int,
createtime datetime
);
insert into tb_user (id, name, phone, email, profession, age, gender, status, createtime) values
(1, '吕布', '17799990001', 'lvbu666@163.com', '软件工程', 23, '1', 6, '2001-02-02 00:00:00'),
(2, '曹操', '17799990002', 'caocao666@qq.com', '通讯工程', 33, '1', 0, '2001-03-05 00:00:00'),
(3, '赵云', '17799990003', '1779990@139.com', '英语', 34, '1', 2, '2002-03-02 00:00:00'),
(4, '孙悟空', '17799990004', '17779990@sina.com', '工程造价', 54, '1', 0, '2001-07-02 00:00:00'),
(5, '花木兰', '17799990005', '19980729@sina.com', '软件工程', 23, '2', 1, '2001-04-22 00:00:00'),
(6, '大乔', '17799990006', 'daqiao666@sina.com', '舞蹈', 22, '2', 0, '2001-02-07 00:00:00'),
(7, '露娜', '17799990007', 'luna_love@sina.com', '应用数学', 24, '2', 0, '2001-02-08 00:00:00'),
(8, '程咬金', '17799990008', 'chengyaojin@163.com', '化工', 38, '1', 5, '2001-05-23 00:00:00'),
(9, '项羽', '17799990009', 'xiaoyu666@qq.com', '金属材料', 43, '1', 0, '2001-08-18 00:00:00'),
(10, '白起', '17799990010', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', 2, '2001-08-16 00:00:00'),
(11, '韩信', '17799990011', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', 0, '2001-06-12 00:00:00'),
(12, '荆轲', '17799990012', 'jingke123@163.com', '会计', 29, '1', 0, '2001-05-11 00:00:00'),
(13, '兰陵王', '17799990013', 'lanlinwang666@126.com', '工程造价', 44, '1', 1, '2001-04-09 00:00:00'),
(14, '狂铁', '17799990014', 'kuangtie@sina.com', '应用数学', 43, '1', 2, '2001-04-10 00:00:00'),
(15, '貂蝉', '17799990015', '84958948374@qq.com', '软件工程', 40, '2', 3, '2001-02-12 00:00:00'),
(16, '妲己', '17799990016', '2783238293@qq.com', '软件工程', 31, '2', 0, '2001-01-30 00:00:00'),
(17, '芈月', '17799990017', 'xiaomin2202@sina.com', '工业经济', 35, '2', 0, '2000-05-03 00:00:00'),
(18, '嬴政', '17799990018', '8839434342@qq.com', '化工', 38, '1', 1, '2001-08-08 00:00:00'),
(19, '狄仁杰', '17799990019', 'jujiamlm8166@163.com', '国际贸易', 30, '1', 0, '2007-03-12 00:00:00'),
(20, '安琪拉', '17799990020', 'jdodm1h@126.com', '城市规划', 51, '2', 0, '2001-08-15 00:00:00'),
(21, '典韦', '17799990021', 'ycaunanjian@163.com', '城市规划', 52, '1', 2, '2000-04-12 00:00:00'),
(22, '廉颇', '17799990022', 'lianpo321@126.com', '土木工程', 19, '1', 3, '2002-07-18 00:00:00'),
(23, '后羿', '17799990023', 'altycj2000@139.com', '城市园林', 20, '1', 0, '2002-03-10 00:00:00'),
(24, '姜子牙', '17799990024', '37483844@qq.com', '工程糟践', 29, '1', 4, '2003-05-26 00:00:00');
-
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, status);
-
为email建立合适的索引来提升查询效率。
create index idx_user_email on tb_user(email); drop index idx_user_email on tb_user;
18.索引-性能-查看执行频次
MySQL客户端连接成功后,通过show [session | global] status
命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT
的访问频次:
SHOW GLOBAL STATUS LIKE 'COM______';
19.索引-性能-慢查询日志
查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志
MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
# 开启MySQL慢日志查询开关
slow_query_log = 1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒就会视为慢查询,记录慢查询日志
long_query_time = 2
配置完成后,通过一下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log
。
SQL语句设置慢日志查询
!!!没找到my.cnf如何配置慢日志查询!!!
直接在datagrip中设置即可
set global slow_query_log = 'ON';
set global slow_query_log_file ='/var/log/mysql/slow_query.log';
set global long_query_time = 2;
show variables like 'slow_query_log';
这里的目标文件地址不可以任意自己制定,只能是’/var/log/mysql/slow_query.log’!!!