MySQL数据库优化和数据表设置注意事项

最新推荐文章于 2023-03-05 13:48:23 发布
最新推荐文章于 2023-03-05 13:48:23 发布
阅读量653 收藏 2
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zdklhh/article/details/100638836
版权

1. 优化一览图

2. 优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

 

2.1 软优化

2.1.1 查询语句优化

 

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
2.例:

DESC SELECT * FROM `user`

例:

显示:

 

其中会显示索引和查询数据读取数据条数等信息.

 

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销，子查询的结果集也无法使用索引，而连接查询不会创建临时表,因此效率比嵌套子查询高.

 

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的注意事项:

  • LIKE关键字匹配'%'开头的字符串,不会使用索引.
  • OR关键字的两个字段必须都是用了索引,该查询才会使用索引.
  • 使用多列索引必须满足最左匹配.
  • WHERE 从句中禁止对列进行函数转换和计算,会导致无法使用索引.

 

 

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

 

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

 

2.1.6 增加冗余字段

类似于创建中间表,增加冗余也是为了减少连接查询.

 

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

 

分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

Op:表示执行的操作.
Msg_type:信息类型,有status,info,note,warning,error.
Msg_text:显示信息.

检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:

QUICK:不扫描行,不检查错误的连接.

FAST:只检查没有正确关闭的表.

CHANGED:只检查上次检查后被更改的表和没被正确关闭的表.

MEDIUM:扫描行,以验证被删除的连接是有效的,也可以计算各行关键字校验和.

EXTENDED:最全面的的检查,对每行关键字全面查找.

优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

 

2.2 硬优化

2.2.1 硬件三件套

 

1、配置多核心和频率高的cpu,多核心可以执行多个线程.

2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

 

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

 

key_buffer_size:索引缓冲区大小

table_cache:能同时打开表的个数

query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.

sort_buffer_size:排序缓冲区

传送门:更多参数

 

2.2.3 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。

 

但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。

 

所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。

 

所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

MySQL高性能优化规范建议

数据库命令规范

  • 所有数据库对象名称必须使用小写字母并用下划线分割

  • 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）

  • 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符

  • 临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀

  • 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

数据库基本设计规范

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操作很耗时。

通常存储于文件服务器，数据库只存储文件地址信息

9. 禁止在线上做数据库压力测试

10. 禁止从开发环境，测试环境直接连接生产环境数据库

数据库字段设计规范

1. 优先选择符合存储需要的最小的数据类型

原因：

列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多，索引的性能也就越差。

方法：

1、将字符串转换成数字类型存储，如：将IP地址转换成整形数据

mysql提供了两个方法来处理ip地址

  • inet_aton 把ip转为无符号整型(4-8位)

  • inet_ntoa 把整型的ip转为地址

插入数据前，先用inet_aton把ip地址转为整型，可以节省空间，显示数据时，使用inet_ntoa把整型的ip地址转为地址显示即可。

2、对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型来存储

原因：

无符号相对于有符号可以多出一倍的存储空间

SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295

VARCHAR(N)中的N代表的是字符数，而不是字节数，使用UTF8存储255个汉字 Varchar(255)=765个字节。过大的长度会消耗更多的内存。

2. 避免使用TEXT、BLOB数据类型，最常见的TEXT类型可以存储64k的数据

1、建议把BLOB或是TEXT列分离到单独的扩展表中

Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型。

如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。

2、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更大的整型数据

索引设计规范

1. 限制每张表上的索引数量，建议单张表索引不超过5个

索引并不是越多越好！索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

因为mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能。

2. 禁止给表中的每一列都建立单独的索引

5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。

3. 每个Innodb表必须有个主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

Innodb是按照主键索引的顺序来组织表的

  • 不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）

  • 不要使用UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）

  • 主键建议使用自增ID值

常见索引列建议

  • 出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列

  • 包含在ORDER BY、GROUP BY、DISTINCT中的字段

  • 并不要将符合1和2中的字段的列都建立一个索引， 通常将1、2中的字段建立联合索引效果更好

  • 多表join的关联列

