使用规则
- 不在数据库做计算,CPU计算务必移到业务层
- 控制单表数据量,单表记录控制在千万级
- 控制列数量,字段数据控制在20以内
- 平衡范式与冗余,为提高效率可以牺牲范式设计,冗余数据
- 拒绝:大Sql,大事务,大批量
Sql语句类规则
- sql语句尽可能简单
大语句拆小语句,减少锁时间 - 事务时间尽可能短
- 避免使用触发器,用户自定义函数,使用程序代替
- 不使用select * 消耗cpu,io,内存,带宽
- OR改写为IN ()
- OR 改写为UNION
- 使用性能分析工具:
show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;
show query_response_time(percona)
字段使用规则
- 避免使用NULL字段
- 不再数据库存储图片(base64)
表操作
- 唯一索引列可以为NULL。
- MYSQL默认是要区分表名大小写的,
Mysql的表名/字段名/字段值是否区分大小写
默认情况下是否区分大小写(影响表名、字段名、varchar字段内容),使用show Variables like '%table_names’查看lower_case_table_names的值,0代表区分,1代表不区分。
设置为不区分时,实际创建时表名将全部保存为小写字母,字段名为原样,字段内容为原样。
索引规则
- 字符字段必须建前缀索引
ALTER TABLE t_account_list ADD INDEX index_c_user_id (c_user_id(7)) ;
确定选择性:
SELECT count(DISTINCT(c_user_id))/count(*) AS t_account_list FROM t_account_list;
观察其选择性,如果选择性较好,接近1,则可以可以考虑建立前缀索引,例如<left(c_user_id,5)>
,如下:
SELECT count(DISTINCT(left(c_user_id,9)))/count(*) AS c_user_id FROM t_account_list;
观察选择性,挑一个接近的前缀长度。
-
不使用外键,由程序约束
-
尽量不使用字符串作为主键
随机字符串的主键数据的写入会有很多碎片产生,很多逻辑上相近的页其实分布在磁盘和内存的各个地方。所以在这类表中需要经常OPTIMIZE,不过最好尽量避免这个类型的主键。
索引使用的几个总结:
(1)使用 force index 来强制使用指定的索引:
select * from order_info force index(idx_uid_stat) where uid = 5837661 order by id asc limit 1
(2)使用 order by (id+0) 方案:将字段*1或者+0可以将MySQL字符串字段按数值排序
select * from order_info where uid = 5837661 order by (id+0) asc limit 1
(3)回表就是 MySQL 要先查询到主键索引,然后再用主键索引定位到数据。
Text与Blob
(1)TEXT与BLOB的主要差别就是BLOB保存二进制数据,TEXT保存字符数据;
(2)BLOB就可以把图片换算成二进制保存到数据库中
(3)BLOB列没有字符集,并且排序和比较基于列值字节的数值值。
TEXT列有一个字符集,并且根据字符集的校对规则对值进行排序和比较;
mysql8 客户端
用软件登陆的Mysql8数据库时,报错:Authentication plugin ‘caching_sha2_password’ cannot be loaded
出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password。
解决问题方法有两种,一种是升级navicat驱动(博主用的是navicat是19年装的了,其他软件同理),另一种是把mysql用户登录密码加密规则还原成mysql_native_password。
查询数据库大小 & 表更新时间
use information_schema;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='database_name';
use information_schema;
select table_name,update_time from TABLES
where table_schema='data_center';
UNSIGNED
mysql中的int类型默认为有符号类型的(signed),为字段设置无符号约束的关键字是UNSIGNED,其作用是规定此列存储的数据不为负数。
修改用户密码&权限
alter user 'root'@'%' identified by 'xxxxx';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxxx' WITH GRANT OPTION;
flush privileges;
设置最大连接数
show variables like 'max_connections';
set global max_connections=1000;
从其他表更新字段
update a inner join b on a.bid=b.id set a.x=b.x,a.y=b.y ;