数据库访问优化那些事

数据库访问优化那些事

1.数据库建模是业务需求具体化的过程，对数据规范化以满足3NF，但在性能要求特高的情况可适当用些反3NF的设计增加冗余。

2.字段必须具有原子性，最好不要一个字段值做多个字段用

3.字段应为非空。select返回列表中只返回需要的字段

4.将一组Boolean型属性值合成一个单独的状态属性，类似用位来表达属性值

5.不要将所有信息都放到一个表中，有时需分而治之

6.当查询返回记录数超过表中数据总量的10%就不要用索引，查询返回几万条记录，使用索引没有意义

7.给频繁更新的字段加索引会增加索引维护的成本

8.不要在数据库查询程序中返回大量无用数据，然后却在程序中将这些无用数据抛弃

9.一个通用查询SQL如果隐藏着三到四类查询，为性能着想可以分拆建立多个不同的“量身定做”的查询

10.程序循环中不应执行查询操作

11.负载增加，查询变慢时应先优化程序，而不是升级硬件。DBA家常便饭式的叫程序员优化某SQL，那肯定是SQL的问题而多半不是加机器的问题

12.update,delete,insert比select要花更长时间维护索引

13.应尽量减少程序和数据库之间的交互次数，减少网络开销

14.把不可靠的程序代码（如http操作)和不重要的SQL工作放在事务代码段的主程序之外执行或者使用JMS之类的异步操作

15.事务型活动密集时，无需加锁的操作一定不要加锁

16.并发用户越多，提交事务的间隔就应该越短

17.调整并发进程数到数据库服务性能达到最佳的数字

18.关联子查询在计算每一条返回记录时都被调用一次，当数据量很大时这是一个重要的问题，所以尽量减少关联子查询对外层查询元素的依赖性

19.单边范围条件(Half-bounded condition)不能充分利用索引和分区，所以应避免类似">"而是使用">="这种条件

20.在所有更新操作中(insert,update,delete)，删除最有可能成麻烦，建议改成切表或者逻辑删除的方式

21.通过分区提高性能

22.行转成列与列转成行的表设计都是不好的设计，这样的设计对数据的统计查询不利

23.基于范围做聚合操作时(如...case when num < 100 ... as sortkey group by ... order dy sortkey)，需要建立人造排序键(如num < 10 as sortkey)以便按需要的顺序显示结果

24. selet * from (select 1 as sortkey ... from A1 where xxx = ? union select 2 as sortkey ... fromA2 where xxx = ? order by 1) actual_table limit 1　这条SQL的"order by"（隐藏排序键）可解决优先从A1表中拿数据的查询问题

25.解决查询"在表A中同时了解oracle和java的员工"的问题,可用case when,select... from A1 where yyy = 'oracle' and ... in (select .... from A1 where yyy = 'java')或使用交集操作符(intersect)或使用枢轴表pivot table或使用基于位的聚合函数

26.最佳匹配查询可用"order by "来做，解决如topN的排行榜问题

27.可使用优化器指令如: force index(..), ,但不推荐开发人员使用这些指令，除非DBA推荐，否则DBA动了数据库参数那就惨了

28.对程序中反复执行的SQL的优化工作很重要，另一方面根据"二八原则“，多数负载高的问题又来自少数SQL语句

29.DBA要随时监控性能指标及找到数据库速度缓慢的原因，DBA要先定好数据库性能KPI。

30.并非只有简单SQL，不含join的SQL才是高效的SQL,有时候恰恰相反。关键要清楚数据引擎，优化器的工作原理，数据库访问是程序中对性能最关键的因素，必须在总体设计中考虑性能问题（包括架构，开发，数据库设计整个过程）

31.把尽可能多的行为放到一个SQL中，这是改善SQL性能的先决条件

32.合理运用执行计划

33.影响查询性能的真正重要因素包括：1表的数据量,2索引，分区，3查询条件的质量,4返回结果集的大小。优化器在以下情况无法高效工作：1通过很多语句，分别读取数据片段，2随便地使用SQL方言提供非关系特性

