MYSQL使用基础、进阶分享

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品，是最流行的关系型数据库管理系统之一。

创新互联建站主要从事成都网站设计、做网站、网页设计、企业做网站、公司建网站等业务。立足成都服务沙洋,十余年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18982081108

端口是3306。

表很多时，使用linux脚本，需要根据需要修改一下：

和创建一样，可以加上 if exists

可两篇文章：

如：

用于在已有的表中添加、删除或修改列。

添加 ADD

或

默认是添加到最后，但可以指定位置。 FIRST ：添加最前

AFTER 字段名 ：添加指定字段之后

例子：

删除 DROP

修改 MODIFY 主要修改原列的类型或约束条件 同样可以用 FIRST 和 AFTER 字段名 ，代表的是修改到哪里。

修改字段名 CHANGE

可以把表2的数据复制到表1中，但 不能复制约束性条件 。

单行

多行，注意 只有一个VALUES ：

不写 (行1, 行2...) 这一部分的话，默认一一对应

除了以上方法外，还可以用SET为每一行附上相应的值。

假如没有筛选的话，就给全部都修改了。可以用 WHERE 筛选。

假如 没有筛选的话，就给全部删除了 。相当于清空。

清空

先把表删除，然后再建一个。与 DELETE FROM 相比， TRUNCATE 的效率更快，因为 DELETE FROM 是把记录逐条删除的。

查询执行的顺序

FROM -- WHERE -- SELECT -- GROUP BY -- HAVING -- ORDER BY -- LIMIT

注意

当数据很大，上百万的时候，使用LIMIT ... OFFSET ..的方式进行分页十分浪费资源且耗时长。最好是结合WHERE使用，如：

REGEXP 使用正则表达进行匹配。 查询时，需要搭配WHERE或HAVING使用 。

两个表之间有交集且要用到两个表的数据时，可以使用内连接查询。

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

用法：

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。 把LEFT JOIN的表1、表2调换顺序，就是REGHT JOIN 。

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行. 相当于结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

但 MySQL中不支持 FULL OUTER JOIN 。

即SELECT嵌套。

IN 一个查询结果作为另一个查询的条件。 如：

EXISTS 用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。True时执行。 如：

索引的本质是一种排好序的数据结构。利用索引可以提高查询速度。

常见的索引有：

MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 外键的使用条件：

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

对已有的两个表增加外键 比如：主表为A，子表为B，外键为aid，外键约束名字为a_fk_b

为子表添加一个字段，当做外键

为子表添加外键约束条件

假如删除记录报错： [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)

这是因为MySQL中设置了foreign key关联，造成无法更新或删除数据。可以通过设置 FOREIGN_KEY_CHECKS 变量来避免这种情况。 第一步：禁用外键约束，我们可以使用： SETFOREIGN_KEY_CHECKS=0; 第二步：删除数据 第三步：启动外键约束，我们可以使用： SETFOREIGN_KEY_CHECKS=1; 查看当前FOREIGN_KEY_CHECKS的值，可用如下命令： SELECT @@FOREIGN_KEY_CHECKS;

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要 保留 相同行，使用 UNION ALL 。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后 。

内置函数很多, 见: MySQL 函数

我们一般使用 START TRANSACTION 或 BEGIN 开启事务, COMMIT 提交事务中的命令, SAVEPOINT : 相当于设置一个还原点, ROLLBACK TO : 回滚到某个还原点下

一般的使用格式如下:

开启事务时, 默认加锁

根据类型可分为共享锁（SHARED LOCK）和排他锁（EXCLUSIVE LOCK）或者叫读锁（READ LOCK）和写锁（WRITE LOCK）。

根据粒度划分又分表锁和行锁。表锁由数据库服务器实现，行锁由存储引擎实现。

除此之外,我们可以显示加锁

加锁时, 如果没有索引，会锁表，如果加了索引，就会锁行

InnoDB默认支持行锁，获取锁是分步的，并不是一次性获取所有的锁，因此在锁竞争的时候就会出现死锁的情况

解决方法:

即ACID特性:

由于并发事务会引发上面这些问题, 我们可以设置事务的隔离级别解决上面的问题.

MySQL的默认隔离级别（可重复读）

查看当前会话隔离级别

方式1

方式2

设置隔离级别

主从集群的示意图如下:

主要涉及三个线程： binlog 线程、 I/O 线程和 SQL 线程。

同步流程:

由于MySQL主从集群只会从主节点同步到从节点, 不会反过来同步, 所以需要读写分离

读写分离需要在业务层面实现 , 写数据只能在主节点上完成, 而读数据可以在主节点或从节点上完成

索引是帮助MySQL高效获取数据的排好序的数据结构

MySQL的索引有

推荐两个在线工具:

简单来说, B树是在红黑树(一个平衡二叉树)的基础上将一个节点存放多个值, 实现的, 降低了树的高度, 每个节点都存放索引及对应数据指针, 同一层的节点是递增的

而B+树在B树的基础上进行优化, 非叶子节点存放 子节点的开始的索引, 叶子节点存放索引和数据的指针, 且叶子节点之间有双向的指针

如下示意图:

不同的引擎, 主键索引存放的数据也不一样, 比如常见的 MyISAM 和 InnoDB

MyISAM 的B+树叶子节点存放表数据的指针, InnoDB 的B+树叶子节点存放处主键外的数据

其他的:

即多个列组成一个索引, 语法:

由于联合索引的B+树的结构, 根据列建立, 所以我们的查找条件也要根据索引列的顺序( where column1=x, column2=y,columnN... ), 否则会全表扫描

如果你对列进行了 （+，-，*，/，!） , 那么都将不会走索引。

