mysql优化2

锁：
为了提高数据库的并发：大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁。
2种锁：
表锁：写锁比读锁优先级更高。
行级锁：最大程度提高并发处理。带来最大锁开销。

事务：事务内的语句要么全部成功，要么全部失败。
单条语句：每条单独的语句都是事务，其后隐含COMMIT。(开关autocommit默认为ON(1),如果为0一直到commit时才会提交，影响其他事务操作)
事务基本语法：以BEGIN TRANSACTION显式开始，以COMMIT或ROLLBACK显式结束。
事务ACID特性：atomicity,consistency,isolation,durability.
atomicity:事务是最小原子，操作要么全做，要么全不做。
Consistency:数据库操作完成前一致，操作完成后一致。
isolation:操作完成前，对其他事务不可见。
durability：完成后，数据永存。

隔离级别：read uncommitted,read committed,repeatable read,serializable
隔离级别    脏读    不可重复读    虚拟读取(幻读)    加锁读
未提交读    是    是        是        否
已提交读    否    是        是        否
可重复读    否    否        是        否
可序列化    否    否        否        是
mysql默认策略：并且通过间隙锁防止幻读。
mysql> show variables like "%isolation%";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

InnoDB是使用最广泛的引擎。
InnoDB在出现锁死时会返回错误，在mysql该引擎才支持事务(NDB cluster也支持)。
InnoDB行级锁工作的很好，很多情况避免加锁，提高效率。根据隔离级别自动加锁。
InnoDB支持热备份，崩溃损坏概率比MyISAM低。
MyISAM不支持事务和行级锁，支持全文索引(InnoDB+Sphinx来支持)。
---注:不得混合使用多引擎。
在autocommit禁用情况下才使用lock tables，否则不使用。不管什么引擎。

sysbench是一款开源的多线程性能测试工具，可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL。

选择优化的数据类型。
1.更小的通常更好。2.简单就好:INT比byte操作代价更低;用内建类型而不用字符串保存日期，用INT储存IP。3.尽量避免NULL:null增加储存空间，索引更复杂，建立索引避免设计到有NULL列。

陷阱：太多的列，太多的关联(一个查询最好在12个表以内作关联)，全能的枚举(防止过度使用枚举)，变相枚举

第一范式:表无重复字段，字段不再分；第二范式:减少字段列的沉余(耦合)。反范式。
employee    department    head(部门领导)
Jones        Accounting    Jones
Smith        Engigeering    Smith
Brown        Accounting    Jones
Green        Engineering    Smith
如果修改head，开销比较大,沉余严重。
可以分为2个表：
1表：
employee    department
Jones        Accounting
Smith        Engigeering
Brown        Accounting
Green        Engineering
2表：
department    head(部门领导)
Accounting    Jones
Engineering    Smith

缓存表和汇总表：有点像中间表的应用。几个表和一(缓存)，GROUP BY形成一表(汇总)，视图，便于查找。
计数器表：放一个int unsigned型进行计数。表小且快。
更快的读，更慢的写：使用额外索引，添加沉余列，创建缓存表，汇总表，试图。但是写变慢。

ALTER TABLE对大表是个大问题，MYSQL先创建一个新表，将旧表所有数据插入新表，然后删除旧表。因为MODIFY修改字段类型，针对数据每一行数据。
ALTER TABLE 允许使用ALTER COLUMN，MODIFY COLUMN，CHANGE COLUMN修改字段。但是字段内容不一样。
ALTER TABLE mytable ALTER COLUMN tableColumn SET DEFAULT 5;该命令只修改.frm文件，而不涉及表数据。
ALTER TABLE mytalbe MODIFY tableColumn INT DEFAULT 5;重新创建表，复制，删除。
类型修改，只修改.frm文件快但是有风险。
步骤：
1.创建一个结构相同的空表，并进行所需要的修改(如:enum('G','PG','PG-13','R','NC-17','PG-14'),添加'PG-14',但是添加需要在尾部添加，放在'PG-13'后面造成'PG-14'替代'R'.)
2.执行FLUSH TABLES WITH READ LOCK;关闭正在使用的表，并禁止任何表被打开。
3.交换.frm文件。
4.执行UNLOCK TABLES;释放第二步的读锁。
CREATE TABLE film_new LIKE film;
ALTER TABLE film_new MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17','PG-14') DEFAULT 'G';
FLUSH TABLE WITH READ LOCK;
系统操作:mv film.frm film-tmp.frm && mv film_new.frm film.frm &&  mv film_tmp.frm film_new.frm;
UNLOCK TABLES;
DROP TABLE film_new;


索引InnoDB中是根据B+TREE数据结构，之用Memory引擎支持hash索引，创建多列索引，索引根据定义索引顺序的第一个字段进行索引，其他字段在第一个字段重复时再索引。
索引就是快速定位数据在表的地址，加快ORDER BY 和GROUP BY操作(不用排序了)。小表可以直接全表扫描更高效，大表索引非常有用。
describe select * from suremployer where num-1=201500007 \G   索引不能用与函数，表达式，这样失去了索引意义。num是索引。
前缀索引长度应当在7个字节内。
如果表多个字段建立一个索引，只有第一个索引有效。and与or：
select * from film_actor where actor_id=1 and film_id=1;两个字段分别建立索引，查询很快。
select * from film_actor where actor_id=1 or film_id=1;5.0以后，因为都是索引没问题，有归并动作，稍慢。
select * from film_actor where actor_id=1 UNION ALL select * from film_actor where actor_id<>1 AND film_id=1;5.0之前。
索引可以减少锁定行。
SELECT * FROM table where sex='m' order by rating limit 10;如果没有索引将会很慢。