锁

1. 表锁

表锁开销小，加锁快，不会出现死锁，并发度低
一般update和delete操作会使用表级锁。全表扫描和 group by 也会使用表级锁

2.行锁

行锁开销大，加锁慢，会出现死锁（Innodb 对死锁有自动回滚机制），并发度高

锁阀值： 这个值表示一个锁超过60秒就会被释放，并报错
可以查看变量innodb_lock_wait_timeout

show variables like 'innodb_lock_wait_timeout';

结果显示

image.png

// 查看全局锁阀值
show global variables like 'innodb_lock_wait_timeout'
// 设置全局锁阀值
set global variables innodb_lock_wait_timeout=100

3.锁在Myisam、Innodb引擎上的表现

Myisam是表级锁。
加读锁不会阻塞读，但会阻塞写。加写锁会阻塞所有。

Innodb是行级锁，是存储引擎通过索引加锁实现的。只有通过索引检索出来的数据，InnoDB才会使用行级锁，否则InnoDB还是回使用表级锁。

缓存优化

1.mysql缓存查询状态

show variables like '%query_cache%'

2.缓存失效场景

a）大小写不同的 sql语句
关键字大小写，保留关键字 "``",where统一条件使用单引号或者双引号
因为query cache在内存中是以hash结构来映射的，所以 sql语句的规范尽量保持一致（很多框架目前都有 ORM ,尽量少的直接手写 sql）

b）事务隔离级别为 serializable 查询语句不缓存
c）mysql函数 now(),current_date()等
d）用户自定义函数
e）存储函数
f）临时表
g）局部变量
h）权限不足
i）子查询
j）预存储语句

如何优化查询缓存（提高查询缓存命中率）
1.通过分区表提高缓存命中率 。
2.尽量一次性写入
3.指定query_cache_type=2(demand),查询时自己定义 SQL_CACHE,SQL_NO_CACHE
4.基于数据库连接设置或者运行缓存
5.对于写操作非常频繁的的表，不要使用缓存，否则会造成缓存频繁修改，没有意义，使用query_cache_type=0设置

对于 innodb存储引擎有几个关键字段需要注意
1.innodb_buffer_pool：数据库索引、表数据缓存
2.innodb_buffer_pool_read_requests：读的次数
3.innodb_buffer_pool_reads：从磁盘读取的次数
4.innodb_buffer_pool_pages_data：有数据的缓存页数
5.innodb_buffer_pool_pages_total：缓存页数的总数量 16k * 65535 = 8M
6.innodb_buffer_pool_pages_free 没有使用缓存的页数，这个数字越大使用内存越小，这个数字越小需要调大内存在my.cnf中通过innodb_buffer_pool_size字段设置内存大小

命中率公式 ： (innodb_buffer_pool_read_requests - innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests

注意：
缓存并不是所有场景都能带来预期的性能改善
在大量写操作的场景时， cache机制可能会让性能下降。因为每次写操作会让系统去做 cache失效操作，这样会造成不小的开销。

另外 cache的访问是由一个全局锁来控制的，这样大量的查询会被阻塞，直到锁被释放。

很多时候我们会将热数据放入缓存数据库（memcached、 redis等）以减轻 数据库的压力

索引优化

1. 优化原则

通过 profile跟explain分析

1. 如果语句执行时间超出容忍范围，优化表设计、索引、sql语句
2. 如果语句等待时间过长，考虑调整服务器参数（缓冲区、线程数）必要时需要升级 内存、cpu

2.索引

关系型数据库存储是有序的，正常就是直接进行全表扫描，建立 btree或者 hash索引以后，通过索引来查询数据能够大大的提升查询效率，注意：一条数据查询只会用一个索引

3.索引类型

Btree：是Myisam、InnoDB 默认的索引，对于排好序的数据能够快速查找到结果
Hash：虽然 hash性能很高，但不能进行范围查询、排序等。使用过 memcache、redis之类的缓存数据库应该都能理解 通过 set的数据想用 get获取一个范围数据很难。

4.使用索引的场景

不以通配符%开头的like语句

// 假设此时field1为索引
SELECT field1,field2 FROM Table1 WHERE field1 LIKE'test%';

order by和 group by 使用索引

// 假设此时field1为索引
SELECT field1,field2 FROM Table1 ORDER BY field1
SELECT field1,field2 FROM Table1 GROUP BY field1

对于范围使用索引if null , between> , =,<等

// 假设此时field1为索引
SELECT field1,field2 FROM Table1 WHERE field1 > 100

TODO 未完待续