OR 引起的索引失效

OR 导致索引是在特定情况下的，并不是所有的 OR 都是使索引失效，如果OR连接的是 同 一个字段，那么索引 不会失效 ， 反之索引失效 。

这个我相信大家都明白，模糊搜索如果你前缀也进行模糊搜索，那么不会走索引。

这两种用法，也将使索引失效。另 IN 会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描, 见: MySQL中使用IN会不会走索引

不走索引。

走索引。

所以设计表的时候, 建议不可为空, 而是将默认值设置为 "" ( NOT NULL DEFAULT "" )

MySQL索引

MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类

特点：B+树叶子节点存储行数据

一个表中，必须有一个聚集索引，只能有一个聚集索引，Innodb通常把一个表的主键索引作为聚集索引，如果没有主键InnoDB会选择一个唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚集索引，这个字段为6个字节，类型为长整形。

利用主键索引查找行数据是最快的，建议使用自增主键原因是利于索引树的构建（主键自增写入时新插入的数据不会影响到原有页，插入效率高；但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率）

特点：B+树叶子节点存储主键ID

一个表中可以有多个非聚集索引，每个非聚集索引即是一棵B+树

通过非聚集索引查找数据时，需要先在非聚集索引上找到主键ID，再从聚集索引获取行数据，这个过程就称之为回表

B树索引中的B树实际上是B+树，至于为什么使用B+树而不使用B树或者红黑树的原因在另外的文章中有提及。

特点：

特点：类似JDK中的HashMap，但无法支持范围查询

特点：使用的算法仍然是B树索引，不同的就是索引列的值必须唯一

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索，提升索引性能

另外插入行时会构建该唯一索引，假如索引值重复将插入失败，适合业务上做唯一性检验

通过建立倒排索引，可以极大的提升检索效率,解决判断字段是否包含的问题，但是业务上一般都不采用这种索引，而是使用ES处理全文搜索需求

仅对某个特定字段建立的索引，如(biz_id)

对多个字段建立的索引，如(biz_id,type)

Mysql InnoDB索引原理

理解Mysql索引的原理和数据结构有助于我们更好的使用索引以及进行SQL优化，索引是在存储引擎层面实现的，所以不同的引擎实现的索引也有一定的区别，但是在生产环境中，我们最常用的就是InnoDB引擎和B树索引，OK，那本文要讨论的重点也同样是 InnoDB引擎下的B树索引 。

我们建立一个表来进行测试，表的DDL如下所示，我们要关注的是表t_book上的主键索引id和name author publish_date三列组成的索引test_index。

Mysql中的B树索引是使用B+树实现的，关于B+树的数据结构个人认为美团点评技术博客中Mysql索引原理及慢查询优化一文中介绍的非常详实，B+树的数据结构如下图所示。

图中浅蓝色块即磁盘块，根节点磁盘块中存储17和35两个数据，其中指针P1指向小于17的数据，指针P2指向大于17小于35的数据，指针P3指向大于35的数据。显然通过B+树索引查询数据与B+树的高度有关，如上图的B+树索引查找一个叶子节点的数据只需要三次磁盘IO，对于Mysql来说三层的B+树可以索引上百万的数据，这对于查询效率的提升是巨大的。

总结起来Mysql中B树索引有以下关键特点：

Mysql中的B树索引有两种数据存储形式，一种为聚簇索引，一种为二级索引。

InnoDB一般会使用表的主键来作为聚簇索引，如果一个表没有主键（不建议这么玩）InnoDB会选用一个唯一非空索引来代替，如果没有这样的索引，InnoDB会隐式建立一个聚簇索引。聚簇的含义即是数据行和相邻的键值紧凑的存储在一起，占据一块连续的磁盘空间，因此通过聚簇索引访问数据可以有效减少随机IO，通常使用聚簇索引查找比非聚簇索引查找速度更快。以我们建立的表t_book为例，聚簇索引即为自增主键id，其B树索引数据结构可以用下图来表示。

聚簇索引有以下关键特点：

InnoDB的B树索引中除了聚簇索引，就都是二级索引了，二级索引的含义是索引的叶子节点除了存储了索引值，还存储了主键id，在使用二级索引进行查询时，查找到二级索引B树上的叶子节点后还需要去聚簇索引上去查询真实数据，但是这里有一种特殊情况，即查询所需的所有字段在二级索引中都可以获取，此时就不需要再去回表查数据了，这种情况就是索引覆盖（EXPLAIN中EXTRA列中会出现USING INDEX，本文只关注索引结构，不详细讨论索引覆盖等技术的使用，如果深入理解索引的数据结构，索引覆盖等技术也没有那么神秘）。

在我们的测试表t_book中，test_index即为二级索引，由于我们把除了主键id所有的列都作为一个联合索引，所以在这个表上的查询都可以使用索引覆盖技术，但是具体生产环境中也不建议总是采用这种做法，索引列的增加也会增大插入更新数据时的索引更新成本，具体的优化要视具体情况决策。t_book上的二级索引test_index的索引结构由下图表示。

通过以上结构，我们可以推断出二级索引的以下关键特点：

索引覆盖：

最左前缀匹配：

二级索引可以说是我们在Mysql中最常用的索引，通过理解二级索引的索引结构可以更容易理解二级索引的特性和使用。

最后聊点轻松的索引结构，哈希索引就是通过哈希表实现的索引，即通过被索引的列计算出哈希值，并指向被索引的记录。

哈希索引有如下特性：

Mysql索引原理及慢查询优化

高性能Mysql 第三版

名称栏目：mysql是怎么用b树 mysqlb+树原理
文章起源：http://pwwzsj.com/article/dosgpip.html