MySQL 知识点

https://www.bilibili.com/video/BV12b411K7Zu?p=179

优秀总结：

• 最全91道MySQL面试题 | 附答案解析
• 2019年最全的数据库面试题，我已整理好了，接下来就看你自己啦！

调优 何时创建索引

MySQL相关概念

MySQL执行顺序

MyISAM 与 Innodb区别

	MyISAM	Innodb
外键	不支持	支持
事务	不支持	支持
行表锁	表锁	行锁（可能出现死锁）
缓存	只缓存索引，不缓存真实数据	缓存索引和真实数据，对内存要求较高

InnoDB 逻辑存储结构

来源：https://www.jianshu.com/p/ff195927b8d5

表空间 Table Space

表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。分为：系统表空间、独占表空间、通用表空间、临时表空间、Undo 表空间。

段 Segment

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等，段是一个逻辑的概念。一个 ibd 文件（独立表空间文件）里面会由很多个段组成。
创建一个索引会创建两个段，一个是索引段：leaf node segment，一个是数据段：non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。也就是说，一个表的段数，就是索引的个数乘以 2。

簇 Extent

一个段（Segment）又由很多的簇（也可以叫区）组成，每个区的大小是 1MB（64个连续的页）。
每一个段至少会有一个簇，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇。

页 Pag

为了高效管理物理空间，对簇进一步细分，就得到了页。簇是由连续的页（Page）组成的空间，一个簇中有 64 个连续的页。 （1MB／16KB=64）。这些页面在物理上和逻辑上都是连续的。
跟大多数数据库一样，InnoDB 也有页的概念（也可以称为块），每个页默认 16KB。页是 InnoDB 存储引擎磁盘管理的最小单位，通过 innodb_page_size 设置。
一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个表空间最多存储 64TB 的数据。

注意，文件系统中，也有页的概念。

操作系统和内存打交道，最小的单位是页 Page。文件系统的内存页通常是 4K。

SHOW VARIABLES LIKE 'innodb_page_size';

假设一行数据大小是 1K，那么一个数据页可以放 16 行这样的数据。
举例：一个页放 3 行数

往表中插入数据时，如果一个页面已经写完，产生一个新的叶页面。如果一个簇的所有的页面都被用完，会从当前页面所在段新分配一个簇。
如果数据不是连续的，往已经写满的页中插入数据，会导致叶页面分裂：

不连续.png

行 Row

InnoDB 存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
Antelope[ˈæntɪləʊp]（羚羊）是 InnoDB 内置的文件格式，有两种行格式：
REDUNDANT[rɪˈdʌndənt] Row Format
COMPACT Row Format（5.6 默认）
Barracuda[ˌbærəˈkjuːdə]（梭子鱼）是 InnoDB Plugin 支持的文件格式，新增了两种行格式：

DYNAMIC Row Format（5.7 默认）
COMPRESSED Row Format

文件格式	行格式	描述
Antelope （Innodb-base）	ROW_FORMAT=COMPACT ROW_FORMAT=REDUNDANT	Compact 和 redumdant 的区别在就是在于首部的存 存内容区别。 compact 的存储格式为首部为一个非 NULL 的变长字 段长度列表 redundant 的存储格式为首部是一个字段长度偏移 列表（每个字段占用的字节长度及其相应的位移）。 在 Antelope 中对于变长字段，低于 768 字节的，不 会进行 overflow page 存储，某些情况下会减少结果 集 IO.
Barracuda (innodb-plugin)	ROW_FORMAT=DYNAMIC ROW_FORMAT=COMPRESSED	这两者主要是功能上的区别功能上的。 另外在行 里的变长字段和 Antelope 的区别是只存 20 个字节， 其它的 overflow page 存储。 另外这两都需要开启 innodb_file_per_table=1

innodb_file_format 在配置文件中指定；row_format 则在创建数据表时指定。

show variables like "%innodb_file_format%";
SET GLOBAL innodb_file_format=Barracuda;

innodb_file_format.png

在创建表的时候可以指定行格式。

CREATE TABLE tf1
(c1 INT PRIMARY KEY)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

查看行格式：

SHOW TABLE STATUS LIKE 'student' \G;

SQL解析顺序

SQL性能下降

索引（Index）

InnoDB 索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

索引类型（B Tree、B+ Tree）

来源：https://www.jianshu.com/p/ff195927b8d5

