数据库开发-apk开发时so库的作用

6、财务相关的金额数据必须使用小数类型

1）非精度浮点数：float、double

2) 精确浮点数：十进制

Decimal 类型是一个精确的浮点数，在计算时不会丢失精度。 占用空间由定义的宽度决定，每4个字节可存放9位数字，小数点占用1个字节。 可用于存储大于 bigint 的整数数据。

7.使用DECIMAL代替FLOAT和DOUBLE来存储精确的浮点数

浮点数相对于定点数的优势在于，当长度固定时，浮点数可以表示更大的数据范围； 浮点数的缺点是会造成精度问题

1）将字符转换为数字

2) 使用 TINYINT 而不是 ENUM 类型

3）字段长度尽量根据实际需要分配，不要随意分配大容量

8.使用UNSIGNED存储非负整数

同样的字节数，存储的取值范围更大。比如tinyint有符号为-128-127，无符号为0-255； INT类型占用4个字节的存储空间

9.使用INT UNSIGNED存储IPV4

10.使用VARBINARY存储区分大小写的变长字符串

11.禁止在数据库中存储明文密码

四、指数设计规范

建立索引的目的是通过索引来查找数据，减少随机IO，提高查询性能。 索引过滤掉的数据越少，从磁盘读取的数据就越少。

索引是一把双刃剑，提高查询效率的同时也会降低插入和更新速度，占用磁盘空间。

1.单表索引数不超过5个

限制每个表上的索引数。 建议单表索引不超过5个； 索引可以提高效率也可以降低效率。 索引可以提高查询效率，但也会降低插入和更新的效率，在某些情况下甚至会降低查询效率。 当优化器选择如何优化查询时，它会根据统一的信息评估每个可用的索引，以生成最佳的执行计划。 如果有多个索引可以同时用于查询，会增加MySQL优化器生成执行计划的时间，降低查询性能。

2.禁止为表中的每一列创建单独的索引

在5.6版本之前，一条SQL只能在一张表中使用一个索引。 5.6之后，虽然有合并索引的优化方式，但是效率远不如使用合并索引的查询方式。

3. Innodb表必须有主键

Innodb是一个索引组织表：数据存储的逻辑顺序和索引的顺序是一样的。

每个表可以有多个索引，但是表的存储顺序只能有一个 Innodb按照主键索引的顺序组织表。 不要使用频繁更新的列作为主键，多列主键不适用（相当于联合索引）。 不要使用UUID、MD5、HASH、string列作为主键（不能保证数据的顺序增长）。

主键推荐使用自增ID值。

4.单个索引的字段数不超过5个

对字符串使用前缀索引，前缀索引的长度不超过10个字符；

例如：如果有一个CHAR(200)列，大部分值在前10个字符内是唯一的，你不需要索引整个列。 索引前 10 个字符可以节省大量索引空间，还可以加快查询速度。

5.表主键建议

1）表必须有主键，不要使用频繁更新的列作为主键

2）尽量不要选择字符串列作为主键

3）不要使用UUID、MD5、HASH作为主键

4）默认使用非空唯一键

5）主键建议选择自增或者issuer。 必须索引重要的 SQL：

SELECT、UPDATE、DELETE语句的WHERE条件列字段 多表JOIN的ORDER BY、GROUP BY、DISTINCT字段

6.区分度最高的字段放在索引前面

7.核心SQL优先覆盖索引

select的数据列只能从索引中获取，不能读取数据行，也就是说查询的列必须被建好的索引覆盖。

8.避免冗余或重复索引

合理创建联合索引（避免冗余），index(a,b,c)等价于index(a),index(a,b),index(a,b,c)

1）指标越多越好。 根据实际需要创建它们。 每个额外的索引都会占用额外的磁盘空间并降低写操作的性能。

2）不要在低基数列上建立索引，例如“性别”

3）不要对索引列进行数学运算和函数运算

9.尽量避免使用外键约束

1）不建议使用外键约束（foreign key），但一定要在表间关联键上建立索引；

2）外键可以用来保证数据的引用完整性，推荐在业务端实现；

3）外键会影响父表和子表的写操作，从而降低性能。

10、不使用前导%的查询，如“%xxx”，不能使用索引

11.不要使用反向查询，比如not in / not like

索引无法使用，导致全表扫描，从而导致bufferpool利用率下降；

12.索引栏目建议

1）出现在SELECT、UPDATE、DELETE语句的WHERE子句中的列；

