原文:http://blog.csdn.net/hguisu/article/details/5731629
SQL Server 数据库性能优化:http://www.cnblogs.com/sydeveloper/archive/2013/04/03/2992881.html
笔记:
1. 学会查看数据库IO, 来决定数据库的设计, set statistic time on, set statistic IO on
2. 学会看执行计划
3. 索引的注意。
索引尽量短小
不要用null字段, null字段上不能建索引
不要用 like %, !=, or, in, 函数 在索引上
4. 查询语句注意
用exist, not exist 代替in,not in, in 先执行内查询, exist先执行外查询,优先先执行的快速返回。
尽量避免select *
5. count优化, count(*) 会用非聚集索引
6. 使用union 代替索引上的OR。
7. 索引的选择。使用自增字段索引,效率高,但是在合表的时候会有问题,使用GUID作为索引,效率太低,可以采用两者合并的方式,使用GUID的前10位加上timestamp做索引。
8. MySQL中, myisam表定义,数据,索引分别在不同文件中,使用Btree,INNODB中,数据索引在一个文件中,使用B+ tree,聚集索引叶子节点是数据
查看执行时间和cpu
set statistics time on select * from Bus_DevHistoryData set statistics time off
执行后在消息里可以看到
2. 查看查询对I/O的操作情况
set statistics io on select * from Bus_DevHistoryData set statistics io off
执行之后的结果:
扫描计数:索引和表执行次数
逻辑读取:数据缓存中读取的页数
物理读取:从磁盘中读取的页数
预读:查询过程中,从磁盘放入缓存的页数
lob逻辑读取:从数据缓存中读取image、text、ntext或大型数据的页数
lob物理读取:从磁盘中读取image、text、ntext或大型数据的页数
lob预读:查询过程中,从磁盘放入缓存的image、text、ntext或大型数据的页数
如果物理读取次数和预计次数比较多,可以使用索引进行优化。
上述两种信息的查看如果不想写sql,可以通过设置完成:
工具->选项
3. 查看执行计划
选中查询语句,点击
一、数据库设计优化
1、不要使用游标。
使用游标不仅占用内存,而且还用不可思议的方式锁定表,它们可以使DBA所能做的一切性能优化等于没做。游标里每执行一次fetch就等于执行一次select。
2、创建适当的索引
每当为一个表添加一个索引,select会更快,可insert和delete却大大变慢,因为创建了维护索引需要许多额外的工作。
(1)采用函数处理的字段不能利用索引
(2)条件内包括了多个本表的字段运算时不能进行索引
3、使用事务
对于一些耗时的操作,使用事务可以达到很好的优化效果。
4、小心死锁
按照一定的次序来访问你的表。如果你先锁住表A,再锁住表B,那么在所有的存储过程中都要按照这个顺序来锁定它们。 如果某个存储过程先锁定表B,再锁定表A,这可能会导致一个死锁。
5、不要打开大的数据集
6、不要使用服务器端游标
与服务器端游标比起来,客户端游标可以减少服务器和网络的系统开销,并且还减少锁定时间。
7、不要忽略同时修改同一记录的问题
有时候,两个用户会同时修改同一记录,这样,后一个修改者修改了前一个修改者的操作,某些更新就会丢失。处理这种情况,创建一个timestamp字段,在写入前检查它,如果允许,就合并修改,如果存在冲突,提示用户。
8、尽量不要使用text数据类型
除非使用text处理一个很大的数据,否则不要使用它。因为它不易于查询,速度慢,用的不好还会浪费大量的空间。一般varchar可以更好的处理数据。
9、避免在索引列上使用计算
where子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描。例如:
(低效)select ... from [dept] where [sal]*12>25000;
(高效)select ... from [dept] where [sal]>25000/12;
10、不同类型的索引效能是不一样的,应尽可能先使用效能高的
数字类型的索引查找效率高于字符串类型,定长字符串char、nchar的索引效率高于变长字符串varchar、nvarchar的索引。
(低效)select ... from tableName where username='张三' and age>=21
(高效)select ... from tableName where age>=21 and username='张三'
二、SQL语句优化
1、不要使用select *
在select中指定所需要的列,将带来的好处:
(1)减少内存耗费和网络的带宽
(2)更安全
(3)给查询优化器机会从索引读取所有需要的列
2、使用参数查询
主要是防止SQL注入,提高安全性。
3、使用exists或not exists代替in或not in
(高效)select * from [emp] where [empno]>0 and exists (select 'X' from [dept] where [dept].[deptno]=[emp].[deptno] and [loc]='MELB');
(低效)select * from [emp] where [empno]>0 and [deptno] in (select [deptno] from [dept] where [loc]='MELB');
4、is null或is not null操作
判断字段是否为空一般是不会应用索引的,因为索引不索引空值。不能用null作索引,任何包含null值的列都将不会被包含在索引中。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器都不允许使用索引。
推荐方案:用其他相同功能的操作运算代替,如:a is not null改为a>0或a>''等。
5、<及>操作
大于或小于一般情况不用调整,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化。如一个表有100万记录,那么执行>2与>=3的效果就有很大区别了。
(低效)select * from [emp] where [deptno]>2;
(高效)select * from [emp] where [deptno]>=3;
6、like操作
like操作可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用不好则会产生性能上的问题,如lide '%5400%' 这种查询不会引用索引,而like 'X5400%' 则会引用范围索引。
7、where后面的条件顺序影响
where子句后面的条件顺序对大数据量表的查询会产生直接的影响。如:
select * from zl_yhjbqk where dy_dj='1KV以下' and xh_bz=1;
select * from zl_yhjbqk where dy_dj=1 and dy_dj='1KV以下';
以上两个查询,两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj='1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较。而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。
8、用union替换or(适用于索引列)
通常情况下,用union替换where子句中的or将会起到较好的效果。对索引列使用or将造成全表扫描。注意:这个规则只针对多个索引列有效。如果有column没有被索引,查询效率可能会因为你没有选择or而降低。下面的例子中loc_id和region上都有建索引。
(低效)select loc_id,loc_desc,begion from location where loc_id=10 or begion='MELBOURNE';
(高效)select loc_id,loc_desc,begion from location where loc_id=10
union
select loc_id,loc_desc_begion from location where begion='MELBOURNE';
9、优化group by
提高group by语句的效率,可以通过将不需要的记录在group by之前过滤掉。
(低效)select [job],avg([sal]) from [emp] group by [job] having job='PRESIDENT' or job='MANAGER';
(高效)select [job],avg([sal]) from [emp] where [job]='PRESIDENT' or job='MANAGER' group by [job];
10、使用存储过程
可以考虑使用存储过程封装那些复杂的SQL语句或业务逻辑,这样有几个好处:
(1)存储过程的执行计划可以被缓存在内存中较长的时间,减少了重新编译的时间。
(2)存储过程减少了客户端和服务器的繁复交互。
(3)如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序,避免需要重新安装部署程序。
11、用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时,服务器自动取消查询,在查询之前就扼杀掉。SET LOCKTIME设置锁的时间。
12、使用select top或set rowcount来限制操作的行。
13、如果使用了in或or等时发现查询没有走索引,使用显式申明指定索引: SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN ('男','女')。
14、如果要插入大的二进制值到Image列,使用存储过程,千万不要用内嵌insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍),服务器受到字符后又将他转换成二进制值。存储过程就没有这些动作: 方法:Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数,这样处理速度明显改善。
15、分析select emp_name form employee where salary>3000 在此语句中若salary是Float类型的,则优化器对其进行优化为Convert(float,3000),因为3000是个整数,我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。
三、处理百万级以上数据提高查询速度的方法
1、尽量避免在where子句中使用!=或<>操作符,否则将使引擎放弃使用索引而进行全表扫描。
2、应考虑在where及order by涉及的列上建立索引。
3、尽量避免在where子句中对字段进行null值判断,否则将导致全表扫描。
4、就是避免在where子句中使用or来连接条件,否则将导致全表扫描。
select id from t where num=10 or num=20 改写为
select id from t where num=10
union all
select id from t where num=20
5、尽量避免使用前置百分号。
select id from t where name like '%abc%'
6、in 和not in也要慎用,很多时候可以用exists和not exists,否则会导致全表扫描。
7、如果在where子句中使用参数,也会导致全表扫描。
select id from t where num=@num 可以改为强制查询使用索引
select id from t with(index(索引名)) where num=@num
8、尽量避免在where子句中对字段进行表达式操作,否则将导致全表扫描。
select id from t where num/2=100
应改为:
select id from t where num=100*2
9、尽量避免在where子句中对字段进行函数操作,否则将导致全表扫描。
select id from t where substring(name,1,3)='abc'
应改为:
select id from t where name like 'abc%'
10、并不是所有索引对查询都有效,SQL根据表中数据来进行查询优化,当索引列有大量数据重复时,SQL查询可能不会去利用索引。
11、索引并不是越多越好,索引提交了select效率,但是降低了insert和update的效率。一个表的索引数最好不要超过6个。
12、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每个字符,而对于数字型而言只需要比较一次就够了。
13、尽可能使用varchar/nvarchar代替char/nchar,因为首先变长字段存储空间小,可以节省存储空间;其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
14、任何地方都不要使用select *,用具体的字段列表代替*,不要返回用不到的字段。
15、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就考虑改写。
16、尽量避免大事务操作,提高系统并发能力。
17、利用set rowcount实现高性能的分页。
Declare @ID int Declare @MoveRecords int --@CurrentPage和@PageSize是传入参数 Set @MoveRecords=@CurrentPage * @PageSize+1 --下面两行实现快速滚动到我们要取的数据的行,并把ID记录下来 Set Rowcount @MoveRecords Select @ID=ID from Table1 Order by ID Set Rowcount @PageSize Select * From Table1 Where ID>=@ID Order By ID Set Rowcount 0
四、数据库主键选取
常见的数据库主键选取方式有:
●自动增长字段
●Uniqueidentifier
●“COMB(Combine)”类型
1、自动增长字段
优点:
(1)简单、效率高。
缺点:
(1)自增一般使用int型,有数据条数的限制。
(2)在数据库进行数据合并时会比较麻烦。
2、GUID
优点:
(1)安全,保证唯一性。
(2)不会产生自增字段那样数据合并时的问题。
缺点:
(1)它的长度是16字节,占用大量存储空间。
(2)该数据类型毫无规律,要在上面建立索引很耗时,所以效率要比使用自增字段低。
3、COMB
考虑到上面两种主键类型的优缺点,这里使用COMB类型可以为两者找到了一个平衡点。它的设计思路是这样的:既然GUID类型无规律可言造成索引效率低下,影响系统的性能,那么能不能通过组合的方式,保留GUID前10个字节,用后6个字节表示GUID生成的时间,这样即保证了唯一性同时增加了有序性,以此来提高索引效率。后6字节的时间精度可以达到1/300秒,两个COMB类型数据完全相同的可能性是在这1/300秒内生成的两个GUID前10个字节完全相同,这几乎是不可能的。
(1)SQL Server中SQL命令实现这一思路的方式:
SET @aGuid = CAST ( CAST ( NEWID () AS BINARY ( 10 )) + CAST ( GETDATE () AS BINARY ( 6 )) AS UNIQUEIDENTIFIER )
(2)实现COMB数据的C#方式:
/// 返回 GUID 用于数据库操作,特定的时间代码可以提高检索效率
/// </summary>
/// <returns> COMB (GUID 与时间混合型) 类型 GUID 数据 </returns>
public static Guid NewComb()
{
byte [] guidArray = System.Guid.NewGuid().ToByteArray();
DateTime baseDate = new DateTime( 1900 , 1 , 1 );
DateTime now = DateTime.Now;
// Get the days and milliseconds which will be used to build the byte string
TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks);
TimeSpan msecs = new TimeSpan(now.Ticks - ( new DateTime(now.Year, now.Month, now.Day).Ticks));
// Convert to a byte array
// Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333
byte [] daysArray = BitConverter.GetBytes(days.Days);
byte [] msecsArray = BitConverter.GetBytes(( long )(msecs.TotalMilliseconds/ 3.333333 ));
// Reverse the bytes to match SQL Servers ordering
Array.Reverse(daysArray);
Array.Reverse(msecsArray);
// Copy the bytes into the guid
Array.Copy(daysArray, daysArray.Length - 2 , guidArray, guidArray.Length - 6 , 2 );
Array.Copy(msecsArray, msecsArray.Length - 4 , guidArray, guidArray.Length - 4 , 4 );
return new System.Guid(guidArray);
}
/// <summary>
/// 从 SQL SERVER 返回的 GUID 中生成时间信息
/// </summary>
/// <param name="guid"> 包含时间信息的 COMB </param>
/// <returns> 时间 </returns>
public static DateTime GetDateFromComb(System.Guid guid)
{
DateTime baseDate = new DateTime( 1900 , 1 , 1 );
byte [] daysArray = new byte [ 4 ];
byte [] msecsArray = new byte [ 4 ];
byte [] guidArray = guid.ToByteArray();
// Copy the date parts of the guid to the respective byte arrays.
Array.Copy(guidArray, guidArray.Length - 6 , daysArray, 2 , 2 );
Array.Copy(guidArray, guidArray.Length - 4 , msecsArray, 0 , 4 );
// Reverse the arrays to put them into the appropriate order
Array.Reverse(daysArray);
Array.Reverse(msecsArray);
// Convert the bytes to ints
int days = BitConverter.ToInt32(daysArray, 0 );
int msecs = BitConverter.ToInt32(msecsArray, 0 );
DateTime date = baseDate.AddDays(days);
date = date.AddMilliseconds(msecs * 3.333333 );
return date;
}
1. 通过 show status和应用特点了解各种 SQL的执行频率
通过 SHOW STATUS 可以提供服务器状态信息,也可以使用 mysqladmin extende d-status 命令获得。 SHOW STATUS 可以根据需要显示 session 级别的统计结果和 global级别的统计结果。
如显示当前session: SHOW STATUS like "Com_%"; 全局级别:show global status;
以下几个参数对 Myisam 和 Innodb 存储引擎都计数:
1. Com_select 执行 select 操作的次数,一次查询只累加 1 ;
2. Com_insert 执行 insert 操作的次数,对于批量插入的 insert 操作,只累加一次 ;
3. Com_update 执行 update 操作的次数;
4. Com_delete 执行 delete 操作的次数;
以下几个参数是针对 Innodb 存储引擎计数的,累加的算法也略有不同:
1. Innodb_rows_read select 查询返回的行数;
2. Innodb_rows_inserted 执行 Insert 操作插入的行数;
3. Innodb_rows_updated 执行 update 操作更新的行数;
4. Innodb_rows_deleted 执行 delete 操作删除的行数;
通过以上几个参数,可以很容易的了解当前数据库的应用是以插入更新为主还 是以查询操作为主,以及各种类型的 SQL大致的执行比例是多少。对于更新操作的计 数,是对执行次数的计数,不论提交还是回滚都会累加。
对于事务型的应用,通过 Com_commit 和 Com_rollback 可以了解事务提交和回 滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。此外,以下几个参数便于我们了解数据库的基本情况:
1. Connections 试图连接 MySQL 服务器的次数
2. Uptime 服务器工作时间
3. Slow_queries 慢查询的次数
2. 定位执行效率较低的SQL语句
可以通过以下两种方式定位执行效率较低的 SQL 语句:
1. 可以通过慢查询日志定位那些执行效率较低的 sql 语句,用 --log-slow-queries[=file_name] 选项启动时, mysqld 写一个包含所有执行时间超过long_query_time 秒的 SQL 语句的日志文件。可以链接到管理维护中的相关章节。
2. 使用 show processlist查看当前MYSQL的线程, 命令慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查 询慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 MySQL 在进行的线程,包括线程的状态,是否锁表等等,可以实时的查看 SQL 执行情况, 同时对一些锁表操作进行优化。
3. 通过EXPLAIN 分析低效 SQL的执行计划:
1. mysql如何使用索引
索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT 操作性能的最佳途径。
查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列 索引,那么只有查询条件使用了多列关键字最左边的前缀时(前缀索引),才可以使用索引,否则 将不能使用索引。
下列情况下, Mysql 不会使用已有的索引:
1、如果 mysql 估计使用索引比全表扫描更慢,则不使用索引。例如:如果 key_part 1均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
2、如果使用 heap 表并且 where 条件中不用=索引列,其他 > 、 < 、 >= 、 <= 均不使 用索引(MyISAM和innodb表使用索引);
3、使用or分割的条件,如果or前的条件中的列有索引,后面的列中没有索引,那么涉及到的索引都不会使用。
4、如果创建复合索引,如果条件中使用的列不是索引列的第一部分;(不是前缀索引)
4、如果 like 是以%开始;
5、对 where 后边条件为字符串的一定要加引号,字符串如果为数字 mysql 会自动转 为字符串,但是不使用索引。
2. 查看索引使用情况
如果索引正在工作, Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使 用。Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描,
该值较高。通常说明表索引不正确或写入的查询没有利用索引。
mysql> show status like 'Handler_read%';
1. 查询进行优化,应尽量避免全表扫描
对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
. 尝试下面的技巧以避免优化器错选了表扫描:
· 使用ANALYZE TABLEtbl_name为扫描的表更新关键字分布。
· 对扫描的表使用FORCEINDEX告知MySQL,相对于使用给定的索引表扫描将非常耗时。
SELECT * FROM t1, t2 FORCE INDEX (index_for_column) WHERE t1.col_name=t2.col_name;
· 用--max-seeks-for-key=1000选项启动mysqld或使用SET max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。
1). 应尽量避免在 where 子句中对字段进行 null 值判断
否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
NULL对于大多数数据库都需要特殊处理,MySQL也不例外,它需要更多的代码,更多的检查和特殊的索引逻辑,有些开发人员完全没有意识到,创建表时NULL是默认值,但大多数时候应该使用NOT NULL,或者使用一个特殊的值,如0,-1作为默 认值。
不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列 就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
此例可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
2). 应尽量避免在 where 子句中使用!=或<>操作符
否则将引擎放弃使用索引而进行全表扫描。
MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。
可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如:
SELECT id FROM t WHERE col LIKE 'Mich%'; # 这个查询将使用索引,
SELECT id FROM t WHERE col LIKE '%ike'; #这个查询不会使用索引。
3). 应尽量避免在 where 子句中使用 or 来连接条件
否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以 使用UNION合并查询: select id from t where num=10 union all select id from t where num=20
在某些情况下,or条件可以避免全表扫描的。
1 .where 语句里面如果带有or条件, myisam表能用到索引, innodb不行。
2 .必须所有的or条件都必须是独立索引
4) .in 和 not in 也要慎用,否则会导致全表扫描,
如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
Select id from t where num between 1 and 3
5).下面的查询也将导致全表扫描:
select id from t where name like '%abc%' 或者
select id from t where name like '%abc' 或者
若要提高效率,可以考虑全文检索。
而select id from t where name like 'abc%' 才用到索引
7). 如果在 where 子句中使用参数,也会导致全表扫描。
因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推 迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
select id from t where num=@num
可以改为强制查询使用索引: select id from t with(index(索引名)) where num=@num
8). 应尽量避免在 where 子句中对字段进行表达式操作,
这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100
应改为: select id from t where num=100*2
9). 应尽量避免在where子句中对字段进行函数操作,
这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)='abc' --name
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’
生成的id 应改为:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10).不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,
否则系统将可能无法正确使用索引。
11). 索引字段不是复合索引的前缀索引
例如 在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
2 .其他一些注意优化:
12). 不要写一些没有意义的查询,
如需要生成一个空表结构:
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: create table #t(...)
13). 很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
14). 并不是所有索引对查询都有效,
SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
15). 索引并不是越多越好,
索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16).应尽可能的避免更新 clustered 索引数据列,
因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。
17).尽量使用数字型字段,
若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
18).尽可能的使用 varchar/nvarchar 代替 char/nchar ,
因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
19).最好不要使用"*"返回所有: select * from t ,
用具体的字段列表代替“*”,不要返回用不到的任何字段。
3. 临时表的问题:
20). 尽量使用表变量来代替临时表。
如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。
21).避免频繁创建和删除临时表,以减少系统表资源的消耗。
22).临时表并不是不可使用,
适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。
23).在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;
如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
24). 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
4. 游标的问题:
25).尽量避免使用游标,
因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。
26).使用基于游标的方法或临时表方法之前,
应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。
27).与临时表一样,游标并不是不可使用。
对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。
28).在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。
无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
5. 事务的问题:
29).尽量避免大事务操作,提高系统并发能力。
6. 数据量的问题
30).尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
7. COUNT优化:
31)
很多人为了统计记录条数,就使用
32)count(column)
这个误区甚至在很多的资深工程师或者是
count(column)
count(*)
1)innodb引擎在统计方面和myisam是不同的,Myisam内置了一个计数器,
Count(*)在没有查询条件的情况下使用 select count(*) from table 的时候,Myisam直接可以从计数器中取出数据。而innodb必须全表扫描一次方能得到总的数量
2. 但是当有查询条件的时候,两者的查询效率一致。
4. 主键索引count(*)的时候之所以慢
InnoDB引擎:
[1] 数据文件和索引文件存储在一个文件中,主键索引默认直接指向数据存储位置。
[2] 二级索引存储指定字段的索引,实际的指向位置是主键索引。当我们通过二级索引统计数据的时候,无需扫描数据文件;而通过主键索引统计数据时,由于主键索引与数据文件存放在一起,所以每次都会扫描数据文件,所以主键索引统计没有二级索引效率高。
[3] 由于主键索引直接指向实际数据,所以当我们通过主键id查询数据时要比通过二级索引查询数据要快。
l MyAsm引擎
[1] 该引擎把每个表都分为几部分存储,比如用户表,包含user.frm,user.MYD和user.MYI。
[2] User.frm负责存储表结构
[3] User.MYD负责存储实际的数据记录,所有的用户记录都存储在这个文件中
[4] User.MYI负责存储用户表的所有索引,这里也包括主键索引。
8. 优化order by语句
MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录,但由于MySQL在查询时最多只能使用一个索引。因此,如果WHERE条件已经占用了索引,那么在排序中就不使用索引了,这将大大降低查询的速度。我们可以看看如下的SQL语句:
SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
在以上的SQL的WHERE子句中已经使用了NAME字段上的索引,因此,在对SALE_DATE进行排序时将不再使用索引。为了解决这个问题,我们可以对SALES表建立复合索引:
ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME,SALE_DATE)
这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意,在使用这个方法时,要确保WHERE子句中没有排序字段,在上例中就是不能用SALE_DATE进行查询,否则虽然排序快了,但是SALE_DATE字段上没有单独的索引,因此查询又会慢下来。
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下情况不使用索引:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ; --order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ;-- 用于查询行的关键字与 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2 ;-- 对不同的关键字使用 ORDER BY :
9. 优化GROUP BY
默认情况下, MySQL 排序所有 GROUP BY col1 , col2 , .... 。查询的方法如同在查询中指定 ORDER BY col1 , col2 , ... 。如果显式包括一个包含相同的列的 ORDER BY子句, MySQL 可以毫不减速地对它进行优化,尽管仍然进行排序。如果查询包括 GROUP BY 但你想要避免排序结果的消耗,你可以指定 ORDER BY NULL禁止排序。
例如 :
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
10. 优化 OR
具体详解看:mysql or条件可以使用索引而避免全表
4. Explain解释说明
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法,在select语句前加上explain就可以了:
如:
table | type | possible_keys | key | key_len | ref | rows | Extra
EXPLAIN列的解释:
1 table:
显示这一行的数据是关于哪张表的2 type:
这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为:system、const、eg_reg、ref、ref_or_null、 range、indexhe、 ALL。system:表仅有一行(=系统表)。这是const联接类型的一个特例
const:(PRIMARY KEY或UNIQUE)
表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。
const表很快,因为它们只读取一次!
const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
在下面的查询中,tbl_name可以用于const表:
eq_reg:key
对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。
eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
在下面的例子中,MySQL可以使用eq_ref联接来处理ref_tables:
ref:key
对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,
或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。
如果使用的键仅仅匹配少量行,该联接类型是不错的。
ref可以用于使用=或<=>操作符的带索引的列。
在下面的例子中,MySQL可以使用ref联接来处理ref_tables:
r ef_or_null:Or Is null
该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
在下面的例子中,MySQL可以使用ref_or_null联接来处理ref_tables:
range:=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN
只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。
key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。
当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range:
indexhe:
该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。
ALL:
对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,
这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,
使得行能基于前面的表中的常数值或列值被检索出。
3 possible_keys :
显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
4 key :
实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引 。这种情况下,可以在SELECT语句中使用USEINDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
5key_len:
使用的索引的长度。在不损失精确性的情况下,长度越短越好
6 ref
显示索引的哪一列被使用了,如果可能的话,是一个常数
7 rows
MYSQL认为必须检查的用来返回请求数据的行数 (扫描行的数量)
8 Extra
该列包含MySQL解决查询的详细信息关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,
意思MYSQL根本不能使用索引,结果是检索会很慢
extra列返回的描述的意义
Distinct:
一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
Not exists :
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了
面是一个可以这样优化的查询类型的例子:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
假定t2.id定义为NOT NULL。在这种情况下,MySQL使用t1.id的值扫描t1并查找t2中的行。
如果MySQL在t2中发现一个匹配的行,它知道t2.id绝不会为NULL,并且不再扫描t2内有相同的id值的行。
换句话说,对于t1的每个行,MySQL只需要在t2中查找一次,无论t2内实际有多少匹配的行。
Range checked for each Record(index map:#)
没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。
这是使用索引的最慢的连接之一
MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
对前面的表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行。
关于适用性标准的描述参见7.2.5节,“范围优化”和7.2.6节,“索引合并优化”,
不同的是前面表的所有列值已知并且认为是常量。这并不很快,但比执行没有索引的联接要快得多。
Using filesort
看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。
它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,
这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary
看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,
并且连接类型ALL或index,这就会发生,或者是查询有问题
Impossible WHERE noticed after reading const table...
2).清空数据表
注意:删除表中的所有记录,应使用TRUNCATE TABLE语句。注意这里为什么要用TRUNCATE TABLE语句代替DELETE语句:当你使用TRUNCATE TABLE语句时,记录的删除是不作记录的。也就是说,这意味着TRUNCATE TABLE要比DELETE快得多。
3)用SELECT创建记录和表
INSERT语句与DELETE语句和UPDATE语句有一点不同,它一次只操作一个记录。然而,有一个方法可以使INSERT 语句一次添加多个记录。要作到这一点,你需要把INSERT语句与SELECT语句结合起来,象这样:
这个语句从anothertable拷贝记录到mytable.只有表anothertable中字段another_first的值为'Copy Me!'的记录才被拷贝。
当为一个表中的记录建立备份时,这种形式的INSERT语句是非常有用的。在删除一个表中的记录之前,你可以先用这种方法把它们拷贝到另一个表中。
如果你需要拷贝整个表,你可以使用SELECT INTO语句。例如,下面的语句创建了一个名为newtable的新表,该表包含表mytable的所有数据:
你也可以指定只有特定的字段被用来创建这个新表。要做到这一点,只需在字段列表中指定你想要拷贝的字段。另外,你可以使用WHERE子句来限制拷贝到新表中的记录。下面的例子只拷贝字段second_columnd的值等于'Copy Me!'的记录的first_column字段。
使用SQL修改已经建立的表是很困难的。例如,如果你向一个表中添加了一个字段,没有容易的办法来去除它。另外,如果你不小心把一个字段的数据类型给错了,你将没有办法改变它。但是,使用本节中讲述的SQL语句,你可以绕过这两个问题。
例如,假设你想从一个表中删除一个字段。使用SELECT INTO语句,你可以创建该表的一个拷贝,但不包含要删除的字段。这使你既删除了该字段,又保留了不想删除的数据。
如果你想改变一个字段的数据类型,你可以创建一个包含正确数据类型字段的新表。创建好该表后,你就可以结合使用UPDATE语句和SELECT语句,把原来表中的所有数据拷贝到新表中。通过这种方法,你既可以修改表的结构,又能保存原有的数据。