1. AVL 树用于存储索引数据

   首先，索引的数据，是放在硬盘上的。查看数据和索引的大小：

   select
   CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data_len,
   CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') as index_len
   from information_schema.TABLES
   where table_schema='gupao' and table_name='user_innodb';
   

   当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次 IO。 InnoDB 操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是 16K(16384 字节)。 那么，一个树的节点就是 16K 的大小。 如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个 或者几十个字节，它远远达不到 16K 的容量，所以访问一个树节点，进行一次 IO 的时候， 浪费了大量的空间。 所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多 的节点，意味着跟磁盘交互次数就会过多。 如果是机械硬盘时代，每次从磁盘读取数据需要 10ms 左右的寻址时间，交互次数 越多，消耗的时间就越多。

比如上面这张图，我们一张表里面有 6 条数据，当我们查询 id=37 的时候，要查询两个子节点，就需要跟磁盘交互 3 次，如果我们有几百万的数据呢？这个时间更加难以 估计。

所以我们的解决方案是什么呢？

第一个就是让每个节点存储更多的数据。

第二个，节点上的关键字的数量越多，我们的指针数也越多，也就是意味着可以有 更多的分叉（我们把它叫做“路数”）。 因为分叉数越多，树的深度就会减少（根节点是 0）。 这样，我们的树是不是从原来的高瘦高瘦的样子，变成了矮胖矮胖的样子？ 这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

1. 多路平衡查找树（B Tree）（分裂、合并）

Balanced Tree 这个就是我们的多路平衡查找树，叫做 B Tree（B 代表平衡）。

跟 AVL 树一样，B 树在枝节点和叶子节点存储键值、数据地址、节点引用。

它有一个特点：分叉数（路数）永远比关键字数多 1。比如我们画的这棵树，每个节 点存储两个关键字，那么就会有三个指针指向三个子节点。

B Tree 的查找规则是什么样的呢？

比如我们要在这张表里面查找 15。

因为 15 小于 17，走左边。

因为 15 大于 12，走右边。

在磁盘块 7 里面就找到了 15，只用了 3 次 IO。

这个是不是比 AVL 树效率更高呢？

那 B Tree 又是怎么实现一个节点存储多个关键字，还保持平衡的呢？跟 AVL 树有什 么区别？

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

比如 Max Degree（路数）是 3 的时候，我们插入数据 1、2、3，在插入 3 的时候， 本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有 4 个指针， 子节点会变成 4 路，所以这个时候必须进行分裂。把中间的数据 2 提上去，把 1 和 3 变 成 2 的子节点。

如果删除节点，会有相反的合并的操作。 注意这里是分裂和合并，跟 AVL 树的左旋和右旋是不一样的。 我们继续插入 4 和 5，B Tree 又会出现分裂和合并的操作。

从这个里面我们也能看到，在更新索引的时候会有大量的索引的结构的调整，所以 解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键。 节点的分裂和合并，其实就是 InnoDB 页的分裂和合并。

1. B+树（加强版多路平衡查找树）

   B Tree 的效率已经很高了，为什么 MySQL 还要对 B Tree 进行改良，最终使用了 B+Tree 呢？ 总体上来说，这个 B 树的改良版本解决的问题比 B Tree 更全面。 我们来看一下 InnoDB 里面的 B+树的存储结构：

   
   MySQL 中的 B+Tree 有几个特点：

1、它的关键字的数量是跟路数相等的；

2、B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索 到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一 层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶 子节点。

举个例子：假设一条记录是 1K，一个叶子节点（一页）可以存储 16 条记录。非叶 子节点可以存储多少个指针？ 假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点（一页）可以存储 16384/14=1170 个这样的 单元（键值+指针），代表有 1170 个指针。

树 深 度 为 2 的 时 候 ， 有 1170^2 个 叶 子 节 点 ， 可 以 存 储 的 数 据 为 1170*1170*16=21902400。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询 数据最多需要访问 3 次磁盘。 所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

3、B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数 据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。

4、它是根据左闭右开的区间 [ )来检索数据。

我们来看一下 B+Tree 的数据搜寻过程：

1）比如我们要查找 28，在根节点就找到了键值，但是因为它不是页子节点，所以 会继续往下搜寻，28 是[28,66)的左闭右开的区间的临界值，所以会走中间的子节点，然 后继续搜索，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后 在叶子节点上找到了需要的数据。

2）第二个，如果是范围查询，比如要查询从 22 到 60 的数据，当找到 22 之后，只 需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）。

总结一下，InnoDB 中的 B+Tree 的特点：

1)它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。

B Tree 解决的两大问题 是什么？（每个节点存储更多关键字；路数更多）

