MySQL索引，锁，三大范式一篇搞定+面试索引18连环问

最新推荐文章于 2022-02-11 20:07:57 发布

老$¥

最新推荐文章于 2022-02-11 20:07:57 发布

阅读量164

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/m0_46266264/article/details/116168466

版权

MySQL 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

在这里插入图片描述
备注：牛客网收藏有答案。
添加链接描述

1. 索引是什么？

答：索引是排好序的快速查找的数据结构

2. 索引优缺点

答：下图2；

3. MySQL有哪几种索引类型

答：

        1、从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式，

		2、从应用层次来分：单值索引，唯一索引，复合索引。
		单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

		唯一索引：索引列的值必须唯一，但允许有空值

		复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
		3. 根据中数据的物理顺序与键值的逻辑（索引）顺序关系： 聚集索引，非聚集索引。

4. 说一说索引的底层实现

答：
在这里插入图片描述

在这里插入图片描述

5. 为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

在这里插入图片描述

6. 讲一讲聚簇索引与非聚簇索引？

在这里插入图片描述

create index index_col_name on table_name(column_name)

在这里插入图片描述

create index index_name on table(col1_name,col2_name)

在这里插入图片描述

PS:经常删改表不适合创建索引，是因为索引在提高了查询速度的同时降低了更新速度。在更新表时，MySQL不仅要保存数据，还要保存索引文件。

PS：如果某个数据列有大量重复内容，建立索引无实际效果。

在这里插入图片描述

MySQL性能分析之explain（是什么，能干嘛，怎么玩，各字段）

PS：MySQL Query Optimizer为下图

在这里插入图片描述

在这里插入图片描述
PS：explain玩儿法就是 Explain+SQL语句

在这里插入图片描述

PS：Linux系统进入MySQL：mysql -u用户名（我是root） -p密码，exit退出MySQL

在这里插入图片描述

explain作用（即各字段含义）：

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

在这里插入图片描述

PS：上面的type太多了，记忆的话记住下面（最好到最差）system>const>eq_ref>ref>range>index>ALL,至少range级别，最好达到ref

PS：ALL就是扫描全表

在这里插入图片描述

在这里插入图片描述
PS：比如查找名字为张三的人，最后可能有5个人都叫张三，这就是ref。

在这里插入图片描述

上图为没建索引之前，然后创建索引

create index idx_name on t2(content)

然后如下

在这里插入图片描述

6.6:index:index与ALL的区别是index类型只遍历索引树，这比ALL快，因为索引文件通常比数据文件小（虽然all与index都是遍历全表），但index是从索引中读取，all是从硬盘中读取。

在这里插入图片描述

PS：查询中若使用了覆盖索引，则该索引仅出现在key列表中

在这里插入图片描述

上图：ref为mytest.emp.deptno,mytest这个数据库里面的emp这张表内的deptno这一列

在这里插入图片描述

Extra里面比较重要的是using filesort,usiing tempoary,using index,剩下的了解就行。

PS：Using filesort(九死一生)
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
PS：最左法则：索引不能跳过，中断了就全扫了不走索引了（即ALL），

组合索引不能中断
如下图：order by col3和 order by cpl2,col3的区别
在这里插入图片描述

如下图，正好对应知识框架

在这里插入图片描述

在这里插入图片描述
PS：using temporary(十死无生)，

在这里插入图片描述

对应上图：查询中如果使用了覆盖索引，则该索引仅出现在key列表中

在这里插入图片描述

下图了解

在这里插入图片描述

索引优化案例

第一步建表
在这里插入图片描述

第二步：建完表以后查询，如下图

在这里插入图片描述

第三步：根据要求查询，结果如下图

在这里插入图片描述

第四步：explain上述查询语句，结果如下图

在这里插入图片描述

第五步：上图的Extra字段有filesort字段且type是ALL，需要优化——首先想到建立索引——show index from article,结果如下图

在这里插入图片描述

第六步：创建索引：create index index_name on table_name(col1,col2,col3),结果如下图

在这里插入图片描述

第七步：此时再次explain上述查询语句，结果如下图，可以看到type已经优化成了range,但是Extra的using filesort还在

在这里插入图片描述

第八步：将筛选条件中的范围筛选变为=（范围会让索引失效），结果如下图

在这里插入图片描述

第九步：分析上述问题，如下图

在这里插入图片描述

第十步：解决问题，删除原来的索引以后新建索引，即创建一个只包含category_id 和 views这两列的索引，如下图

在这里插入图片描述

两表优化案例

步骤1：创建表class与表book并且对2表 inner join一次，直观感受

