mysql高性能扩展_____第一章

涉及到电商常用功能模块的数据库设计

注册模块,用户模块,商品模块,购物车模块,订单模块,仓库模块,物流模块.(购物流程为基础)

业务场景中常见的数据库问题解决方案

只包含数据库的设计和开发(不设计前后台的设计和开发)

准备工作:

mysql 5.7_version(5.5,5.6都可,不涉及差异问题)

mysql客户端工具 navcat工具

linux常用命令和shell脚本的开发知识

项目说明:

电商项目部:数据库的设计和开发:参照京东的样子来做.

第一期:完成用户的正常购物,

用户登录 选购商品 加入购物车 检查库存 提交订单 货到付款 在线支付 发货

用户模块:完成用户的注册和登录验证

商品模块:前后台商品管理和浏览

订单模块:订单及购物车的生成和管理

仓配模块:仓库库存和物流的管理

数据库的设计规范:

1.数据库的结构数据,先逻辑设计,后物理设计(实际工作中,不是如此,逻辑设计中已经进行了物理设计[表名和字段的确定和选择])

数据库命名规范:(从名称上就能了解到数据库的作用以及所存储数据的内容)

数据库基本设计规范:(常用的数据库设计的方式,存储引擎的选择,字符集的选择等内容,)

数据库索引设计规范:(索引的选择对数据库的性能是很有必要的,索引列的选择常规方式)

数据库字段类型设计规范:(字段类型的选择和数据库的性能是息息相关的)

数据库字段设计规范(命名规范和字段长度规范[见名知意])

数据库sql开发规范(对开发人员来说能更好的写sql语句)

数据库操作行为规范(对运维人员,所针对的数据库,避免造成数据库的事故)

禁止使用数据库保留字

http://dev.mysql.com/doc/refman/5.7/en/keywords.html 查看mysql的所有关键字(保留字)

在数据库中建立一些临时的表:比如导入导出数据时所使用的中间表,或者用于临时数据统计所使用的表,如果这些表用完马上就清理掉最好,如若不能数据库中就会出现很多的垃圾表,规范如果是临时表在其:

关联列的数据类型必须一致,不然在关联的时候就会有隐式的数据类型转换,造成索引不可用,降低数据库的性能.

数据库基本设计规范

所有表须使用innodb存储引擎.5.6_version以后mysql的存储引擎已成为默认.

支持行级锁,事务,更好的恢复性,高并发下性能更好.

数据库和表的字符集统一使用utf8,可以避免由于字符集转换造成的编码问题

innodb是一种索引组织表,就是数据存储的逻辑顺序和索引的顺序是相同的.当然在每个表上都可以有多个索引,而表的存储顺序只能有一种.innodb是按照那个索引的顺序来组织表的呢.答案:主键,

如果没有主键,就是用非空唯一索引来作为主键,连非空唯一索引都么有,那就自己生成一个6个字节的主键,而这个生成的主键的性能不是最好的.

如何选择索引的顺序:

区分度最高的列放在联合索引的最左侧,区分度:表中列中唯一值的数量.(区分度最大的就是主键和唯一索引列)

区分度相差不大的,把字段长度小的放在联合索引的左侧.

把使用列最频繁的列放在联合索引的最左侧.

控制索引的数量:重复索引,冗余了,如
primary key(id),index(id),unique index (id) 重复索引

冗余索引
index(a ,b,c) index(a,b) index(a)
 
对于频繁查询的sql考虑覆盖索引(就是包含了所有查询字段的索引)
好处:避免innodb表进行索引的二次查找
可以把随机io变为顺序io加快查询效率.

尽量不用外键
不建议使用外键约束,但一定在表与表之间的关联键上建立索引.
外键的作用是保证数据的参照完整性,但建议在业务端实现(主表和子表中的数据是一致的)
外键会影响主表和子表的写操作从而降低性能.

优先选择符合存储需要的最小的数据类型(不容易,需要经验)
将字符串转换为数字类型存储
inet_aton('255.255.255.255') = 4264967295
inet_ntoa(4264967295) = ('255.255.255.255')
对于非负整数,使用无符号的数据类型
signed int -2147483648 ~ 2147483647
unsigned int 0~4264967295

