https://blog.boychai.xyz/index.php/tag/mysql/ https://blog.boychai.xyz/index.php/archives/64/ 2023-09-05T04:51:00+00:00 插入数据批量插入数据推荐使用下面语句INSERT INTO 表名(字段1，字段2，......) VALUES (值1，值2，......),(值1，值2，......),(值1，值2，......);如果数据量过于庞大，数据条数大于1000推荐使用文件导入的方式进行插入，方法如下## 登录mysql的时候需要添加--local-infile参数 mysql --local-infile -u root -p ## 登录之后设置local_infile为1,开启从本地加载文件导入数据的开关 set global local_infile=1; ## 使用load命令导入数据 load data local infile '文件' into table `表名` fields terminated by `文件里面的分隔符` lines terminated by '\n';文件里面的内容格式如下1,a,b,c 2,b,c,dPS:主键顺序插入性能高于乱序插入主键优化数据组织方式在InnoDB存储引擎中，表数据都是根据逐渐顺序组织存放的，这种存储方式的表成为索引组织表(index organized table IOT)页分裂页可以为空 ，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果 一行数据多大，会溢出)，根据主键排列。页合并当删除一条记录时，实际上记录并没有被物理删除，只是被标记(flaged)为啥暗处并且他的空间变得允许被其他记录声明使用。当页面中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并已优化空间使用。主键设置原则满足业务要求的情况下,尽量降低主键长度。插入数据的时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。尽量不要使用UUID做主键或者是其他自然主键，如身份证。业务操作时，避免对主键的修改。order by优化Using filesort通过表的索引或全表扫描,读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。Using index通过有序索引顺序扫描直接返回有序数据，这种情况即为using index,不需要额外排序，操作效率更高。优化根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。尽量使用覆盖索引。多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。group by优化分组操作时，可以通过索引来提高效率。分组操作时，索引的使用也是满足最左前缀法则的。limit优化一般分页查询时，通过创建 覆盖索引 能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化。count优化在MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。InnoDB引擎就麻烦了，他执行count(*)的时候，需要把数据一行一行的从引擎里面读出来，让后累计计数。count(主键)InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行累加(主键不可能为null)。count(字段)没有not null约束时: InnoDB疫情会便利整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null约束时: InnoDB疫情会便利整张表把每一行的字段值都取出来，返回给服务层们直接按行进行累加。count(1)InnoDB疫情遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行累加。count(*)InnoDB疫情并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。优化方案可以自己做count，使用缓存数据库做这类工作。按照效率排序的话，count(字段)<count(主键)<count(1)≈count(*)，所以尽量使用count(*)。update优化InnoDB的行锁是针对索引加的锁，不是针对记录家的锁，并且该索引不能失效，否则会从行锁升级为表锁。规避这种情况就需要在对应字段创建索引。 https://blog.boychai.xyz/index.php/archives/18/ 2022-06-19T07:07:00+00:00 数据库迁移起因本站用的服务器是腾讯云的轻量应用服务器，规格是2核4G8M的，前些日子开了一个游戏服务器和朋友联机，发现这个内存跑到3000左右的时候就会变的比较卡，到3500左右就会直接死机，当时就想着给服务器优化一下环境当时我服务器运行了typecho、harbor、gogs、Jenkins、游戏服务器、还有一些我自己写的后端程序，都在docker上运行，其中typecho、gogs的数据库都是运行单独的mysql5.7，端口不往公网暴露，然后还有一个mysql8.0对外暴露给我自己用，一共是三个，当时就寻思把gogs和typecho的迁移到8.0里面第一次迁移当开始迁移的时候我是直接使用DataGrip把老的数据库表拖拽到新数据表里面，修改好网站的数据地址之后基本没有任何问题，之后就没怎么在意。第二次迁移当我想要发布一次文章的时候出现了报错。找了好久的问题也没找到，当时就没想过是数据库的问题，重新部署了很多遍的typecho，一直是没有找到问题，一直到我打算重新部署一个数据库的时候我发现迁移之后的数据库他的主键自增索引什么的全都没有了，于是就开始第二次迁移。第二次我是使用mysqldump来进行导出，命令如下mysqldump --defaults-extra-file=/etc/mysql/my.cnf Blog > blog.sql然后用DataGrip导入的，导入之后数据库的表结构都回来了内容也都有但是前端对接好之后又出现了问题handsome的主题不能恢复备份，emoji表情全部变成问号"?"。第三次迁移emoji加载不出来无非就是编码的问题，typecho之前是不支持emoji的，之前我做过数据库字符集的修改，第三次导出的时候还是用mysqldump的方式导出但是命令改成了这样，命令如下mysqldump --defaults-extra-file=/etc/mysql/my.cnf --default-character-set=utf8mb4 Blog > blog.sqlmysqldump默认导出的字符集为utf8，emoji的字符集需要utf8mb4，使用DataGrip导入之后就没问题了，emoji显示了，handsome主题备份也能恢复了。