MySql优化基础

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySql优化基础，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7462字，纯文字阅读大概需要11分钟。

内容图文

MySql有一下三层逻辑架构：

第一层并不是MySQL独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
第二层是MySQL多数核心功能位置，包括查询解析、分析、优化、缓存以及所有的内置函数（日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信，这些API屏蔽了不同存储引擎之间的差异。

读写锁
在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁（Shared Lock）和排它锁（Exclusive Lock），也叫读锁（Read Lock）和写锁（Write Lock）。
读锁是共享的，写锁是排它的，一个写锁会阻塞其它写锁和读锁。
锁粒度
MySQL中不同的存储引擎支持不同的锁机制：MyISAM与MEMORY存储引擎采用表级锁；BDB存储引擎采用的是页级锁，也支持表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，默认采用行级锁。
表级锁：MySQL中开销最小的策略，加锁速度快，锁定整张表，粒度大。不会出现死锁，发生锁竞争的概率最高，并发度最低，性能最差。
行级锁：开销大，加锁速度慢，锁定一行数据，粒度小。会出现死锁，发生锁竞争的概率最低，并发读最高，性能高。
页级锁：开销和加锁速度介于表锁和行锁之间，锁定一页数据。会出现死锁，锁竞争概率、并发性、性能均位于表锁和行锁之间。

什么是事务？
事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么执行该组查询。如果其中有任何一条语句应为崩溃或其他原因无法执行，
那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。良好的事务必须满足四大特性。

事务的四大特性
l 原子性(Atomicity)
一个事务中的SQL，要么全部执行，要么全部不执行；
l 一致性(Consistency)
一个事务执行前后，数据库中的所有约束依然满足；
l 隔离性(Isolation)
一个事务执行过程中，数据不受另一个事务的影响；
l 持久性(Durability)
一个事务执行完成后，事务对数据的修改必须持久化到数据库中。

事务隔离级别
隔离性其实比想象的要复杂。在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

l READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也都是可见的
l READ COMMITTED（已提交读/不可重复读）：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的
l REPEATABLE READ（可重复读）：同一事务中多次读取同样的记录结果是一致的，MySQL默认
l SERIALIZABLE（序列化）：事务串行执行

死锁
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。多个事务同时锁定同一个资源时，也会产生死锁。如下：
事务1：
START TRANSACTION;
UPDATE customer SET first_name = ‘LINDA’ WHERE customer_id=1;
UPDATE customer SET first_name = ‘LISA’ WHERE customer_id=2;
COMMIT;
事务2：
START TRANSACTION;
UPDATE customer SET last_name = ‘JONES’ WHERE customer_id=2;
UPDATE customer SET last_name = ‘LEE’ WHERE customer_id=1;
COMMIT;
如果刚好两个事务都执行了第一个条update语句，更新了一行数据，同时也锁定了该行数据。接着两个事务都尝试去执行第二条update语句，发现该行数据已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入了死循环。

事务日志
存储引擎在修改表的数据时，使用事务日志，使得只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久化到磁盘。
事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。我们通常称为预写式日志，修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。

查看数据库存储引擎支持：SHOW ENGINES;

InnoDB引擎
InnoDB表是基于聚簇索引建立的，其索引结构和MySQL的其他存储引擎有很大区别。内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读。

MyISAM引擎
在MySQL5.1版本之前，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文检索、压缩、空间函数等，但MyISAM不支持事务和行级锁，而有一个缺陷就是崩溃后无法安全恢复。虽然MyISAM有这样的缺陷，但并非一无是处。
对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以继续使用MyISAM引擎。
MyISAM特性
①加锁与并发：MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入则对表加排它锁。在表有读取查询的同时，也可以往表中插入新的记录（被称为并发插入）。
②修复：MySQL可以对MyISAM表进行手工或者自动检查和修复操作，但这里所说的修复和事务恢复以及崩溃修复是不同的概念。执行表的修复可能导致一些数据丢失，而且修复操作非常慢。可以通过CHECK TABLE tableName检查表的错误，如果有错误可以通过执行REPAIR TABLE tableName进行修复。
③索引特性：对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
④延迟更新索引建：创建MyISAM表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。可以极大的提升写入性能，但是数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。
MyISAM压缩表
可以使用myisampack工具对MyISAM表进行压缩。压缩表是不能进行修改的。压缩表可以极大的减少磁盘空间占用，因此也可以减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。压缩表中的记录是独立压缩的，所以读取单行的时候不需要去解压整个表。

Memory引擎
在Memory表中，所有的数据都是保存在内存中，不需要进行磁盘I/O。重启时候会保留表结构，但数据会丢失。
修改表的存储引擎
1.ALTER TABLE tableName ENGINE = InnoDB
2.导出导入，可使用mysqldump工具
3.CREATE and SELECT
CREATE TABLE newTable LIKE oldTable;
ALTER TABLE newTable ENGINE=InnoDB;
INSERT INTO newTable SELECT * FROM oldTable;

MySQL的性能优化主要在几个方面：

① 硬件优化
② 系统配置优化
③ 数据库表结构优化
④ SQL及索引优化

多版本并发控制（MVCC）
MySQL中大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性能考虑，他们一般都同时实现了多版本并发控制（Multi-Version Concurrency Control，MVCC）.Oracle等其他数据库也实现了MVCC，但是各自实现机制不尽相同，以为MVCC没有一个统一的实现标准。
可以认为MVCC就是行级锁的一个变种，但是他在很多情况下避免了加锁操作。

MVCC是通过保存数据在某个时间点的快照来实现的。也就是说，不管一个事务需要执行多长时间，每个事物看到的数据都是一致的。根据事务开始时间的不同，不同事务对同一张表，在同一个时刻看到的数据可能是不一样的。

MVCC有很多种版本的实现，在InnoDB引擎下MVCC是通过在每行记录后面保存两个隐藏的列来实现的。一列保存了行的创建时间（版本），一列保存了行的删除时间（版本）。存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询中每行记录的版本号进行比较。

比如在REPEATABLE TABLE隔离级别下：
Insert: 为新插入的每一行记录保存当前系统版本号作为行版本号。
Delete：为删除的每一行保存当前系统版本号作为删除版本号。
Update：插入一行新的记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号为原来的行作为删除版本号。
Select：
1.只查找版本号早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号）；这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或修改过的。
2.行的删除版本号要么没有，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。

MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

注意：需要周期性的整理，来真实的删除老的、过期的数据。

MyISAM引擎加锁并发特性：
concurrent_insert
0 : 不允许并发
1 ：（默认）如果表中没有被删除的行， 允许一个进程读数据，另一个线程从表尾写入数据。
2 ：不管有没有被删除的行， 都允许从表尾写入数据。

内容总结

以上是互联网集市为您收集整理的MySql优化基础全部内容，希望文章能够帮你解决MySql优化基础所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端