一. 索引

SQL性能分析

创建一张表：

  1. CREATE TABLE tb_user(
  2.     id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  3.     NAME VARCHAR(50) NOT NULL COMMENT '用户名',
  4.     phone VARCHAR(11) NOT NULL COMMENT '手机号',
  5.     email VARCHAR(100) COMMENT '邮箱',
  6.     profession VARCHAR(11) COMMENT '专业',
  7.     age TINYINT UNSIGNED COMMENT '年龄', 
  8.     gender CHAR(1) COMMENT '性别 , 1: 男, 2: 女', 
  9.   STATUS CHAR(1) COMMENT '状态', 
  10.     createtime DATETIME COMMENT '创建时间'
  11. ) COMMENT '系统用户表';

插入几条数据：

  1. INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
  3. INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
  5. INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00'); 
  7. INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00'); 
  9. INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00'); 
  11. INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00'); 
  13. INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
  15. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00'); 
  17. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00'); 
  19. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动 化', 27, '1', '2', '2001-08-16 00:00:00'); 
  21. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工 程', 27, '1', '0', '2001-06-12 00:00:00'); 
  23. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00'); 
  25. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00'); 
  27. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00'); 
  29. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00'); 
  31. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00'); 
  33. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00'); 
  35. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00'); 
  37. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
  39. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00'); 
  41. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00'); 
  43. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00'); 
  45. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00'); 
  47. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

查询数据表中的索引

  1. show index from tb_user;

创建索引：

  1. # 为name字段创建索引
  2. CREATE INDEX idx_user_name ON tb_user(name);
  4. # 为phone字段创建唯一索引
  5. CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone); 
  7. # 为profession、age、status创建联合索引
  8. CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
  10. # 为email建立合适的索引来提升查询效率
  11. CREATE INDEX idx_email ON tb_user(email);

其他索引语法：

  1. DELETE FROM tb_user WHERE 1=1;
  3. DROP TABLE tb_user;
  6. # 创建索引
  7. CREATE INDEX idx_user_name ON tb_user(NAME);
  9. CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
  11. CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, STATUS);
  15. SHOW INDEX FROM tb_user;
  17. SHOW GLOBAL STATUS LIKE 'Com_______';
  22. SHOW VARIABLES LIKE 'slow_query_log';
  27. SELECT COUNT(*) FROM tb_user; 
  30. # 查询当前数据库支持的存储引擎
  31. SHOW ENGINES;
  32. # 查询建表语句  默认存储引擎: InnoDB
  33. SHOW CREATE TABLE tb_user;
  34. # 如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件
  35. SHOW VARIABLES LIKE 'innodb_file_per_table';
  38. show index from tb_user;    # 查询索引
  41. select * from tb_user;
  43. SELECT @@have_profiling;
  45. select @@profiling;
  47. set profiling = 1;
  49. select * from tb_user; 
  51. select * from tb_user where id = 20; 
  53. select * from tb_user where name = '白起'; 
  55. select count(*) from tb_user;
  57. show profiles;
  59. show profile for query 6;
  61. # --------------------------------------------
  63. explain select * from tb_user where id = 20;    # 主键索引
  65. EXPLAIN SELECT * FROM tb_user WHERE phone = "17799990021";    # phone 唯一索引
  67. EXPLAIN SELECT * FROM tb_user WHERE name = "典韦";    # name 非唯一普通索引
  69. explain select * from tb_user where age = 22;
  71. # ------------------- 最左前缀法则 -------------------------
  73. show index from tb_user;
  75. explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
  77. explain select * from tb_user where profession = '软件工程' and age = '31';
  79. explain select * from tb_user where profession = '软件工程';
  81. EXPLAIN SELECT * FROM tb_user WHERE age = 31 AND STATUS = '0' AND profession = '软件工程';
  83. # 索引失效
  84. explain select * from tb_user where age = 31 and status = '0';
  86. explain select * from tb_user where status = '0';
  88. explain select * from tb_user where profession = '软件工程' and status = '0';    # 部分失效
  91. # 出现范围查询> <，右侧索引失效
  92. explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
  95. # 
  96. explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';
  98. # 
  99. EXPLAIN select * from tb_user where substring(phone, 10, 2) = '19';
  102. # 字符串类型的字段，加单引号与不加单引号
  103. explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
  105. explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;
  107. # 模糊查询
  108. explain select * from tb_user where profession like '软件%';
  110. explain select * from tb_user where profession like '%工程';
  112. explain select * from tb_user where profession like '%工%';
  114. # or连接
  115. explain select * from tb_user where id = 10 or age = 23;
  117. explain select * from tb_user where phone = '17799990017' or age = 23;
  119. show index from tb_user;
  121. create index idx_user_age on tb_user(age);
  123. drop index idx_user_age on tb_user;
  126. # 数据分布影响
  127. explain select * from tb_user where phone >= '17799990005';
  129. EXPLAIN select * from tb_user where phone >= '17799990015'; 
  132. explain select * from tb_user where profession is null;
  134. explain select * from tb_user where profession is not null; 
  136. update tb_user set profession = null where name = '姜子牙';
  138. # --------------------SQL提示---------------------
  140. create index idx_user_pro on tb_user(profession);
  142. SHOW INDEX FROM tb_user; 
  144. EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程';
  146. explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
  148. explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
  150. explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
  153. # --------------------覆盖索引-----------------------
  155. explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0';
  157. explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0';
  159. explain select id,profession,age, status, name from tb_user where profession = '软 件工程' and age = 31 and status = '0';
  161. explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
  165. # -------------------单列索引与联合索引-----------------------------------
  167. explain select id,phone,name from tb_user where phone = '17799990022' and name = '后羿';
  169. create unique index id_user_phone_name on tb_user(phone, name);
  171. EXPLAIN SELECT id,phone,NAME FROM tb_user use index(id_user_phone_name) WHERE phone = '17799990022' AND NAME = '后羿';

