mysql

mysql 慢查询监控

mysql5.6版本以上，取消了参数log-slow-queries，更改为slow-query-log-file
还需要加上 slow_query_log = on 否则，还是没用 log-slow-queries = /var/log/mysql/mysql-slow.log;
用下面的三个参数；
slow_query_log = on
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 2
mysqldumpslow -s r -t 10 /data/mysql/localhost-slow.log

表设计是否需要外键

1：在大数量的情况下，使用外键约束会导致很差的性能。一般互联网应想都不要去想外键这种东西了，连表连接查询最好都不要使用
2：大数据量时进行表的水平切分，像外键约束、触发器、存储过程这些都是禁区
3：数据完整性是业务的需要，因此得由业务层的应用程序来控制
4：外键会导致表结构非常混乱，几乎是动都不能去动，一层套一层的外键约束，在表很多的情况下很可能会导致循环约束

数据和索引查询

/查询MySQL数据库里面的所有数据库各自占用大小/
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),’ MB’) as data_size,
concat(truncate(sum(index_length)/1024/1024,2),‘MB’) as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;

/查询MySQL数据库里面的单个数据库占用大小/
SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),‘MB’) AS data_size,
CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),‘MB’) AS max_data_size,
CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),‘MB’) AS data_free,
CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),‘MB’) AS index_size
FROM information_schema.tables WHERE TABLE_SCHEMA = ‘数据库名’;

/查询MySQL数据库里面的单个数据库所有表各自占用大小/
select TABLE_NAME, concat(truncate(data_length/1024/1024,2),’ MB’) as data_size,
concat(truncate(index_length/1024/1024,2),’ MB’) as index_size
from information_schema.tables where TABLE_SCHEMA = ‘数据库名’
group by TABLE_NAME
order by data_length desc;

/查询MySQL数据库里面的单个数据库指定表占用大小/
SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),‘MB’) AS data_size,
CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),‘MB’) AS max_data_size,
CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),‘MB’) AS data_free,
CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),‘MB’) AS index_size
FROM information_schema.tables WHERE TABLE_NAME = ‘表名’;
原文链接：https://blog.csdn.net/hww9011/article/details/51482099

RROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

https://blog.csdn.net/a1173537204/article/details/88069724

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

快照读：简单的select操作，属于快照读，不加锁 select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁;
select * from table where ? lock in share mode;(S锁)
select * from table where ? for update;(X锁)
insert into table values (…);(X锁)
update table set ? where ?;(X锁)
delete from table where ?;(X锁)

共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
参考：https://blog.csdn.net/soonfly/article/details/70238902

事务的隔离级别

读未提交 Read uncommited(脏读问题)：一个事务，可以看到另外一个事务还没有commit的数据；

读提交 Read commited(不可重复读)： 解决了脏读问题，但还是有不可重复读问题；读事务A开始查询一个数据，这个时候另外一个写事务B开启事务修改了该数据并提交，导致事务A再次查询这个数据的时候前后不一致；

重复读 Repeatable read : 解决了不可重复读问题，但还是有幻读问题；事务A开启事务后，就不允许写事务B进行修改操作，所以解决了重复读问题；但不可重复读对应的是修改update操作,但是解决不了insert操作，比如事务A查询消费记录，另外一个事务B又insert了一条消费记录，等事务A再提交的时候就会出现事务B插入的消费记录，似乎出现了幻觉，这就是幻读； 另外，应用可以通过间隙锁的方式解决幻读问题；

串行化 Serializable 解决了幻读问题；可以避免脏读，不可重复读，幻读问题，但是效率低下；

Mysql 默认 Repeatable read级别；
oracle sqlserver 默认 Read commited级别；

解释计划

explain只能解释select语句；
id 表示执行的顺序，id越大越先执行，id一样的从上往下执行
select_type 表示查询类型；

• simple：表示不需要union操作或者不包含子查询的简单查询。 primary：表示最外层查询。
  union：union操作中第二个及之后的查询。 dependent
  union：union操作中第二个及之后的查询，并且该查询依赖于外部查询。 subquery：子查询中的第一个查询。 dependent
  subquery：子查询中的第一个查询，并且该查询依赖于外部查询。 derived：派生表查询，既from字句中的子查询。
  materialized：物化查询。 uncacheable subquery：无法被缓存的子查询，对外部查询的每一行都需要重新进行查询。
  uncacheable union：union操作中第二个及之后的查询，并且该查询属于uncacheable subquery。

table 表名或者表的别名；
partitions 分区信息，非分区表为null；
type 访问类型，表示找到所查询数据的方法，也是本文重点介绍的属性。该属性的常见值如下，性能从好到差：

• NULL：无需访问表或者索引，比如获取一个索引列的最大值或最小值。
  system/const：当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。
  eq_ref：多表关联查询时，根据唯一非空索引进行查询的情况。
  ref：多表查询时，根据非唯一非空索引进行查询的情况。
  range：在一个索引上进行范围查找。
  index：遍历索引树查询，通常发生在查询结果只包含索引字段时。
  ALL：全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免。

possible_keys 表示mysql此次查询中可能使用的索引。
key 表示mysql实际在此次查询中使用的索引
key_len 表示mysql使用的索引的长度。该值越小越好。
ref 表示连接查询的连接条件。
rows 表示mysql估计此次查询所需读取的行数。该值越小越好。