MYSQLMYSQL性能优化性能优化 令狐伟令狐伟 存储引擎存储引擎 MyISAM InnoDB表锁行锁OLAPOLTP不支持事务ACID事务、XA事务全文索引插入缓存、二次写、自适应哈希索引支持外键 存储引擎存储引擎 OLTP OLAP用户操作并发量大.事务处理时间较短.查询语句较简单，使用索引.复杂的查询较少. InnoDBInnoDB后台线程后台线程后台线程后台线程内存池文件文件文件缓冲池redo缓冲池其他内存4个IO 线程1个master 线程1个锁线程1个错误监控线程 InnoDB InnoDB 先决条件先决条件 辅助索引辅助索引 非唯一非唯一索引 (数据)缓冲池索引页存在存在 不存在不存在 合并合并master thread更新插入 InnoDBInnoDB 插入缓冲是为了提高写性能 两次写是为了提高可靠性 InnoDBInnoDB 时间复杂度为O(1)，比BTREE 效率还要快。Innodb会自动的根据访问的频率和模式为某些页建立哈希索引。使用哈希索引，读取和写入的速度会提高两倍，对于辅助索引的join操作性能会提高5倍。 自适应哈希索引只能用于等值查询： select * from table where name=张三; select * from table where age20 and age30; SHOW ENGINE INNODB STATUS; InnoDBInnoDB mysql在启动时会读取一个配置文件，定位数据库的各种文件位置以及制定某种初始化参数。 数据库文件 PID文件 二进制文件 InnoDBInnoDB可以使用二进制文件进行point-in-time的恢复主从复制SHOW variable LIKE datadir; InnoDBInnoDB InnoDBInnoDB tablespace: 段(segment)、区(extent)、页(page) 表空间中存放的数据包括数据、索引、插入缓存，以及一些其他数据。表空间中存放的数据包括数据、索引、插入缓存，以及一些其他数据。 ( (二次写缓存还是在共享的内存池中二次写缓存还是在共享的内存池中) ) InnoDBInnoDB当父表进行update或delete操作时，子表数据同时进行update或delete操作当父表进行update或delete操作时，子表数据更新为NULL当父表进行update或delete操作时，子表数据不进行操作当父表进行update或delete操作时，抛出错误，默认外键设置InnoDBInnoDB会对外键自动添加索引会对外键自动添加索引 索引索引索引哈希索引BTree索引聚集索引辅助聚集索引联合索引单一索引 索引索引 聚集索引是按照每张表的主键构造一棵BTree树，叶节点存放整张表行记录数据，聚集索引的叶节点成为数据页。 索引索引 辅助聚集索引，及非聚集索引。 索引索引 对于索引的添加和删除操作，新创建一张临时表，然后把数据导入临时表，删除原表，最好把临时表名字改为原表名。 针对非聚集索引，InnoDB实现了一种快速索引创建方法，避免数据复制和临时表的创建。 Btree索引正确使用：高选择性、20% 索引索引 左侧原则 索引索引 一、索引要占用磁盘空间。通常情况下，这个问题不是非常突出。不过，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果你有一个非常大的表，索引文件的大小可能达到操作系统允许的最大文件限制。第二，对于需要写入数据的操作，比如delete、update及insert操作，索引会降低他们的速度。这是因为mysql不仅要把改动数据写入数据文件，而且他还要把这些改动写入索引文件。 索引索引 锁锁 由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获 取排它锁，也就是说只能读不能写入。 由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁，允许事务删除或者更新一条数据 锁锁 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRXSELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;/;/查看正在锁的事务明细查看正在锁的事务明细SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;/SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;/查看正在锁的事务查看正在锁的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;/SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;/查看等待锁的事查看等待锁的事务务show status like innodb_row_lock%;/show status like innodb_row_lock%;/查看行锁争夺情况查看行锁争夺情况 事务事务 整个事务是不可分离的工作单位事务将数据从一种状态转变为下一种一致状态，事务开始和结束数据完整性没有被破坏一个事务的影响在该事务提交前对其他事务不可见，通过锁来实现事务一旦提交，结果是永久性的 事务事务 当两个事务同时进行时，即使事务没有提交，所做的修改也会对事务内的查询做出影响， 这种级别显然很不安全。修改时，会对该行加上行共享锁。 只有在事务提交后，才会对另一个事务产生影响，修改时，会对该行加上行共享锁。 当两个事务同时进行时，其中一个事务修改数据对另一个事务不会造成影响，即使修改的事务已经提交也不会对另一个事务造成影响。修改时，会对该行加上行共享锁 事务隔离级别最严厉，在进行查询时就会对表或行加上共享锁，其他事务对该表将只能进行读操作，而不能进行写操作。 优化建议优化建议 1、开启查询缓存。 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 子句中对字段进行表达式操作、函数操作，这将导致引擎放弃使用索引而进行全表扫描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、使用视图加速查询。把表的一个子集进行排序并创建视图，有时能加速查询15、能用DISTINCT的就不用GROUP BY （group by 操作特别慢）16、使用好范围查询，当使用索引时，InnoDB会锁住它不需要的元组20、尽量减少数据库的访问次数，转移到程序中The end,thank you!