MySQL面试

Python 中操作 Mysql 步骤？
SQL 的 select 语句完整的执行顺序
1、from 子句组装来自不同数据源的数据；
2、where 子句基于指定的条件对记录行进行筛选；
3、group by 子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用 having 子句筛选分组；
6、计算所有的表达式；
7、select 的字段；
8、使用 order by 对结果集进行排序。
SQL 语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中，代码按
编码顺序被处理。但在 SQL 语句中，第一个被处理的子句式 FROM，而不是第一出现的 SELECT。
SQL 查询处理的步骤序号：
(1) FROM <left_table>
(2) <join_type> JOIN <right_table>
(3) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(8) SELECT
(9) DISTINCT
(9) ORDER BY <order_by_list>
(10) <TOP_specification> <select_list>
以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用
者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在
查询中指定某一个子句，将跳过相应的步骤。
逻辑查询处理阶段简介：
1、 FROM：对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表 VT1。
2、 ON：对 VT1 应用 ON 筛选器，只有那些使为真才被插入到 TV2。
3、 OUTER (JOIN):如果指定了 OUTER JOIN(相对于 CROSS JOIN 或 INNER JOIN)，保留
表中未找到匹配的行将作为外部行添加到 VT2，生成 TV3。如果 FROM 子句包含两个以上的表，
则对上一个联接生成的结果表和下一个表重复执行步骤 1 到步骤 3，直到处理完所有的表位置。
4、 WHERE：对 TV3 应用 WHERE 筛选器，只有使为 true 的行才插入 TV4。
5、 GROUP BY：按 GROUP BY 子句中的列列表对 TV4 中的行进行分组，生成 TV5。
6、 CUTE|ROLLUP：把超组插入 VT5，生成 VT6。
7、 HAVING：对 VT6 应用 HAVING 筛选器，只有使为 true 的组插入到 VT7。
8、 SELECT：处理 SELECT 列表，产生 VT8。
9、 DISTINCT：将重复的行从 VT8 中删除，产品 VT9。
10、 ORDER BY：将 VT9 中的行按 ORDER BY 子句中的列列表顺序，生成一个游标(VC10)。
11、 TOP：从 VC10 的开始处选择指定数量或比例的行，生成表 TV11，并返回给调用者。
where 子句中的条件书写顺序
说一下 Mysql 数据库存储的原理？
储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。存储过程通常有以下优点：
1、存储过程能实现较快的执行速度
2、存储过程允许标准组件是编程。
3、存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
4、存储过程可被作为一种安全机制来充分利用。
5、存储过程能够减少网络流量
事务的特性？
1、原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执
行。
2、一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相
一致。
3、隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透
明的。
4、持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即
使数据库出现故障
数据库索引？
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。
索引的实现通常使用 B_TREE。B_TREE 索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需
要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。
数据库怎么优化查询效率？
1、储存引擎选择：如果数据表需要事务处理，应该考虑使用 InnoDB，因为它完全符合 ACID 特性。
如果不需要事务处理，使用默认存储引擎 MyISAM 是比较明智的
2、分表分库，主从。
3、对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索
引
4、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全
表扫描
5、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫
描
6、应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，
将导致引擎放弃使用索引而进行全表扫描
7、Update 语句，如果只更改 1、2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的
性能消耗，同时带来大量日志
8、对于多张大数据量（这里几百条就算大了）的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，
性能很差。
Mysql 集群的优缺点？
优点：
99.999%的高可用性
快速的自动失效切换
灵活的分布式体系结构，没有单点故障
高吞吐量和低延迟
可扩展性强，支持在线扩容
缺点：
存在很多限制，比如：不支持外键
部署、管理、配置很复杂
占用磁盘空间大、内存大
备份和恢复不方便
重启的时候，数据节点将数据 load 到内存需要很长的时间
你用的 Mysql 是哪个引擎，各引擎之间有什么区别？
主要 MyISAM 与 InnoDB 两个引擎，其主要区别如下：
InnoDB 支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一
些列增删改中只要哪个出错还可以回滚还原，而 MyISAM 就不可以了；
MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用；
InnoDB 支持外键，MyISAM 不支持；
MyISAM 是默认引擎，InnoDB 需要指定；
InnoDB 不支持 FULLTEXT 类型的索引；
InnoDB 中不保存表的行数，如 select count() from table 时，InnoDB；需要扫描一遍整个表来
计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。注意的是，当 count()语句包含
where 条件时 MyISAM 也需要扫描整个表；
对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字
段一起建立联合索引；清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建
表；
InnoDB 支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'
数据库的优化？
1.优化索引、SQL 语句、分析慢查询；
2.设计表的时候严格根据数据库的设计范式来设计数据库；
3.使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘 IO
4.优化硬件；采用 SSD，使用磁盘队列技术(RAID0,RAID1,RDID5)等
5.采用 MySQL 内部自带的表分区技术，把数据分层不同的文件，能够提高磁盘的读取效率；
6.垂直分表；把一些不经常读的数据放在一张表里，节约磁盘 I/O；
7.主从分离读写；采用主从复制把数据库的读操作和写入操作分离开来；
8.分库分表分机器（数据量特别大），主要的的原理就是数据路由；
9.选择合适的表引擎，参数上的优化
10.进行架构级别的缓存，静态化和分布式；
11.不采用全文索引；
12.采用更快的存储方式，例如 NoSQL 存储经常访问的数据**。
Mysql 数据库如何分区、分表？
分表可以通过三种方式：Mysql 集群、自定义规则和 merge 存储引擎。
分区有四类：
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个
值来进行选择。
HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的
这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。
KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器
提供其自身的哈希函数。必须有一列或多列包含整数值
如何对查询命令进行优化？
a. 应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索。
b. 应尽量避免在 where 子句中对字段进行 null 值判断，避免使用!=或<>操作符，避免使用 or 
连接条件，或在 where 子句中使用参数、对字段进行表达式或函数操作，否则会导致权标扫描
c. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无
法正确使用索引。
d. 使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为
条件时才能保证系统使用该索引，否则该索引将不会被使用。
e. 很多时候可考虑用 exists 代替 in。
f. 尽量使用数字型字段。
g. 尽可能的使用 varchar/nvarchar 代替 char/nchar。
h. 任何地方都不要使用 select from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。
i. 尽量使用表变量来代替临时表。
j. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
k. 尽量避免使用游标，因为游标的效率较差。
l. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET 
NOCOUNT OFF。
m. 尽量避免大事务操作，提高系统并发能力。
n. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
NoSQL 和关系数据库的区别？
a. SQL 数据存在特定结构的表中；而 NoSQL 则更加灵活和可扩展，存储方式可以省是 JSON 文档、
哈希表或者其他方式。
b. 在 SQL 中，必须定义好表和字段结构后才能添加数据，例如定义表的主键(primary key)，索引
(index),触发器(trigger),存储过程(stored procedure)等。表结构可以在被定义之后更新，但是如果有
比较大的结构变更的话就会变得比较复杂。在 NoSQL 中，数据可以在任何时候任何地方添加，不需要
先定义表。
c. SQL 中如果需要增加外部关联数据的话，规范化做法是在原表中增加一个外键，关联外部数据表。
而在 NoSQL 中除了这种规范化的外部数据表做法以外，我们还能用如下的非规范化方式把外部数据直
接放到原数据集中，以提高查询效率。缺点也比较明显，更新审核人数据的时候将会比较麻烦。
d. SQL 中可以使用 JOIN 表链接方式将多个关系数据表中的数据用一条简单的查询语句查询出来。
NoSQL 暂未提供类似 JOIN 的查询方式对多个数据集中的数据做查询。所以大部分 NoSQL 使用非规范
化的数据存储方式存储数据。
e. SQL 中不允许删除已经被使用的外部数据，而 NoSQL 中则没有这种强耦合的概念，可以随时删
除任何数据。
f. SQL 中如果多张表数据需要同批次被更新，即如果其中一张表更新失败的话其他表也不能更新成
功。这种场景可以通过事务来控制，可以在所有命令完成后再统一提交事务。而 NoSQL 中没有事务这
个概念，每一个数据集的操作都是原子级的。
g. 在相同水平的系统设计的前提下，因为 NoSQL 中省略了 JOIN 查询的消耗，故理论上性能上是
优于 SQL 的。
Mysql 数据库中怎么实现分页？
select * from table limit (start-1)*limit,limit; 其中 start 是页码，limit 是每页显示的条数。
sql 语句怎么看效率？
SQLServer2005-->新建一个查询-->输入语句 SELECT * FROM Person.Contact
执行(F5)-->Ctrl+L。
优化数据库？提高数据库的性能？
对语句的优化
①用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；
  通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；
  ②能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用 SQL 时，尽量把使用
  的索引放在选择的首列；算法的结构尽量简单；
  ③在查询时，不要过多地使用通配符如 SELECT * FROM T1 语句，要用到几列就选择几列如：
  SELECT COL1,COL2 FROM T1；
  ④在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM 
  T1,因为某些情况下用户是不需要那么多的数据的。
  ⑤不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常规的、面向集的 SQL 语
  句需要更大的开销；按照特定顺序提取数据的查找。
