Mysql高级-总结

MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

 

索引是帮助MYSQL高速获取数据的数据结构，索引本身也很大，不可能完全存储在内存中，因此索引往往以索引文件的形式存在磁盘文件中

 

索引有哪几种类型？

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

• 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

• 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

• 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

 

哪些情况下不需要建立索引：

1.表记录太少

2.经常增删改的表

3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，注意，如果某个数据列包含许多重复的内容，为他建立索引就没有太大的效果

 explain作用

1.表的读取顺序

2.数据读取操作的操作类型

3.哪些索引可以使用

4.哪些索引被实际使用

5.表之间的引用

6.每张表有多少行被优化器查询

EXPLAIN select * from `agency_game` where `game_id`=10 union all select * from `agency_game` where game_id=20;

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

Id:

1.id相同，执行顺序由上到下

2.id不同，id值越大优先级越高，越先被执行

 

 select_type:

SIMPLE 简单的SELECT查询，查询中不包含子查询或者UNION

PRIMARY 查询中若包含任何复杂的子成分，最外层查询则被标记为

SUBQUERY 在SELECT或者where列表中包含了子查询

DERIVED 在from列表中包含的子查询被标记为DERIVED(衍生)Mysql会递归执行这些子查询，把结果放在临时表里

UNION 若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

UNION_RESULT从UNION表获取结果的SELECT

 

type:

访问类型排序，显示查询用了何种类型，从最好到最差依次是：

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

一般来说，得至少达到range级别，最好能达到ref

 

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

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

possible_key和key结合看，理论上用到的索引，和实际上查询用到的索引。

---

什么是覆盖索引：

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中 （联合索引），可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数，这就是 覆盖索引，在了解覆盖索引之前，我们先来看看什么是索引。

---

key_len：表示索引中使用的字节数，可通过该列计算查询中，使用的索引长度，在不损失精度的情况下，长度越小越好，key_len显示的值为索引字段最大的可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值。

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

 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子句的情况下，基于索引优化Min/Max操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

 

Using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。

如果同时出现了using where，表明索引被用来执行索引键值的查找；

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

覆盖索引：一说为索引覆盖。

理解方式一：就是select的数据列只用从索引中就能取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

 

注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，

因为如果将所有的字段一起做索引会导致索引文件下降，查询性能下降。

理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此他不必读取整个行，毕竟索引叶子节点存储了他们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

 

 二叉树在数据单边增长的时候相当于链表结构，查询数据效率下降

 

B树在红黑树的每个节点上拓展多个节点，横向拓展，形成树的高度可控。把索引中间的值，放到子节点上（二分查找效率快）

B树在每个节点存储data（data中保存着指向磁盘空间的数据地址）。

B+树在叶子节点存储数据，非叶子节点不存储数据只存储索引，都是冗余索引，在叶子节点里有整张表的完整索引。

B+tree性质：

1.）n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

2.）所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.）所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

4.）B+ 树中，数据对象的插入和删除仅在叶节点上进行。

5.）B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

2）哈希索引

简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。

索引算法有哪些？

索引算法有 BTree算法和Hash算法

BTree算法

BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量(以通配符开头的会导致索引失效)， 例如：

-- 只要它的查询条件是一个不以通配符开头的常量
select * from user where name like 'jack%'; 
-- 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： 
select * from user where name like '%jack'; 

 

Hash算法

Hash Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

索引设计的原则？

1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2. 基数较小的类，索引效果较差，没有必要在此列建立索引
3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
4. 不要过多索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

 

创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的（根据树的规则，会逐一比对每个索引的值，直到遇到大小不同的，后面的d会丢弃），如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

 

 

 存储引擎是相对于表来说的

数据库表相对于磁盘存储位置在数据库安装目录下的data下面   .frm  是数据库表结构文件

.MYD是 MY是代表MYISAM，D是代表数据，存储数据

.MYI  I代表Index，索引，存放的是索引

 

 

 InnoDB如果没有创建主键，会自动找一个字段（数据不重复）列维护为主键，如果没有MySQL会在后台自己维护一列隐藏索引

 

联合索引不能跳过第一个索引字段，如下图不能跳过name（第一二条不走索引） 最左前缀原理

如果第一个跳过第一个字段，第二个字段是无序的，只有在第一个字段顺序相等的情况下才会去比较第二个字段的排序 （索引从左到右依次递增）

