mysql索引优化项目经验_mysql常见优化操作的总结（经验共享）

前言

对于以数据为中心的应用程序，的质量直接影响程序的性能，因此性能至关重要. 因此，每个人都必须了解mysql的优化操作. 本文主要总结了mysql中的常见优化操作. 我不再赘述. 让我们看一下详细的介绍.

一个，索引

首先输入索引. 不用说，我们一直在悄悄地使用这种优化方法. 那是主键索引. 有时我们可能不在乎，如果定义了合适的索引，查询性能(速度)将提高几倍甚至几十倍.

一般索引

该功能是为了提高查询速度.

建立表格，创建索引

CREATE TABLE tbl_name(

字段名称 字段类型 [完整性约束条件],

~

index [索引名] (column_name)

);

创建索引

CREATE INDEX index_name ON tab_name (column_name)

删除索引

DROP INDEX index_name FROM tab_name

查看索引

SHOW index FROM tab_name

主键索引

角色是加速查询和唯一约束

建立表格，创建索引

CREATE TABLE tbl_name(

字段名称 字段类型 [完整性约束条件],

~

PRIMARY KEY(column_name)

);

创建索引

ALTER TABLE tab_name ADD PRIMARY KEY(column_name)

删除索引

ALTER TABLE tab_name DROP PRIMAY KEY(column_name)

唯一索引

角色是加速查询和唯一约束

建立表格，创建索引

CREATE TABLE tbl_name(

字段名称 字段类型 [完整性约束条件],

~

unique [索引名] (column_name)

);

创建索引

CREATE UNIQUE INDEX index_name ON tab_name (column_name)

删除索引

DROP UNIQUE INDEX index_name FROM tab_name

第二，使用较少的SELECT *

当某些人查询时，他们将选择要查询的时间，这是不适当的行为. 我们应该获取我们要使用的数据，而不是全部，因为选择该数据将增加Web服务器的负担，增加网络传输的负载，并且查询速度自然会降低.

三，说明选择

估计许没有看到此功能常用的优化方法，但强烈建议使用. 说明显示了mysql如何使用索引来处理选择语句和联接表. 可以帮助选择更好的索引并编写更多优化的查询语句. 主要用途是在选择之前添加说明.

EXPLAIN SELECT [查找字段名] FROM tab_name ...

四，打开查询缓存

大多数MySQL服务器都启用了查询缓存. 这是提高性能的最有效方法之一，它由MySQL引擎处理. 多次执行许多相同的查询时，这些查询的结果将放入缓存中，以便后续相同的查询将直接访问缓存的结果，而无需操作表.

第一步是将query_cache_type设置为ON，然后查询系统变量have_query_cache是​​否可用:

show variables like 'have_query_cache'

之后

，将内存大小分配给查询缓存，以控制缓存的查询结果的最大值. 相关操作在配置文件中进行了修改.

五，使用NOT NULL

许多表包含的列可以是NULL(空)，即使应用程序很好地不需要保存NULL也是如此，因为NULL是列的默认属性. 除非确实有必要存储NULL值，否则通常最好将列指定为NOT NULL.

如果查询包含NULL列，则MySQL难以优化，因为NULL列会使索引，索引统计信息和值比较更加复杂. 可以为NULL的列将使用更多的存储空间，并需要在MySQL中进行特殊处理. 当一个可以为NULL的列被索引时，每个索引记录都需要一个额外的字节. 在MyISAM中，即使是固定大小的索引(例如只有一个整数列的索引)也可能成为可变大小的索引.

通常，将可以将NULL的列更改为NOT NULL会带来相对较小的性能改进，因此(调整时)除非必须确定存在以下情况，否则不必先在现有模式中找到井来修改这种情况. 这会引起问题. 但是，如果计划在列上建立索引，则应尽量避免将其设计为NULL列. 当然，也有例外. 例如，值得一提的是InnoDB使用单个位来存储NULL值，因此对于稀疏数据具有良好的空间效率. 但这不适用于MyISAM.

六常用的优化方法，存储引擎的选择

对于如何选择MyISAM和InnoDB，如果需要事务处理或外键，那么InnoDB可能是更好的方法. 如果您需要全文本索引，那么MyISAM通常是一个不错的选择，因为它是内置在系统中的，但是，我们实际上并不经常测试两百万行的记录. 因此，即使速度稍慢，我们也可以使用Sphinx从InnoDB获取全文索引.

数据大小是影响选择哪种存储引擎的重要因素. 大型数据集倾向于选择InnoDB方法，因为它支持事务处理和故障恢复. 的大小决定了故障恢复的时间长度. InnoDB可以使用事务日志进行数据恢复，这将更快. MyISAM可能需要它

InnoDB只需几分钟即可完成数小时甚至数天的操作.

您操作表的习惯也可能是极大影响性能的一个因素. 例如: 在MyISAM表中COUNT()将非常快，但是在InnoDB表下可能会很痛苦. 在InnoDB下，主键查询将非常快，但是如果我们的主键过长也会引起性能问题，请务必小心. 在MyISAM下，大量的inserts语句会更快，但在InnoDB下，更新会更快，尤其是在并发量很大时.

那么，您使用哪一个？根据经验，如果是一些小型应用程序或项目，则MyISAM可能更适合. 当然，在大型环境中使用MyISAM也将获得巨大的成功，但这并非总是如此. 如果您打算使用具有大量数据的项目，并且需要事务处理或外键支持，那么您实际上应该直接使用InnoDB. 但是请记住，InnoDB表需要更多的内存和存储空间. 将100GB MyISAM表转换为InnoDB表可能会给您带来非常糟糕的体验.

七，避免在where子句中使用或连接

如果字段具有索引而字段没有索引，则将导致引擎放弃索引并执行全表扫描，例如:

select id from t where num=10 or Name = 'admin'

您可以这样查询它:

select id from t where num = 10

union all

select id from t where Name = 'admin'

八，多使用varchar / nvarchar

使用varchar / nvarchar代替char / nchar，因为可变长度字段的存储空间很小，可以节省存储空间，其次，对于查询，在较小字段中的搜索效率显然更高.

九，避免返回大量数据

此处应考虑限制，以限制返回的数据量. 如果每次都返回大量不需要的数据，查询速度也会降低.

X. Where子句优化

在where子句中使用参数会导致全表扫描，因为SQL仅在运行时解析局部变量，但优化器无法将对访问计划的选择推迟到运行时. 必须在编译时选择它. 但是，如果在编译时建立了访问计划，则该变量的值仍然未知，因此不能用作索引选择的输入.

请尝试避免对where子句中的字段执行表达式操作，并避免对where子句中的字段执行函数操作. 这将导致引擎放弃使用索引并执行全表扫描. 不要在where子句中“ =”的左侧执行函数，算术运算或其他表达式运算，否则系统可能无法正确使用索引.

摘要

以上是本文的全部内容. 我希望本文的内容可以为每个人的学习或工作带来帮助. 如有任何疑问，可以留言交流. 感谢您对脚本库的支持.

本文来自电脑杂谈，转载请注明本文网址：

http://www.pc-fly.com/a/jisuanjixue/article-234682-1.html