MySQL
MySQL概述
CentOS7安装
下载RPM安装包
下载: mysql download
将四个包下载后移动到opt文件夹下
检查是否已安装MySQL
如果没有信息显示则表示未安装MySQL
1 | rpm -qa | grep -i mysql |
安装MySQL
-i：显示套件的文件列表
-v：显示指令执行过程
-h：套件安装时列出标记
1 | rpm -ivh <包名> |
安装顺序：
- common
- libs
- client
- server
依赖冲突：
直接卸载mariadb
详情如下：
1 | [root@parak opt]# rpm -ivh mysql-community-common-8.0.20-1.el7.x86_64.rpm |
查看是否安装成功
- 方式一：查看mysql版本号
mysqladmin --version - 方式二：查看是否创建了mysql用户(组)
cat /etc/passwd | grep mysql
1 | [root@parak opt]# mysqladmin --version |
启动MySQL服务
- 启动MySQL：
systemctl start mysqld - 停止MySQL：
systemctl stop mysqld
登录MySQL
两种方式：
- 方式一：查看MySQL初始密码
cat /var/log/mysqld.log | grep password - 方式二：修改
my.cnf配置文件用于跳过密码，在[mysqld]下添加skip-grant-tables
推荐使用第一种，因为在MySQL8在跳过登录的状态下是不允许修改登录密码的。
1 | 登录mysql |
Navicat连接产生问题
（1）HOST
解决：关闭防火墙，更新可连接IP
1 | 查看防火墙状态 |
（2）Client does not support authentication protocol requested by server
解决：更改加密规则，更新用户密码
1 | #更改加密方式 |
设置开机自启动
按照以上安装方式mysql服务是默认开机自启动的，可以通过systemctl list-unit-files 查看开机启动项
1 | [root@parak mysql]# systemctl list-unit-files | grep mysql |
如果不是开机自启动，可以通过ntsysv启用服务，[]中设置*即可使其开机自启动：
- 上下键：可以在中间的方框当中，在各个服务之间移动
- 空格键：可以用来选择你所需要的服务，[*]表示开起启动
- tab键：可以在方框、OK、Cancel之间移动
- F1键：可以显示该服务的说明
Docker安装
拉取镜像
1 | docker pull mysql:8.0.20 |
创建挂载的数据和配置文件夹
1 | mkdir -p /home/mysql/data /home/mysql/conf |
先启动MySQL容器
1 | docker run --name mysql -d -p 3306:3306 \ |
进入容器查看配置文件位置
1 | docker exec -it mysql bash |
将配置文件复制到挂载配置文件夹
1 | docker cp mysql:/etc/mysql/my.cnf /home/mysql/conf |
在配置文件中添加
1 | 表名称大小写不敏感 |
先停止并删除容器
1 | docker stop mysql && docker rm mysql |
重新运行MySQL容器
1 | docker run --name mysql \ |
Navicat无法连接
1 | $ docker exec -it mysql bash |
配置文件
安装目录
| 路径 | 解释 |
|---|---|
| /var/lib/mysql/ | 数据库文件的存放位置 |
| /usr/share/mysql-8.0/ | 配置文件目录 |
| /usr/bin/ | 相关命令目录 |
查看编码
1 | show variables like 'character%'; |
可以看到，MySQL 8的默认编码格式除了文件系统是二进制编码以外，已经全部改为utf8和utf8mb4(拥有比utf8更好的兼容性)，因此我们不需要再修改。
注意：使用Navicat连接MySQL时编码应设置为自动，切勿设置为utf8，否则会中文乱码。
逻辑架构
优势
和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用和。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
图示
详解
- 连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。在该层上引入了线程池的概念，未通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
- 服务层：第二层架构主要完成大多数的核心功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
- 引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
- 存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
存储引擎
查看存储引擎
1 | mysql>show engines; |
MyISAM和InnoDB
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不合适高并发的操作 | 行锁，操作时只锁住某一行，不对其他行有影响，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
| 默认安装 | Y | Y |
说明
MySQL 8.0：不再支持查询缓存。
MySQL团队博客：https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/
1 | 尽管MySQL Query Cache旨在提高性能，但它存在严重的可伸缩性问题，并且很容易成为严重的瓶颈。 |
Alibaba选择
- Percona为MySQL数据库服务进行了改进，在功能和性能上较MySQL有着很显著的提升。该版本提升了在高负载情况下的InnoDB的性能、为DBA提供了一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。
- 阿里巴巴大部分MySQL数据库其实使用的时percona的原型加以修改。阿里新建了一款存储引擎叫xtradb完全可以替代innodb，并且在性能和并发上做得更好。
索引优化
索引简介
定义
MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。
简单理解：排好序的快速查找数据结构。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，不一定是二叉树）结构的索引。
其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。除了B+树这种类型的索引之外，还有哈希索引等。
优势
类似于大学图书馆建数目索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列队数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势
虽然索引大大提高了查询速度，同时会降低表的更新速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。
索引分类
单值索引
一个索引列只包含单个列，一个表可以有多个索引。
唯一索引
索引列的值必须唯一，但允许有空值。
复合索引
一个索引包含多个列。
覆盖索引
SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据，即查询字段为索引字段。
基本语法
- 创建
1 | CREATE [UNIQUE] INDEX <index_name> ON <table_name>(<column_name>(length)); |
- 删除
1 | DROP INDEX <index_name> ON <table_name>; |
- 查看
1 | SHOW INDEX FROM <table_name>; |
- ALTER
1 | -- 添加主键，即唯一索引 |
索引结构
索引
- BTREE
- HASH
- FULL-TEXT
- R-TREE
需要建立索引的情况
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 频繁更新的字段不适合创建索引
- Where条件里用不到的字段不创建索引
- 单值/组合索引的选择，在高并发下倾向创建组合索引
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
不需要建立索引的情况
- 表记录太少 [原因：低于百万数的表MySQL还是扛得住的。]
- 经常增删改的表 [原因：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。]
- 数据重复且分布均匀的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果 [例如：性别等字段。]
Join查询
join说明
- LEFT JOIN：返回左表中的所有记录和右表中联结字段相等的记录。
- 格式：
SELECT ... table1 LEFT JOIN table2 ON ... - 说明：会取得table1全部记录，即使table2没有匹配记录
- 格式：
- RIGHT JOIN：返回右表中的所有记录和左表中联结字段相等的记录。
- 格式：
SELECT ... table1 RIGHT JOIN table2 ON ... - 说明：会取得table2全部记录，即使table1没有匹配记录
- 格式：
- INNER JOIN：只返回两个表中联结字段相等的记录。
- 格式：
SELECT ... table1 INNER JOIN table2 ON ... - 说明：会取得table1table2联结字段相等的记录
- 格式：
join图示
1 | SELECT <select_list> from table_a a LEFT JOIN table_b b ON a.key = b.key |
1 | SELECT <select_list> from table_a a LEFT JOIN table_b ON a.key == b.key WHERE b.key is NULL |
1 | SELECT <select_list> from table_a a INNER JOIN table_b b ON a.key = b.key |
1 | SELECT <select_list> from table_a a RIGHT JOIN table_b b ON a.key = b.key |
1 | SELECT <select_list> from table_a a RIGHT JOIN table_b ON a.key = b.key WHERE a.key is NULL |
1 | # Oracle支持 FULL OUTER JOIN，但是MySQL不支持 |
1 | # Oracle |
SQL执行顺序
- 手写
1 | SELECT DISTINCT |
- 机读
性能分析
MySQL Query Optimizer
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划(它认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)
当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的Hint 信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
MySQL性能瓶颈
CPU: CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO: 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top、free、iostat、vmstat来查看系统的性能状态
Explain
官网介绍：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
使用目的：
- 表的读取顺序
- 数据读取操作的操作类型
- 可以使用的索引
- 实际使用的索引
- 表之间的引用
- 每张表被优化器查询的行数
使用方法：explain + sql语句
字段解释
（1）id
解释：
select查询的序列号，包含一组数字，表示查询中执行的select子句或操作表的顺序。
说明：
- id相同，执行顺序由上至下；
- id不同，如果是子查询，id递增，id值越大优先级越高，越先被执行；
- id相同不同，同时存在，id如果相同，可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高。
（2）select_type
解释：
查询的类型：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT。查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
说明：
- SIMPLE：简单的select查询，查询中不包含子查询或者UNION；
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询责则被标记为PRIMARY；
- SUBQUERY：在SELECT或WHERE列表中包含了子查询；
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里；
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被被标记为：DERIVED；
- UNION RESULT：从UNION表获取结果的SELECT。
（3）table
解释：显示这一行数据是关于哪一张表的。
（4）type
解释：显示查询了何种类型。
说明：
从最好到最差依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
常见的是：system > const > eq_ref > ref > range > index > ALL。
一般来说，得保证查询至少达到range级别，最好能到ref。
- system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
- const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引会比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
- index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然ALL和index都是全表，但index是从索引出发的，而ALL是从硬盘读取的。）
- ALL：Full Table Scan，将扫描全表以找到匹配的行。
（5）possible_keys
解释：
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
（6）key
解释：实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
（7）key_len
解释：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len时根据表定义计算而得，不是通过表内检索出的。
计算：
- 字符串
- char(n)：n字节长度
- varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
- 数值类型
- tinyint：1字节
- smallint：2字节
- int：4字节
- bigint：8字节
- 时间类型
- date：3字节
- timestamp：4字节
- datetime：8字节
- 如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
（8）ref
解释：
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
（9）rows
解释：
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
（10）extra
解释：
包含不适合在其他列中显示但十分重要的额外信息。
说明：
using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录，是性能高的表现。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。using filesort：MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时MySQL会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况一般是很危险的，九死一生。using temporary：MySQL需要创建一张临时表来处理查询，对于查询结果排序时使用临时表，常见于排序order by和分组查询group by。常见于这种情况就更加危险了，十死无生。using where：使用where过滤。using join buffer：使用连接缓存。impossible where：where子句总是false，不能用来获取任何元组。select tables optimized away：在没有GROUP BY子句的情况下，基于索引优化MIN/MAX或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。distinct：一旦MySQL找到了与行相联合匹配的行，就停止搜索。
SQL优化
案例1
建表：
1 | CREATE TABLE `tb2_article` ( |
第一次explain：
1 | EXPLAIN SELECT id, author_id FROM tb2_article WHERE category_id = 1 AND comments > 1 order by views desc limit 1; |
结果：
1 | +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ |
建立索引：
1 | CREATE INDEX idx_article_ccv on tb2_article(category_id,comments, views); |
第二次explain：
1 | EXPLAIN SELECT id, author_id FROM tb2_article WHERE category_id = 1 AND comments > 1 order by views desc limit 1; |
结果：
1 | +----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+ |
发现key中已经显示了刚刚建立的索引，但是依然使用了文件排序。
第三次explain：
1 | EXPLAIN SELECT id, author_id FROM tb2_article WHERE category_id = 1 AND comments = 1 order by views desc limit 1; |
结果：
1 | +----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------+ |
当把查询条件修改为等于时，发现ref中出现两个常量，即两个查询常量，并且没有使用文件排序。说明当查询条件为大于号时，索引失效。
浅析第二次加了索引之后explain依然使用filesort：
按照BTree的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments，如果遇到相同的commnents则再排序views。当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。
删除索引：
1 | DROP INDEX idx_article_ccv ON tb2_article; |
新建索引：
1 | CREATE INDEX idx_article_cv ON tb2_article(category_id, views); |
再次explain：
1 | EXPLAIN SELECT id, author_id FROM tb2_article WHERE category_id = 1 AND comments > 1 order by views desc limit 1; |
结果：
1 | +----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------+ |
可以看到type变成了ref，Extra中的using filesort也消失了，结果非常理想。
结论：建立复合索引的时候最好不要带上含有范围查询的字段。
案例2
继续建表：
1 | CREATE TABLE `tb2_class` ( |
第一次explain：
1 | EXPLAIN SELECT * FROM tb2_class LEFT JOIN tb2_book on tb2_class.card = tb2_book.card; |
结果：
1 | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ |
可以看到type都为ALL。
建立右表tb2_book索引：
1 | ALTER TABLE tb2_book ADD INDEX(card); |
第二次explain：
1 | EXPLAIN SELECT * FROM tb2_class LEFT JOIN tb2_book on tb2_class.card = tb2_book.card; |
结果：
1 | +----+-------------+-----------+------------+------+---------------+------+---------+----------------------------+------+----------+-------------+ |
可以看到tb2_class的type依然是ALL，tb2_book的type优化为ref。
删除tb2_book的索引：
1 | DROP INDEX card ON tb2_book; |
建立左表tb2_class索引：
1 | ALTER TABLE tb2_class ADD INDEX(card); |
第三次explain：
1 | EXPLAIN SELECT * FROM tb2_class LEFT JOIN tb2_book on tb2_class.card = tb2_book.card; |
结果：
1 | +----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+ |
可以看到tb2_class的type为index，tb2_book的type下降为ALL。
最后再次建立tb2_book的索引：
1 | ALTER TABLE tb2_book ADD INDEX(card); |
第四次explain：
1 | EXPLAIN SELECT * FROM tb2_class LEFT JOIN tb2_book on tb2_class.card = tb2_book.card; |
结果：
1 | +----+-------------+-----------+------------+-------+---------------+------+---------+----------------------------+------+----------+-------------+ |
发现type都提升了，index和ref，结果很理想。
结论：对于JOIN连接查询的两张表最好都在联结字段建立单值索引。
案例3
继续建表：
1 | CREATE TABLE `tb2_phone` ( |
第一次explain：
1 | EXPLAIN SELECT * FROM tb2_class LEFT JOIN tb2_book ON tb2_class.card = tb2_book.card LEFT JOIN tb2_phone ON tb2_book.card = tb2_phone.card ; |
结果：
1 | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ |
发现三张表的type都是ALL。
建立tb2_phone和tb2_book的索引：
1 | ALTER TABLE tb2_phone INDEX(card); |
第二次explain：
1 | EXPLAIN SELECT * FROM tb2_class LEFT JOIN tb2_book ON tb2_class.card = tb2_book.card LEFT JOIN tb2_phone ON tb2_book.card = tb2_phone.card ; |
结果：
1 | +----+-------------+-----------+------------+------+---------------+------+---------+----------------------------+------+----------+-------------+ |
发现tb2_phone和tb2_book的type被优化为ref，并且rows也优化的很好。
结论：
- 永远用小结果集驱动大结果集
- 尽可能减少Join语句中的NestedLoop的循环总次数
- 优先优化NestedLoop的内存循环
- 保证Join语句中被驱动表上Join条件字段已经被索引
- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置
索引失效
SQL脚本
1 | CREATE TABLE `tb3_staff` ( |
生效场景
EXPLAIN SELECT * FROM tb3_staff WHERE name = 'KHighness';EXPLAIN SELECT * FROM tb3_staff WHERE name = 'KHighness' and age = 19;EXPLAIN SELECT * FROM tb3_staff WHERE name = 'KHighness' and age = 19 and pos = "dev";
失效场景
EXPLAIN SELECT * FROM tb3_staff WHERE age = 19 and pos = "dev";-
EXPLAIN SELECT * FROM tb3_staff WHERE pos = "dev";
部分失效
EXPLAIN SELECT * FROM tb3_staff WHERE name = 'KHighness' and pos = "dev";
总结
1、最理想的情况就是查询字段与索引字段相同
2、最佳左前缀法则
3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4、存储引擎不能使用索引中范围条件右边的列
5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
6、使用不等于(!= 或者 <>)的时候索引失效会导致range（MySQL5中是ALL）
7、使用is null或者is not null的时候索引失效会导致range（MySQL5中是ALL）
8、like以通配符开头索引失效会导致ALL，建立覆盖索引可以防止
9、MySQL5中字符串不加单引号索引失效会导致ALL，MySQL8中直接报错
10、使用or连接索引失效会导致ALL
索引面试
SQL语句
1 | CREATE TABLE `tb4_test` ( |
EXPLAIN测试
1 | -- 最好索引如何创建就如何使用，避免让MySQL自己再翻译优化 -- |
总结
- 定值、范围还是排序，一般order by是给个范围。
- group by基本上都需要进行排序，会有临时表产生。
- like匹配%在字符串最右边会使用使用，%在字符串最左边不会使用。
一般性建议
对于单键索引，尽量选择针对当前query过滤性更好的索引。
在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
优化口诀
带头大哥不能死，中间兄弟不能断；
覆盖索引不写星，索引列上少计算；
不等有时会失效，范围之后全失效；
LIKE百分写最右，一般SQL少用OR。
查询截取
查询优化-1
优化策略
永远小表驱动大表。
IN
1 | SELECT * FROM A WHERE id IN (SELECT id FROM B) |
等价于：
1 | for select id from B |
当A表的数据集大于B表的数据集时，用in优先exists。
EXISTS
1 | SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id) |
等价于
1 | for select id from A |
当A表的数据集小于B表的数据集时，用exists优先in。
查询优化-2
优化策略
Order By关键字优化：
Order By子句，尽量使用Index方式排序，避免使用FileSort方式排序。
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。
如果不在索引列上，FileSort有两种算法：MySQL就要启动双路排序和单路排序。
案例
SQL脚本：
1 | CREATE TABLE `tb5_a` ( |
八个case：
EXPLAIN SELECT * FROM tb5_a WHERE age > 20 ORDER BY age;=> 正常EXPLAIN SELECT * FROM tb5_a WHERE age > 20 ORDER BY birth;=> using filesortEXPLAIN SELECT * FROM tb5_a WHERE age > 20 ORDER BY age, birth;=> 正常EXPLAIN SELECT * FROM tb5_a WHERE age > 20 ORDER BY birth，age;=> using filesprtEXPLAIN SELECT * FROM tb5_a WHERE birth > '2020-3:23 00:00:00' ORDER BY age;=> 正常EXPLAIN SELECT * FROM tb5_a WHERE birth > '2020-3:23 00:00:00' ORDER BY birth;=> using filesortEXPLAIN SELECT * FROM tb5_a WHERE birth > '2020-3:23 00:00:00' ORDER BY age,birth;=> 正常EXPLAIN SELECT * FROM tb5_a WHERE birth > '2020-3:23 00:00:00' ORDER BY birth,age;=> using filesortEXPLAIN SELECT * FROM tb5_a ORDER BY age ASC,birth ASC;=> 正常EXPLAIN SELECT * FROM tb5_a ORDER BY age DESC,birth DESC;=> 正常EXPLAIN SELECT * FROM tb5_a ORDER BY age ASC,birth DESC;=> using filesortEXPLAIN SELECT * FROM tb5_a ORDER BY age DESC,birth ASC;=> using filesort
总结
总结：Order By满足两种情况，会使用Index方式排序：
- Order By语句使用索引最左前列
- 使用Where子句与Order By子句条件列组合满足索引最左前列
注意：如果不在索引列上，filesort有两种算法：
- 双路排序：两次扫描磁盘获取数据，读取行指针和order by列，对它们进行排序，然后扫描已经排序号的列表，按照列表中的值重新从列表中读取对应的数据输出。
- 单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
- 单路问题：在单路排序中，要占用很多空间，因为需要把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排，从而导致多次I/O。
- 优化策略：SQL服务器参数调优，增大sort_buffer_size参数的设置，增大max_length_for_sort_data参数的设置。
提高Order By的速度：
（1）Order By时select *是一个大忌，只查询需要字段，这点非常重要。
（2）尝试提高sort_buffer_size。
（3）尝试提高max_length_for_sort_data。
排序案例
1 | index a_b_c(a, b, c); |
查询优化-3
优化策略
Group By关键字优化：
优化策略与Order By相似。
Group By实质是先排序后进行分组，遵照索引建的最佳左前缀。
当无法使用索引列，增大max_length_for_sort_data参数设置，增大sort_buffer_size参数的设置。
where高于having，能卸载where限定的条件就不要去having限定了。
慢查询日志
简介
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启漫长哈讯日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
配置
查看是否开启慢查询日志和文件位置：
1 | mysql> SHOW VARIABLES LIKE '%SLOW_QUERY_LOG%'; |
开启慢查询日志（只对本次生效，重启后失效）：
1 | SET global slow_query_log = 1; |
查看慢查询阈值（默认为10s）：
1 | mysql> SHOW VARIABLES LIKE 'long_query_time%'; |
设置慢查询阈值（3s）：
1 | SET global long_query_time = 3; |
查看慢查询记录数量：
1 | SHOW global STATUS LIKE '%SLOW_QUERIES%'; |
永久生效需要修改配置文件/etc/my.cnf，需要在[mysqld]下增加或修改参数：
1 | slow_query_log = 1 |
mysqldumpslow
参数：
- s：表示按照何种方式排序
- c：访问次数
- l：锁定时间
- r：返回记录
- t：查询时间
- al：平均锁定时间
- ar：平均返回记录数量
- at：平均查询时间
- t：返回数据数量
- g：正则匹配，大小写不敏感
得到返回记录最多的10个SQL：
1 | mysqldumpslow -s r -t 10 /var/lib/mysql/parak-slow.log |
得到访问次数最多的10个SQL：
1 | mysqldumpslow -s c -t 10 /var/lib/mysql/parak-slow.log |
得到按照时间排序的前10条里面含有左连接的查询语句：
1 | mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/parak-slow.log |
建议在使用这些命令时结合 | 和 more使用，防止爆屏。
批量数据脚本
配置
变量log_bin_trust_function_creators：控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。
查看是否开启：
1 | mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators'; |
开启（暂时性开启，永久性依然是修改my.cnf）：
1 | SET global log_bin_trust_function_creators = 1; |
数据准备
1 | CREATE TABLE `tb6_dept` ( |
创建函数
1 | # 随机产生字符串 |
创建存储过程
1 | # 向tb6_dept表批量插入 |
批量插入数据
1 | # 向tb6_dept中插入10条数据 |
Show Profile
概述
MySQL提供的可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL调优的测量。
官网：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
配置
查看开启状态：
1 | mysql> SHOW VARIABLES LIKE 'profiling'; |
开启：
1 | SET global profiling = 1; |
MySQL8需要关闭依赖检测，即从sql_mode中移除ONLY_FULL_GROUP_BY：
查看sql_mode：
1 | mysql> SELECT @@global.sql_mode; |
移除ONLY_FULL_GROUP_BY：
1 | SET global @@sql_mode = `STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION`; |
相关命令
查看SQL：SHOW PROFILES
诊断SQL：SHOW PROFILE <type ...> FOR QUERY <Query_ID>
可选参数如下：
1 | | ALL -- 显示所有的开销信息 |
日常开发需要注意
- converting HEAP to MyISAM 查询结果太大，内存都不够用了王磁盘上搬了
- Creating tmp table 拷贝数据到临时表，用完再删除
- Copying to tmp table on disk 吧内存中临时表复制到磁盘，危险
- locked 死锁
全局查询日志
永久启用
修改my.cnf，设置如下：
1 | # 开启 |
临时启用
1 | set global general_log = 1; |
注意
永远不要在生产环境开启这个功能。
锁机制
概述
前言
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
分类
从对数据操作的类型分：
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响。
- 写锁（排他锁）：当前写操作没有完成之前，它会阻断其他写锁和读锁。
从对数据操作的粒度分：
- 行级锁：对当前操作的行加锁。（开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高）
- 表级锁：对当前操作的表加锁。（开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低）
- 页级锁：介于行级锁和表级锁中间的一种锁。（开销和加锁时间介于表锁和行锁之间，会出现死锁；并发度一般）
事务
概述
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。
A(atomicity/原子性)：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
C(consistency/一致性)：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
I(isolation/隔离性)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
D(durability/持久性)：事务完成之后，它对于数据的修改是永久性的，即时出现系统故障也能够保持。
并发事务处理带来的问题
更新丢失（Lost Update）
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了由其他事务所做的更新。
例如，两个程序员修改同一Java文件。每程序员独立地更改其副本，然后保存更改的副本后，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果一个在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。
脏读（Dirty Reads）
一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
事务A读取到事务B已修改但未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
不可重复读（Non-Repeatable Reads）
一个事务在服务某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除，这种现象就叫做不可重复读。
事务A读取到了事务B已经提交的修改数据，不符合隔离性。
幻读（Phantom Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
事务A读取到事务B提交的新增数据，不符合隔离性。
事务隔离级别
“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的书屋隔离级别来解决。
数据库实现事务隔离的方式，基本上可分为以下两种：
- 一种是在读取数据前，对其加锁，阻止其他事物对数据修改。
- 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。同用户的角度来看，好像是数据库可以提供统一数据的多个版本，因此，这种技术叫数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑需求，通过选择不同的隔离级别来平衡“隔离”与“并发”的矛盾。
| 读数据一致性及允许的并发副作用隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 未提交读（Read uncommitted） | 最低级别，只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交读（Read committed） | 语句级 | 否 | 是 | 是 |
| 可重复读（Repeatable read） | 事务级 | 否 | 否 | 是 |
| 可序列化（Serializable） | 最高级别，事务级 | 否 | 否 | 否 |
数据库的事务隔离越严格，并发副作用越小，但付出的代价也玖越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
配置
查看的事务隔离级别（默认为可重复读）：SELECT @@transaction_isolation;
1 | mysql> SELECT @@transaction_isolation; |
设置读未提交: SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
设置读已提交：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
设置可重复读：SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
设置可序列化： SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SQL命令
事务开始：BEGIN 或者 START TRANSACTION
事务结束：COMMIT或者COMMIT WORK
事务回滚：ROLLBACK或者ROLLBACK WORK
表锁
特点
表锁（偏读）：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
操作
手动增加表锁：LOCK TABLE <tablename_1> read(write),<tablename_1> read(write) ...
查看表上的锁：SHOW OPEN TABLES;
解锁：UNLOCK TABLES;
分析
分析表锁定：SHOW STATUS LIKE 'table%';
有两个状态变量记录MySQL内部表级锁定的情况，两个变量说下：
Table_locs_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值+1;
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值+1），此值高则说明存在着比较严重的表级锁争用情况。
此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
总结
当前线程给一个表加上读锁时，当前线程和其他线程都可以读这个表，但是当前线程线程读其他表时会报错，当前线程写该表时会报错，其他线程写该表时会阻塞。
当前线程给一个表加上写锁时，当前线程可以对该表进行读和写操作，但是当前线程对其他表进行读和写操作时会报错，其他线程对该表进行读和写时会阻塞。
读锁阻塞写，写锁都阻塞。
行锁
特点
行锁（偏写）：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。
InnoDB行锁实现方式
InnoDB行锁是通过索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件来检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。
总结
当前线程begin以后对一张表进行写操作，其他线程对该表进行写操作时会阻塞。当前线程写操作完毕后仅当前线程可见，其他线程只能读更新之前的数据，只有当前线程commit;之后其他线程才可读更新数据。
间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙。
InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁。
危害：因为Query执行过程中通过范围查找的话，它会锁定整个范围内的所有索引键值，即使这个键值并不存在。间隙所有一个比较致命的弱点，就是当锁定一个范围键值之后，即时某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
如何锁定一行
SELECT .....FOR UPDATE在锁定某一行后，其他写操作会被阻塞，直到锁定的行被COMMIT。
结论：
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
但是，InnoDB的行级锁定同样有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能更差。
分析行锁定
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：SHOW STATUS LIKE 'innodb_row_lock;'
1 | mysql> 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：系统启动后到现在总共等待的次数
注意waits金额time_avg比较高的，就要分析系统并制定优化方案。
优化建议
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小锁的范围
- 尽可能减少检索条件，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可能低级别事务隔离
MVCC
说明
MVCC（Multiversion concurrency control ）是一种多版本并发控制机制。
实现
- 插入：添加隐藏两列，创建版本号(当前事务id)和删除版本号(null)
- 查询：需要满足条件，创建版本号 < 当前事务id < 删除版本号
- 删除：更新数据的删除版本号为当前事务id
- 更新：复制一份数据，先执行删除，再执行插入，就旧数据的删除版本号和新数据的创建版本号都设置为当前事务id
主从复制
复制的基本原则
MySQL复制过程分为三步：
- Master将改变记录到二进制日志(Binary Log)。这些记录过程叫做二进制日志事件，
Binary Log Events； - Slave将Master的
Binary Log Events拷贝到它的中继日志(Replay Log); - Slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步且串行化的。
复制的最大问题
- 每个Slave只有一个Master。
- 每个Slave只能有一个唯一的服务器ID。
- 每个Master可以有多个Salve。
一主一从配置
基本准备
1 | [root@parak home]# mkdir -p mysql-3307/conf mysql-3307/data mysql-3308/conf mysql-3308/data |
Docker启动
mysql-3307用作Master
1 | docker run -d \ |
mysql-3308用作Slave
1 | docker run -d \ |
查看网络
查看bridge网络的所有容器：docker inspect bridgr
根据容器ID或者容器名称查询：docker inspect --format='{{.NetworkSettings.IPAddress}}' 容器名称 | 容器id
1 | [root@parak ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-3307 |
mysql-3307的IP为：172.17.0.5
mysql-3308的IP为：172.17.0.6
Master配置
进入Master内部
1 | # 创建用户，用于Slave访问Master |
用户名：slave
File：mysql-bin.000003
Position：4440
Slave配置
进入Slave内部
1 | mysql> CHANGE MASTER TO |
![wechat]()
wechat![alipay]()
alipay
