Mysql之五-查询要点和注意事项

order by 工作原理

  1. mysql会给每个线程分配一个内存(sort_buffer)用于排序, 其大小为sort_buffer_size;
    • 如果排序数据量小于sort_buffer_size, 排序将在内存中进行;
    • 如果数据量很大, 内存无法存下这么多数据, 则会用磁盘临时文件来辅助排序, 也称外部排序;
    • 在使用外部排序时, mysql会分成好几份单独的临时文件用来存放排序后的数据,然后再将这些文件合并成一个大文件
  2. 全字段排序
    • 太多数据的情况下会使用到磁盘临时表, 对IO开销大;
    • 这个算法的过程就是读出所有数据在内存中, 然后在sort_buffer和临时文件中执行,如果字段太多会导致内存放进的行数很少, 导致最后会分拆成太多的临时文件, 排序性能变差;
  3. rowId排序
    • 这个排序的算法是先只查主键和排序字段, 然后再由主键回表去查询出所需要的内容, 这个优点可以加快排序, 但是要多回表查询;(并且是随机IO读)
    • max_length_for_sort_data (设置多少字段长度时, 就使用rowId)
  4. 使用覆盖索引加快排序;

随机排序的处理方式

  1. 使用order by rand();
    • 这个会使用了内存临时表, 内存临时表排序的时候使用了rowid排序方式(使用全表扫描)
    • tmp_table_size配置了内存临时表大小(16m), 过大的话会变成了磁盘临时表;
    • 如果使用了limit的方式, 如果取出数据行大小小于sort_buffer_size, 会使用优先队列算法,(堆算法), 减少了临时文件的使用, 如果是超过了sort_buffer_size, 则只能使用归并排序算法;
  2. 使用业务缓存排序
  3. 使用取出整表长度, 再随机取的方式:(伪随机)

注意sql的执行慢的坑

  1. 使用条件字段函数操作(会不使用索引)
  2. 隐性类型转换
    • 字符串和数字做比较的话, 是将字符串转为数字;
  3. 隐性字符编码转换
    • 要把小编码集转为大编码集

sql查询一行慢的原因;

    • MDL锁;
    • 表锁;
    • 行锁;
    • 通过锁日志或者执行日志查看;
    • 查询条件没有索引;
    • 该表可能有大量的并发写入;(导致快照读非常慢)

join的使用方法注意

1.驱动表是走全表扫描,而被驱动表是走树搜索;(通常而言, 前面的表为驱动表, 后面的为被驱动表)(仅限于被驱动表有索引的情况下)
2.所以前面的表要尽量小;

lock table 与 for update:

1.表级锁lock table t1 read

  • 会导致其他线程中, 这个表只读;(共享锁, 排他锁均不可加)
  • 本线程接下来也只能操作t1, 且只读, 其他表不可操作;

2.表级锁lock table t1 write

  • 会导致其他线程对该表读写均不可;(快照读也不行)
  • 本线程接下来也只能操作t1, 但读写均可;

3.若是select * from t1 for update;

  • 会导致其他线程中, 这个表只读;(共享锁, 排他锁均不可加)
  • 但本线程接下来的操作可以操作t1和其他表, 且t1可读写;
hyhcoder wechat
扫码关注我的个人订阅号