mysql数据库常见的优化操作总结(经验分享)

前言

对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要。所以mysql数据库的优化操作大家都要有所了解,本文就主要总结了mysql数据库中常见的优化操作,下面话不多说了,来看看详细的介绍吧。

一、Index索引

将Index放第一位,不用说,这种优化方式我们一直都在悄悄使用,那便是主键索引。有时候我们可能并不在意,如果定义适合的索引,数据库查询性能(速度)将提高几倍甚至几十倍。

普通索引

作用是提高查询速度。

建表,创建索引

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*

可能有的人查询数据库时,遇到要查询的都会select,这是不恰当的行为。我们应该取我们要用的数据,而不是全取,因为当我们select时,会增加web服务器的负担,增加网络传输的负载,查询速度自然就下降 。

三、EXPLAIN SELECT

对于这个功能估计很多人都没见过,但是这里强烈推荐使用。explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。主要用发就是在select前加上explain即可。

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 是列的默认属性。通常情况下最好指定列为 NOT NULL,除非真 的需要存储 NULL 值。

如果查询中包含可为 NULL 的列,对 MySQL 来说更难优化 ,因为可为 NULL 的列使 得索引、索引统计和值比较都更复杂 。可为NULL 的列会使用更多的存储空间 ,在 MySQL 里也需要特殊处理 。当可为NULL 的列被索引肘,每个索引记录需要一个额 外的字节,在 MyISAM 里甚至还可能导致固定大小 的索引 (例如只有一个整数列的 索引) 变成可变大小的索引。

通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小 ,所以 (调优时) 没有 必要首先在现有schema中查找井修改掉这种情况 ,除非确定这会导致问题。但是, 如果计划在列上建索引 ,就应该尽量避免设计成可为 NULL 的列。当然也有例外 ,例如值得一提的是,InnoDB 使用单独的位 (bit ) 存储 NULL 值 ,所 以对于稀疏数据由有很好的空间效率 。但这一点不适用于MyISAM 。

六、存储引擎的选择

对于如何选择MyISAM和InnoDB,如果你需要事务处理或是外键,那么InnoDB可能是比较好的方式。如果你需要全文索引,那么通常来说MyISAM是好的选择,因为这是系统内建的,然而,我们其实并不会经常地去测试两百万行记录。所以,就算是慢一点,我们可以通过使用Sphinx从InnoDB中获得全文索引。

数据的大小,是一个影响你选择什么样存储引擎的重要因素,大尺寸的数据集趋向于选择InnoDB方式,因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短,InnoDB可以利用事务日志进行数据恢复,这会比较快。而MyISAM可能会需要

几个小时甚至几天来干这些事,InnoDB只需要几分钟。

您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如: COUNT() 在 MyISAM表中会非常快,而在InnoDB表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快,但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts语句在MyISAM下会快一些,但是updates在InnoDB 下会更快一些——尤其在并发量大的时候。

所以,到底你检使用哪一个呢?根据经验来看,如果是一些小型的应用或项目,那么MyISAM也许会更适合。当然,在大型的环境下使用MyISAM也会有很大成功的时候,但却不总是这样的。如果你正在计划使用一个超大数据量的项目,而且需要事务处理或外键支持,那么你真的应该直接使用InnoDB方式。但需要记住InnoDB的表需要更多的内存和存储,转换100GB的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。

七、避免在 where 子句中使用 or 来连接

如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

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 ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

九、避免大数据量返回

这里要考虑使用limit,来限制返回的数据量,如果每次返回大量自己不需要的数据,也会降低查询速度。

十、where子句优化

where 子句中使用参数,会导致全表扫描,因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。

应尽量避免在 where 子句中对字段进行表达式操作,避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

(0)

