MySQL性能优化.ppt

MySQLMySQL介绍及性能优化介绍及性能优化l lMySQLMySQL基本介绍基本介绍l lMySQLMySQL优化方式优化方式l lMySQLMySQL技巧分享技巧分享l lQ Q&A A目录索引目录索引什么是什么是MySQLMySQLl lMySQLMySQLMySQLMySQL是一个小型关系型数据库管理系统，开发者为是一个小型关系型数据库管理系统，开发者为是一个小型关系型数据库管理系统，开发者为是一个小型关系型数据库管理系统，开发者为瑞典瑞典瑞典瑞典MySQL ABMySQL ABMySQL ABMySQL AB公司。目前公司。目前公司。目前公司。目前MySQLMySQLMySQLMySQL被广泛地应用在被广泛地应用在被广泛地应用在被广泛地应用在InternetInternetInternetInternet上的中小型网站中。由于其体积小、速度快、上的中小型网站中。由于其体积小、速度快、上的中小型网站中。由于其体积小、速度快、上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中总体拥有成本低，尤其是开放源码这一特点，许多中总体拥有成本低，尤其是开放源码这一特点，许多中总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了小型网站为了降低网站总体拥有成本而选择了小型网站为了降低网站总体拥有成本而选择了小型网站为了降低网站总体拥有成本而选择了MySQLMySQLMySQLMySQL作为网站数据库。作为网站数据库。作为网站数据库。作为网站数据库。l lMySQLMySQLMySQLMySQL官方网站：官方网站：官方网站：官方网站：http:/http:/http:/http:/谁在用谁在用MySQLMySQLMySQLMySQL历史历史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 l2008200820082008年年年年1 1 1 1月，月，月，月，SunSunSunSun公司以公司以公司以公司以10101010亿美元收购亿美元收购亿美元收购亿美元收购MySQL ABMySQL ABMySQL ABMySQL AB公司公司公司公司l l2009200920092009年年年年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,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 stored procedure 5.0 stored procedure 5.0 stored procedure、viewviewviewview、triggerstriggerstriggerstriggers、query query query query optimizeroptimizeroptimizeroptimizerl 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.MySQLMySQL历史历史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 l2008200820082008年年年年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：默认的：默认的：默认的：默认的MySQLMySQLMySQLMySQL插件式存储引擎，它是在插件式存储引擎，它是在插件式存储引擎，它是在插件式存储引擎，它是在WebWebWebWeb、数据仓储和其他应用环、数据仓储和其他应用环、数据仓储和其他应用环、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改境下最常使用的存储引擎之一。注意，通过更改境下最常使用的存储引擎之一。注意，通过更改境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINESTORAGE_ENGINESTORAGE_ENGINESTORAGE_ENGINE配置变量，配置变量，配置变量，配置变量，能够方便地更改能够方便地更改能够方便地更改能够方便地更改MySQLMySQLMySQLMySQL服务器的默认存储引擎。服务器的默认存储引擎。服务器的默认存储引擎。服务器的默认存储引擎。l l InnoDBInnoDBInnoDBInnoDB：用于事务处理应用程序，具有众多特性：用于事务处理应用程序，具有众多特性：用于事务处理应用程序，具有众多特性：用于事务处理应用程序，具有众多特性，包括，包括，包括，包括ACIDACIDACIDACID事务支持。事务支持。事务支持。事务支持。l l BDB BDB BDB BDB：可替代：可替代：可替代：可替代InnoDBInnoDBInnoDBInnoDB的事务引擎，支持的事务引擎，支持的事务引擎，支持的事务引擎，支持COMMITCOMMITCOMMITCOMMIT、ROLLBACKROLLBACKROLLBACKROLLBACK和其他事务特性。和其他事务特性。和其他事务特性。和其他事务特性。l l Memory Memory Memory Memory：将所有数据保存在：将所有数据保存在：将所有数据保存在：将所有数据保存在RAMRAMRAMRAM中，在需要快速查找引用和其他类似数据的中，在需要快速查找引用和其他类似数据的中，在需要快速查找引用和其他类似数据的中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。环境下，可提供极快的访问。环境下，可提供极快的访问。环境下，可提供极快的访问。l lMergeMergeMergeMerge：允许：允许：允许：允许MySQL DBAMySQL DBAMySQL DBAMySQL DBA或开发人员将一系列等同的或开发人员将一系列等同的或开发人员将一系列等同的或开发人员将一系列等同的MyISAMMyISAMMyISAMMyISAM表以逻辑方式组表以逻辑方式组表以逻辑方式组表以逻辑方式组合在一起，并作为合在一起，并作为合在一起，并作为合在一起，并作为1 1 1 1个对象引用它们。对于诸如数据仓储等个对象引用它们。对于诸如数据仓储等个对象引用它们。对于诸如数据仓储等个对象引用它们。对于诸如数据仓储等VLDBVLDBVLDBVLDB环境十分适合。环境十分适合。环境十分适合。环境十分适合。l l Archive Archive Archive Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提：为大量很少引用的历史、归档、或安全审计信息的存储和检索提：为大量很少引用的历史、归档、或安全审计信息的存储和检索提：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供供供供 了完美的解决方案。了完美的解决方案。了完美的解决方案。了完美的解决方案。l l Federated Federated Federated Federated：能够将多个分离的：能够将多个分离的：能够将多个分离的：能够将多个分离的MySQLMySQLMySQLMySQL服务器链接起来，从多个物理服务器创服务器链接起来，从多个物理服务器创服务器链接起来，从多个物理服务器创服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。建一个逻辑数据库。十分适合于分布式环境或数据集市环境。建一个逻辑数据库。十分适合于分布式环境或数据集市环境。建一个逻辑数据库。十分适合于分布式环境或数据集市环境。l l Cluster/NDB Cluster/NDB Cluster/NDB Cluster/NDB：MySQLMySQLMySQLMySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的的簇式数据库引擎，尤其适合于具有高性能查找要求的的簇式数据库引擎，尤其适合于具有高性能查找要求的的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。l l Other Other Other Other：其他存储引擎包括：其他存储引擎包括：其他存储引擎包括：其他存储引擎包括CSVCSVCSVCSV（引用由逗号隔开的用作数据库表的文件），（引用由逗号隔开的用作数据库表的文件），（引用由逗号隔开的用作数据库表的文件），（引用由逗号隔开的用作数据库表的文件），BlackholeBlackholeBlackholeBlackhole（用于临时禁止对数据库的应用程序输入），以及（用于临时禁止对数据库的应用程序输入），以及（用于临时禁止对数据库的应用程序输入），以及（用于临时禁止对数据库的应用程序输入），以及ExampleExampleExampleExample引擎引擎引擎引擎（可为快速创建定制的插件式存储引擎提供帮助）。（可为快速创建定制的插件式存储引擎提供帮助）。（可为快速创建定制的插件式存储引擎提供帮助）。（可为快速创建定制的插件式存储引擎提供帮助）。MyISAM MyISAM MyISAM MyISAM 特点特点特点特点MyISAM vs InnoDB MyISAM vs InnoDB 数据存储方式简单，使用数据存储方式简单，使用数据存储方式简单，使用数据存储方式简单，使用 B+Tree B+Tree 进行索引进行索引进行索引进行索引 使用三个文件定义一个表：使用三个文件定义一个表：使用三个文件定义一个表：使用三个文件定义一个表：.MYI .MYD .frm.MYI .MYD .frm 少碎片、支持大文件、能够进行索引压缩少碎片、支持大文件、能够进行索引压缩少碎片、支持大文件、能够进行索引压缩少碎片、支持大文件、能够进行索引压缩 二进制层次的文件可以移植二进制层次的文件可以移植二进制层次的文件可以移植二进制层次的文件可以移植 (Linux(Linux Windows)Windows)访问速度飞快，是所有访问速度飞快，是所有访问速度飞快，是所有访问速度飞快，是所有MySQLMySQL文件引擎中速度最快的文件引擎中速度最快的文件引擎中速度最快的文件引擎中速度最快的 不支持一些数据库特性，比如不支持一些数据库特性，比如不支持一些数据库特性，比如不支持一些数据库特性，比如 事务、外键约束等事务、外键约束等事务、外键约束等事务、外键约束等 Table level lockTable level lock，性能稍差，更适合读取多的操作，性能稍差，更适合读取多的操作，性能稍差，更适合读取多的操作，性能稍差，更适合读取多的操作 表数据容量有限，一般建议单表数据量介于表数据容量有限，一般建议单表数据量介于表数据容量有限，一般建议单表数据量介于表数据容量有限，一般建议单表数据量介于 50w50w200w 200w MyISAM vs InnoDBMyISAM vs InnoDBMyISAM MyISAM MyISAM MyISAM 索引结构索引结构索引结构索引结构MyISAM vs InnoDBMyISAM vs InnoDBMyISAM MyISAM MyISAM MyISAM 存储结构存储结构存储结构存储结构MyISAM vs InnoDBMyISAM vs InnoDBInnoDB InnoDB 特点特点 使用使用使用使用 Table Space Table Space 的方式来进行数据存储的方式来进行数据存储的方式来进行数据存储的方式来进行数据存储 (ibdata1,ib_logfile0)(ibdata1,ib_logfile0)支持支持支持支持 事务、外键约束等数据库特性事务、外键约束等数据库特性事务、外键约束等数据库特性事务、外键约束等数据库特性 Rows level lock,Rows level lock,读写性能都非常优秀读写性能都非常优秀读写性能都非常优秀读写性能都非常优秀 能够承载大数据量的存储和访问能够承载大数据量的存储和访问能够承载大数据量的存储和访问能够承载大数据量的存储和访问 拥有自己独立的缓冲池，能够缓存数据和索引拥有自己独立的缓冲池，能够缓存数据和索引拥有自己独立的缓冲池，能够缓存数据和索引拥有自己独立的缓冲池，能够缓存数据和索引 在关闭自动提交的情况下，与在关闭自动提交的情况下，与在关闭自动提交的情况下，与在关闭自动提交的情况下，与MyISAMMyISAM引擎速度差异不大引擎速度差异不大引擎速度差异不大引擎速度差异不大InnoDB InnoDB InnoDB InnoDB 数据结构数据结构数据结构数据结构MyISAM vs InnoDB MyISAM 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_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 innodb_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_buffer_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和和和和InnoDBInnoDB存储引擎性存储引擎性存储引擎性存储引擎性能差别并不是很大，针对能差别并不是很大，针对能差别并不是很大，针对能差别并不是很大，针对InnoDBInnoDB来说，影响性能的主要是来说，影响性能的主要是来说，影响性能的主要是来说，影响性能的主要是 innodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit 这个选项，如果设置为这个选项，如果设置为这个选项，如果设置为这个选项，如果设置为1 1的的的的话，那么每次插入数据的时候都会自动提交，导致性能急剧话，那么每次插入数据的时候都会自动提交，导致性能急剧话，那么每次插入数据的时候都会自动提交，导致性能急剧话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为下降，应该是跟刷新日志有关系，设置为下降，应该是跟刷新日志有关系，设置为下降，应该是跟刷新日志有关系，设置为0 0效率能够看到明显效率能够看到明显效率能够看到明显效率能够看到明显提升，当然，同样你可以提升，当然，同样你可以提升，当然，同样你可以提升，当然，同样你可以SQLSQL中提交中提交中提交中提交“SET AUTOCOMMIT=SET AUTOCOMMIT=0 0”来设置达到好的性能。来设置达到好的性能。来设置达到好的性能。来设置达到好的性能。同时也可以看出值得使用同时也可以看出值得使用同时也可以看出值得使用同时也可以看出值得使用 InnoDB InnoDB 来替代来替代来替代来替代 MyISAM MyISAM 引擎来进引擎来进引擎来进引擎来进行开发，毕竟行开发，毕竟行开发，毕竟行开发，毕竟InnoDB InnoDB 有多数据库特性、更良好的数据存储性有多数据库特性、更良好的数据存储性有多数据库特性、更良好的数据存储性有多数据库特性、更良好的数据存储性能和查询性能能和查询性能能和查询性能能和查询性能MySQLMySQL优化方式优化方式l l系统优化：硬件、架构系统优化：硬件、架构l l服务优化服务优化l l应用优化应用优化MySQL优化方式优化方式影响性能的因素影响性能的因素l l应用程序应用程序应用程序应用程序l l查询查询查询查询l l事务管理事务管理事务管理事务管理l l数据库设计数据库设计数据库设计数据库设计l l数据分布数据分布数据分布数据分布l l网络网络网络网络l l操作系统操作系统操作系统操作系统l l硬件硬件硬件硬件l l使用好的硬件，更快的硬盘、大内存、多核使用好的硬件，更快的硬盘、大内存、多核使用好的硬件，更快的硬盘、大内存、多核使用好的硬件，更快的硬盘、大内存、多核CPUCPUCPUCPU，专业的存，专业的存，专业的存，专业的存储服务器（储服务器（储服务器（储服务器（NASNASNASNAS、SANSANSANSAN）l l设计合理架构，如果设计合理架构，如果设计合理架构，如果设计合理架构，如果 MySQL MySQL MySQL MySQL 访问频繁，考虑访问频繁，考虑访问频繁，考虑访问频繁，考虑 Master/Slave Master/Slave Master/Slave Master/Slave 读写分离；数据库分表、数据库切片（分布式），也考虑使读写分离；数据库分表、数据库切片（分布式），也考虑使读写分离；数据库分表、数据库切片（分布式），也考虑使读写分离；数据库分表、数据库切片（分布式），也考虑使用相应缓存服务帮助用相应缓存服务帮助用相应缓存服务帮助用相应缓存服务帮助 MySQL MySQL MySQL MySQL 缓解访问压力缓解访问压力缓解访问压力缓解访问压力系统优化系统优化l l配置合理的配置合理的配置合理的配置合理的MySQLMySQLMySQLMySQL服务器，尽量在应用本身达到一个服务器，尽量在应用本身达到一个服务器，尽量在应用本身达到一个服务器，尽量在应用本身达到一个MySQLMySQLMySQLMySQL最合理的使用最合理的使用最合理的使用最合理的使用l l针对针对针对针对 MyISAM MyISAM MyISAM MyISAM 或或或或 InnoDB InnoDB InnoDB InnoDB 不同引擎进行不同定制性配置不同引擎进行不同定制性配置不同引擎进行不同定制性配置不同引擎进行不同定制性配置l l针对不同的应用情况进行合理配置针对不同的应用情况进行合理配置针对不同的应用情况进行合理配置针对不同的应用情况进行合理配置l l针对针对针对针对 f f f f 进行配置，后面设置是针对内存为进行配置，后面设置是针对内存为进行配置，后面设置是针对内存为进行配置，后面设置是针对内存为2G2G2G2G的服的服的服的服务器进行的合理设置务器进行的合理设置务器进行的合理设置务器进行的合理设置服务优化服务优化MySQLMySQL配置原则配置原则配置原则配置原则 服务优化服务优化公共选项公共选项公共选项公共选项选项选项缺省值缺省值推荐值推荐值说明说明max_connections1001024MySQL服务器同时处理的数据库连接的最大数量query_cache_size0(不打开）16M查询缓存区的最大长度，按照当前需求，一倍一倍增加，本选项比较重要sort_buffer_size512K16M每个线程的排序缓存大小，一般按照内存可以设置为2M以上，推荐是16M，该选项对排序order by，group by起作用record_buffer128K16M每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区，可以设置为2M以上table_cache64512为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。服务优化服务优化MyISAM MyISAM 选项选项选项选项选项缺省值推荐值说明key_buffer_size8M256M用来存放索引区块的缓存值,建议128M以上，不要大于内存的30%read_buffer_size128K16M用来做MyISAM表全表扫描的缓冲大小.为从数据表顺序读取数据的读操作保留的缓存区的长度myisam_sort_buffer_size16M128M设置,恢复,修改表的时候使用的缓冲大小，值不要设的太大服务优化服务优化InnoDB InnoDB 选项选项选项选项选项选项缺省值缺省值推荐值推荐值说明说明innodb_buffer_pool_size32M1GInnoDB使用一个缓冲池来保存索引和原始数据,这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少，一般是内存的一半，不超过2G，否则系统会崩溃，这个参数非常重要innodb_additional_mem_pool_size2M128MInnoDB用来保存 metadata 信息,如果内存是4G，最好本值超过200Minnodb_flush_log_at_trx_commit10 0 代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘;1 为执行完没执行一条SQL马上commit;2 代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.对速度影响比较大，同时也关系数据完整性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设计合理的数据表结构：适当的数据冗余设计合理的数据表结构：适当的数据冗余设计合理的数据表结构：适当的数据冗余设计合理的数据表结构：适当的数据冗余l l对数据表建立合适有效的数据库索引对数据表建立合适有效的数据库索引对数据表建立合适有效的数据库索引对数据表建立合适有效的数据库索引l l数据查询：编写简洁高效的数据查询：编写简洁高效的数据查询：编写简洁高效的数据查询：编写简洁高效的SQLSQLSQLSQL语句语句语句语句应用优化应用优化应用优化方式应用优化方式应用优化方式应用优化方式 应用优化应用优化表结构设计原则表结构设计原则表结构设计原则表结构设计原则l l 选择合适的数据类型：如果能够定长尽量定长选择合适的数据类型：如果能够定长尽量定长选择合适的数据类型：如果能够定长尽量定长选择合适的数据类型：如果能够定长尽量定长l l 使用使用使用使用 ENUMENUM而不是而不是而不是而不是 VARCHAR,ENUMVARCHAR,ENUM类型是非常快和紧凑的，在实际类型是非常快和紧凑的，在实际类型是非常快和紧凑的，在实际类型是非常快和紧凑的，在实际上，其保存的是上，其保存的是上，其保存的是上，其保存的是 TINYINTTINYINT，但其外表上显示为字符串。这样一来，用这个，但其外表上显示为字符串。这样一来，用这个，但其外表上显示为字符串。这样一来，用这个，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美字段来做一些选项列表变得相当的完美字段来做一些选项列表变得相当的完美字段来做一些选项列表变得相当的完美。l l 不要使用无法加索引的类型作为关键字段，比如不要使用无法加索引的类型作为关键字段，比如不要使用无法加索引的类型作为关键字段，比如不要使用无法加索引的类型作为关键字段，比如 texttexttexttext类型类型类型类型l l 为了避免联表查询，有时候可以适当的数据冗余，比如为了避免联表查询，有时候可以适当的数据冗余，比如为了避免联表查询，有时候可以适当的数据冗余，比如为了避免联表查询，有时候可以适当的数据冗余，比如 邮箱、姓名这些不容易更改的数据邮箱、姓名这些不容易更改的数据邮箱、姓名这些不容易更改的数据邮箱、姓名这些不容易更改的数据l l 选择合适的表引擎，有时候选择合适的表引擎，有时候选择合适的表引擎，有时候选择合适的表引擎，有时候 MyISAM MyISAM MyISAM MyISAM 适合，有时候适合，有时候适合，有时候适合，有时候 InnoDBInnoDBInnoDBInnoDB适合适合适合适合l l 为保证查询性能，最好每个表都建立有为保证查询性能，最好每个表都建立有为保证查询性能，最好每个表都建立有为保证查询性能，最好每个表都建立有 auto_increment auto_increment auto_increment auto_increment 字段，字段，字段，字段，建立合适的数据库索引建立合适的数据库索引建立合适的数据库索引建立合适的数据库索引l l 最好给每个字段都设定最好给每个字段都设定最好给每个字段都设定最好给每个字段都设定 default default default default 值值值值应用优化应用优化索引建立原则（一）索引建立原则（一）索引建立原则（一）索引建立原则（一）l l 一般针对数据分散的关键字进行建立索引，比如一般针对数据分散的关键字进行建立索引，比如一般针对数据分散的关键字进行建立索引，比如一般针对数据分散的关键字进行建立索引，比如IDIDIDID、QQQQQQQQ，像性别、状态值等等建立索引没有意义像性别、状态值等等建立索引没有意义像性别、状态值等等建立索引没有意义像性别、状态值等等建立索引没有意义l l 字段唯一，最少，不可为字段唯一，最少，不可为字段唯一，最少，不可为字段唯一，最少，不可为nullnullnullnulll l 对大数据量表建立聚集索引，避免更新操作带来的碎片。对大数据量表建立聚集索引，避免更新操作带来的碎片。对大数据量表建立聚集索引，避免更新操作带来的碎片。对大数据量表建立聚集索引，避免更新操作带来的碎片。l l 尽量使用短索引，一般对尽量使用短索引，一般对尽量使用短索引，一般对尽量使用短索引，一般对intintintint、char/varcharchar/varcharchar/varcharchar/varchar、date/time date/time date/time date/time 等等等等 类型的字段建立索引类型的字段建立索引类型的字段建立索引类型的字段建立索引l l 需要的时候建立联合索引，但是要注意查询需要的时候建立联合索引，但是要注意查询需要的时候建立联合索引，但是要注意查询需要的时候建立联合索引，但是要注意查询SQLSQLSQLSQL语句的编写语句的编写语句的编写语句的编写l l 谨慎建立谨慎建立谨慎建立谨慎建立 unique unique unique unique 类型的索引（唯一索引）类型的索引（唯一索引）类型的索引（唯一索引）类型的索引（唯一索引）l l 大文本字段不建立为索引，如果要对大文本字段进行检索，大文本字段不建立为索引，如果要对大文本字段进行检索，大文本字段不建立为索引，如果要对大文本字段进行检索，大文本字段不建立为索引，如果要对大文本字段进行检索，可以考虑全文索引可以考虑全文索引可以考虑全文索引可以考虑全文索引l l 频繁更新的列不适合建立索引频繁更新的列不适合建立索引频繁更新的列不适合建立索引频繁更新的列不适合建立索引应用优化应用优化索引建立原则（二）索引建立原则（二）索引建立原则（二）索引建立原则（二）l l order by order by order by order by 字句中的字段，字句中的字段，字句中的字段，字句中的字段，where where where where 子句中字段，最常用的子句中字段，最常用的子句中字段，最常用的子句中字段，最常用的sql sql sql sql 语句中字段，应建立索引。语句中字段，应建立索引。语句中字段，应建立索引。语句中字段，应建立索引。l l 唯一性约束，系统将默认为改字段建立索引。唯一性约束，系统将默认为改字段建立索引。唯一性约束，系统将默认为改字段建立索引。唯一性约束，系统将默认为改字段建立索引。l l 对于只是做查询用的数据库索引越多越好，但对于在线实时对于只是做查询用的数据库索引越多越好，但对于在线实时对于只是做查询用的数据库索引越多越好，但对于在线实时对于只是做查询用的数据库索引越多越好，但对于在线实时 系统建议控制在系统建议控制在系统建议控制在系统建议控制在5 5 5 5个以内。个以内。个以内。个以内。l l 索引不仅能提高查询索引不仅能提高查询索引不仅能提高查询索引不仅能提高查询SQLSQLSQLSQL性能，同时也可以提高带性能，同时也可以提高带性能，同时也可以提高带性能，同时也可以提高带wherewherewherewhere字句字句字句字句 的的的的updateupdateupdateupdate，Delete SQLDelete SQLDelete SQLDelete SQL性能。性能。性能。性能。l l Decimal Decimal Decimal Decimal 类型字段不要单独建立为索引，但覆盖索引可以包类型字段不要单独建立为索引，但覆盖索引可以包类型字段不要单独建立为索引，但覆盖索引可以包类型字段不要单独建立为索引，但覆盖索引可以包 含这些字段。含这些字段。含这些字段。含这些字段。l l 只有建立索引以后，表内的行才按照特地的顺序存储，按照只有建立索引以后，表内的行才按照特地的顺序存储，按照只有建立索引以后，表内的行才按照特地的顺序存储，按照只有建立索引以后，表内的行才按照特地的顺序存储，按照 需要可以是需要可以是需要可以是需要可以是ascascascasc或或或或descdescdescdesc方式。方式。方式。方式。l l 如果索引由多个字段组成将最用来查询过滤的字段放在前面如果索引由多个字段组成将最用来查询过滤的字段放在前面如果索引由多个字段组成将最用来查询过滤的字段放在前面如果索引由多个字段组成将最用来查询过滤的字段放在前面 可能会有更好的性能。可能会有更好的性能。可能会有更好的性能。可能会有更好的性能。应用优化应用优化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)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 条件写在前面，如果有恒量条件，条件写在前面，如果有恒量条件，条件写在前面，如果有恒量条件，条件写在前面，如果有恒量条件，也尽量放在前面也尽量放在前面也尽量放在前面也尽量放在前面l l 尽量避免使用尽量避免使用尽量避免使用尽量避免使用 GROUP BYGROUP BY、DISTINCT DISTI