varchar(n) n:代表的是字符数,而不是字节数.255的话,是能存储255个汉字的
使用utf8存储汉字varchar(255) = 765个字节
过大的长度会消耗更多的内存
避免使用text, blob数据类型.(备注说明这样的列也不用)
避免使用enum数据类型(在别的数据库中并不存在这种类型)
尽可能把所有列定义为not null?对于可能为空的列有null值,要占用更多的空间,在进行列的比较和计算时要进行null处理,索引失效

避免使用字符串存储日期类型的数据
1.无法用日期函数进行计算和比较
2.用字符串存储日期要占用更多的空间.
timestamp datetime
timestamp 显示年月日时分秒 19700101 - 20380119 03:14:07 占用4个字节  和int相同,但比int可读性高.
对于超出timestamp取值范围的使用datetime类型
decimal类型:精准的浮点数,在计算时不会丢失精度
占用空间由定义的宽度决定,能保存比bigint更大的数据

建议使用预编译语句进行数据库操作

好处:只传参数,比传递sql更高效,避免sql注入
编码数据类型的隐式转换:导致索引时效.
select name,phone from customer where id = '111';值是字符串,但是id的数据类型是int型,就会隐式转换,
导致索引失效

合理利用存在索引,不能盲目增加
避免使用双%的查询条件.如 a like '%123%'
一个sql只能利用到复合索引中的一列进行范围查询.
使用left join或者 not exists 来优化not in操作.

应该对以后的数据库的扩展考虑
程序连接不同的数据库使用不同的账号,禁止跨库查询.目的:为数据库迁移和分库分表留出余地
降低业务耦合度
避免黑客看到另一个库

禁止select *
缺点:消耗更多的CPU和io以及网络带宽资源.

无法使用覆盖索引:不可能把所有表中的列都包含到一个索引中.
可减少表结构变更带来的影响.

避免使用子查询,可以把子查询转换为关系查询(left join)优化
子查询的性能差:子查询的结果集无法使用索引
子查询会产生临时表的操作,如果子查询数据量大则严重影响效率.
消耗过多的CPU和io资源(由于创建了多的临时表)

避免使用join关联太多的表
每join一个表会占用一部分内存(join_buffer_size)
会产生临时表操作,影响查询效率
mysql运行关联61个表,建议不超过5个.

减少同数据库的交互次数

数据库更适合批量操作
合并多个相同的操作到一起,可以提高处理效率(alter操作批处理更好)
alter table t1 add column c1 int,change column c2 c2 int ...

or逻辑条件
使用in 代替or
in中的值不要超过500个
in操作是可以有效的利用索引的
禁用order by rand()进行随机排序.
会把表中所有符合条件的数据装载到内存中进行排序
会消耗大量的cup和io及内存资源
推荐在程序中获取一个随机值,然后从数据库中获取数据的方式

where从句中禁止对列进行函数转换和计算 会导致无法使用索引
where date(createtime) = '20160910' 不可取
where createtime >= '20160910' and createtime < '20160911'


union 会把所有的数据放到临时表中再进行去重操作
union all 不会再对结果集进行去重操作

拆分复杂的大sql为多个小sql
一个sql只能使用CPU进行计算.
sql拆分后可以通过并行执行来提高处理效率
对于100万行的批量写操作,要分批多次进行操作.
大批量的写操作可能会造成严重的主从延迟,
binlog日志为row格式时会产生大量的日志
避免产生大事务操作
 
对于列的数据类型的修改,不能在线ddl
对于大表的表结构修改,使用pt-online-schema-change修改表结构

禁止为程序使用的账号赋予super权限
当达到最大连接限制时,还允许1个有super权限的用户连接.
super权限只能留给dba处理问题的账号使用.

对于程序连接数据库账号,遵循权限最小原则

程序使用数据库账号只能在一个db下使用,不准跨库
程序使用的账号原则上不准有drop权限.