常用sql优化2019-09-27

sql优化

sql优化............................................................................................................................... 1

1.      基础规范...................................................................................................................... 4

1.1.      必须使用innodb存储引擎................................................................................. 4

1.1.1.       支持事务....................................................................................................... 4

1.1.2.       行级锁........................................................................................................... 4

1.1.3.       并发性能好................................................................................................... 4

1.1.4.       cpu以及缓存页优化使用,利用率高........................................................... 5

1.2.      必须使用UTF8字符集........................................................................................ 5

1.2.1.       万国码........................................................................................................... 5

1.2.2.       无需转码....................................................................................................... 5

1.2.3.       没有乱码风险............................................................................................... 5

1.2.4.       节省空间....................................................................................................... 5

1.3.      数据表,数据字段必须加入中文注释................................................................. 5

1.3.1.       时间久了谁知道字段干嘛的....................................................................... 5

1.4.      禁止使用存储过程 视图 触发器 event........................................................... 5

1.4.1.       高并发的大数据互联网业务,架构设计思路是解放数据库CPU 将计算业务转移到服务层  6

1.4.2.       并发量大的情况下,这些功能可能会将数据库拖死,业务逻辑放到服务层具有更好的扩展性              6

1.4.2.1.       能够轻易实现增加性能........................................................................ 6

1.4.2.2.       数据库擅长 存储和索引...................................................................... 6

1.4.2.3.       cpu计算放到业务层............................................................................. 6

1.5.      禁止存储大文件或者照片.................................................................................. 6

1.5.1.       大文件存在文件系统里面,数据库用来存url............................................ 6

2.       命名规范..................................................................................................................... 6

2.1.      只允许使用内网域名,而不是IP 链接数据库................................................... 6

2.2.      线上环境.............................................................................................................. 7

2.3.      开发环境.............................................................................................................. 7

2.4.      测试环境.............................................................................................................. 7

2.5.      库名 表名 字段名 小写_下划线风格  不超过32个字符见名知意  禁止中英文混用     7

遵循命名规范                                                                                   (线上环境 , 开发环境, 测试环境).............................................................................. 7

2.5.1.       业务名称:xx.................................................................................................. 7

2.5.2.       线上环境:dj.xxx.db....................................................................................... 7

2.5.3.       开发环境：dj.xxx.rdb................................................................................... 7

2.5.4.       测试环境：dj.xxx.tdb................................................................................... 7

2.5.5.       从库在名称后加-s标识，备库在名称后加-ss标识.................................. 7

2.5.5.1.       线上从库：dj.xxx-s.db.......................................................................... 7

2.5.5.2.       线上备库：dj.xxx-sss.db....................................................................... 7

3.       表设计规范................................................................................................................. 8

3.1.      单实例表数据必须小于500............................................................................... 8

3.2.      单表列数据必须小于30..................................................................................... 8

3.3.      表必须有主键...................................................................................................... 8

3.3.1.       自增主键....................................................................................................... 8

3.3.2.       a..................................................................................................................... 8

3.3.2.1.       数据行写入可以提高插入性能............................................................ 8

3.3.2.2.       避免page分裂...................................................................................... 8

3.3.2.3.       减少表的碎片提升空间和内存的使用................................................ 9

3.3.3.       b..................................................................................................................... 9

3.3.3.1.       主键选择比较短的数据类型................................................................ 9

3.3.3.1.1.     Innodb 引擎普通索引都会保存主键的值..................................... 9

3.3.3.1.2.     比较短的主键可以减少索引的磁盘空间 提高索引的利用效率 9

3.4.        禁止使用外键 如果有外键的完整性约束 需要应用程序控制..................... 9

3.4.1.       外键导致表间耦合 update和delete都会涉及到关联的表,影响sql性能  甚至死锁 高并发情况下容易造成数据库性能低............................................................................. 9

3.5.        字段设计规范..................................................................................................... 9

3.5.1.       字段必须定义为not null 并且提供默认值............................................. 10

3.5.1.1.       null 容易引起全盘扫描...................................................................... 10

3.5.2.       禁止使用text blob类型............................................................................ 10

3.5.2.1.       非必要的大量文字字段查询会淘汰掉热数据 导致内存命中率很低 影响数据库性能                 10