在这里插入图片描述

步骤二：explain class表 left join book表，如下图

在这里插入图片描述

上图分析：两张表的type都是ALL,需要建立索引优化。结论如下两张图

在这里插入图片描述

索引失效

步骤一：如下两图，先建立一个表

在这里插入图片描述

第一步：建表如下二图

在这里插入图片描述

第二步：创建索引，如下图：create index idx_test03_c1234 on test03(c1,c2,c3,c4)

在这里插入图片描述

第三步：explain(PS:下图10张图，key_len为31代表1个，为62代表2个.93代表3个，124代表4个)

在这里插入图片描述

在这里插入图片描述
注意下图的using filesort

在这里插入图片描述

然后下面三图建表

在这里插入图片描述

order by满足两种情况会使用index方式排序，一种是order by字句使用索引最左前列，另外一种是使用了where子句和order by 子句条件列组合满足索引最左前列。

在这里插入图片描述

在生产环境中，如果要手工分析日志，查找，分析SQL，是个体力活，MySQL提供了日志分析工具mysqldumpslow

在这里插入图片描述

上图的DELIMITER $$表示最后要有个END $才算结束，即$ 取代分号作为结束符号，在begin和end中间的语句最后会一起执行，如下图输入分号以后回车会认为你的语句未结束，要输入$$以后回车语句才算结束。

在这里插入图片描述
这个时候再DELIMITER ;就什么也没有

在这里插入图片描述

诊断SQL：show profile cpu,block io for query

在这里插入图片描述

锁

在这里插入图片描述

对数据操作类型可分为读/写锁，对数据操作的粒度分为表锁和行锁

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前写操作没有完成时，它会阻断其他写锁和读锁

还有页锁，很少用

在这里插入图片描述

# SESSION1

# 问题1：SESSION1为mylock表加了读锁，可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了读锁，不可以修改mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

# 问题3：SESSION1为mylock表加了读锁，不可以读其他的表！
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES


# SESSION2

# 问题1：SESSION1为mylock表加了读锁，SESSION2可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了读锁，SESSION2修改mylock表会被阻塞，需要等待SESSION1释放mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题3：SESSION1为mylock表加了读锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    1 |
|      7 |    4 |
|      8 |    4 |
|      9 |    5 |
|      5 |    6 |
|     17 |    6 |
|     15 |    8 |
+--------+------+
24 rows in set (0.00 sec)

在这里插入图片描述

# SESSION1

# 问题1：SESSION1为mylock表加了写锁，可以读mylock的表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了写锁，可以修改mylock表!
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 问题3：SESSION1为mylock表加了写锁，不能读其他表!
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

# SESSION2

# 问题1：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> SELECT * FROM `mylock`;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题2：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题3：SESSION1为mylock表加了写锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    1 |
|      7 |    4 |
|      8 |    4 |
|      9 |    5 |
|      5 |    6 |
|     17 |    6 |
|     15 |    8 |
+--------+------+
24 rows in set (0.00 sec)

在这里插入图片描述

SESSION1

SESSION1対test_innodb_lock表做写操作，但是没有commit。

执行修改SQL之后，查询一下test_innodb_lock表，发现数据被修改了。

mysql> UPDATE test_innodb_lock SET b = ‘88’ WHERE a = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM test_innodb_lock;
±-----±-----+
| a | b |
±-----±-----+
| 1 | 88 |
| 2 | 3 |
| 3 | 4000 |
| 4 | 5000 |
| 5 | 6000 |
| 6 | 7000 |
| 7 | 8000 |
| 8 | 9000 |
±-----±-----+
8 rows in set (0.00 sec)

# SESSION2 

# SESSION2这时候来查询test_innodb_lock表。
# 发现SESSION2是读不到SESSION1未提交的数据的。
mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 se

在这里插入图片描述

锁总结

在这里插入图片描述

数据库三大范式

在这里插入图片描述

老$¥

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引，锁，三大范式一篇搞定+面试索引18连环问

备注：牛客网收藏有答案。PS:经常删改表不适合创建索引，是因为索引在提高了查询速度的同时降低了更新速度。在更新表时，MySQL不仅要保存数据，还要保存索引文件。PS：如果某个数据列有大量重复内容，建立索引无实际效果。MySQL性能分析之explain（是什么，能干嘛，怎么玩，各字段）PS：MySQL Query Optimizer为下图PS：explain玩儿法就是 Explain+SQL语句PS：Linux系统进入MySQL：mysql -u用.
复制链接

扫一扫

专栏目录