MySQL学习整理（下）

MySQL学习整理（下）

MySQL学习整理（上）—>点我鸭

附带Linux下通过tar包安装MySQL

一.索引

1）MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。索引是数据结构

2）排好序的快速查找数据结构即索引=排序+查找

索引优劣势

优势：

提高数据检索效率，降低数据库的IO成本

降低数据排序成本，降低CPU的消耗

劣势：

索引列也占用空间

提高查询速度的同时，降低更新表的速度

MySQL索引分类

1）普通索引

最基本的索引，没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；一张表不要超过五个，优先考虑复合索引

2）复合索引

多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

3）唯一索引

与普通索引类似，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

4）主键索引

特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般建表时同时创建主键索引

5）全文索引

用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其他索引大不相同，更像是一个搜索引擎，而不是简单的where语句参数匹配

MySQL索引语法

创建索引

如果是char和varchar类型，length可以小于字段实际长度，blob和text类型必须指定length

CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
' or '
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));

删除索引

DROP INDEX [indexName] ON mytable;

查看索引（\G表示将查询到的横向表格纵向输出，方便阅读）

SHOW INDEX FROM table_name\G

使用alter命令，有四种方式来添加数据表的索引

#该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)

#这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)

#添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD INDEX index_name(column_list)

#该语句指定了索引为FULLTEXT，用于全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)

MySQL索引结构

1.Btree索引

Btree 索引搜索过程

初始化介绍

一颗 b 树， 浅蓝色的块我们称之为一个磁盘块， 可以看到每个磁盘块包含几个数据项（深蓝色所示） 和指针（黄色所示）

如磁盘块 1 包含数据项 17 和 35， 包含指针 P1、 P2、 P3

P1 表示小于 17 的磁盘块， P2 表示在 17 和 35 之间的磁盘块， P3 表示大于 35 的磁盘块

真实的数据存在于叶子节点和非叶子节点中

查找过程

如果要查找数据项 29， 那么首先会把磁盘块 1 由磁盘加载到内存， 此时发生一次 IO， 在内存中用二分查找确定 29在 17 和 35 之间， 锁定磁盘块 1 的 P2 指针， 内存时间因为非常短（相比磁盘的 IO） 可以忽略不计

通过磁盘块 1的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存， 发生第二次 IO， 29 在 26 和 30 之间， 锁定磁盘块 3 的 P2 指针

通过指针加载磁盘块 8 到内存， 发生第三次 IO， 同时内存中做二分查找找到 29， 结束查询， 总计三次 IO

2.B+tree索引

B+tree 索引搜索过程

B+Tree 与 BTree 的区别

B-树的关键字（数据项）和记录是放在一起的

B+树的非叶子节点中只有关键字和指向下一个节点的索引, 记录只放在叶子节点中

B+Tree 与 BTree 的查找过程

在 B 树中， 越靠近根节点的记录查找时间越快， 只要找到关键字即可确定记录的存在； 而 B+ 树中每个记录的查找时间基本是一样的， 都需要从根节点走到叶子节点， 而且在叶子节点中还要再比较关键字。

从这个角度看 B 树的性能好像要比 B+ 树好， 而在实际应用中却是 B+ 树的性能要好些。 因为 B+ 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B 树多， 树高比 B 树小， 这样带来的好处是减少磁盘访问次数。

尽管 B+ 树找到一个记录所需的比较次数要比 B 树多， 但是一次磁盘访问的时间相当于成百上千次内存比较的时间， 因此实际中B+ 树的性能可能还会好些， 而且 B+树的叶子节点使用指针连接在一起， 方便顺序遍历（范围搜索）， 这也是很多数据库和文件系统使用 B+树的缘故。

性能提升

真实的情况是， 3 层的 B+ 树可以表示上百万的数据， 如果上百万的数据查找只需要三次 IO， 性能提高将是巨大的，如果没有索引， 每个数据项都要发生一次 IO， 那么总共需要百万次的 IO， 显然成本非常非常高

为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引

B+树的磁盘读写代价更低：B+树的内部结点并没有指向关键字具体信息的指针。 因此其内部结点相对 B 树更小。 如果把所有同一内部结点的关键字存放在同一盘块中， 那么盘块所能容纳的关键字数量也越多。 一次性读入内存中的需要查找的关键字也就越多。 相对来说 IO 读写次数也就降低了

B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点， 而只是叶子结点中关键字的索引。 所以任何关键字的查找必须走一条从根结点到叶子结点的路。 所有关键字查询的路径长度相同， 导致每一个数据的查询效率相当

建立索引场景

适合建立索引：

主键自动建立唯一索引

频繁作为查询的条件的字段

查询中与其他表关联的字段，外键关系建立索引

查询中统计或者分组字段

查询中排序的字段

不适合建立索引：

表记录太少

经常增删改的表

数据重复且分布平均的表字段

索引的选择性

索引列中不同数值的数目与表中记录数的比。一个索引的选择性越接近1，这个索引效率就越高

索引失效和注意事项

索引失效的情况

随着表的增长，where条件出来的数据太多，大于15%

统计信息失效 需要重新搜集统计信息

索引本身失效 需要重建索引