二. 常见面试题

2.1 主键索引和非主键索引的区别？

  1. 主键索引是针对于表中的主键创建的索引，默认自动创建，且只能有一个；而非主键索引又分为普通索引、唯一索引和全文索引，它们都可以有多个；
  2. 主键索引中，将数据与索引放到了一块，索引结构的叶子节点中保存了每行的行数据；普通索引，即二级索引将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键；
  3. 通过主键索引查找数据时，直接查找可得到行记录；而通过普通索引查找时，先要在普通索引中找到对应的主键值，再通过主键值去主键索引中得到对应的行记录，即回表查询。

2.2 建索引时有没有什么技巧？

  1. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引；
  2. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率就越高；
  3. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率；
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引；
  5. 建立有必要的索引，索引并不是多多益善，索引越多，维护索引的代价越大 ，并且还占空间。

2.3 谈谈MySQL中的乐观锁与悲观锁？

首先答什么是乐观锁和悲观锁：
悲观锁，就是对数据的处理持悲观态度，总认为会发生并发冲突，即线程安全问题，在获取和修改数据时，总会认为别人会修改数据，所以在整个数据处理过程中，需要将数据锁定，严禁其他人访问。
乐观锁，就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。如果发现冲突了，则返回错误信息给用户，让用户自已决定如何操作。
MySQL中的悲观锁有共享锁（读锁）和排他锁（写锁），

  • 共享锁简称S锁，就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是其他事务只能读不能修改；
  • 排他锁简称X锁，它允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
  • 总结一下就是如果一个事务A获取了该行的共享锁，那事务B也可以获取该行的共享锁，但事务B不能得到该行的排他锁；而如果一个事务获取到了某一行数据的排他锁，那么它可以对数据进行写操作，而其他事务不能获取该行数据的共享锁和排他锁。
  • 加共享锁：
    • SELECT ... LOCK IN SHARE MODE
  • 加排他锁：
    • 自动加锁：INSERT...、UPDATE...、DELETE...
    • 手动加锁：SELECT ... FOR UPDATE
  • 注意：SELECT ...不加任何锁；