避免使用不兼容的数据类型
例如 float 和 int、char 和 varchar、binary 和 varbinary 是不兼容的。
  数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。
  例如:
  SELECT name FROM employee WHERE salary ＞ 60000
  在这条语句中,如 salary 字段是 money 型的,则优化器很难对其进行优化,因为 60000 是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。
  若在查询时强制转换，查询速度会明显减慢。
避免在 WHERE 子句中对字段进行函数或表达式操作。
若进行函数或表达式操作，将导致引擎放弃使用索引而进行全表扫描。
避免使用!=或＜＞、IS NULL 或 IS NOT NULL、IN ，NOT IN 等这样的操作符
尽量使用数字型字段
合理使用 EXISTS,NOT EXISTS 子句。
尽量避免在索引过的字符数据中，使用非打头字母搜索。
分利用连接条件
消除对大型表行数据的顺序存取
避免困难的正规表达式
使用视图加速查询
能够用 BETWEEN 的就不要用 IN
DISTINCT 的就不用 GROUP BY
部分利用索引
能用 UNION ALL 就不要用 UNION
不要写一些不做任何事的查询
尽量不要用 SELECT INTO 语句
必要时强制查询优化器使用某个索引
虽然 UPDATE、DELETE 语句的写法基本固定，但是还是对 UPDATE 语句给点建议：
a) 尽量不要修改主键字段。
b) 当修改 VARCHAR 型字段时，尽量使用相同长度内容的值代替。
c) 尽量最小化对于含有 UPDATE 触发器的表的 UPDATE 操作。
d) 避免 UPDATE 将要复制到其他数据库的列。
e) 避免 UPDATE 建有很多索引的列。
f) 避免 UPDATE 在 WHERE 子句条件中的列
提取数据库中倒数 10 条数据？
  select top (10) * from table1 order by id desc
