MySQL

本文主要是介绍MySQL，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

基于锁的属性分类：共享锁、排他锁。
基于锁的粒度分类：全局锁、表锁、行锁、记录锁、间隙锁、临键锁。
基于锁的状态分类：意向共享锁、意向排它锁。

全局锁：
对整个数据库实例加锁，MySQL提供了加全局读锁的方法，命令是Flush tables with read lock，如果需要整库处于只读状态，可以使用此条命令；以下语句会被阻塞：数据更新语句（增删改）、数据定义语句（建表、修改表结构等）和更新类事务的提交语句。
全局锁使用场景：全库逻辑备份，使库处于只读状态，但是这样子备份会导致很大问题（暂停写入，主从同步延迟）。

问题：
insert，update，delete要不要加锁

事务隔离级别：
读未提交（Read Uncommitted）：脏读，不可重复读，幻读
读提交（Read Commited）：不可重复读，幻读
可重复读（Repeatable Read）：幻读（对InnoDB不可能）
串行化（Serializable）：

不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

MySQL可以对global，session，下次事务设置隔离级别。

记录锁(Record Locks)、间隙锁(Gap Locks)、临键锁（Next-Key Lock）都是排它锁
间隙不锁记录本身，临键锁记录和间隙。

产生间隙锁的条件（RR事务隔离级别下）：记录前后的开放区间
使用普通索引锁定；
使用多列唯一索引；
使用唯一索引锁定多行记录或不存在记录。

innodb_locks_unsafe_for_binlog：默认值为OFF，即启用间隙锁

间隙锁只有在事务隔离级别 RR 中才会产生；
唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现 幻读 现象；
普通索引的间隙，优先以普通索引排序，然后再根据主键索引排序（多普通索引情况还未研究）；
没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加；

在 MySQL 可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说 MVCC 对于幻读的解决时不彻底的。

autocommit=1
手动start transaction，依然需要手动commit
未手动start transaction，则不需要手动commit

哈希索引和B+Tree索引：
需要存储行指针
无序（列值本身对顺序无意义则适合，比如URL）
不支持部分列
只支持等值，不支持范围
哈希冲突多查找效率会退化
需要扩容
查找快速
长键值缩为短索引

where exists (not exists)
using
USE INDEX(PRIMARY)
SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0)) AS red FROM items;
ON DUPLICATE KEY UPDATE
LEAST()、GREATEST()、LENGHT()、ISNULL()、NULLIFL()、IF()和COALESCE()、RAND() 、ROUND(3.333, 3)、
MD5()、SHA1()或者UUID()，UNHEX()函数转换UUID值为16字节的数字，检索时可以通过HEX()函数来格式化为十六进制格式。
INET_ATON()和INET_NTOA()
CRC32(), FNV64()函数作为哈希函数, conv(right(md5('www.mysql.com'), 16), 16, 10)
FROM DUAL
SET AUTOCOMMIT = 1
ORDER BY SUSTRING（column，length）
SELECT SQL_NO_CACHE COUNT(*)
可以配置MySQL的SQL_MODE来禁止不可能的日期
DROP TABLE IF EXISTS my_summary_new, my_summary_old;
CREATE TABLE my_summary_new LIKE my_summary;
RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;
FLUSH TABLES WITH READ LOCK; UNLOCK TABLES;
DISABLE KEYS只对非唯一索引有效，ENABLE KEYS;
使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。
触发器
SELECT SUM(staff_id = 2), SUM(customer_id = 584)
OPTIMIZE TABLE命令
SET AUTOCOMMIT=0;
EXPLAIN PARTITIONS
CREATE ALGORITHM=TEMPTABLE VIEW
show full columns from

BINARY和VARBINARY，blob, text, bit, enum, set,

MySQL Proxy
.frm文件, .MYD文件

显式松散索引：
假设我们有如下索引（a，b），有下面的查询：
mysql> SELECT ... FROM tbl WHERE b BETWEEN 2 AND 3;
mysql 优化器无法自动进行，必须在查询语句中进行必要的声明。 select * from xxx where B = xxx group by A; 添加 group by 字段后，会先根据 A 索引分组后，会在每个 A 的范围内使用索引进行快速查询定位所需要的 B 列，这就叫做松散索引扫描，比新建一个索引的效率会慢 A 的 distinct 倍，但省去了新索引的消耗。

取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。
人们会告诉我们说这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码片段的复用性

主从：
1、高性能方面：主从复制通过水平扩展的方式，解决了原来单点故障的问题，并且原来的并发都集中到了一台Mysql服务器中，现在将单点负载分散到了多台机器上，实现读写分离，不会因为写操作过长锁表而导致读服务不能进行的问题，提高了服务器的整体性能。
2、可靠性方面：主从在对外提供服务的时候，若是主库挂了，会有通过主从切换，选择其中的一台Slave作为Master；若是Slave挂了，还有其它的Slave提供读服务，提高了系统的可靠性和稳定性。

策略方式：
「同步策略」：Master会等待所有的Slave都回应后才会提交，这个主从的同步的性能会严重的影响。
「半同步策略」：Master至少会等待一个Slave回应后提交。
「异步策略」：Master不用等待Slave回应就可以提交。
「延迟策略」：Slave要落后于Master指定的时间。

<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

这篇关于MySQL的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！