MySQL的那些事

MySQL常见问题（后面会补全）：

目录导航

• 常见键的含义
• 数据库事务的四大特征及意义
• 索引的工作原理和种类
• SQL语句的连接问题
• 数据库的范式
• 数据库的优化

第一章： 常见键的含义

1.1 主键：

• 数据库表中对存储数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只有一个主键，且主键的取值不能缺失，即不能为空（Null）；

1.2 超键：

• 在关系中唯一标识元素的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

1.3 候选键：

• 是最小超键，即没有冗余元素的超键

1.4 外键：

• 在一个表中存在的另一个表的主键称此表的外键；

第二章： 数据库事务的四大特征及含义

2.1 概述：

	数据库事务正确执行的四个基本要素。ACID，原子性，一致性，隔离性，持久性；

2.2 具体描述：

• 原子性：整个事务中所有操作，要么都全部完成，要么全部都不完成，不可能停在某个中间环节。事务在执行过程中发生错误，会被回滚到事务的开始前的状态，就像这个事务从来没有执行过一样；
• 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；
• 隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统；这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间内仅有一个请求用于同一数据；
• 持久性：在事务完成以后，该事务对数据库所做的更改变持久的保存在数据库之中，并不会被回滚；

2.3 视图的作用，视图可以被更改吗？

• 视图是虚拟的表，与包含的数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用他们；

• 视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。

• 创建视图？
  create view XXXX as XXXXXX;

对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的额，对视图的更新对基表进行更新；

但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新；

2.4 drop,delete与truncate的区别？

• drop直接删除表，truncate删除表中的数据，再插入时自增长id又从1开始delete删除表中数据，可以加where字句；
• drop>truncate>delete
• 应用范围： TRUNCATE只能对TABLE；DELETE可以是table和view
• TRUNCATE和DELETE只删除数据，而DROP则删除整个表（结构和数据）

第三章： 索引的工作原理和种类：

3.1 数据库索引

• 是数据库管理系统中的一个排序的数据结构，以协助快速查询、更新数据库表中的数据。索引的实现通常使用B树及其变种B+树；
• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向的数据，这样就可以在这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找方法。这种数据结构，就是索引；
• 为表设置索引要付出代价的：一是增加了数据库的存储空间，二使在插入和修改数据时要花费较多的时间（因为索引也要随之变动）

3.2 优点：

• 创建索引可以大大提高系统的性能。
• 举例：
  • 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的检索速度，这也是创建索引的主要原因；
  • 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

3.3 缺点：

• 创建索引和维护索引需要耗费时间，这种时间随着数据量的增加而增加。

• 索引需要占物理空间，除了数据表占数据空间外，每一个索引还要占一定的物理空间，如果要建立聚族索引，那么需要的空间就会更大；

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

• 哪些数据不适合建索引？

  • 查询中很少使用或者参考的列不应该建索引；
  • 很少数据值的列也不应该建索引
  • 数据量大或者取值很少的比如text,image,bit等数据类型不适合建索引
  • 当修改性能远远大于检索性能时，不应该建索引；

3.4 索引的类型：

• 根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引；
• 唯一索引：是不允许其中任何两行具有相同索引值的索引。
• 主键索引：要求主键中的每个值都唯一
• 聚集索引：表中行的物理顺序与键值的逻辑（索引）顺序相同，一个表只能包含一个聚集索引。

第四章： SQL语句

4.1 外连接

• 概念：包括左外连接、右外连接和完整外部连接

• 使用： left join 或者left outer join

• 示例：

    `select * from table1 left join table2 on table1.id=table2.id;`
  

• 右外连接：right jion 或者right outer join

• 概念：右外连接是左向外连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将左表返回空值；

• 完整的外部连接：full join 或者**full outer join **

• 完整外部连接返回左表或者右表中所有行。

4.2 内连接：

• 概念： 内连接是用比较运算符比较要连接列的值的连接

• 内连接： join 或者inner join

• 语句：

  select * from table1 join table2 on table1.id=table2.id;

• 交叉连接：

  • 概念：没有WHERE 子句的交叉连接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小（table1和table2交叉连接产生3*3=9条记录）
  • 语句：cross join(不带条件where)
  • sql语句：select * from table1 cross join table2

第五章： 数据库范式

• 第一范式：无重复的列
• 第二范式：非主属性非部分依赖于主关键字
• 第三范式：属性不依赖于其他非主属性（消除冗余）

第六章： 数据库优化的思路：

6.1 SQL语句优化：

• 尽量避免在where字句汇总使用！=或者<>操作符，否则将引擎放弃使用索引而进行全表扫描；
• 应尽量避免在where子句中对字段进行null判断，否则将导致引擎放弃使用索引而进行全局扫描；

解决办法： 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0;

• 很多时候用exists代替in是一个好的选择
• 用where 字句替换HAVING子句，因为HAVING只会在检索出所有记录后才对结果集进行过滤；

6.2 索引优化：

• 看上面索引

• 数据库结构优化：

  • 范式优化：消除冗余，节省空间；
  • 反范式优化：适当加冗余，减少join
  • 拆分表：
    • 分区将数据在物理上隔开，不同的分区可以指定保存在不同磁盘上的数据文件里，这样在查询的时候只需要在表分区查询而不是全表扫描，明显缩短了查询时间；
    • 处于不同磁盘的额分区可以将数据传入对磁盘I/O竞争均匀地散开。对数据量大的时时表可采取此方法。可按月自动建表分区；
    • 拆分其实又分垂直拆分和水平拆分：
      垂直拆分：用户表和产品表分别放到不同的服务器上
水平拆分：比如用户表将分为男性和女性，分开来；

6.3 服务器硬件优化：

要花钱

6.4 存储过程和触发器的区别：

• 触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。
• 触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。
• 触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称和名字而直接调用。

6.5 MYSQL实现自定义排序和去重复：

• 使用DISTINCT 去除重复行

  • 查找所有不同的vend_id,prod_price
    select distinct vend_id,prod_price From products;

• 使用Limit限制行数

  • 输出第三行之后的第四行：
    SELECT prod_name from products limit 3,4;

• 对结果排序：

  • 默认升序：
    select prod_name from products order by prod_name

  • 按照多个列排序：

    • 先按照价格排序，价格相同时按照名称排序:
      select prod_id,prod_price,prod_name from products order by prod_price,prod_name;

  • 按指定排序规则：

    • 指定降序
      select prod_id,prod_price,prod_name from products order by prod_price DESC;

  • 先按降序排列价格，价格相同的按产品名称升序排列：
    select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name;

注意： DESC关键字只作用于其之前的那一列，与DESC对应的是升序ASC，是默认的，所以不用写；

• where 子句过滤数据

  • 查询语句：
    select prod_price,prod_name from products where prod_name='fuses';

• 使用通配符过滤：

  • 使用通配符时必须使用like关键字

  • %通配符：找出所有name以jet开头的物品；
    select prod_id,prod_name from products where prod_name like 'jet%';

  • _通配符，与%通配符不同，_只匹配一个字符；

• 使用正则表达式搜索：

  • 概念： 正则表达式使用来匹配文本的特殊的串；
    select prod_id,prod_name from products where prod_name REGEXP '1000';