mysql索引学习笔记

感谢尚学堂

b站有。
思维导图和相应的sql大部分来自课程中，少部分来自《高性能mysql》书籍

思维导图

部分截图

文件链接：链接：https://pan.baidu.com/s/1bTUsfIO6YQiBx5knjMnG5A
提取码：x2fw

sql文件

use shangxuetang;
-- todo p13
CREATE TABLE `tbl_dept` (
                            `id` INT(11) NOT NULL AUTO_INCREMENT,
                            `deptName` VARCHAR(30) DEFAULT NULL,
                            `locAdd` VARCHAR(40) DEFAULT NULL,
                            PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_emp` (
                           `id` INT(11) NOT NULL AUTO_INCREMENT,
                           `name` VARCHAR(20) DEFAULT NULL,
                           `deptId` INT(11) DEFAULT NULL,
                           PRIMARY KEY (`id`),
                           KEY `fk_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_jid` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('BSR',250);

INSERT INTO tbl_emp(NAME ,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME ,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME ,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',5 );
INSERT INTO tbl_emp(NAME,deptId) VALUES('wgk',999);
select *
from tbl_dept;
-- 内连接
select *
from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
-- 左连接
select *
from tbl_emp a left join tbl_dept b on a.deptId = b.id;
-- 右连接
select *
from tbl_emp a right join tbl_dept b on a.deptId = b.id;
-- 左外连接
select *
from tbl_emp a left join tbl_dept b on a.deptId = b.id
where b.id is null;
-- 右外连接
select *
from tbl_emp a right join tbl_dept b on a.deptId = b.id
where a.deptId is null;

-- 全连接
# select * from tbl_emp a full outer join  tbl_dept b on a.deptId = b.id;  mysql 不支持这种语法
select *
from tbl_dept A left join tbl_emp B on B.deptId = A.id
union select *
      from tbl_dept A right join tbl_emp B on B.deptId = A.id;
-- a 的独有 b的独有
explain select *
        from tbl_dept A left join tbl_emp B on B.deptId = A.id
        where B.deptId is null
        union select *
              from tbl_dept A right join tbl_emp B on B.deptId = A.id
              where A.id is null;

-- 查询
EXPLAIN select *
        from tbl_emp;
explain select *
        from tbl_emp
        where name = 'z3';
explain select *
        from tbl_emp
        where id between 1 and 10;



-- todo  p31 建表语句-索引优化单表分析
CREATE TABLE IF NOT EXISTS `article` (
  `id`          INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `author_id`   INT(10) UNSIGNED NOT NULL,
  `category_id` INT(10) UNSIGNED NOT NULL,
  `views`       INT(10) UNSIGNED NOT NULL,
  `comments`    INT(10) UNSIGNED NOT NULL,
  `title`       VARBINARY(255)   NOT NULL,
  `content`     TEXT             NOT NULL
);
INSERT INTO `article` (`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
  (1, 1, 1, 1, '1', '1'),
  (2, 2, 2, 2, '2', '2'),
  (3, 3, 3, 3, '3', '3');
-- 查询category_id为1且comments大于1的情况下，viewa最多的article_id
explain select id
        from article
        where category_id = 1 and comments > 1
        order by views desc
        limit 1;
-- 创建索引
create index idx_article_ccv
  on article (category_id, comments, views);
-- 查看建立的索引
show index from article;
-- extra: using where using index Using filesort  type: range 说明有点优化，但是还是出现 using filesort
explain select id
        from article
        where category_id = 1 and comments > 1
        order by views desc
        limit 1;
-- 把 comments >1 修改为 comments =1.Type取值不同。
explain select id
        from article
        where category_id = 1 and comments = 1
        order by views desc
        limit 1;
-- type 为 const 常量。
explain select id from article where id=1;
drop index idx_article_ccv
on article;
show index from article;
-- 再次创建索引
create index idx_article_cv
  on article (category_id, views);
explain select id
        from article
        where category_id = 1 and comments > 1
        order by views desc
        limit 1;

-- todo p32  索引分析案例
CREATE TABLE IF NOT EXISTS `class` (
  `id`   INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `card` INT(10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book` (
  `bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `card`   INT(10) UNSIGNED NOT NULL
);
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));

INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
-- 验证数据
select *
from class;
select *
from book;
-- 两个type都是all 准备加索引，但是不知道加在哪张表之上。
explain select *from class left join book on class.card = book.card;

