MySQL高级笔记

mysql高级笔记

索引优化
性能下降sql慢、执行时间长、等待时间长。
索引分为：单值索引和复合索引。
sql执行顺序


左连接

右连接

内连接

左外连接

右外连接

全连接

两张表中都没有出现的数据集

两张表的共有：select * from testmysql.tbl_emp e inner join testmysql.tbl_dept d on e.deptId = d.id;

左连接：select * from testmysql.tbl_emp e left join testmysql.tbl_dept d on e.deptId = d.id;

右连接：select * from testmysql.tbl_emp e right join testmysql.tbl_dept d on e.deptId = d.id;

左外连接：select * from testmysql.tbl_emp e left outer join testmysql.tbl_dept d on e.deptId = d.id where d.deptName is null；

左外连接：where 右表等于null；
右外连接：select * from testmysql.tbl_emp e right outer join testmysql.tbl_dept d on e.deptId = d.id where e.deptId is null；

右外连接：where 左表等于null；
全连接：select * from tbl_emp e left join testmysql.tbl_dept d on d.id = e.deptId union select * from tbl_emp e right join testmysql.tbl_dept d on d.id = e.deptId;

explain

id：select 的身份。
select_type：select 的类型。
table: 查询的表名。
partitions：匹配的分区。
type：join的类型。
possible_keys：可能的索引选择。
key：实际选择的索引。
key_len：选择索引的长度。
ref：列与索引的比。
rows：要检查的行数。
filtered：按照条件过滤的百分比。
Extra：额外的信息。

select_type Value
simple：简单查询，没有用到联合查询或子查询。
primary：最外层查询。
union：用到了查询或者更高级的查询。
dependent union：内查询取决于外查询。
union result：联合查询的结果。
subquery：在子查询中首先选择select。
dependent subquery：子查询依赖于外部查询。
derived: Derived table。
materialized: Materialized subquery。
uncacheable subquery：无法缓存结果的子查询，必须为外部查询的每一行重新计算。
uncacheable union：UNION中的第二个或更晚的选择属于不可缓存的子查询（请参阅UNCACHEABLE SUBQUERY）。

table
（union m,n）、（derived n）、（subquery n）。

type
possible_keys 、key。

索引
索引是帮助mysql高效获取数据的数据结构和快速排序。索引是以B树存储。
建立索引的7大原则：
1．选择唯一性索引
2．为经常需要排序、分组和联合操作的字段建立索引
3．为常作为查询条件的字段建立索引
4．限制索引的数目
5．尽量使用数据量少的索引
6．尽量使用前缀来索引
7．删除不再使用或者很少使用的索引

索引优势：提高了数据的检索效率，降低了数据库的IO成本。
通过对数据的排序，降低数据排序的成本，降低了CPU的消耗。
劣势：提高了查询效率，降低了更新表的效率。如：update，delete，insert。因为mysql不仅要保存数据，而且还要保存索引文件。

索引分类
单值索引：一个索引只能包含一列，一张表可以有多个单值索引。
唯一索引：索引列的值必须唯一，但是可以为空。
复合索引：
基本语法
创建：create [unique] index indexName on tableName(columnName(length));
alter tableName add [unique] index on tableName(columnName(length));
删除：drop index [indexName] on tableName;
查看：show index from tableName \G;

索引结构
BTree索引：检索原理。
建立索引的情况
主键自动建立唯一索引。
频繁作为查询条件的字段。
查询与其他表关联的字段，外键关系建立索引。
频繁更新的字段不适合建索引。
where条件中用不到的字段不创建索引。
查询中排序 的字段，排序字段通过索引可以大大提高检索效率。
查询中统计与分组字段。
单值/组合索引的选择（在高并发的情况下倾向于组合索引）。
不建索引的情况
表记录太少。
经常增删改的表。
数据重复且平均分布。

性能分析
mysql query optimer：
MySQL的瓶颈：
explain：
explain sql。

id


select type


type


优化至少到range，最好达到ref。

possible key

key最终用到那个索引

key_len

ref

row

Extra
user filesort: 文件内排序。这个说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取

using temporary：这意味着mysql对查询结果进行排序的时候使用了一张临时表。

using index：出现这个说明mysql使用了覆盖索引，避免访问了表的数据行，效率不错！



using where：这说明服务器在存储引擎收到行后将进行过滤。有些where中的条件会有属于索引的列，当它读取使用索引的时候，就会被过滤，所以会出现有些where语句并没有在extra列中出现using where这么一个说明。
using join buffer

索引优化
索引失效

索引优化口诀