数据库负载均衡
  负载均衡集群是由一组相互独立的计算机系统构成，通过常规网络或专用网络进行连接，由路
  由器衔接在一起，各节点相互协作、共同负载、均衡压力，对客户端来说，整个群集可以视为一台
  具有超高性能的独立服务器。
  1、 实现原理
  实现数据库的负载均衡技术，首先要有一个可以控制连接数据库的控制端。在这里，它截断了
  数据库和程序的直接连接，由所有的程序来访问这个中间层，然后再由中间层来访问数据库。这样，
  我们就可以具体控制访问某个数据库了，然后还可以根据数据库的当前负载采取有效的均衡策略，
  来调整每次连接到哪个数据库。
  2、 实现多据库数据同步
  对于负载均衡，最重要的就是所有服务器的数据都是实时同步的。这是一个集群所必需的，因
  为，如果数不据实时、不同步，那么用户从一台服务器读出的数据，就有别于从另一台服务器读出
  的数据，这是不能允许的。所以必须实现数据库的数据同步。这样，在查询的时候就可以有多个资
  源，实现均衡。比较常用的方法是 Moebius for SQL Server 集群，Moebius for SQL Server 集群
  采用将核心程序驻留在每个机器的数据库中的办法，这个核心程序称为 Moebius for SQL Server 
  中间件，主要作用是监测数据库内数据的变化并将变化的数据同步到其他数据库中。数据同步完成
  后客户端才会得到响应，同步过程是并发完成的，所以同步到多个数据库和同步到一个数据库的时
  间基本相等；另外同步的过程是在事务的环境下完成的，保证了多份数据在任何时刻数据的一致性。
  正因为 Moebius 中间件宿主在数据库中的创新，让中间件不但能知道数据的变化，而且知道引起
  数据变化的 SQL 语句，根据 SQL 语句的类型智能的采取不同的数据同步的策略以保证数据同步成
  本的最小化。
  数据条数很少，数据内容也不大，则直接同步数据。数据条数很少，但是里面包含大数据类型，
  比如文本，二进制数据等，则先对数据进行压缩然后再同步，从而减少网络带宽的占用和传输所用
  的时间。
  数据条数很多，此时中间件会拿到造成数据变化的 SQL 语句， 然后对 SQL 语句进行解析，分
  析其执行计划和执行成本，并选择是同步数据还是同步 SQL 语句到其他的数据库中。此种情况应用
  在对表结构进行调整或者批量更改数据的时候非常有用。
  3、 优缺点
  优点：
  1) 扩展性强：当系统要更高数据库处理速度时，只要简单地增加数据库服务器就 可以得到扩展。
  2) 可维护性：当某节点发生故障时，系统会自动检测故障并转移故障节点的应用，保证数据库的持
  续工作。
  3) 安全性：因为数据会同步的多台服务器上，可以实现数据集的冗余，通过多份数据来保证安全性。
  另外它成功地将数据库放到了内网之中，更好地保护了数据库的安全性。
  4) 易用性：对应用来说完全透明，集群暴露出来的就是一个 IP
  缺点：
  a) 不能够按照 Web 服务器的处理能力分配负载。
  b) 负载均衡器(控制端)故障，会导致整个数据库系统瘫痪。
