mysql-注意点
按锁粒度:
全局锁: 锁的是整个database. 由mysql的sql layer层实现。
表级锁:锁的是某个table.由mysql的sql layer层实现。
行级锁:锁的是某行数据。由存储引擎实现。
InnoDB行级锁是通过给索引上的索引项加锁来实现的,只有通过索引检索的数据,InnoDB才使用行级锁。
表级锁和行级锁的区别:
表级锁:开销小,枷锁快。不会出现死锁。锁定力度大,发生锁冲突的概率高,并发度低
行级锁:开销大,枷锁慢。会出现死锁。锁定粒度最小,发生锁冲突的概率低,并发度高。
锁的功能:
共享读锁和排他写锁
锁的实现方式:
悲观锁和乐观锁
8.3 事务并发问题
在事务的并发操作中可能会出现一些问题:
l 丢失更新:一个事务更新之后,另一个事务也更新了,但是第二个事务回滚了,则第一个事务也被回滚了。
l 脏读:一个事务读取到另一个事务未提交的数据。
l 不可重复读:一个事务因读取到另一个事务已提交的数据。导致对同一条记录读取两次以上的结果不一致。
update操作
l 幻读:一个事务因读取到另一个事务已提交的数据。导致对同一张表读取两次以上的结果不一致。insert、
delete操作
隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能影响也越大
索引
优点:提高检索效率,降低IO成本
缺点:索引占用空间,降低表更新效率
聚集索引:在InnoDB里的主键索引,索引的叶子节点存的数据是整行数据
辅助索引:在InnoDB里的非主键索引,索引的叶子节点存的数据是整行数据的主键
哪些情况创建索引
1. 主键自动创建唯一索引
2. 频繁查询的字段
3. 多表关联字段
4. 查询中排序的字段
哪些情况不需要创建索引
1. 表记录太少
2. 频繁更新的字段
覆盖索引:只查询索引的列
避免索引失效:
1.最佳左前缀法则: 如果索引了多个列,查询从索引的最左列开始,不跳过索引中的列
2. 不要在索引字段上做计算
3. 减少select *
4. 索引字段不要使用!=,or, 不要判断null,
5. 索引字段使用like不要以通配符开头(使用覆盖索引)解决
日志:
二进制日志(bin log)记录了所有的ddl, dml ,主要用于实现mysql主从复制、数据备份、数据恢复
慢查询日志、事务日志(redo日志)、中继日志(从机可以从中继日志中获取到主机同步过来的sql)
B+树和B树的最大区别:
B树非叶子节点和叶子节点都会存储数据
B+树只有叶子节点才会存储数据
ü1. 首先需要使用慢查询功能,去获取所有查询时间比较长的SQL语句
ü2. 其次使用explain命令去查看有问题的SQL的执行计划
ü3. 最后可以使用show profile[s] 查看有问题的SQL的性能使用情况
l 通常我们是使用的explain,以及slow query log都无法做到精确分析,但是Query Profiler却可以定位出一条
SQL语句执行的各种资源消耗情况,比如CPU,IO等,以及该SQL执行所耗费的时间等。不过该工具只有在
MYSQL 5.0.37以及以上版本中才有实现。
ü4. 优化改造SQL语句(需要对于需求有很好的理解,比如查询某个人最近半年的银行流水,银行只会提供最近一
年内的流水,还有的只提供最近半年的查询)
mysql性能优化
服务器层面:配置高
表结构设计:创建合理的冗余字段。减少关联查询。 字段太多的大表,考虑拆表。
通常不被使用的字段或存储数据比较多的字段,考虑拆表
sql语句优化:
索引优化
limit优化:如果预估查询结果是一条数据,使用Limit 1, 可以停止全表扫描
limit offset, size : offset非常大的时候,会导致mysql扫描大量不需要的行然后抛弃掉。解决方案,使用order by 和索引覆盖
* LIMIT的优化问题,其实是OFFSET的问题,它会导致MySql扫描大量不需要的行然后再抛弃掉。
* 解决方案:使用order by 和索引覆盖
原SQL(如果film ``表中的记录有10020条):
SELECT film_id, description FROM film LIMIT 10000 , 20 ;
优化的SQL:
SELECT film_id, description FROM film ORDER BY title LIMIT 20 ;
11.3.3其他优化
l 尽量不使用count(*)、尽量使用count(主键)
COUNT(*):查询行数,是会遍历所有的行、所有的列。
COUNT(列):查询指定列不为null的行数(过滤null),如果列可以为空,则COUNT()不等于 COUNT(列),
除非指定的列是非空的列才会让COUNT()等于COUNT(列)
COUNT(伪列):比如COUNT(1)
l JOIN两张表的关联字段最好都建立索引,而且最好字段类型是一样的。
SELECT * FROM orders o LEFT JOIN user u on o.user_id = u.id
orders表中的user_id和user表中的id,类型要一致
l WHERE条件中尽量不要使用1=1、not in语句(建议使用not exists)、
l 不用 MYSQL 内置的函数,因为内置函数不会建立查询缓存。