MYSQL优化(innodb干货)

1、 后台线程后台线程后台线程后台线程内存池文件文件文件缓冲池redo缓冲池其他内存4个IO 线程1个master 线程1个锁线程1个错误监控线程 先决条件先决条件 辅助索引辅助索引 非唯一非唯一索引 (数据)缓冲池索引页存在存在 不存在不存在 合并合并master thread更新插入 插入缓冲是为了提高写性能 两次写是为了提高可靠性 时间复杂度为O(1)，比BTREE 效率还要快。Innodb会自动的根据访问的频率和模式为某些页建立哈希索引。使用哈希索引，读取和写入的速度会提高两倍，对于辅助索引的join操作性能会提高5倍。 自适应哈希索引只能用于等值查询： select * from tab

2、le where name=张三; select * from table where age20 and age30; SHOW ENGINE INNODB STATUS; mysql在启动时会读取一个配置文件，定位数据库的各种文件位置以及制定某种初始化参数。 数据库文件 PID文件 二进制文件12恢复 recovery复制 repication可以使用二进制文件进行point-in-time的恢复主从复制SHOW variable LIKE datadir; tablespace: 段(segment)、区(extent)、页(page) 12CASCADESET NULL当父表进行upd

3、ate或delete操作时，子表数据同时进行update或delete操作当父表进行update或delete操作时，子表数据更新为NULL34NO ACTION当父表进行update或delete操作时，子表数据不进行操作RESTRICT当父表进行update或delete操作时，抛出错误，默认外键设置InnoDB会对外键自动添加索引索引哈希索引BTree索引聚集索引辅助聚集索引联合索引单一索引 聚集索引是按照每张表的主键构造一棵BTree树，叶节点存放整张表行记录数据，聚集索引的叶节点成为数据页。 1叶节点存放整行数据，查询速度快2针对主键排序和范围查询速度快3一张表只能有一个聚集索引 辅

4、助聚集索引，及非聚集索引。 1一张表可以由多个聚集索引2叶节点不包含全部数据3有机会使用插入缓存 对于索引的添加和删除操作，新创建一张临时表，然后把数据导入临时表，删除原表，最好把临时表名字改为原表名。 针对非聚集索引，InnoDB实现了一种快速索引创建方法，避免数据复制和临时表的创建。 Btree索引正确使用：高选择性、20% 左侧原则 一、索引要占用磁盘空间。通常情况下，这个问题不是非常突出。不过，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果你有一个非常大的表，索引文件的大小可能达到操作系统允许的最大文件限制。第二，对于需要写入数据的操作，比如delet

5、e、update及insert操作，索引会降低他们的速度。这是因为mysql不仅要把改动数据写入数据文件，而且他还要把这些改动写入索引文件。 由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获 取排它锁，也就是说只能读不能写入。 由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁，允许事务删除或者更新一条数据1共享锁 S Lock2排他锁 X Lock1原子性整个事务是不可分离的工作单位2一致性事务将数据从一种状态转变为下一种一致状态，事务开始和结束数据完整性没有被破坏3隔离性一个事务的影响在该事务提交前对其他事务不可见，通过锁来实现4持久性事务一旦提交，结果是永久性的

6、当两个事务同时进行时，即使事务没有提交，所做的修改也会对事务内的查询做出影响， 这种级别显然很不安全。修改时，会对该行加上行共享锁。 只有在事务提交后，才会对另一个事务产生影响，修改时，会对该行加上行共享锁。1READ UNCOMMITTED2READ COMMITTED3REPEATABLE READ4SERIALIZABLE 当两个事务同时进行时，其中一个事务修改数据对另一个事务不会造成影响，即使修改的事务已经提交也不会对另一个事务造成影响。修改时，会对该行加上行共享锁 事务隔离级别最严厉，在进行查询时就会对表或行加上共享锁，其他事务对该表将只能进行读操作，而不能进行写操作。1、开启查询缓

7、存。 CURDATE()、now()不会使用查询缓存 使用变量替代2、当只要一行数据时使用 LIMIT 13、like 字符%4、在Join表的时候使用相当类型的例，并将其索引5、尽可能避免使用SELECT *6、字段尽可能的使用 NOT NULL , is null 可能会导致全表查询7、应尽量避免在 where 子句中使用!=或操作符，or in等关键词，否则将引擎放弃使用索引而进行全表扫描。，=，between，in，及某些时候的like。in的值不要超过1000个union all - union - exists - between - in - or、8、避免在 where 子句中

8、对字段进行表达式操作、函数操作，这将导致引擎放弃使用索引而进行全表扫描9、用好条件中字段的使用顺序 select * from table where id1000 and name=XXX;10、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。11、先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定12、尽量避免大事务操作，提高系统并发能力13、充分利用连接条件（条件越多越快）14、使用视图