MySQL笔记

城南花已开丶
355 阅读12分钟

通配符

  在 SQL 中，通配符与 SQL LIKE 操作符一起使用，常见的通配符有：

通配符 含义
% 匹配0个或多个字符
_ 匹配1个字符

同时，还可以使用 RLIKE NOT RLIKE(或者REGEXP NOT REGEXP)来操作正则表达式。

null

  数据库的null值是一个非常特殊的存在，它不能被直接比较，而应该使用 is 或 is not 来判断。

# 错误！null不能被直接比较
select * from table where name != null; 
# 正确用法
select * from table where name is not null;

同时，凡是有null参与的运算，结果一律为null，此时可以使用 ifnull()函数来为可能为null的列提供默认值。

需求：
    计算员工一年的总薪资，其中奖金(bonus)列可能为null
    
实例：
    # 结果可能为null，如果bonus为null的话
    select salary * 12 + bonus as total from empt; # error
    # 使用ifnull函数，如果bonus为null，将其视作0
    select salary * 12 + ifnull(bonus,0) as total from empt;

注意：聚合函数在计算时会自动忽略null值。

# 不需要后置 where bonus is not null，avg函数在执行时会忽略null值
# 其它聚合函数 sum/max/min/count 对null值的处理也类似
select avg(bonus) from empt;

SQL的执行顺序

  SQL语句的书写一般如下所示：

    select distinct
        < select_list >
    from
        < left_table> 
    < join_type > join
        < right_table >
    on
        < join_condition >
    where 
        < where_condition >
    group by
        < group_by_list >
    having
        < having_condition >
    order by
        < order_by_list >
    limit
        < limit_number >

那么在MySQL中是如何执行的呢？MySQL中最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。

  1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
  2. ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
  3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, 如果 from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止
  4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
  5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
  6. HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被插入到虚拟表VT7中。
  7. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
  8. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
  9. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
  10. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

SQL JOINS

joins

ACID

  所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位（执行单个逻辑功能的一组指令或操作称为事务）。

  数据库管理系统中事务(transaction)的四个特性（分析时根据首字母缩写依次解释）：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

  1. 原子性：原子性是指事务是一个不可再分割的工作单元，事务中的操作要么都发生，要么都不发生。在DBMS中，默认情况下一条SQL就是一个单独事务，事务是自动提交的。只有显式的使用start transaction开启一个事务，才能将一个代码块放在事务中执行。

  2. 一致性：一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

  3. 隔离性：多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

  4. 持久性：持久性，意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚（完成的事务是系统永久的部分，对系统的影响是永久性的，该修改即使出现致命的系统故障也将一直保持）。

Explain

  使用explain关键字可以查看执行计划，从而了解MySQL内部是如何执行SQL语句的，为性能调优提供依据。

字段 解释 常见值
id select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序 1. id相同，执行顺序由上到下
2. id不同，值越大优先级越高，越先执行
select_type 查询的类型，主要用于区分普通查询、联合查询和子查询等情况 1. SIMPLE：简单的select查询，不包含子查询或联合查询
2. PRIMARY：查询中若包含任何类型的子部分，最外层查询则被标记为PRIMARY
3. SUBQUERY：在select或where列表中包含了子查询
4. DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表中
5. UNION：若第二个select出现在UNION之后，则被标记为UNION；若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
6. UNION RESULT： 从UNION中获取结果的select
table 显示数据是属于哪张表的 <derived2>表示数据来自id为2的语句产生的衍生表
type join类型 1. system：表只有一行记录，这是const类型的特例
2. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行记录，所以很快
3. eq_ref：当连接使用索引的所有部分且索引是primary key或unique not null索引时, 将使用该值。
4. ref：非唯一性索引扫描，返回匹配某个值的所有行（可能有多个）
5. fulltext：使用全文索引执行连接
6. ref_or_null：类似于ref，但会额外检索包含NULL值的行(key_column is null)
7. index_merge
8. unique_subquery：将eq_ref替换为IN子查询(value IN (SELECT primary_key FROM single_table WHERE some_expr))
9. index_subquery：类似unique_subquery，针对的是nonunique索引
10. range：只检索指定范围的行，一般就是在where子句中出现了>、<、is null、between...and或in等的查询
11. index：类似于ALL，但只需要遍历索引树就能得到结果
12. ALL：全表扫描
注意： 从上到下效率依次降低。一般来说得保证查询达到range级别，最好能达到ref级别。
possible_keys 可能用到的索引（实际上不一定会用上）
key 实际用到的索引（查询中若使用了覆盖索引，则该索引仅会出现在key列表中）
key_len 索引字段最大可能长度，由表定义计算得出，不是实际数据，一般来说，长度越小越好
ref 显示索引的哪一列被使用了，如果可能的话是一个常数
row 根据表统计信息和索引使用情况，大致估算出找到所需的记录需要读取的行数
Extra 补充信息 1. Using filesort：使用文件排序而不是索引
2. Using temporary：使用临时表保存中间结果，比如order by和group by使用的索引列不同
3. Using index：表示使用了覆盖索引。如果同时出现了Using where，表示索引被用来执行索引键值的查找，如果没有出现Using where，表示索引被用来读取数据而非查找操作
4.Using where：使用了条件过滤
5. Using join buffer：使用了连接缓存
6. Impossible where：where条件总是为false，不能用来获取数据

SQL优化