MySQL中的乐观锁需要开发者自己实现，没有自带的。在对表中的数据执行更新操作时，先给数据表加一个版本version字段，每操作一次，就把该条记录的版本号加1。也就是在更新前会先去查询出那条记录，获取出version字段。执行更新操作时，判断此时version字段的值是否跟刚才查询出的version字段的值相等，有两种情况：

  • 如果相等，说明没有其他线程来更改本行记录，可以执行更新操作，并将version的值加1；
  • 如果不相等，说明从查询到执行更新操作这段时间内，已经有其他线程来操作本行记录了，则放弃更新操作。

三. SQL练习

  1. insert into student (stu_id, stu_name, birth, sex) values('0001' , '悟空' , '1989-01-01' , '男');
  2. insert into student (stu_id, stu_name, birth, sex) values('0002' , '悟净' , '1990-05-21' , '男');
  3. insert into student (stu_id, stu_name, birth, sex) values('0003' , '嫦娥' , '1995-12-12' , '女');
  4. insert into student (stu_id, stu_name, birth, sex) values('0004' , '马云' , '1998-06-21' , '男');
  5. insert into student (stu_id, stu_name, birth, sex) values('0005' , '秋雅' , '1999-08-15' , '女');
  8. insert into score (stu_id, c_id, score) values ('0001', '01' , 80);
  9. insert into score (stu_id, c_id, score) values ('0001', '02' , 90);
  10. insert into score (stu_id, c_id, score) values ('0001', '03' , 95);
  11. insert into score (stu_id, c_id, score) values ('0002', '01' , 78);
  12. insert into score (stu_id, c_id, score) values ('0002', '02' , 86);
  13. insert into score (stu_id, c_id, score) values ('0002', '03' , 93);
  14. insert into score (stu_id, c_id, score) values ('0003', '01' , 85);
  15. insert into score (stu_id, c_id, score) values ('0003', '02' , 92);
  16. insert into score (stu_id, c_id, score) values ('0003', '03' , 91);
  17. insert into score (stu_id, c_id, score) values ('0004', '01' , 78);
  18. insert into score (stu_id, c_id, score) values ('0004', '02' , 86);
  19. insert into score (stu_id, c_id, score) values ('0004', '03' , 84);
  20. insert into score (stu_id, c_id, score) values ('0005', '01' , 80);
  21. insert into score (stu_id, c_id, score) values ('0005', '02' , 90);
  22. insert into score (stu_id, c_id, score) values ('0005', '03' , 92);
  25. insert into course (c_id, c_name, t_id) values ('01', '语文', '001');
  26. insert into course (c_id, c_name, t_id) values ('02', '数学', '002');
  27. insert into course (c_id, c_name, t_id) values ('03', '英语', '003');
  30. insert into teacher (t_id, t_name) values ('001', '孔子');
  31. insert into teacher (t_id, t_name) values ('002', '葛军');
  32. insert into teacher (t_id, t_name) values ('003', '朱伟');

常见面试题：

  1. # 查询姓“马”的学生名单
  2. SELECT * from student where stu_name like '马%';
  4. # 查询姓“朱”的老师的个数
  5. select count(t_name) from teacher where t_name like '朱%';
  7. # 查询课程编号为‘02’的总成绩
  8. select SUM(score) from score where c_id = '02';
  10. # 查询每门课程成绩的最高分和最低分，以如下的形式显示：课程号，最高分，最低分
  11. select c_id as '课程号', max(score) as '最高分', min(score) as '最低分' from score GROUP BY c_id;
  13. # 查询男生、女生人数
  14. select sex, count(*) from student GROUP BY sex;
  16. # 查询平均成绩大于85分学生的学号和平均成绩
  17. SELECT stu_id, AVG(score) FROM score GROUP BY stu_id HAVING AVG(score) > 85;

image.png

四. 查漏

inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。

关键字: on
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
image.png
inner join 会把值为null的行给过滤掉；
left join 则不会过滤掉值为null的行；