mysql高级教程
1.索引相关
1.创建索引
-- 创建索引
ALTER TABLE `test`.`test`
ADD INDEX `myindex`(`name`) USING BTREE;
CREATE INDEX myindex ON test(`name`);
-- 查看索引
SHOW INDEX FROM test;
-- 删除索引
ALTER TABLE `test`.`test`
DROP INDEX `myindex`;
DROP INDEX myindex on test;
2.sql分析explan
- key不为空时,是索引名称时,代表走了做引;如果key为空代表没有走索引
EXPLAIN SELECT * from test WHERE `name`='test'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OzjXmnjd-1677651926633)(C:\Users\LiBo\AppData\Roaming\Typora\typora-user-images\image-20221226153105574.png)]
3.索引失效情况
- 计算、函数、类型转换(自动或手动)导致索引失效
- 范围条件右边的列索引失效
应用开发中范围查询,例如:金额查询,日期查询往往都是范围查询。应将查询条件放置 WHERE 语句最后。(创建的联合索引中,务必把范围涉及到的字段写在最后
- 不等于( != 或者 <> )索引失效
- is null 可以使用索引,is not null 无法使用索引
- like 以通配符 % 开头索引失效
- OR 前后存在非索引的列,索引失效
4.查询正在执行的sql(慢查询)
select * from information_schema.`PROCESSLIST` where info is not null;
show full processlist;
2.事物相关
-- 开启事物
START TRANSACTION;
-- 插入数据
INSERT INTO test(id,name) VALUES (3,'test2');
-- 查询数据
SELECT * FROM test;
-- 回滚事物
ROLLBACK;
-- 提交事物
COMMIT;
-- 查询事物隔离级别
show variables like 'transaction_isolation';
show variables like 'tx_isolation';
2.1 事务的四大特性(ACID):
- 原子性(Atomicity):事物是最小单位,不可再分;
- 一致性(Consistency):事物要求所有的DML语句操作的时候,必须保证同时成功或者同事失败;
- 隔离性(Isolation):同一时间,只允许一个事物请求同一数据,不同的事物之间 彼此没有任何干扰;
- 持久性(Durability):是事物的保证,事物终结的标志(内存的数据持久到硬盘文件当中)
2.2事物的隔离级别:
-
读未提交(Read uncommitted)设置隔离级别set session transaction isolation level read uncommitted;
-
读已提交(Read committed)设置隔离级别set session transaction isolation level read committed;
-
可重复读(Repeatable read)(mysql默认事物隔离级别)设置隔离级别set session transaction isolation level repeatable read;
-
串行化(Serializable )设置隔离级别set session transaction isolation level serializable;
set session transaction isolation level ***;只对当前会话生效
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4uma04lU-1677651926634)(C:\Users\LiBo\AppData\Roaming\Typora\typora-user-images\image-20221226161433771.png)]
3.Mysql 子查询提高Limit查询效率
SELECT * FROM t_test WHERE age = 100 LIMIT 1000000, 10
SELECT * FROM `t_test` a INNER JOIN (SELECT id FROM t_test WHERE age = 100 LIMIT 1000000, 10) b on a.id = b.id;
4. distinct 和 group by 哪个效率更高
- 在语义相同,有索引的情况下:
group by
和distinct
都能使用索引,效率相同。因为group by
和distinct
近乎等价,distinct 可以被看做是特殊的group by
。 - 在语义相同,无索引的情况下:
distinct
效率高于group by
。原因是distinct
和group by
都会进行分组操作,但group by
在 MySQL8.0 之前会进行隐式排序,导致触发 filesort,sql 执行效率低下。但从 MySQL8.0 开始,MySQL 就删除了隐式排序,所以,此时在语义相同,无索引的情况下,group by
和distinct
的执行效率也是近乎等价的。
相比于distinct
来说,group by
的语义明确。且由于 distinct 关键字会对所有字段生效,在进行复合业务处理时,group by
的使用灵活性更高,group by
能根据分组情况,对数据进行更为复杂的处理,例如通过having
对数据进行过滤,或通过聚合函数对数据进行运算。
stinct 关键字会对所有字段生效,在进行复合业务处理时,group by
的使用灵活性更高,group by
能根据分组情况,对数据进行更为复杂的处理,例如通过having
对数据进行过滤,或通过聚合函数对数据进行运算。