如何选择索引列的顺序

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

  • 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）

  • 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）

  • 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）

  • 重复索引示例：primary key(id)、index(id)、unique index(id)

  • 冗余索引示例：index(a,b,c)、index(a,b)、index(a)

对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引

覆盖索引的好处：

避免Innodb表进行索引的二次查询

Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。

而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了IO操作，提升了查询效率。

可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

索引SET规范

尽量避免使用外键约束

  • 不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引

  • 外键可用于保证数据的参照完整性，但建议在业务端实现

  • 外键会影响父表和子表的写操作从而降低性能

数据库SQL开发规范

1. 建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的SQL注入的问题。

只传参数，比传递SQL语句更高效。

相同语句可以一次解析，多次使用，提高处理效率。

2. 避免数据类型的隐式转换

隐式转换会导致索引失效如:

select name,phone from customer where id = '111';

3. 充分利用表上已经存在的索引

避免使用双%号的查询条件。如：a like '%123%'，（如果无前置%,只有后置%，是可以用到列上的索引的）

一个SQL只能利用到复合索引中的一列进行范围查询。如：有 a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到。

在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧，使用left join 或 not exists 来优化not in 操作，因为not in 也通常会使用索引失效。

4. 数据库设计时，应该要对以后扩展进行考虑

5. 程序连接不同的数据库使用不同的账号，进制跨库查询

  • 为数据库迁移和分库分表留出余地

  • 降低业务耦合度

  • 避免权限过大而产生的安全风险

6. 禁止使用SELECT * 必须使用SELECT <字段列表> 查询

原因：

  • 消耗更多的CPU和IO以网络带宽资源

  • 无法使用覆盖索引

  • 可减少表结构变更带来的影响

7. 禁止使用不含字段列表的INSERT语句

如：

insert into values ('a','b','c');

应使用：

insert into t(c1,c2,c3) values ('a','b','c');

8. 避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

9. 避免使用JOIN关联太多的表

对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。

在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说，会产生临时表操作，影响查询效率，Mysql最多允许关联61个表，建议不超过5个。

10. 减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

11. 对应同一列进行or判断时，使用in代替or

in 的值不要超过500个，in 操作可以更有效的利用索引，or大多数情况下很少能利用到索引。

12. 禁止使用order by rand() 进行随机排序

order by rand()会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源。

推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

13. WHERE从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

不推荐：

where date(create_time)='20190101'

推荐：

where create_time >= '20190101' and create_time < '20190102'

14. 在明显不会有重复值时使用UNION ALL 而不是UNION

  • UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作

  • UNION ALL 不会再对结果集进行去重操作

15. 拆分复杂的大SQL为多个小SQL

  • 大SQL逻辑上比较复杂，需要占用大量CPU进行计算的SQL

  • MySQL中，一个SQL只能使用一个CPU进行计算

  • SQL拆分后可以通过并行执行来提高处理效率

数据库操作行为规范

超100万行的批量写（UPDATE、DELETE、INSERT）操作，要分批多次进行操作

1、大批量操作可能会造成严重的主从延迟

主从环境中,大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，
而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况

2、binlog日志为row格式时会产生大量的日志

大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因

3、避免产生大事务操作

大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对MySQL的性能产生非常大的影响。

特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批

对于大表使用pt-online-schema-change修改表结构

  • 避免大表修改产生的主从延迟

  • 避免在对表字段进行修改时进行锁表

对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。

pt-online-schema-change它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。把原来一个DDL操作，分解成多个小的批次进行。

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

  • 当达到最大连接数限制时，还运行1个有super权限的用户连接

  • super权限只能留给DBA处理问题的账号使用

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

  • 程序使用数据库账号只能在一个DB下使用，不准跨库

程序使用的账号原则上不准有drop权限

MySQL 表设计注意事项

问题1:为什么一定要设一个主键？
回答:因为你不设主键的情况下，innodb也会帮你生成一个隐藏列，作为自增主键。所以啦，反正都要生成一个主键，那你还不如自己指定一个主键，在有些情况下，就能显式的用上主键索引，提高查询效率！