1、b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

2、比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）

 

MySQL中InnoDB引擎的行锁是怎么实现的？

答：InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

InnoDB存储引擎的锁的算法有三种

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

1. innodb对于行的查询使用next-key lock
2. Next-locking keying为了解决Phantom Problem幻读问题
3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

 

什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

视图有哪些特点？

视图的特点如下:

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。

• 视图是由基本表(实表)产生的表(虚表)。

• 视图的建立和删除不影响基本表。

• 对视图内容的更新(添加，删除和修改)直接影响基本表。

• 当视图来自多个基本表时，不允许添加和删除数据。

视图的操作包括创建视图，查看视图，删除视图和修改视图。

视图的使用场景有哪些？

视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。

下面是视图的常见使用场景：

• 重用SQL语句；

• 简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；

• 使用表的组成部分而不是整个表；

• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；

• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的优点

1. 查询简单化。视图能简化用户的操作
2. 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
3. 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

视图的缺点

1. 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。

2. 修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

   这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图（其中有些例外）

什么是游标？

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

存储过程与函数

什么是存储过程？有哪些优缺点？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。

2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

触发器

什么是触发器？触发器的使用场景有哪些？

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。
• 大家需要牢记以上基础知识点，重点是理解数据类型CHAR和VARCHAR的差异，表存储引擎InnoDB和MyISAM的区别。

MySQL中都有哪些触发器？

在MySQL数据库中有如下六种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

 

超键、候选键、主键、外键分别是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
• 候选键：是最小超键，即没有冗余元素的超键。
• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

 

SQL 约束有哪几种？

SQL 约束有哪几种？

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
• UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK: 用于控制字段的值范围。

 

 

六种关联查询

• 交叉连接（CROSS JOIN）
• 内连接（INNER JOIN）
• 外连接（LEFT JOIN/RIGHT JOIN）
• 联合查询（UNION与UNION ALL）
• 全连接（FULL JOIN）
• 交叉连接（CROSS JOIN）

 

SELECT * FROM A,B(,C)或者SELECT * FROM A CROSS JOIN B (CROSS JOIN C)#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用内连接（INNER JOIN）SELECT * FROM A,B WHERE A.id=B.id或者SELECT * FROM A INNER JOIN B ON A.id=B.id多表中同时符合某种条件的数据记录的集合，INNER JOIN可以缩写为JOIN

 

内连接分为三类

• 等值连接：ON A.id=B.id
• 不等值连接：ON A.id > B.id
• 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外连接（LEFT JOIN/RIGHT JOIN）

• 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
• 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

联合查询（UNION与UNION ALL）

SELECT * FROM A UNION SELECT * FROM B UNION ...

• 一、区别1：取结果的交集

  1、union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;

  2、union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;
  二、区别2：获取结果后的操作

  1、union: 会对获取的结果进行排序操作

  2、union all: 不会对获取的结果进行排序操作

  三、区别3：

  1、union看到结果中ID=3的只有一条

  select * from student2 where id < 4

  union

  select * from student2 where id > 2 and id < 6

  2、union all 结果中ID=3的结果有两个
  select * from student2 where id < 4

  union all

  select * from student2 where id > 2 and id < 6

四、总结

union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高
 

什么是子查询

1. 条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果

2. 嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

 

子查询的三种情况

1. 子查询是单行单列的情况：结果集是一个值，父查询使用：=、 <、 > 等运算符

-- 查询工资最高的员工是谁？ 
select  * from employee where salary=(select max(salary) from employee);   

 

1. 子查询是多行单列的情况：结果集类似于一个数组，父查询使用：in 运算符

-- 查询工资最高的员工是谁？ 
select  * from employee where salary=(select max(salary) from employee);    

 

1. 子查询是多行多列的情况：结果集类似于一张虚拟表，不能用于where条件，用于select子句中做为子表

-- 1) 查询出2011年以后入职的员工信息
-- 2) 查询所有的部门信息，与上面的虚拟表中的信息比对，找出所有部门ID相等的员工。
select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;    

-- 使用表连接：
select d.*, e.* from  dept d inner join employee e on d.id = e.dept_id where e.join_date >  '2011-1-1'  

 

mysql中 in 和 exists 区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

1. 如果查询的两个表大小相当，那么用in和exists差别不大。
2. 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
3. not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

