MySql数据库日常设计和使用需要注意的几点总结

database

a，UTF-8的字符集是⼀一个汉字3个字节:varchar(255) UTF-8 255*3=765字节

b，禁⽌在表中建立预留字段

c，尽量避免加⼊外键约束，因为外键写入的时候会降低存储效率，但要给这些关联字段加索引

2，索引设计方面：

a，单张表索引数量建议不不超过5个，如果列列多可以适当增加

b，每个innodb表都必须有一个主键，innoDB表就是一个索引组织表，实际存储顺序只能有一种，innoDB是按照主键进⾏存放的

c，不能使⽤更新频繁的列和联合索引做主键，主键不断变，数据的存放顺序就会不断变化

d，不要使⽤UUID、MD5、HASH等做主键，不能保证这些值是按顺序增长的。如果⽣成较⼩的字符串就会导致不断变化数据存储的位置，影响I/O性能

e，避免建⽴立冗余和重复索引

f，对于频繁查询的数据列列，优先考虑使⽤用覆盖索引

3，数据库SQL开发方面：

a，在程序中使用PrepareStatement，#{}。既可以降低词法和语法分析器的重复执行，还能防止SQL注入

b，合理充分的利用表上的索引，使用left join或not exists来优化not in

c，连接不同的数据库使用不同的账号，禁止跨库操作。

d，禁止使用select *，不能使用覆盖索引。

e，避免使用自查询，可以把自查询优化为join操作。

因为自查询是结果集，无法使用索引。另外自查询会产生临时表操作，如果查询量大则会严重影响效率。

f，避免使用join关联太多表。大查询拆成小查询，利用程序来做关联和合并。可以适当的进行表数据的冗余设计。

g，禁止用order by rand（），同样使用程序来完成。

h，where中不要对列进行函数计数，因为无法使用索引

i，UNION ALL 和UNION，如果数据明显不重复，就使用UNION ALL，UNION会增加一次去重复操作。

4，数据库操作行为方面：

a，对大表结构对操作会导致锁表，使用pt-online-schema-change (PERCONA)

原理：创建新表结构->复制旧表数据到新表->在原表上加入触发器确保数据同步->所有操作进行完毕后对原表进入一个很短暂的时间锁 ->把原表名修改，再更新新表名->删除原表

b，数据库出现问题后很多时候无需重启数据库，重启也不能解决实际问题，还是留有隐患。此时需要查找正在执行的语句id，kill掉，即可起死回生，再分析该语句产生问题的原因，解决问题。

mysql> show processlist;
mysql> kill pid；