# Mysql笔记

# 1. mysql重点

# 1.1 知识结构

# 1.2 优化

# 1. 如何定位慢查询？

# 使用MySQL自带慢日志

慢查询日志将记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
开启慢查询日志。在MySQL的配置文件（/etc/my.cnf）中配置：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕后，重启MySQL服务器。慢日志文件路径：/var/lib/mysql/localhost-slow.log

# 2. 慢SQL语句如何分析？

# 使用EXPLAIN 或 DESC命令

直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

possible_key：当前sql可能会使用到的索引

key：当前sql实际命中的索引（查看是否可能会命中索引）

key_len：索引占用的大小（查看是否可能会命中索引）

Extra：额外的优化建议

Using where; Using Index 查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据

Using index condition 查找使用了索引，但是需要回表查询数据

type：这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all

Using where; Using Index	查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
Using index condition	查找使用了索引，但是需要回表查询数据

具体可参考后附「详解Explain命令」

# 分析步骤

通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）

通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描

通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

# 3. MYSQL支持哪些存储引擎, 区别？

特性	MyISAM	InnoDB	MEMORY
事务安全	不支持	支持	不支持
锁机制	表锁	表锁/行锁	表锁
外键	不支持	支持	不支持

InnoDB存储引擎是mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁
MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多
Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多

# 4. 索引咋用？

**索引（index）**是帮助MySQL高效获取数据的数据结构(有序)。

提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

MySQL的InnoDB引擎采用B+树的数据结构来存储索引。特点：

阶数更多，路径更短

磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据

B+树便于扫库和区间查询，叶子节点是一个双向链表

# 聚簇索引、非聚簇索引

