mysql

[499689317]

mysql注意事项

---

1. select语句避免使用select * from table全字段查询，当数据量非常大，字段非常多时，无法预估当前的查询性能(性能必然会很差)
2. select语句应与limit语句一起使用，限制当前查询的行数，避免一次查询大量的数据。
3. where子句中避免在比较符>,<右侧出现运算表达式或函数，会导致当前查询全表扫描

   select name from student where score + 1 > 80
   改为
   select name from student where score > 80 + 1

4. where条件中避免使用or字句，会导致当前查询全表扫描

   select name from student where id = 1 or id = 2
   改为
   select name from student where id = 1 union select name from student where id = 2

5. 不能将货币存储为小数，可以将货币转为以分为单位存储为整数
6. 使用utf8mb4字会集，可以兼容类似表情包相关的字符
7. mysql数据类型分为数字类型，时间类型，字符串类型

   /*
   常用数字类型
   tinyint，占1字节，无符号范围0-255
   smallint，占2字节，无符号范围0-2^16-1
   middleint，占3字节，无符号范围0-2^24-1
   int，占4字节，无符号范围0-2^32-1
   bigint，占8字节，无符号范围0-2^64-1
   应用场景尽可能细分数据类型
   主键id定义为bigint(20)注意：括号内代表的是数字个数，不是字节数
   人的年龄定义为tinyint
   动物的年龄定义为smallint
   常用时间类型
   常用字符串类型
   char，定长字符串，长度0-255
   varchar，可变字符串，长度0-65535
   text，文本，长度0-65535
   */

   ---

[499689317]

---

8. 负向条件查询会全表扫描

   // 使用!=, not in, not exist等负向条件
   select name from student where id != 1 and id != 2

9. 前导模糊查询会全表扫描

   // _表示匹配单个任意字符，%表示匹配任意个任意字符
   select id from student where name like '%小明'
   改为
   select id from student where name like '王小%'

10. B-Tree索引的时间复杂度是O(logn)，Hash索引的时间复杂度是O(1)。

    /*
    计算机硬盘的最小存储单元为扇区，一个扇区占512字节。操作系统中文件系统最小存储单元为块，一个块占用4KB，Mysql中的文件存储系统最小单元为页，一页占用16KB。
    如：一个文本文件写入一个字符，文件大小为1个字节，但在计算机磁盘中要占用4KB，相当于8个扇区。
    假设一个Mysql表中一条记录占用1KB情况下，一页可存储16条记录，B+树中非叶子节点存储的是键与指针，叶子节点存储数据。如果主键为bigint类型(占用8字节)，Mysql中指针占用6字节，则可知一个键与指针占用14字节。那么一页可存储16384/14=1170个键与指针。当树高度为2时，可存储1170*16=18720条记录，当树高为3时可存储1170*1170*16=21902400条记录，一般不会让树高度超过3层。即查询一次数据库只需要1-3次io操作。
    */

    如果表数据以单条记录查询为主，建议创建Hash索引

11. 如果查询的结果只有一条记录，使用limit 1主动让数据库查询到结果后停止游标向后移动，可以优化此次查询

    ---

[499689317]

---

12. 高并发下，应该将计算转移到业务层，释放数据库cpu，数据库仅只是擅长存储与索引。
13. 字段定义为NOT NULL，并提供相应的默认值

    /*
    NULL的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
    NULL这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
    NULL值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识
    */

14. 避免多表join查询，在join查询时要确保关联字段有索引。
15. 创建复合索引时，区分度越高越靠前创建。
16. 使用唯一索引可确保业务层不需要先查后插就能保证记录唯一性。
17. 使用ISNULL()函数来判断字段是否为NULL。
18. 使用外键会引发级联操作，应尽量避免使用外键。

    ---