郑州网站建设七彩科技,怎么把一个网站的关键词,做任务换流量的网站,网站赞赏一、建表规范 1、数据库名、表名、字段名必须使用小写字母或数字#xff0c;并且禁止以数字开头
示例#xff1a;goods_category、agent_operate_201812_log 2、数据库名、表名、字段名要做到见名识意
示例#xff1a;goods_category#xff0c;不能 gc 3、配置表建议以 …一、建表规范 1、数据库名、表名、字段名必须使用小写字母或数字并且禁止以数字开头
示例goods_category、agent_operate_201812_log 2、数据库名、表名、字段名要做到见名识意
示例goods_category不能 gc 3、配置表建议以 xx_config 形式命名
示例shop_payment_config 4、日志表建议以 xx_log 形式命名
示例system_log 5、临时表建议以 temp_xx 形式命名
示例temp_order_info_export 6、创建时间使用 create_time更新时间使用 update_time
类型使用 int(11) unsigned 7、字段类型为字符串时需要注意的
如果存储的字符串长度几乎相等则应该使用 char 定长字符串类型。
如果长度超过5000个字符则应该将字段类型定义为 text并独立出来一张表用主键对应避免影响其他字段的索引效率。 8、字段允许适当冗余以提高查询性能但必须考虑数据一致性 9、单表行数超过 500 万行 或者 单表容量超过 2GB 时才推荐进行分库分表 10、当存储的字段为小数时数据类型设置为 decimal禁止使用 float 和 double
在存储的时候float 和 double 存在精度损失的问题很可能在比较值的时候得到不正确的结果。
如果存储的数据范围超过 decimal 的范围建议将数据拆成整数和小数分开存储。
存储商品价格时统一转为分存储类型应为整型 int。 11、没有特殊要求的情况下所有的数据表必须使用 Innodb 存储引擎
Innodb 支持事务支持行级锁拥有更好的并发性能和恢复性。 12、数据库和数据表的字符集统一使用 utf8需要存储 emoji 表情的使用 utf8mb4 13、所有数据表和字段必须写 comment 注释说明
有条件尽量建立数据字典。 14、尽量做到冷热数据分离减小表的宽度
表越宽把表装进内存缓冲池时所占用的内存也就越大也会消耗更多的 IO。 15、禁止在数据表中建立预留字段
预留字段的命名很难做到见名识意并且无法选择合适的类型而且对预留字段修改时会对整张表进行锁定。 16、禁止在数据库中存储图片、文件等大的二进制数据
文件很大时IO 将会很耗时也会占用很多带宽影响响应速度。
建议图片、视频、大文件统一存储在文件存储空间比如阿里云、腾讯云的对象存储空间和文件存储空间数据库中只记录文件地址。 17、设置合适的字符存储长度 对象 年龄区间类型 字节表示范围人 150岁以内unsigned tinyint1无符号值 0~255乌龟 数百岁unsigned smallint2无符号值 0~65535恐龙化石数千万年unsigned int4无符号值 0~约42.9亿太阳 约50亿年unsigned bigint8无符号值 0~约10^1918、条件允许就将字符串转换成数字类型存储
比如存储ip时使用 ip2long 和 long2ip 19、避免使用 enum 类型存储字段
enum 类型的 orderby 操作效率低。 20、建议把所有列定义为 NOT NULL
索引 null 列需要额外的空间来保存要占用更多空间。进行比较时和计算时要对 null 值进行特别处理。 21、禁止在开发环境、测试环境直接连接生产环境数据库 二、索引规范 1、业务上具有唯一特性的字段即使是多个字段的组合也必须建成唯一索引
唯一索引影响 insert 的速度可以忽略不计但会明显提高查询速度。
另外即使在应用层做了非常完善的校验控制只要没有唯一索引根据墨菲定律必然会有脏数据产生。 2、禁止3个表以上join。需要join的字段数据类型必须一致当多表关联时保证被关联的字段有索引 3、限制每张表上的索引数量尽量不超过5个
索引增加查询效率的同时也会降低插入和更新的效率甚至有时会降低查询效率。
mysql优化器在选择如何优化查询时会根据统一信息对每一个可以用到的索引进行评估以生成一个最佳的执行计划。
如果同时有很多个索引都可以用于查询就会增加mysql优化器生成执行计划的时间进而降低查询性能。 4、在 varchar 字段上建立索引时必须指定索引长度
没必要对全字段建立索引根据实际文本区分度决定索引长度即可。一般对字符串数据长度为20的索引区分度就会高达 90%。
可以使用下列语句来确定区分度。
count(distinct left (列名,索引长度)) / count(*) 5、页面搜索严禁左模糊或全模糊如果需要请使用搜索引擎解决
索引文件具有最左匹配特性如果左边的值未确定则无法使用此索引。 6、如果有order by 的场景请注意利用索引的有序性
正例where a5 and b10 order by c; #索引 a_b_c 生效反例where a10 order by b #索引中有范围查找索引 a_b 不生效 7、使用延迟关联或者子查询优化超多分页场景
MySQL 并不是跳过 offset 行而是取 offset n 行。
当 offset 特别大时效率将会非常低要么控制返回的总页数要么对超过特定阀值的页数进行 SQL 改写。
正例先快速定位需要获取的 id 段然后再关联。
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.idb.id实例对比
select a.* from agent_admin a,
(select agent_admin_id from agent_admin where admin_id 11400 limit 1000,5
) b
where a.agent_admin_idb.agent_admin_id;
0.017sSELECT * from agent_admin where admin_id 11400 limit 1000,5;
0.023S 8、建立组合索引时区分度最高的放在最左边 9、哪些字段最好建索引
1经常出现在 where 从句的字段
2包含在 order bygroup by、distinct 中的字段 10、避免建立重复索引和冗余索引
建立冗余索引ui增加查询优化器生成执行计划的时间
// 重复索引示例
primary key(id)
index(id)
unique key(id)// 冗余索引示例
index(a,b,c)
index(a,b)
index(a)11、创建索引时尽量避免如下误解
1宁滥勿缺认为一个查询就需要建立一个索引
2宁缺毋滥任务索引会消耗空间、严重拖慢更新和新增速度
3抵制唯一索引认为业务的唯一性一律需要在应用层通过“先查后插”的方式解决 三、SQL 开发规范 1、不要使用 count(列名) 或 count(常量) 来替代 count(\*
count(\*) 是 SQL92 定义的标准统计行数的语法count(\*) 会统计值为NULL的行而count(列名) 不会统计此列值为 null 的行。 2、在代码中写分页查询逻辑时如果 count 为 0 应直接返回结果避免继续执行后面的程序再返回结果 3、禁止使用存储过程
存储过程难以调试和扩展新人接手麻烦可移植性差。 4、禁止使用外键与级联一切外键概念必须在应用层解决
以学生和成绩的关系为例学生表中的student_id是主键那么成绩表中的student_id则为外键。如果更新学生表中的 student_id,同时触发成绩表中的student_id更新即为级联更新。
外键与级联更新适用于单机低并发不适合分布式、高并发集群
级联更新是强阻塞存在数据库更新风暴的风险外键影响数据库的插入速度。 5、建议使用预编译语句进行数据库操作
尽量使用框架自带的查询构造器其底层均封装了预编译处理。如果特殊情况使用不了框架的查询构造器也要手动预编译查询。
预编译语句可以重复使用优化查询器生成的执行计划减少 SQL编译 所需要的时间还可以解决动态 SQL 所带来的的 SQL 注入问题。 6、避免数据类型的隐式转换
select user_name,age from admin where admin_id 11140;7、禁止使用 select \*必须指定要查询的具体字段 1无法使用覆盖索引
注覆盖索引的含义是 select 的数据列只从索引中就能够取得不必读取数据行换句话说查询列已经被所建的索引覆盖。
2消耗更多的 cpu 和 IO 以及网络带宽资源。 8、避免使用子查询可以把子查询优化成join查询
子查询的结果集无法使用索引通常子查询的结果集会被存储到临时表中不论是内存临时表还是磁盘临时表都不会存在索引所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询其对查询性能的影响也就越大。
注通常子查询在in子句中并且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。 9、避免使用JOIN关联太多的表
对于Mysql来说是存在关联缓存的缓存的大小可以由join_buffer_size参数进行设置。
在 Mysql 中对于同一个 SQL 多关联一个表就会多分配一个关联缓存在一个SQL 中关联的表越多所占用的内存就越大。
如果程序中大量的使用了多表关联的操作同时 join_buffer_size 设置的也不合理的情况下就容易造成服务器内存溢出的情况进而影响到服务器数据库性能的稳定性。
MySQL 最多允许关联61个表建议不超过5个 10、减少和数据库的交互操作
合并多个相同的操作到一起可以提高处理效率。比如批量更新时将语句处理合并后在提交到 MySQL 中进行处理这样效率会更高。
一定要避免在循环中执行 SQL。 11、对同一列进行 or 判断时使用 in 代替 or
举例
select user_name,age from admin where city in (1024, 1028);in 操作可以更有效的利用索引or 大多数情况下很少能利用到索引。但需要注意的是in 的值不要超过500个。 12、禁止在 SQL 语句中进行函数转换和计算
将数据取出来再在程序中进行处理比如格式化时间和转换ip时。 13、在明显不会有重复值时使用 UNINON ALL而不是 UNION
UNION 会把两个结果集的所有数据放到临时表再进行去重操作
UNINON ALL不会再对结果集进行去重操作 14、拆分复杂的大 SQL 为多个小 SQL
SQL 拆分后可以通过并行执行来提高处理效率。 15、大批量操作分批执行
大批量修改数据会造成表中大量数据行被锁定从而造成大量的阻塞。
长时间的阻塞会占满数据库所有的可用连接使生产环境中的其他应用无法连接到数据库。
因此一定要注意大批量写操作一定要分批执行。 原文链接https://www.haveyb.com/article/149
