MySQL设计规范及性能优化

àì夳堔傛蜴生んèń 2022-12-05 12:13 179阅读 0赞

### 文章目录 ###

*  一、数据库命令规范
 *  二、数据库基本设计规范
 *  三、数据库字段设计规范
 *  四、索引设计规范
 *   *  常见索引列建议
     *  如何选择索引列的顺序
     *  索引SET规范
 *  五、数据库SQL开发规范
 *  六、数据库操作行为规范
 *   *  超100万行的批量更新操作，要分批多次进行操作
     *  禁止为程序使用的账号赋予super权限
     *  对于程序连接数据库账号，遵循权限最小原则

# 一、数据库命令规范 #

1.  所有数据库对象名称必须使用小写字母并用下划线分割。
2.  所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）。
3.  数据库对象的命名要能做到见名识意，并且最好不要超过32个字符。
4.  临时库表必须以tmp\_为前缀并以日期为后缀，备份表必须以bak\_为前缀并以日期(时间戳)为后缀。
5.  所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。

# 二、数据库基本设计规范 #

1.  所有表必须使用Innodb存储引擎  
    没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb），Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。
2.  数据库和表的字符集统一使用UTF8  
    兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储emoji表情的需要，字符集需要采用utf8mb4字符集。
3.  所有表和字段都需要添加注释  
    使用comment从句添加表和列的备注，从一开始就进行数据字典的维护。
4.  尽量控制单表数据量的大小，建议控制在500万以内  
    500万并不是Mysql数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题，可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据，该部分能力另开专题讨论）等手段来控制数据量大小。
5.  谨慎使用Mysql分区表  
    分区表在物理上表现为多个文件，在逻辑上表现为一个表，谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据。（该部分能力另开专题讨论）
6.  尽量做到冷热数据分离，减小表的宽度  
    Mysql限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节，减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO），更有效的利用缓存，避免读入无用的冷数据，经常一起使用的列放到一个表中（避免更多的关联操作）。
7.  禁止在表中建立预留字段  
    预留字段的命名很难做到见名识义  
    预留字段无法确认存储的数据类型，所以无法选择合适的类型  
    对预留字段类型的修改，会对表进行锁定
8.  禁止在数据库中存储图片，文件等大的二进制数据  
    通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。  
    通常存储于文件服务器，数据库只存储文件地址信息

# 三、数据库字段设计规范 #

1.  优先选择符合存储需要的最小的数据类型  
    原因是：列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多，索引的性能也就越差。  
    方法：  
    将字符串转换成数字类型存储，如：将IP地址转换成整形数据，mysql提供了两个方法来处理ip地址  
    inet\_aton 把ip转为无符号整型(4-8位)  
    inet\_ntoa 把整型的ip转为地址  
    插入数据前，先用inet\_aton把ip地址转为整型，可以节省空间；显示数据时，使用inet\_ntoa把整型的ip地址转为地址显示即可。  
    对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型来存储，因为：无符号相对于有符号可以多出一倍的存储空间：  
    SIGNED INT -2147483648~2147483647  
    UNSIGNED INT 0~4294967295。  
    VARCHAR(N)中的N代表的是字符数，而不是字节数，使用UTF8存储255个汉字 Varchar(255)=765个字节，过大的长度会消耗更多的内存。  
    尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型。相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销。
2.  避免使用TEXT、BLOB数据类型，最常见的TEXT类型可以存储64k的数据  
    建议把BLOB或是TEXT列分离到单独的扩展表中。  
    Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型。  
    如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select \* 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。  
    TEXT或BLOB类型只能使用前缀索引，因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的。
3.  避免使用ENUM类型  
    修改ENUM值需要使用ALTER语句。  
    ENUM类型的ORDER BY操作效率低，需要额外操作。  
    禁止使用数值作为ENUM的枚举值。
4.  尽可能把所有列定义为NOT NULL  
    原因：索引NULL列需要额外的空间来保存，所以要占用更多的空间  
    进行比较和计算时要对NULL值做特别的处理
5.  使用TIMESTAMP（4个字节）或DATETIME类型（8个字节）存储时间  
    TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。  
    TIMESTAMP 占用4字节和INT相同，但比INT可读性高。  
    超出TIMESTAMP取值范围的使用DATETIME类型存储。  
    经常会有人用字符串存储日期型的数据（不正确的做法）  
    缺点1：无法用日期函数进行计算和比较  
    缺点2：用字符串存储日期要占用更多的空间。
6.  同财务相关的金额类数据必须使用decimal类型  
    非精准浮点：float,double  
    精准浮点：decimal  
    Decimal类型为精准浮点数，在计算时不会丢失精度  
    占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节  
    可用于存储比bigint更大的整型数据

# 四、索引设计规范 #

索引（Index）是帮助MySQL高效获取数据的数据结构。MySQL的数据存储结构 b+树  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1amluZ2dlbg_size_16_color_FFFFFF_t_70_pic_center]  
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项。

