MYSQL语句和表的优化.docx
- 文档编号:22986512
- 上传时间:2023-04-29
- 格式:DOCX
- 页数:15
- 大小:23.85KB
MYSQL语句和表的优化.docx
《MYSQL语句和表的优化.docx》由会员分享,可在线阅读,更多相关《MYSQL语句和表的优化.docx(15页珍藏版)》请在冰豆网上搜索。
MYSQL语句和表的优化
MYSQL语句和表的优化
--优化SQL的一般步骤
--一、通过showstatus和应用特点了解各种SQL的执行频率
/*
通过SHOWSTATUS可以提供服务器状态信息,也可以使用mysqladminextended-status命令获得。
SHOWSTATUS可以根据需要显示session级别的统计结果和global级别的统计结果。
以下几个参数对Myisam和Innodb存储引擎都计数:
1.Com_select执行select操作的次数,一次查询只累加1;
2.Com_insert执行insert操作的次数,对于批量插入的insert操作,只累加一次;
3.Com_update执行update操作的次数;
4.Com_delete 执行delete操作的次数;
*/
SHOWSTATUSWHEREVariable_name='Com_select';
/*
以下几个参数是针对Innodb存储引擎计数的,累加的算法也略有不同:
1.Innodb_rows_readselect查询返回的行数;
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慢查询的次数
二、定位执行效率较低的SQL语句
可以通过以下两种方式定位执行效率较低的SQL语句:
1.可以通过慢查询日志定位那些执行效率较低的sql语句,用--log-slow-queries[=file_name]选项启动时,
mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
可以链接到管理维护中的相关章节。
2.慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,
可以使用showprocesslist命令查看当前MySQL在进行的线程,包括线程的状态,是否锁表等等,可以实时的查看SQL执行情况,同时对一些锁表操作进行优化。
*/
SHOWPROCESSLIST;
/*
三、通过EXPLAIN分析低效SQL的执行计划
通过以上步骤查询到效率低的SQL后,我们可以通过explain或者desc获取MySQL如何执行SELECT语句的信息,包括select语句执行过程表如何连接和连接的次序。
explain可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
*/
EXPLAINSELECT*FROMmessageaLEFTJOINmytablebONa.id=b.idWHEREa.id=1;
/*
返回结果
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|1|SIMPLE|a|const|PRIMARY|PRIMARY|4|const|1||
|1|SIMPLE|b|ALL|NULL|NULL|NULL||9999||
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
select_type:
select类型
table:
输出结果集的表
type:
表示表的连接类型
①当表中仅有一行是type的值为system是最佳的连接类型;
②当select操作中使用索引进行表连接时type的值为ref;
③当select的表连接没有使用索引时,经常会看到type的值为ALL,表示对该表进行了全表扫描,这时需要考虑通过创建索引来提高表连接的效率。
possible_keys:
表示查询时,可以使用的索引列.
key:
表示使用的索引
key_len:
索引长度
rows:
扫描范围
Extra:
执行情况的说明和描述
四、确定问题,并采取相应的优化措施
经过以上步骤,基本可以确认问题出现的原因,可以根据情况采取相应的措施,进行优化提高执行的效率。
例如上面的例子,我们确认是对b表的全表扫描导致效率的不理想,我们对b表的id字段创建了索引,查询需要扫描的行数明显较少。
返回结果
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|1|SIMPLE|a|const|PRIMARY|PRIMARY|4|const|1||
|1|SIMPLE|b|const|PRIMARY|PRIMARY|4|const|1||
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
大批量插入数据时优化SQL语句==============================================
一、对于Myisam类型的表,可以通过以下步骤快速的导入大量的数据。
ALTERTABLEtablenameDISABLEKEYS;
批量插入数据
ALTERTABLEtablenameENABLEKEYS;
前后两个命令用来打开或者关闭Myisam表非唯一索引的更新。
在导入大量的数据到一个非空的Myisam表时,通过设置这两个命令,可以提高导入的效率。
对于导入大量数据到一个空的Myisam表,默认就是先导入数据然后才创建索引的,所以不用进行设置。
*/
ALTERTABLEmytableDISABLEKEYS;
INSERTINTOmytable(id,username,city,age)VALUES(1,'name1','city1',10),(2,'name2','city2',20),(3,'name3','city3',30);
ALTERTABLEmytableENABLEKEYS;
/*
二、而对于Innodb类型的表,这种方式并不能提高导入数据的效率。
对于Innodb类型的表,我们有以下几种方式可以提高导入的效率:
①因为Innodb类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。
如果Innodb表没有主键,那么系统会默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这个优势提高导入数据的效率。
②在导入数据前执行SETUNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SETUNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。
③如果应用使用自动提交的方式,建议在导入前执行SETAUTOCOMMIT=0,关闭自动提交,导入结束后再执行SETAUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。
*/
SETUNIQUE_CHECKS=0;
SETUNIQUE_CHECKS=1;
SETAUTOCOMMIT=0;
SETAUTOCOMMIT=1;
/*
优化insert语句==============================================
1、如果同时插入很多行,请使用多个值的INSERT语句。
这比使用分开INSERT语句快(在一些情况中几倍)。
Insertintotestvalues(1,2),(1,3),(1,4)…
2、如果从不同客户插入很多行,能通过使用INSERTDELAYED语句得到更高的速度。
Delayed的含义是让insert语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘;这比每条语句分别插入要快的多;
LOW_PRIORITY刚好相反,在所有其他用户对表的读写完后才进行插入;
3、将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);
4、如果批量插入,可以增加bulk_insert_buffer_size变量值的方法来提高速度,但是,这只能对myisam表使用;
5、当从一个文本文件装载一个表时,使用LOADDATAINFILE。
这通常比使用很多INSERT语句快20倍;
6、根据应用情况使用replace语句代替insert;
7、根据应用情况使用ignore关键字忽略重复记录。
*/
INSERTDELAYEDINTOmytable(id,username,city,age)VALUES(4,'name4','city4',40);
INSERTLOW_PRIORITYINTOmytable(id,username,city,age)VALUES(5,'name5','city5',50);
REPLACEINTOmytable(id,username,city,age)VALUES(5,'name5','city5',50);
INSERTIGNOREINTOmytable(id,username,city,age)VALUES(5,'name5','city5',50);
/*
优化groupby语句==============================================
默认情况下,MySQL排序所有GROUPBYcol1,col2,....。
查询的方法如同在查询中指定ORDERBYcol1,col2,...。
如果显式包括一个包含相同的列的ORDERBY子句,MySQL可以毫不减速地对它进行优化,尽管仍然进行排序。
如果查询包括GROUPBY但你想要避免排序结果的消耗,你可以指定ORDERBYNULL禁止排序。
例如:
*/
SELECT*FROMmytableGROUPBYusernameORDERBYNULL;
/*
优化orderby语句==============================================
在某些情况中,MySQL可以使用一个索引在ORDERBY子句中,而不需要额外的排序。
where条件和orderby使用相同的索引,并且orderby的顺序和索引顺序相同,并且orderby的字段都是升序或者都是降序。
例如:
下列sql可以使用索引。
SELECT*FROMt1ORDERBYkey_part1,key_part2,...;--同一组合索引的某一部分
SELECT*FROMt1WHEREkey_part1=1ORDERBYkey_part1DESC,key_part2DESC;
SELECT*FROMt1ORDERBYkey_part1DESC,key_part2DESC;
但是以下情况不使用索引:
SELECT*FROMt1ORDERBYkey_part1DESC,key_part2ASC;
--orderby的字段混合ASC和DESC
SELECT*FROMt1WHEREkey2=constantORDERBYkey1;
--用于查询行的关键字与ORDERBY中所使用的不相同
SELECT*FROMt1ORDERBYkey1,key2;
--对不同的索引使用ORDERBY:
优化join语句==============================================
Mysql4.1开始支持SQL的子查询。
这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。
但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。
假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:
SELECT*FROMcustomerinfoWHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)
如果使用连接(JOIN)..来完成这个查询工作,速度将会快很多。
尤其是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询如下:
SELECT*FROMcustomerinfo
LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID
WHEREsalesinfo.CustomerIDISNULL
连接(JOIN)..之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
insert、update、delete的使用顺序==============================================
MySQL还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协作,这样单个客户端就不会由于锁定而等待很长时间。
改变优先级还可以确保特定类型的查询被处理得更快。
我们首先应该确定应用的类型,判断应用是以查询为主还是以更新为主的,是确保查询效率还是确保更新的效率,决定是查询优先还是更新优先。
下面我们提到的改变调度策略的方法主要是针对Myisam存储引擎的,对于Innodb存储引擎,语句的执行是由获得行锁的顺序决定的。
MySQL的默认的调度策略可用总结如下:
1.写入操作优先于读取操作。
2.对某张数据表的写入操作某一时刻只能发生一次,写入请求按照它们到达的次序来处理。
3.对某张数据表的多个读取操作可以同时地进行。
MySQL提供了几个语句调节符,允许你修改它的调度策略:
1.LOW_PRIORITY关键字应用于DELETE、INSERT、LOADDATA、REPLACE和UPDATE。
2.HIGH_PRIORITY关键字应用于SELECT和INSERT语句。
3.DELAYED关键字应用于INSERT和REPLACE语句。
如果写入操作是一个LOW_PRIORITY(低优先级)请求,那么系统就不会认为它的优先级高于读取操作。
在这种情况下,如果写入者在等待的时候,第二个读取者到达了,那么就允许第二个读取者插到写入者之前。
只有在没有其它的读取者的时候,才允许写入者开始操作。
这种调度修改可能存在LOW_PRIORITY写入操作永远被阻塞的情况。
SELECT查询的HIGH_PRIORITY(高优先级)关键字也类似。
它允许SELECT插入正在等待的写入操作之前,即使在正常情况下写入操作的优先级更高。
另外一种影响是,高优先级的SELECT在正常的SELECT语句之前执行,因为这些语句会被写入操作阻塞。
如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理,那么请使用--low-priority-updates选项来启动服务器。
通过使用INSERTHIGH_PRIORITY来把INSERT语句提高到正常的写入优先级,可以消除该选项对单个INSERT语句的影响。
*/
INSERTLOW_PRIORITYINTOmytable(id,username,city,age)VALUES(7,'name7','city7',70);
/*
优化数据表==============================================
一、优化表的数据类型
表需要使用何种数据类型,是需要根据应用来判断的。
虽然应用设计的时候需要考虑字段的长度留有一定的冗余,但是不推荐让很多字段都留有大量的冗余,这样即浪费存储也浪费内存。
我们可以使用PROCEDUREANALYSE()对当前已有应用的表类型的判断,该函数可以对数据表中的列的数据类型提出优化建议,可以根据应用的实际情况酌情考虑是否实施优化。
语法:
SELECT*FROMtbl_namePROCEDUREANALYSE();
SELECT*FROMtbl_namePROCEDUREANALYSE(16,256);
输出的每一列信息都会对数据表中的列的数据类型提出优化建议。
第二个例子告诉PROCEDUREANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。
如果没有这样的限制,输出信息可能很长;ENUM定义通常很难阅读。
在对字段类型进行优化时,可以根据统计信息并结合应用的实际情况对其进行优化。
二、通过拆分,提高表的访问效率
这里我们所说的拆分,主要是针对Myisam类型的表,拆分的方法可以分成两种情况:
1、纵向拆分:
纵向拆分是只按照应用访问的频度,将表中经常访问的字段和不经常访问的字段拆分成两个表,经常访问的字段尽量是定长的,这样可以有效的提高表的查询和更新的效率。
2、横向拆分:
横向拆分是指按照应用的情况,有目的的将数据横向拆分成几个表或者通过分区分到多个分区中,这样可以有效的避免Myisam表的读取和更新导致的锁问题。
三、逆规范化
数据库的规范化设计强调数据的独立性,数据应该尽可能少地冗余,因为存在过多的冗余数据,这就意味着要占用了更多的物理空间,同时也对数据的维护和一致性检查带来了问题。
对于查询操作很多的应用,一次查询可能需要访问多表进行,如果通过冗余相同数据纪录在一个表中,更新的代价增加不多,
但是查询操作效率可以有明显提高,这种情况就可以考虑通过冗余数据来提高效率。
四、使用冗余统计表
使用createtemporarytable语法,它是基于session的表,表的数据保存在内存里面,当session断掉后,表自然消除。
对于大表的统计分析,如果统计的数据量不大,利用insert。
。
。
select将数据移到临时表中比直接在大表上做统计要效率更高。
五、选择更合适的表类型
1、如果应用出现比较严重的锁冲突,请考虑是否更改存储引擎到innodb,行锁机制可以有效的减少锁冲突的出现。
2、如果应用查询操作很多,且对事务完整性要求不严格,则可以考虑使用Myisam存储引擎。
*/
SELECT*FROMmytablePROCEDUREANALYSE(16,256);
/*
其他优化措施
使用连接池==============================================
对于访问数据库来说,建立连接的代价比较昂贵,因此,我们有必要建立"连接池"以提高访问的性能。
我们可以把连接当作对象或者设备,池中又有许多已经建立的连接,访问本来需要与数据库的连接的地方,都改为和池相连,池临时分配连接供访问使用,结果返回后,访问将连接交还。
减少对Mysql的访问==============================================
一、避免对同一数据做重复检索:
应用中需要理清楚对数据库的访问逻辑,需要对相同表的访问,尽量集中在相同sql访问,一次提取结果,减少对数据库的重复访问。
二、使用mysqlquerycache:
作用:
查询缓存存储SELECT查询的文本以及发送给客户端的相应结果。
如果随后收到一个相同的查询,服务器从查询缓存中重新得到查询结果,而不再需要解析和执行查询。
适用范围:
不发生数据更新的表。
当表更改(包括表结构和表数据)后,查询缓存值的相关条目被清空。
查询缓存的主要参数设置:
*/
SHOWVARIABLESLIKE'%query_cache%';--也可以写成SHOWVARIABLESWHEREVariable_nameLIKE'%query_cache%';
/*
have_query_cache表明服务器在安装使已经配置了高速缓存
query_cache_size表明缓存区大小,单位为字节(1024字节为1KB)
query_cache_type值从0到2,含义分别为
0或者off(缓存关闭)
1或者on(缓存打开,使用sql_no_cache的select除外)
2或者demand(只有带sql_cache的select语句提供高速缓存)
*/
SETGLOBALquery_cache_size=1024*50;
--设置查询缓存大小,单位字节,1024字节为1KB,query_cache_size大小的设置必须大于40KB
--在SHOWSTATUS中,你可以监视查询缓存的性能
SHOWSTATUSLIKE'%Qcache%';
/*
Qcache_queries_in_cache在缓存中已注册的查询数目
Qcache_inserts被加入到缓存中的查询数目
Qcache_hits缓存采样数数目
Qca
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MYSQL 语句 优化
![提示](https://static.bdocx.com/images/bang_tan.gif)