MySQL高级学习笔记

文章目录

MySQL基础篇学习笔记

SQL性能下降的原因SQL的执行顺序 索引

索引的优劣势索引的分类索引的创建索引结构判断是否需要创建索引 EXPLAIN

EXPLAIN之idEXPLAIN之select_typeEXPLAIN之tableEXPLAIN之typeEXPLAIN之possible_keysEXPLAIN之keyEXPLAIN之key_len

MySQL基础篇学习笔记

---

SQL性能下降的原因

查询语句写的烂索引失败关联查询太多join（设计缺陷或不得已的需求）服务器调优及各个参数设置（缓冲、线程数等）

SQL的执行顺序

FROM 
ON 
 JOIN 
WHERe 
GROUP BY 
HAVINg 
SELECt
DISTINCT 
ORDER BY 
LIMIT 

索引

索引（Index）是帮助MySQL高效获取数据的数据结构。可以理解为“排好序的快速查找数据结构”。

除了数据以外，数据库还维护着一个满足特定查找的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存在磁盘中。

索引的优劣势

优势：

类似于图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。索引可以大大提高查询速度，但是会降低更新表的速度，如进行INSERT、UPDATE、DELETE。因为更新表时，不仅要保存数据，还要改索引。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立更优秀的索引。

索引的分类

单列索引：单列索引即一个索引只包含单个列。组合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合唯一索引：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

索引的创建

语法：

CREATE INDEX index_name ON table_name (column_name(length))
# 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

实例：

CREATE INDEX idx1 ON t1(id);# 单列索引
CREATE UNIQUE INDEX idx2 ON t1(nam(10));# 唯一索引
CREATE UNIQUE INDEX idx3 ON t1(id,nam(10));# 组合索引

索引结构

1. BTree索引
2. Hash索引
3. full-text全文索引
4. R-Tree索引

判断是否需要创建索引

需要创建索引的情况：

主键自动创建唯一索引频繁作为查询条件的字段应该创建索引查询中与其他表关联的字段，外键关系建立索引where条件里用不到的字段不创建索引单列、组合索引的选择问题查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。查询中统计或者分组字段

不需要创建索引的情况：

表记录太少经常增删改的字段。【索引增加查询速度，降低更新速度】如果某个字段包含太多重复的内容，那么建索引不会有太大的效果。【索引的选择性：索引列中不同值/表中总记录数，越接近1，索引效率越高】

EXPLAIN

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

作用：可以分析出以下信息

表的读取顺序数据读取操作的操作类型哪些索引可以被使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询

使用：

Explain + SQL语句  

返回一张表，表头信息依次为：

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra

EXPLAIN之id

主要用于分析表的读取顺序。

三种情况：

id全相同，执行顺序由上至下id全不同，如果是子查询，id的序号会递增。先执行子查询，也就是id值越大的优先级越高，越先被执行。id有相同也有不同，id相同的被分为一组，同一组内由上至下执行，不同组按照id从大到小执行。【derived_x表示该表来自于id为x的操作产生的临时表】

EXPLAIN之select_type

主要用于区别普通查询、联合查询、子查询等的复杂查询。

六种情况：

SIMPLE：简单的select查询，查询中不包含子查询或者UNIOn。PRIMARY：查询中若有子查询，PRIMARY是最外层查询。SUBQUERY：子查询DERIVED：在FROM列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里。UNIOn:若第二个select出现在UNIOn之后，则被标记为UNIOn。UNIOn RESULT：从UION表获取结果的SELECT。

实例参考博客：MySQL高级——Explain信息中select_type字段解释

EXPLAIN之table

说明当前这行数据是关于哪张表的。

EXPLAIN之type

显示查询使用了何种类型：
从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref级别。

system：表中只有一行数据（系统表）的情况，基本上不会出现，可以忽略不计。const：表示通过索引一次就找到了，const用于比较primary key或unique索引。因为只匹配一行数据，所以很快，如主键默认具有唯一索引，MySQL就能将该查询转换为一个常量。【场景：命中主键或唯一索引、被连接的部分是一个常量值(const)】eq_ref：唯一性索引扫描，多表连接时，对于前表中的每一行（row），对应后表只有一行被扫描，这类扫描的速度也非常的快。【场景：联表查询、命中主键或唯一索引】ref：非唯一性索引扫描，后表使用了普通非唯一索引，对于前表的每一行(row)，后表可能有多于一行的数据被扫描。ref每一次匹配可能有多行数据返回，虽然它比eq_ref要慢，但它仍然是一个很快的join类型。【场景：联表查询、普通非唯一索引】range：range比较好理解，它是索引上的范围查询，它会在索引上扫码特定范围内的值。【场景：>、>=、between and等】index：需要扫描索引上的全部数据。ALL：不建索引，全表扫描。

实例参考博客：Mysql Explain之type详解

EXPLAIN之possible_keys

显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

EXPLAIN之key

实际使用的索引，如果为NULL，则没有使用索引。
查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

EXPLAIN之key_len

key_len说明了查找时用到的索引长度，可以根据长度，推测多维索引用到了几维。