- 插入数据
- 主键优化
- order by 优化
- group by 优化
- limit 优化
- count 优化
- update 优化
- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- 插入数据
- 原始操作:insert into tablename values('','');insert into tablename values('','')......
- 批量插入:不建议超过1000条,insert into tablename values(),(),()
- 手动提交事务 :start transaction insert into tablename values('','');insert into tablename values('','')...... commit;
- 主键顺序插入:顺序插入性能高于乱序插入
- 大批量数据插入:如果一次性需要插入大批量数据,使用insert语句插入性能较低,可使用Mysql数据库提供的 load 指令进行插入。操作如下所示:
-
- 主键优化
- 数据组织方式
- 在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为--索引组织表——index organized table,IOT
- 页分裂
- 页可以为空,也可以填充一般,也可以填充100%。每个也包含了 2-N 行数据(如果一行数据过大,会行溢出),根据主键排列。
- 页合并
- 当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且其空间变得允许被其他记录声明使用。
- 当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
- 注:MERGE_THRESHOLD,合并页的阈值,可以自己设置,在创建表或创建索引时指定
- 主键设计原则
- 满足业务需求的情况下,尽量降低主键的长度
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID做主键或者是其他自然主键,如身份证号(UUID--无序,且长度长)
- 业务操作是,避免对主键的修改
- 数据组织方式
- order by 优化
- Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后再排序缓冲区 sort buffer 中完成排序操作,
- 所有不是通过索引直接返回排序结果的排序都叫filesort 排序
- Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高。
- eg:
-
EXPLAIN select id,NAME,age,gender,phone from tb_users order by age;
EXPLAIN select id,NAME from tb_users use INDEX(idx_user_name) order by name; -
- 创建索引优化:
- create index idx_user_age_phone on tb_users(age asc,phone desc)——联合索引,指定排序规则
- explain select id,age,phone from tb_users order by age asc,phone desc
- 总结:
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ACS\DESC)
- 如果不可避免的出现filesort,大数据排序时,可以适当增大排序缓冲区大小 sort_buffer_size (默认256k)
- Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后再排序缓冲区 sort buffer 中完成排序操作,
- group by 优化
- 在分组操作时,可以通过索引来提高效率
- 分组操作是,索引的使用也是满足最左前缀法则的
- limit 优化
- 在使用limit进行分页查询时,一个常见的问题——越往后的数据,查询代价越大。例如 limit 2000000,10——此时需要mysql排序前2000010的数据,并返回后10条数据,其他记录丢弃,查询排序的代价非常大。
- 优化方案:覆盖索引 + 子查询(一般分页查询时,通过创建覆盖索引能够比较好的提高性能,可以通过覆盖索引加子查询形式进行优化)
- SELECT s.* from tb_users s ,(select id from tb_users ORDER BY id LIMIT 2,2) a where a.id=s.id;
- count 优化
- count 的几种用法
- count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就是 1 ,否则不加,最后返回累计值(count(name)——如果name==NULL,则计数不加)。
- count(*)、count(主键)、count(字段)、count(1)
- count(主键):InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为NULL)
- count(字段):
- 字段没有not null约束,InnoDB引擎会遍历整张表把每一行的字段值取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。
- 有not null 约束,InnoDB引擎会遍历整张表把每一行的字段值取出来,返回给服务层,直接进行累加。
- count(*):InnoDB引擎并不会吧全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。
- count(1):InnoDB引擎遍历整张表,但不取值,服务层对于返回的每一行,放一个数字 1 进去,直接按行进行累加。——count(数字)
- 按效率排序:count(*) > count(1) > count(主键) > count(字段)
- count 的几种用法
- update 优化
- 在更新数据时,需根据索引列作为条件更新——索引列,更新触发行锁、 非索引列触发表锁
- 行锁升级为表锁降低并发性能
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!