MySQL基础考点2

1.MySQL真题感受1下：

    有A（id,sex,par,c1,c2）,B(id,age,c1,c2)两张表，其中A.id和B.id关联，现在要求写出一条SQL语句，将B中age>50的记录的c1,c2更新到A表中统一记录中的c1,c2字段中。

    答：UPDATE A,B SET A.c1 = B.c1,A.c2 = B.c2 WHERE A.id = B.id and B.age > 50;

    或者UPDATE A INNER JOIN B ON A.id = B.id SET A.c1 = B.c1,A.c2 = B.c2 WHERE B.age > 50;

    有两张表，team(id,name), match(id, host_team_id, guest_team_id, match_time, result),其中，match表的host_team_id,guest_team_id都和team表的id关联，查询2006-6-1到2006-7-1之间举行的所有比赛，结果输出：拜仁 2：0 不莱梅 2006-6-21.

    答：SELECT t1.name, result, t2.name, match_time FROM match as m  LEFT JOIN team as t1 ON m.host_team_id=t1.id,LEFT JOIN team as t2 ON m.guest_team_id=t2.id WHERE m.match_time between '2006-6-1' and '2006-7-1';

    解题方法：首先要搞清楚表的结构和多表之间的关系，根据想要的结果思考使用哪种关联方式，通常把要查询的列先写出来，然后分析这些列都属于哪些表，才考虑使用关联查询。

2.六种关联查询

    交叉连接（CROSS JOIN）,笛卡儿积，没啥意义

    全连接（FULL JOIN）:MySQL不支持全连接，可以使用LEFT JOIN和UNION和RIGHT JOIN联合使用

    SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id=B.id;

    内连接（INNER JOIN 缩写JOIN）

    SELECT * FROM A,B WHERE A.id = B.id;

    SELECT * FROM A INNER JOIN B ON A.id = B.id;

    多表中同时符合某种条件的数据记录的集合

    等值连接: ON A.id = B.id

    不等值连接： ON A.id > B.id

    自连接：SELECT *FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid

    左外连接（LEFT JOIN）：LEFT OUTER JOIN,以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN

    右外连接（RIGHT JOIN）：RIGHT OUTER JOIN,以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

    联合查询（UNION与UNION ALL）：SELECT * FROM A UNION SELECT * FROM B UNION ...

    就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并

    如果使用UNION ALL,不会合并重复的记录行

3.嵌套查询(不建议使用，效率不好把控)

    用一条SQL语句的结果作为另外一条SQL语句的条件

    SELECT * FROM A WHERE id IN (SELECT id FROM B);

4.MySQL的高可扩展和高可用

    真题：简述MySQL分表操作和分区的工作原理，分别说说分区和分表的使用场景和各自优缺点。

    分区表的原理（物理层面的拆分）：对用户而言，分区表是一个独立的逻辑表，但是底层MySQL将其分成了多个物理子表，这对用户来说是透明的，每一个分区表都会使用一个独立的表文件。

    创建表的时候使用partition by子句定义每个分区存放的数据，执行查询时，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询只需要查询所需数据在的分区即可。

    分区的主要目的是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关的数据存放在一起，而且如果想一次性删除整个分区的数据也很方便。

使用场景：

    1.表非常大的时候，无法全部存在内存，或者只在表的最后有热点数据，其他都是历史数据

    2.分区表的数据更易维护，可以对独立的分区进行独立的操作。

    3.分区表的数据可以分布在不同的机器上，从而高效使用资源。

    4.可以使用分区表来避免某些特殊的瓶颈。

    5.可以备份和恢复独立的分区。

限制：

    1.一个表最多只能有1024个分区。

    2. 5.1版本中，分区表表达式必须是整数，5.5可以使用列分区。

    3.分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来。

    4.分区表中无法使用外键约束

    5.需要对现有表的结构进行修改

    6.所有分区都必须使用相同的存储引擎

    7.分区函数中可以使用的函数和表达式会有一些限制

    8.某些存储引擎不支持分区

    9.对于MyISAM的分区表，不能使用load index into cache

    10.对于MyISAM表，使用分区表时需要打开更多的文件描述符

5.分库分表的原理：通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分

    使用场景：单表记录条数达到百万到千万级别时。

    分表方式：水平、垂直分割

    水平分割：表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度

水平分割使用场景：

    1.表中的数据本身就有独立性，例如表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用

    2.需要把数据存放在多个介质上

缺点：

    1.给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作

    2.在许多数据库应用中，这种复杂性会超过它带来的优点，查询时增加读一个索引层的磁盘次数。

    垂直分表：把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中

垂直分表使用场景：

    1.如果一个表中某些列常用，而另外一些列不常用。

    2.可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数

    垂直分表缺点：管理冗余列，查询所有数据需要JOIN操作