分类	含义	特点
聚簇索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
非聚簇索引（二级索引）(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引规则:

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表。

覆盖索引

定义：如果查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到，就称这次索引查询为覆盖索引 。它是索引的一种方式。

使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

Mysql超大分页处理

优化思路: 一般分页查询时，通过创建 覆盖索引 能够较好提高性能，可以通过覆盖索引+子查询的方式进行优化。

select *
from tb_sku t,
	 (select id from tb_sku order by id limit 9000000,10) a
where t.id = a.id;

# 索引规则，索引咋用？

针对于数据量较大，且查询频繁的表建立索引。

针对于常作为查询条件（where）、排序（order by）、**分组（group by）**操作的字段建立索引。

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

若索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它会更好确定哪个索引最有效用于查询。

# 什么时候索引会失效？

违反最左前缀法则

范围查询右边的列，不能使用索引

不要在索引列上进行运算操作，索引将失效

字符串不加单引号，造成索引失效。(类型转换)

以%开头的Like模糊查询，索引失效

# 5. mysql怎么优化

表的设计优化，数据类型的选择

索引优化，索引创建原则

sql语句优化，避免索引失效，避免使用select * ….

主从复制、读写分离，不让数据的写入，影响读操作

分库分表

# 1.3 详解Explain命令

查询结构中有12列，理解每一列的含义，对理解执行计划至关重要，下面进行说明。

SELECT识别符，这是SELECT的查询序列号。

select_type

SELECT类型,可以为以下任何一种:

SIMPLE:简单SELECT(不使用UNION或子查询)

PRIMARY:最外面的SELECT

UNION:UNION中的第二个或后面的SELECT语句

DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询

UNION RESULT:UNION 的结果

SUBQUERY:子查询中的第一个SELECT

DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询

DERIVED:导出表的SELECT(FROM子句的子查询)

table

输出的行所引用的表

partitions

如果查询是基于分区表的话，显示查询将访问的分区。

type

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:

system:表仅有一行(=系统表)。这是const联接类型的一个特例。

const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!

eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。

ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。

ref_or_:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。

index_merge:该联接类型表示使用了索引合并优化方法。

unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

range:只检索给定范围的行,使用一个索引来选择行。

index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。

ALL:对于每个来自于先前的表的行组合,进行完整的表扫描，说明查询就需要优化了。

一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys

指出MySQL能使用哪个索引在该表中找到行

key

显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。

key_len

显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。在不损失精确性的情况下，长度越短越好

ref

显示使用哪个列或常数与key一起从表中选择行。

rows

显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。

filtered

显示了通过条件过滤出的行数的百分比估计值。

Extra

该列包含MySQL解决查询的详细信息

Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。

Select tables optimized away MySQL根本没有遍历表或索引就返回数据了，表示已经优化到不能再优化了

Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。

range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。

Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行，说明查询就需要优化了。

Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果，说明查询就需要优化了。

Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。

Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。

Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

# 2. Sql经典50题

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数	

select student.*,s1.s_score `课程1`,s2.s_score `课程2`  from student 
inner join score s1 on student.s_id = s1.s_id and s1.c_id = '01'
inner join score s2 on s1.s_id = s2.s_id and s2.c_id = '02'
where s1.s_score > s2.s_score 

-- 或者也可以这样写（大关联）
	select a.*,b.s_score as 01_score,c.s_score as 02_score from student 		  a,score b,score c 
			where a.s_id=b.s_id 
			and a.s_id=c.s_id 
			and b.c_id='01' 
			and c.c_id='02' 
			and b.s_score>c.s_score

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select student.*,s1.s_score `课程1`,s2.s_score `课程2`  from student 
inner join score s1 on student.s_id = s1.s_id and s1.c_id = '01'
inner join score s2 on s1.s_id = s2.s_id and s2.c_id = '02'
where s1.s_score  < s2.s_score 

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select  score.s_id ,student.s_name,  AVG(score.s_score) as `平均成绩`
from score,student
WHERE score.s_id = student.s_id 
GROUP BY score.s_id 
HAVING  `平均成绩` >= 60 


-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select  student.s_id ,student.s_name  ,  case  when  AVG(score.s_score) is  null  then 0  else  ROUND(AVG(score.s_score) ,2)  end as `平均成绩`  
from student
LEFT  JOIN score on student.s_id = score.s_id
GROUP BY student.s_id 
HAVING  ( AVG(score.s_score)  < 60 or AVG(score.s_score) is null  )

-- 或者也可以这样写 
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 
	student b 
	left join score a on b.s_id = a.s_id
	GROUP BY b.s_id,b.s_name HAVING avg_score <60
	union
select a.s_id,a.s_name,0 as avg_score from 
	student a 
	where a.s_id not in (
				select distinct s_id from score);


-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select student.*, count( score.c_id ) as `选课总数`,  case when  SUM(score.s_score) is null then 0 else SUM(score.s_score)   end   as `总成绩`  
from student 
LEFT JOIN score on student.s_id = score.s_id  
GROUP BY student.s_id 

-- 6、查询"李"姓老师的数量 
select count(1)  from teacher where teacher.t_name like '李%'

-- 7、查询学过"张三"老师授课的同学的信息 
select student.* from student 
INNER JOIN score on score.s_id = student.s_id
INNER JOIN course on course.c_id = score.c_id
INNER JOIN teacher on teacher.t_id = course.t_id 
WHERE teacher.t_name = '张三' 
-- 或 
select a.* from 
	student a 
	join score b on a.s_id=b.s_id where b.c_id in(
		select c_id from course where t_id =(
			select t_id from teacher where t_name = '张三'));

-- 8、查询没学过"张三"老师授课的同学的信息 
select student.* from student
where student.s_id not in 
(
select student.s_id from student 
INNER JOIN score on score.s_id = student.s_id
INNER JOIN course on course.c_id = score.c_id
INNER JOIN teacher on teacher.t_id = course.t_id 
WHERE teacher.t_name = '张三' 
)

-- 或
select * from 
    student c 
    where c.s_id not in(
        select a.s_id from student a join score b on a.s_id=b.s_id where b.c_id in(
        select a.c_id from course a join teacher b on a.t_id = b.t_id where t_name ='张三'));

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.* ,b.* ,c.* from 
	student a,score b,score c 
	where a.s_id = b.s_id  and a.s_id = c.s_id 
	and b.c_id='01' and c.c_id='02';
	
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.* ,b.* ,c.* from 
	student a,score b,score c 
	where a.s_id = b.s_id  and a.s_id = c.s_id 
	and a.s_id in ( select  score.s_id  from score where c_id ='01' ) and a.s_id not in ( select  score.s_id  from score where c_id ='02' )    ;
	
-- 11、查询没有学全所有课程的同学的信息 
select student.* from student where student.s_id in 
(
select student.s_id from student where s_id not in (select distinct(score.s_id) from score ) 
union all 
select s_id from score GROUP BY s_id HAVING count(1) <  ( select count(1) from course) 
)

-- @wendiepei的写法   这个方法比我更优 
select s.* from student s 
left join Score s1 on s1.s_id=s.s_id
group by s.s_id having count(s1.c_id)<(select count(*) from course)	

-- @k1051785839的写法
select *
from student
where s_id not in(
select s_id from score t1  
group by s_id having count(*) =(select count(distinct c_id)  from course)) 


-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
select student.* from student 
where student.s_id in (
select DISTINCT(score.s_id )  from score  
where score.c_id  in 
( select score.c_id  from score where score.s_id = '01' )  
and score.s_id != '01'
)

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
-- 这一题我当时没做出来,后来参考了网友的。
select student.* from student
where student.s_id in 
(
	select  score.s_id 
	 from score 
	 WHERE score.s_id != 01 
	GROUP BY score.s_id 
	HAVING     GROUP_CONCAT( score.c_id ORDER BY score.s_id SEPARATOR ',' )  = 
	(
	select GROUP_CONCAT( score.c_id ORDER BY score.s_id SEPARATOR ','  ) courses1 
	 from score 
	 WHERE score.s_id = 01 
	GROUP BY score.s_id
	)
)

-- @ouyang_1993的写法
SELECT
 Student.*
FROM
 Student
WHERE
 s_id IN (SELECT s_id FROM Score GROUP BY s_id HAVING COUNT(s_id) = (
    #下面的语句是找到'01'同学学习的课程数
    SELECT COUNT(c_id) FROM Score WHERE s_id = '01'
   )
 )
AND s_id NOT IN (
 #下面的语句是找到学过‘01’同学没学过的课程，有哪些同学。并排除他们
 SELECT s_id FROM Score
 WHERE c_id IN(
   #下面的语句是找到‘01’同学没学过的课程
   SELECT DISTINCT c_id FROM Score
   WHERE c_id NOT IN (
     #下面的语句是找出‘01’同学学习的课程
     SELECT c_id FROM Score WHERE s_id = '01'
    )
  ) GROUP BY s_id
) #下面的条件是排除01同学
AND s_id NOT IN ('01')

-- @k1051785839的写法
SELECT
 t3.*
FROM
 (
  SELECT
   s_id,
   group_concat(c_id ORDER BY c_id) group1
  FROM
   score
  WHERE
   s_id > '01'
  GROUP BY
   s_id
 ) t1
INNER JOIN (
 SELECT
  group_concat(c_id ORDER BY c_id) group2
 FROM
  score
 WHERE
  s_id = '01'
 GROUP BY
  s_id
) t2 ON t1.group1 = t2.group2
INNER JOIN student t3 ON t1.s_id = t3.s_id

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 
select DISTINCT student.s_name from student
where  student.s_id not in  
(
select score.s_id from score 
where score.c_id  in 
(
 select course.c_id from course,teacher where teacher.t_id = course.t_id and teacher.t_name = '张三' 
)
)

-- 答案 
select a.s_name from student a where a.s_id not in (
	select s_id from score where c_id = 
				(select c_id from course where t_id =(
					select t_id from teacher where t_name = '张三'))   );


-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
select student.* , ROUND(AVG( score.s_score ) ,1  ) `平均成绩`    from student 
inner join 
(
select score.s_id from score 
where score.s_score < 60 
GROUP BY score.s_id 
HAVING count(score.s_score)>=2 
) a  on a.s_id = student.s_id
left join score on score.s_id =a.s_id 
GROUP BY student.s_id 

-- 答案 
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from 
	student a 
	left join score b on a.s_id = b.s_id
	where a.s_id in(
			select s_id from score where s_score<60 GROUP BY  s_id having count(1)>=2)
	GROUP BY a.s_id,a.s_name

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息 
select student.* ,score.s_score `分数` from score 
left join student on score.s_id = student.s_id 
where score.s_score <60 and score.c_id='01' 
order by score.s_score desc 


-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 标准答案1 
select a.s_id,(select s_score from score where s_id=a.s_id and c_id='01') as 语文,
				(select s_score from score where s_id=a.s_id and c_id='02') as 数学,
				(select s_score from score where s_id=a.s_id and c_id='03') as 英语,
			round(avg(s_score),2) as 平均分 from score a  GROUP BY a.s_id ORDER BY 平均分 DESC;
			
-- @喝完这杯还有一箱的写法
SELECT a.s_id,MAX(CASE a.c_id WHEN '01' THEN a.s_score END ) 语文, 
MAX(CASE a.c_id WHEN '02' THEN a.s_score END ) 数学, 
MAX(CASE a.c_id WHEN '03' THEN a.s_score END ) 英语, 
avg(a.s_score),b.s_name FROM Score a JOIN Student b ON a.s_id=b.s_id GROUP BY a.s_id ORDER BY 5 DESC	


-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select  
score.c_id,
course.c_name `课程name`  ,
MAX(score.s_score) `最高分`,
MIN( score.s_score ) `最低分`,
AVG(score.s_score) `平均分`,
ROUND(100*(SUM(case when score.s_score>=60 then 1 else 0 end)/SUM(case when score.s_score then 1 else 0 end)),2) as 及格率 ,
ROUND(100*(SUM(case when score.s_score>=70 and score.s_score <= 80  then 1 else 0 end)/SUM(case when score.s_score then 1 else 0 end)),2) as 中等率,
ROUND(100*(SUM(case when score.s_score>=80 and score.s_score <= 90  then 1 else 0 end)/SUM(case when score.s_score then 1 else 0 end)),2) as 优良率,
ROUND(100*(SUM(case when score.s_score>=90 then 1 else 0 end)/SUM(case when score.s_score then 1 else 0 end)),2) as 优秀率
from score,course 
where score.c_id = course.c_id 
GROUP BY score.c_id 

-- 答案 
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
	ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
	ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
	ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
	ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name

-- 19、按各科成绩进行排序，并显示排名


(select * from (

select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='01') rank
FROM score t1 where t1.c_id='01'
order by t1.s_score desc

) t1)

union

(select * from (select 
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='02') rank
FROM score t1 where t1.c_id='02'
order by t1.s_score desc) t2)

union

(select * from (select 
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank
FROM score t1 where t1.c_id='03'
order by t1.s_score desc) t3)

← Markdown笔记设计模式笔记 →