2)扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据）

• B+Tree 的磁盘读写能力相对于 B Tree 来说更强（根节点和枝节点不保存数据区， 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）

4)排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）

5)效率更加稳定（B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的）

什么是索引?

• 索引可以理解为排好序的快速查找数据结构，加了索引后会进行排序因此会更快。索引会影响where 后面的查找和 order by 后面的排序。
• 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
• 我们平常所说的索引，如果没有特别指明，都是指 B+树 (多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。
• 优势：提高数据检索效率，减低数据库的IO成本，降低数据排序成本，降低CPU消耗。
• 劣势：索引本质也是一张表，也要占空间。
  提高了查询效率，但增删改时也要修改索引数据。
  索引是不断优化研究建立出来的。

举例：
添加：create [unique] index idx_user_name on user(name);
	 alter mytable add [unique] index [indexName] on (columnname(length));

删除：drop index [indexName] on mytable;
查看：show index from table_name\G (\G的意义为纵向打印)

有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

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

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

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

段区块
高度最好不超过三，三层的b树可以表示上百万的数据

索引的选择性是指索引列中不同值的数目与表中记录数的比。

• 建立索引的情况：
  主键自动建立唯一索引。
  频繁作为查询条件的字段应该创建索引(where 后面的语句)。
  查询中与其它表关联的字段，外键关系建立索引。
  单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)。
  查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
  查询中统计或者分组字段。
• 不该建立索引：
  表记录太少。
  经常增删改的表（Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。）因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
  Where条件里用不到的字段不创建索引。
  数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
  注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
• 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

需要注意group by，正常来说要么不建索引，要么一定要尽量根据索引的个数和顺序来，否则特别容易产生文件内排序。

及order by 相关问题

索引分类

https://www.jianshu.com/p/ff195927b8d5

1. InnoDB

   InnoDB 只有一个文件（.ibd 文件），那索引放在哪里呢？

   在 InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文 件是同一个文件，都在.ibd 文件里面。

   在 InnoDB 的主键索引的叶子节点上，它直接存储了我们的数据。
   

   聚集索引（聚簇索引）

   就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录 是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引）。

   在 InnoDB 里面，它组织数据的方式叫做叫做（聚集）索引组织表（clustered index organize table），所以主键索引是聚集索引，非主键都是非聚集索引。 如果 InnoDB 里面主键是这样存储的，那主键之外的索引，比如我们在 name 字段 上面建的普通索引，又是怎么存储和检索数据的呢？

   
   InnoDB 中，主键索引和辅助索引是有一个主次之分的。

   辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主 键索引中查询，最终取得数据。

   比如我们用 name 索引查询 name= ‘青山’，它会在叶子节点找到主键值，也就是 id=1，然后再到主键索引的叶子节点拿到数据。

   为什么在辅助索引里面存储的是主键值而不是主键的磁盘地址呢？如果主键的数据 类型比较大，是不是比存地址更消耗空间呢？

   我们前面说到 B Tree 是怎么实现一个节点存储多个关键字，还保持平衡的呢？

   是因为有分叉和合并的操作，这个时候键值的地址会发生变化，所以在辅助索引里 面不能存储地址。

   另一个问题，如果一张表没有主键怎么办？
   1、如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。
   2、如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
   3、如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐 藏的聚集索引，它会随着行记录的写入而主键递增。

   select _rowid name from t2;
   

• 联合索引：
  
  联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 （name 在左边，phone 在右边）。
  从这张图可以看出来，name 是有序的，phone 是无序的。当 name 相等的时候， phone 才是有序的。

联合索引

按照我们的想法，一个查询创建一个索引，所以我们针对这两条 SQL 创建了两个索引，这种做法觉得正确吗？

CREATE INDEX idx_name on user_innodb(name);
CREATE INDEX idx_name_phone on user_innodb(name,phone);

当我们创建一个联合索引的时候，按照最左匹配原则，用左边的字段 name 去查询 的时候，也能用到索引，所以第一个索引完全没必要。
相当于建立了两个联合索引(name),(name,phone)。

如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引：
index(a)
index(a,b)
index(a,b,c)
用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引的。不能不用第一个字段，不能中断。 这里就是 MySQL 联合索引的最左匹配原则。

覆盖索引

回表：
非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。 例如：select * from user_innodb where name = ‘青山’;

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引 中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免 了回表。
我们先来创建一个联合索引：

-- 创建联合索引
ALTER TABLE user_innodb DROP INDEX comixd_name_phone;
ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);

