mysql 高级第二天:
回顾:
mysql 的逻辑结构:
连接层:
服务层:
引擎层:
存储层:
select * from stu where id = 1;
MySQL索引底层结构为什么是B+tree?
BTree : 数据是存储在叶子节点与非叶子节点中!
B+Tree : 将所有的数据都存在在了叶子节点!
mysql5.5 以后底层使用B+tree
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
时间复杂度:
hash 次之平衡树!
- 7 中join
mysql 不支持 full outer join !
解决方案:
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId=b.id;
索引优化:
索引都是存储在磁盘上的!
聚簇索引:通常指主键 并且推荐主键使用int 类型自增! 不建议使用varchar类型! ``` mysql 的索引分类: 单值 复合 唯一 主键select id ,name from stu where id =? and name = ? order by id;
语法:
name varchar(20); 张三123安慰而啊啊的去,张三张阿嘎啊123安慰而啊啊的去
create index idx_name on tableName(name length(10));
这个name 可以小于当前字段的长度;
drop index idx_name on customer;
SHOW INDEX FROM table_name;
*:
创建索引:
1)主键自动建立唯一索引
2)频繁作为查询条件的字段应该创建索引
3)查询中与其它表关联的字段,外键关系建立索引
4)单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
5)查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
6)查询中统计或者分组字段
不建议创建索引: 1)表记录太少 2)经常增删改的表 3)where条件里用不到的字段不创建索引
在实际的业务中,根据需要适当的创建索引!
3.
索引优化分析_explain
<br />使用方法:<br />
Explain + sql
会产生笛卡尔积!
explain select * from t_emp a ,t_dept b where a.deptid = b.id;
不会产生笛卡尔积!
explain select * from t_emp a inner join t_dept b on a.deptid = b.id;
但是:mysql 这个优化器 自动优化了!
上午总结:<br />
1. 七种join 链接方式:需要弄懂!
2. 多表关联查询:
# 步骤1:先找出相关的表!t_dept,t_emp
# 步骤2:找关联方式:LEFT JOIN ,RIGHT JOIN, INNER JOIN , UNION , 自关联 ON 关联条件!a.deptId = b.id , a.id = b.CEO
# 步骤3:是否有筛选条件
# 步骤4:调试过程,将这个sql 语句 写对!
必会!
SELECT e1.name,e2.name FROM t_emp e1
LEFT JOIN t_dept d ON e1.deptId = d.id
LEFT JOIN t_emp e2 ON d.ceo = e2.id;
3. 如何创建索引,删除
单一,复合
唯一,主键
4. explain 使用
explain + sql!
*** mysql 这个优化器 自动优化了!
下午:<br />
explain + sql 具体描述!
1.
数据准备:<br />
explain 的各个字段表示的意思:<br />
id: 表示查询的顺序,这个id 出现的个数越少越好!<br />
type:<br />
system>const>eq_ref>ref>range>index>ALL<br />
开发中最优选择:ref,range
index: 表示遍历了整个索引树!
All:遍历整个磁盘的数据!
建立索引的情况:
1)主键自动建立唯一索引
2)频繁作为查询条件的字段应该创建索引
3)查询中与其它表关联的字段,外键关系建立索引
4)单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
5)查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
6)查询中统计或者分组字段
key_len:
使用到索引的长度,通常这个值越大越好!
rows:列显示MySQL认为它执行查询时必须检查的行数。值越小越好
Extra;
Using filesort: 如果额外的列中出现了这个标识 , 则表示排序没有使用到索引!
解决方案:给排序字段添加索引
create index idx_name on tableName();
Using temporary: 表示分组没有使用到索引!
Using index:表示使用了覆盖索引 :覆盖索引【后面的检索条件,正好在查询项中也存在!】
using join buffer :表示关联字段没有使用索引!
```
- 模拟数据优化: