黑马mysql优化

插入优化：

1.插入大量数据时建议执行批量插入（insert into 表名 values（一行数据),(二行数据),...(n行数据)）

2.进行手动事物提交

start transaction；

insert into 表名 values（一行数据),(二行数据),...(n行数据)；

insert into 表名 values（一行数据),(二行数据),...(n行数据)；

insert into 表名 values（一行数据),(二行数据),...(n行数据)；

insert into 表名 values（一行数据),(二行数据),...(n行数据)；

commit；

3.主键顺序插入

顺序插入效率大于乱序插入

4.如果插入数据数量到达十万百万级别（大批量数据插入）可以使用mysql提供的load指令进行插入

5.load指令

mysql --local-infile -u root -p（linux下登录数据库）

select @@local_infile;(查看是否开启此功能（1为开启）)

将数据脚本下载到/root目录下

load data local infile '/root/文件名 into table 表名 fields terminated by '列分割符号' lines terminated by '\n''

主键优化：

1.数据的组织方式

在innoDB存储引擎中，表数据都是根据主键顺序存放的，这种存储方式的表称为索引组织表（IOT）

聚集索引：叶子节点为行数据（主键索引为聚集索引）

二级索引：叶子节点为主键

最终表数据存放时根据主键进行顺序存放。

如图，非叶子节点只时进行了数据的索引作用，叶子节点进行了数据的存储

非叶子节点和叶子节点都是存储在一个名为页（page）的逻辑结构中（页大小固定16k）

 页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-n行数据（如果一行数据太大，则会行溢出），根据主键排列

如图主键顺序插入

当第一页剩余空间无法容纳下一条数据时会申请第二页进行数据存储第一页与第二页会维护一个双向指针

 乱序插入：

如图如果还想插入一个主键为50则会将第一页的一半数据放入到一个新页中在将主键为50的元素插入到新页中，插入到第一页与第二页之间成为新的第二页

 加入50后出现页分裂

 页合并：

当删除一行数据时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用

当页面中删除记录数达到MERGE_THRESHOLD(默认为页的50%，可以自己设置，在创建表或者创建索引时指定) ，innoDB会开始寻找最靠近（前后）看看能否可以将两个页合并从而优化使用空间

 主键设计原则：

满足业务需求的情况下尽量降低主键长度

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键

尽量不要使用UUID做主键或者是其他自然主键，如：身份证号

业务操作时尽量不要对主键进行修改。

order by优化

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

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

当group by （字段）字段没有索引时就会是using filesort

通过降序排列时，如果索引没有规定是降序排列则也会是usingindex，不过会出现back word index scan

如果聚合索引，排序时列名位置与创建索引时不一致则会导致不符合最左前缀法则

联合索引中排序方式为一个升序一个降序（索引创建时未明确指定排序方式默认为升序）

会导致一个升序列走索引而降序列不走索引

可以在创建索引时通过在索引列后方声明排序方式（列名 asc，列名desc）

在排序时如果查询关键字通过索引都可以查询到则是覆盖索引，反之如果查询字段在索引中无法查询到则会进行回表操作

总结：

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

尽量使用覆盖索引

多字段排序，一个升序一个降序需要注意联合索引创建时的排序规则

如果不可避免的出现file sort 大数据排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)

group by优化

需要满足最左前缀法则

limit优化：

select * from 表名 limit 2000000，10

优化为：

select u.* from user u,(select id from 表名 order by id limit 2000000，10 ) a where a.id=u.id

覆盖索引

count优化：

在没有where条件的情况下

MYISAM 引擎把一个表的总行数存在了磁盘中因此执行count（*）的时候会直接返回这个数，效率很高；

innoDB 引擎就很麻烦，它执行count（*）的时候需要把一行行的数据从引擎中读取出来然后开始累计计数

优化思路自己技计数                                                                                                                                           用法：count（*）count （主键）count（字段）count（1）

count （主键） 

innoDB引擎会遍历整张表把每一行的主键都取出来，返回给服务层，服务层拿到主键后直接进行累加（不用判断null）                                                                                                                             count（字段）         

没有加not  null约束innoDB引擎会遍历整张表把每一行的字段都取出来，返回给服务层，服务层拿到主键后进行判断是否为null，不是null则进行累加

有not null约束innoDB引擎会遍历整张表把每一行的主键都取出来，返回给服务层，服务层拿到主键后直接进行累加（不用判断null）           

count（1）

innoDB引擎便利遍历整张表，但是不取值服务层对于返回的每一行放入一个数字进去直接按行进行累加           （（）里面放入其他也可以）                                                                                                                                                           

count（*）

innoDB引擎不会把所有字段全部取出来，而是做了专门的优化，不取值。服务层直接进行累加           

update优化

如果where 后面的字段有索引

innoDB会在执行更新操作时加一个行级锁，不提交更新其他事物不许对该行进行操作

若没有索引

innoDB会在执行更新操作时加一个表锁，不提交更新其他事物不许对该表进行操作

总结：

innoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且索引不能失效，否则会使得行锁升级为表锁