mysql高级教程

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）：

1. 原子性（Atomicity）：事物是最小单位，不可再分；
2. 一致性（Consistency）：事物要求所有的DML语句操作的时候，必须保证同时成功或者同事失败；
3. 隔离性（Isolation）：同一时间，只允许一个事物请求同一数据，不同的事物之间 彼此没有任何干扰；
4. 持久性（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对数据进行过滤，或通过聚合函数对数据进行运算。