varchar与char的区别

char的特点

• char表示定长字符串，长度是固定的；

• 如果插入数据的长度小于char的固定长度时，则用空格填充；

• 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

• 对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

• varchar表示可变长字符串，长度是可变的；

• 插入的数据是多长，就按照多长来存储；

• varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；

• 对于varchar来说，最多能存放的字符个数为65532

总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

 

varchar(50)中50的涵义

最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。

int(20)中20的涵义

是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示

 

mysql为什么这么设计

对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

mysql中int(10)和char(10)以及varchar(10)的区别

• int(10)的10表示显示的数据的长度，不是存储数据的大小；chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。

  int(10) 10位的数据长度 9999999999，占32个字节，int型4位
  char(10) 10位固定字符串，不足补空格 最多10个字符
  varchar(10) 10位可变字符串，不足补空格 最多10个字符

• char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间

• varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符

FLOAT和DOUBLE的区别是什么？

• FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
• DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

drop、delete与truncate的区别

三者都表示删除，但是三者有一些差别：

	Delete	Truncate	Drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有的数据行，索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

 

mysql 分页

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 

 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last. 

 如果只给定一个参数，它表示返回最大的记录行数目：

mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行 

换句话说，LIMIT n 等价于 LIMIT 0,n。

 

慢查询日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

开启慢查询日志

配置项：slow_query_log

可以使用show variables like ‘slov_query_log’查看是否开启，如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，它会在datadir下产生一个xxx-slow.log的文件。

设置临界时间

配置项：long_query_time

查看：show VARIABLES like 'long_query_time'，单位秒

设置：set long_query_time=0.5

实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉

查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

 

 

主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。

因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。

总之，在数据量大一些的情况下，用自增主键性能会好一些。

关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

 

字段为什么要求定义为not null？

null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

优化查询过程中的数据访问

• 访问数据太多导致查询性能下降
• 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
• 确认MySQL服务器是否在分析大量不必要的数据行
• 避免犯如下SQL语句错误
• 查询不需要的数据。解决办法：使用limit解决
• 多表关联返回全部列。解决办法：指定列名
• 总是返回全部列。解决办法：避免使用SELECT *
• 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
• 是否在扫描额外的记录。解决办法：
• 使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：
• 使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
• 改变数据库和表的结构，修改数据表范式
• 重写SQL语句，让优化器可以以更优的方式执行查询。

优化长难的查询语句

• 一个复杂查询还是多个简单查询
• MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多
• 使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。
• 切分查询
• 将一个大的查询分为多个小的相同的查询
• 一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。
• 分解关联查询，让缓存的效率更高。
• 执行单个查询可以减少锁的竞争。
• 在应用层做关联更容易对数据库进行拆分。
• 查询效率会有大幅提升。
• 较少冗余记录的查询。

优化特定类型的查询语句

• count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)
• MyISAM中，没有任何where条件的count(*)非常快。
• 当有where条件时，MyISAM的count统计不一定比其它引擎快。
• 可以使用explain查询近似值，用近似值替代count(*)
• 增加汇总表
• 使用缓存

优化关联查询

• 确定ON或者USING子句中是否有索引。
• 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

优化子查询

• 用关联查询替代
• 优化GROUP BY和DISTINCT
• 这两种查询据可以使用索引来优化，是最有效的优化方法
• 关联查询中，使用标识列分组的效率更高
• 如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。
• WITH ROLLUP超级聚合，可以挪到应用程序处理

优化LIMIT分页

• LIMIT偏移量大的时候，查询效率较低
• 可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

优化UNION查询

• UNION ALL的效率高于UNION

 

优化WHERE子句

解题方法

对于此类考题，先说明如何定位低效SQL语句，然后根据SQL语句可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

SQL语句优化的一些方法？

• 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null
-- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=

 

• 3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
• 4.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20
-- 可以这样查询：
select id from t where num=10 union all select id from t where num=20

 

• 5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3) 
-- 对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

 

6.下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num
-- 可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

 8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100
-- 应改为:
select id from t where num=100*2

 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’
-- name以abc开头的id应改为:
select id from t where name like ‘abc%’

 

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

数据库优化

为什么要优化

• 系统的吞吐量瓶颈往往出现在数据库的访问速度上
• 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
• 数据是存放在磁盘上的，读写速度无法和内存相比

优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等