Mysql 数据库的操作?
  修改表-修改字段，重命名版：
  alter table 表名 change 原名 新名 类型及约束；
  alter table students change birthday birth datetime not null;
  修改表-修改字段，不重名版本：
  alter table 表名 modify 列名 类型和约束；
  alter table students modify birth date not null
  全列插入：insert into 表名 values(...)
  insert into students values(0,"郭靖", 1,"内蒙","2017-6");
  部分插入：值的顺序与给出的列顺序对应：
  insert into students(name, birthday) values("黄蓉","2017-8");
  修改：update 表名 set 列 1=值 1，列 2=值 2.。。where 
  update students set gender=0, homwtown="古墓"， where id = 5;
  备份：mysqldump -uroot -p 数据库名 》 python.sql,
  恢复：mysql -uroot -p 数据库名 < python.sql
数据库的设计？
  第一范式：数据库表的每一列都是不可分割的原子数据项，即列不可拆分
  第二范式：建立在第一范式的基础上，要求数据库表中的每个实例或记录必须是可以唯一被区分的，
  即唯一标识。
  第三范式：建立在第二范式的基础上，任何非主属性不依赖与其他非主属性，即引用主键
存储过程和函数的区别?
  相同点：存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合。
  1）存储过程和函数都是一次编译，就会被缓存起来，下次使用就直接命中已经编译好的 sql 语句，
  不需要重复使用。减少网络交互，减少网络访问流量。
  不同点：标识符不同，函数的标识符是 function，存储过程是 proceduce。
  1）函数中有返回值，且必须有返回值，而过程没有返回值，但是可以通过设置参数类型（in,out)
  来实现多个参数或者返回值。
  2）存储函数使用 select 调用，存储过程需要使用 call 调用。
  3）select 语句可以在存储过程中调用，但是除了 select..into 之外的 select 语句都不能在函数中使
  用。
  4）通过 in out 参数，过程相关函数更加灵活，可以返回多个结果。