读书笔记 —— 《深入浅出MySQL数据库开发、优化与管理维护第2版》

最新推荐文章于 2024-06-26 11:12:53 发布

lxxxxxl_

最新推荐文章于 2024-06-26 11:12:53 发布

阅读量2.9k

点赞数 1

分类专栏：读书笔记 mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_36045946/article/details/82971909

版权

读书笔记同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

14.1 LOCK TABLE 和 UNLOCK TABLE

18.5 MYSQL中能使用索引的典型场景

18.6 存在索引但不能使用索引的典型场景

第1章 Mysql的安装与配置

1.1 启动和关闭Mysql服务

Windows(管理员命令行下)

启动服务：net start mysql
关闭服务：net stop mysql

Linux

启动服务：service mysql start
重启服务：service mysql restart
关闭服务：service mysql stop

第2章 SQL基础

2.1 SQL分类

DDL(Data Definition Languages)：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象，常用的关键字包括 create、drop、alter
DML(Data Manipulation Language)：数据操纵语句，用于添加、删除和更新数据库记录，并检查数据完整性，常用关键词包括 insert、delete、update等
DQL(Data Query Language)：数据查询语句，用于查询数据库记录，常用关键词包括 select
DCL(Data Control Language)：数据控制语句，用于控制不同数据段之间的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别，主要关键词包括 grant、revoke

2.2 常用语句

DDL语句

创建数据库：CREATE DATABASE db_name;
查看全部数据库：SHOW DATABASES;
选择数据库：USE db_name;
查看全部数据表：SHOW TABLES;
删除数据库：DROP DATABASE db_name;
创建数据表：CREATE TABLE [IF NOT EXISTS] ` table_name ` (

column_name data_type, ...

)ENGINE=InnoDB DEFAULT CHARSET=utf-8

查看数据表定义：DESC table_name; / SHOW CREATE TABLE table_name \G;
删除数据表：DROP TABLE table_name;
清空数据表：TRUNCATE TABLE table_name;
修改表字段：ALTER TABLE table_name MODIFY [COLUMN] col_definition [FIRST | AFTER col_name];
增加表字段：ALTER TABLE table_name ADD [COLUMN] col_definition [FIRST | AFTER col_name];
删除表字段：ALTER TABLE table_name DROP [COLUMN] col_name;
字段改名：ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_definition [FIRST | AFTER col_name];
更改表名：ALTER TABLE table_name RENAME new_table_name;

DML语句

增：INSERT INTO table_name (field1,field2 ...) VALUES (value1,value2 ...);
删：DELETE FROM table_name [WHERE CONDITION];
改：UPDATE table_name SET field1 = value1, field2 = value2 ... [WHERE CONDITION];

DQL语句

查：SELECT * FROM table_name [WHERE CONDITON];
查询不重复的记录：SELECT DISTINCT field FROM table_name;
排序：SELECT * FROM table_name [WHERE CONDITION] [ORDER BY field [DESC / ASC]];
截取：SELECT ... [LIMIT offset_start, row_count]; // offset_start：起始偏移量(默认为0)，row_count：显示的行数
聚合：

左/右连接：SELECT field FROM table_name1 LEFT/RIGHT JOIN table_name2 ON CONDITION;

第3章 Mysql支持的数据类型

3.1 数值类型

Mysql支持在类型名称后面的小括号内指定显示宽度，例如int(5)表示当数值宽度小于5位的时候再数字前面填满宽度，如果不显示指定宽度则默认为int(11)
所有的整数类型都有一个可选属性UNSIGNED(无符号)，如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项
AUTO_INCREMENT：在需要产生唯一标识符或顺序值时，可用此属性，必须定义为NOT NULL
float和double在不指定精度时，默认会按照实际的精度(由实际的硬件和操作系统决定)来显示，而decimal在不指定精度时，默认的整数位为10，默认小数位为0

3.2 日期时间类型

3.3 字符串类型

CHAR是固定长度，删除尾部空格；VARCHAR是可变长度，保留尾部空格

第4章 Mysql中的运算符

4.1 运算符优先级

第7章表类型(存储引擎)的选择

7.1 各种存储引擎的特性

7.2 MyISAM

7.3 InnoDB

7.4 MEMORY

MEMORY存储引擎使用存在于内存的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常地块，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。

第8章选择合适的数据类型

8.1 CHAR和VARCHAR

8.2 TEXT和BLOB

BLOB和TEXT值会引起一些性能问题，特别是在执行了大量删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用 OPTIMIZE TABLE table_name; 对表进行碎片整理，避免因为“空洞”导致性能问题。