2）ORDER BY、GROUP BY、DISTINCT中包含的字段；

3）多表join关联列

注意：不要为匹配1和2中字段的列建立索引，通常情况下，最好为1和2中的字段建立联合索引。

13、如何选择索引列的顺序

1）区分度最高的放在联合索引的最左边（区分度=列中不同值的个数/列中的总行数）；

2）尽量把字段长度小的列放在联合索引的最左边（因为字段长度越小，一页可以存储的数据量越大，IO性能越好）；

3）最常用的列放在联合索引的左侧（这样可以少建索引）。

14.避免创建冗余和重复的索引

冗余/重复索引会增加查询优化器生成执行计划所需的时间。

1）重复索引示例：primary key(id), index(id), unique index(id)

2) 冗余索引示例：index(a,b,c), index(a,b), index(a)

15.优先覆盖索引

覆盖索引是频繁查询的首选。

覆盖索引：即包含所有查询字段（where、select、ordery by、group by字段）的索引，覆盖索引的好处：

1）避免Innodb表二次查询做索引

Innodb是按照聚簇索引的顺序存储的。 对于 Innodb 来说，二级索引存储的是叶子节点中行的主键信息。 如果使用二级索引来查询数据，在找到对应的键值后，还需要通过主键进行二级查询，获取我们真正需要的数据。

在覆盖索引中，所有的数据都可以在二级索引的key值中获取，避免了主键的二次查询，减少IO操作，提高查询效率。

2）可以将随机IO改为顺序IO，加快查询效率

由于覆盖索引是按照键值顺序存储的，对于IO密集型范围搜索来说，每一行的数据IO要比从磁盘随机读取每一行要少得多。 读取 IO 转换为顺序 IO 以进行索引查找。

五、SQL开发规范

1.推荐使用预编译语句进行数据库操作

预编译语句可以重用这些计划，减少SQL编译所需的时间，也可以解决动态SQL带来的SQL注入问题。 仅传递参数比传递 SQL 语句更高效。 同一条语句可以被解析一次并被多次使用。 提高加工效率。

2.避免隐式转换数据类型

隐式转换使索引无效。

3.充分利用表上已有的索引

1) 避免使用双% 号查询条件。

如果没有前导 %，则只能将尾随 % 用于列上的索引

2）一条SQL只能使用复合索引中的一列进行范围查询

解释：存在a、b、c列的联合索引。 如果查询条件中有a列的范围查询，则不会使用b列和c列的索引。 定义联合索引时，如果需要a列 如果使用范围搜索，则a列必须放在联合索引的右侧。 使用left join or not exists来优化not in操作，因为not in通常也使用索引失效。

4.设计数据库时，要考虑未来的扩展

5.程序连接不同的数据库，使用不同的账号，禁止跨库查询

1）为数据库迁移和分库分表留有余地

2）降低业务耦合

3）避免权限过高带来的安全风险

6. 强烈不推荐SELECT *； 推荐使用 SELECT 查询

原因：

1）消耗较多的CPU和IO以及网络带宽资源

2）不能使用覆盖索引

3）可以减少表结构变化的影响

7.禁止使用没有字段列表的INSERT语句

例子：insert into values('a','b','c');

应该使用 insert into t(c1,c2,c3) values('a','b','c');

8、避免使用子查询，将子查询优化为join操作

通常子查询在in子句中，子查询是简单的SQL（不包括union、group by、order by、limit子句），那么可以将子查询转化为关联查询进行优化。

子查询性能差的原因：

1）子查询的结果集不能使用索引。 通常，子查询的结果集会存储在一个临时表中。 内存临时表和磁盘临时表都不会有索引，所以查询性能会受到一定的影响；

2）尤其是返回结果集比较大的子查询，对查询性能的影响更大；

3）由于子查询会产生大量的临时表，又没有索引，会消耗过多的CPU和IO资源，产生大量的慢查询。

9.避免使用JOIN关联过多的表

MySQL 最擅长查询单表的主键/二级索引。 MySQL有一个关联的缓存，缓存的大小可以通过join_buffer_size参数来设置。 在MySQL中，同一条SQL关联（join）一张表，会多分配一个关联缓存。 一条SQL关联的表越多，占用的内存就越大。

Join消耗内存较多，会生成临时表；

如果程序中大量使用多表关联操作数据库开发，join_buffer_size设置不合理，很容易造成服务器内存溢出，影响服务器数据库性能的稳定性。