3.5.3.       禁止使用小数存储货币............................................................................. 10

3.5.3.1.       容易对不上.......................................................................................... 11

3.5.4.       必须使用varchar(20)存储手机号............................................................. 11

3.5.4.1.       设计区号或者国家代号 可能出现 +-()............................................ 11

3.5.4.2.       手机号不会做数学运算...................................................................... 11

3.5.4.3.       varchar支持模糊查询........................................................................ 11

3.5.4.3.1.     例如 like "138%"........................................................................... 11

3.5.5.       索引设计规范............................................................................................. 11

3.5.5.1.       单表索引控制在5个以内.................................................................. 11

3.5.5.2.       单索引字段不允许超过5个.............................................................. 11

3.5.5.2.1.     超过5个已经没有........................................................................ 12

3.5.5.3.       禁止在更新十分频繁 区分度不高的属性上建立索引.................... 12

3.5.5.3.1.     更新会变更B+树........................................................................... 12

3.5.5.3.1.1.     更新频繁的字段简历索引会大大降低数据库性能............. 12

3.5.5.3.2.     性别这种区分度不是很大的属性 ,建立索引是没有意义的不能有效的过滤数据 性能与全表扫描类似....................................................................................... 12

3.5.5.4.       建立组合索引 必须把区分度高的字段放在前面............................ 12

3.5.5.4.1.     能够更加有效的过滤数据............................................................ 13

4.       sql使用规范............................................................................................................. 13

4.1.        禁止使用* 只获取必要的字段 需要显示说明使用列的属性..................... 13

4.1.1.       读取不需要的列 会增加cpu IO 消耗..................................................... 13

4.1.2.       不能有效的利用覆盖索引......................................................................... 13

4.1.3.       使用 select * 容易在增加或者删除字段后出现程序bug..................... 13

4.2.        禁止使用insert into t xxx

values(xxx) 必须显示指定插入的列属性............ 13

4.2.1.       容易在增加或者删除之后出现程序的bug.............................................. 13

4.3.        禁止使用属性的隐式转换............................................................................... 13

4.3.1.       解读：SELECT uid FROM t_user

WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次）....................... 14

4.4.        禁止where条件属性上使用函数或者表达式............................................... 14

4.4.1.       错误............................................................................................................. 14

4.4.1.1.       SELECT uid FROM t_user WHERE

from_unixtime(day)>='2017-02-15' 会导致全表扫描                 14

4.4.2.       正确............................................................................................................. 14

4.4.2.1.       SELECT uid FROM t_user WHERE day>=

unix_timestamp('2017-02-15 00:00:00')   14

4.5.        禁止使用负向查询 以及%开头的模糊查询.................................................. 14

4.5.1.       a................................................................................................................... 15

4.5.1.1.       负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT

LIKE等，会导致全表扫描  15

4.5.2.       b................................................................................................................... 15

4.5.2.1.       %开头的模糊查询，会导致全表扫描............................................... 15

4.6.        禁止大表使用join查询  禁止大表使用子查询........................................... 15

4.6.1.       子查询会产生临时表 消耗过多的内存和cpu........................................ 16

4.7.        禁止使用or 条件 必须改为in查询............................................................. 16

4.8.        应用程序必须捕获sql异常,并做响应处理................................................... 16

[if !vml]

[endif]

[if !supportLists]1.       [endif]基础规范

[if !vml]

[endif]

[if !supportLists]1.1.        [endif]必须使用innodb存储引擎

[if !vml]

[endif]

[if !supportLists]1.1.1.         [endif]支持事务

[if !supportLists]1.1.2.         [endif]行级锁

[if !supportLists]1.1.3.         [endif]并发性能好

[if !supportLists]1.1.4.         [endif]cpu以及缓存页优化使用,利用率高

[if !supportLists]1.2.        [endif]必须使用UTF8字符集

[if !vml]

[endif]

[if !supportLists]1.2.1.         [endif]万国码

[if !supportLists]1.2.2.         [endif]无需转码

[if !supportLists]1.2.3.         [endif]没有乱码风险

[if !supportLists]1.2.4.         [endif]节省空间

[if !supportLists]1.3.        [endif]数据表,数据字段必须加入中文注释

[if !vml]

[endif]

