数据库性能优化(建议)总结

嗯,这里是简介,主题配置内可以修改,如留空则不显示

数据库性能优化(建议)总结

本分部分内容来自网络加上自己的总结而来。动态修改。
首先从整体heig level的角度来看:
磁盘io优化 属于数据库系统已经做好的东西,我们不需要做优化
应用优化
1 )使用连接池
对于访问数据库来说,建立连接的代价比较昂贵,因此,我们有必要建立 " 连接池 " 以提高访问的性能。我们可以把连接当作对象或者设备,池中又有许多已经建立的连接,访 问本来需要与数据库的连接的地方,都改为和池相连,池临时分配连接供访问使用,结果返 回后,访问将连接交还。
2)减少对mysql的访问,使用mem缓存等
3)负载均衡,复制分流查询操作
           利用mysql的主从复制,分流更新操作和查询操作
          1), 创建复制账号:Gran replication slave on *.* to 'rel'@'10.0.1.2' identified by '123456'
          2), 修改主服务器的配置my.conf 开启binlog和设置server-id
          3), 将主服务器的数据一致性恢复到从服务器,保证将要复制的数据时一只的,否则出问题
          4), 在从服务器上修改配置my.conf
               server-id=2
               master-host=10.0.1.3
               master-user='rel'
               master-password='123456'
               master-port='3306'
          5), 从服务器启动slave线程: start slave
          show processlist 查看。
4) 分布式cluster 数据库架构
对表进行分拆
1)水平划分
    如果某个表的数据太多,预期有上千条甚至上亿以上,我们可以化整为0:拆表。
    这里就涉及到拆表的算法:
    记录日志的表,也可以按周或者按月来拆。
    记录用户信息的表,按用户id的hash算法来拆。
2)垂直拆分
  如果表记录数并不多,可能也就2、3万条,但是字段却很长,表占用空间很大,检索表时需要执行大量I/O,严重降低了性能。这个时候需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。
其次,从小的sql语句一般的建议:
1.查询的模糊匹配
尽量避免在一个复杂查询里面使用 LIKE '%parm1%'—— 百分号会导致相关列的索引无法使用,最好不要用.
解决办法:
其实只需要对该脚本略做改进,查询速度便会提高近百倍。改进方法如下:
a、修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表,用户模糊输入供应商名称时,直接在前台就帮忙定位到具体的供应商,这样在调用后台程序时,这列就可以直接用等于来关联了。
b、直接修改后台——根据输入条件,先查出符合条件的供应商,并把相关记录保存在一个临时表里头,然后再用临时表去做复杂关联
2.索引问题
在做性能跟踪分析过程中,经常发现有不少后台程序的性能问题是因为缺少合适索引造成的,有些表甚至一个索引都没有。这种情况往往都是因为在设计表时,没去定义索引,而开发初期,由于表记录很少,索引创建与否,可能对性能没啥影响,开发人员因此也未多加重视。然一旦程序发布到生产环境,随着时间的推移,表记录越来越多
这时缺少索引,对性能的影响便会越来越大了。
这个问题需要数据库设计人员和开发人员共同关注
法则:不要在建立的索引的数据列上进行下列操作:
◆避免对索引字段进行计算操作
◆避免在索引字段上使用not,<>,!=
◆避免在索引列上使用IS NULL和IS NOT NULL
◆避免在索引列上出现数据类型转换
◆避免在索引字段上使用函数
◆避免建立索引的列中使用空值。
3.复杂操作
部分UPDATE、SELECT 语句 写得很复杂(经常嵌套多级子查询)——可以考虑适当拆成几步,先生成一些临时数据表,再进行关联操作
4.update
同一个表的修改在一个过程里出现好几十次,如:
update table1
set col1=...
where col2=...;
update table1
set col1=...
where col2=...
......
象这类脚本其实可以很简单就整合在一个UPDATE语句来完成(前些时候在协助xxx项目做性能问题分析时就发现存在这种情况)
5.在可以使用UNION ALL的语句里,使用了UNION
UNION 因为会将各查询子集的记录做比较,故比起UNION ALL ,通常速度都会慢上许多。一般来说,如果使用UNION ALL能满足要求的话,务必使用UNION ALL。还有一种情况大家可能会忽略掉,就是虽然要求几个子集的并集需要过滤掉重复记录,但由于脚本的特殊性,不可能存在重复记录,这时便应该使用UNION ALL,如xx模块的某个查询程序就曾经存在这种情况,见,由于语句的特殊性,在这个脚本中几个子集的记录绝对不可能重复,故可以改用UNION ALL)
6.在WHERE 语句中,尽量避免对索引字段进行计算操作
这个常识相信绝大部分开发人员都应该知道,但仍有不少人这么使用,我想其中一个最主要的原因可能是为了编写写简单而损害了性能,那就不可取了
9月份在对XX系统做性能分析时发现,有大量的后台程序存在类似用法,如:
......
where trunc(create_date)=trunc(:date1)
虽然已对create_date 字段建了索引,但由于加了TRUNC,使得索引无法用上。此处正确的写法应该是
where create_date>=trunc(:date1) and create_date
或者是
where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60)
注意:因between 的范围是个闭区间(greater than or equal to low value and less than or equal to high value.),
故严格意义上应该再减去一个趋于0的小数,这里暂且设置成减去1秒(1/(24*60*60)),如果不要求这么精确的话,可以略掉这步。
7.对Where 语句的法则
7.1 避免在WHERE子句中使用in,not  in,or 或者having。
可以使用 exist 和not exist代替 in和not in。
可以使用表链接代替 exist。Having可以用where代替,如果无法代替可以分两步处理。
例子
SELECT *  FROM ORDERS WHERE CUSTOMER_NAME NOT IN
(SELECT CUSTOMER_NAME FROM CUSTOMER)
优化
SELECT *  FROM ORDERS WHERE CUSTOMER_NAME not exist
(SELECT CUSTOMER_NAME FROM CUSTOMER)
7.2 不要以字符格式声明数字,要以数字格式声明字符值。(日期同样)否则会使索引无效,产生全表扫描。 【此处需要验证】
例子使用:
SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;
不要使用:SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369’
8.对Select语句的法则
在应用程序、包和过程中限制使用select * from table这种方式。看下面例子
使用SELECT empno,ename,category FROM emp WHERE empno = '7369‘
而不要使用SELECT * FROM emp WHERE empno = '7369'
9.排序
避免使用耗费资源的操作,带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行,耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序
10.临时表
慎重使用临时表可以极大的提高系统性能。
尽量使用表变量来代替临时表。
避免频繁创建和删除临时表,以减少系统表资源的消耗。
在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;
如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
11.查询进行优化,应尽量避免全表扫描
  对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