8.3 浮点数和定点数

8.4 日期类型选择

第10章索引的设计与使用

MyISAM和InnoDB存储引擎默认使用BTREE索引

可以用 explain 语句查看查询语句是否使用索引

MEMORY可以使用HASH索引，只能用=或<=>操作符的等式比较

新增索引：ALTER TABLE table_name ADD INDEX index_name (column_list);
删除索引：ALTER TABLE table_name DROP INDEX index_name;
查看索引：SHOW INDEX FROM table_name;

第13章触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

第14章事务控制和锁定语句

14.1 LOCK TABLE 和 UNLOCK TABLE

LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，知道可以获取所有锁定为止

UNLOCK TABLE 可以释放当前线程获得的任何锁定

当前线程执行另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁

14.2 事务控制

第17章 Mysql分区

17.1 概述

分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上这个表可能由数10个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑

17.2 分区类型

第18章 SQL优化

18.1 定位执行效率低的SQL语句

18.2 EXPLAIN

字段解析

select_type：表示SELECT的类型，常见的区志有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等
table：输出结果集的表
type；表示MYSQL在表中找到所需行的方式，或者叫访问类型。常见类型有
- ALL：全盘扫描，MYSQL遍历全表来找到匹配的行
- index：索引全扫描，MYSQL遍历整个索引来查询匹配的行
- range：索引范围扫描，常见于<、<=、>、>=、between等操作符
- ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行
- eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用primaty key 或者 unique index 作为关联条件
prossible_keys：表示查询时可能使用的索引
key：表示实际使用的索引
key_len：使用到索引字段的长度
rows：扫描行的数量
Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

explain patitions：查看SQL所访问的分区

18.3 索引的存储分类

18.4 B-Tree索引

B-Tree索引是最常见的索引，构造类似二叉树，能根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行。B代表的是平衡树(balanced)，而不是二叉树(binary)。B-Tree索引并不是一棵二叉树。

18.5 MYSQL中能使用索引的典型场景

匹配全值(Match the full value)：对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件
匹配值的范围查询(Match a range of values)：对索引的值能够进行范围查找
匹配最左前缀(Match a leftmost prefix)：仅仅使用索引中最左边列进行查找，比如在col1 + col2 + col3 字段上的联合索引能够被包含col1、(col1 + col2)、(col1 + col2 + col3)的等值查询利用到，可是不能够被col2、(col2 + col3)的等值查询利用到。最左匹配原则可以算是Mysql中的B-Tree索引使用的首要原则
仅仅对索引进行查询(Index only query)：当查询的列都在索引的字段中时，索引的效率更高。避免使用select *，直接查询所需要的列
匹配列前缀(Match a column prefix)，仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找
能够实现索引匹配部分精确而其他部分进行范围匹配

18.6 存在索引但不能使用索引的典型场景

以%开头的LIKE查询不能够利用B-Tree索引，一般使用全文索引解决
数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在where条件中把字符常量用引号引起来，否则即使这个列上有索引，Mysql也不会用到，因为Mysql默认把输入的常量值进行转换以后才进行检索
复合索引的情况下，加入查询条件不包含索引列最左边部分，即不满足最左原则Leftmost，是不会使用复合索引的
如果Mysql估计使用索引比全表扫描更慢，则不使用索引
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。因为or后面的条件列没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加I/O访问，一次全表扫描过滤条件就够了

18.7 查看索引使用情况

使用：show status like 'Handler_read&';
Handler_read_key：如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低表明增加索引得到的性能改善不高，因为索引并不经常使用
Handler_read_rnd_next：值高意味着查询运行低效，并且应建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，值会很高，说明表索引不正确或写入的查询没有利用索引

18.8 两个简单实用的优化方法

定期分析表和检查表：ANALYZE TABLE table_name / CHECK TABLE table_name
定期优化表：OPTIMIZE TABLE table_name 可以将表中的空间碎片进行合并，并且可以消除由于删除或更新造成的空间浪费，但 OPTIMIZE TABLE 命令只对MyISAM、BDB和InnoDB表起作用
对于InnoDB引擎的表来说，通过设置 innodb_file_pre_table 参数，设置InnoDB为独立表空间模式，这样每个数据库的每个表都会生成一个独立的ibd文件，用于存储标的数据和索引，这样可以一定程度上减轻InnoDB表的空间回收问题。另外，在删除大量数据后，InnoDB表可以通过alter table 但是不修改引擎的方式来回收不用的空间：ALTER TABLE table_name ENGINE=InnoDB;
ANALYZE、CHECK、OPTIMIZE、ALTER TABLE 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作

