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 内置的函数，因为内置函数不会建立查询缓存。