批量插入数据,将
insert into tb_1 values(字段1, 字段2...)
insert into tb_1 values(字段1, 字段2...)
改为按主键顺序插入,手动提交事务,可以提高插入效率
(1). 连接数据库 mysql --local-infile -u{root} -p{pwd} // 代表需加载本地文件
(2). 查询本地文件加载状态 select @@local_infile;
(3). 若未开启本地文件加载,则 set global local_infle=1; // 设定开启本地文件加载
(4). 加载本地文件到表 load data local infile '/url/xx.log' into table {表名} fields teminated by ',' lunes teminated by '\n'; // 代表加载文件 log 到表,按逗号分割字段,按\n换行
数据库存储分为:表空间(64段),段(64区),区(64页-1M),页(16KB),行。
(1). 主键顺序插入:一页写满,按顺序开新页
(2). 主键乱序插入:插入数据在两个满页之间,将一个页分裂成两个,插入新页(页分裂)。
删除数据,是将数据标记为删除,页不满50%会找前后两个页,与另一个50%页合并(页合并)。
注释:可以在建表或索引时,指定页合并的数值(merge_threshold值)
结论,页合并和页分裂都会影响插入效率,所以主键顺序插入效率高。
主键设计原则:降低长度,选择自增属性,避免修改主键
(1). 根据索引顺序排序,效率最高(通过索引获取数据就是排好的顺序)。
(2). 排序的顺序,与联合索引创建时的字段顺序不一致(违背最左前缀法则),不完全按索引排序
(3). 创建索引默认都是升序,排序按联合索引字段顺序都升都降是索引排序。一升一降(违背最左前缀法则)
(4). 可以创建索引时指定排序规则:create index 索引名 on 表名(字段 asc, 字段 desc);
(1). 与排序优化一样,按索引顺序分组,效率最高(满足最左前缀法则)
(2). 可以将最左前缀(联合索引字段)写在where中,之后的(联合索引字段)写在group by中。(是满足最左前缀法则的)
大数据(1千万条以上)查询页越往后越慢,
网上查询给的解决办法是:关联id排序过的子查询,能优化大数据后面排序的效率
但是这样做,为了保持分页顺序,势必前面的数据也要按id排序,会影响效率高的查询也变慢。
最优解决策待定。。。
(1). count函数,返回不为null的记录数。
(2). count(*) mysql做了优化,性能最好。
(3). count(主键) 主键与唯一字段的count性能第二。
(4). count(普通字段) 性能最差,字段如果有null取到的是不为Null的数据的件数
(5). count(1) 不取数据,性能与count(*)一样,性能最好。
更新条件如果不是索引,事务会进行表锁,导致其他事务不能更新数据。避免非索引更新条件。