34.参考io各层性能汇总数据（仅供参考)

＝＝＝＝高效Mysql之SQL语句最优化===========

35. 用SHOW FULL PROCESSLIST寻找运行缓慢的SQL语句

36. 生成查询执行计划（QEP),对于delete,update语句，可以用查询语句代替查看性能(将update,delete改为select,返回update语句中的set列)

37. 查看表信息SHOW CREATE TABLE 表名\G SHOW TABLE STATUS LIKE '表名'\G; 优化SQL语句绝不仅是添加索引。

38. 基本分析命令：EXPLAIN, EXPLAN PARTITIONS, EXPLAIN EXTENDED, SHOW CREATE TABLE, SHOW INDEXES, SHOW TABLE STATUS, SHOW WARNINGS, SHOW [GLOBAL|SESSION] VARIABLES, 用户也可以从INFORMATION-SCHEMA.GLOBAL_VARIABLES表和INFORMATION_SCHEMA.SESSION_VARIABLES表中获取同样的VARIABLES信息。PERFORMANCE_SCHEMA可以对有问题的SQL进行分析

39.索引在表连接列，排序列，聚合操作上表现良好。分区对索引的使用和优化也有帮助，索引可建在where, order by, group by列上

40. 在可为空的列上定义唯一索引也行，这种情况下NULL值被认为是一个未知的值，并且NULL!=NULL,这是三态逻辑的好外，它避免了使用默认值或者一个空字符串值

41. 可以建一个能同时满足WHERE语句和ORDER BY语句的覆盖索引

42. 索引技术优化中最简单的是删除重复的索引，有个开源工具：Maatkit mk-duplicate-key-checker. 2008年google发布了SHOW INDEX_STATISTICS命令来更精确的方式获取索引使用情况。一些搜索引擎可以以不同方式来实现执行锁定的快速索引的创建：Tokutek就是其中一个。

43. DDL(如ALTER语句）除了会阻塞其它语句外，还会增加额外的磁盘空间，但是有些工具可减少影响，如oak-online-alter-table工具，还有facebook发布的在线变更工具（OSC),以类似的方式运行ALTER操作

44. 索引可用来限制需查询的行数，但如果Mysql需要获取大量行中更多列的数据，那么创建具有更小行宽度的小型索引则会更加高效。如alter table xxx add index(name(20)).

45. 使用SHOW GLOBAL STATUS或SHOW ENGINE INNODB STATUS命令监控Innodb缓冲池的使用情况

46. 优化mysql配置选项：1全局内存缓冲区：key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size, query_cache_size. 2全局/会话内存缓冲区：max_heap_table_size,tmp_table_size 3.会话缓冲区：join_buffer_size, sort_buffer_size, read_buffer_size, read_rnd_buffer_size 4.一些基础工具的变量：slow_query_log, slow_query_log_file, long_query_time, general_log, log_output, profiling. 5其它优化变量: optimizer_switch, default_storage_engine, max_allowed_packet, sql_mode, innodb_strict_mode 6更多变量:concurent_insert, foreign_key_checks, log_bin, max_join_size, max_seeks_for_key, min_examined_row_count, open_files_limit, optimizer_prune_level, optimizer_search_depth, sql_buffer_result, sql_select_limit, sync_binlog, thread_cache_size, thread_stack, tmpdir, tx_isolation, unique_checks.

47. 可用select sleep(0.5)来判断long_query_time是否生效.log_bin启动二进制日志可记录所有非select语句的分析.

48.开源SQL审计工具：sqlstats, mysql-proxy, 还可用tcp/ip命令: $sudo tcpdump -l -i eth0 -w -src or dst port 3306 -c 1000|stings.可用这些工具截取sql找到频繁执行和运行缓慢的所有SQL以优化。可使用mysqldumpslow, mysqlbinlog定位sql工具，还有maatkit的mk-query-digest工具.

49.SQL运行的快慢受当时执行时的系统负载，查询并发程度，网络开销，Mysql查询缓慢以及能否在内存内部访问必要的表索引和数据。

