MySQL相关

Li_XiaoJin

发布于 2022-06-10 20:40:26

4630

发布于 2022-06-10 20:40:26

文章被收录于专栏：Lixj's BlogLixj's Blog

整理MySQL相关的知识。

1.SQL查询之语法顺序和执行顺序（重要）

MySQL查询语法顺序

SELECT
FROM
LEFT JOIN
ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

执行顺序示例SQL： SELECT * FROM user LEFT JOIN order ON user.id = order.uid WHERE order.price > 1000 GROUP BY user.name HAVING count(1) > 5 ORDER BY user.name LIMIT 0,10

FROM（将最近的两张表，进行笛卡尔积）---VT1
ON（将VT1按照它的条件进行过滤）---VT2
LEFT JOIN（保留左表的记录）---VT3
WHERE（过滤VT3中的记录）--VT4…VTn
GROUP BY（对VT4的记录进行分组）---VT5
HAVING（对VT5中的记录进行过滤）---VT6
SELECT（对VT6中的记录，选取指定的列）--VT7
ORDER BY（对VT7的记录进行排序）--游标
LIMIT（对排序之后的值进行分页）

WHERE条件执行顺序（影响性能）

MYSQL：从左往右去执行WHERE条件的。
Oracle：从右往左去执行WHERE条件的。

结论：写WHERE条件的时候，优先级高的部分要去编写过滤力度最大的条件语句。

2.MySQL索引

介绍

使用索引的主要目的是为了优化查询速度
索引是一种特殊的文件或者叫数据结构(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引的分类索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引

索引的分类

单列索引：
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
唯一索引：索引列中的值必须是唯一的，但是允许为空值，
主键索引：是一种特殊的唯一索引，不允许有空值。
组合索引
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。
空间索引：不做介绍，一般使用不到。

索引的使用

创建索引

单列索引之普通索引 CREATE INDEX index_name ON table(column(length)) ALTER TABLE table_name ADD INDEX index_name ON (column(length))
单列索引之唯一索引 CREATE UNIQUE INDEX index_name ON table(column(length))
单列索引之全文索引 CREATE FULLTEXT INDEX index_name ON table(column(length))
联合索引 ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))
删除索引 DROP INDEX index_name ON table

3.MySQL性能优化之慢查询

性能优化的思路

首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句
其次使用explain命令去查看有问题的SQL的执行计划
最后可以使用show profile[s] 查看有问题的SQL的性能使用情况

简单介绍
- 数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的 SQL。
- MySQL 数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL，这将极大程度帮助我们快速定位到症结所在，以便对症下药。
- 至于查询时间的多少才算慢，每个项目、业务都有不同的要求。
- 传统企业的软件允许查询时间高于某个值，但是把这个标准放在互联网项目或者访问量大的网站上，估计就是一个bug，甚至可能升级为一个功能性缺陷。
- MySQL的慢查询日志功能，默认是关闭的，需要手动开启。
开启慢查询功能 show variables like '%slow_query_log%'; show variables like '%long_query_time%';

参数说明：

slow_query_log ：是否开启慢查询日志，ON 为开启，OFF 为关闭，如果为关闭可以开启。
log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志，单位为秒。
临时开启慢查询功能在 MySQL 执行 SQL 语句设置，但是如果重启 MySQL 的话将失效set global slow_query_log = ON;set global long_query_time = 1;

永久开启慢查询功能修改/etc/my.cnf配置文件，重启 MySQL, 这种永久生效.[mysqld]slow_query_log = ONslow_query_log_file = /var/log/mysql/slow.loglong_query_time = 5

执行： select sleep(5); 查看日志： tail -100f lixj-server-01-slow.log

/usr/local/mysql/bin/mysqld, Version: 5.7.24 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2020-12-01T08:32:49.043023Z
# User@Host: root[root] @  [113.118.184.243]  Id:   927
# Query_time: 11.000212  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use lixj;
SET timestamp=1606811569;
select sleep(11);
/usr/local/mysql/bin/mysqld, Version: 5.7.24 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument

格式说明：