1.  非聚簇索引和聚簇索引  
    聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。  
    InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。  
    MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。  
    InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。非聚簇索引，叶子节点上的data是主键(所以聚簇索引的key，不能过长)。为什么存放的主键，而不是记录所在地址呢，理由相当简单，因为记录所在地址并不能保证一定不会变，但主键可以保证。
2.  每个Innodb表必须有个主键，并且自增  
    Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的，每个表都可以有多个索引，但是表的存储顺序只能有一种，Innodb是按照主键索引的顺序来组织表的。  
    不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）。  
    不要使用UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）。  
    主键建议使用自增ID值。为什么主键通常建议使用自增id呢？  
    聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
3.  限制每张表上的索引数量，建议单张表索引不超过5个  
    索引并不是越多越好！索引可以提高效率同样可以降低效率；索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。  
    因为mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能 。
4.  禁止给表中的每一列都建立单独的索引  
    5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。
5.  索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段  
    因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

## 常见索引列建议 ##

1.  出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列。
2.  包含在ORDER BY、GROUP BY中的字段

## 如何选择索引列的顺序 ##

建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

1.  最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
2.  区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）。,区分度的公式是count(distinct col)/count(\*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
3.  尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
4.  尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）。
5.  使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）。
6.  避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）  
    重复索引示例：primary key(id)、index(id)、unique index(id)  
    冗余索引示例：index(a,b,c)、index(a,b)、index(a)
7.  对于频繁的查询优先考虑使用覆盖索引  
    覆盖索引：就是查询的结果字段，包含在索引字段中（where,select,ordery by,group by包含的字段）。  
    覆盖索引的好处:  
    避免Innodb表进行索引的二次查询  
    Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据，而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了IO操作，提升了查询效率。
8.  可以把随机IO变成顺序IO加快查询效率  
    由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

## 索引SET规范 ##

1.  尽量避免使用外键约束  
    不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引  
    外键可用于保证数据的参照完整性，但建议在业务端实现  
    外键会影响父表和子表的写操作从而降低性能

# 五、数据库SQL开发规范 #

1.  禁止使用SELECT \* 必须使用SELECT <字段列表> 查询  
    原因：  
    消耗更多的CPU和IO以网络带宽资源  
    无法使用覆盖索引  
    可减少表结构变更带来的影响
2.  应尽量避免在where子句中使用or来连接条件  
    新建一个user表，它有一个普通索引userId，表结构如下：

CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userId` int(11) NOT NULL,
      `age` int(11) NOT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_userId` (`userId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假设现在需要查询userid为1或者年龄为18岁的用户，很容易有以下sql

--反例
    select userid,age,name from user where userid=1 or age =18
    --正例
    select userid,age,name from user where userid=1 
    union all 
    select userid,age,name from user where age = 18

使用or可能会使索引失效，从而全表扫描。对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并 如果它一开始就走全表扫描，直接一遍扫描就完事。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。

1.  优化limit分页  
    我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下。

--查询第10000条以后的10条数据
    --反例
    select id，name from employee limit 10000，10
    --正例
    //方案一 ：返回上次查询的最大记录(偏移量)
    select id，name from employee where id>10000 limit 10
    //方案二：order by + 索引
    select id，name from employee order by id  limit 10000，10

理由：当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。  
如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。  
方案二使用order by+索引，也是可以提高查询效率的。

1.  避免使用双%号，进行模糊查询  
    避免使用双%号的查询条件。如 a like ‘%123%’，如果无前置%,只有后置%，是可以用到列上的索引的。
2.  建议使用预编译语句进行数据库操作  
    预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的SQL注入的问题。  
    只传参数，比传递SQL语句更高效。  
    相同语句可以一次解析，多次使用，提高处理效率。
3.  避免数据类型的隐式转换  
    隐式转换会导致索引失效，如: select name,phone from customer where id = ‘111’。
4.  WHERE从句中禁止对列进行函数转换和计算  
    对列进行函数转换或计算时会导致无法使用索引

--反例：
    where date(create_time)='20190101'
    --正例：
    where create_time >= '20190101' and create_time < '20190102'

1.  应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫描

--反例
    select * from user where age-1 =10；
    --正例
    select * from user where age =11；

1.  Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

> Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集  
> left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。  
> right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

都满足SQL需求的前提下，推荐优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一点。同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。

--反例
    select * from tab1 t1 left join tab2 t2  on t1.size = t2.size where t1.id>2000;
    --正例
    select * from (select * from tab1 where id >2000) t1 left join tab2 t2 on t1.size = t2.size;

1.  使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。  
    当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。  
    造成索引失效的原因：  
    前边的任意一个索引没有参与查询，后边的全部不生效。  
    前边的任意一个索引字段参与的是范围查询（> < between like），后面的不会生效。  
    断点跟索引字字段在SQL语句中的位置前后无关，只与是否存在有关。

CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userId` int(11) NOT NULL,
      `age` int(11) DEFAULT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_userid_age` (`userId`,`age`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
    --反例
    select * from user where age = 10;
    --正例
    --符合最左匹配原则
    select * from user where userid=10 and age =10；
    --符合最左匹配原则
    select * from user where userid =10;

1.  如果插入数据过多，考虑批量插入

for(User u :list){ 
     INSERT into user(name,age) values(#name#,#age#)   
    }

//一次500批量插入，分批进行
    insert into user(name,age) values
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.name},#{item.age})
    </foreach>

1.  在适当的时候，使用覆盖索引  
    覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。

-- 反例：like模糊查询，不走索引了
    select * from user where userid like '%123%'
    -- 正例：查询的结果字段，都为索引字段
    select id,userId,age from user where userId like '%123%'

1.  慎用distinct关键字  
    distinct 关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。
2.  where子句中考虑使用默认值代替null  
    并不是说使用了is null 或者 is not null 就一定不走索引了，这个跟mysql版本以及查询成本都有关。.如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件!=,is null,is not null经常被认为让索引失效。如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

--反例：
    select * from user where age is not null;
    --设置0为默认值
    select * from user where age>0;

1.  不要有超过5个以上的表连接  
    对于Mysql来说，是存在关联缓存的，缓存的大小可以由join\_buffer\_size参数进行设置，在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。  
    如果程序中大量的使用了多表关联的操作，同时join\_buffer\_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性，同时对于关联操作来说，会产生临时表操作，影响查询效率。  
    Mysql最多允许关联61个表，建议不超过5个。
2.  exist & in的合理利用  
    假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下SQL:

select * from A where deptId in (select deptId from B);

等价于：  
select deptId from B，先查询部门表B；  
select \* from A where A.deptId = B.deptId，再由部门deptId，查询A的员工。

select * from A where exists (select 1 from B where A.deptId = B.deptId);

等价于：  
select \* from A, 先查询A的员工；  
select \* from B where A.deptId = B.deptId,在查询B表部门.

in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。**小表驱动大表原则：先执行查询速度快的表，这张表最好还有索引**  
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。  
not in 和not exists：如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

1.  尽量用 union all 替换 union  
    如果检索结果中不会有重复的记录，推荐union all 替换 union。如果使用union，不管检索结果有没有重复，都会尝试进行合并,去重，然后在输出最终结果前进行排序。
2.  为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。

--反例：
    select job，avg（salary） from employee  group by job having job ='president' 
    or job = 'managent'
    --正例：
    select job，avg（salary） from employee where job ='president' 
    or job = 'managent' group by job；

1.  使用explain 分析你SQL的计划  
    日常开发写SQL的时候，尽量养成一个习惯吧。用explain分析一下你写的SQL，尤其是走不走索引这一块。
2.  禁止使用不含字段列表的INSERT语句  
    如： insert into values (‘a’,‘b’,‘c’);  
    应使用 insert into t(c1,c2,c3) values (‘a’,‘b’,‘c’);
3.  避免使用子查询，可以把子查询优化为join操作  
    通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。子查询性能差的原因：  
    子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。  
    特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。  
    由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。
4.  对应同一列进行or判断时，使用in代替or  
    in 的值不要超过500个。  
    in 操作可以更有效的利用索引，or大多数情况下很少能利用到索引。
5.  禁止使用order by rand() 进行随机排序  
    会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源，推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。
6.  拆分复杂的大SQL为多个小SQL  
    大SQL:逻辑上比较复杂，需要占用大量CPU进行计算的SQL  
    MySQL 一个SQL只能使用一个CPU进行计算  
    SQL拆分后可以通过并行执行来提高处理效率

# 六、数据库操作行为规范 #

## 超100万行的批量更新操作，要分批多次进行操作 ##

1.  大批量操作可能会造成严重的主从延迟  
    主从环境中,大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，  
    而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况
2.  binlog日志为row格式时会产生大量的日志  
    大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多，  
    产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因
3.  避免产生大事务操作  
    大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对MySQL的性能产生非常大的影响。  
    特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批。

## 禁止为程序使用的账号赋予super权限 ##

当达到最大连接数限制时，还运行1个有super权限的用户连接  
super权限只能留给DBA处理问题的账号使用

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

1.  程序使用数据库账号只能在一个DB下使用，不准跨库。
2.  程序使用的账号原则上不准有drop权限
3.  禁止在线上做数据库压力测试
4.  禁止从开发环境，测试环境直接连接生成环境数据库
5.  程序连接不同的数据库使用不同的账号，禁止跨库查询  
    为数据库迁移和分库分表留出余地  
    降低业务耦合度  
    避免权限过大而产生的安全风险

整理自：  
[Mysql高性能优化规范建议][Mysql]  
[后端程序员必备：书写高质量SQL的30条建议][SQL_30]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1amluZ2dlbg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/a576cd1876944516bb21d9d52cf4c970.png
[Mysql]: https://www.cnblogs.com/huchong/p/10219318.html
[SQL_30]: https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247486461&idx=1&sn=60a22279196d084cc398936fe3b37772&chksm=cea24436f9d5cd20a4fa0e907590f3e700d7378b3f608d7b33bb52cfb96f503b7ccb65a1deed&token=1987003517&lang=zh_CN#rd