这三个查询语句都用到了覆盖索引：

EXPLAIN SELECT name,phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';
EXPLAIN SELECT name FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';
EXPLAIN SELECT phone FROM user_innodb WHERE name= '青山' AND phone

Extra 里面值为“Using index”代表使用了覆盖索引。

select * ，用不到覆盖索引。
很明显，因为覆盖索引减少了 IO 次数，减少了数据的访问量，可以大大地提升查询 效率。

前缀索引

当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可
以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引：

create table shop(address varchar(120) not null);
alter table shop add key (address(12));

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的，截取得少了，重复内容太多，字段的散列度（选择性）会降低。怎么计算不同的长度的选择性呢？
先看一下字段在全部数据中的选择度：

select count(distinct address) / count(*) from shop;

通过不同长度去计算，与全表的选择性对比：

select count(distinct left(address,10))/count(*) as sub10, count(distinct left(address,11))/count(*) as sub11, count(distinct left(address,12))/count(*) as sub12, count(distinct left(address,13))/count(*) as sub13
from shop

只要截取前 13 个字段，就已经有比较高的选择性了（这里的数据只是举例）。

索引操作

ALTER TABLE xxx DROP INDEX comidx_name_phone;
ALTER TABLE xxx add INDEX comidx_name_phone (name, phone); # 联合索引

索引优化一般性建议

• 建立索引，要使用离散度（选择度）更高的字段.
• 对于单键索引，尽量选择针对当前query过滤性更好的索引。
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)。
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
• 在建立联合索引的时候，一定要把最常用的列放在最左边
• 在用于 where 判断 order 排序和 join 的（on）字段上创建索引
• 索引的个数不要过多。——浪费空间，更新变慢。
• 区分度低的字段，例如性别，不要建索引。 ——离散度太低，导致扫描行数过多。
• 频繁更新的值，不要作为主键或者索引。 ——页分裂
• 组合索引把散列性高（区分度高）的值放在前面。
• 创建复合索引，而不是修改单列索引。

MySQL优化：

1. 全值匹配（按索引创建顺序匹配的）。
2. 最佳左前缀法则 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
6. mysql 在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描
7. is not null 也无法使用索引,但是is null是可以使用索引的
8. like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作。问题：
   解决 like ‘%字符串%’ 时索引不被使用的方法？？——》%写在右边或者使用覆盖索引。
   %在左边为range，%在右边为ref。
9. 字符串不加单引号索引失效 （否则会发生MySQL的隐式类型转换）
10. 少用or,用它来连接时会索引失效
    

永远用小结果集驱动大结果集。
索引最好加在需要经常查询的字段中。

单表查询优化

新建复合索引但查询的顺序中出现范围时导致索引后面失效，type 为 range。
方法：新建复合索引中不要包含范围查询字段。

两表查询优化

左连接索引应加在右表。（因为左连接左表全都会有）
右连接索引应加在左表。

order by优化

主要解决会不会产生 filesort

select age,birth from tblA order by age ASC（升序）, birth DESC（降序）;

创建索引的顺序和排序的顺序相同就不会产生 filesort。

• ORDER BY满足情况，会使用Index方式排序:
  ORDER BY 语句使用索引最左前列
  使用Where子句与Order BY子句条件列组合满足索引最左前列
  where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。
• 如果不在索引列上，filesort有两种算法：
  双路排序（从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。）
  单路排序（从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。）
• 优化策略
  • Order by 时 select * 是一个大忌，只Query需要的字段， 这点非常重要。
  • 尝试提高 sort_buffer_size
    （不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的）
  • 尝试提高 max_length_for_sort_data
    （提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率）
    

group by优化

基本与 order by 相同

• GROUP BY关键字优化
  group by实质是先排序后进行分组，遵照索引建的最佳左前缀
  当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
  where高于having，能写在where限定的条件就不要去having限定了。

exists 和 in

explain

重点：id、type、key、rows、Extra

• id ：
  id相同，执行顺序由上至下
  id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  id相同不同，同时存在
• select_type（查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询）：
  

名称	作用
SIMPLE	简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED	在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY	在SELECT或WHERE列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUREY	无法被缓存的子查询
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

• table：
  显示这一行的数据是关于哪张表的
• type：
  从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL

名称	作用
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，将遍历全表以找到匹配的行

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

select 后面用到了索引 type 为 index
where 后面用到了索引 type 为 ref

• possible_keys：（可能用到的索引）
  显示可能应用在这张表中的索引，一个或多个。
  查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