建议：

对于单键索引，尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好

在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

二.Explain

模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

作用：

查看表的加载顺序

查看sql的查询类型

哪些索引可能被使用，哪些索引又被实际使用了

表之间的引用关系

一个表中有多少行被优化器查询

其他额外的辅助信息

Explain+SQL语句查询：

explain select t2.*

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1.ID

select查询的序列号，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行

id有相同和不同的情况，id相同的可以认为是一组，由上至下顺序执行。在所有组中，id的值越大优先级越高，越先被执行

2.elect_type

查询的类型，主要用于区别普通查询，联合查询，子查询等复杂查询

类型	含义
SIMPLE	简单查询，查询中包含子查询或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>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
#重要的几个，一般得保证至少range级别，最好能ref
system>const>eq_ref>ref>range>index>ALL

1）system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计

2）const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

3）eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

4）ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

5）range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束于另一点，不用扫描全部索引

6）index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘数据库文件中读的）

7）all：FullTable Scan，将遍历全表以找到匹配的行（全表扫描）

5.possible_keys

显示可能应用在这张表中的索引，一个或多个，若查询涉及的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

6.key

实际使用的索引，如果为null，则没有使用索引，若查询中使用了覆盖索引，则该索引出现在key列表中

7.key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好，key_len中显示的值为索引最大可能长度，并非实际长度

8.ref

显示索引被哪一列使用了

9.rows

根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数

10.Extra

包含不适合在其他列表中显示但十分重要的额外信息

1）Using filesort（文件排序）

2）Using temporary（创建临时表）

3）Using where（使用了where过滤）

4）Using join buffer（使用了连接缓存）

5）impossible where（where子句总是false）

6）select tables optimized away（在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化）

7）distinct（优化distinct，在找到第一匹配的元组后即停止同样值的工作）

三.MySQL锁

1.按粒度划分的锁

1）表级锁（偏向于读）

优缺点：

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

支持引擎：MyISAM、MEMORY、InNoDB

表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

2）行级锁

优缺点

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

支持引擎：InnoDB

行级锁定分为行共享读锁（共享锁）与行独占写锁（排他锁）

3）页级锁

对于行级锁与表级锁的折中，开销和加锁时间界于表锁和行锁之间

会出现死锁

锁定粒度界于表锁和行锁之间，并发度一般

2.按锁的级别划分

1）共享锁（读锁）

共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改

2）排他锁（写锁）

一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改
可以直接通过select …from…查询数据，因为普通查询没有任何锁机制

3.MyISAM存储引擎的锁

1）支持表锁（偏向于读）

MyISAM在执行SQL语句时，会自动为SELECT语句加上共享锁，为UDI操作加上排它锁
MyISAM读写、写写之间是串行的，读读之间是并行的
由于表锁的锁定粒度大,读写又是串行的,因此如果更新操作较多,ＭyISAM表可能会出现严重的锁等待

2）并发锁

在存储引擎中有一个系统变量concurrent_insert，专门控制其并发插入的行为
concurrent_insert=0时，不允许并发插入
concurrent_insert=1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），其允许在一个进程读表的同事，另一个进程从表插入记录，这也是MySQL的默认设置
concurrent_insert=2时，如果MyISAM表中没有空洞，允许在表尾并发插入记录

锁调度

MySQL认为写请求一般比读请求要重要，所以如果有读写请求同时进行的话，MYSQL将会优先执行写操作
这样MyISAM表在进行大量的更新操作时（特别是更新的字段中存在索引的情况下），会造成查询操作很难获得读锁，从而导致查询阻塞

设置MyISAM调度行为

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级
系统参数max_write_lock_count设置一个合适的值;当一个表的读锁达到这个值后，MySQL便暂时将写请求的优先级降低，给读进程一定获得锁的机会

4.InNoDB存储引擎的锁

支持事务

1）需要提到的事务ACID（原子性、一致性。隔离性和持久性）

2）事务的并发处理导致的问题

更新丢失：读取数据后，被其他事务覆盖数据
读取脏数据：读取数据后，更新数据的事务回滚了，也就是读取的数据不正确
不可重复读：由于其他事物的插手，在同一事务中两次相同的查询数据是不同的（由于修改导致）
幻读：返回记录数不同（由于新增或者删除导致）

3）事务隔离级别

更新数据丢失不仅仅是数据库事务控制器解决，主要由应用解决。本来是为了实现事务的并发，以下对于操作对于并发的副作用越来越小，但付出的代价越来越大
读未提交的数据（Read uncommitted）：可能有有脏读、不可重复度、幻读的问题
读提交的数据（Read committed），没有脏读的问题，可能有不可重复度、幻读的问题
可重复读（Repeatable read）：没有脏读、不可重复度的问题，可能有幻读的问题
可序列化（Serializable）：没有脏读、不可重复度、幻读的问题

行锁

行级锁分为：
记录锁（Record lock）：对索引项加锁，即锁定一条记录
间隙锁（Gap lock）：对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身
Next-key Lock：锁定一个范围的记录并包含记录本身（上面两者的结合）

共享锁与排他锁