[if !supportLists]1.3.1.         [endif]时间久了谁知道字段干嘛的

[if !supportLists]1.4.        [endif]禁止使用存储过程 视图 触发器 event

[if !vml]

[endif]

[if !supportLists]1.4.1.         [endif]高并发的大数据互联网业务,架构设计思路是解放数据库CPU 将计算业务转移到服务层

[if !supportLists]1.4.2.         [endif]并发量大的情况下,这些功能可能会将数据库拖死,业务逻辑放到服务层具有更好的扩展性

[if !vml]

[endif]

[if !supportLists]1.4.2.1.          [endif]能够轻易实现增加性能

[if !supportLists]1.4.2.2.          [endif]数据库擅长 存储和索引

[if !supportLists]1.4.2.3.          [endif]cpu计算放到业务层

[if !supportLists]1.5.        [endif]禁止存储大文件或者照片

[if !vml]

[endif]

[if !supportLists]1.5.1.         [endif]大文件存在文件系统里面,数据库用来存url

[if !supportLists]2.       [endif]命名规范

[if !vml]

[endif]

[if !supportLists]2.1.        [endif]只允许使用内网域名,而不是IP 链接数据库

[if !supportLists]2.2.        [endif]线上环境

[if !supportLists]2.3.        [endif]开发环境

[if !supportLists]2.4.        [endif]测试环境

[if !supportLists]2.5.        [endif]库名 表名 字段名 小写_下划线风格  不超过32个字符见名知意  禁止中英文混用

遵循命名规范                                                                                   (线上环境 , 开发环境, 测试环境)

[if !vml]

[endif]

[if !supportLists]2.5.1.         [endif]业务名称:xx

[if !supportLists]2.5.2.         [endif]线上环境:dj.xxx.db

[if !supportLists]2.5.3.         [endif]开发环境：dj.xxx.rdb

[if !supportLists]2.5.4.         [endif]测试环境：dj.xxx.tdb

[if !supportLists]2.5.5.         [endif]从库在名称后加-s标识，备库在名称后加-ss标识

[if !vml]

[endif]

[if !supportLists]2.5.5.1.          [endif]线上从库：dj.xxx-s.db

[if !supportLists]2.5.5.2.          [endif]线上备库：dj.xxx-sss.db

[if !supportLists]3.       [endif]表设计规范

[if !vml]

[endif]

[if !supportLists]3.1.        [endif]单实例表数据必须小于500

[if !supportLists]3.2.        [endif]单表列数据必须小于30

[if !supportLists]3.3.        [endif]表必须有主键

[if !vml]

[endif]

[if !supportLists]3.3.1.         [endif]自增主键

[if !supportLists]3.3.2.         [endif]a

[if !vml]

[endif]

[if !supportLists]3.3.2.1.          [endif]数据行写入可以提高插入性能

[if !supportLists]3.3.2.2.          [endif]避免page分裂

[if !supportLists]3.3.2.3.          [endif]减少表的碎片提升空间和内存的使用

[if !supportLists]3.3.3.         [endif]b

[if !vml]

[endif]

[if !supportLists]3.3.3.1.          [endif]主键选择比较短的数据类型

[if !vml]

[endif]

[if !supportLists]3.3.3.1.1.            [endif]Innodb 引擎普通索引都会保存主键的值

[if !supportLists]3.3.3.1.2.            [endif]比较短的主键可以减少索引的磁盘空间 提高索引的利用效率

[if !supportLists]3.4.        [endif]禁止使用外键 如果有外键的完整性约束 需要应用程序控制

[if !vml]

[endif]

[if !supportLists]3.4.1.         [endif]外键导致表间耦合update和delete都会涉及到关联的表,影响sql性能 甚至死锁 高并发情况下容易造成数据库性能低

[if !supportLists]3.5.        [endif]字段设计规范

[if !vml]

[endif]

[if !supportLists]3.5.1.         [endif]字段必须定义为not

null 并且提供默认值

[if !vml]

[endif]

[if !supportLists]3.5.1.1.          [endif]null 容易引起全盘扫描

[if !supportLists]3.5.2.         [endif]禁止使用text

blob类型

[if !vml]

[endif]

[if !supportLists]3.5.2.1.          [endif]非必要的大量文字字段查询会淘汰掉热数据 导致内存命中率很低

