【MySQL.底层知识自我理解汇总】
拾刻:
基础薄弱,重新学习,查漏补缺。
文章目录
一、基础
1. MySQL的核心是存储引擎
主要存储引擎有:MyISAM,InnoDB,Memory,Archive,Federated …存储引擎概述;
2. InnoDB
MySQL默认用InnoDB引擎存储,innoDB默认用B+树索引(二叉查找树,平衡二叉树,B树演化而来)。B+树详解;
3. 一条sql语句在数据库框架中执行的流程:
- 客户端发送服务端接收;
- 查找缓存,是否存在;
- 分析,预处理,优化sql执行计划;
- 查询;
- 返回客户端信息;
4. 查询语句前加explain来查看执行计划,相应优化;
4.1执行计划:
EXPLAIN SELECT * FROM
user
- 先看type之后看key,再看rows,最后是Extra;
- 如果type出现ALL时是全表扫描,这时候就一定要进行调优!
- rows的最佳值是1:row越小越好;
- 当extra中出现Using temporary或者Using filesort时,说明sql语句需要进行优化(对于Using temporary,当我们的查询涉及多张表时,需要将查询结果放入第三张临时表中来存放,这样势必会降低我们的查询效率)
4.2查询性能的优化方法:
-
1,减少请求的数据量:
- 只返回必要的列:最好不要使用 SELECT * 语句。
只返回必要的行:使用 LIMIT 语句来限制返回的数据。
缓存重复查询的数据:使用缓存可以避免在数据库中进行查询,特别在要查询的数据经常被重复查询时,缓存带来的查询性能提升将会是非常明显的;
- 只返回必要的列:最好不要使用 SELECT * 语句。
-
2,减少服务器端扫描的行数:
- 最有效的方式是使用索引来覆盖查询。
4. 索引的分类:
- 1,数据结构分类:
- 树索引 (O(log(n)));
- 哈希索引(hash);
- 2,物理存储角度:树
- 聚焦索引;
- 非聚焦索引;
- 3,逻辑角度:
- 普通索引
- 唯一索引;
- 主键索引;
- 全文索引;
- 联合索引;
5. 索引失效情况分类:
- 索引列参与表达式计算:
SELECT ‘sname’ FROM ‘stu’ WHERE ‘age’ + 10 = 30;
- 函数运算:
SELECT ‘sname’ FROM ‘stu’ WHERE LEFT(‘date’,4) < 1990;
- %词语%–模糊查询:
SELECT * FROM ‘stu’ WHERE
uname
LIKE ‘码农%’ ; ---- 走索引
SELECT * FROM ‘stu’ WHEREuname
LIKE ‘%码农%’ ; ---- 不走索引
- 字符串与数字比较不走索引:
EXPLAIN SELECT * FROM ‘stu’ WHERE ‘age’ = “1” ---- 走索引
EXPLAIN SELECT * FROM ‘stu’ WHERE ‘age’ = 1 ---- 不走索引,同样也是使用了函数运算
- 查询条件中有 or ,即使其中有条件带索引也不会使用(换言之,就是要求使用的所有字段,都必须建立索引):
select * from ‘stu’ where dname=‘xxx’ or loc=‘xx’ or deptno = 45;
-
正则表达式不使用索引;
-
MySQL 内部优化器会对 SQL 语句进行优化,如果优化器估计使用全表扫描要比使用索引快,则不使用索引;