mysql 中的sql优化
文章目录
- **SQL优化**
- 插入数据的优化
- 主键优化
- **order By优化**
- **group by的优化**
- **分页优化**
- **count优化**
- update 优化
SQL优化
插入数据的优化
insert
**批量操作：**数据数量500-1000条
insert into 表 values(),(),()...;
手动提交事务优化sql
如何优化的：不影响其他并行线程资源，处理完成后统一提交
start transaction ;insert into 表 values(),(),()...;insert into 表 values(),(),()...;insert into 表 values(),(),()...;insert into 表 values(),(),()...;commit；
主键顺序插入
后续讲解，见后
-- 主键乱序插入(速度慢)-- 主键顺序插入(速度快)
Load命令
插入大量数据，百万/千万条数据，这是根据表结构创建的有顺序的数据文件
-- 客户端一次性插入大批量数据，使用insert语句插入性能较低，此时可以使用mysql数据库提供的load命令--mysql --local-infile -u root -p-- 设置全局参数，local_infine = 1 ,开启本地加载文件数据的开关set global local_infile = 1;-- 执行load 指令，准备好数据加载到数据表格中local data local infile '/root/sql.log' 'tb_user' fields into terminated by ',' lines termin;
示例
CREATE TABLE `tb_user` (`id` INT(11) NOT NULL AUTO_INCREMENT,`username` VARCHAR(50) NOT NULL,`password` VARCHAR(50) NOT NULL,`name` VARCHAR(20) NOT NULL,`birthday` DATE DEFAULT NULL,`sex` CHAR(1) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `unique_user_username` (`username`)) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
主键优化
数据组织方式
innoDB存储引擎中 ，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表，表数据都存放在页中。
页分裂：当乱序插入时会产生页分裂情况，大大降低了查询效率
页合并：顺序插入，可以接连合并页
主键的设计原则：
- 尽量降低主键的长度。空间时间
- 插入数据时，尽量选择顺寻插入，选择AUTO_INCREMENT 自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证ID（无需）。
- 业务操作时，尽量减少主键的修改。修改了主键，很多索引结构都会发生改变。
order By优化
Using fileSort : 指的是 用过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer 中完成排序操作，索引不是通过索引直接排序数据的。此排序被称为 FileSort 排序
Using index : 指的是，通过有序索引顺序扫描直接返回结果。这种情况视为using index ，不需要额外的排序，操作效率高。
在构建索引，如聚集索引，覆盖索引 时就将数据存储在叶子节点中，构建了每个字段的顺序。
-- 语法-- 创建索引，根据age 升序，phone 降序,在创建索引时就开始排序好徐，索引创建完毕，数据也排好了序create index idx_user_age_phone_ad on tb_user (age asc ,phone desc);-- 查看详情explain select id ,age ,phone from tb_user order by age asc ,phone desc;
- 根据排序字段建立合适的索引，多字段排序是=时，也遵循最左前缀法则。
- 尽量使用覆盖索引
- 多字段排序，一个升序，一个降序时，此时需要注意联合索引在创建的时候的规则
- 如果不避免出现filesort ，大数据量时，可以适当增大排序缓冲区的大小 sort_buffer_size (默认256k)。
group by的优化
using tempary : 临时表，创建历临时表来进行分组的话，效率是非常低的
-- 语法-- 创建索引create index idx_user_profession_age on tb_user (profession ,age);-- 根据索引进行分组查询select profession ,count(*) from tb_user group by profession;
在分组操作时，可以通过索引来提高检索的效率
分组操作时，索引的使用也是满足最左前缀法则的
分页优化
一个常见又非常头疼的问题：limit 2000000 ,10 ，此时需要mysql 排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询的代价非常大。
优化思路：一般分页查询时，通过创建索引，覆盖索引能够较好地提高性能，可以通过覆盖索引加子查询形式进行优化
select s.* from tb_user t ,(select id from tb_user limit 9000000,10 ) a where s.id = a.id;
count优化
- myISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(*) 的时候会直接返回这个数，效率很高；
- InnoDB 引擎就麻烦了，他执行count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数
优化思路
- count()是一个聚合函数，对于返回的结果，一行行的判断，如果count()函数的参数不是null 累积值就加一，否则不加，最后返回累计值
- 用法count(*)，count(字段)，count(1);
count 的几种用法
count(主键)
- innoDB引擎会遍历整张表，把每一行的主键id值取出来，返回给服务层，服务层拿到主键后直接进行累加，住建部可能为空
count(字段)
- 没有not null 约束：取出每一行的值，让服务层进行判断，是否为空，不为空，进行累加
- 有not null 约束：直接按行进行累加
count(1)
- innodb 遍历引擎整张表，但不取值，服务曾对于返回的每一行，放一个1数字进去，直接进行累加
count(*)
- 直接按行累加
效率比较：count(*) = count(1) > count(主键) > count (字段)；
update 优化
我们在更新数据是一定要根据索引字段进行更新
原因：没有创建索引的字段被作为条件，是表锁。而创建了索引的字段为行锁。升级为表索引，并发性能会大大降低
r囧r小猫
朱雀
亦凉
你的名字
分手后的思念是犯贱
不念不忘少年蓝@
还没有评论，来说两句吧...