Mysql高级

更新丢失

@H_197_46@

脏读

mysql锁详解： https://blog.csdn.net/u013967628/article/details/85293041 索引 MySQL之索引： https://blog.csdn.net/single_0910/article/details/113828569 MySQL之查询优化（EXPLAIN）： https://blog.csdn.net/single_0910/article/details/113836467 Mysql索引面试题： https://www.cnblogs.com/williamjie/p/11187470.html https://www.cnblogs.com/blogtech/p/10530794.html MySQL索引原理及BTree（B-/+Tree）结构详解： https://blog.csdn.net/u013967628/article/details/84305511#B%2BTree 索引是排序号的࿰c;能后快速查找数据的数据结构࿰c;能够帮助MySQL高效获取数据。 下图是一种可能的索引方式：

左边是数据表࿰c;一共有两列七条记录࿰c;最左边的是数据记录的物理地址。

为了加快 Col2 的查找࿰c;可以维护一个右边所示的二叉查找树࿰c;每个节点分别包含索引键值和一个指向对应数据记录的地址指针࿰c;这样就可以运用 二叉查找在一定的复杂度内获取到相应数据࿰c;从而快速检索出符合条件的记录。

一般来说索引本身也很大࿰c;不可能全部存储在内存中࿰c;因此索引往往以索引文件的形式存储的磁盘上。 索引如果没有特别指明࿰c;都是指B-Tree（多路搜索树࿰c;并不一定是二叉树）结构组织的索引。 其中聚集索引࿰c;次要索引࿰c;覆盖索引࿰c;复合索引࿰c;前缀索引࿰c;唯一索引默认都是使用B+树索引。

除了B+树索引࿰c;还有哈希索引。 索引的分类 单值索引：一个索引只包含单个列࿰c;一个表可以有多个单列索引 唯一索引：索引内的值必须唯一࿰c;但允许有空值 复合索引：一个索引包含多个列 建议：一张表建的索引最好不要超过5个！

/* 基本语法 */

/* 1、创建索引 [UNIQUE]可以省略*/ /* 如果只写一个字段就是单值索引࿰c;写多个字段就是复合索引 */ CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));

/* 2、删除索引 */ DROP INDEX [indexName] ON tabName;

/* 3、查看索引 / / 加上G就可以以列的形式查看了 不加G就是以表的形式查看 */ SHOW INDEX FROM tabName G;

7.3MySQL索引数据结构

索引数据结构：

BTree索引。 Hash索引。 Full-text全文索引。 R-tree索引。

7.4.哪些情况需要建索引

主键自动建立主键索引（唯一 + 非空）。 频繁作为查询条件的字段应该创建索引。 查询中与其他表关联的字段࿰c;外键关系建立索引。 查询中排序的字段࿰c;排序字段若通过索引去访问将大大提高排序速度。 查询中统计或者分组字段（group by也和索引有关）。

7.5.那些情况不要建索引

记录太少的表。

经常增删改的表。

频繁更新的字段不适合创建索引。

Where条件里用不到的字段不创建索引。

假如一个表有10万行记录࿰c;有一个字段A只有true和false两种值࿰c;并且每个值的分布概率大约为50%࿰c;那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录࿰c;表索引列有1980个不同的值࿰c;那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1࿰c;这个索引的效率就越高。

8.性能分析

8.1.EXPLAIN简介 EXPLAIN是什么？

EXPLAIN：SQL的执行计划࿰c;使用EXPLAIN关键字可以模拟优化器执行SQL查询语句࿰c;从而知道MySQL是如何处理SQL语句的。

EXPLAIN怎么使用？

语法：explain + SQL。

@H_385_5@mysql> explain SELEct * from pms_category G; *************************** 1. row *************************** id: 1 SELEct_type: SIMPLE table: pms_category partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1425 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 seC)

EXPLAIN能干嘛？

可以查看以下信息：

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

8.2.EXPLAIN字段

id

id：表的读取和加载顺序。

• 值有以下三种情况：

id相同࿰c;执行顺序由上至下。 id不同࿰c;如果是子查询࿰c;id的序号会递增࿰c;id值越大优先级越高࿰c;越先被执行。 id相同不同࿰c;同时存在。永远是id大的优先级最高࿰c;id相等的时候顺序执行。 SELEct_type

SELEct_type：数据查询的类型࿰c;主要是用于区别࿰c;普通查询、联合查询、子查询等的复杂查询。

SIMPLE：简单的SELECT查询࿰c;查询中不包含子查询或者UNION 。 PRIMary：查询中如果包含任何复杂的子部分࿰c;最外层查询则被标记为PRIMary。 SUBQUERY：在SELECT或者WHERE子句中包含了子查询。 DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)࿰c;R_853_11845@ySQL会递归执行这些子查询࿰c;把结果放在临时表中。 UNION：如果第二个SELECT出现在UNION之后࿰c;则被标记为UNION；若UNION包含在FROM子句的子查询中࿰c;外层SELECT将被标记为DERIVED。 UNION RESULT：从UNION表获取结果的SELECT。 type

type：访问类型排列。

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引࿰c;其他级别都用到索引了。

