MYSQL

  1. 结构化查询语句分类:

    名称解释命令
    DDL （数据定义语言)定义和管理数据对象，如数据库，数据表等CREATE、DROP、ALTER
    DML （数据操作语言）用于操作数据库对象中所包含的数据INSERT、UPDATE、DELETE
    DQL （数据查询语言）用于查询数据库数据SELECT
    DCL （数据控制语言）用来管理数据库的语言，包括管理权限及数据更改GRANT、COMMIT、ROLLBACK

  2. 数据引擎:

    1. InnerDB：业务数据表，要保证数据的安全性，这种表当中会出现大量的增删改（写）操作。必须要有一个主键。
      1. 聚集索引
      2. 保存的是数据本身
      3. 主键查询高效，辅助索引查两遍
    2. MyISAM：增量数据表，通常来说，这种表当中，几乎不会去修改数据，只是对数据的查询效率有较高的要求。可以没有主键。
      1. 非聚集索引
      2. 保存数据的地址
      3. 主键和辅助索引查询没有区别

  3. 常见的MyISAM与InnoDB类型

    名称MyISAMInnoDB
    事务处理不支持支持
    数据行锁定不支持支持
    外键约束不支持支持
    全文索引支持不支持
    表空间大小较小较大,约2倍

  4. 索引：

    1. 作用：
      1. 提高查询速度
      2. 确保数据的唯一性
      3. 可以加速表和表之间的连接，实现表与表之间的参照完整性
      4. 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
      5. 全文检索字段进行搜索优化
    2. 分类：
      1. 主键索引（PRIMARY KEY）
      2. 唯一索引（UNIQUE）
      3. 常规索引（INDEX）
      4. 全文索引（FULLTEXT）
    3. 索引准则
      1. 索引不是越多越好
      2. 不要对经常变动的数据加索引
      3. 小数据量的表建议不要加索引
      4. 索引一般应加在查找条件的字段
    4. 索引失效：
      1. where语句中包含or时，可能会导致索引失效
      2. where语句中索引列使用了负向查询，可能会导致索引失效
      3. 索引字段可以为null，使用is null或is not null时，可能会导致索引失效
      4. 在索引列上使用内置函数，一定会导致索引失效
      5. 对索引列进行运算，一定会导致索引失效
      6. like通配符可能会导致索引失效
      7. 联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效
      8. MySQL优化器的最终选择，不走索引

  5. 执行计划：

    1. Id：
      1. 相同由上而下
      2. 不同由大到小
    2. 查询类型select_type：主要是用于区分普通查询、联合查询、子查询
    3. type：访问类型，SQL 查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL
    4. possible_keys：查询过程中有可能用到的索引
    5. key：实际使用的索引，如果为 NULL ，则没有使用索引。
    6. rows：根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数，越小越好。
    7. filtered：表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

  6. 优化策略：

    1. 字段类型优化：
      1. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
      2. 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.（备注、描述、评论之类的可以设置为 NULL）
    2. where条件语句优化：
      1. WHERE子句中的连接顺序：数据库采用自左向右的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前(左), 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的前面（最左）.
      2. 避免在where语句中出现字段的类型转换（字段的类型和传入的参数类型不一致的时候发生的隐式类型转换），当两张表字符集不同的时候关联查询
      3. 不要在where条件语句中的"="左边进行函数、算数运算或其他表达式运算，可以通过使用冗余字段来替代函数运算，否则系统无法正确使用索引
      4. where 子句中对字段进行 null 值判断、包含not、!=、<>等操作符，或like的关键词前加%(like ‘%关键词’)，都无法使用索引，从而引发全表扫描.
      5. 使用like进行模糊查询时应注意,除非必要，否则不要在关键词前加%，否则必然导致全表查询
    3. 索引优化
      1. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
      2. 用索引提高效率：合理使用索引和复合索引同样能提高效率.但使用索引是有代价的, 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的。
      3. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。
      4. 要注意索引的维护，周期性重建索引。
    4. 分页优化：分页查询的优化。页数比较多的情况下，如limit 10000,10 影响的结果集是10010行，查询速度会比较慢。推荐的解决方案是：先只查询主键select id from table where … order by … limit 10000,10(搜索条件和排序请建立索引)，再通过主键去获取数据。

  7. 事务的ACID原则：

    image-20210509170842003

  8. 事务隔离级别：

    1. Read Uncommitted：未解决并发问题
    2. Read Committed：解决脏读问题
    3. Repeatable Read：解决不可重复读问题（innerDB默认）
    4. Serializable：解决所有问题

  9. MySQL InnoDB 锁：

    1. 共享锁（Shared Locks）:又称为读锁，简称 S 锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

      select * from dept where deptno=10 lock in share mode

    2. 排他锁（Exclusive Locks）：又称为写锁，简称 X 锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对数据行进行读取和修改。（其他事务要读取数据可来自于快照）

      select * from dept where deptno=10 for update
posted @ 2022-02-21 22:16  隐风  阅读(33)  评论(0)    收藏  举报