（1）共享锁：事务对数据添加了读锁，事务只能读而不能修改，其他事务也只能加读锁，期间不能修改，直到事务释放读锁

（2）排他锁：事务获取写锁后，只有自己可以操作（读取或者修改），而其他事务不能操作

意向共享锁与意向排他锁

意向共享锁（IS）：事务打算给数据行共享锁;，事务在给一个数据行加共享锁前必须先取得该表的IS锁

意向排他锁（IX）事务打算给数据行加排他锁;事务在给一个数据行加排他锁前必须先取得该表的IX锁

行级锁产生死锁

产生死锁原理：在MySQL中，行级锁并不是直接锁记录，而是锁索引
索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引

如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引
在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking

死锁导致原因：当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁

如何避免死锁：
用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施
如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能

行级锁的间隙锁（Next-Key lock）

select * from 表名 where 字段名>参数**（在一个范围内）** for update；

使用范围条件而不是相等条件检索数据，InnoDB除了给索引记录加锁，还会给不存在的记录（间隙）加锁，其他事务不能操作当前事务锁定的索引与间隙
目的:
防止幻读,避免其他事务插入数据
满足其恢复和复制的需要，MySQL的恢复机制是通过BINLOG记录来执行IUD操作来同步Slave的

​ 要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，为了恢复不能插入其他事务

什么时候使用表锁？

绝大部分情况使用行锁，但在个别特殊事务中,也可以考虑使用表锁

a）事务需要更新大部分数据，表又较大
若使用默认的行锁,不仅该事务执行效率低(因为需要对较多行加锁,加锁是需要耗时的)

而且可能造成其他事务长时间锁等待和锁冲突;

这种情况下可以考虑使用表锁来提高该事务的执行速度

b）事务涉及多个表，较复杂，很可能引起死锁，造成大量事务回滚
这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ

lock tables 加表锁，需要设置set autocommite=0来关闭自动提交，否则ＭySQL不会给表加锁，最后使用UNLOCK TABLES释放表锁，以下是定义写锁

乐观锁与悲观锁

悲观锁

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中

流程：

（1）在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）
如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常

具体响应方式由开发者根据实际需要决定
如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了

（2）其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常

优缺点：

优点：悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证
缺点：
在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会
在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载

还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

乐观锁

乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做
如果系统并发量非常大，悲观锁会带来非常大的性能问题，选择使用乐观锁，现在大部分应用属于乐观锁

版本控制机制

每一行数据多一个字段version，每次更新数据对应版本号+1，
原理：读出数据，将版本号一同读出，之后更新，版本号+1，提交数据版本号大于数据库当前版本号，则予以更新，否则认为是过期数据，重新读取数据

使用时间戳实现

每一行数据多一个字段time
原理：读出数据，将时间戳一同读出，之后更新，提交数据时间戳等于数据库当前时间戳，则予以更新，否则认为是过期数据，重新读取数据

四.Linux下通过tar包安装MySQL

环境与准备工具 Centos7 ，Xshell，XFTP

官网下载安装包–>>>下载地址点我鸭

1.使用XFTP上传下载安装包并解压重命名

cd /usr/local  #进入这个路径打开xftp
#执行一系列命令
rpm -pa | grep mysql #有显示就删除 没显示不管 有显示就 yum remove 查出来的依赖与软件包
find / -name mysql #出来的文件使用 rm- rf 删除
rpm -qa | grep mariadb #查看是否安装Mariadb 有则卸载
rpm -e --nodeps 后面跟查出来的Mariadb
--------------------------------------------------------------------
yum install libaio #安装mysql依赖包
tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz #解压 注意版本号
rm -rf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz  #删除tar包 这里个人习惯 不删除亦可
mv mysql-8.0.20-linux-glibc2.12-x86_64/ mysql  #将解压后的文件重命名为mysql

2.配置

#创建数据存放目录
mkdir /usr/local/mysql/data
#给与权限
chmod -R 777 /usr/local/mysql/
chmod -R 777 /usr/local/mysql/data/
#创建mysql用户，并且设置密码（@pz990428）
useradd mysql
passwd mysql
#将mysql目录的权限授给mysql用户和mysql组
chown -R mysql:mysql /usr/local/mysql
#创建mysql 安装初始化配置文件
vim /etc/my.cnf

这里没有vim命令执行 yum install vim -y

进入后 i进入编辑模式 添加以下内容

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=10000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

ESC退出编辑模式 输入:wq 保存并退出（冒号wq）

3.安装MySQL

#进入这个目录
cd /usr/local/mysql/bin/
#执行这个命令
./mysqld --initialize --console  #记住临时密码
#启动数据库
cd /usr/local/mysql/support-files #进入这个目录
./mysql.server start #执行命令
#如果报错 执行
chmod -R 777 /usr/local/mysql
#在执行
./mysql.server start 

#创建软连接(实现可直接命令行执行mysql)
ln -s /usr/local/mysql/bin/mysql /usr/bin
#登录数据库
mysql -uroot -p #这里密码是执行./mysqld --initialize --console后界面中的临时密码

#进去后修改密码为123456
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
flush privileges;
#授权远程连接
CREATE USER 'root'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
flush privileges;