• key：（实际用到的索引）
  实际使用的索引。如果为NULL，则没有使用索引
  查询中若使用了覆盖索引，则该索引和查询的select字段重叠
• key_len：
  表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
  key_len字段能够帮你检查是否充分的利用上了索引
• ref：
  显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
• rows：
  rows列显示MySQL认为它执行查询时必须检查的行数。
  越少越好
• Extra：
  包含不适合在其他列中显示但十分重要的额外信息

名称	意义
Using filesort	说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary	使了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
USING index	表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！ 如果同时出现using where，表明索引被用来执行索引键值的查找; 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。覆盖索引(Covering Index)
Using where	表明使用了where过滤
using join buffer	使用了连接缓存
impossible where	where子句的值总是false，不能用来获取任何元组
select tables optimized away	在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者 对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算， 查询执行计划生成的阶段即完成优化。

官网优化建议

1. 在数据库级别进行优化

• 表格的结构是否正确？特别是，列是否具有正确的数据类型，以及每个表是否具有适合工作类型的列？例如，执行频繁更新的应用程序通常具有许多包含少量列的表，而分析大量数据的应用程序通常具有包含许多列的表。
• 是否设置了正确的索引来提高查询效率？
• 您是否为每个表使用适当的存储引擎，并利用所使用的每个存储引擎的优势和功能？特别是，选择事务性存储引擎（如）或非事务性存储引擎（如）对于性能和可伸缩性非常重要。InnoDBMyISAM

注意
InnoDB是新表的默认存储引擎。在实践中，高级性能功能意味着表的性能通常优于较简单的表，特别是对于繁忙的数据库。InnoDBInnoDBMyISAM

• 每个表是否使用适当的行格式？此选择还取决于用于表的存储引擎。特别是，压缩表使用较少的磁盘空间，因此读取和写入数据所需的磁盘 I/O 更少。压缩可用于具有表的各种工作负载以及只读表。InnoDBMyISAM

• 应用程序是否使用适当的锁定策略？例如，在可能的情况下允许共享访问，以便数据库操作可以并发运行，并在适当的时候请求独占访问，以便关键操作获得最高优先级。同样，存储引擎的选择也很重要。存储引擎无需您参与即可处理大多数锁定问题，从而在数据库中实现更好的并发性，并减少代码的试验和优化量。InnoDB

• 用于缓存的所有内存区域的大小是否正确？也就是说，大到足以容纳经常访问的数据，但不能大到使物理内存过载并导致分页。要配置的主要内存区域是缓冲池和密钥缓存。InnoDBMyISAM

2. 在硬件级别进行优化
   随着数据库变得越来越繁忙，任何数据库应用程序最终都会达到硬件限制。DBA 必须评估是否可以调整应用程序或重新配置服务器以避免这些瓶颈，或者是否需要更多硬件资源。系统瓶颈通常来自以下来源：

• 磁盘寻道。磁盘查找一段数据需要时间。对于现代磁盘，平均时间通常低于10ms，因此理论上我们可以在一秒钟内执行大约100次寻道。使用新磁盘时，此时间会缓慢改进，并且很难针对单个表进行优化。优化寻道时间的方法是将数据分布到多个磁盘上。
• 磁盘读写。当磁盘处于正确的位置时，我们需要读取或写入数据。对于现代磁盘，一个磁盘可提供至少 10–20MB/s 的吞吐量。这比查找更容易优化，因为您可以从多个磁盘并行读取。
• 中央处理器周期。当数据在主内存中时，我们必须对其进行处理才能获得结果。与内存量相比，具有较大的表是最常见的限制因素。但是对于小桌子，速度通常不是问题。
• 内存带宽。当 CPU 需要的数据超过 CPU 缓存所能容纳的数据时，主内存带宽就会成为瓶颈。对于大多数系统来说，这是一个不常见的瓶颈，但需要注意。

3. 平衡便携性和性能
   要在可移植的MySQL程序中使用面向性能的SQL扩展，您可以将MySQL特定的关键字包装在注释分隔符内的语句中。其他 SQL 服务器忽略注释的关键字。有关编写注释的信息，请参见第 9.7 节 “注释”。/*! */

优化 SELECT 语句

https://dev.mysql.com/doc/refman/8.0/en/select-optimization.html

Information_schema

https://dev.mysql.com/doc/mysql-infoschema-excerpt/8.0/en/preface.html

查询截取分析

数据库慢原因：

• 内存泄漏、死锁、网络、sql语句差。

解决方法：

