SQL优化
插入数据
insert: 批量插入、手动控制事务、主键顺序插入
大批量插入: load data local infile
主键优化*
1.主键长度尽量短
因为一个表可能会有很多个二级索引,而二级索引的叶子节点中存放的除了索引字段还有
主键id,因此如果主键id尽可能的段,就能省磁盘空间
2.顺序插入
尽量使用 AUTO_INCREMENT
主键如果乱序插入会导致页分裂
页分裂
页合并
MERGE_THRESHOLD: 合并页的阈值,可以自己设置
order by优化
using index: 直接通过索引返回数据,性能高
using filesort: 需要将返回的结果在排序缓冲区排序
通过创建索引来解决
group by优化
索引、多字段分组满足最左前缀法则
limit优化
mysql查询数据的时候,分页越往后效率越低(要查询从第10000条数据后取10条,那么mysql会将前一万条都排好序然后取后面十条数据)排序的代价大
通过覆盖索引+子查询
1.select * from tb_sku order by id limit 900000,10; (19 sec)
2. select s.* from tb_sku s, (select id from tb_sku order by id limit 9000000,10) a where s.id = a.id; (覆盖+子查询) (11 sec)
count优化
性能: count 1 约等于 count * > count(主键id) > count(字段)
update 优化
尽量根据主键/索引进行数据更新
防止不用主键或索引导致由行锁升级为表锁
锁
全局锁
全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML的写语句,DDL语句,以及更新操作的事务提交语句都将被阻塞。
例子:使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。
加全局锁: flush tables with read lock;
备份数据:mysqldump -uroot -pxxxx itcast > itcast.sql;
解除全局锁:unlock tables;
特点
数据库加全局锁,是一个比较重的操作,存在以下问题:
1.如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
2.如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟
在备份时加上--single-transaction
参数来完成不加锁的一致性数据备份
mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql
新版的mysqldump默认启用了新标志,通过 --column-statistics=0禁用此标志
mysqldump --single-transaction --column-statistics=0 -h 192.168.128.165 -uroot -p123456 itcast> D:/itcast.sql;
表级锁
表级锁,每次操作锁住整张表。锁的粒度大,发生锁冲突的概率最高,并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
主要有以下三类:
- 表锁
- 元数据锁(meta data lock、 MDL)
- 意向锁
表锁
表锁分为两类:
1.表共享读锁(read lock)
2.表独占写锁(write lock)
读锁不会阻塞其它客户端的读,但是会阻塞写。写锁阻塞其它客户端的读和写,自身可读可写
语法:
加锁:lock tables 表名 read/write
释放锁:unlock tables / 客户端断开连接
元数据锁(meta data lock, MDL)
MDL加锁过程是系统自动控制的,无需显示使用,在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据 一致性,在表上有活动事务的时候,不可以对元数据进行写操作。
为了避免DML与DDL冲突,保证读写的正确性
在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候,会自动的加MDL读锁(共享);当对表结构进行变更操作的时候,会自动加MDL写锁(排他)。
执行select等查询语句时,会默认加上SHARED_READ
锁,执行insert、update、delete等语句,会默认加上SHARED_WRITE
锁,它与SHARED_READ兼容,因此两个事务,一个通过select查询加上了SHARED_READ锁,其它的事务依然可以执行insert这些语句
查看元数据锁:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
意向锁
在事务1中,如果有通过id字段去update数据的情况,那么数据库就会为这一行加上行锁,这时事务2想加表锁,因为行锁和表锁不兼容,因此mysql需要去判断该表中每一行是否有加行锁,而去遍历检查,造成很大的性能浪费。因此引入了意向锁,事务中,只要加上了行锁,就会自动的也加上意向锁,那么其它事务只需要看整个表是否有意向锁就能知道是否有行锁,而不需要去关注行锁在第几行。
1.意向共享锁(IS):与表锁共享锁(read)兼容,与表锁排它锁(write)互斥。
select * from score where id = 1 lock in share mode;
使用 lock in share mode 会加上这行的共享锁,同时加上score这个表的意向共享锁
2.意向排他锁(IX):与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥。
update score set math=66 where id = 1
执行update操作时(DML)会自动为这行加上行锁,以及这个表加上意向排他锁
查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
行级锁
行级锁,每次操作锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用在InnoDB存储引擎中。
1.行锁(Record Lock):锁定单个行记录的锁,防止其它事务对此行进行update和delete。在RC、RR隔离级别下都支持。
2.间隙锁(Gap Lock):锁定索引记录间隙(不含记录本身),确保索引记录间隙不变,防止其它事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
3.临建锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。
行锁
1.共享锁(S):允许一个事务去读一行,阻止其它事务获得相同数据集(同一行)的排它锁。
2.排他锁(X):允许获取排它锁的事务更新数据,阻止其它事务获得相同数据集的共享锁和排他锁。
insert、update、delete处理自动加上 行的‘排它锁’,还会加上表的意向排他锁,下面的同理都会加上同样的意向锁。
行锁
默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜索和索引扫描,以防止幻读。
1.针对唯一索引进行检索式时,对已存在的记录进行等值匹配时,将会自动优化为行锁。
2.InnoDB的行锁是针对索引加的锁,不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,此时就会升级为表锁。
间隙锁
1.索引上的等值查询(唯一索引),给不存在的记录加锁时,优化为间隙锁。
update test set name = ‘test’ where id = 7; id是唯一索引,且该表中不存在id为7的数据,则
会加上间隙锁。比如只有数据 id 为 3 和 id 为 8,那么会用间隙锁锁住3 到 8之间,不允许其它事务插入数据到id 3-8之间
2.索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock退化为间隙锁。
会把符合目标的数据,以及符合目标的数据上下段的间隙也锁住
3.索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止
间隙锁唯一目的是防止其他事务插入间隙,间隙锁可以共存,一个事务采用的间隙锁不会阻止另一个事务在用一间隙上采用间隙锁