mysql调优

一、insert调优

　　1、插入多条数据时最好批量插入 （但一般不超过一千条）

　　2、手动提交事务，多条语句一起提交

start transaction;
insert into table values (),(),();
insert into table values (),(),();
insert into table values (),(),();
commit;

　　3、主键顺序插入 ，性能更高，取决于mysql的数据组织结构。

　　4、如果一次性插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令。操作如下

#客户端连接服务器时，加上参数 --local-infile
mysql --local-infile -u root -p；
#设置全局参数local_infile = 1
set global local_infile = 1
#执行load指令，将准备好的数据加载到本地表中, 引号中依次为 ‘文件路径’，‘表名’，‘分隔符’，‘行分隔符’
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n'

 

 

二、主键调优

　　1、首先要了解，在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table) IOT

下图可以看到在聚簇索引B+树的结构中，行数据都是按照主键顺序存放的

 

　　2、主键设计原则

　　　　（1）、满足业务需求的情况下，尽量降低主键的长度。

　　　　（2）、插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

　　　　（3）、尽量不要使用UUID做主键或是其他自然主键，比如身份证号。

　　　　（4）、业务操作时，避免对主键的修改。

 

三、order by调优

　　先总结：

　　　　1、根据字段建立合适的索引，多字段排序时，也遵从最左前缀法则

　　　　2、尽量使用覆盖索引

　　　　3、多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ACS/DESC）

　　　　4、如果不可避免的出现filesort，大数据量排序时，可是适当增大缓冲区大小，sort_buffer_size（默认256k），超过缓冲区大小的话就需要在磁盘文件中排序了，效率会更低。

　　下面演示一下案例。

　　（1）、利用explain性能分析一条order by语句，按照age和phone升序排序，Extra中写的是Using filesort

　　Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FIlesort排序。

 

 　　（2）、给age和phone创建联合索引 (默认age和phone都是升序建立索引)

create index idx_user_age_phone_aa on tb_user(age,phone)

　　（3）、再次利用explain性能分析一条order by语句，按照age和phone升序排序，Extra中写的是Using index

　　Using index：通过有序索引扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

 

 　　（4）、如果要执行的语句是一个升序，一个降序怎么办？

　　可以看到by age用的是index， by phone 还是filesort， 效率不高

 

 　　（5）、可以再创建一个按phone 降序的索引

create index idx_user_age_phone_ad on tb_user(age asc, phone desc);

　　再次执行order by语句，一个升序，一个降序，是直接从索引返回的，效率高。

 

 　　（6）、要注意的是，order by的顺序要遵从索引的最左原则

　　上面建里的索引是升序age在左，降序phone在右，如果sql语句的顺序是先按照phone降序，再按照age升序排列，仍然会用到filesort