面试MySQL相关/查询和索引

关联查询

关联查询即把多个表关联起来的查询，可以分为内连接查询和外连接查询，交叉连接等（笛卡儿积）。
1、内连接查询
1）【概念：】
    内连接查询实际上是一种任意条件的查询。使用内连接时，如果两个表的相关字段满足连接条件，就从这两个表中提取数据并组合成新的记录，也就是在内连接查询中，只有满足条件的元组才能出现在结果关系中。
2）【使用】
例如：

学生表：

学号	名字	班级
01	张三	1
02	李四	2
03	王五	2
04	李六	3

选课表：

学号	课程名
01	物理
01	生物
03	数学
04	数学
05	历史

内连接查询：

现在我想要查已经选课的学生选的什么课，以及他们的名字

SELECT 学生表.学号,学生表.名字,选课表.课程名
FROM 学生表
INNER JOIN //
选课表 ON 学生表.学号=选课表.学号

INNER JOIN运算。这是最普通的联接类型。只要在这两个表的公共字段之中有相符值，内部联接将组合两个表中的记录。
查询的结果就是：

学号	姓名	课程名
01	张三	物理
01	张三	生物
03	王五	数学
04	李六	数学

只选择了已经选课的学生，而且显示了他们的学号名字，课程名(被连接的是学号)

选择条件的分类(比较方式分为)：

1）等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
2）不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
3）自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。
例如：要求检索出学号为20210的学生的同班同学的信息，查询语句为

SELECT 学生表.学号,学生表.名字,选课表.课程名
FROM学生表
JOIN学生表AS学生表1
ON学生表.班级=学生表1.班级
WHERE学生表1.学号='20210'

学生表示主表，拿着一行学生表的班级，去学生表1中，的班级列找相同的行进行连接（条件在on 后面），join的方式可以用where代替，但是，From 表1，表2 where 表1.a = 表2.a，如果表1是100行，表2也是100行，则from的笛卡尔积就是1万行，效率较慢，因此用join较好
2、外连接
1）概念：

内连接的查询结果都是满足连接条件的元组。但有时我们也希望输出那些不满足连接条件的元组信息。比如，我们想知道每个学生的选课情况，包括已经选课的学生（这部分学生的学号在学生表中有，在选课表中也有，是满足连接条件的），也包括没有选课的学生（这部分学生的学号在学生表中有，但在选课表中没有，不满足连接条件），这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式。

外连接包括三种：
a) 左外连接查询
连接的左端的表中所有的元组都列出来，如果能在右端的表中找到匹配的元组，那么连接成功。如果在右端的表中，没能找到匹配的元组，那么对应的元组是空值（NULL）。这时，查询语句使用关键字LEFT OUTERJOIN
也就是说以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null
如：

SELECT 学生表.学号,学生表.名字,选课表.课程名
from 学生表 e
LEFT OUTER JOIN 课程表 d 
on d.学号 = e.学号;

学生表就是基准。

查询结果：

学号	姓名	课程名
01	张三	物理
01	张三	生物
02	李四	Null
03	王五	数学
04	李六	数学

b) 右外连接查询

同左外连接原理相同。
不同的是右外连接使用的关键字是RIGHT OUTER JOIN。以右边的表为基准，去匹配左边的表，左边的表若是没有则填null

SELECT *
from 学生表 e
RIGHT OUTER JOIN 课程表 d 
on d.学号 = e.学号;

查询结果:

学号	姓名	课程名	班级
01	张三	物理	1
01	张三	生物	1
03	王五	数学	2
04	李六	数学	3
05	刘斯	历史	Null

c) 全外连接