同时对于关联操作，会产生临时表操作，影响查询效率。 MySQL最多允许关联61张表，但在业务生产环境中建议不要超过5张。

10.减少与数据库的交互次数

1）数据库更适合处理批量操作，将多个相同的操作合并在一起，可以提高处理效率。

11、做or判断对应同一列时，用in代替or

操作中不要超过500次，以更有效地利用索引，或者在大多数情况下很少使用索引。

12、禁止使用order by，rand()进行随机排序

随机排序会将表中所有符合条件的数据加载到内存中，然后根据随机生成的值对内存中的所有数据进行排序，并且可能会为每一行生成一个随机值，如果满足条件的数据集很大的话，会消耗大量的CPU、IO和内存资源。

简单来说：order by，rand()会从磁盘中读取数据并排序，会消耗大量的IO和CPU。

建议在程序中获取一个随机值，然后从数据库中获取相应的数据。

13、WHERE子句中禁止对列进行函数转换和计算

对列进行函数转换或计算时不能使用索引。

14. 当很明显不会有重复值时，使用 UNION ALL 而不是 UNION

1）UNION会将两个结果集的数据全部放入临时表，然后进行去重操作；

2) UNION ALL 将不再去重结果集。

15.将复杂的大SQL拆分成多个小SQL

原因如下：

1）Big SQL：逻辑复杂，需要大量CPU进行计算；

2）MySQL：一条SQL只能占用一个CPU进行计算；

3）SQL拆分后，可以通过并行执行提高处理效率。

16.避免使用存储过程、触发器、EVENTS等。

1）降低业务耦合度，为分库分表的sacleout和sharding留有余地；

2）改变策略可以有效避免BUG。

17. 避免在数据库中做数学运算

1）很容易将业务逻辑和DB耦合在一起

2）MySQL不擅长数学运算和逻辑判断

3）无法使用索引

6. 行为准则

1.超过100万行的批量写入（UPDATE、DELETE、INSERT）操作需要批量执行多次

1）大批量操作可能会造成严重的主从延迟。

在主从环境下，大规模的操作可能会造成严重的主从延迟。 一般来说，大规模的写操作需要执行一定的时间。 只有主库执行完成后，才会在其他从库上执行，这会造成主库和从库之间的长时间延迟。

2）当binlog日志为行格式时，会产生大量的日志

大量的写操作会产生大量的日志，尤其是行格式的二进制数据。 由于每行数据的修改都是以行格式记录的，一次修改的数据越多，产生的日志就越多。 传输和恢复所需的时间也较长，这也是主从延迟的一个原因。

3）避免大事务操作

大批量修改数据必须在一个事务中完成，这会导致表中大量数据被锁定，造成大量阻塞数据库开发，这将极大地影响MySQL的性能。 特别是长期阻塞会占用数据库的所有可用连接，导致生产环境中的其他应用无法连接到数据库。 因此，注意批量写入操作很重要。

2.对于大表，使用pt-online-schema-change修改表结构

1）可以避免大表修改带来的主从延迟

2）可以避免在修改表字段时锁定表

在生产环境中，修改大表的数据结构时一定要谨慎，会造成严重的表锁操作；

pt-online-schema-change首先创建一个与原表结构相同的新表，并在新表上修改表结构，然后将原表中的数据复制到新表中，并添加一些触发器。

将原表中新增的数据复制到新表中。 复制完所有数据后，将新表命名为原表，删除原表。 将原来的 DDL 操作分解为多个小批量作业。

这也是对表进行碎片整理/重组的常用方法。

3.禁止给程序使用的账号授予super权限

原因：当MySQL达到最大连接数限制时，此时还有一个超级权限的用户连接在运行，超级权限只能保留给DBA的账号处理问题。

4.对于连接数据库账号的程序，遵循最小权限程序原则

数据库账号只能在一个DB下使用，不允许跨库程序使用的账号原则上不允许有drop权限。

其他一些操作规范：

5. 任何对数据库的在线操作都必须经过工单

6、禁止对主库进行统计功能查询；

7、大型营销运营活动，必须提前通知DBA进行流量评估；

8、对单张表的多次alter操作必须合并为一次操作；

9、可以创建存储过程，无需在MySQL数据库中存储业务逻辑；

10、重大项目数据库方案的选择和设计，必须提前通知DBA参与；

11、数据要有备份机制，定期恢复演练；

12、不要在业务高峰期批量更新或查询数据库；