1. 慢查询的开启并捕获
2. explain+慢SQL分析
3. show profile查询 SQL 在 MySQL 服务器里面的执行细节和生命周期情况
4. 参数调优

慢查询日志

慢查询日志用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。结合之前explain进行全面分析。
会对性能产生一定影响，需要时再打开

查看是否开启及日志目录：show variables like "%slow_query_log%";
开启：set global slow_query_log = 1;，只对当前数据库生效，且数据库重启后失效。
慢查询时间：show variables like "%long_query_time%";
设置慢查询时间：set global long_query_time = 3; 需要重新连接才能看到修改值。
查询慢查询条数：show global status like "%slow_queries%";
日志分析工具：mysqldumpslow [选项] -t 数量 log目录

命令	作用
-s	是表示按照何种方式排序；
-c	访问次数
-l	锁定时间
-r	返回记录
-t	查询行数
-al	平均锁定时间
-ar	平均返回记录数
-at	平均查询时间
-t	即为返回前面多少条的数据；
-g	后边搭配一个正则匹配模式，大小写不敏感的；

Show Profile方法

• 查看是否开启：show variables like "profiling";
• 开启：set profiling = on;
• 查看：show profiles;
• 查看某一个：show profile [选项(cpu,block io)] for query 查询id;

选项	作用
ALL	显示所有的开销信息
BLOCK IO	显示块IO相关开销
CONTEXT SWITCHES	上下文切换相关开销
CPU	显示CPU相关开销信息
IPC	显示发送和接收相关开销信息
MEMORY	显示内存相关开销信息
PAGE FAULTS	显示页面错误相关开销信息
SOURCE	显示和Source_function，Source_file，Source_line相关的开销信息
SWAPS	显示交换次数相关开销的信息

日常开发需要注意的结论

语句	意义
converting HEAP to MyISAM	查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table	创建临时表，拷贝数据到临时表，用完再删除
Copying to tmp table on disk	把内存中临时表复制到磁盘，危险！！！
locked

MySQL锁机制

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

事务（Transaction）四大（ACID）属性

• 事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。
  • 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
    一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
    隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
    持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

三大范式

1. 是数据的原子性
2. 是确保每列都和主键相关，意思是：只做一件事
3. 确保每列都和主键直接相关，而不是间接相关，意思是不能存在传递依赖

事务隔离级别

MySQL默认为可重复读
查看事务隔离级别：show variables like 'transaction_isolation';

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

参考：https://www.cnblogs.com/snsdzjlz320/p/5761387.html

• 第1级别：Read Uncommitted(读未提交)
  (1)所有事务都可以看到其他未提交事务的执行结果
  (2)本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少
  (3)该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据