show index from class;
show index from book;
-- 添加索引优化
alter table book
  add index Y (card);
-- 再次执行。可以看到第二行 type列，变为ref，rows列优化也比较明显。这是由于左连接特性决定的，left join 从右表搜索行，左边一定要有，所以右表是关键点，一定要在右表建立索引。
explain select *
        from class
          left join book on class.card = book.card;

-- 删除右边索引 并且现在加索引在左表
drop index Y
on book;

show index from book;

alter table class
  add index left_table_index (card);

drop index left_table_index on class;
-- 再次执行
explain select *
        from class
          right join book on class.card = book.card;

alter table book
    add index Y (card);

show index from class;
show index from book;


#  todo  p33
CREATE TABLE IF NOT EXISTS `phone` (
  `phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `card`    INT(10) UNSIGNED NOT NULL
)
  ENGINE = INNODB;

INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));

-- 验证数据
select * from phone;

show index in class;
-- 删除 class 索引
drop index Y on class;
--
show index from class;
-- 三表查询
select *
from class
  left join book on class.card = book.card
  left join phone on book.card = phone.card;
explain  select *
from class
  left join book on class.card = book.card
  left join phone on book.card = phone.card;

show index in phone;
show index in book;
-- 建立索引，进行优化
alter table phone add  index z (card);
alter table book add index  Y(card);
-- 再次执行
explain  select *
         from class
           left join book on class.card = book.card
           left join phone on book.card = phone.card;



-- todo p34
CREATE TABLE staffs(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
  `age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
  `pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
  `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
-- 添加索引
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
-- 验证数据
select * from staffs;
show index  from staffs;
-- 建立的索引是 name-age-POS

-- 验证：全值匹配我最爱
explain select * from staffs where name = 'July';
explain select * from staffs where name = 'July' and  age=25;
explain select * from staffs where name = 'July' and  age=23 and pos='dev';

-- 验证：最左前缀要遵守。 虽然中间断了，但是带头大哥还在。所以：ref列，只有一个const
explain select * from staffs where name = 'July' and  pos='dev';

-- 验证：带头大哥不能死，中间兄弟不能断。可以理解为楼梯一楼二楼三楼，没有一楼怎么直接去二楼三楼
explain select * from staffs where   age=25 and pos='dev';
explain select * from staffs where  age=24;
explain select * from staffs where  pos='dev';

-- 验证：不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
explain select * from staffs where name = 'July';
explain select * from staffs where left(name,4) = 'July';

-- 验证：.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
explain select  name ,age,pos from staffs where age != 22;
explain select  name ,age,pos from staffs where age <> 22;
explain select name ,age,pos from staffs where name = 'July' and add_time < 2021-12-25;

-- like 失效导致全盘扫描
explain select * from staffs where name like '%July';
explain select * from staffs where name like '%July%';
explain select * from staffs where name like 'July%';

-- 字符串不加单引号索引失效
explain select name ,age,pos from staffs where name = '2000';
explain select name ,age,pos,add_time from staffs where name = 2000;

-- todo p40
CREATE TABLE `tbl_user`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `age`INT(11) DEFAULT NULL,
  `email` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

-- 开启慢sql记录
set slow_query_log=ON;
-- 查看是否开启了 慢sql日志 ON开启，OFF关闭
show variables like 'slow_query%';
-- 查看 多少秒，被记录
show variables like 'long_query%';
-- 设置 多少秒
set long_query_time=5;


show profiles;
-- 建立索引之前
explain select  name ,age from tbl_user where name like '%aa%';
explain  select  id from tbl_user where  name like '%aa%';
explain select  name  from tbl_user where name like '%aa%';
explain select  age  from tbl_user where name like '%aa%';
explain  select  id ,name from tbl_user where  name like '%aa%';
explain  select  id ,name,age from tbl_user where  name like '%aa%';
explain  select  name,age from tbl_user where  name like '%aa%';

explain  select * from tbl_user where  name like '%aa%';
explain  select  id ,name,age, email from tbl_user where  name like '%aa%';
-- 建立索引
create index idx_user_nameAge on tbl_user(name,age);


-- todo p44
create table test03(
  id int primary key not null auto_increment,
  c1 char(10),
  c2 char(10),
  c3 char(10),
  c4 char(10),
  c5 char(10));

insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

-- 验证数据
select * from test03;
-- 建立索引
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
-- 查看索引
show index from test03;
-- 建立的索引是1234,分析一下能不能用到索引，用到几个
explain select * from test03 where c1='a1' and  c2= 'a2' and c4='a4'and c3='a3';
explain select * from test03 where c1='a1' and  c2= 'a2' and c3='a3'and c4='a4';
-- c3范围之后全失效
explain select * from test03 where c1='a1' and  c2= 'a2' and c3>'a3'and c4='a4';
-- c4 虽然是范围，但是c4之后没了，所以全会用到索引
explain select * from test03 where c1='a1' and  c2= 'a2' and c4>'a4'and c3='a3';
-- 用到了c1 c2 索引不仅仅用于查找还有排序，所以c3也用到了
explain select * from test03 where c1='a1' and  c2= 'a2' and c4='a4' order by c3;
-- 和上面一条语句一样，key_len
explain select * from test03 where c1='a1' and  c2= 'a2'  order by c3;
-- 出现了 extra：using filesort 中间兄弟不能断
explain select * from test03 where c1='a1' and  c2= 'a2'  order by c4;
-- 只有c1用到了索引，但是c2，c3用于排序，没有出现file sort
explain select * from test03 where c1='a1'   order by c2,c3;
-- 对比上条语句，这条语句出现了 using file sort  原因是，顺序不一致
explain select * from test03 where c1='a1'   order by c3,c2;
-- 下面两条对比学习
explain select * from test03 where c1='a1' and  c2= 'a2'  order by c2,c3;
-- 字段c1 c2 用于索引，c2 c3 用于排序，不会出现 using file sort
explain select * from test03 where c1='a1' and  c2= 'a2' and c5='a5' order by c2,c3;

-- 注意这条语句，没有出现 using file sort
explain select * from test03 where c1='a1' and  c2= 'a2' and c5='a5' order by c3,c2;
-- 用到了索引一个
explain select * from test03 where c1='a1' and  c4= 'a4'  order by c2,c3;
-- 下面这个语句， using file sort
explain select * from test03 where c1='a1' and  c4= 'a4'  order by c3,c2;

-- todo p48
create table tblA(
  #id int primary key not null auto_increment,
  age int,
  birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());

-- 建立索引
create index idx_A_ageBirth on tblA(age, birth);
show index in tblA;

select * from tblA;
-- 案例 ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
-- age birth 一共有四种情况。 order by 。只有age开头的，执行之后都是：type 列是index
explain select age ,birth from tblA where age>20 order by age;
explain select * from tblA where age>20 order by age,birth;

-- 产生了 using file sort
explain select * from tblA where age>20 order by birth;
explain select * from tblA where age>20 order by birth,age;
-- 查看慢查询是否开启 on代表开启
show variables like '%slow_query_log%';

set global slow_query_log_file='D:/log/mysql/slow.log';
-- mysql 默认大于十秒的是慢sql ，查看
show variables like '%long_query_time%';
-- 设置3秒为慢sql
set  long_query_time =3;
select  sleep(4);
-- 查看系统中有多少慢sql被记录
show global status like '%Slow_queries%';
-- Mysql 提供了慢sql分析命令
-- 开启mysql 此功能
SET profiling = 1;
-- 执行一条语句之后，执行下列语句。
show profiles;
-- 用法还是很多的，可以找相关博客进行学习。
SHOW PROFILE CPU FOR QUERY 108;

-- mysql 日志分析工具
# mysqldumpslow --help;


-- todo p51
select * from tblA where age>20 order by age,birth;
select * from test03 where c1='a1' and  c4= 'a4'  order by c3,c2;

-- 默认是关闭的 off
show variables like 'profiling';
-- 开启
set profiling = on;

show profiles;

-- todo p54
create table mylock (
  id int not null primary key auto_increment,
  name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

select * from mylock;
-- 语法  lock tables table1 read（write),table2 read(write)
-- 为表加读锁
lock tables mylock read ;
-- 查看有没有锁
show open tables ;
-- 释放锁
unlock tables ;


show open tables ;
show  status  like 'table_locks%';
-- todo p57
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');


-- 验证数据
select * from test_innodb_lock;
-- 建立索引
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
-- 自动提交关闭，记得开启
SET autocommit=0;

荐书

高性能mysql