影响数据库性能

[if !supportLists]3.5.3.         [endif]禁止使用小数存储货币

[if !vml]

[endif]

[if !supportLists]3.5.3.1.          [endif]容易对不上

[if !supportLists]3.5.4.         [endif]必须使用varchar(20)存储手机号

[if !vml]

[endif]

[if !supportLists]3.5.4.1.          [endif]设计区号或者国家代号 可能出现 +-()

[if !supportLists]3.5.4.2.          [endif]手机号不会做数学运算

[if !supportLists]3.5.4.3.          [endif]varchar支持模糊查询

[if !vml]

[endif]

[if !supportLists]3.5.4.3.1.            [endif]例如 like "138%"

[if !supportLists]3.5.5.         [endif]索引设计规范

[if !vml]

[endif]

[if !supportLists]3.5.5.1.          [endif]单表索引控制在5个以内

[if !supportLists]3.5.5.2.          [endif]单索引字段不允许超过5个

[if !vml]

[endif]

[if !supportLists]3.5.5.2.1.            [endif]超过5个已经没有

[if !supportLists]3.5.5.3.          [endif]禁止在更新十分频繁 区分度不高的属性上建立索引

[if !vml]

[endif]

[if !supportLists]3.5.5.3.1.            [endif]更新会变更B+树

[if !vml]

[endif]

[if !supportLists]3.5.5.3.1.1.             [endif]更新频繁的字段简历索引会大大降低数据库性能

[if !supportLists]3.5.5.3.2.            [endif]性别这种区分度不是很大的属性 ,建立索引是没有意义的 不能有效的过滤数据 性能与全表扫描类似

[if !supportLists]3.5.5.4.          [endif]建立组合索引 必须把区分度高的字段放在前面

[if !vml]

[endif]

[if !supportLists]3.5.5.4.1.            [endif]能够更加有效的过滤数据

[if !supportLists]4.       [endif]sql使用规范

[if !vml]

[endif]

[if !supportLists]4.1.        [endif]禁止使用* 只获取必要的字段 需要显示说明使用列的属性

[if !vml]

[endif]

[if !supportLists]4.1.1.         [endif]读取不需要的列 会增加cpu IO 消耗

[if !supportLists]4.1.2.         [endif]不能有效的利用覆盖索引

[if !supportLists]4.1.3.         [endif]使用select * 容易在增加或者删除字段后出现程序bug

[if !supportLists]4.2.        [endif]禁止使用insert into t xxx values(xxx) 必须显示指定插入的列属性

[if !vml]

[endif]

[if !supportLists]4.2.1.         [endif]容易在增加或者删除之后出现程序的bug

[if !supportLists]4.3.        [endif]禁止使用属性的隐式转换

[if !vml]

[endif]

[if !supportLists]4.3.1.         [endif]解读：SELECT

uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次）

[if !supportLists]4.4.        [endif]禁止where条件属性上使用函数或者表达式

[if !vml]

[endif]

[if !supportLists]4.4.1.         [endif]错误

[if !vml]

[endif]

[if !supportLists]4.4.1.1.          [endif]SELECT uid

FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描

[if !supportLists]4.4.2.         [endif]正确

[if !vml]

[endif]

[if !supportLists]4.4.2.1.          [endif]SELECT uid

FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

[if !supportLists]4.5.        [endif]禁止使用负向查询 以及%开头的模糊查询

[if !vml]

[endif]

[if !supportLists]4.5.1.         [endif]a

[if !vml]

[endif]

[if !supportLists]4.5.1.1.          [endif]负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT

LIKE等，会导致全表扫描

[if !supportLists]4.5.2.         [endif]b

[if !vml]

[endif]

[if !supportLists]4.5.2.1.          [endif]%开头的模糊查询，会导致全表扫描

[if !supportLists]4.6.        [endif]禁止大表使用join查询  禁止大表使用子查询

[if !vml]

[endif]

[if !supportLists]4.6.1.         [endif]子查询会产生临时表 消耗过多的内存和cpu

[if !supportLists]4.7.        [endif]禁止使用or 条件 必须改为in查询

[if !supportLists]4.8.        [endif]应用程序必须捕获sql异常,并做响应处理