MySQLl面试热点，MySQL优化授课笔记.pdf-资料库

geekdong-11847298-4744302542849082798.pdf-第1页.png

第1页 / 共40页

geekdong-11847298-4744302542849082798.pdf-第2页.png

第2页 / 共40页

geekdong-11847298-4744302542849082798.pdf-第3页.png

第3页 / 共40页

geekdong-11847298-4744302542849082798.pdf-第4页.png

第4页 / 共40页

geekdong-11847298-4744302542849082798.pdf-第5页.png

第5页 / 共40页

geekdong-11847298-4744302542849082798.pdf-第6页.png

第6页 / 共40页

geekdong-11847298-4744302542849082798.pdf-第7页.png

第7页 / 共40页

geekdong-11847298-4744302542849082798.pdf-第8页.png

第8页 / 共40页

MySQL面试热点与MySQL高级特性,性能优化课程介绍 MySQL是使用最为广泛的开源数据库系统，是后端开发工程师，架构师，运维工程师，DBA，面试中几乎必定被问到的内容。本次课程以面试真题为起点,结合MySQL必问的热点技能为方向，覆盖MySQL面试相关的方方面面，梳理和总结相关知识点。通过本课程的学习，不仅可以深入了解MySQL数据库，也能助力轻松面试，获得心怡的工作机会。主要内容目标 MySQL常见基础面试热点 MySQL语句的执行顺序 MySQL内外，全连接深入存储引擎的选择和区别存储过程，函数，触发器锁问题深入事物与隔离级别深入 MySQL数据库优化问题 MySQL数据库优化方案 MySQL索引深入 MySQL索引失效的避免 MySQL其他性能优化方案 MySQL常见问题和大厂面试题解决第一章 Mysql数据库基础面试热点目标掌握数据库范式什么是范式创建表的规则，指导我们后期如何去设计自己的表。数据库表规范化的好处：减少数据的冗余。减少后期Java代码的工作量。三大范式小结范式特点第一范式原子性每列不可再拆分第二范式不产生局部依赖，每列都完全依赖于主键 , 一张表只描述一件事情第三范式不产生传递依赖，所有的列都直接依赖于主键,使用外键关联，外键都来源与其他表的主键北京市昌平区建材城西路金燕龙办公楼一层电话：400-618-9090

