MySQL引擎、索引和优化（Ali）

　　存储引擎，MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。InnoDB存储引擎是5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定。另外还有常见的MyISAM存储引擎，它拥有较高的插入，查询速度，但不支持事务。所以，很明显：插入不频繁，查询非常频繁，没有事务，用MyISAM；可靠性要求高，表更新频繁，事务多，用InnoDB。

//#查看本机MySQL提供的什么存储引擎
//show ENGINES;
//
//#查看Mysql当前默认的存储引擎
//show variables like ‘%storage_engine%‘;
//
//#查看当前表用什么存储引擎（DDL最后）
//show create table idc_work_order_main;

//#修改当前表的存储引擎
//ALTER TABLE idc_work_order_main ENGINE = ‘MyISAM‘

　　MySQL官方对InnoDB是这样解释的，InnoDB给MySQL提供了具有提交、回滚和奔溃恢复能力的事务安全存储引擎。InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。

　　如果使用innodb存储引擎，我们知道该引擎最主要的特点是transactional和row lock(行级锁)。按理说不会出现表锁才对，但是事实上还是会出现锁表的的情况，也会比较严重，下面主要就是来探讨一下这个问题。查看mysql文档会发现，虽然innodb使用的的row lock(行级锁)，但是在处理具有auto increment字段的表的时候，会使用一种特殊的表锁：AUTO-INC。简单来说就是Innodb会在内存里保存一个计数器用来记录 auto_increment的值，当插入数据时，就会用一个表锁来锁住这个计数器，直到插入结束。一条一条插入问题不大，但是如果高并发插入，就会造成 sql阻塞。

　　解决方案：1.不使用auto increment字段，自己维护主键生成。该方法中选择主键生成策略很重要, 要综合考虑简单和效率问题。假设使用uuid，虽然简单但是会造成该表的主键效率很低（innodb的主键是特殊的index，其他的index会引用主键）。2.升级到最新的5.2版本。

//#MySQL5.1.22版本之前，这种方式的特点就是“表级锁定”，并发性较差
//innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表锁)
//
//#推荐使用“consecutive”，并发性相对较高，特点是，即保证同一条insert语句中新插入的auto_increment id都是连续的
//innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
//
//#这种模式是来一个分配一个，而不会锁表，只会锁住分配id的*过程*，和innodb_autoinc_lock_mode = 1的区别在于
//#不会预分配多个，这种方式并发性最高。但是在replication中当binlog_format为statement-based时
//#（简称SBR statement-based replication）存在问题，因为是来一个分配一个，这样当并发执行时，
//#“Bulk inserts”在分配时会同时向其他的INSERT分配，会出现主从不一致（从库执行结果和主库执行结果不一样），因为binlog只会记录开始的 insert id。
//innodb_autoinc_lock_mode = 2 (“interleaved” lock mode：全部使用新方式，不安全，不适合replication)

二、索引　　

　　索引是帮助MySQL高效获取数据的数据结构。介绍MySQL的索引结构，索引原理，进而学习索引的优化。MySQL的索引结构包括：B-tree索引、Tree索引、哈希索引(Hash)、位图索引(Bitmap)、跳表。

MySQL引擎、索引和优化（Ali）

标签：