MySQL-sql优化

插入数据

insert优化

批量插入

insert into tb_users values (1,'cat'),(2,'mouse'),(3,'bird');

手动提交事务

因为MySQL中默认的事务提交方式为自动提交，所以在每次插入数据时，就是涉及到事务频繁地开启和关闭。所以将事务提交方式改成自动提交，就能避免这种情况。

start transaction;
insert ----;
insert ----;
insert ----;
commit;

主键顺序插入

按主键的顺序插入，会比乱序插入的效率更高，这与MySQL的数据组织顺序有关。

大批量数据插入

如果需要一次性插入大批量数据，使用insert的效率太低了，可以使用MySQL中的load指令，这个指令是从文件中直接将大批量数据导入。

首先在登入MySQL需要允许其访问本地文件

mysql --local-infile -u root -p //连接远程服务器时，加上参数

然后将全局参数设置为1（可以理解为一个从本地加载文件的开关）

set global local_infile = 1;

查询是否打开

select @@local_infile;

然后使用load导入文件

load data local infile '文件路径' into table 表名 fields terminated by ','
lines terminated by '\n';

其中 fiields terminated by 代表每一个字段用什么隔开，lines terminated by 表示行用什么隔开

主键优化

在InnoDB储存引擎中的数据是按照主键顺序组织存放的，这种储存方式叫做索引组织表（IOT）

逻辑储存结构如下：

页分裂

数据都是储存在页中的，页中的数据可以填充一半，也可以填充百分之一百。按照主键的顺序填入数据。

顺序插入数据：

乱序插入数据：

此时如果要插入50的话，会将第一页的数据，做分割：

然后将分割出的23、47放入新的数据页，将50插入到47的后面，最后调整页的连接，这样才能保证每一页的数据是按照主键顺序存放的。

页合并

再删除数据时，其实并没有在物理层面对记录进行删除，只不过是对这块空间标记为（flaged），使得能够被其他空间声明记录。

而当页中删除的记录达到MERGE_THRESHOLD（默认页的50%），InnoDB就会开始寻找该页的前后是否有合适的页能够进行合并，以优化MySQL的储存空间。（MERGE_THRESHOLD合并页的阈值，能够自己设置）

主键设计原则

在不影响业务功能的情况下，应该尽可能降低主键的长度：

这一点从聚集索引和二级索引的结构来理解，因为聚集索引一般是主键索引，而聚集索引虽然只能有一个，但是二级索引可以有很多个。二级索引的叶节点就是逐渐的值，所以如果主键太长，就会导致二级索引的叶节点占用大量磁盘空间。

插入数据时，采用顺序插入（使用主键自增）

乱序插入就会导致出现页分裂现象

尽量不要使用UUID作主键，也不要使用自然主键，如身份证（过长）
在业务操作时，避免对主键的修改

order by优化

using filesort

通过扫描索引或者全表扫描，读取数据后，传入排序缓存区（sort Buffer）。只要不是通过扫描索引直接返回结果的（需要额外排序的），都是using filesort

using index

通过扫描索引直接返回结果（不用进行额外排序的），查询效率高

在创建索引时，是可以指定创建索引的顺序的，可以使顺序（asc）也可以是倒序（desc）：

create index idx_xxx on tb_xxx(字段名1 asc,字段名2 desc)；
#idx_xxx创建的索引名
#tb_xxx表名
#指定创建索引的顺序（顺序还是倒序）

根据排序字段建立适当的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引，避免回表查询
多字段排序时，升序和降序，注意在建立联合索引时的顺序
如果实在不能避免，file_sort的使用，那么需要适当增大sort_buffer_size（默认为256k）

group by优化

创建表如下

然后为age、contact、address字段创建联合索引：

create index idx_age_contact_address on users(user_age,user_contact,user_address);

同样需要满足最左前缀法则：

可以比较一下这两条命令的云运行结果

explain select user_age,count(*) from users group by user_age;

explain select user_contact,count(*) from users group by user_contact;

两者的运行结果区别在于，前者Extra信息中只有Using Index，但是后者的Extra信息中多了一个Using temporary，这是因为并没有满足最左前缀法则（没有包含联合索引的坐左边的字段）。

limit优化

假如，有一张1000万数据量的表，我想通过分页查询找到第500,0000条数据后面的十条数据，那么意味着MySQL要对500,0010条数据进行排序，然后只返回十条数据。这就会在造成极低的查询效率。

通常情况下，会使用覆盖索引，以及子查询的方式进行limit的优化。

select *from tb_uesrs t , (select id from tb_users order by id limit 5000000,10) a where t.id = a.id;

如上就是一个经典的联表查询。

count优化

select count(*) from users;

MyISAM会将表的总行数储存在磁盘空间中，所以执行计数这个操作就会很快。

但是对于InnoDB来说，它执行count时，会一条一条的读取数据，然后计数，效率很低。

count(主键)

InnoDB会遍历整张表，拿到每一行的主键，返回给服务层，然后服务层拿到数据后按行累加。

count(字段值)

没有not null约束，InnoDB遍历整张表然后取出每行字段值，返回给服务层，然后服务层判断字段值是不是为空，不为空的话就计数累加。

有not null约束，InnoDB遍历整张表，然后取每行的字段值返回个服务层，然后直接累加计数。

count(1)

InnoDB会遍历整张表，但是并不取值，服务层对于返回的每一行都会放一个1进去，然后每行进行累加。

count(*)

同样只会遍历整张表，并不取值，并且做了专门的优化，服务层直接按行进行累加。

综上所述，count（）的查询效率如下：

count(*) ≈ count(1) > count(主键) > count(字段)

update优化

InnoDB的行锁是针对索引加的锁，而并不是加在记录上的。并且该索引不能失效，要不然行锁就会升级为表锁。

尽量根据主键或者索引更新信息。

插入数据

insert优化

批量插入

手动提交事务

主键顺序插入