mysql 杂记-Toy模板网

这篇具有很好参考价值的文章主要介绍了mysql 杂记。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

意向锁
- 如果事务B申请表锁时，表中存在行锁，则需遍历表中每一行是否加锁，效率低。意向锁解决了此问题
- 事务申请行锁时，要先申请意向锁，这样事务B在申请表锁时，如果存在意向锁就会阻塞，不用在遍历表。
mysql一致性非锁定读
先获取行的数据，如果行被加X锁，在读取行的快照。
in 和not in不能过滤null值
mysql前三级隔离级别用mvcc实现，select语句不会加锁，但是dml语句会用到锁，4级完全用锁来实现
X锁保持到事务结束
悲观锁和乐观锁
1.悲观锁，不相信在其他人也参与的情况下，自己拿到的数据是最新的，所以不允许其他人访问。成就自己，恶心别人。
2. 乐观锁，不管有没有其他人参与，总是相信自己拿到的数据是最新的，在最后更新数据的时候，在通过版本号判断自己开始得到数据是不是最新的，如果不是重复执行先前的操作，直到是为止。参与的人少的情况下，共赢。在参与的人多的情况下，恶心自己，成就别人。
获取某列值时，如果连接的表中都有此列，优先使用驱动表的列。
先连接后筛选，会因为被驱动表的筛选而让驱动表的记录被筛去，解决方法，先筛选在连接。
任何值（包括和null本身）与null对比和函数运算操作结果都为null
SELECT * FROM club_member c WHERE c.member_type!=2
c.member_type 为null查不到
慢 sql分析
```
sql select * from club_card_record where 1=1  and create_date>='2018-12-26 00:00:00' and create_date<='2018-12-26 23:59:59' and id>6322442 order by id asc limit 10000
```
原因
使用了主键索引找到所有id>6322442的记录（大约一千万行，所以很慢）在做create_date刷选，然后取前10000行。
使用了create_date索引，找到相应的create_date项，然后在到叶子节点得到id索引，并进行id>6322442刷选，取前10000行id值。由于并不需要查询实际的row记录，所以很快
列的先筛选在去重
```
SELECT COUNT(DISTINCT IF(c.`merchant_code`!=c.`super_merchant_code`,c.`merchant_code`,-1))-1 FROM club_card c WHERE c.`create_time`>'2018-01-01 00:00:00' AND c.`create_time`<'2018-12-31 23:59:59' AND c.`card_type` IN (0,1,2,3,4,5,7,8,9);
```
IF(c.merchant_code!=c.super_merchant_code,c.merchant_code,-1) 结果是满足条件的为merchant_code,不满足条件为-1，这样的话出现一堆重复的-1，在distinct去重就剩下-1和想要的值，在-1就是筛选去重后的结果
缺省Oracle在Order by 时缺省认为null是最大值，所以如果是ASC升序则排在最后，DESC降序则排在最前,mysql则相反。
mysql中count(*),count(id),count(1)都是遍历全索引树(index),count(非索引列)遍历全表(all)
explain rows 猜想
感觉它是根据索引来预测的值，如果where 后的刷选列没有索引，它反回的就是全表数据量。如
SELECT id FROM tourism.tourism_product_inventory WHERE remaining_inventory>0 AND ts_inventory_status=1
虽然做了筛选，但是rows值为全表数据。但实际数据量是刷选后的行数。
关于distinct
加上distinct可能会对表进行排序，因而降低效率。
相关子查询可能会使内表的索引失效。
sql效率高，执行计划必然好，但是执行计划好，sql不一定执行快（也可能是我对执行计划并不了解）
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接署的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时，

19.mysql in 索引使用情况
1. 普通索引超过150个左右就不会使用了
2. 主键索引试验500个仍然会使用索引
以上测试可能会根据不同的环境而有所变化，但是可以肯定的是对于普通索引，in后的值超过一定数量便不会再使用索引

如果字段名和mysql关键字冲突，可能会出现莫名其妙的错误，如，你有一个语法错误。
多表链接
a b c
记录数 1 3 3
对应关系链接记录数
顺序关系 a-b b-c a left join b left join c 3条
非顺序关系 a-b a-c a left join b left join c 3x3=9条
只有顺序对应关系才能保证是预料结果，其他的必须谨慎分析。
插入语句在没有提交事务前就已经可以拿到主键列。
如无必要，应以较少记录表作为驱动表。
mysql null和空值列不参与数值比较
执行计划extra
- using mrr：针对type=rang 时使用了mrr优化技术
- using index condition：使用了icp优化技术
characterEncoding=UTF-8 类似http content-type:charset=utf-8
jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=UTF-8
grant
grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址;
权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。
- 当权限1,权限2,…权限n被all privileges或者all代替，表示赋予用户全部权限。
- 当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。
- 用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%'表示从任何地址连接。
mysql 默认排序不定
InnoDB有三种行锁的算法：
- Record Lock：单个行记录上的锁。
- Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
- Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。
在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。
mysql主从连接协议
jdbc:mysql:replication://master,slave1,slave2,slave3/test
mysql常见运行状态参数

参数	解释
qcache_hits	缓存命中次数
com_select	查询次数（缓存未命中）。缓存命中率p=qcache_hits/(qcache_hits+com_select)
qcache_not_cached	没有利用缓存次数（如，查询语句因为一些原因没能被缓存，则该值会增大）
com_*	数据操作次数（com_select,com_update,com_delete）
qcache_lowmen_prunes	因为内存不足而导致缓存失效的次数
qcache_inserts	缓存查询的次数，如果com_select和qcache_inserts值相当，那么可能的原因是：修改操作使缓存失效，而查询总是发生在缓存失效之后。这会降低系统性能。理想状态qcache_inserts远远小于com_select 3:1说明缓存是有效的，最好是10:1，如果没有达到这个比率那么就可以考虑禁用缓存了。
qcache_free_memory	因为内存不足而导致缓存失效的次数
qcache_queries_in_cache	缓存剩余的块数， qcache_total_blocks:缓存总块数，如果acache_free_blocks=qcache_total_blocks/2那么说明缓存中的碎片很多。

禁止使用mysql子查询，难于理解，容易出问题
慎用force Index
innodb使用大字段text，blob
- 对于blob，text，varchar(8099)这样的大字段，innodb在发生溢出时只会存放前768字节在数据页中，而剩余的数据则会存储在溢出段中
- innodb的块大小默认为16kb，由于innodb存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k（8098字节），否则会溢出；
- mysql在操作数据的时候，以page为单位，不管是更新，插入，删除一行数据，都需要将那行数据所在的page读到内存中，然后在进行操作，这样就存在一个命中率的问题，如果一个page中能够相对的存放足够多的行，那么命中率就会相对高一些，性能就会有提升；
in 和 exists的区别
exists，那么外层表为驱动表，先被访问；in，那么先执行子查询
死锁原因
死锁都是多种不同的流程在不同的线程访问同一些数据造成的文章来源地址https://www.toymoban.com/news/detail-490540.html