MySQL使用总结

使用规则

• 不在数据库做计算，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 ;