问题2:主键是用自增还是UUID?
回答:肯定答自增啊。innodb 中的主键是聚簇索引。如果主键是自增的，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果不是自增主键，那么可能会在中间插入，就会引发页的分裂，产生很多表碎片！。
上面那句话看不懂没事，大白话一句就是:用自增插入性能好！


另外，附一个测试表给你们，表名带uuid的就是用uuid作为主键。大家看一下就知道性能差距了:


如上图所示，当主键是UUID的时候，插入时间更长，而且占用空间更大！

额，大家千万不要忘了，当你回答自增主键后，想一下《自增主键用完该怎么办？》

ps：这个问题，你要是能把UUID讲出合理的理由也行。

问题3:主键为什么不推荐有业务含义?
回答:有如下两个原因

  • (1)因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义，那么主键就有可能发生变更。主键一旦发生变更，该数据在磁盘上的存储位置就会发生变更，有可能会引发页分裂，产生空间碎片。

  • (2)带有业务含义的主键，不一定是顺序自增的。那么就会导致数据的插入顺序，并不能保证后面插入数据的主键一定比前面的数据大。如果出现了，后面插入数据的主键比前面的小，就有可能引发页分裂，产生空间碎片。

问题4:表示枚举的字段为什么不用enum类型？
回答:在工作中表示枚举的字段，一般用tinyint类型。
那为什么不用enum类型呢？下面两个原因
(1)ENUM类型的ORDER BY操作效率低，需要额外操作
(2)如果枚举值是数值，有陷阱
举个例子，表结构如下

CREATE TABLE test (foobar ENUM('0', '1', '2'));

此时，你执行语句

mysql> INSERT INTO test VALUES (1);

查询出的结果为

foobar
0

就产生了一个坑爹的结果。
插入语句应该像下面这么写，插入的才是1

mysql> INSERT INTO test VALUES (`1`);

问题5:货币字段用什么类型?
回答:如果货币单位是分，可以用Int类型。如果坚持用元，用Decimal。
千万不要答float和double，因为float和double是以二进制存储的，所以有一定的误差。
打个比方，你建一个列如下

