mysql索引56

一、索引的概述
1.索引就类似图书的目录，可以提高查询效率；
2.索引其实就是一种排好序的数据结构。



索引的分类

主键索引：根据主键创建索引，不能重复，不能空值；

-- 创建表时创建主键索引
create table `user`(
`id` int not null primary key auto_increment,
`uid` int
)engine=innodb default charset=utf8mb4;

-- 修改时添加主键和自增
alter table user modify uid int primary key auto_increment;


-- 删除主键时，先删除自增，再删除主键
-- 先删除自增
alter table user modify uid int;
-- 再删除主键
alter table user drop primary key;





唯一索引：用来作为索引的值必须是唯一的，允许为空；

-- 创建表时创建唯一索引
create table `user`(
`name` varchar(255),
unique index `uin_name`(`name`)
)engine=innodb default charset=utf8mb4;

-- 修改时添加唯一索引
alter table user add unique index uid_name(name);

-- 删除唯一索引
alter table user drop index uin_name;


普通索引：用表中的普通列构建的索引，没有任何限制；

-- 创建表时直接创建普通索引
create table `user`(
`id` int, 
`name` varchar(10) not null,
key `in_name`(`name`)
)engine=innodb default charset=utf8mb4;

-- 添加索引
alter table user add index in_id(id);


-- 删除索引
drop index in_id on user;


全文索引：用大文本对象构建的列形成的索引；
组合索引：用多个列组合构成索引，这多个列中的值不允许有空值。



二、索引的原理


B+tree 数据结构的优点？
1.磁盘读写代价更低；
2.随机I/O次数降低；
3.读写速度更稳定；


聚簇索引：索引即数据，数据即索引；
非聚簇索引：主键外的其他索引，找到了索引，仅仅是找到了当前索引值和key，
		想要找到这之外的内容，要回表。


三、慢查询与SQL优化
再了解了索引的基本概述和索引的原理后，如何更好的运用索引？对执行较慢的
索引进行优化也成为我们必须掌握的一项技能。


SQL中的慢查询，全名叫慢查询日志，
是SQL中用来记录执行时间超过阀值的SQL语句，
默认情况下，MYSQL数据库并不开启慢查询日志，需要手动开启这个参数，
如果不是调优需要的话，一般不建议启动该参数，因为开启的话或多或少会对性能产生影响。

注：慢查询日志可以查询执行较慢的SQL语句，因此是调优的候选者。


-- 查询慢查询设置
show variables like '%slow%';

-- 查看默认慢查询阀值时间
show variables like '%long_query_time%';

-- 设置慢查询阀值时间
set long_query_time = 2;

-- 开启慢查询
set global slow_query_log = 'ON';


-- 创建一个user表
create table `user`(
`id` int not null auto_increment,
`name` varchar(255) default null,
`age` int(255) default null,
primary key id(id)
)engine=innodb default charset=utf8mb4;

-- 创建一个能插入一千万条数据的存储过程p1
\d //
create procedure p1()
begin
set @i = 1;
while @i <= 10000000 do
insert into user values(null, concat('user:',@i),@i%100+10); 
set @i = @i + 1;
end while;
end;
//
\d ;

-- 执行p1()，往user表中插入一千万条数据
call p1();
Query OK, 0 rows affected (21 min 9.35 sec)

-- 数据插入成功
select count(id) from user;
+-----------+
| count(id) |
+-----------+
|  10000000 |
+-----------+
1 row in set (2.16 sec)

select * from user where name = 'user:999999';
+--------+-------------+------+
| id     | name        | age  |
+--------+-------------+------+
| 999999 | user:999999 |  109 |
+--------+-------------+------+
1 row in set (2.71 sec)

-- 类似上述的两个查询就是慢查询了
-- 之前讲过，给字段添加索引可以提高查询的速度

-- 给name字段添加一个普通索引
alter table user add index name(name);
Query OK, 0 rows affected (20.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

select * from user where name = 'user:999999';
+--------+-------------+------+
| id     | name        | age  |
+--------+-------------+------+
| 999999 | user:999999 |  109 |
+--------+-------------+------+
1 row in set (0.00 sec)
-- 同样的SQL语句，查询速度明显加快了很多

-- 由上可见，索引能高效的加快查询的速度，但是每创建一个索引就会建立一个B+tree，
-- 并且需要维护，这是很费性能和内存的。
-- 因此，要合理创建索引。


慢查询日志的查看方式
show variables like '%slow%';
+---------------------------+---------------------------------------------------------+
| Variable_name             | Value                                                   |
+---------------------------+---------------------------------------------------------+
| log_slow_admin_statements | OFF                                                     |
| log_slow_slave_statements | OFF                                                     |
| slow_launch_time          | 2                                                       |
| slow_query_log            | ON                                                      |
| slow_query_log_file       | /usr/local/var/mysql/menglingpengdeMacBook-Pro-slow.log |
+---------------------------+---------------------------------------------------------+
5 rows in set (0.01 sec)

上面的表格中slow_query_log_file就是慢查询日志在电脑上的存储位置
进入finder选择go to folder 进入指定路径即可。

# Time: 2021-08-19T06:55:33.848094Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 2.716305  Lock_time: 0.000326 Rows_sent: 1  Rows_examined: 10000000
SET timestamp=1629356133;
select * from user where name = 'user:999999';


MySQL 为我们提供了explain 语句来展示某个语句的具体执行计划。
explain select * from user where name = 'user:999999'\G;
*************************** 1. row ***************************
           id: 1     					-- 在一个大的查询语句中每一个select关键字都对应一个确定的id
  select_type: SIMPLE					-- select关键字对应的那个查询语句的类型
        table: user    					-- 表名
   partitions: NULL						-- 匹配的分区信息
         type: ALL 						-- 针对单表的查询方式
possible_keys: NULL						-- 可能用到的索引
          key: NULL						-- 实际用到的索引
      key_len: NULL						-- 实际用到的索引的长度
          ref: NULL						-- 当时用到索引等值查询时，与索引列进行等值匹配的对象信息
         rows: 9759999					-- 预计需要读取的记录条数
     filtered: 10.00      				-- 某个表经过过滤条件后，剩余记录条数的百分比
        Extra: Using where    			-- 一些额外的信息
1 row in set, 1 warning (0.00 sec)