索引失效场景
- 没有遵循最左原则;
- 加了索引的字段进行数学运算或者使用了sql函数或者自动或手动进行了类型转换
- 范围之后全失效;比如 a、b、c复合索引 a=x and b>20 and c=x,这样c没有使用到索引
- 尽量使用覆盖索引(索引列与查询列一致)减少select *
- 使用!= 或者 <> 导致索引失效,扫描全表
- is null、is not null导致索引失效,扫描全表
- like模糊匹配%xx%,或导致索引失效,但%放在最右边(xx%)可以避免索引失效,如果非要使用%xx%,可以建复合索引,当查询列和索引列匹配时可以避免索引失效,比如 a、b复合索引,select Id / select id, a / select id, a, b / select a / select a, b / select b/ 都能使用到索引,但上面任何一种查询中增加字段 c 就会导致索引失效,select * 也会导致索引失效。(因为没有使用覆盖索引)
- 字符串不加单引号导致索引失效
慢SQL
MySQL默认没有开启慢sql日志,查看是否开启:
SHOW VARIABLES LIKE '%slow_query_log%'
开启命令:
SET GLOBAL slow_query_log = 1;
开启慢查询日志只对当前数据库生效,MySQL重启后会失效,相当于本此开启了,如果要永久生效,就必须修改配置文件my.cnf(其它系统变量也是如此);
MySQL判定慢查询的为10s,可以通过命令查看:
SHOW VARIABLES LIKE 'LONG_QUERY_TIME%'
设置慢查询阙值命令:
SET GLOBAL long_query_time = 3;
查询当前系统中有多少条慢查询记录:
SHOW GLOBAL STATUS LIKE '%slow_queries%'
使用show profile进行sql分析
查看状态:
SHOW VARIABLES LIKE 'PROFILING'
开启命令:
SET profiling = ON
查看最近执行的sql(只保留15条):
SHOW PROFILES;
诊断sql:
SHOW PROFILE cpu,block io FOR QUERY 75(数字是Query Id)
分析数据中需要注意的点:
MySQL 锁
给表加锁命令:
LOCK TABLE student read;(读锁);
LOCK TABLE student WRITE(写锁);
查询哪些表被锁了:
SHOW OPEN TABLES
释放锁:
UNLOCK TABLES
MyISAM引擎下:
分析表锁定:
SHOW STATUS LIKE 'TABLE%'
事务
- A:原子性
- C:一致性
- I: 隔离性
- D:持久性
并发事务带来的问题
- 更新丢失 当一个或多个事务对同一数据做修改时,由于事务之间互相不知道,最后做修改的事务会把之前事务的修改覆盖;解决:当一事务未提交是,另一事务不可访问。
- 脏读 当A事务更改但未提交时,B事务读取到了更改未提交的值,并在此基础上做了操作,当A事务回滚B事务读取到的数据无效,不符合数据一致性。
- 不可重复读 事务A读取到了事务B已经提交修改的数据,不符合事务的隔离性。
- 幻读 事务A读取到了事务B插入的数据,不符合隔离性。
事务的隔离级别
MySQL默认可重复读
索引失效会导致行锁变表锁
间隙锁
概念:当sql进行范围查找或修改时(where xx > 1 and xx < 6),InnoDB会给符合条件的数据记录的索引项加锁,对于键值在条件范围内但不存在的数据,叫做“间隙”(GAP),假如没有 x = 2这条记录,也会给这条记录加锁,这种锁机制叫做间隙锁(Next-Key锁)。
行锁分析命令
SHOW STATUS LIKE 'innodb_row_lock%'
行锁优化建议
- 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能较少检索条件,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度 尽可能低级别事务隔离