18.9 常用的SQL优化

大批量插入数据

MyISAM：

ALTER TABLE table_name DISABLE KEYS;
load data infile ...
ALTER TABLE table_name ENABLE KEYS;

InnoDB：

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率
在倒入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性检验，可以提高导入效率
如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率

优化INSERT语句

如果可以同时从一个客户端插入很多行，应尽量使用多个值表的INSERT语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个INSERT语句块

优化ORDER BY语句

Mysql有两种排序方式

通过有序索引顺序扫描直接返回有序数据，这种方式在使用 EXPLAIN 分析查询时 Extra 显示为Using Index
第二种是通过返回数据进行排序，也就是通常说的 Filesort 排序

优化方案：尽量减少额外的排序，通过索引直接返回有序数据。WHERE 条件和 ORDER 条件使用相同的索引，并且ORDER BY 的顺序和索引顺序相同，并且ORDER BY 的字段都是升序或者都是降序。否则肯定需要额外的排序，这样就会出现Filesort

优化GROUP BY语句

如果查询包括GROUP BY 但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL 禁止排序

e.g. explain select payment_date, sum(amont) from payment group by payment_date order by null\G;

优化OR条件

如果要使用索引，OR之间的每个条件都必须用到索引，如果没有索引，应该考虑增加索引。不能是复合索引

用RAND()提取随机行

随机抽取5行：select * from category order by rand() limit 5;

第19章优化数据库对象

优化表的数据

SELECT * FROM table_name PROCEDURE ANALYSE();

使用PROCEDURE ANALYSE(); 对当前应用的表进行分析，该函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施优化。

通过拆分提高表的访问效率

垂直拆分：把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。如果一个表中某些列常用，而另一些列不常用，则可以采用垂直拆分，另外，垂直拆分可以使得数据行变小，一个数据页就能存放更多数据，在查询时就会减少I/O次数。其缺点是需要管理冗余项，查询所有数据时需要联合(JOIN)操作
水平拆分：根据一列或多列数据的值把数据行放到两个独立的表中。水平拆分会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要UNION操作

反规范化

反规范化的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，响应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。

使用中间表提高统计查询速度

第20章锁问题

20.1 MySQL锁概述

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高

20.2 MyISAM表锁

MySQL表级锁的所模式

MySQL的表级锁有两种模式：表共享读锁和表独占写锁

可见，对MySIM表的读操作，不会阻塞其他用户对同一表的读操作，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作

如何加表锁

MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给设计的表加写锁

LOCK TABLES table_name read/write [local];

local：允许并发插入
在用LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及的表，并且MySQL不支持锁升级，也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作，在自动加锁的情况下也是如此，MyISAM总是一次获得SQL语句所需要的全部锁，这也是MyISAM表不会出现死锁的原因

并发插入

MyISAM有一个系统变量 concurrent_insert，专门用以控制其并发插入行为

值为0时，不允许并发插入
值为1时，默认设置。如果MyISAM表中没有空间（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录
值为2时，无论MyISAM表中有没有空间，都允许在表尾并发插入记录

MyISAM的锁调度

20.3 事务

事务及其ACID属性

并发处理事务带来的问题

事务隔离

20.4 InnoDB行锁

行锁模式

加锁方式

共享锁(S)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
排他锁(X)：SELECT * FROM table_name WHERE ... FOR UPDATE;

InnnoDB行锁实现方式

在不通过索引条件查询时，InnoDB会锁定表中的所有记录
由于Mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的，应用设计时应考虑这一点
当表有多个索引时，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁
即便在条件中使用了索引字段，但是否使用索引来检索数据是由Mysql通过判断不同执行计划的代价来决定的，如果Mysql认为全表扫描效率更高，比如对一些很小的表，就不会使用索引，这种情况InnoDB也会对所有记录加锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确定是否真正使用了索引

Next-Key锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙锁，InnnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的Next-Key锁

20.5 InnoDB表锁

lxxxxxl_

关注

1
点赞
踩
14

收藏

觉得还不错? 一键收藏
2
评论
读书笔记 —— 《深入浅出MySQL数据库开发、优化与管理维护第2版》

目录第1章 Mysql的安装与配置1.1 启动和关闭Mysql服务第2章 SQL基础2.1 SQL分类2.2 常用语句第3章 Mysql支持的数据类型3.1 数值类型3.2 日期时间类型3.3 字符串类型第4章 Mysql中的运算符4.1 运算符优先级第7章表类型(存储引擎)的选择7.1 各种存储引擎的特性7.2 MyISAM7.3 I...
复制链接

扫一扫