day36_mysql索引
目录
1、使用索引的好处
提高查询效率
2、索引的本质
给表格创建索引后，会将数据按照索引有序存储，即将本来无序的数据表，重新按照索引的规则重排
实例：
创建无索引的表格test1:
create table test(
id int,
a int,
b int,
c int
)charset utf8;
insert into test values
(5,2,6,9),
(2,3,1,4),
(4,4,3,2),
(1,5,2,9);
创建索引前表格存储顺序结构：
mysql> select * from test;
+------+------+------+------+
| id | a | b | c |
+------+------+------+------+
| 5 | 2 | 6 | 9 |
| 2 | 3 | 1 | 4 |
| 4 | 4 | 3 | 2 |
| 1 | 5 | 2 | 9 |
+------+------+------+------+
4 rows in set (0.00 sec)
添加主键索引：
alter table test add primary key(id);
创建索引后的表格存储顺序结构：
mysql> select * from test;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 5 | 2 | 9 |
| 2 | 3 | 1 | 4 |
| 4 | 4 | 3 | 2 |
| 5 | 2 | 6 | 9 |
+----+------+------+------+
4 rows in set (0.00 sec)
添加联合索引ix_a_b_c:
为了显示联合索引的排序特点，需要添加几行数据：
insert into test(id,a,b,c) values
(6,1,1,1),
(7,2,2,2),
(8,3,3,3),
(9,4,4,4),
(10,5,5,5);
mysql> select * from test;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 5 | 2 | 9 |
| 2 | 3 | 1 | 4 |
| 4 | 4 | 3 | 2 |
| 5 | 2 | 6 | 9 |
| 6 | 1 | 1 | 1 |
| 7 | 2 | 2 | 2 |
| 8 | 3 | 3 | 3 |
| 9 | 4 | 4 | 4 |
| 10 | 5 | 5 | 5 |
+----+------+------+------+
10 rows in set (0.00 sec)
添加索引：
alter table test add ix_a_b_c(a,b,c);
添加联合索引后的表格存储顺序结构：
mysql> select * from test;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 6 | 1 | 1 | 1 |
| 7 | 2 | 2 | 2 |
| 5 | 2 | 6 | 9 |
| 2 | 3 | 1 | 4 |
| 8 | 3 | 3 | 3 |
| 4 | 4 | 3 | 2 |
| 9 | 4 | 4 | 4 |
| 1 | 5 | 2 | 9 |
| 10 | 5 | 5 | 5 |
+----+------+------+------+
9 rows in set (0.00 sec)
明显看到：
1、主键排序优先级最低
2、按照联合索引，从最左边索引键排序，在此基础上按照第二个索引键排序，再在此基础上按照第三个索引键排序
结论：
1、创建索引时，数据存储会按照索引重排
2、主键索引的排序级别最低
3、创建联合索引时，相当于创建多个索引，从左至右逐次排序：
如联合索引(a,b,c)等同于创建了三个索引(a),(a,b),(a,b,c)
3、索引的底层原理
B+树
4、索引的种类
主键索引：加速查找，不能重复，不能为空 primary key
唯一索引：加速查找，不能重复 unipue(字段名)
普通索引：加速查找 index (字段名)
联合索引：
- 联合唯一索引：
unipue(name,password) - 联合索引：
index(name,password)
5、索引的创建
5.1 主键索引
新增主键索引：一般将id设为主键，不新增字段
# 方法1
create table 表名(
id int auto_increment privary key);
# 方法2 将某字段修改为主键
alter table 表名 change 旧字段名 新字段名 数据类型 auto_increment privary key;
# 方法3 为
alter table 表名 add primary key (name)
删除主键索引：
如果主键被自增约束，需要将自增去掉，才能删除主键索引
alter table drop primary key
5.2 唯一索引
新增唯一索引：
# 方法1
create table 表名(
id int auto_increment privary key,
name varchar(32) not null default '',
unipue index 唯一索引名 (name)
) charset utf8;
# 方法2
alter table 表名 add unipue index 唯一索引名 (字段名)
# 方法3
create unique index 唯一索引名 on 表名(字段名);
删除唯一索引：
alter table 表名 drop index 唯一索引名
5.3 普通索引
新增普通索引：
# 方法1
create table 表名(
id int auto_increment privary key,
name varchar(32) not null default '',
index 普通索引名 (name)
) charset utf8;
# 方法2
alter table 表名 add index 普通索引名(字段名)
# 方法3
create index 普通索引名 on 表名(字段名);
删除普通索引：
alter table 表名 drop index 普通索引名
5.4 联合索引
新增联合索引
# 方法1
create table 表名 (
id int primary key,
name varchar(32),
password varchar(32),
nuique index ix_name_password (name,password))
# 方法2
alter table 表名 add index ix_name_password (name,password);
删除联合索引
alter table 表名 drop index ix_name_password;
联合索引最左前缀
创建联合索引时，遵循最左前缀匹配的原则，即最左优先，检索数据时从联合索引的最左便开始匹配
联合索引
5.5 注意点
1、联合索引最左前缀
即只要联合索引的最左边的索引命中之后，就能命中联合索引，见本页索引的本质
2、联合唯一索引
此时唯一指的是联合整体唯一，单个是可以重复的
实例：
create table test1(
id int auto_increment primary key,
a varchar(32),
b varchar(32),
c varchar(32),
d varchar(32),
unique index ix_a_b_c_d(a,b,c,d)
)charset utf8;
# 正常
insert into test1(a,b,c,d) values
(1,2,3,4),
(1,2,3,5);
# 报错
insert into test1(a,b,c,d) values
(1,2,3,4),
(1,2,3,4);
3、普通索引与唯一索引只有是否约束了unique的区别，就像无符号整形与整·型一样
6、索引的优缺点
通过观察 *.ibd文件可知：
1、索引加快了查询速度
2、加了索引后，会占用大量磁盘空间
7、explain
查看执行sql语句时发生的事情
在sql语句前 加上expalin
expalin select * from user\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: class
type: ALL
possible_keys: NULL # 可能用到的key
key: NULL # 用到的key
key_len: NULL # 用到的key 长度
ref: NULL
rows: 1 # 执行结果遍历的行数
Extra: NULL # 用到的约束条件
1 row in set (0.00 sec)
ERROR:
No query specified
8、慢查询日志
8.1 查看满sql相关变量
show variables like '%slow%';
show variables like '%slow%';
+---------------------------+------------------------------------------------------------+
| Variable_name | Value |
+---------------------------+------------------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | D:\MySQL\mysql-5.6.46-winx64\data\DESKTOP-0575AOD-slow.log |
+---------------------------+------------------------------------------------------------+
8.2 配置慢sql的变量
set global 变量名 = 值
set global slow_query_log = on;
set global slow_query_log_file = "D:\MySQL\mysql-5.6.46-winx64\data\DESKTOP-0575AOD-slow.log";
set global long_query_time = 1;
9、命令行操作日志记录
tee log_file_path
