![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/6db17647bef88d1679a59b8c6ae4ba39.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/3473ea5105421eff61b9896e02301e4b.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/15a1001fe03be78fa93e4637a8bf35b9.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/cc024aaaccee4ca53e018a095e98d566.png)
更新丢失
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/a5061f1481cc9a3c14d3224b4f747728.png)
脏读
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/78d4cc3eea108dae26335ba524492639.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/daa1ed1e662587ec8a7506c510961a91.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/3aa01661095dacc190c3de605f9dd624.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/44c7030bce4a943aef9dcf0a39672ad0.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/03a82cca9b2be3be63dcc35fd214e5b0.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/62b1660cf839fb4aa20e4e2f08c9500b.png)
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
索引是排序号的,能后快速查找数据的数据结构,能够帮助MySQL高效获取数据。
下图是一种可能的索引方式:
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址。
为了加快 Col2 的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录的地址指针,这样就可以运用 二叉查找在一定的复杂度内获取到相应数据,从而快速检索出符合条件的记录。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。
索引如果没有特别指明,都是指B-Tree(多路搜索树,并不一定是二叉树)结构组织的索引。
其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引。
除了B+树索引,还有哈希索引。
索引的分类
单值索引:一个索引只包含单个列,一个表可以有多个单列索引
唯一索引:索引内的值必须唯一,但允许有空值
复合索引:一个索引包含多个列
建议:一张表建的索引最好不要超过5个!
/* 基本语法 */
/* 1、创建索引 [UNIQUE]可以省略*/
/* 如果只写一个字段就是单值索引,写多个字段就是复合索引 */
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.哪些情况需要建索引
主键自动建立主键索引(唯一 + 非空)。
频繁作为查询条件的字段应该创建索引。
查询中与其他表关联的字段,外键关系建立索引。
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。
查询中统计或者分组字段(group by也和索引有关)。
7.5.那些情况不要建索引
记录太少的表。
经常增删改的表。
频繁更新的字段不适合创建索引。
Where条件里用不到的字段不创建索引。
假如一个表有10万行记录,有一个字段A只有true和false两种值,并且每个值的分布概率大约为50%,那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1,这个索引的效率就越高。
8.性能分析
8.1.EXPLAIN简介
EXPLAIN是什么?
EXPLAIN:SQL的执行计划,使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL语句的。
EXPLAIN怎么使用?
语法:explain + SQL。
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相同,执行顺序由上至下。
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。
id相同不同,同时存在。永远是id大的优先级最高,id相等的时候顺序执行。
select_type
select_type:数据查询的类型,主要是用于区别,普通查询、联合查询、子查询等的复杂查询。
SIMPLE:简单的SELECT查询,查询中不包含子查询或者UNION 。
PRIMARY:查询中如果包含任何复杂的子部分,最外层查询则被标记为PRIMARY。
SUBQUERY:在SELECT或者WHERE子句中包含了子查询。
DERIVED:在FROM子句中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中。
UNION:如果第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED。
UNION RESULT:从UNION表获取结果的SELECT。
type
type:访问类型排列。
从最好到最差依次是:system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引,其他级别都用到索引了。
一般来说,得保证查询至少达到range级别,最好达到ref。
system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计。
const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转化为一个常量。
eq_ref:唯一性索引扫描,读取本表中和关联表表中的每行组合成的一行,查出来只有一条记录。除 了 system 和 const 类型之外, 这是最好的联接类型。
ref:非唯一性索引扫描,返回本表和关联表某个值匹配的所有行,查出来有多条记录。
range:只检索给定范围的行,一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好,因为它只需要开始于索引树的某一点,而结束于另一点,不用扫描全部索引。
index:Full Index Scan,全索引扫描,index和ALL的区别为index类型只遍历索引树。也就是说虽然ALL和index都是读全表,但是index是从索引中读的,ALL是从磁盘中读取的。
ALL:Full Table Scan,没有用到索引,全表扫描。
possible_keys 和 key
possible_keys:显示可能应用在这张表中的索引,一个或者多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。
key:实际使用的索引。如果为NULL,则没有使用索引。查询中如果使用了覆盖索引,则该索引仅仅出现在key列表中。
key_len
key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。在不损失精度的情况下,长度越短越好。
key_len计算规则:https://blog.csdn.net/qq_34930488/article/details/102931490
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 # 用到了主键索引,通过查看表结构知道,cat_id是bigint类型,占用8个字节
key_len: 8 # 这里只用到了cat_id主键索引,所以长度就是8!
ref: NULL
rows: 11
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
ref
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
rows
rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录需要读取的行数。
Extra
Extra:包含不适合在其他列中显示但十分重要的额外信息。
Using filesort:说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。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)
#~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
#排序使用到了索引
mysql> explain select name from pms_category where name=‘Tangs’ order by parent_cid,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
1 row in set, 1 warning (0.00 sec)
Using temporary:使用了临时表保存中间结果,MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。
Using index:表示相应的SELECT操作中使用了覆盖索引,避免访问了表的数据行,效率不错!如果同时出现Using where,表示索引被用来执行索引键值的查找;如果没有同时出现Using where,表明索引用来读取数据而非执行查找动作。
聚簇索引与非聚簇索引(也叫二级索引):https://www.jianshu.com/p/fa8192853184