12.索引
SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
较频繁的作为查询条件字段应该创建索引
唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件(如性别)
更新非常频繁的字段不适合创建索引
不会出现在WHERE子句中字段不该创建索引
(index,unique,primary三种索引较常见,还有外键索引,frogine key,复合索引)
13.游标
尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。
使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。
14.事务
尽量避免大事务操作,提高系统并发能力。
15.数据量
尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
16.锁机制(选择)
跟性能相关的最重要的区别就是 MyISAM 和 InnoDB 实现的锁机制不一样! MyISAM 使用的是表锁, 而 InnoDB实现的是行锁。 1) MyISAM为表级锁 由于MyISAM写进程优先获得锁,使得读锁请求靠后等待队列。不仅如此,即使读请求先到锁等待队列,写请求后 到,写锁也会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求要重要。 如果在大量更新操作的情况下,使得很难获得读锁。从而造成阻塞。 所以MyIsam不适合做大量更新操作的原因 2 )INNODB的行锁是基于索引实现,如果不通过索引访问数据,Innodb会使用表锁 表级锁更适合以查询为主,只有少量按索引条件更新数据的应用。 行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又并发查询。因为只锁定要操作的行, 所以可以多个线程同时操作不同的行(只要不操作其他线程已经锁定的行)。
17.永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT标志。就算是你users表有一个主键叫“email”的字段,你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区……在这里,只有一个情况是例外,那就是“关联表”的“外键”,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如:有一个“学生表”有学生的ID,有一个“课程表”有课程ID,那么,“成绩表”就是“关联表”了,其关联了学生表和课程表,在成绩表中,学生ID和课程ID叫“外键”其共同组成主键。
18.垂直分割
  “垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(以前,在银行做过项目,见过一张表有100多个字段,很恐怖)
       示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢?这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。
       示例二:你有一个叫“last_login”的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所以,你可以把这个字段放到另一个表中,这样就不会影响你对用户ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。hp程序员之家
另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。
19.选择正确的存储引擎
 在MySQL中有两个存储引擎MyISAM和InnoDB,每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情MyISAM适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM对于 SELECT COUNT(*) 这类的计算是超快无比的。 www~phperz~com
InnoDB的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM还慢。他是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。
下面是MySQL的手册
target=”_blank”MyISAM Storage Engine
InnoDB Storage Engine
最后,对于一个一般已经运行很久的系统的优化建议:
优化步骤
1.定位执行效率较低的SQL语句
可以通过以下两种方式定位执行效率较低的 SQL 语句:
     1. 可以通过慢查询日志定位那些执行效率较低的 sql 语句,用 --log-slow-queries[=file_name] 选项启动时, mysqld 写一个包含所有执行时间超过long_query_time 秒的 SQL 语句的日志文件。可以链接到管理维护中的相关章节。
     2. 使用 show processlist查看当前MYSQL的线程, 命令慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查 询慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 MySQL 在进行的线程,包括线程的状态,是否锁表等等,可以实时的查看 SQL 执行情况, 同时对一些锁表操作进行优化。
2.通过EXPLAIN 分析低效 SQL的执行计划:
通过以上步骤查询到效率低的 SQL 后,我们可以通过 explain 或者 desc 获取MySQL 如何执行 SELECT 语句的信息,包括 select 语句执行过程表如何连接和连接 的次序。
3.通过 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 慢查询的次数

发表评论

必填

选填

选填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

Hello world. 豫ICP备16008819号-1.

Power by Z-BlogPHP  Theme by wzdaxue