50.有个补丁可以打破mysql默认10秒的查询时间统计精度，http://EffectiveMysql.com/article/microsecond-mysql-client

51.SQL优化步骤：截取SQL查询－>找出有问题的所有SQL->确认SQL当前操作->分析SQL语句和辅助信息->优化SQL->验证SQL优化后的结果

52.SQL性能优化的策略：减少或合并多个DDL语句为一个；去除重复，不用，无效的索引；索引列的改进：为节省存储空间，在业务允许情况下将字段的数据类型和长度定义得越小越好, 因为较小的类型通常较快，它们使用较小的磁盘空间、内存和cpu cache，也只需要较小的cpu周期来处理。可将bigint改成无符号的int(int unsigned); datetime改为timestamp；使用enum存静态代码值，定义所有列为非空；SQL语句中数据连接的字段类型要一致，以免数据库执行SQL时隐含转换字段类型进行比较；IPV4地址定义为int unsigned(int_aton()和net_ntoa()转换)而不是用varchar(15);IPV6地址定义为binary(16);MD5使用UNHEX()和HEX()函数存在binary(16)类型中更高效，而不是varchar(32);删除内容重复的SQL语句，删除重复执行的SQL语句（N+1问题），删除不必要的SQL语句，缓存SQL语句的返回结果；将子查询改成表连接；慎用视图；使用Mysql的slaves机制扩展节点复制功能。

53.分层树型数据结构设计方案有：邻接表（parent_id),枚举路径（用'1/2/3'代替parent_id),嵌套表（nsleft, comment_id, nsright),嵌套表这种设计比较高效但对其它需求的要求一般。闭包表是最通用的设计，它要求一个额外的表treepaths(表字段ancestor int, descendant int)，但它浪费存储空间，使用空间换时间的方案减少操作过程中由冗余的计算所造成的消耗。

54. 不是每张表都需要一个伪主键id,它可以是别的字段名字或其它唯一键列作为主键列（前提是其它可作主键列的类型是int型，可作高效索引），主键列也可是多个int型的组合键.

55. 支持使用外键，它使用方便，提高性能还能帮助你在任何简单或复杂形式的数据变更下始终维持引用完整性.

56. 如何识别SQL反模式：1是否支持数据完整性（如外键约束）2是否支持业务扩展及所有业务场景（包括CRUD操作）3是否支持比较复杂的统计查询业务 4是否支持数据库优化工作（如索引，分区，切表，分片，主从复制等）5是否支持特别是高并发下的高效性 6.是否可利用到数据库3NF的好处 7设计但不要过渡设计，特别不要轻易提前开发N年后也不会用的功能。8在表数据量不大或性能要求很高或某些特殊的业务要求下可适当地特意应用一下SQL反模式,适当情况下利用数据冗余存储来达到减少表连接查询操作

57.需要支持可变属性时，很多人第一反应是将属性当成列来存储，从而形成实体－属性－值的设计（简称EAV),多数情况下这种设计不合理，EAV可改为：1单表继承，将所有可能的属性都固定成一个个的列，然后加个varchar2类型区别一组属性,不用的属性置空处理 2实体表继承，这种方式父子表有重复列存在 3类表继承：这种方式父子表无重复列，不需要了解在基类表中的行表示的是哪个子类型，可以写一个视图将父类和所有子类的表的列都查到一个行上 4如果需经常增加新的属性支持，可用一个BLOB列来存储数据，用XML或JSON格式表示值的内容，这种方式没办法选择BLOB列中某个独立的属性，无法进行聚合排序等运算。

58.表M中一个列（如issue_type)的外键有时关键A表（issue1），有时关键B表（issue2),因此无法对issue_type使用外键约束，此时出现了多态关联问题，可以创建共用的超级表：多加1张A和B表的祖先表X,然后让M添加一个指向X表的外键约束（issue_id),这时X表可以只有一列（如issue_id)，X中该列和M,A,B都关联，而M表和A,B表没有外键关联，只和X表有外键关联。但在程序查询统计的时候可以分别将X,A或B表与M表作连接查询。

