MySQL

Q：SQL分类

DDL(Data Definition Language) 数据定义语言
– 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

DML(Data Manipulation Language) 数据操作语言
– 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

DQL(Data Query Language) 数据查询语言
– 用来查询数据库中表的记录(数据)。关键字：select, where 等

DCL(Data Control Language) 数据控制语言(了解)
– 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

---

Q：什么是sql注入？如何防止sql注入？

sql注入
通过在 Web 表单中输入（恶意）SQL 语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行 SQL 语句。

举例：当执行的 sql 为 select * from user where username = "admin" or "a"="a"时，sql 语句恒成立，参数 admin 毫无意义。

防止 sql 注入的方式：
预编译语句：如，select * from user where username = ？，sql 语句语义不会发生改变，sql 语句中变量用？表示，即使传递参数时为"admin or ‘a’= ‘a’"，也会把这整体当做一个字符创去查询。

Mybatis 框架中的 mapper 方式中的 # 也能很大程度的防止 sql 注入（$无法防止 sql 注入）。

---

Q：#{}和${}的区别是什么？

（1）#{}是预编译处理，${}是字符串替换
（2）Mybatis在处理#{}时，会将sql中的#{}替换为?号，调用PreparedStatement的set方法来赋值；
（3）Mybatis在处理${}时，就是把{}替换成变量的值。
（4）使用#{}可以有效的防止SQL注入，提高系统安全性

---

Q：面试题：where 和 having 的区别？

• where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询展示出来
• where 后不可以跟聚合函数的判断，having可以进行聚合函数的判断。

Q：有哪些sql优化方法？

（1）当只要一行数据时使用 limit 1
查询时如果已知会得到一条数据，这种情况下加上 limit 1 会增加性能。因为 mysql 数据库引擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。

（2）选择正确的数据库引擎
Mysql 中有两个引擎 MyISAM 和 InnoDB，每个引擎有利有弊。
MyISAM 适用于一些大量查询的应用，但对于有大量写功能的应用不是很好。甚至你只需要update 一个字段整个表都会被锁起来。而别的进程就算是读操作也不行要等到当前 update 操作完成之后才能继续进行。另外，MyISAM 对于 select count(*)这类操作是超级快的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用会比 MyISAM 还慢，但是支持“行锁”，所以在写操作比较多的时候会比较优秀。并且，它支持很多的高级应用，例如：事务。

（3）用 not exists 代替 not in
Not exists 用到了连接能够发挥已经建立好的索引的作用，not in 不能使用索引。Not in 是最慢的方式要同每条记录比较，在数据量比较大的操作时不建议使用这种方式。

（4）对操作符的优化，尽量不采用不利于索引的操作符
如：in not in is null is not null <>等
某个字段总要拿来搜索，为其建立索引：
Mysql 中使用 alter table 语句来为表中的字段添加索引：alter table 表明 add index (字段名)；

---

Q：mysql中 in 和 exists 区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

（1）如果查询的两个表大小相当，那么用in和exists差别不大。
（2）如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
（3）not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

---

Q：创建索引的原则？

1、 对于查询频率高的字段创建索引；

索引的目的就是提升查询速度，这一点毋庸置疑。所以对于查询频率高的字段要创建索引，如：用户表的手机号。

2、 对排序、分组、联合查询频率高的字段创建索引；

3、 索引的数目不宜太多

每创建一个索引都会占用相应的物理控件；
过多的索引会导致insert、update、delete语句的执行效率降低；

4、若在实际中，需要将多个列设置索引时，可以采用多列索引

如：某个表(假设表名为Student)，存在多个字段(StudentNo, StudentName, Sex, Address, Phone, BirthDate)，其中需要对StudentNo,StudentName字段进行查询，对Sex字段进行分组，对BirthDate字段进行排序，此时可以创建多列索引

index index_name (StudentNo, StudentName, Sex, BirthDate); #index_name为索引名
在上面的语句中只创建了一个索引，但是对4个字段都赋予了索引的功能。

创建多列索引，需要遵循BTree类型，即第一列使用时，才启用索引。

在上面的创建语句中，只有mysql语句在使用到StudentNo字段时，索引才会被启用，如:

select * from Student where StudentNo = 1000; #使用到了StudentNo字段，索引被启用。
可以使用explain检测索引是否被启用，如:

explain select * from Student where StudentNo = 1000;
MySql创建索引的原则，知道这些，你再也不用担心面试了！

附：EXPLAIN列的解释

table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

5、选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

6、尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

7、尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

8、删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

Q：事务的四大特征?

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间。相互独立。
一致性：事务操作前后，数据总量不变（转账是否成功数据库总额不变）

---

Q：事务的隔离级别（了解）?

（1）概念

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

（2）存在问题

脏读：一个事务，读取到另一个事务中没有提交的数据
不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

（3）四种隔离级别

隔离级别	产生的问题
read uncommitted：读未提交	脏读、不可重复读、幻读
read committed：读已提交 （Oracle）	不可重复读、幻读
repeatable read：可重复读 （MySQL默认）	幻读
serializable：串行化	可以解决所有的问题 ，同时只有一个事务对一张表进行操作（加锁）

注意：隔离级别从小到大安全性越来越高，但是效率越来越低。所以不会选择级别最高的。

---

Q：百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

（1）所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
（2）然后删除其中无用数据（此过程需要不到两分钟）
（3）删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
（4）与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

---

Q：SQL 的 select 语句完整的执行顺序？

1、from 子句组装来自不同数据源的数据；
2、where 子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用 having 子句筛选分组；
6、计算所有的表达式；
7、select的字段；
8、使用 order by 对结果集进行排序。

---

Q：左连接和右连接的区别?

外连接

左连接（左外连接）：以左表作为基准进行查询，左表数据会全部显示出来，右表如果和左表匹配的数据则显示相应字段的数据，如果不匹配则显示为 null。

右连接（右外连接）：以右表作为基准进行查询，右表数据会全部显示出来，左表如果和右表匹配的数据则显示相应字段的数据，如果不匹配则显示为 null。

全连接
先以左表进行左外连接，再以右表进行右外连接

内连接
显示表之间有连接匹配的所有行。

---

Q：MySQL 索引的“使用”要注意什么？

1. 避免在 WHERE 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

2. 避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20 。

3. 避免在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

4. 避免在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

5. LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引。

---

Q：Mysql 存储引擎有哪些？

1.InnoDB 存储引擎
InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB 是默认的MySQL引擎。

2.MyISAM 存储引擎
MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事物。

3.MEMORY 存储引擎
MEMORY 存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

4.NDB 存储引擎
DB 存储引擎是一个集群存储引擎，类似于 Oracle 的 RAC，但它是 Share Nothing 的架构，因此能提供更高级别的高可用性和可扩展性。NDB 的特点是数据全部放在内存中，因此通过主键查找非常快。
关于 NDB，有一个问题需要注意，它的连接(join)操作是在 MySQL 数据库层完成，不是在存储引擎层完成，这意味着，复杂的 join 操作需要巨大的网络开销，查询速度会很慢。

5.Memory (Heap) 存储引擎
Memory 存储引擎（之前称为 Heap）将表中数据存放在内存中，如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。

6.Archive 存储引擎
正如其名称所示，Archive 非常适合存储归档数据，如日志信息。它只支持 INSERT 和 SELECT 操作，其设计的主要目的是提供高速的插入和压缩功能。

7.Federated 存储引擎
Federated 存储引擎不存放数据，它至少指向一台远程 MySQL 数据库服务器上的表，非常类似于 Oracle 的透明网关。

8.Maria 存储引擎
Maria 存储引擎是新开发的引擎，其设计目标是用来取代原有的 MyISAM 存储引擎，从而成为 MySQL 默认的存储引擎。

上述引擎中，InnoDB 是事务安全的存储引擎，设计上借鉴了很多 Oracle 的架构思想，一般而言，在 OLTP应用中，InnoDB 应该作为核心应用表的首先存储引擎.InnoDB 是由第三方的 Innobase Oy 公司开发，现已被Oracle 收购，创始人是Heikki Tuuri，芬兰赫尔辛基人，和著名的 Linux 创始人 Linus 是校友。

---