把两张表的字段都查出来，没有对应的值就显示null，但是注意：mysql是没有全外连接的(mysql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接。例如：

select 学生表.学号,学生表.名字,选课表.课程名
     FROM 学生表 e 
     left JOIN 选课表 d
     ON 学生表.学号 = 选课表.学号
UNION
select 学生表.学号,学生表.名字,选课表.课程名
     FROM 学生表  e 
     RIGHT JOIN 选课表 d //选课表为基准
     ON 学生.学号 = 选课表.学号;

子查询

子查询:意为在SELECT查询语句中嵌套另外的查询语句
如：

select 学生表.名字
From 学生表
Where 学生表.学号 IN(
select 学生表.学号 FROM 选课表 Where 课程表.课程名称="数学"
)

问题1、 如果A表不为空，B表可能为空，使用关联查询还是子查询，为什么

个人觉的是，子查询。子查询可以启到过滤筛选的功能。如果B为空，可以直接过滤掉。而采用关联查询，还要进行数据库表字段的比较，浪费时间。

索引

1、概念：

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

索引分单列索引和组合索引。
【单列索引：】即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
【组合索引：】即一个索引包含多个列。

2、创建索引

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

1)普通索引
创建索引:
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构(添加索引):

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

删除索引的语法

DROP INDEX [indexName] ON mytable; 

2)唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);  

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list);
 添加普通索引，索引值可出现多次。
 
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
该语句指定了索引为 FULLTEXT ，用于全文索引。

我觉得并不是所以的表都需要去建立索引，对于一些业务数据，可能量比较大了，查询数据已经有了一点压力，那么最简单、快速的办法就是建立合适的索引，但是有些业务可能表里就没多少数据，或者表的使用频率非常不高的情况下是没必要必须要去做索引的。

问题：如何防止你的索引失效

答案

1.使用多列作为索引，则需要遵循最左前缀匹配原则（查询从索引的最左前列开始并且不跳过索引中的列）
2.不再索引列上做任何操作，例如（计算，函数，（自动 or 手动的类型转换）），会导致索引失效而转向全表扫描
3.尽量使用覆盖索引（之访问索引列的查询），减少select *,覆盖索引能减少回表次数；
4.mysql再使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
5.like以通配符开头（%abc） mysql索引会失效变成全表扫描的操作；
6.字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）

表结构：

CREATE TABLE `user` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL,
  `name` varchar(5) NOT NULL,
  `age` tinyint(2) unsigned zerofill NOT NULL,
  `sex` char(1) NOT NULL,
  `mobile` char(12) NOT NULL DEFAULT '',
  `address` char(120) DEFAULT NULL,
  `height` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_createtime` (`create_time`) USING BTREE,
  KEY `idx_name_age_sex` (`name`,`sex`,`age`) USING BTREE,
  KEY `idx_ height` (`height`) USING BTREE,
  KEY `idx_address` (`address`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;

1、使用多列作为索引，则需要遵循最左前缀匹配原则（查询从索引的最左前列开始并且不跳过索引中的列）
例子：
最左匹配原则，就是对于组合索引来说，它的一个索引的顺序是从左往右依次进行比较的，

SELECT * FROM `user` WHERE sex = '男' AND age = 22 AND `name` = 'Fan';

建立索引是KEY idx_name_age_sex (name,sex,age) USING BTREE, 它其实跟查找顺序WHERE sex = '男' AND age = 22 AND name = 'Fan';是没有任何关系的
这句SQL语句中，name 走索引，接下来回去找 age ，如果在结果条件中没有 age 那么后面的 sex 也将不走索引。会导致索引失效

2、不再索引列上做任何操作，例如（计算，函数，（自动 or 手动的类型转换）），会导致索引失效而转向全表扫描
例子：

SELECT * FROM `user` WHERE age - 1 = 20
SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';
SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';

如果你对列进行了（+，-，*，/，!）、函数，or运算， 那么都将不会走索引。

3、尽量使用覆盖索引（只访问索引列的查询），减少select *,覆盖索引能减少回表次数

常见的方法是：将被查询的字段，建立到联合索引里去。
1、如实现：select id,age from user where age = 10;
explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B+树即可查询到相应的结果，这样就实现了覆盖索引

4、mysql再使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

SELECT * FROM `user` WHERE `name` != 'Fan';

5、like以通配符开头（%abc） mysql索引会失效变成全表扫描的操作

SELECT * FROM `user` WHERE `name` LIKE '%冰';

这个我相信大家都明白，模糊搜索如果你前缀也进行模糊搜索，那么不会走索引

6、字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）

SELECT * FROM `user` WHERE `name` = Fan;