整体分表缺点：

    有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差。

对于应用层来说，逻辑算法无疑增加开发成本

6.MySQL的复制原理及负载均衡

    复制原理：在主库上把数据更改记录到二进制日志、从库将主库的日志复制到自己的中继日志、从库读取中继日志中的事件，将其重放到从库数据中

    主从复制解决的问题：

    数据分布：随意停止或开始复制，并在不同地理位置分布数据备份

    负载均衡：降低单个服务器的压力

    高可用和故障切换；帮助应用程序避免单点失败

    升级测试：可以使用更高版本的MySQL作为从库

    答：水平分表或者分区

7.MySQL查询优化

    真题：请简述项目中优化SQL语句执行效率的方法，从哪些方面,SQL语句性能如何分析？

    解题方法：先说明如何定位低效SQL语句，然后根据SQL语句可能低效的原因做排查，先从索引着手，如果索引没问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

查找分析查询速度慢的原因

    记录慢查询日志

    分析查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用pt-query-digest工具进行分析

    使用show profile，set profiling=1；开启，服务器上执行的所有语句会检测消耗的时间，存到临时表中。

    show profiles; show profile for query 临时表ID

    使用show status,其会返回一些计数器，show global status查看服务器级别的所有计数。就可以猜测出哪些操作代价较高或者消耗时间多。

    使用show processlist,观察是否有大量线程处于不正常的状态或者特征

    使用explain,分析单条的sql语句

优化查询过程中的数据访问

    访问数据太多导致查询性能下降

    确定应用程序是否在检索大量超过需要的数据，可能是太多行或列

    确认MySQL服务器是否在分析大量不必要的数据行

8.避免使用如下SQL语句

    查询不需要的记录，使用limit解决

    多表关联返回全部列，指定A.id,A.name,B.age

    总是取出全部列，SELECT *会让优化器无法完成索引覆盖扫描的优化

    重复查询相同的数据，可以缓存数据，下次直接读取缓存

    是否在扫描额外的记录

    使用explain来进行分析，如果发现查询需要扫描大量的数据但只返回少数的行，可以通过如下技巧去优化：使用索引覆盖扫描，把所有用的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。

    改变数据库和表结构，修改数据表范式

    重写SQL语句，让优化器可以以更优的方式执行查询

    优化长难的查询语句

    MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢的多

    使用尽可能少的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。

切分查询

    将一个大的查询分为多个小的相同的查询

    比如：一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销

分解关联查询

    可以将一条关联语句分解成多条SQL来执行

    好处：让缓存的效率更高、执行单个查询可以减少锁的竞争、在应用层做关联可以更容易对数据库进行拆分、查询效率会有大幅提升、较少冗余记录的查询

    优化特定类型的查询语句

    优化count()查询

    count(*)中的*会忽略所有的列，直接统计所有列数，因此不要使用count(列名)

    MyISAM中，没有任何WHERE条件的count(*)非常快

    当有WHERE条件，MyISAM的count统计不一定比其他表引擎快

    可以使用explain查询近似值，用近似值替代count(*)、增加汇总表、使用缓存

优化关联查询

    确定ON或者USING子句的列上有索引

    确保GROUP BY和ORDER BY中只有一个表中的列，这样MySQL才有可能使用索引

优化子查询

    尽可能使用关联查询来替代

优化GROUP BY和DISTINCT

    这两种查询均可使用索引来优化，是最有效的优化方法

    关联查询中，使用标识列进行分组的效率会更高

    如果不需要ORDER BY，进行GROUP BY时使用ORDER BY NULL，MySQL不会再进行文件排序

    WITH ROLLUP超级聚合，可以挪到应用程序处理

优化LIMIT分页

    LIMIT偏移量大的时候，查询效率较低

    可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

优化UNION查询

    UNION ALL的效率高于UNION

9.MySQL安全性考察（防SQL注入）

    1.使用预处理语句（prepare）防SQL注入

    delete from user where id = ?;

    例子：delete from user where id = 1;

    GET ?id=1

    user/delete?id=1

    user/delete?id=1 or 1=1;

    delete from user where id = 1 or 1 = 1;就会删除所有的数据

    2.写入数据库的数据要进行特殊字符的转义

    3.查询错误信息不要返回给用户，将错误记录到日志

10.其他安全设置

    1.定期做数据备份

    2.不给查询用户root权限，合理分配权限

    3.关闭远程访问数据库权限

    4.修改root口令，不用默认口令，使用较复杂的口令

    5.删除多余的用户

    6.改变root用户的名称

    7.限制一般用户浏览其他库

    8.限制用户对数据文件的访问权限

最后一问：为什么使用PDO和MySQLi链接数据库会比mysql函数库更加安全？答案写在评论区。。