Mysql(一)

MySql InnoDB引擎事务

死锁时指两个事务在同一资源上相互占用，并请求锁定对方占用的资源。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁，多个事务同时锁定同一个资源时，也会产生死锁。InnoDB目前处理死锁的方法是将持有最少行级排他锁的事务进行回滚。死锁发生以后只有部分或者完全回滚其中一个事务，才能打破死锁

mysql默认采用自动提交模式，也就是如不显示地开始一个事务，则每个查询都被当做一个事务执行提交操作

Mysql优化

schema优化，索引优化，查询优化齐头并进

schema优化

良好的schema设计原则是普遍适用的，尽可能保持任何东西小而简单总是好的

1. 尽量避免过度设计，例如会导致其复杂查询的schema设计，或者有多列的表设计
2. 使用小而简单的合适数据类型，除非真是数据模型中有确切的需要，否则应该尽可能地避免使用NULL值(索引会导致全表）
3. 尽可能使用相同的数据类型存储相似或相关的值，尤其是在关联条件中使用的列
4. 注意可变长度字符，在临时表和排序时可能悲观的按最大长度分配内粗
5. 尽量使用整形定义标识列
6. 避免使用Mysql已经遗弃的特性，例如指定浮点数的精度或者整数显示宽度
7. 小心使用ENUM和SET，最好避免使用BIT

创建高性能的索引：

查询流程：先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行，所i你可以包含一个或者多个列的值，如果多列索引，那么需要考虑顺序，因为mysql只能高效的使用索引的最左前缀列。

B-Tree索引：B+Tree，即每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历，InnoDB根据主键引用被索引的行，B-Tree索引 能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描，而是从索引的根节点开始进行搜索，跟节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找，通过比较节点页的值和要查找的值找到合适的指针进入下层子节点，最终存储引擎要么是找到对应的值，要么该记录不存在。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。如果查询中有某个列的范围哈寻，则其右边所有列都无法使用索引优化查找，例如like 'J%'后面的，故而索引的顺序时多么重要，在优化性能的时候可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

InnoDB有个功能叫做“自适应哈希索引”：当InnoDB注意到某些索引值被使用非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。

索引的优点：

1. 索引大大减少了服务器需要扫描的数据表
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机I/O变为顺序I/O

高性能的索引策略：

1. 独立的列：如果查询中的列不是独立的，即索引列不能时表达式的一部分，也不能时函数的参数，否则Mysql不会使用索引
2. 前缀索引：对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引
3. 多列索引：当服务器对多个索引做相交操作(通过有多个AND条件),通常意味着需要包含所有相关列的多列索引，而不是多个独立索引；当服务器需要对多个索引做联合操作时(通常有多个OR条件)，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上
4. 选择合适的索引列顺序：正确的顺序依赖于使用该索引的查询，并同时需要考虑如何更好地满足排序和分组的需要。索引首先按照最左列进行排序，其次时第二列.....索引，索引可以按照升序或者降序进行扫描，以满足order by、group by、distinct等子句

查询优化：

1. 关联查询优化：确保ON或者USING子句的列上有索引；确保GROUP BY和ORDER BY 的表达式中只涉及到一个表中的列，这样Mysql才能有可能使用索引来优化这个过程
2. 子查询优化：尽量使用关联查询代替子查询
3. 优化group by和Distinct：去重，分组，MySQL在优化器内部优化时会互相转换这两种查询，一般应用索引来优化
4. limit优化:配上order by，如果有索引，一般效率不错；假设每页显示20条数据，如果有第21条数据就显示下一页；另一种做法是先获取较多的缓存，每次从缓存种获取，如果集小于缓存值，就从缓存种获取，如果大于缓存值，页面就设计一个找到额外的按钮
5. UNION查询优化：MYSQL总是通过创建并填充临时表的方式来执行UNION查询。经常需要将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中。除非确实需要服务器消除重复的行，否则就一定要用UNION ALL, 不然会给临时表加上DISTINCT选项，导致对整个临时表左唯一性检查