一般来说࿰c;得保证查询至少达到range级别࿰c;最好达到ref。

system：表只有一行记录（等于系统表）࿰c;这是const类型的特例࿰c;平时不会出现࿰c;这个也可以忽略不计。 const：表示通过索引一次就找到了࿰c;const用于比较priMary key或者unique索引。因为只匹配一行数据࿰c;所以很快。如将主键置于where列表中࿰c;R_853_11845@ySQL就能将该查询转化为一个常量。 eq_ref：唯一性索引扫描࿰c;读取本表中和关联表表中的每行组合成的一行࿰c;查出来只有一条记录。除 了 system 和 const 类型之外, 这是最好的联接类型。 ref：非唯一性索引扫描࿰c;返回本表和关联表某个值匹配的所有行࿰c;查出来有多条记录。 range：只检索给定范围的行࿰c;一般就是在WHERE语句中出现了betweeN、< >、in等的查询。这种范围扫描索引比全表扫描要好࿰c;因为它只需要开始于索引树的某一点࿰c;而结束于另一点࿰c;不用扫描全部索引。 index：Full Index Scan࿰c;全索引扫描࿰c;index和ALL的区别为index类型只遍历索引树。也就是说虽然ALL和index都是读全表࿰c;但是index是从索引中读的࿰c;ALL是从磁盘中读取的。 ALL：Full Table Scan࿰c;没有用到索引࿰c;全表扫描。

possible_keys 和 key

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

key：实际使用的索引。如果为null࿰c;则没有使用索引。查询中如果使用了覆盖索引࿰c;则该索引仅仅出现在key列表中。

key_len

key_len：表示索引中使用的字节数࿰c;可通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段的最大可能长度࿰c;并非实际使用长度࿰c;即key_len是根据表定义计算而得࿰c;不是通过表内检索出的。在不损失精度的情况下࿰c;长度越短越好。

key_len计算规则：https://blog.csdn.net/qq_34930488/article/details/102931490

@H_442_175@mysql> desc pms_category;
+---------------+------------+------+-----+---------+----------------+
| Field         | Type       | Null | Key | Default | Extra          |
+---------------+------------+------+-----+---------+----------------+
| cat_id        | bigint(20) | NO   | PRI | NULL    | auto_increment |
| name          | char(50)   | YES  |     | NULL    |                |
| parent_cid    | bigint(20) | YES  |     | NULL    |                |
| cat_level     | int(11)    | YES  |     | NULL    |                |
| show_status   | Tinyint(4) | YES  |     | NULL    |                |
| sort          | int(11)    | YES  |     | NULL    |                |
| icon          | char(255)  | YES  |     | NULL    |                |
| product_unit  | char(50)   | YES  |     | NULL    |                |
| product_count | int(11)    | YES  |     | NULL    |                |
+---------------+------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)


mysql> explain SELEct cat_id from pms_category where cat_id between 10 and 20 G;
*************************** 1. row ***************************
           id: 1
  SELEct_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: range
possible_keys: PRIMary
          key: PRIMary  # 用到了主键索引࿰c;通过查看表结构知道࿰c;cat_id是bigint类型࿰c;占用8个字节
      key_len: 8        # 这里只用到了cat_id主键索引࿰c;所以长度就是8！
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

ref

ref：显示索引的哪一列被使用了࿰c;如果可能的话࿰c;是一个常数。哪些列或常量被用于查找索引列上的值。

rows

rows：根据表统计信息及索引选用情况࿰c;大致估算出找到所需的记录需要读取的行数。

Extra

Extra：包含不适合在其他列中显示但十分重要的额外信息。

Using filesort：说明MySQL会对数据使用一个外部的索引排序࿰c;而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。 #排序没有使用索引 mysql> explain SELEct name from pms_category where name=‘Tangs’ order by cat_level G *************************** 1. row *************************** id: 1 SELEct_type: SIMPLE table: pms_category partitions: NULL type: ref possible_keys: idx_name_parentCid_catLevel key: idx_name_parentCid_catLevel key_len: 201 ref: const rows: 1 filtered: 100.00 Extra: Using where; Using index; Using filesort 1 row in set, 1 warning (0.00 seC)

#~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ #排序使用到了索引

@H_385_5@mysql> explain SELEct name from pms_category where name=‘Tangs’ order by parent_cid,cat_levelG *************************** 1. row *************************** id: 1 SELEct_type: SIMPLE table: pms_category partitions: NULL type: ref possible_keys: idx_name_parentCid_catLevel key: idx_name_parentCid_catLevel key_len: 201 ref: const rows: 1 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 seC) Using temporary：使用了临时表保存中间结果࿰c;R_853_11845@ySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。

Using index：表示相应的SELECT操作中使用了覆盖索引࿰c;避免访问了表的数据行࿰c;效率不错！如果同时出现Using where࿰c;表示索引被用来执行索引键值的查找；如果没有同时出现Using where࿰c;表明索引用来读取数据而非执行查找动作。

聚簇索引与非聚簇索引（也叫二级索引）:https://www.jianshu.com/p/fa8192853184