Mysql

这是一篇关于Mysql的思维导图，包含优化的八种方式：事务，锁定表，使用外键，使用索引等内容。

MySql

• 三大范式（Normal Form）
  • 第一范式（1NF）
    • 数据库表中，所有的字段值都是不可分解的原子值
    • 第一范式的合理需求要根据系统实际需求来定
  • 第二范式（2NF）
    • 满足第二范式（2NF）必须 首先满足第一范式（1NF）
    • 非主键列必须要完全依赖于主键，而不能只依赖于主键的一部分。
  • 第三范式（3NF）
    • 满足第三范式（3NF）必须先满足第二范式
    • 每一列的数据都和主键直接相关，而不能间接相关，不能存在传递依赖
  • 第二范式和第三范式的区别？
    • 2NF，非主键列是否完全依赖于主键，还是部分依赖于主键。
    • 3NF，非主键列是直接依赖于主键，还是直接依赖于非主键列
• MySQL索引根据物理存储的两大形式
  • 聚集索引（聚簇索引 clustered index）
    • 聚集索引的叶节点存放表的行记录数据。
      • 优点
        • 查找目标数据时理论上比非聚集索引要更快，因为聚集索引能够在B+树索引的叶子节点上直接找到数据。
        • 由于定义了数据的逻辑顺序，聚集索引能够特别快速地访问一定范围内的数据。
    • 叶节点就是数据节点。
    • 聚集索引是物理连续的吗？
      • 聚集索引的存储并不是物理连续，而是逻辑连续的。
      • 数据页之间是通过双向链表连接的，按照主键的顺序排序的。
      • 数据页内的记录是通过单项链表进行维护的，物理存储上可以同样不按照主键的顺序存储。
  • 非聚集索引 non clustered index（二级索引、辅助索引）
    • 非聚集索引的叶节点存放的是指针，指针对应的数据块。
    • 叶节点仍然是索引节点。
    • 劣势
      • 非聚集索引查找目标数据时需要多一次索引查询。
• 优化查询速度的方法
  • 查询慢的原因
    • 从程序员的角度来说
      • 1、查询语句写不好
      • 2、没建立索引，索引建的不合理或索引失效
      • 3、关联查询有太多的join
    • 从服务器的角度来说
      • 1、服务器磁盘空间不足
      • 2、服务器调优配置参数设置不合理
  • 优化的八种方式
    • 1、选取最适用的字段属性
      • 1、为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
      • 2、使用更小的整数类型，尽可能的定义字段不为空（not null），否者会导致索引复杂。
    • 2、使用连接join来代替子查询（Sub-Queries）
      • 子查询：使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。 因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
      • 如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多 表中对ID建有索引的话，性能将会更好。
    • 3、使用联合（union）来代替手动创建的临时表
      • MySQL 4.0版开始支持union语句。
      • 它可以把需要使用临时表的两条或更多的select查询合并到一个查询中。
      • 在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库的整齐，高效
      • 只需要用UINION作为关键字把多个select语句连接起来就可以了； 注意的是所有select语句中的 字段数目 要 相同。
    • 4、事务
      • 事务是以begin关键字开始，commit关键字结束的。在其中的一条sql语句操作失败了，那么，rollback命令就可以把数据库恢复到begin开始之前的状态。
      • 重要作用：当多个用户同时使用相同的数据源时，它可以 利用锁定数据库 的方法来为用户提供一种安全的访问方式，可以保证用户的操作不被其他的用户所打扰。
      • 作用：要么语句块中的每条语句都操作成功，要么都失败。 就是可以保证数据库中的一致性和完整性。
      • 劣势：1、因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。 2、如果有成千上万 的用户同时访问一个数据库系统，就会产生比较严重的响应延迟。 可以通过锁定表的方法来获取更好的性能。
    • 5、锁定表
      • LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book'; UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES
      • 包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory（库存）进行插入、更新或者删除的操作。
    • 6、使用外键
      • 锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
      • 外键可以保证每一条销售记录都指向某一个存在的客户。
      • 当某表中的一条记录被删除时，另一张表中的所有与之相关联的记录也会被自动删除。
    • 7、使用索引
    • 8、优化的查询语句
      • 绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。
      • sql查询语句的优化方法：
        • 1、避免操作多余的数据
          • 1、尽量避免 select * ，改用select 列名，避免返回多余的列。
          • 2、若插入数据过多，考虑批量插入。
          • 3、尽量避免同时修改或删除过多数据。
          • 4、使用where条件语句限制要查询的数据，避免返回多余的行
        • 2、避免删库 跑路
        • 3、where查询子句优化
          • 1、应尽量避免在where子句中使用 != 或者 <>操作符，否则将引擎放弃使用索引而进行全表扫描。
          • 2、应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。
          • 3、查询语句的查询条件中只有or关键字，并且or前后的两个条件中的列都是索引时，查询中才使用索引。否则，索引失效。
          • 4、不要再where字句中使用 not in。
          • 5、合理使用 exist & in。
          • 6、in和 not in 也要慎用，否则会导致全表扫描。 对于连续的数值，能用 between 就不用 in 了，指定区间。
          • 7、如果在where子句中使用参数，也会导致全表扫描。 因为sql 只有在运行时才会解析局部变量， 但优化程序不能将访问计划的选择到运行时；
            • 可以改为强制查询使用索引。
          • 8、应尽量避免在where子句 对 字段 进行 表达式操作， 这将导致引擎放弃使用索引而进行全表扫描。
            • select id from t where num/2 = 100; 应改为：select id from t where num = 100 * 2;
          • 9、谨慎使用 distinct 关键字。
        • 4、like语句的优化
          • 前导模糊查询不能利用索引(like '%XX'或者like '%XX%')，可以使用索引覆盖避免
          • 优化： select * from student where name like "张三%";
        • 5、limit查询优化
          • 查询一条或者最大、最小一条记录，建议使用 limit 1
          • 优化limit 分页
        • 6、索引优化
          • 4、考虑在where及order by 涉及的列上建立索引。 应尽量避免进行全表扫描。
          • 索引不要超过六个
          • 删除冗余和无效的索引
          • 尽量使用数字型字段。
          • 建议使用自增主键。
• 锁有哪些，如何理解？
  • 按锁粒度分类
• select id from t with(index(索引名)) where num=@num
• SELECT Name,Phone FROM client UNION SELECT Name,BirthDate FROM author UNION SELECT Name,Supplier FROM product