DBA笔记-第一部分

优化排序占用内存（order by）

--查看全局global
show global variables like 'sort_buffer_size%';
--修改当前内存占用为256兆  注意每个会话都可能会占用，需要根据
实际用户来判断设置为多少（线程使用到了才会分配给会话）
set sort_buffer_size = 256*1024*1204
----查看状态  包含顺序   （global 可看全局）
show status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 360   |  内存不足导致多次调用内存排序次数 越小越好
| Sort_range        | 0     |
| Sort_rows         | 20000 |
| Sort_scan         | 1     |
+-------------------+-------+
 

 优化临时表查询（group by）

--查看全局global
show global variables like '%tmp%';
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| default_tmp_storage_engine       | InnoDB   |
| innodb_tmpdir                    |          |
| internal_tmp_disk_storage_engine | InnoDB   |
| max_tmp_tables                   | 32       |
| slave_load_tmpdir                | /tmp     |
| tmp_table_size                   | 16777216 | 临时表大小
| tmpdir                           | /tmp     |
+----------------------------------+----------+

---可根据需求增大，修改为1g  
set tmp_table_size = 1024*1024*1024;

show variables like '%tmp%';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| default_tmp_storage_engine       | InnoDB     |
| innodb_tmpdir                    |            |
| internal_tmp_disk_storage_engine | InnoDB     |
| max_tmp_tables                   | 32         |
| slave_load_tmpdir                | /tmp       |
| tmp_table_size                   | 1073741824 |
| tmpdir                           | /tmp       |
+----------------------------------+------------+

show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |内存不足使用硬盘次数
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 5     |
+-------------------------+-------+

查询小技巧

-----count说明----

count(1)=count(2)=count(3)=count(*)查询数据条数包含null 且count几都是一样的没区别

count(字段名)查询数据条数不包含null

-----null的判断------

子查询要加is not null 否则会导致查询不到想要的结果

a表             b表

id   列1       id   列1 

1    1           1     1

2     2          2     2

3     null       3    3

select 列1 from b  where 列1  not in （select 列1 from a）  结果为null

select 列1 from b  where 列1  not in （select 列1 from a where 列1 is not null）  结果为3

-----增加行号查询-----

注意要给表取别名 使用*会有问题

SELECT @a := @a + 1 AS row_number, e.*
FROM employees e,(select @a:=0) a;

面试能写出来就过的，需要sql天赋过人，但实际没啥用的相关子查询行号写法，一般人理解不了。

（求和小于等于每行数据值得数量）配合mysql官网中employees数据库练习

SELECT emp_no,( SELECT count( 1 ) FROM employees t2 WHERE t2.emp_no <= t1.emp_no ) AS rownum 
FROM
    employees t1 
GROUP BY
    emp_no 
    LIMIT 10

------limit 解释------

limit 10 取得是随机数据，不是按顺序取，取得是一个集合中的数，集合的定义是无序数据的合集。