mysql个人笔记

语法

union: 将多条查询结果语句合并为一个结果。

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点：要求多条查询语句的列数一致

select * from employees where email like '%a%' or department_id >90;
等价于
select * from employees where email like '%a%'
union
select * from employees where department_id >90

连接

复制

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

sql约束

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

约束有以下几种：

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。

左连接添加右边索引

避免索引失效

1. 全值索引坠吼滴
2. 最佳左前缀法则
   • 如果索引了多列，遵循最佳左前缀法则：查询从索引的最左列开始并且不跳过索引的列
3. 不在索引列上做任何操作（计算，函数，类型转换），否则会导致索引失效从而转向全表扫描
4. 存储引擎不能试用索引中范围条件右边的列
5. 尽量试用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
6. mysql在使用不等于（！= 或者<>）的时候无法使用索引会导致全表扫描
7. is null，is not null也无法使用索引
8. like以通配符开头（‘%abc…’）mysql索引会变成全表扫描的操作
9. 字符串不加单引号会导致索引失效
10. 少用or，用它连接索引会失效

小表驱动大表：小的数据集，驱动大的数据集

select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id=B.id

当B的数据集必须小于A表的数据集时，用in优于exists

select * from A where exists (select 1 from B where B.id=A.id)
等价于
for select * from A
for select * from B where B.id=a.id

当A表的数据集小于B表的数据集时，用exists优于in

• EXISTS

  select ... from table where exists (subquery)

  该语法可理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE OR FALSE）来决定主查询的数据结果是否得以保留

为排序使用索引

mysql两种排序方式：文件排序或者扫描有序索引排序

mysql能为排序 与查询使用相同的索引

key a_b_c (a,b,c)
order by可以使用索引最左前缀
-order by a
-order by a,b
-order by a,b,c
-order by a desc,b desc,c desc
如果where使用索引的最左前缀定义为常量，则order by能使用索引
-where a=const order by b,c
-where a=const and b=const order by c
-where a=const order by b,c 
-where a=const and b>const order by b,c
不能用索引排序
-order by a asc,b desc,c desc 排序不一致
-where g=const order by b,c 丢失a索引
-where a=const order by c 丢失b索引
-where a=const order by a,d d不是索引的一部分
-where a in (...) order by b,c 对于排序来说，多个相等条件也是范围查询

group by关键字优化

• 实质是先排序后进行分组，遵照索引建的最佳左前缀法则
• 当无法使用索引列，增大max_length_for_sort_data参数的设置+sort_buffer_size的设置
• where高于having，能写在where限定的条件就不要去having限定了

慢查询日志

默认：show variables like '%slow_query_log%'

开启：set global slow_query_log_1;

怎么样的sql才会记录到日志里？

在mysql源码里是判断大于long_query_time，而非大于等于

设置慢的阙值时间：set global long_query_time=3，接着重新连接，或者新开一个会话才能看到。

show profile

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于sql调优的测量。

默认参数处于关闭状态，并保存最近十五次运行结果

查看结果show profiles

诊断sqlshow profile cpu,block io for query上面前一步问题sql的数字号码id

需要注意的结论：

• converting heap to myisam 查询结果太大，内存不够了往磁盘上搬了
• creating tmp table 创建临时表
• copying to tmp table on disk 把内存中临时表复制到磁盘，危险行为
• locked

mysql锁

读锁(共享）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排他）：当前写操作没完成前，它会阻断其他写锁和读锁

• 手动增加表锁lock table table_name read(write),table_name1 read(write)
• 查看表上加过的锁show open tables
• 解锁unlock tables

同一个会话中，添加读锁后，其他会话也可以读，但是本会话无法修改表，必须设置读锁时间方可，在其他会话中，如果修改表，则会处于阻塞状态，需要会话1解锁后方可继续完成。

如果添加写锁，当前session对锁定表的查询+更新+插入操作都可以执行，其他session对锁定表的查询被阻塞，需要等待锁被释放。

myisam在执行语句（select）前，会自动给设计的所有表加读锁，在执行cud操作前，会自动给涉及的表加写锁

mysql的表级锁有两种模式，表共享读锁（table read lock），表独占写锁（table write lock）

锁类型	可否兼容	读锁	写锁
读锁	是	是	否
写锁	是	否	否

总结：读锁会阻塞写，但不会阻塞读；写锁会把读和写都堵塞

myisqm的读写锁调度是写优先，这也是myisam

不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

并发事务带来的问题

1. 更新丢失：最后的更新覆盖了其他事务所做的更新
2. 脏读：事务A读取到了事务B已修改未提交的数据，并且在这个数据基础上做了操作。如果B回滚，A读取无效，不符合一致性
3. 不可重复读：事务A读取到了事务B已经提交的修改数据，不符合隔离性
4. 幻读：事务A读取到了事务B提交的新增数据不符合隔离性

区别：脏读是事务B里修改了数据，幻读是事务B里新增了数据

隔离级别	读数据一致性	脏读	不可重复读	幻读
读取未提交	最低，只能保证不读取物理上损坏的数据	是	是	是
读取已提交	语句级	否	是	是
可重复读	事务级	否	否	是
可序列化	最高级别，事务级	否	否	否

查看当前隔离级别show variables like 'tx_isolation'

行表锁待更新…