* 第一行,SQL查询执行的时间
* 第二行,执行SQL查询的连接信息，用户和连接IP
* 第三行,记录了一些我们比较有用的信息，如下解析
    Query_time,这条SQL执行的时间,越长则越慢
    Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
    Rows_sent,查询返回的行数
    Rows_examined,查询检查的行数，越长就当然越费时间
* 第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。
* 第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长。

MySQL性能优化细节
1. 合理的创建及使用索引（考虑数据的增删情况）
2. 合理的冗余字段（尽量建一些大表，考虑数据库的三范式和业务设计的取舍）
3. 使用SQL要注意一些细节：select语句中尽量不要使用、count()，WHERE语句中尽量不要使用1=1、in语句（建议使用exists）、注意组合索引的创建顺序按照顺序组着查询条件、尽量查询粒度大的SQL放到最左边、尽量建立组合索引
4. 合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

4.MySQL的存储引擎

存储引擎	Innodb	Myisam
存储文件	.frm 表定义文件.ibd 数据文件	.frm 表定义文件.myd 数据文件.myi 索引文件
锁	表锁、行锁	表锁
事务	ACID	不支持
CRUD	读、写	读多
count	扫表	专门存储的地方
索引结构	B+ Tree	B+ Tree

5.MySQL事务

事务概述
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理DDL、DML、DCL操作，比如 insert,update,delete 语句，默认是自动提交的。

一般来说，事务是必须满足4个条件（ACID）：

Atomicity（原子性）
Consistency（稳定性、一致性）
Isolation（隔离性）
Durability（可靠性、持久性）

对于ACID的解释如下：

原子性：构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行。
稳定性（一致性）：数据库在事务执行前后状态都必须是稳定的或者是一致的。
隔离性：事务之间不会相互影响。
可靠性（持久性）：事务执行成功后必须全部写入磁盘。

四种隔离级别：现在来看看MySQL数据库为我们提供的四种隔离级别（由低到高）： ① Read uncommitted (读未提交)：最低级别，任何情况都无法保证。 ② Read committed (读已提交)：可避免脏读的发生。 ③ Repeatable read (可重复读)：可避免脏读、不可重复读的发生。 ④ Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
默认隔离级别大多数数据库的默认隔离级别是Read committed，比如Oracle、DB2等。 MySQL数据库的默认隔离级别是Repeatable read。
如何查看和设置隔离级别：在MySQL数据库中查看当前事务的隔离级别： select @@tx_isolation; 在MySQL数据库中设置事务的隔离级别： set [glogal | session] transaction isolation level 隔离级别名称; set tx_isolation=’隔离级别名称;’

注意事项：隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

6.MySQL锁

数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。
对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。
MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。
总的来说，MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。
按照锁的粒度来分：行级锁和表级锁
按照锁的功能来分：共享读锁和排他写锁
悲观锁（排他写锁）和乐观锁（使用某一版本列或者唯一列进行逻辑控制）
MySQL的InnoDB和MyISAM存储引擎最大的区别：事务的支持、行级锁的支持。
行级锁定（row-level）行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
表级锁定（table-level）和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。
页级锁定（page-level）页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
总的来说，MySQL这3种锁的特性可大致归纳如下：
- 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
- 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
- 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

表级锁

MySQL的表级锁定有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

MySQL 实现的表级锁定的争用状态变量：

show status like 'table%';

table_locks_immediate：产生表级锁定的次数；
table_locks_waited：出现表级锁定争用而发生等待的次数；
手动增加表锁 lock table 表名称 read(write),表名称2 read(write)，其他;
查看表锁情况 show open tables;
删除表锁 unlock tables;
读锁演示

写锁演示：

InnoDB引擎的锁机制

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

说明： 1）共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预。 2）对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。 3）InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

Innodb的锁定是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。Innodb的这种锁定实现方式被称为“NEXT-KEYlocking”（间隙锁），因为Query执行过程中通过过范围查找的华，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
Innodb所使用的行级锁定争用状态查看： show status like '%innodb_row_lock%';

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是： Innodb_row_lock_time_avg（等待平均时长） Innodb_row_lock_waits（等待总次数） Innodb_row_lock_time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

mysql> create table test_innodb_lock (a int(11),b varchar(16)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
 
mysql> create index test_innodb_a_idx on test_innodb_lock(a);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index test_innodb_lock_b_idx on test_innodb_lock(b);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0