相关推荐

  • mysql 5.7安装 MySQL 服务无法启动但是服务没有报告任何错误

    MySQL 5.7的安装有两种方式,一种是下载安装程序,点击安装,另一种是下载压缩包,直接解压,配置一下就可以运行了 个人更喜欢压缩包的形式,所以本文记录的也是压死包形式的安装(配置) https://dev.mysql.com/downloads/mysql/ 我下载的最新的是 mysql-5.7.17-win32.zip 加压到mysql-5.7.17-win32文件夹,后文提到的文件和文件夹都以此为更目录 把bin目录配置到环境变量path的后面 我的是  ;D:\mysql-5.7.17

  • SQL 获取所有上级的实现方法

    SQL 获取所有上级的实现方法 说明: (1)可以做成一个函数,直接调用即可: (2) M0Org 是数据库存在的表 ,M0OrgPID 为目标表,OrgID为ID字段,PID为上级ID字段 DECLARE @OrgID NVARCHAR(36)= '00000000-0000-0000-00000002205223459'; --获取当前机构的所有上级 放入M0OrgPID WITH M0OrgPID AS ( SELECT *, 0 AS lvl FROM M0Org WHERE OrgID

  • SQL Server 使用触发器(trigger)发送电子邮件步骤详解

    sql 使用系统存储过程 sp_send_dbmail 发送电子邮件语法: sp_send_dbmail [ [ @profile_name = ] 'profile_name' ] [ , [ @recipients = ] 'recipients [ ; ...n ]' ] [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ] [ , [ @blind_copy_recipients = ] 'blind_copy_recipien

  • Mysql中Join的使用实例详解

    在前几章节中,我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据. 本章节我们将向大家介绍如何使用MySQL 的 JOIN 在两个或多个表中查询数据. 你可以在SELECT, UPDATE 和 DELETE 语句中使用Mysql 的 join 来联合多表查询. 以下我们将演示MySQL LEFT JOIN 和 JOIN 的使用的不同之处. 在命令提示符中使用JOIN 我们在RUNOOB数据库中有两张表 tcount_tbl 和 runoob_t

  • mysql处理海量数据时的一些优化查询速度方法

    由于在参与的实际项目中发现当mysql表的数据量达到百万级时,普通SQL查询效率呈直线下降,而且如果where中的查询条件较多时,其查询速度简直无法容忍.曾经测试对一个包含400多万条记录(有索引)的表执行一条条件查询,其查询时间竟然高达40几秒,相信这么高的查询延时,任何用户都会抓狂.因此如何提高sql语句查询效率,显得十分重要.以下是网上流传比较广泛的30种SQL查询语句优化方法: 1.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描. 2.对查询

  • mysql安装配置方法图文教程(CentOS7)

    一.系统环境 [root@localhost home]# cat /etc/redhat-release CentOS Linux release 7.2.1511 (Core) 二.mysql安装 安装mysql和mysql-devel都成功,但是安装mysql-server失败,如下: [root@localhost home]# yum install mysql-server 已加载插件:fastestmirror, langpacks Repodata is over 2 weeks

  • mysql数据库常见的优化操作总结(经验分享)

    前言 对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要.所以mysql数据库的优化操作大家都要有所了解,本文就主要总结了mysql数据库中常见的优化操作,下面话不多说了,来看看详细的介绍吧. 一.Index索引 将Index放第一位,不用说,这种优化方式我们一直都在悄悄使用,那便是主键索引.有时候我们可能并不在意,如果定义适合的索引,数据库查询性能(速度)将提高几倍甚至几十倍. 普通索引 作用是提高查询速度. 建表,创建索引 CREATE TABLE tbl_

  • mysql数据库索引损坏及修复经验分享

    mysql表索引被破坏的问题及解决 下午上班,惊闻我的dedecms的网站出问题了,访问一看,果然全屏报错,检查mysql日志,错误信息为: Table '.\dedecmsv4\dede_archives' is marked as crashed and should be repaired 提示说cms的文章表dede_archives被标记有问题,需要修复.于是赶快恢复历史数据,上网查找原因.最终将问题解决.解决方法如下: 找到mysql的安装目录的bin/myisamchk工具,在命令

  • mysql数据库常见基本操作实例分析【创建、查看、修改及删除数据库】

    本文实例讲述了mysql数据库常见基本操作.分享给大家供大家参考,具体如下: 本节相关: 创建数据库 查看数据库 修改数据库 删除数据库 首发时间:2018-02-13 20:47 修改: 2018-04-07:考虑到规范化,将所有语法中"关键字"变成大写;以及因为整理"mysql学习之路",移除字符集和校对集问题并归成一个新博文. 创建数据库  : 语法  : CREATE DATABASE 数据库名字[库选项]; 库选项说明  : 库选项是可选项,可以不写 ,如

  • MySQL数据库21条最佳性能优化经验

    今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情. 当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库.希望下面的这些优化技巧对你有用. 1. 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存.这是提高性最有效的方法之一,而且这是被M

  • MySQL数据库InnoDB引擎主从复制同步经验总结

    近期将公司的MySQL架构升级了,由原先的一主多从换成了DRBD+Heartbeat双主多从,正好手上有一个电子商务网站新项目也要上线了,用的是DRBD+Heartbeat双主一从,由于此过程还是有别于以前的MyISAM引擎的,所以这里也将其心得归纳总结了一下: 1)MySQL的replication过程是一个异步同步的过程,并非完全的主从同步,所以同步的过程中是有延迟的,如果做了读写分离的业务的话,建议也要监控此延迟时间: 2)MySQL的master与slave机器记得server-id要保

  • Python操作MySQL数据库的两种方式实例分析【pymysql和pandas】

    本文实例讲述了Python操作MySQL数据库的两种方式.分享给大家供大家参考,具体如下: 第一种 使用pymysql 代码如下: import pymysql #打开数据库连接 db=pymysql.connect(host='1.1.1.1',port=3306,user='root',passwd='123123',db='test',charset='utf8') cursor=db.cursor()#使用cursor()方法获取操作游标 sql = "select * from tes

  • PHP连接MySQL数据库的三种方式实例分析【mysql、mysqli、pdo】

    本文实例讲述了PHP连接MySQL数据库的三种方式.分享给大家供大家参考,具体如下: PHP与MySQL的连接有三种API接口,分别是:PHP的MySQL扩展 .PHP的mysqli扩展 .PHP数据对象(PDO) ,下面针对以上三种连接方式做下总结,以备在不同场景下选出最优方案. PHP的MySQL扩展是设计开发允许php应用与MySQL数据库交互的早期扩展.MySQL扩展提供了一个面向过程的接口,并且是针对MySQL4.1.3或者更早版本设计的.因此这个扩展虽然可以与MySQL4.1.3或更

  • PHP5.5基于mysqli连接MySQL数据库和读取数据操作实例详解

    本文实例讲述了PHP5.5基于mysqli连接MySQL数据库和读取数据操作.分享给大家供大家参考,具体如下: 在学习1. 开启PHP的API支持 (1)首先修改您的php.ini的配置文件. 查找下面的语句: ;extension=php_mysqli.dll 将其修改为: extension=php_mysqli.dll (2)重新启动Apache/IIS,即可. (3)说明:PHP需要单独的文件来支持这个扩展库,一般在PHP目录下的ext目录里能找到php_mysqli.dll文件(PHP

  • php将图片保存入mysql数据库失败的解决方法

    本文实例分析了php将图片保存入mysql数据库失败的解决方法.分享给大家供大家参考.具体分析如下: 图片保存数据库并不是一个明智的做法,我们多半是把图片保存到服务器,然后把图片地址保存到数据库,这样我们每次只要读出图片地址就可以显示了,但下面我还是来介绍一个图片保存到mysql数据库的问题解决办法,代码如下: 复制代码 代码如下: require 'class/db.php'; $fileName = "a1.jpg"; $fp = fopen($fileName, "r&

  • PHP中用mysqli面向对象打开连接关闭mysql数据库的方法

    如下所示: 代码如下: <meta http-equiv="content-type" content="text/html" charset="utf-8"/> <h1>用mysqli面向对象方法连接数据库!-姚远的博客</h1> <form method="POST" action="<?php echo htmlspecialchars($_SERVER['PHP

随机推荐