CREATE TABLE `t` (
  `price` float(10,2) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后insert给price列一个数据为1234567.23，你会发现显示出来的数据变为1234567.25，精度失准！

问题6:时间字段用什么类型?
回答:此题无固定答案，应结合自己项目背景来答！把理由讲清楚就行！
(1)varchar，如果用varchar类型来存时间，优点在于显示直观。但是坑的地方也是挺多的。比如，插入的数据没有校验，你可能某天就发现一条数据为2013111的数据，请问这是代表2013年1月11日，还是2013年11月1日？
其次，做时间比较运算，你需要用STR_TO_DATE等函数将其转化为时间类型，你会发现这么写是无法命中索引的。数据量一大，是个坑！

(2)timestamp，该类型是四个字节的整数，它能表示的时间范围为1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以后的时间，是无法用timestamp类型存储的。
但是它有一个优势，timestamp类型是带有时区信息的。一旦你系统中的时区发生改变，例如你修改了时区

SET TIME_ZONE = "america/new_york";

你会发现，项目中的该字段的值自己会发生变更。这个特性用来做一些国际化大项目，跨时区的应用时，特别注意！

(3)datetime，datetime储存占用8个字节，它存储的时间范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。显然，存储时间范围更大。但是它坑的地方在于，他存储的是时间绝对值，不带有时区信息。如果你改变数据库的时区，该项的值不会自己发生变更！

(4)bigint，也是8个字节，自己维护一个时间戳，表示范围比timestamp大多了，就是要自己维护，不大方便。

问题7:为什么不直接存储图片、音频、视频等大容量内容?
回答:我们在实际应用中，都是用HDFS来存储文件。然后mysql中，只存文件的存放路径。mysql中有两个字段类型被用来设计存放大容量文件，也就是text和blob类型。但是，我们在生产中，基本不用这两个类型！
主要原因有如下两点

  • (1)Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。导致查询效率缓慢

  • (2)binlog内容太多。因为你数据内容比较大，就会造成binlog内容比较多。大家也知道，主从同步是靠binlog进行同步，binlog太大了，就会导致主从同步效率问题！

因此，不推荐使用text和blob类型！

问题8:字段为什么要定义为NOT NULL?
回答:OK，这问题从两个角度来答
(1)索引性能不好

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。                                                                                 —— 出自《高性能mysql第二版》

(2)查询会出现一些不可预料的结果
这里举一个例子，大家就懂了。假设，表结构如下

create table table_2 (
     `id` INT (11) NOT NULL,
    name varchar(20) NOT NULL
)

表数据是这样的

idname
1孤独烟
3null
5肥朝
7null

你执行语句

select count(name) from table_2;

你会发现结果为2，但是实际上是有四条数据的！类似的查询问题，其实有很多，不一一列举。
记住，因为null列的存在，会出现很多出人意料的结果，从而浪费开发时间去排查Bug.

阿宝PHP
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
【数据库系统】SQL修改的注意事项
星拱北辰的博客
09-26 522
SQL修改的注意事项
SQL优化系列 - MySQL表设计时要注意什么？
Totoro1745的博客
05-31 357
前言 今天是5月的最后一天了，一边听着五月天的线上演唱会，一边整理下学过SQL相关的内容，之前有篇是针对SQL的知识点：https://blog.csdn.net/Totoro1745/article/details/106036161，接下来的优化系列也会不断的进行更新~ MySQL表设计时要注意什么？ 1.为什么一定要设一个主键？ 在不设主键的情况下，innodb也会生成一个隐藏列，作为自增主键。自己指定一个主键，在有些情况下，就能显式的用上主键索引，提高查询效率~ 2.主键为什么最好用自增呢? inn
mysql优化注意点
weixin_34124939的博客
04-29 154
通常和zerofill一起使用，比如int(3)，存入1，则自动填充为001。并不是存入的长度char(n)，varchar(n)中的n是最大长度。外键索引一般不用，可以通过业务逻辑来实现。全文索引一般也不用，直接上全文搜索引擎：sphinx、elasticsearch表索引的创建原则：最适合素银的列是出现在where子句中的列，或连接子句中的列而不是出现在select关键字...
mysql设计与查询的优化
whyhe216的博客
07-14 83
mysql 1 尽量保持查询简单且只返回必须的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免select * 以及加上limit 限制的原因 2 用多个小表代替一个大表 批量插入代替循环单条插入 合理控制缓存空间大小，一般来说大小设置为几十兆比较合适 可以通过sql_cache 和 sql_no_cache来控制是否x需要进行缓存 3 选择数据类型只要遵循小而简单的原则，越小的数据k类型通常会更快，占用更少的磁盘，内存，处理时需要的CPU周期也更少 4 指定宽度 对整数类型无效
MySQL调优之大表处理探索那些事
小小默：进无止境
10-24 984
当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下： * 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。； * 优化SQL和索引 * 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存如Redis； * 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读； * MySQL自带的分区表。无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要
MySQL数据库表分区注意事项大全【推荐】
09-09
MySQL数据库表分区是一种优化策略，用于提高大数据量表的查询性能和管理效率。表分区将一个大表逻辑上划分为多个较小的部分，每个部分存储在不同的物理位置，从而分散I/O负载并加速查询处理。然而，正确地实施分区...
MySQL数据库安全设置与注意事项小结
12-15
当你第一次在机器上安装MySQL时，mysql数据库中的授权表是这样初始化的： 你可以从本地主机（localhost）上以root连接而不指定口令。root用户拥有所有权限（包括管理权限） 并可做任何事情。（顺便说明，MySQL超级...
MySQL数据库优化技术之配置技巧总结
09-10
综上所述，MySQL数据库优化是一个综合性的过程，需要从多个维度进行考虑，既要考虑硬件升级，也要关注操作系统配置和数据库配置的细节，同时不能忽视应用程序的优化。通过这些方法，可以有效地提升MySQL的性能，满足...
Mysql 数据库常用备份方法和注意事项
12-15
Mysql数据库备份的常用3种方法： 1、直接拷贝(cp、tar,gzip,cpio) 2、mysqldump 3、mysqlhotcopy 1.使用直接拷贝数据库备份 典型的如cp、tar或cpio实用程序。 当你使用直接备份方法时，必须保证表不在被使用。如果...
最详细Sql语句优化大汇总 面试必问 我就不信你还不会Sql优化
m0_62314761的博客
03-05 1501
若要了解SQL语句的优化，可以Sql语句的语法、Sql 语句的解析、Sql语句的执行顺序等入手。查询尽量不要Select * 而是返回真正需要的字段 无用字段增加网络 消耗，消耗更多的 CPU 和 IO 以网络带宽资源 如果*中的数据范围过大，可能会不走索引，降低性能 增加了回表的可能性（会通过辅助索引去过滤不必要的信息，增加了一次IO操作）
MySQL优化书写高质量sql语句
热门推荐
Java小皮孩
03-23 1万+
1. 查询SQL尽量不要使用全查 select *，而是 select + 具体字段。 反例： select * from student; 正例： select id，name, age from student; 理由： 只取需要的字段，可以节省资源、减少CPU和IO以及网络开销。 select * 进行查询时，无法使用到覆盖索引，就会造成回表查询。 使用具体字段可以减少表结构变动带来的影响。 2. 使用预编译语句进行数据库操作 理由： 预编译语句可以重复使用计划，减少SQL编译所需要
MySQL数据库设计规范
weixin_51724765的博客
12-02 517
数据库规范设计
1、 数据库开发规范
fxbar的博客
06-03 1万+
第1章 数据库开发规范的制定
mysql安装；数据库和表的配置
z07121的博客
04-28 135
mysql的安装，yum安装 关闭CentOS8中MYSQL默认的mysql仓库 [root@localhost ~]# sudo dnf remove @mysql CentOS Stream 8 - AppStream 4.5 MB/s | 6.7 MB 00:01 CentOS Stream 8 - BaseOS 1.6 MB/s | 2.3 MB 00:01
数据库操作的注意事项
七月流星
09-27 4407
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库. 备注、
MySQL基础教程14-Mysql插入中文数据报错的原因和解决办法
Anthony_tester的博客
12-21 4832
有时候，我们用中文系统连接mysql数据库时候，插入或者更新的数据是中文的时候，可能会出现报错，这篇就来分析下为什么有这个报错。 我们来新增一个中文数据，结果遇到了报错。 问题分析： 报错提示：不正确的字符值 原因：       上面报错的\xDD\xFE这六个是十六进制的表示，我们知道计算机只识别二进制的数据，但是人类能识别很多符号，所以我们需要把符号都想办法给转换成二进制。不
工作日志——数据库建表注意事项
liucunguang2的专栏
03-20 1120
每个数据库存在很多用户，不同的用户有不同的权限。作为工作中的一般用户，需要给他的只有SELECT 权限就好。 例如：我需要以center_admin的用户建一张SYS_TABLEDESC表 1、新建表语句 create table SYS_TABLEDESC (   tablename   VARCHAR2(64) PRIMARY KEY,   tabledesc   VARCHA
mysql大事务导致系统变慢_MySQL因大事务导致的Insert慢实例分析
weixin_35160622的博客
01-21 1167
【问题】INSERT语句是最常见的SQL语句之一，最近有台MySQL服务器不定时的会出现并发线程的告警，从记录信息来看，有大量insert的慢查询，执行几十秒，等待flushing log，状态query end【初步分析】从等待资源来看，大部分时间消耗在了innodb_log_file阶段，怀疑可能是磁盘问题导致，经过排查没有发现服务器本身存在硬件问题后面开启线程上升时pstack的自动采集，定...
100道MySQL数据库经典面试题
最新发布
06-12
MySQL索引是提高查询...综上所述，正确合理地使用MySQL索引可以显著提高查询效率，但需要注意避免索引失效的情况，同时根据场景选择合适的索引策略，并定期进行优化和维护。希望以上内容能对使用MySQL索引有所帮助。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值