59.解决表名或列名为"bug_2008,bug_2009,..."的按年分割的元数据分裂问题，可以使用水平分区（按行拆分表）或垂直分区（按列拆分表）的方案解决。

60.使用NUMERIC或DECIMAL类型代替FLOAT来存储固定精度的数据；将varchar型数据类型的列都想办法转成能以char型固定长度存储

61.ENUM适合存储没有业务逻辑且不需要改变的候选值（如ON/OFF),而check约束可以在更多场景下使用。对状态值维护问题，最好再增加一张维护状态值的检查表与之关联。

62.将图片存到数据库之外（数据库只存路径字符）的方案不能享用数据库带来的好处（如事务，备份等），最好的方案是使用BLOB类型存储，MYSQL有MEDIUMBLOB(最大16M)，配合使用LOAD_FILE()读文件，使用DUMPFILE转成文件。

63.MENTOR你的索引：测量(Measure),解释（Explain),挑选未使用索引的查询（Nominate),测试（Test),优化（Optimizer),重建（rebuild)

64.不必使用SQL解决所有的问题：如可借用第3方工具实现搜索，随机显示等数据库不特长的功能

65.有时候我们需要使用多个SQL解决一个复杂查询任务，而不是一味追求高效的一条SQL搞定

66.密码应使用“明文密码+随机码”进行哈希然后入库保存，密码重置可发临时链接邮件，加密方式还可参考PBKDF2和BCRYPT

67.注意SQL注入的安全问题

68.每一列只能有一个数据类型，每列的意义必须在所有行中保持一致

69.理解数据库第一，二，三，四，五，六范式

70.领域模型包括CRUD外还应包含领域相关的业务方法，服务类和领域模型类打交道。为让日后数据库访问的优化工作更有效开展，应使用mybatis(ibatis)而不是使用hibernate之类的数据库开发框架.

71.在性能要求特高或不是很高的情况下，要平衡设计，可以适当用些数据库访问的反模式。

 

参考资料列表:

1.《SQL语言艺术》

2.系统性能优化总结—数据库访问优化:http://blog.csdn.net/smszhuang168/article/details/8469332

3.Effective MySQL Optimizing SQL Statements:http://effectivemysql.com/

4.《SQL Antipartterns avoiding the pitfalls of database programming》

5.MySQL 数据类型 详解：http://blog.csdn.net/jiemushe1814/article/details/4716069

6.MySQL数据库之数据类型BOOL/BOOLEAN与TINYINT测试总结：http://www.mysqlops.com/2012/03/15/mysql-boolean-tinyint.html

7.MySQL数据类型及范围用法一览表：http://www.metsky.com/archives/84.html

8.MySQL性能优化的21个最佳实践（转载）：http://www.cnblogs.com/wubenzhimu/archive/2012/12/02/2798669.html

9.20个数据库设计的最佳实践:http://www.iteye.com/news/24148

10.使用mysql自带工具mysqlslap测试数据库性能：http://lvjian.blog.51cto.com/1304372/533877

11.mysql数据库开发36条军规：http://www.lmscn.com/2012/08/25/mysql数据库开发36条军规/

12.事务管理最佳实践全面解析http://blog.csdn.net/shendl/article/details/1415958

13.事务管理最佳实践多余的话之一“每次请求，一次数据库连接，一次事务”是不是金科玉律？ http://blog.csdn.net/shendl/article/details/1417657

14.提升可伸缩性的八项最佳实践：http://www.infoq.com/cn/news/2009/05/8-Best-Practices-Scalability

15.mysql的事务管理机制 http://rdc.taobao.com/blog/cs/?p=816

16.事务策略: 高并发策略 http://www.ibm.com/developerworks/cn/java/j-ts5/

17.利用 Percona Playback warm-up MySQL 資料庫…http://jnlin.org/2013/02/08/602/

18.MySQL InnoDB之事务与锁详解 http://www.mysqlops.com/2011/02/16/mysql-innodb-lock.html