《MySQL介绍及性能优化课件》由会员分享，可在线阅读，更多相关《MySQL介绍及性能优化课件（40页珍藏版）》请在金锄头文库上搜索。
1、MySQLMySQL介绍及性能优化介绍及性能优化l lMySQLMySQL基本介绍基本介绍l lMySQLMySQL优化方式优化方式l lMySQLMySQL技巧分享技巧分享l lQ Q & & A A目录索引目录索引什么是什么是MySQLMySQLl lMySQLMySQLMySQLMySQL是一个小型关系型数据库管理系统，开发者为瑞是一个小型关系型数据库管理系统，开发者为瑞是一个小型关系型数据库管理系统，开发者为瑞是一个小型关系型数据库管理系统，开发者为瑞典典典典MySQL ABMySQL ABMySQL ABMySQL AB公司。目前公司。目前公司。目前公司。目前MySQLMySQLMy
2、SQLMySQL被广泛地应用在被广泛地应用在被广泛地应用在被广泛地应用在InternetInternetInternetInternet上的中小型网站中。由于其体积小、速度快、总体拥上的中小型网站中。由于其体积小、速度快、总体拥上的中小型网站中。由于其体积小、速度快、总体拥上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网有成本低，尤其是开放源码这一特点，许多中小型网有成本低，尤其是开放源码这一特点，许多中小型网有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了站为了降低网站总体拥有成本而选择了站为了降低网站总体拥有成本而
3、选择了站为了降低网站总体拥有成本而选择了MySQLMySQLMySQLMySQL作为网站作为网站作为网站作为网站数据库。数据库。数据库。数据库。l lMySQLMySQLMySQLMySQL官方网站：官方网站：官方网站：官方网站：http:/http:/http:/http:/谁在用谁在用MySQLMySQLMySQLMySQL历史历史l l1979197919791979年，年，年，年， 报表工具，数据引擎报表工具，数据引擎报表工具，数据引擎报表工具，数据引擎l l1996199619961996年，年，年，年，MySQL 1.0 (3.11.1) MySQL 1.0 (3.11.1) My
4、SQL 1.0 (3.11.1) MySQL 1.0 (3.11.1) 发布，支持发布，支持发布，支持发布，支持SQLSQLSQLSQLl l2000200020002000年，成立年，成立年，成立年，成立 MySQL AB MySQL AB MySQL AB MySQL AB 公司公司公司公司l l2008200820082008年年年年1 1 1 1月，月，月，月，SunSunSunSun公司以公司以公司以公司以10101010亿美元收购亿美元收购亿美元收购亿美元收购MySQL ABMySQL ABMySQL ABMySQL AB公司公司公司公司l l2009200920092009年年年
5、年4 4 4 4月，月，月，月，OracleOracleOracleOracle公司以公司以公司以公司以74747474亿美元收购亿美元收购亿美元收购亿美元收购SunSunSunSun公司公司公司公司MySQLMySQL里程碑里程碑l l 3.11.1 First public release 3.11.1 First public release 3.11.1 First public release 3.11.1 First public releasel l 3.23 3.23 3.23 3.23 集成集成集成集成Berkeley DB, Berkeley DB, Berkeley DB
6、, Berkeley DB, 支持事务，抽象出支持事务，抽象出支持事务，抽象出支持事务，抽象出Storage EngineStorage EngineStorage EngineStorage Enginel l 4.0 4.0 4.0 4.0 集成集成集成集成InnoDBInnoDBInnoDBInnoDBl l 4.1 4.1 4.1 4.1 重大改进，子查询、重大改进，子查询、重大改进，子查询、重大改进，子查询、unicodeunicodeunicodeunicode、c/sc/sc/sc/s通信协议通信协议通信协议通信协议l l 5.0 stored procedure 5.0 sto
7、red procedure 5.0 stored procedure 5.0 stored procedure、viewviewviewview、triggerstriggerstriggerstriggers、query optimizerquery optimizerquery optimizerquery optimizerl l 5.1 File NDB 5.1 File NDB 5.1 File NDB 5.1 File NDB、record replication.record replication.record replication.record replication.My
8、SQLMySQL历史历史l l1979197919791979年，年，年，年， 报表工具，数据引擎报表工具，数据引擎报表工具，数据引擎报表工具，数据引擎l l1996199619961996年，年，年，年，MySQL 1.0 (3.11.1) MySQL 1.0 (3.11.1) MySQL 1.0 (3.11.1) MySQL 1.0 (3.11.1) 发布，支持发布，支持发布，支持发布，支持SQLSQLSQLSQLl l2000200020002000年，成立年，成立年，成立年，成立 MySQL AB MySQL AB MySQL AB MySQL AB 公司公司公司公司l l200820
9、0820082008年年年年1 1 1 1月，月，月，月，SunSunSunSun公司以公司以公司以公司以10101010亿美元收购亿美元收购亿美元收购亿美元收购MySQL ABMySQL ABMySQL ABMySQL AB公司公司公司公司l l2009200920092009年年年年4 4 4 4月，月，月，月，OracleOracleOracleOracle公司以公司以公司以公司以74747474亿美元收购亿美元收购亿美元收购亿美元收购SunSunSunSun公司公司公司公司MySQLMySQL存储引擎存储引擎l l MyISAM MyISAM MyISAM MyISAM：默认的：默认的
10、：默认的：默认的MySQLMySQLMySQLMySQL插件式存储引擎，它是在插件式存储引擎，它是在插件式存储引擎，它是在插件式存储引擎，它是在WebWebWebWeb、数据仓储和其他应用环、数据仓储和其他应用环、数据仓储和其他应用环、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改境下最常使用的存储引擎之一。注意，通过更改境下最常使用的存储引擎之一。注意，通过更改境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINESTORAGE_ENGINESTORAGE_ENGINESTORAGE_ENGINE配置变量，能配置变量，能配置变量，能配置变量，能够方便地更改够方
11、便地更改够方便地更改够方便地更改MySQLMySQLMySQLMySQL服务器的默认存储引擎。服务器的默认存储引擎。服务器的默认存储引擎。服务器的默认存储引擎。l l InnoDBInnoDBInnoDBInnoDB：用于事务处理应用程序，具有众多特性：用于事务处理应用程序，具有众多特性：用于事务处理应用程序，具有众多特性：用于事务处理应用程序，具有众多特性，包括，包括，包括，包括ACIDACIDACIDACID事务支持。事务支持。事务支持。事务支持。l l BDB BDB BDB BDB：可替代：可替代：可替代：可替代InnoDBInnoDBInnoDBInnoDB的事务引擎，支持的事务引擎
12、，支持的事务引擎，支持的事务引擎，支持COMMITCOMMITCOMMITCOMMIT、ROLLBACKROLLBACKROLLBACKROLLBACK和其他事务特性。和其他事务特性。和其他事务特性。和其他事务特性。l l Memory Memory Memory Memory：将所有数据保存在：将所有数据保存在：将所有数据保存在：将所有数据保存在RAMRAMRAMRAM中，在需要快速查找引用和其他类似数据的环中，在需要快速查找引用和其他类似数据的环中，在需要快速查找引用和其他类似数据的环中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。境下，可提供极快的访问。境下，可提供极快的
13、访问。境下，可提供极快的访问。l lMergeMergeMergeMerge：允许：允许：允许：允许MySQL DBAMySQL DBAMySQL DBAMySQL DBA或开发人员将一系列等同的或开发人员将一系列等同的或开发人员将一系列等同的或开发人员将一系列等同的MyISAMMyISAMMyISAMMyISAM表以逻辑方式组合表以逻辑方式组合表以逻辑方式组合表以逻辑方式组合在一起，并作为在一起，并作为在一起，并作为在一起，并作为1 1 1 1个对象引用它们。对于诸如数据仓储等个对象引用它们。对于诸如数据仓储等个对象引用它们。对于诸如数据仓储等个对象引用它们。对于诸如数据仓储等VLDBVLD
14、BVLDBVLDB环境十分适合。环境十分适合。环境十分适合。环境十分适合。l l Archive Archive Archive Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供 了完美的解决方案。了完美的解决方案。了完美的解决方案。了完美的解决方案。l l Federated Federated Federated Federated：能够将多个分离的：能够将多个分离的：能够将多个分离的：
15、能够将多个分离的MySQLMySQLMySQLMySQL服务器链接起来，从多个物理服务器创服务器链接起来，从多个物理服务器创服务器链接起来，从多个物理服务器创服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。建一个逻辑数据库。十分适合于分布式环境或数据集市环境。建一个逻辑数据库。十分适合于分布式环境或数据集市环境。建一个逻辑数据库。十分适合于分布式环境或数据集市环境。l l Cluster/NDB Cluster/NDB Cluster/NDB Cluster/NDB：MySQLMySQLMySQLMySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的
16、的簇式数据库引擎，尤其适合于具有高性能查找要求的的簇式数据库引擎，尤其适合于具有高性能查找要求的的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。l l Other Other Other Other：其他存储引擎包括：其他存储引擎包括：其他存储引擎包括：其他存储引擎包括CSVCSVCSVCSV（引用由逗号隔开的用作数据库表的文件），（引用由逗号隔开的用作数据
17、库表的文件），（引用由逗号隔开的用作数据库表的文件），（引用由逗号隔开的用作数据库表的文件）， BlackholeBlackholeBlackholeBlackhole（用于临时禁止对数据库的应用程序输入），以及（用于临时禁止对数据库的应用程序输入），以及（用于临时禁止对数据库的应用程序输入），以及（用于临时禁止对数据库的应用程序输入），以及ExampleExampleExampleExample引擎（可引擎（可引擎（可引擎（可为快速创建定制的插件式存储引擎提供帮助）。为快速创建定制的插件式存储引擎提供帮助）。为快速创建定制的插件式存储引擎提供帮助）。为快速创建定制的插件式存储引擎提供帮助）。
18、 MyISAM MyISAM MyISAM MyISAM 特点特点特点特点MyISAM vs InnoDB MyISAM vs InnoDB 数据存储方式简单，使用数据存储方式简单，使用数据存储方式简单，使用数据存储方式简单，使用 B+ Tree B+ Tree 进行索引进行索引进行索引进行索引 使用三个文件定义一个表：使用三个文件定义一个表：使用三个文件定义一个表：使用三个文件定义一个表：.MYI .MYD .frm.MYI .MYD .frm 少碎片、支持大文件、能够进行索引压缩少碎片、支持大文件、能够进行索引压缩少碎片、支持大文件、能够进行索引压缩少碎片、支持大文件、能够进行索引压缩 二
19、进制层次的文件可以移植二进制层次的文件可以移植二进制层次的文件可以移植二进制层次的文件可以移植 (Linux (Linux Windows) Windows) 访问速度飞快，是所有访问速度飞快，是所有访问速度飞快，是所有访问速度飞快，是所有MySQLMySQL文件引擎中速度最快的文件引擎中速度最快的文件引擎中速度最快的文件引擎中速度最快的 不支持一些数据库特性，比如不支持一些数据库特性，比如不支持一些数据库特性，比如不支持一些数据库特性，比如 事务、外键约束等事务、外键约束等事务、外键约束等事务、外键约束等 Table level lockTable level lock，性能稍差，更适合读取
20、多的操作，性能稍差，更适合读取多的操作，性能稍差，更适合读取多的操作，性能稍差，更适合读取多的操作 表数据容量有限，一般建议单表数据量介于表数据容量有限，一般建议单表数据量介于表数据容量有限，一般建议单表数据量介于表数据容量有限，一般建议单表数据量介于 50w50w200w 200w MyISAM vs InnoDBMyISAM vs InnoDBMyISAM MyISAM MyISAM MyISAM 索引结构索引结构索引结构索引结构MyISAM vs InnoDBMyISAM vs InnoDBMyISAM MyISAM MyISAM MyISAM 存储结构存储结构存储结构存储结构MyISA
21、M vs InnoDBMyISAM vs InnoDBInnoDB InnoDB 特点特点 使用使用使用使用 Table Space Table Space 的方式来进行数据存储的方式来进行数据存储的方式来进行数据存储的方式来进行数据存储 (ibdata1, ib_logfile0)(ibdata1, ib_logfile0) 支持支持支持支持 事务、外键约束等数据库特性事务、外键约束等数据库特性事务、外键约束等数据库特性事务、外键约束等数据库特性 Rows level lock , Rows level lock , 读写性能都非常优秀读写性能都非常优秀读写性能都非常优秀读写性能都非常优秀
22、能够承载大数据量的存储和访问能够承载大数据量的存储和访问能够承载大数据量的存储和访问能够承载大数据量的存储和访问 拥有自己独立的缓冲池，能够缓存数据和索引拥有自己独立的缓冲池，能够缓存数据和索引拥有自己独立的缓冲池，能够缓存数据和索引拥有自己独立的缓冲池，能够缓存数据和索引 在关闭自动提交的情况下，与在关闭自动提交的情况下，与在关闭自动提交的情况下，与在关闭自动提交的情况下，与MyISAMMyISAM引擎速度差异不大引擎速度差异不大引擎速度差异不大引擎速度差异不大InnoDB InnoDB InnoDB InnoDB 数据结构数据结构数据结构数据结构MyISAM vs InnoDB MyISA
23、M vs InnoDB MyISAM vs InnoDB MyISAM vs InnoDB 性能测试性能测试数据量/单位:万MyISAMInnoDB备注: f 特殊选项插入：插入：1w1w3 3秒秒219219秒秒innodb_flush_log_at_trx_commit=1innodb_flush_log_at_trx_commit=1插入：插入：10w10w29 29 秒秒20922092秒秒innodb_flush_log_at_trx_commit=1innodb_flush_log_at_trx_commit=1插入：插入：100w100w287287秒秒N/AN/Ainnodb_
24、flush_log_at_trx_commit=1innodb_flush_log_at_trx_commit=1插入：插入：1w1w3 3秒秒3 3秒秒innodb_flush_log_at_trx_commit=0 innodb_flush_log_at_trx_commit=0 插入：插入：10w10w3030秒秒2929秒秒innodb_flush_log_at_trx_commit=0 innodb_flush_log_at_trx_commit=0 插入：插入：100w100w273273秒秒423423秒秒innodb_flush_log_at_trx_commit=0 inno
25、db_flush_log_at_trx_commit=0 插入：插入：1w1wN/AN/A3 3秒秒innodb_flush_log_at_trx_commit=0 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=256Minnodb_buffer_pool_size=256M插入：插入：10W10WN/AN/A2626秒秒innodb_flush_log_at_trx_commit=0 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=256Minnodb_buf
26、fer_pool_size=256M插入：插入：100W100WN/AN/A379379秒秒innodb_flush_log_at_trx_commit=0innodb_flush_log_at_trx_commit=0innodb_buffer_pool_size=256Minnodb_buffer_pool_size=256MMyISAM vs InnoDB MyISAM vs InnoDB 性能测试性能测试测试结果测试结果测试结果测试结果可以看出在可以看出在可以看出在可以看出在MySQL 5.0MySQL 5.0里面，里面，里面，里面，MyISAMMyISAM和和和和InnoDBInno
27、DB存储引擎性存储引擎性存储引擎性存储引擎性能差别并不是很大，针对能差别并不是很大，针对能差别并不是很大，针对能差别并不是很大，针对InnoDBInnoDB来说，影响性能的主要是来说，影响性能的主要是来说，影响性能的主要是来说，影响性能的主要是 innodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit 这个选项，如果设置为这个选项，如果设置为这个选项，如果设置为这个选项，如果设置为1 1的的的的话，那么每次插入数据的时候都会自动提交，导致性能急剧话，那么每次插入数据的时候都会自动提交，导致性能急剧话，那么每次插入数据的时候都会自
28、动提交，导致性能急剧话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为下降，应该是跟刷新日志有关系，设置为下降，应该是跟刷新日志有关系，设置为下降，应该是跟刷新日志有关系，设置为0 0效率能够看到明显效率能够看到明显效率能够看到明显效率能够看到明显提升，当然，同样你可以提升，当然，同样你可以提升，当然，同样你可以提升，当然，同样你可以SQLSQL中提交中提交中提交中提交“SET AUTOCOMMIT = SET AUTOCOMMIT = 0 0”来设置达到好的性能。来设置达到好的性能。来设置达到好的性能。来设置达到好的性能。同时也可以看出值得使用同时也可以
29、看出值得使用同时也可以看出值得使用同时也可以看出值得使用 InnoDB InnoDB 来替代来替代来替代来替代 MyISAM MyISAM 引擎来进引擎来进引擎来进引擎来进行开发，毕竟行开发，毕竟行开发，毕竟行开发，毕竟InnoDB InnoDB 有多数据库特性、更良好的数据存储性有多数据库特性、更良好的数据存储性有多数据库特性、更良好的数据存储性有多数据库特性、更良好的数据存储性能和查询性能能和查询性能能和查询性能能和查询性能MySQLMySQL优化方式优化方式l l系统优化：硬件、架构系统优化：硬件、架构l l服务优化服务优化l l应用优化应用优化MySQL优化方式优化方式影响性能的因素影
30、响性能的因素l l应用程序应用程序应用程序应用程序l l查询查询查询查询l l事务管理事务管理事务管理事务管理l l数据库设计数据库设计数据库设计数据库设计l l数据分布数据分布数据分布数据分布l l网络网络网络网络l l操作系统操作系统操作系统操作系统l l硬件硬件硬件硬件l l使用好的硬件，更快的硬盘、大内存、多核使用好的硬件，更快的硬盘、大内存、多核使用好的硬件，更快的硬盘、大内存、多核使用好的硬件，更快的硬盘、大内存、多核CPUCPUCPUCPU，专业的存，专业的存，专业的存，专业的存储服务器（储服务器（储服务器（储服务器（NASNASNASNAS、SANSANSANSAN）l l设计
31、合理架构，如果设计合理架构，如果设计合理架构，如果设计合理架构，如果 MySQL MySQL MySQL MySQL 访问频繁，考虑访问频繁，考虑访问频繁，考虑访问频繁，考虑 Master/Slave Master/Slave Master/Slave Master/Slave 读写分离；数据库分表、数据库切片（分布式），也考虑使读写分离；数据库分表、数据库切片（分布式），也考虑使读写分离；数据库分表、数据库切片（分布式），也考虑使读写分离；数据库分表、数据库切片（分布式），也考虑使用相应缓存服务帮助用相应缓存服务帮助用相应缓存服务帮助用相应缓存服务帮助 MySQL MySQL MySQL M
32、ySQL 缓解访问压力缓解访问压力缓解访问压力缓解访问压力系统优化系统优化l l配置合理的配置合理的配置合理的配置合理的MySQLMySQLMySQLMySQL服务器，尽量在应用本身达到一个服务器，尽量在应用本身达到一个服务器，尽量在应用本身达到一个服务器，尽量在应用本身达到一个MySQLMySQLMySQLMySQL最合理的使用最合理的使用最合理的使用最合理的使用l l针对针对针对针对 MyISAM MyISAM MyISAM MyISAM 或或或或 InnoDB InnoDB InnoDB InnoDB 不同引擎进行不同定制性配置不同引擎进行不同定制性配置不同引擎进行不同定制性配置不同引擎
33、进行不同定制性配置l l针对不同的应用情况进行合理配置针对不同的应用情况进行合理配置针对不同的应用情况进行合理配置针对不同的应用情况进行合理配置l l针对针对针对针对 f f f f 进行配置，后面设置是针对内存为进行配置，后面设置是针对内存为进行配置，后面设置是针对内存为进行配置，后面设置是针对内存为2G2G2G2G的服的服的服的服务器进行的合理设置务器进行的合理设置务器进行的合理设置务器进行的合理设置服务优化服务优化MySQLMySQL配置原则配置原则配置原则配置原则 服务优化服务优化公共选项公共选项公共选项公共选项选项选项缺省值缺省值推荐值推荐值说明说明max_connections10
34、01024MySQL服务器同时处理的数据库连接的最大数量query_cache_size0 (不打开）16M查询缓存区的最大长度，按照当前需求，一倍一倍增加，本选项比较重要sort_buffer_size512K16M每个线程的排序缓存大小，一般按照内存可以设置为2M以上，推荐是16M，该选项对排序order by，group by起作用record_buffer128K16M每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区，可以设置为2M以上table_cache64512为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表
35、需要2个文件描述符。服务优化服务优化MyISAM MyISAM 选项选项选项选项选项缺省值推荐值说明key_buffer_size8M256M用来存放索引区块的缓存值, 建议128M以上，不要大于内存的30%read_buffer_size128K16M用来做MyISAM表全表扫描的缓冲大小. 为从数据表顺序读取数据的读操作保留的缓存区的长度myisam_sort_buffer_size16M128M设置,恢复,修改表的时候使用的缓冲大小，值不要设的太大服务优化服务优化InnoDB InnoDB 选项选项选项选项选项选项缺省值缺省值推荐值推荐值说明说明innodb_buffer_pool_si
36、ze32M1GInnoDB使用一个缓冲池来保存索引和原始数据, 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少，一般是内存的一半，不超过2G，否则系统会崩溃，这个参数非常重要innodb_additional_mem_pool_size2M128MInnoDB用来保存 metadata 信息, 如果内存是4G，最好本值超过200Minnodb_flush_log_at_trx_commit10 0 代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘; 1 为执行完没执行一条SQL马上commit; 2 代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.
37、对速度影响比较大，同时也关系数据完整性innodb_log_file_size8M256M在日志组中每个日志文件的大小, 一般是innodb_buffer_pool_size的25%，官方推荐是 innodb_buffer_pool_size 的 40-50%, 设置大一点来避免在日志文件覆写上不必要的缓冲池刷新行为innodb_log_buffer_size128K8M用来缓冲日志数据的缓冲区的大小. 推荐是8M，官方推荐该值小于16M，最好是 1M-8M 之间l l设计合理的数据表结构：适当的数据冗余设计合理的数据表结构：适当的数据冗余设计合理的数据表结构：适当的数据冗余设计合理的数据表结
38、构：适当的数据冗余l l对数据表建立合适有效的数据库索引对数据表建立合适有效的数据库索引对数据表建立合适有效的数据库索引对数据表建立合适有效的数据库索引l l数据查询：编写简洁高效的数据查询：编写简洁高效的数据查询：编写简洁高效的数据查询：编写简洁高效的SQLSQLSQLSQL语句语句语句语句应用优化应用优化应用优化方式应用优化方式应用优化方式应用优化方式 应用优化应用优化表结构设计原则表结构设计原则表结构设计原则表结构设计原则l l 选择合适的数据类型：如果能够定长尽量定长选择合适的数据类型：如果能够定长尽量定长选择合适的数据类型：如果能够定长尽量定长选择合适的数据类型：如果能够定长尽量定长
39、l l 使用使用使用使用 ENUMENUM而不是而不是而不是而不是 VARCHAR,ENUMVARCHAR,ENUM类型是非常快和紧凑的，在实际类型是非常快和紧凑的，在实际类型是非常快和紧凑的，在实际类型是非常快和紧凑的，在实际上，其保存的是上，其保存的是上，其保存的是上，其保存的是 TINYINTTINYINT，但其外表上显示为字符串。这样一来，用这个，但其外表上显示为字符串。这样一来，用这个，但其外表上显示为字符串。这样一来，用这个，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美字段来做一些选项列表变得相当的完美字段来做一些选项列表变得相当的完美字段来做一些选项
40、列表变得相当的完美 。l l 不要使用无法加索引的类型作为关键字段，比如不要使用无法加索引的类型作为关键字段，比如不要使用无法加索引的类型作为关键字段，比如不要使用无法加索引的类型作为关键字段，比如 texttexttexttext类型类型类型类型l l 为了避免联表查询，有时候可以适当的数据冗余，比如为了避免联表查询，有时候可以适当的数据冗余，比如为了避免联表查询，有时候可以适当的数据冗余，比如为了避免联表查询，有时候可以适当的数据冗余，比如 邮箱、姓名这些不容易更改的数据邮箱、姓名这些不容易更改的数据邮箱、姓名这些不容易更改的数据邮箱、姓名这些不容易更改的数据l l 选择合适的表引擎，有时
41、候选择合适的表引擎，有时候选择合适的表引擎，有时候选择合适的表引擎，有时候 MyISAM MyISAM MyISAM MyISAM 适合，有时候适合，有时候适合，有时候适合，有时候 InnoDBInnoDBInnoDBInnoDB适合适合适合适合l l 为保证查询性能，最好每个表都建立有为保证查询性能，最好每个表都建立有为保证查询性能，最好每个表都建立有为保证查询性能，最好每个表都建立有 auto_increment auto_increment auto_increment auto_increment 字段，字段，字段，字段， 建立合适的数据库索引建立合适的数据库索引建立合适的数据库索引建
42、立合适的数据库索引l l 最好给每个字段都设定最好给每个字段都设定最好给每个字段都设定最好给每个字段都设定 default default default default 值值值值应用优化应用优化索引建立原则（一）索引建立原则（一）索引建立原则（一）索引建立原则（一）l l 一般针对数据分散的关键字进行建立索引，比如一般针对数据分散的关键字进行建立索引，比如一般针对数据分散的关键字进行建立索引，比如一般针对数据分散的关键字进行建立索引，比如IDIDIDID、QQQQQQQQ， 像性别、状态值等等建立索引没有意义像性别、状态值等等建立索引没有意义像性别、状态值等等建立索引没有意义像性别、状态值等
43、等建立索引没有意义l l 字段唯一，最少，不可为字段唯一，最少，不可为字段唯一，最少，不可为字段唯一，最少，不可为nullnullnullnulll l 对大数据量表建立聚集索引，避免更新操作带来的碎片。对大数据量表建立聚集索引，避免更新操作带来的碎片。对大数据量表建立聚集索引，避免更新操作带来的碎片。对大数据量表建立聚集索引，避免更新操作带来的碎片。l l 尽量使用短索引，一般对尽量使用短索引，一般对尽量使用短索引，一般对尽量使用短索引，一般对intintintint、char/varcharchar/varcharchar/varcharchar/varchar、date/time dat
44、e/time date/time date/time 等等等等 类型的字段建立索引类型的字段建立索引类型的字段建立索引类型的字段建立索引l l 需要的时候建立联合索引，但是要注意查询需要的时候建立联合索引，但是要注意查询需要的时候建立联合索引，但是要注意查询需要的时候建立联合索引，但是要注意查询SQLSQLSQLSQL语句的编写语句的编写语句的编写语句的编写l l 谨慎建立谨慎建立谨慎建立谨慎建立 unique unique unique unique 类型的索引（唯一索引）类型的索引（唯一索引）类型的索引（唯一索引）类型的索引（唯一索引）l l 大文本字段不建立为索引，如果要对大文本字段进行
45、检索，大文本字段不建立为索引，如果要对大文本字段进行检索，大文本字段不建立为索引，如果要对大文本字段进行检索，大文本字段不建立为索引，如果要对大文本字段进行检索， 可以考虑全文索引可以考虑全文索引可以考虑全文索引可以考虑全文索引l l 频繁更新的列不适合建立索引频繁更新的列不适合建立索引频繁更新的列不适合建立索引频繁更新的列不适合建立索引应用优化应用优化索引建立原则（二）索引建立原则（二）索引建立原则（二）索引建立原则（二）l l order by order by order by order by 字句中的字段，字句中的字段，字句中的字段，字句中的字段，where where where
46、where 子句中字段，最常用的子句中字段，最常用的子句中字段，最常用的子句中字段，最常用的sql sql sql sql 语句中字段，应建立索引。语句中字段，应建立索引。语句中字段，应建立索引。语句中字段，应建立索引。l l 唯一性约束，系统将默认为改字段建立索引。唯一性约束，系统将默认为改字段建立索引。唯一性约束，系统将默认为改字段建立索引。唯一性约束，系统将默认为改字段建立索引。l l 对于只是做查询用的数据库索引越多越好，但对于在线实时对于只是做查询用的数据库索引越多越好，但对于在线实时对于只是做查询用的数据库索引越多越好，但对于在线实时对于只是做查询用的数据库索引越多越好，但对于在线
47、实时 系统建议控制在系统建议控制在系统建议控制在系统建议控制在5 5 5 5个以内。个以内。个以内。个以内。l l 索引不仅能提高查询索引不仅能提高查询索引不仅能提高查询索引不仅能提高查询SQLSQLSQLSQL性能，同时也可以提高带性能，同时也可以提高带性能，同时也可以提高带性能，同时也可以提高带wherewherewherewhere字句字句字句字句 的的的的updateupdateupdateupdate，Delete SQLDelete SQLDelete SQLDelete SQL性能。性能。性能。性能。l l Decimal Decimal Decimal Decimal 类型字段
48、不要单独建立为索引，但覆盖索引可以包类型字段不要单独建立为索引，但覆盖索引可以包类型字段不要单独建立为索引，但覆盖索引可以包类型字段不要单独建立为索引，但覆盖索引可以包 含这些字段。含这些字段。含这些字段。含这些字段。l l 只有建立索引以后，表内的行才按照特地的顺序存储，按照只有建立索引以后，表内的行才按照特地的顺序存储，按照只有建立索引以后，表内的行才按照特地的顺序存储，按照只有建立索引以后，表内的行才按照特地的顺序存储，按照 需要可以是需要可以是需要可以是需要可以是ascascascasc或或或或descdescdescdesc方式。方式。方式。方式。l l 如果索引由多个字段组成将最用
49、来查询过滤的字段放在前面如果索引由多个字段组成将最用来查询过滤的字段放在前面如果索引由多个字段组成将最用来查询过滤的字段放在前面如果索引由多个字段组成将最用来查询过滤的字段放在前面 可能会有更好的性能。可能会有更好的性能。可能会有更好的性能。可能会有更好的性能。应用优化应用优化MSSQL MSSQL MSSQL MSSQL 执行顺序执行顺序执行顺序执行顺序(8) SELECT (9) DISTINCT (11) (8) SELECT (9) DISTINCT (11) (1) FROM (1) FROM (3) JOIN (3) JOIN (2) ON (2) ON (4) WHERE (4)
50、 WHERE (5) GROUP BY (5) GROUP BY (6) WITH CUBE | ROLLUP (6) WITH CUBE | ROLLUP (7) HAVING (7) HAVING (10) ORDER BY (10) ORDER BY http:/ 应用优化应用优化编写高效的编写高效的编写高效的编写高效的 SQL SQL SQL SQL （一）（一）（一）（一）l l 能够快速缩小结果集的能够快速缩小结果集的能够快速缩小结果集的能够快速缩小结果集的 WHERE WHERE 条件写在前面，如果有恒量条件，条件写在前面，如果有恒量条件，条件写在前面，如果有恒量条件，条件写在前
51、面，如果有恒量条件， 也尽量放在前面也尽量放在前面也尽量放在前面也尽量放在前面l l 尽量避免使用尽量避免使用尽量避免使用尽量避免使用 GROUP BYGROUP BY、DISTINCT DISTINCT 、OROR、IN IN 等语句的使用，等语句的使用，等语句的使用，等语句的使用， 避免使用联表查询和子查询，因为将使执行效率大大下降避免使用联表查询和子查询，因为将使执行效率大大下降避免使用联表查询和子查询，因为将使执行效率大大下降避免使用联表查询和子查询，因为将使执行效率大大下降l l 能够使用索引的字段尽量进行有效的合理排列，如果使用了能够使用索引的字段尽量进行有效的合理排列，如果使用了
52、能够使用索引的字段尽量进行有效的合理排列，如果使用了能够使用索引的字段尽量进行有效的合理排列，如果使用了 联合索引，请注意提取字段的前后顺序联合索引，请注意提取字段的前后顺序联合索引，请注意提取字段的前后顺序联合索引，请注意提取字段的前后顺序l l 针对索引字段使用针对索引字段使用针对索引字段使用针对索引字段使用 , =, =, , , =, =, , =, IF NULL和和和和BETWEEN BETWEEN 将会使用将会使用将会使用将会使用 索引，索引，索引，索引， 如果对某个索引字段进行如果对某个索引字段进行如果对某个索引字段进行如果对某个索引字段进行 LIKE LIKE 查询，使用查询
53、，使用查询，使用查询，使用 LIKE %abc% LIKE %abc% 不能使用索引，使用不能使用索引，使用不能使用索引，使用不能使用索引，使用 LIKE abc% LIKE abc% 将能够使用索引将能够使用索引将能够使用索引将能够使用索引l l 如果在如果在如果在如果在SQLSQL里使用了里使用了里使用了里使用了MySQLMySQL部分自带函数，索引将失效，同时将无法部分自带函数，索引将失效，同时将无法部分自带函数，索引将失效，同时将无法部分自带函数，索引将失效，同时将无法 使用使用使用使用 MySQL MySQL 的的的的 Query CacheQuery Cache，比如，比如，比如，
54、比如 LEFT(), SUBSTR(), TO_DAYS() LEFT(), SUBSTR(), TO_DAYS() DATE_FORMAT(), DATE_FORMAT(), 等，如果使用了等，如果使用了等，如果使用了等，如果使用了 OR OR 或或或或 ININ，索引也将失效，索引也将失效，索引也将失效，索引也将失效l l 使用使用使用使用 Explain Explain 语句来帮助改进我们的语句来帮助改进我们的语句来帮助改进我们的语句来帮助改进我们的SQLSQL语句语句语句语句应用优化应用优化编写高效的编写高效的编写高效的编写高效的 SQL SQL SQL SQL （二）（二）（二）（二
55、）l l 不要在不要在不要在不要在where where 子句中的子句中的子句中的子句中的“= =”左边进行算术或表达式运算，否则系统将左边进行算术或表达式运算，否则系统将左边进行算术或表达式运算，否则系统将左边进行算术或表达式运算，否则系统将 可能无法正确使用索引可能无法正确使用索引可能无法正确使用索引可能无法正确使用索引l l 尽量不要在尽量不要在尽量不要在尽量不要在wherewhere条件中使用函数，否则将不能使用索引条件中使用函数，否则将不能使用索引条件中使用函数，否则将不能使用索引条件中使用函数，否则将不能使用索引l l 避免使用避免使用避免使用避免使用 select *, sele
56、ct *, 只取需要的字段只取需要的字段只取需要的字段只取需要的字段l l 对于大数据量的查询，尽量避免在对于大数据量的查询，尽量避免在对于大数据量的查询，尽量避免在对于大数据量的查询，尽量避免在SQLSQL语句中使用语句中使用语句中使用语句中使用order by order by 字句，避免字句，避免字句，避免字句，避免 额为的开销，替代为使用额为的开销，替代为使用额为的开销，替代为使用额为的开销，替代为使用ADO.NET ADO.NET 来实现。来实现。来实现。来实现。l l 如果插入的数据量很大，用如果插入的数据量很大，用如果插入的数据量很大，用如果插入的数据量很大，用select in
57、to select into 替代替代替代替代 insert into insert into 能带来更好的性能能带来更好的性能能带来更好的性能能带来更好的性能l l 采用连接操作，避免过多的子查询，产生的采用连接操作，避免过多的子查询，产生的采用连接操作，避免过多的子查询，产生的采用连接操作，避免过多的子查询，产生的CPUCPU和和和和IOIO开销开销开销开销l l 只关心需要的表和满足条件的数据只关心需要的表和满足条件的数据只关心需要的表和满足条件的数据只关心需要的表和满足条件的数据l l 适当使用临时表或表变量适当使用临时表或表变量适当使用临时表或表变量适当使用临时表或表变量l l 对于
58、连续的数值，使用对于连续的数值，使用对于连续的数值，使用对于连续的数值，使用betweenbetween代替代替代替代替ininl l where where 字句中尽量不要使用字句中尽量不要使用字句中尽量不要使用字句中尽量不要使用CASECASE条件条件条件条件l l 尽量不用触发器，特别是在大数据表上尽量不用触发器，特别是在大数据表上尽量不用触发器，特别是在大数据表上尽量不用触发器，特别是在大数据表上应用优化应用优化编写高效的编写高效的编写高效的编写高效的 SQL SQL SQL SQL （三）（三）（三）（三）l l 更新触发器如果不是所有情况下都需要触发，应根据业务需要加更新触发器如果
59、不是所有情况下都需要触发，应根据业务需要加更新触发器如果不是所有情况下都需要触发，应根据业务需要加更新触发器如果不是所有情况下都需要触发，应根据业务需要加 上必要判断条件上必要判断条件上必要判断条件上必要判断条件l l 使用使用使用使用union all union all 操作代替操作代替操作代替操作代替OROR操作，注意此时需要注意一点查询条操作，注意此时需要注意一点查询条操作，注意此时需要注意一点查询条操作，注意此时需要注意一点查询条 件可以使用聚集索引，如果是非聚集索引将起到相反的结果件可以使用聚集索引，如果是非聚集索引将起到相反的结果件可以使用聚集索引，如果是非聚集索引将起到相反的结
60、果件可以使用聚集索引，如果是非聚集索引将起到相反的结果l l 当只要一行数据时使用当只要一行数据时使用当只要一行数据时使用当只要一行数据时使用 LIMIT1LIMIT1l l 尽可能的使用尽可能的使用尽可能的使用尽可能的使用 NOTNULLNOTNULL填充数据库填充数据库填充数据库填充数据库l l 拆分大的拆分大的拆分大的拆分大的 DELETEDELETE或或或或 INSERTINSERT语句语句语句语句l l 批量提交批量提交批量提交批量提交SQLSQL语句语句语句语句MySQLMySQL技巧分享技巧分享MySQLMySQL技巧分享技巧分享常用技巧常用技巧常用技巧常用技巧l l 使用使用使
61、用使用 Explain/ DESC Explain/ DESC 来分析来分析来分析来分析SQLSQL的执行情况的执行情况的执行情况的执行情况l l 使用使用使用使用 SHOW PROCESSLIST SHOW PROCESSLIST 来查看当前来查看当前来查看当前来查看当前MySQLMySQL服务器线程服务器线程服务器线程服务器线程 执行情况，是否锁表，查看相应的执行情况，是否锁表，查看相应的执行情况，是否锁表，查看相应的执行情况，是否锁表，查看相应的SQLSQL语句语句语句语句l l 设置设置设置设置 f f 中的中的中的中的 long-query-time long-query-time
62、和和和和 log-slow-queries log-slow-queries 能够能够能够能够 记录服务器那些记录服务器那些记录服务器那些记录服务器那些SQLSQL执行速度比较慢执行速度比较慢执行速度比较慢执行速度比较慢l l 另外有用的几个查询：另外有用的几个查询：另外有用的几个查询：另外有用的几个查询：SHOW VARIABLESSHOW VARIABLES、SHOW SHOW STATUS STATUS、SHOW ENGINESSHOW ENGINESl l 使用使用使用使用 DESC TABLE xxx DESC TABLE xxx 来查看表结构，使用来查看表结构，使用来查看表结构，使
63、用来查看表结构，使用 SHOW INDEXSHOW INDEX FROM xxx FROM xxx 来查看表索引来查看表索引来查看表索引来查看表索引l l 使用使用使用使用 LOAD DATA LOAD DATA 导入数据比导入数据比导入数据比导入数据比 INSERT INTO INSERT INTO 快多了快多了快多了快多了l l SELECT COUNT(*) FROM Tbl SELECT COUNT(*) FROM Tbl 在在在在 InnoDB InnoDB 中将会扫描全表中将会扫描全表中将会扫描全表中将会扫描全表 MyISAM MyISAM 中则效率很高中则效率很高中则效率很高中则
64、效率很高MySQL MySQL 技巧分享技巧分享Explain Explain Explain Explain 使用使用使用使用l l 语法：语法：语法：语法：EXPLAIN SELECT EXPLAIN SELECT select_optionsselect_options Type: Type: 类型，是否使用了索引还是全表扫描类型，是否使用了索引还是全表扫描类型，是否使用了索引还是全表扫描类型，是否使用了索引还是全表扫描, const,eg_reg,ref,range,index,ALL , const,eg_reg,ref,range,index,ALL Key: Key: 实际使用上
65、的索引是哪个字段实际使用上的索引是哪个字段实际使用上的索引是哪个字段实际使用上的索引是哪个字段 Ken_len: Ken_len: 真正使用了哪些索引，不为真正使用了哪些索引，不为真正使用了哪些索引，不为真正使用了哪些索引，不为 NULL NULL 的就是真实使用的索引的就是真实使用的索引的就是真实使用的索引的就是真实使用的索引Ref: Ref: 显示了哪些字段或者常量被用来和显示了哪些字段或者常量被用来和显示了哪些字段或者常量被用来和显示了哪些字段或者常量被用来和 key key 配合从表中查询记录出来配合从表中查询记录出来配合从表中查询记录出来配合从表中查询记录出来 Rows: Rows:
66、 显示了显示了显示了显示了MySQLMySQL认为在查询中应该检索的记录数认为在查询中应该检索的记录数认为在查询中应该检索的记录数认为在查询中应该检索的记录数 Extra: Extra: 显示了查询中显示了查询中显示了查询中显示了查询中MySQLMySQL的附加信息，关心的附加信息，关心的附加信息，关心的附加信息，关心Using filesort Using filesort 和和和和 Using temporaryUsing temporary，性能杀手，性能杀手，性能杀手，性能杀手MySQL MySQL 技巧分享技巧分享索引实践索引实践索引实践索引实践MySQL MySQL 技巧分享技巧分
67、享函数和索引函数和索引函数和索引函数和索引MySQL MySQL 技巧分享技巧分享MySQL Slow Log MySQL Slow Log MySQL Slow Log MySQL Slow Log 分析工具分析工具分析工具分析工具l l mysqldumpslow - mysqlmysqldumpslow - mysqlmysqldumpslow - mysqlmysqldumpslow - mysql官方提供的慢查询日志分析工具官方提供的慢查询日志分析工具官方提供的慢查询日志分析工具官方提供的慢查询日志分析工具 l l mysqlsla - mysqlsla - mysqlsla - m
68、ysqlsla - 推出的一款日志分析工具，功能推出的一款日志分析工具，功能推出的一款日志分析工具，功能推出的一款日志分析工具，功能 非常强大非常强大非常强大非常强大l l mysql-explain-slow-logmysql-explain-slow-logmysql-explain-slow-logmysql-explain-slow-log 德国工程师使用德国工程师使用德国工程师使用德国工程师使用PerlPerlPerlPerl开发的把开发的把开发的把开发的把 Slow Log Slow Log Slow Log Slow Log 输出到屏幕，功能简单输出到屏幕，功能简单输出到屏幕，功
69、能简单输出到屏幕，功能简单l l mysql-log-filtermysql-log-filtermysql-log-filtermysql-log-filter - Google code - Google code - Google code - Google code 上一个开源产品，报表上一个开源产品，报表上一个开源产品，报表上一个开源产品，报表简洁简洁简洁简洁MySQL MySQL 技巧分享技巧分享MySQLMySQLMySQLMySQL优化网站优化网站优化网站优化网站/ / / /书籍分享书籍分享书籍分享书籍分享l l MySQL Performance BlogMySQL Performance Blogl l MySQL MySQL 中文网中文网中文网中文网l lMySQLMySQL性能调优与架构设计性能调优与架构设计性能调优与架构设计性能调优与架构设计 l l深入浅出深入浅出深入浅出深入浅出MySQLMySQLQ & AQ & A
