1.需求的影响

例子:统计一个热门论坛的发帖总量,要求实时更新

方案1:SELECT COUNT(*) from 表名  可以得到结果。

采用MyISAM 存储引擎,没有where的count(*)要比InnoDB快得多,因为MyISAM内置了一个计数器,count(*)时它直接从计数器中读,而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where,且where中要包含主键以外的索引列。如果存放帖子的表中已经有上千万的帖子的时候,执行这条Query 语句需要成本就很高

方案2:专门建一个表,就只有一个字段,一条记录,就存放这个统计量,每新增帖子,将这个值增加1,每次都只需要查询这个表就可以得到结果了。查询效率肯定能够满足要求,可是如果帖子产生很快,在高峰时期可能每秒就有几十甚至上百个帖子新增操作的时候,可能会因为并发的问题造成统计结果的不准确,也可能因为锁资源争用严重造成整体性能的大幅度下降。

方案三:如果对这个总数的准确性要求不高,可以去掉这个“实时更新”的附加条件,就可以非常容易的实现这个功能了。利用方案二通过创建一个统计表,然后通过一个定时任务每隔一定时间段去更新一次里面的统计值,这样既可以解决统计值查询的效率问题,又可以保证不影响新发贴的效率。

2.数据类型的影响

以下几类数据都是不适合在数据库中存放的:

二进制多媒体数据:主要包括图片,音频、视频和其他一些相关的二进制文件。存放这些数据会造成数据库空间资源耗用非常严重,这些数据的存储很消耗数据库主机的CPU 资源

超大文本数据:对于5.0.3之前的MySQL版本,VARCHAR 类型的数据最长能存放255 个字节,TEXT 类型最大可存放64KB,LONGTEXT 类型最大4GB。而TEXT类型数据的处理性能要远比VARCHAR 类型数据的处理性能低下很多。从5.0.3版本开始,VARCHAR 类型的最大长度被调整到64KB了,所以,超大文本数据存放在数据库中不仅会带来性能低下的问题,还会带来空间占用的浪费问题

3.查询语句对性能的影响

在数据库管理软件中,最大的性能瓶颈就是在于磁盘IO,也就是数据的存取操作上面。

查看执行计划

explain sql命令

打开mysql的profiling 功能,来查看sql的实际执行计划

set profiling=1

获取当前系统中保存的多个Query 的profile 的概要信息。

show profile CPU,BLOCK IO for query 1;

性能优化------索引

索引可以让mysql高效运行,可以大大提高mysql的查询(包括排序,分组)效率;数据约束(唯一索引的建立)。

索引本身是以文件的形式存放在硬盘,需要的时候才加载至内存,所以添加索引会增加磁盘的开销

写数据:需要更新索引,对数据库是个很大的开销,降低表更新、添加和删除的速度

不建议使用索引的情况:

a) 表记录较少

b) 索引的选择性较低,所谓索引的选择性,是指不重复的索引值与表记录数的比值,取值范围(0-1)。选择性越高,索引的价值越大。

索引类型:

单列索引和组合索引

单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

普通索引:这是最基本的索引,它没有任何限制

CREATE UNIQUE INDEX indexName ON tablename(column1[,column2,……])主键索引:一种特殊的唯一索引,不允许有空值,一般在建表的时候同时建立主键索引

CREATE TABLE tablename(ID INT NOT NULL,  username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

组合索引:最左前缀

例如:假设存在组合索引index1(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是,查询语句select * from t1 where c2=2不能够使用该索引,因为没有组合索引的引导列,即要想使用c2列进行查找,必需出现c1等于某值。因此我们在创建组合索引时应该将最常用作限制条件的列放在最左边,依次递减。

全文索引:只用于MyISAM表 对文本域进行索引。字段类型包括char、varchar、text

不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

CREATE FULLTEXT INDEX indexname ON tablename(column)建立索引时机:在WHERE和JOIN子句中出现的列需要建立索引,索引的列的基数越大,索引的效果越好。

注意事项:

以通配符%和_开头作查询时,MySQL不会使用索引

不要在列上进行运算,将在每个行上进行运算,这将导致索引失效而进行全表扫描

使用短索引,如果对字符串列进行索引,应该指定一个前缀长度,可节省大量索引空间,提升查询速度。

不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。 在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。MySQL只对一下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。

性能优化------慢查询分析,优化索引和配置

基本思路

1.性能瓶颈定位

show 命令

show processlist; -- 显示系统中正在运行的所有进程,也就是当前正在执行的查询。大多数用户可以查看他们自己的进程,但是如果他们拥有process权限,就可以查看所有人的进程,包括密码。

查看MySQL服务器配置信息mysql>show variables;

查看MySQL服务器运行的各种状态值mysql>show global status;

慢查询日志

在配置文件my.cnf中在[mysqld]一行下面加入3个配置参数,并重启mysql服务

我们也可以通过命令行设置变量来即时启动慢日志查询

mysql>set global slow_query_log = on; mysql>set long_query_time = 0.01; mysql>set global slow_query_log_file = "/usr/local/mysql/data/slow-query.log";查看慢查询的设置信息

mysql>show variables like '%slow_query_log%'; mysql>show variables like '%long_query_time%';如果慢查询日志中记录内容很多,可以使用mysqldumpslow工具(MySQL客户端安装自带)来对慢查询日志进行分类汇总。

explain分析查询

profiling分析查询

通过慢日志查询可以知道哪些SQL语句执行效率低下,通过explain我们可以得知SQL语句的具体执行情况,索引使用等,还可以结合show命令查看执行状态。如果觉得explain的信息不够详细,可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。

2.索引及查询优化

3.配置优化

连接请求的变量:max_connections

MySQL的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多, MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。

数值过小会经常出现ERROR 1040: Too many connections错误,可以过mysql>show status like 'connections';通配符查看当前状态的连接数量(试图连接到MySQL(不管是否连接成功)的连接数),以定夺该值的大小。

mysql>show variables like ‘max_connections’ 最大连接数 mysql>show  status like ‘max_used_connections’ 响应的连接数 max_used_connections / max_connections * 100% (理想值≈ 85%) 如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了,低于10%则设置过大。

修改/etc/my.cnf文件,在[mysqld]下面添加如下内容,如设置最大连接数为1024

max_connections = 1024重启mysql服务

MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,它就会起作用。如果MySQL的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。

back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它。

当观察你主机进程列表(mysql>show full processlist),发现大量xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时,就要加大back_log的值了或加大max_connections的值。

通过mysql>show variables like 'back_log';查看back_log的设置

修改/etc/my.cnf文件,在[mysqld]下面添加如下内容,如设置最大连接数为1024

back_log = 数值重启mysql服务

还有其他配置优化项,这里不列这么多了

MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候那么平瓶颈就会出现在网络上,我们可以用mpstat,iostat, sar和vmstat来查看系统的性能状态。