OR

  对于包含OR的查询语句，如果要利用索引，需要保证OR两边的条件都用到索引。

# 可以使用索引
select * from tbl where key_column1=const or key_colomn2=const
# 无法使用索引
select * from tbl where key_column=const or other_column=const

有两种常见的手段进行优化：

  1. 给没有索引的列加上索引
  2. 使用联合查询（不一定更好）

ORDER BY/GROUP BY

  1. 查询的字段，应该只包含此次查询使用的索引字段和主键，其余的非索引字段和索引字段作为查询字段则不会使用索引
# 无法使用索引
select * from tbl order by key_colomn
# 可以使用索引
select key_colomn from tbl order by key_colomn
  1. 排序字段在一个索引中,并且WHERE条件和ORDER BY使用相同的索引且排序方向相同,可以利用索引排序
  2. 遵循索引最左原则（索引最左部分是常量时，也可以利用上索引）
# idx_age_salary
# age是一个常量，此时可以利用索引
select age, salary from EMP where age=10 order by salary

GROUP BY的实质是先排序后分组，因此和ORDER BY优化策略相同。

LIMIT

  一般分页查询时，通过创建覆盖索引可以比较好的提升性能，比较头疼的点在于LIMIT 20000,10这样的语句。MySQL在执行分页时，需要读取并排序前20010条数据，但是仅仅返回20000-20010之间的数据，其它数据都被丢弃，查询代价非常大。可以在索引上完成排序分页操作，再通过主键关联回原表查询所需的其他列内容来进行优化

select * from tbl join (select id from tbl order by id limit 20000,10) t on tbl.id=t.id

日志

  MySQL中主要有五种不同的日志，分别是error log、general query log、slow query log、slave relay log和binary log，这些日志记录着MySQL在不同方面的踪迹。

error log

  错误日志记录着MySQL启动和停止时，以及运行过程中发生任何严重错误时的相关信息，当MySQL出现故障无法使用时，首先应该查看该日志。

  错误日志是默认开启的，log_error相关的环境变量记录着错误日志存放位置等相关信息：

show variables like 'log_error%';

binary log

  binlog记录了所有的DDL和DML语句，但是不包括DQL语句。此日志对于灾难时的数据恢复起着极其重要的作用，同时MySQL的主从复制，也是通过binlog实现的。

  binlog不是默认开启的，需要在配置文件中开启，并配置binlog的格式。

# 获取MySQL配置文件的读取路径
$ mysql --help | grep 'my.cnf'

# 查询binlog存放位置等相关信息 
show variables like 'log_bin%'

# 查询binlog格式等相关信息
show variables like 'binlog_%'

binlog有三种格式：

  • STATEMENT
  • ROW
  • MIXED

  STATEMENT是基于sql语句的，每一条修改数据的sql都会被保存到binlog里，主从复制的时候，从库会将binlog解析成文本并在从库执行一次。

  ROW是基于数据行的，它会记录每一行记录的变化，也就是将每一行的修改都记录到binlog里面，但sql语句并没有记录在binlog里，在replication里面也不会因为存储过程触发器等造成Master-Slave数据不一致，但是有个致命的缺点日志量比较大。由于要记录每一行的数据变化，当执行update语句后面不加where条件或alter table的时候，产生的日志量相当的大。

  MIXED在默认情况下是STATEMENT，但是在某些情况下会切换到ROW状态，比如与时间、用户相关的函数等。在主从复制的情况下，主机上NOW、UUID等函数的执行和在备机的执行不一致，所以对于这种情况就必须把STATEMENT模式更改为ROW模式，因为ROW模式会直接写值而不是写语句。同样ROW模式还可以减少从机的相关计算，如在主机中存在统计写入等操作时，从机就可以免掉该计算把值直接写入从机。

  可以通过MySQL提供的mysqlbinlog工具读取binlog，对于ROW类型的binlog，需要附加 -v 参数将其转换成可以看懂的信息。

slave relay log

  转储日志记录的是主从关系中，从master同步过来的binlog，因此也可以使用 mysqlbinlog 程序进行查看，与其相关的配置参数包括：

  • relay-log：转存日志文件名
  • relay-log-index：转存日志索引文件名
  • max_relay_log_size：转存文件大小限制，如果不设置则使用 max_binglog_size 的设置。

general query log

  常规操作日志可以说是内容最全的日志，包括所有 mysql 所作的操作和连接情况。由于此类日志记录内容较多，所以对数据库性能会有一定的影响，所以在生产环境中不建议开启，与其相关的配置项如下：

  • general_log：开关日志，0/OFF代表关，1/ON代表开
  • general_log_file：日志文件位置

slow query log

  慢查询日志在调优过程中使用的比较多，其中主要记录的是执行较慢的操作，具体时间可通过参数进行设置。与其相关的配置项如下：

  • slow_query_log：是否开启慢查询日志，可选值为0和1，默认为0。
  • slow_query_log_file：慢查询日志文件名。
  • long_query_time：时间长度，单位毫秒，超过此时间的操作将会被记录至日志。
  • log_slow_admin_statements：是否记录管理员维护操作，可选值为ON/OFF，默认为OFF。
  • log_queries_not_using_indexes：是否记录未使用索引的查询可选值为ON/OFF，默认为OFF。

慢查询日志不一定非要看日志文件，通过 mysqldumpslow 命令可在控制台直接查看。

avatar
文章
阅读
粉丝