• 第2级别：Read Committed(读已提交)
  (1)这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）
  (2)它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变
  (3)这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。
  |——>导致这种情况的原因可能有：(1)有一个交叉的事务有新的commit，导致了数据的改变;(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit
• 第3级别：Repeatable Read(可重复读)
  (1)这是MySQL的默认事务隔离级别
  (2)它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
  (3)此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行
  (4)InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题
• 第4级别：Serializable(可串行/序列化)
  (1)这是最高的隔离级别
  (2)它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
  (3)在这个级别，可能导致大量的超时现象和锁竞争

并发事务处理带来的问题：

• 脏读(Dirty Reads)： 事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
• 不可重复读(Non-Repeatable Reads)：一个事务范围内两个相同的查询却返回了不同数据。
• 幻读(Phantom Reads)：事务A读取到了事务B提交的新增数据，不符合隔离性。
• 更新丢失(Lost Update)：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。

锁的分类

• 从对数据操作的类型（读\写）分
  读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
  写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
• 从对数据操作的粒度分
  表锁
  行锁

读锁/共享锁（读阻塞写）

对表上锁：lock table 表名 read(write);
查看所有表锁情况：show open tables;
解锁：unlock tables;

共享锁(Share Lock)
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
添加行锁：SELECT ... LOCK IN SHARE MODE;
在查询语句后面增加 LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表（行？），而且这些线程读取的是同一个版本的数据。

写锁/排它锁（阻塞读和写）

排他锁（exclusive Lock）
共享锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
添加写锁：SELECT ... FOR UPDATE;
在查询语句后面增加 FOR UPDATE ，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

mylockwrite(MyISAM)

session_1session_2获得表mylock的WRITE锁定待Session1开启写锁后，session2再连接终端当前session对锁定表的查询+更新+插入操作都可以执行： 其他session对锁定表的查询被阻塞，需要等待锁被释放： 在锁表前，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞住。释放锁 Session2获得锁，查询返回：

表锁（偏读）

特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行锁（偏写）

特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

• 无索引行锁升级为表锁：Session_1Session_2正常情况，各自锁定各自的行，互相不影响，一个2000另一个3000，由于在column字段b上面建了索引，如果没有正常使用，会导致行锁变表锁比如没加单引号导致索引失效，行锁变表锁被阻塞，等待。只到Session_1提交后才阻塞解除，完成更新。
• 【什么是间隙锁】
  当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，
  InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。
• 如何锁定一行：select xxx.. for update 直到 commit 释放。
• 【如何分析行锁定】
  通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：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（等待总时长）这三项。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
  尽可能较少检索条件，避免间隙锁
  尽量控制事务大小，减少锁定资源量和时间长度
  锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
  涉及相同表的事务，对于调用表的顺序尽量保持一致。
  在业务环境允许的情况下,尽可能低级别事务隔离

乐观锁与悲观锁

乐观锁

        乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

        通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

悲观锁

        悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。
        说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

SQL面试题/优化相关

面试题

group by 与 distinct 对比

在语义相同，有索引的情况下： group by和 distinct 都能使用索引，效率相同。因为group by和distinct近乎等价，distinct 可以被看做是特殊的group by。
在语义相同，无索引的情况下： distinct效率高于group by。原因是distinct 和 group by都会进行分组操作，但group by在 MySQL8.0 之前会进行隐式排序，导致触发 filesort，sql 执行效率低下。但从 MySQL8.0 开始，MySQL 就删除了隐式排序，所以，此时在语义相同，无索引的情况下，group by和distinct的执行效率也是近乎等价的。

两者的语法区别在于，group by可以进行单列去重，group by的原理是先对结果进行分组排序，然后返回每组中的第一条数据。且是根据group by的后接字段进行去重的。

在 MySQL8.0 之前,GROUP BY会默认根据作用字段（GROUP BY的后接字段）对结果进行排序。在能利用索引的情况下，GROUP BY不需要额外进行排序操作；但当无法利用索引排序时，MySQL 优化器就不得不选择通过使用临时表然后再排序的方式来实现GROUP BY了。

SQL 优化

面试官：给你一段有问题的SQL，如何优化？

理论

• 合理使用索引
• 使用UNION ALL替代UNION
• 不要使用select * 写法
• JOIN字段建议建立索引
• 避免复杂SQL语句

找出有问题的SQL

1. 系统层面
   CPU消耗严重
   IO等待严重
   页面响应时间过长
   应用的日志出现超时等错误
2. SQL语句层面
   冗长
   执行时间过长
   从全表扫描获取数据
   执行计划中的rows、cost很大

实践

其他

主从复制

• 复制的基本原理
  slave会从master读取binlog来进行数据同步
  
  • 三步骤+原理图
    master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events
    slave将master的binary log events拷贝到它的中继日志（relay log）
    slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的
• 复制的基本原则
  每个slave只有一个master
  每个slave只能有一个唯一的服务器ID
  每个master可以有多个salve
• 复制的最大问题
  延时

MySQL 常用语句

1. 修改字段的长度

ALTER TABLE 表名 MODIFY COLUMN 字段名 数据类型(修改后的长度)

2. 修改字段的名称

ALTER TABLE <表名> CHANGE <字段名> <字段新名称> <字段的类型>

3. 新增字段
   新增默认为空的字段

ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 DEFAULT NULL;

新增不为空的字段

ALTER TABLE 表名 ADD COLUMN 字段名 字段类型  NOT NULL;

4. 删除字段

ALTER TABLE 表名 DROP COLUMN 字段名;

5. 批量增加字段

ALTER TABLE 表名 ADD (字段1 类型(长度),字段2 类型(长度),字段3 类型(长度));

6. 批量修改字段名称

ALTER TABLE 表 CHANGE 修改前字段名  修改后字段名称 int(11) NOT NULL,
CHANGE 修改前字段名  修改后字段名称 int(11) NOT NULL,
CHANGE 修改前字段名  修改后字段名称 int(11) NOT NULL,
CHANGE 修改前字段名  修改后字段名称 int(11) NOT NULL,
CHANGE 修改前字段名  修改后字段名称 int(11) NOT NULL

7. 新建数据表

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

8. 查看表结构

DESC 表名

9. 删除数据表

DROP TABLE 表名;

10. 插入数据

INSERT INTO 表名 ( 字段1, 字段2,...字段N ) VALUES ( value1, value2,...valueN );

SQL 左连接 一对多查询

SELECT
  a.books,
  GROUP_CONCAT(DISTINCT a.name SEPARATOR '||') AS NAMES,  -- 使用 distinct 去除重复数据、
  GROUP_CONCAT(a.code ORDER BY a.code DESC SEPARATOR '||') AS codes  -- 使用order by 对数据进行排序 5 FROM 6  temp_info a 7 GROUP BY a.books ;

插入返回主键

        <selectKey keyProperty="accountid" resultType="int" order="AFTER"> <!-- keyProperty 填实体类属性名 -->
            SELECT LAST_INSERT_ID()
        </selectKey>

其他

Join语法

select <select_list> from TableA A INNER JOIN TableB B ON A.Key = B.Key;

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NUll;

MySQL不支持 FULL OUTER 这种语法

SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key 

MySQL 中可以写

select <select_list> from TebleA A left join TebleB B ON A.Key = B.Key
union
select <select_list> from TableA A right join TableB B ON A.Key = B.Key;

MySQL不支持 FULL OUTER 这种语法

SELECT <select_list> FORM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NUll OR B.Key IS NULL; 

MySQL 中改为

select * from TableA A left join TableB B on A.Key = B.Key where B.Key is null
union 
select * from TableA A right join TableB B on A.Key = B.Key where A.Key is null;

批量数据脚本操作

• 批量数据脚本（往表里插入1000W数据）
  • 建表

# 新建库
create database bigData;
use bigData;
#1 建表dept
CREATE TABLE dept(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
dname VARCHAR(20) NOT NULL DEFAULT "",  
loc VARCHAR(13) NOT NULL DEFAULT ""  
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;

#2 建表emp
CREATE TABLE emp
(id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ; 

• 设置参数log_bin_trust_function_creators

创建函数，假如报错：This function has none of DETERMINISTIC…
# 由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
show variables like ‘log_bin_trust_function_creators’;
set global log_bin_trust_function_creators=1;
# 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法（不建议）：
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

• 创建函数,保证每条数据都不同
  • 随机产生字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    ##方法开始
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 ##声明一个 字符窜长度为 100 的变量 chars_str ,默认值
DECLARE return_str VARCHAR(255)
DEFAULT '';
DECLARE i INT DEFAULT 0;
##循环开始
WHILE i < n DO  
SET return_str
=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
##concat 连接函数  ，substring(a,index,length) 从index处开始截取
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
 
#假如要删除
#drop function rand_string;

• 随机产生部门编号

#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);  
RETURN i;
END $$
 
#假如要删除
#drop function rand_num;

• 创建存储过程
  • 创建往emp表中插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10))  
BEGIN
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成0  ；提高执行效率
 SET autocommit = 0;    
 REPEAT  ##重复
 SET i = i + 1;  
 INSERT INTO emp (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());  
 UNTIL i = max_num   ##直到  上面也是一个循环
 END REPEAT;  ##满足条件后结束循环
 COMMIT;   ##执行完成后一起提交
 END $$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;
 

- 创建往dept表中插入数据的存储过程

#执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$ 
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

• 调用存储过程
  • dept

DELIMITER ;
CALL insert_dept(100,10); 

- emp

#执行存储过程，往emp表添加50万条数据
DELIMITER ;    #将结束标志换回 ;
CALL insert_emp(100001,500000);
CALL insert_emp10000(100001,10000); 

	大量数据案例

Linux中安装MySQL常见问题

• 不支持中文，修改 /etc/my.cnf 文件，[mysqld] 添加支持中文字符集 character-set-server=utf8，重启mysql服务，如果修改前已经创建库，需要修改库的字符集。
  修改数据库的字符集：alter database 库名 character set 'utf8';
  修改数据表的字符集：alter table 表名 convert to character set 'utf8';

授权命令：

grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;
该权限如果发现没有该用户，则会直接新建一个用户。
比如

• grant select,insert,delete,drop on atguigudb.* to li4@localhost ;
  #给li4用户用本地命令行方式下，授予atguigudb这个库下的所有表的插删改查的权限。
• grant all privileges on *.* to joe@'%' identified by '123';
  #授予通过网络方式登录的的joe用户 ，对所有库所有表的全部权限，密码设为123.

修改密码

• 修改当前用户的密码:
  set password =password('123456')
  修改某个用户的密码:
  update mysql.user set password=password('123456') where user='li4';
flush privileges; #所有通过user表的修改，必须用该命令才能生效。