mysql 索引与慢查询优化

前世今生

数据是存在硬盘的，那么查询数据不可避免进行io操作

啥事索引

索引其实就是数据结构，类似书的目录。你看书的时候，要看某个章节，是在目录里查找该章节的页数的，然后再去该页数看内容，那么索引是目录，那么查数据就要先找目录，再去找数据,而不是一个一个去寻找了
索引在mysql中叫做贱，不对不对，是键，是存储引擎用户快速找到记录的一种数据结构

三种key

1. primary key
2. unique key
3. index key

注意：primary 和 unqiue 除了有加速的查询效果之外，还额外的加了约束的条件，且primary非空且唯一，而unique唯一。而index key没有任何的 约束功能只会帮助你加速查询

索引的本质

通过不断的缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的影响

在表中大量数据的前提下，创建索引速度会很慢
在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

B+树 待更新

根和树枝节点存的仅仅是虚拟数据
叶子节点是存放真实的数据
查询次数由树的层次决定，层级越低次数越少。
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项
思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

聚集索引 primary key

指的是表的主键，innodb引擎规定一张表中必须要有主键
myisam 在硬盘有三个表，一个放表结构，一个放数据，一个放索引
inodb 在硬盘有两个表，一个放表结构，一个放数据，那么它的索引放哪了？其实就和数据放一起了
myisam查询数据快的原因就是因为它存放了索引
特点：叶子节点放的一条条完整的记录

辅助索引 unique key, index key

查询数据的时候我们不可能都用id来查询数据，也可以用别的字段来查询，那么这个时候就可以给其他字段建立索引，这些索引就是辅助索引
特点：叶子节点存放的是辅助索引字段对应的那条记录的主键的值， what！！如何理解？如果给name字段来创建索引，那么叶子节点存放的是name对应的值所在的那条件记录的主键值，

慢查询日志

设定一个时间，然后检测所有超出该时间的sql语句，然后针对性的进行优化

测试查询

测试的数据提前准备

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明 分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

测试测试

# 表没有任何索引的情况下
select * from s1 where id = 2999999;

mysql> select * from s1 where id = 2999999;
+---------+-------+--------+---------------------+
| id      | name  | gender | email               |
+---------+-------+--------+---------------------+
| 2999999 | jason | male   | jason2999999@oldboy |
+---------+-------+--------+---------------------+
1 row in set (0.00 sec)

# 避免打印带来的时间损耗
select count(id) from s1 where id = 2999999;
select count(id) from s1 where id = 1;

mysql> select count(id) from s1 where id = 2999999;
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(id) from s1 where id = 1;
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

select count(id) from s1 where name = 'jason'

mysql> select count(id) from s1 where name = 'jason';
+-----------+
| count(id) |
+-----------+
|   2999999 |
+-----------+
1 row in set (0.88 sec)

select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

mysql> select count(id) from s1 where id > 1;
+-----------+
| count(id) |
+-----------+
|   2999998 |
+-----------+
1 row in set (0.56 sec)

mysql> select count(id) from s1 where id > 1 and id < 3;
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(id) from s1 where id > 1 and id < 10000;
+-----------+
| count(id) |
+-----------+
|      9998 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(id) from s1 where id != 3;
+-----------+
| count(id) |
+-----------+
|   2999998 |
+-----------+
1 row in set (0.56 sec)

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

mysql> alter table s1 drop primary key;
Query OK, 2999999 rows affected (8.85 sec)
Records: 2999999  Duplicates: 0  Warnings: 0
mysql> select count(id) from s1 where name = 'jason';
+-----------+
| count(id) |
+-----------+
|   2999999 |
+-----------+
1 row in set (1.22 sec)

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！

mysql> create index idx_name on s1(name);
Query OK, 0 rows affected (6.64 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select count(id) from s1 where name = 'jason';
+-----------+
| count(id) |
+-----------+
|   2999999 |
+-----------+
1 row in set (15.62 sec)  # 辅助索引 还是不如没有索引来的快呢

“”"
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
“”"

select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

mysql> select count(id) from s1 where name = 'jason';
+-----------+
| count(id) |
+-----------+
|   2999999 |
+-----------+
1 row in set (19.16 sec)
mysql> select count(id) from s1 where name like 'jason';
+-----------+
| count(id) |
+-----------+
|   2999999 |
+-----------+
1 row in set (2.62 sec)
mysql> select count(id) from s1 where name like 'jaso%';
+-----------+
| count(id) |
+-----------+
|   2999999 |
+-----------+
1 row in set (2.52 sec)
mysql> select count(id) from s1 where name like '%ason';
+-----------+
| count(id) |
+-----------+
|   2999999 |
+-----------+
1 row in set (1.33 sec)

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

mysql> create index idx_id on s1(id);
Query OK, 0 rows affected (2.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(id) from s1 where id = 3;
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(id) from s1 where id*12 = 3;
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.69 sec)

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件

mysql> drop index idx_id on s1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(id) from s1 where name = 'jason' and gender = 'male' and id = 3 and email = 'xx';
+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (17.88 sec)