反三范式反3NF：为了提高数据的性能，增加冗余字段，以便提高查询性能目标 Select语句执行顺序 SQL Select语句完整的执行顺序： SQL语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中，代码按编码顺序被处理。但在 SQL语句中，第一个被处理的子句式FROM，而不是第一出现的SELECT。SQL查询处理的步骤序号： SELECT DISTINCT FROM JOIN ON WHERE GROUP BY HAVING ORDER BY LIMIT 测试脚本： CREATE TABLE customer ( customer_id VARCHAR(10) NOT NULL, city VARCHAR(10) NOT NULL, PRIMARY KEY(customer_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8; CREATE TABLE order ( order_id INT NOT NULL auto_increment, customer_id VARCHAR(10), PRIMARY KEY(order_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8; INSERT INTO customer(customer_id,city) VALUES('163','hangzhou'); INSERT INTO customer(customer_id,city) VALUES('9you','shanghai'); INSERT INTO customer(customer_id,city) VALUES('tx','hangzhou'); INSERT INTO customer(customer_id,city) VALUES('baidu','hangzhou'); INSERT INTO order(customer_id) VALUES('163'); INSERT INTO order(customer_id) VALUES('163'); INSERT INTO order(customer_id) VALUES('9you'); INSERT INTO order(customer_id) VALUES('9you'); INSERT INTO order(customer_id) VALUES('9you'); INSERT INTO order(customer_id) VALUES('tx'); INSERT INTO order(customer_id) VALUES(NULL); 查询来自杭州，并且订单数少于2的客户。北京市昌平区建材城西路金燕龙办公楼一层电话：400-618-9090

SELECT a.customer_id, COUNT(b.order_id) as total_orders FROM customer AS a LEFT JOIN order AS b ON a.customer_id = b.customer_id WHERE a.city = 'hangzhou' GROUP BY a.customer_id HAVING count(b.order_id) < 2 ORDER BY total_orders DESC; 目标内连接的介绍内连接：内连接可整合多张表，获取相关关系或者公共部分的记录数据准备 # 创建部门表 create table dept( id int primary key auto_increment, name varchar(20) ); insert into dept (name) values ('开发部'),('市场部'),('财务部'); # 创建员工表 create table employee ( id int primary key auto_increment, name varchar(10), gender char(1), -- 性别 salary double, -- 工资 join_date date, -- 入职日期 dept_id int, foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键) ); insert into employee(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013- 02-24',1); insert into employee(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010- 12-02',2); insert into employee(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08- 08',2); insert into employee(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015- 10-07',3); insert into employee(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011- 03-14',1); 笛卡尔积的介绍 SELECT 列名 FROM 左表,右表内连接分为：隐式内连接，显式内连接隐式内连接北京市昌平区建材城西路金燕龙办公楼一层电话：400-618-9090

select 列名 from 左表,右表 where 主表.主键=从表.外键显式内连接 --使用INNER JOIN ... ON语句, 可以省略INNER select 列名 from 左表 inner join 右表 on 主表.主键=从表.外键 --显式内连接与隐式内连接查询结果是一样的** select * from dept d inner join emp e on d.id = e.dept_id; 目标左外连接的介绍外连接：外连接分为两种，一种是左连接（Left JOIN）和右连接（Right JOIN) 左外连接：在内连接的基础上，保证左表中所有的记录都出现。相应记录使用NULL和它匹配。左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略 -- 需求：在部门表中增加一个销售部，需要查询所有的部门和员工，将部门表设置成左表，员工表设置成右表 select * from dept; insert into dept values(null,'销售部'); -- 使用内连接查询 select * from employee e dept d inner join on d.id = e.dept_id; -- 使用左外连接查询 select * from dept d left join employee e on d.id = e.dept_id; 目标右外连接的介绍右外连接概念：在内连接的基础上，保证右表中所有的数据都显示。左表中如果没有匹配的数据，使用NULL匹配。右连接的语法：右外连接：使用RIGHT OUTER JOIN ... ON，OUTER可以省略右连接的案例： -- 需求：在员工表中增加一个员工:'沙僧','男',6666,'2013-02-24',null select * from emp; insert into emp values(null,'沙僧','男',6666,'2013-02-24',null); -- 希望员工的信息全部显示出来 -- 使用内连接查询 select * from dept d inner join emp e on d.id = e.dept_id; -- 使用右外连接查询 select * from dept d right join emp e on d.id = e.dept_id; 目标查询左边独有的数据北京市昌平区建材城西路金燕龙办公楼一层电话：400-618-9090

查询左表独有数据作用查询A的独有数据语句 Select from tableA A Left Join tableB B on A.Key = B.Key where B.key IS NULL 示例 SELECT * from employee e LEFT JOIN department d on e.depart_id = d.id WHERE d.id IS NULL; 目标查询右表独有的数据查询右表独有数据作用查询B的独有数据语句 Select from tableA A Right Join tableB B on A.Key = B.Key where A.key IS NULL 示例 SELECT * from employee e RIGHT JOIN department d on e.depart_id = d.id WHERE e.id IS NULL; 目标全连接介绍北京市昌平区建材城西路金燕龙办公楼一层电话：400-618-9090

作用查询两个表的全部信息语句 Select from tableA A Full Outter Join tableB B on A.Key = B.Key 注:Mysql 默认不支持此种写法 Oracle支持示例 SELECT * from employee e LEFT JOIN department d on e.depart_id = d.id UNION SELECT * from employee e RIGHT JOIN department d on e.depart_id = d.id 目标查询左右表各独有的数据图示作用查询A和B各自的独有的数据语句 Select from tableA A Full Outter Join tableB B on A.Key = B.Key where A.key = null or B.key=null 示例 SELECT * from employee e LEFT JOIN department d on e.depart_id = d.id WHERE d.id is NULL UNION SELECT * from employee e RIGHT JOIN department d on e.depart_id = d.id WHERE e.depart_id is NULL 目标表的级联操作什么是级联操作在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作 ON UPDATE CASCADE -- 级联更新，主键发生更新时，外键也会更新 ON DELETE CASCADE -- 级联删除，主键发生删除时，从表关联的全部数据都会被直接删除。具体操作：删除employee表重新创建employee表，添加级联更新和级联删除北京市昌平区建材城西路金燕龙办公楼一层电话：400-618-9090

CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_id INT, -- 添加外键约束,并且添加级联更新和级联删除 CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE ); 再次添加数据到员工表和部门表 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2); 第二章存储引擎的选择目标存储引擎的概念和查看存储引擎的概念插件式存储引擎是Mysql中最重要的特性之一，用户可以根据应用的需要选择如何存储数据和索引，是否使用事物等，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎,MySQL默认支持多种存储引擎，以适应不同领域数据库的需求，用户可以通过选择不同的存储引擎，提高应用的效率，提供灵活的存储，用户也可以按照自己的需求定制和使用存储引擎。查看存储引擎 show engines; 查看当前存储引擎查看系统当前支持的存储引擎，需要使用如下命令： show variables like '%storage_engine%'; 效果如下：北京市昌平区建材城西路金燕龙办公楼一层电话：400-618-9090

可以看到：当前MySQL默认的存储引擎是InnoDB 当前MySQL正在使用的存储引擎也是InnoDB 目标存储引擎的创建和修改引入创建新表时，如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL 5.5之前的默认存储引擎是MyISAM , 5.5之后改为了InnoDB,如果需要修改存储引擎可以在核心配置文件中配置如下操作 default-storage-engine=INNODB 也可以在创建表的时候，通过增加ENGINE关键字设置新表的存储引擎，例如下面的例子： CREATE TABLE `test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM default CHARSET=utf8 CREATE TABLE `test2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 也可以把一个已经存在的表的存储引擎，修改成其他表的存储引擎，操作如下 alter table test1 ENGINE = innoDB; show create table test1 目标常见存储引擎的区别北京市昌平区建材城西路金燕龙办公楼一层电话：400-618-9090

资料库

MySQLl面试热点，MySQL优化授课笔记.pdf

相关推荐

课程资源

热门标签

最新资料