mysql通关面试宝典_mysql面试宝典

1.mysql事务的常见异常和隔离级别

1.常见异常：

​ -第一类丢失更新、第二类丢失更新；

​ -脏读、不可重复读、幻读。

第一类丢失更新：某一个事务的回滚操作(rollback)，导致另一个事务已经提交(commit)的数据丢失了. 如：事务2提交N=9,但是事务1回滚到N=10,那么事务2的9就丢失了。

第二类丢失更新：某一个事务的提交操作(commit)，导致另一个事务已经提交(commit)的数据丢失了。如：事务2提交N=9,但是事务1提交数据N=11,那么事务2的9就丢失了。

脏读：某一个事务读取到了另一个事务未提交的数据，即错误的数据，脏读现象。如：事务1未提交数据N=11的过程中，事务2读取到未提交数据11，之后事务1回滚操作到10。

不可重复读(针对某一条数据)：某事务1读取到数据a，之后事物2去修改该条数据a,然后一会儿事务1又重新读数据a，发现前后读取数据不一致(已经被事务2修改了，但是事务1不知道)，前后读取的数据不一致就是不可重复读现象。如事务Read:N=10/11不一致

幻读(针对某几行/几条数据的增删)：某事务1查询有多少数据，之后事务2去增加或者删除数据，导致事务1再次重新去查询这些数据时，发现数据多了或少了，就像查询到了虚假的数据，即幻读现象。如：事务2查询有3条数据，事务1去增加数据，事务2再次查询就有4条数据了。一定是查询前后行数不一致。

2.事务的隔离级别:

​ -Read Uncommitted：读取未提交的数据；安全级别最低，但是效率最高，所有异常问题都不能解决(丢失、脏读、不可重复读、幻读)

​ -Read committed：读已提交的数据；解决了第一类丢失更新(rollback导致丢失)，脏读问题

​ -Repeatable Read(mysql默认隔离级别)：可重复读；解决了丢失、脏读、不可重复读，

​ -Serializable:串行化读/序列化：解决所有异常问题。隔离级别最高，可以解决所有的问题，但是他解决问题是有代价的，需要对数据加锁，加锁会降低数据处理的性能，效率最低。（银行，金融的业务可以选择）。

一般使用读已提交和可重复读，如果对安全性要求比较高就是用可重复读，否则使用读已提交，具体情况格局业务问题而定。

2.mysql的B树索引，B+树索引，哈希索引原理。

一般我们是为了减少IO磁盘次数，将树变得“矮胖”，所以增加树的度(树的阶数);

索引是一种数据结构。索引本身很大，不可能全部存储在内存中，因此索引以索引表的形式存储在磁盘中。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度

1. B树索引：

B树索引是在二叉排序树的基础上升级的，为了减少磁盘IO次数(IO查找次数就是树的高度)。B树的每个节点都存放键值(索引值：主键索引值或非主键索引值)和数据库data数据(除主键外的表中的数据)，即key和data（17-data/35-data），索引值就是主键值(InoDB默认是采用聚集索引中的主键索引)。叶子节点没有指针(而B+树添加了叶子节点的指针)。

其实磁盘块就是页，每次进行IO读取时都会读取一页的数据：

假如我们要查找 id=28 的用户信息，那么我们在上图 B 树中查找的流程如下：

1、先找到根节点页 1，判断 28 在键值 17 和 35 之间，根据页 1 中的指针 p2 找到页 3。（1次
IO）
2、将 28 和页 3 中的键值相比较，28 在 26 和 30 之间，根据页 3 中的指针 p2 找到页 8。（1次
IO）
3、将 28 和页 8 中的键值相比较，发现匹配的键值 28，键值 28 对应的用户信息为（28，bv）。
（1次IO）  
1
2
3
4
5
6

2. B+树索引：

下面图更清晰：

B+树与B树区别：

(1)B+树的非叶子节点不存放data数据(除主键外的表中的数据)，只存放键值(主键索引或非主键索引)，而B树所有节点不仅存放data数据，还存放键值；
(2)B+树的data数据都存放在叶子节点,并且叶子节点还存放索引值；(主键索引：主键值+data数据就是表中的各行数据；非主键索引：非主键索引值（辅助索引）+data数据就是主键值)
(3)B+树的每一个父节点的元素都出现在子节点中，是子节点的最大(或最小)元素，如18出现在一二三层；
(4)B+树添加了叶子节点的指针（双向链表）；
(5)范围查询时，B树需要多轮IO磁盘操作(从上往下通过指针P，最好是根节点，最差是叶子节点)才能查到范围内的各个值；但是B+树的data数据都是在叶子节点(最后一层)，而且数据按照升序排列，所以一轮IO次数(从上到下)后，直接通过指针链表来查到范围内的数据。
(6)数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。如果不存储数据，那么就会存储更多的键值，键值数量就是树的阶数，相应的树的阶数（节点的子节点树）就会更大。B+树非叶子节点只存放键值key,相对于B树(即存key又存data)，它就会存放更多的键值，树的阶数也就会变大，树变得更矮更胖，磁盘IO次数就会变少，效率提高。
1
2
3
4
5
6

B+树的优势：

1.单一节点存储更多的键值key，使得查询的IO次数更少。
2.所有查询都要查找到叶子节点，查询性能稳定。而B树(最好情况：根节点查到；最坏情况：叶子节点查到，查询不稳定)
3.所有叶子节点形成有序链表，便于范围查询。
1
2
3

B+树范围查询(从上到下一轮IO磁盘操作，数据按照升序排列，直接最后一层双向链表指针查找)

3. Hash索引：(引擎Memory拥有的索引)

Hash查询原理：Hash索引就是将键值key通过哈希算法(主键索引或非主键索引)计算得到哈希值，之后哈希值再与（桶下标数组的长度-1）进行&位运算，得到索引值，索引值存放在桶下标数组中(000,…255)，再通过这个索引值去链表中查找，只需要一次哈希算法就可以定位到相应的数据位置(O（1）)，不需要像B+树那样通过根节点到叶子节点逐一查找，前提是key不重复(key重复就需要调用equals方法再次检测是否真的相同)。记住单向链表中不仅存放value数据(521-1234)，也存放键值key(John Smith)。这句其实是put添加的操作：因为调用equals方法需要比较key,唯一key则在链表后面添加数据，相同key就覆盖原先value数据。

Hash索引与B+树索引的区别：

(1)只适合等值查询：(查询不是添加，查询说明这个哈希表的数据(单向链表)已经存放不重复的索引值对应的value数据了，即value已经覆盖过)
    在键值是唯一的情况下，通过哈希算法hasn(key).hashcode()求出哈希值(哈希值唯一)，再由哈希值计算得到数组索引值，通过索引值一次定位到数据位置(理想：一个下标对应一个节点)。如果键值不唯一(key相等)，可能会产生相同的哈希值(哈希冲突)，那么就会得到相同的索引值(152，只是把很多个152放一起了)，所以,需要通过该索引值index找到链表进行扫描，找到满足条件的数据(键值相等即找到，John Smith=链表的John Smith，数据是521-1234,这就查询到了)。
(2)不能范围查询：
    原先的键值是连续的，但是经过哈希算法后的哈希值得到的索引值不一定连续(比如John Smith和Lisa Smith是相邻的，但是求出的索引值分别是001、152，不连续了)，所以链表中的数据顺序也不一样了，不能范围查询,所以没法进行排序和分组
(3)存在hash冲突：
    如果大量的键值不唯一(key相等)，那么可能产生很多相同的哈希值，也就是得到很多相等的索引值(152),那么链表中就会存放很多的数据节点，即一个索引值(152)对应多个数据节点value,最后通过键值key与链表节点值中的key相等去判断，太耗费性能。
(4)InnoDB索引是存放在硬盘上的，如果使用hash存储的话需要将所有的数据文件加载至内存，比较
耗费内存空间。但是Memory存储引擎的索引是存在内存中的，因此可以使用Hash索引，而且默
认使用的也是hash索引
1
2
3
4
5
6
7
8
9

2.mysql的引擎

InnoDB是MySQL默认使用的引擎，它支持外键、事务、锁是行锁(容易死锁，不易锁冲突)，表锁也行，它是支持高并发的事务操作(数据不易出错)；B+树索引

MyISAM不支持外键和事务，默认只能使用表锁(不容易死锁，易锁冲突)，不适合高并发操作，一般性能优先。B+树索引

3.mysql中为什么用B+树

(1)InnoDB默认页的大小为16KB，在数据量相同的情况下，相对于B树节点存放键值Key和data域数据，而B+树的非叶子节点只存放键值key(key的数量等于树的阶数)，这样树阶数很多，B+树就会变得更矮更胖，查询数据时磁盘的IO次数就会减少，效率提高；

(2)B+树的data域数据只存放在叶子节点，每次查询数据是稳定的(最后一层)，而B树的data域数据存放在节点，每次磁盘IO读写时，查询数据不稳定(最好的情况在根节点，最坏的情况在叶子节点)；

(3)范围查询时，B树可能需要多轮磁盘的IO次数(从上到下：根到节点)才能找到范围内的全部数据，但是B+树的叶子节点的data域数据都是升序排列好的，并且叶子节点是双向链表的结构，只要一轮从上往下(根到叶子节点)的磁盘IO次数，就可以根据链表横向找到范围内的全部数据。

(4)相对于Hash索引只适用于等值查询，在键值key唯一时查询快，不唯一时还产生Hash冲突，它是MEMORY引擎使用的索引。

4.讲一讲索引，最左匹配原则(最佳左前缀)

优势：MySQL用到索引是因为索引可以提高Mysql查询数据的效率(减少磁盘IO次数)；还有降低CPU的使用率(索引本身就是按照二叉排序树的原则，已经排好序，order by就不用排序)。

弊端：不适用的情况：
(1)少量的数据：本身数据很少，再用索引数据结构，那么既有数据表又有索引表，繁琐；

(2)频繁更新的字段：字段更新，不仅数据表得更新，索引的树结构的节点键值key(索引值)也得更新，这样节点经常更新，繁琐；(3)不经常使用的字段：这样的字段加索引，会浪费索引表。

分类：(1)单值索引：表中的各列字段都可以作为索引列；(2)唯一索引：索引列数据不能重复，所以一般用主键Id创建索引；(3)复合索引：将多个字段联合创建索引列。(name,age)先找name，如果name相同再找age;(4)主键索引：索引列的数据既不能重复，也不能为null，一般InnoDB默认创建主键id。全表索引：。

索引的类型：system>const>eq_ref>ref>range>index>all,一般优化达到range,尽可能达到ref.

最左匹配原则:这是explain的extra字段 using filesort额外查询字段，它常用于ordey by排序。在复合索引中，我们where和order by拼接的索引字段的顺序要和复合索引一致，不能够跨列和无序，保持最左边的字段和复合索引的最左边索引一致，这就是最左匹配原则，否则就会using filesort。

如：复合索引(a,b,c,d) select a,b,c from tab where a=1 and b=3 and d=1 order by c=4; where和order by 拼接 a,b,c按照复合索引顺序不出现using filesort，但是跨列d,d索引失效会回表查询，但是这最佳左前缀满足(a)。

不满足最佳左前缀：select a,b,c from tab where b=3 and a=2 order by c=4;(b,a,c)与复合索引不一致。

5.什么是SQL注入，怎么预防

SQL注入：用户通过动态SQL语句提交恶意的数据(比如drop table语句)与SQL语句进行字符串拼接，从而影响SQL语句的正常语义，产生了数据威胁。

预防：(1)遵循编程规范，首先预编译，然后再填参数到占位符?上，最后再执行完整的sql语句；(2)mybatis半自动持久化框架，其底层原理也是预编译，参数替换占位符？

 在编写MyBatis的映射语句时，尽量采用“#{xxx}”这样的格式。若不得不使用“${xxx}”这样的参数，要手工地做好过滤工作，来防止SQL注入攻击。
        #{}：相当于JDBC中的PreparedStatement
        ${}：是输出变量的值
        简单说，#{}是经过预编译的，是安全的；${}是未经过预编译的，仅仅是取变量的值，是非安全的，存在SQL注入。
1
2
3
4

6.你对mysql了解有多少（讲了innodb、myiasm两个引擎的区别，隔离级别，索引，使用B+树的优势，最左匹配原则，MVCC）

MVCC:

7.怎么对sql进行分析（explain和show profile）

7.1 explain的字段

1.id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。

id值相同：加载表的顺序从上往下执行t、tc、c 数据量少的表优先查询

t当前的数据量为3，tc当前的数据量为3，c当前的数据量为4

查询课程编号为2 或 教师证编号为3 的老师信息：

explain select  t.* 
from teacher t , course c , teacherCard tc 
where t.tid=c.tid  and  t.tcid=tc.tcid  and  (c.cid=2 or tc.tcid=3);
1
2
3

id值不同：id值越大，优先级越高，越先被执行：c、t、tc

查询教授SQL课程的老师的描述（desc）：

explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
and t.tcid = tc.tcid and c.cname = 'sql' ;
1
2

将以上多表查询 转为子查询形式：(像数学的括号一样，总是先计算括号里的8-（2-（3+6））)，因为c表在最内层，先查询c表；接着是t表；再接着是最外层tc表。下面嵌套select从内往外查。

explain select tc.tcdesc from teacherCard tc where tc.tcid = 
(select t.tcid from teacher t where  t.tid =  
	(select c.tid from course c where c.cname = 'sql')
);
1
2
3
4

这个跟SQL语句的子查询有关：应给先查询最内存的表，然后查询外层的表，一层一层的查询。

3、 id 有相同，也有不同。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行：c、t、tc

子查询+多表：

explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc
where t.tcid= tc.tcid and t.tid = (select c.tid from course c where cname = 'sql') ;
1
2

2.select_type字段

表示 select 的类型（查询类型），常见的取值：

1、simple：简单的select查询，查询中不包含子查询或者union（简单查询）

2、primary：查询中若包含复杂的子查询，最外层查询为PRIMARY（最外层的查询，主查询）

3、subquery：在select或where 列表中包含了子查询（非最外层的查询，子查询）

explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc
where t.tcid= tc.tcid and t.tid = (select c.tid from course c where cname = 'sql') ;
1
2

4、derived：在from列表中包含的子查询，使用到了临时表（衍生查询）

在from子查询中只有一张表，那么该表的查询就是DERIVED：

explain select cr.cname from ( select * from course where tid in(1,2) ) cr ;
1

在from子查询中，如果table1 union table2，那么table1就是DERIVED查询，table2就是UNION查询：

explain select  cr.cname 
from ( select * from course where tid = 1 union select * from course where tid = 2 ) cr ;
1
2

5、union：若第二个select出现在union之后，则标记为union ; （连接查询）

6、union result：从union表获取结果的select

3.type 索引类型

system>const>eq_ref>ref>range>index>all ，要对type进行优化的前提：有索引

其中：system,const只是理想情况；实际优化只能达到 ref>range

system（忽略）: 只有一条数据的系统表 ；或 衍生表只有一条数据的主查询

type 是索引类型，是较为重要的一个指标，可取值为：

结果值从最好到最坏依次是：要对type进行优化，前提是要有索引才可以

-- 全部的类型：
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

-- 常用的类型：越往前查询效率越高，system和const为理想情况，基本达不到要求。
system > const > eq_ref > ref > range > index > ALL

1
2
3
4
5
6

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。

1、system：只有一条数据的系统表，或者衍生表只有一条数据的主查询（测试为ref,老师为system）

create table test01(
	tid int(3),
	tname varchar(20)
);
insert into test01 values(1,'a') ;
commit;
增加索引：alter table test01 add constraint tid_pk primary key(tid) ;
衍生表只有一条数据：explain select * from (select * from test01 ) t where tid =1 ;
1
2
3
4
5
6
7
8

2.const：仅仅能查询到一条数据的SQL，且用于primary key (主键约束)或者unique key (唯一索引)索引（类型和索引类型有关）

explain select tid from test01 where tid =1 ;
1

-- 删除主键：
alter table test01 drop primary key ;
-- 给tid添加一个普通索引而不是primary key：
create index test01_index on test01(tid) ;
1
2
3
4

-- 补充点知识：
1.什么是约束：约束就是表中的限制条件、约束的关键字是：constraint
2. 约束的分类：非空约束：not null、唯一性约束：unique、主键约束： primary key、外键约束：foreign key
1
2
3

3、eq_ref:唯一性索引：根据每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）
常见于唯一索引 和主键索引。 select … from …where name = … .（查询的出的name不能重复是唯一的）

-- 给tid字段创建添加主键约束：Alter table 表名 add constraint 约束名 约束类型(列名)
alter table teacherCard add constraint pk_tcid primary key(tcid);
-- 给tcid字段添加唯一约束:unique index 唯一索引
alter table teacher add constraint uk_tcid unique index(tcid) ;
explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;
1
2
3
4
5

以上SQL，用到的索引是 t.tcid,即teacher表中的tcid字段；
如果teacher表的数据个数 和 连接查询的数据个数一致（都是3条数据），则有可能满足eq_ref级别；否则无法满足。

4、ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多），与eq_ref条件相反

-- 准备数据：
insert into teacher values(4,'tz',4) ;
insert into teacherCard values(4,'tz222');
1
2
3

根据索引查询，查询的结果可以多个就是ref级别，如果查询的结果只有唯一一个就是eq_ref级别

5、range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊**:in有时候会失效 ，从而转为 无索引all**)

alter table teacher add index tid_index (tid) ;
explain select t.* from teacher t where t.tid in (1,2) ;
explain select t.* from teacher t where t.tid <3 ;
explain select t.* from teacher t where t.tid between 1 and 2 ;
1
2
3
4

6、index：查询全部索引中数据，扫描索引的那一列(B+树：索引表)

-- tid 是索引，只需要扫描索引表，不需要所有表中的所有数据
explain select tid from teacher ;
1
2

7、all：查询全部表中的数据，因为没有索引，所以扫描整张表

-- cid不是索引，需要全表扫描，即需要扫描所有表中的所有数据
explain select cid from course ; 
1
2

type索引类型总结：

system/const: 结果只有一条数据
eq_ref:结果多条；但是每条数据是唯一的 ；
ref：结果多条；但是每条数据是是0或多条 ；

4、possible_keys、key

1、possible_keys ：可能用到的索引，是一种预测，不准。

2、key ：实际使用到的索引

查看各个表中哪些字段中加了索引以及对应索引名称：

explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc
 where t.tcid= tc.tcid
and t.tid = (select c.tid from course c where cname = 'sql') ;
1
2
3

如果 possible_key/key是NULL，则说明没用索引

5、explain之key_len

key_len ：索引的长度 ;
作用：用于判断复合索引是否被完全使用 （a,b,c）。

1、如果没用索引长度为0，现在索引长度为80，说明用了索引：char(20)固定字符，与后面的可变varchar(20)不一样

create table test_kl(
    -- 创建表，只有一个字段name,有非空约束，字段类型为Char固定长度
	name char(20) not null default ''
);
alter table test_kl add index index_name(name) ;
explain select * from test_kl where name ='' ;   -- key_len :80
1
2
3
4
5
6

2、 如果索引字段可以为Null,会使用1个字节用于标识：

-- 向表中增加一个字段name1,name1可以为null
alter table test_kl add column name1 char(20) ;  
alter table test_kl add index index_name1(name1) ;
explain select * from test_kl where name1 ='';
1
2
3
4

3、判断复合索引是否被完全使用：

drop index index_name on test_kl ;
drop index index_name1 on test_kl ;

-- 增加一个复合索引 
alter table test_kl add index name_name1_index (name,name1) ; 

explain select * from test_kl where name1 = '' ; --161
explain select * from test_kl where name = '' ; --80
1
2
3
4
5
6
7
8

4、将表的字段添加一个字段：varchar(20)可变字符

-- varchar(20)，添加一个字段name2，类型为varchar(20)可变字符
alter table test_kl add column name2 varchar(20) ; --可以为Null 
-- 为name2字段添加一个索引
alter table test_kl add index name2_index (name2) ;

explain select * from test_kl where name2 = '' ;  --83
20*4=80 +  1(null)  +2(用2个字节 标识可变长度)  =83
1
2
3
4
5
6
7

6、explain之ref、rows

1、ref : 注意与type中的ref值区分。
作用： 指明当前表所 参照的 字段。select …where a.c = b.x ;(其中b.x可以是常量，const)

c表参照了t表的tid字段、t表参照了常量为const：

2、rows: 被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)

7、explain之Extra

1、using filesort ： 性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。

对于单索引：where哪些字段，就order by哪那些字段

create table test02(
	a1 char(3),
	a2 char(3),
	a3 char(3),
	index idx_a1(a1),
	index idx_a2(a2),
	index idx_a3(a3)
);
explain select * from test02 where a1 ='' order by a1 ;
explain select * from test02 where a1 ='' order by a2 ; --using filesort
1
2
3
4
5
6
7
8
9
10

结论：对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort(排序的字段前需要查询才能排，不同字段要额外查询)；避免： where哪些字段，就order by哪那些字段

对于复合索引：不能跨列查询（最佳左前缀）

drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;

-- 创建复合索引
alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;

-- 垮了a2列，产生using filesort
explain select *from test02 where a1='' order by a3 ;  -- using filesort
-- 垮了a1列，产生using filesort
explain select *from test02 where a2='' order by a3 ; -- using filesort
-- 没有using filesort
explain select *from test02 where a1='' order by a2 ;-- 复合索引，索引a1后面就是a2
-- 只要包含了a1最左列，并且没有跨列就不会出现using filesort
explain select *from test02 where a2='' order by a1 ;-- using filesort 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

结论： where和order by 拼接起来按照复合索引的顺序(a1,a2,a3)使用，不要跨列或无序使用。

2、using temporary：性能损耗大 ，用到了临时表。一般出现在group by 语句中。

避免：查询那些列，就根据那些列 group by .

-- SQL解析过程：from..on..join..where..group by..having..select dinstinct..order by limit..
explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;
explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; -- using temporary

解析过程：			
from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...
-- where和group by的列必须一一对应
explain select * from test03 where a2=2 and a4=4 group by a2,a4 ; -- 没有using temporary
-- 出现了临时表，因为分组和where是不一样的列
explain select * from test03 where a2=2 and a4=4 group by a3 ;
1
2
3
4
5
6
7
8
9
10

​

3、 using index ：性能提升; 索引覆盖（覆盖索引只在复合索引有效(除了全表只有一个索引外)）。原因：不读取原表，只从索引表中获取数据 （不需要回表查询）。只要使用到的列全部都在索引中，就是索引覆盖using index

explain select a1,a2 from test02 where a1='' or a2= '' ; -- using index ，a1与a2都是索引列，要查询数据直接从索引表中查询，不需要回到原表中再查询数据(不需要回表查询)
1

drop index idx_a1_a2_a3 on test02;
-- 创建复合索引
alter table test02 add index idx_a1_a2(a1,a2) ;
explain select a1,a3 from test02 where a1='' or a3= '' ;
1
2
3
4

如果用到了索引覆盖(using index时)，会对 possible_keys和key造成影响：
	a.如果没有where，则索引只出现在key中；
	b.如果有where，则索引 出现在key和possible_keys中。
	
	explain select a1,a2 from test02 where a1='' or a2= '' ;
	explain select a1,a2 from test02  ;
1
2
3
4
5
6

覆盖索引只在复合索引有效，多个单值索引无效(除了全表只有一个索引外)

------+
 | idx_a1   |            1 | a1          |
      |
 | idx_a2   |            1 | a2          |
      |
 | idx_a3   |            1 | a3          |
      |
-+----------+--------------+-------------+
1
2
3
4
5
6
7
8

mysql> explain select a1,a2 from test02 where a1=’ ’ or a2= ’ ’ 因为用到or,导致前后a1,a2索引也失效了
±—±------------±-------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±-------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | SIMPLE | test02 | NULL | ALL | idx_a1,idx_a2 | NULL | NULL | NULL | 1 | 100.00 | Using where |
±—±------------±-------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+

4、using where （需要回表查询）查询不是索引列的要求数据
假设age是索引列
但查询语句select age,name from …where age =…,此语句中name不是索引列，必须回原表查Name，因此会显示using where.

explain select a1,a3 from test02 where a3 = ''; -- a3需要回原表查询
1

5、 impossible where ： where子句永远为false

explain select * from test02 where a1='x' and a1='y';
1

7.2 show profile

分析海量数据的

1、profiles：

show profiles ; --默认关闭
show variables like '%profiling%';  查看默认状态
set profiling = on ; --开启
show profiles  ：会记录所有profiling打开之后的  全部SQL查询语句所花费的时间。
		缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu io ）
1
2
3
4
5

2、精确分析:sql诊断

show profile all for query 上一步查询的的Query_Id
show profile cpu,block io for query 上一步查询的的Query_Id
1
2

3、全局查询日志 ：记录开启之后的 全部SQL语句。 （这次全局的记录操作 仅仅在调优、开发过程中打开即可，在最终的部署实施时 一定关闭）

		--查询是否开启全局日志
		show variables like '%general_log%';
		--执行的所有SQL记录在表中
		set global general_log = 1 ;--开启全局日志
		set global log_output='table' ; --设置 将全部的SQL 记录在表中
	
		--执行的所有SQL记录在文件中
		set global log_output='file' ;
		set global general_log = on ;
		set global general_log_file='/tmp/general.log' ;
		
		-- 开启后，会记录所有SQL ： 会被记录 mysql.general_log表中。
		select * from  mysql.general_log ;

### 8.写了一道group by+子查询的题

```mysql
select t.age,t.name from (select * from teacher where age >20 ) t group by t.age;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

9.数据库全局锁

全局锁就是对整个数据库加锁，MySQL 提供了2种加全局读锁的方法，
方法一： Flush tables with read lock (FTWRL)。
方法二：set global readonly=true

相同点：业务的更新不只是增删改数据(DML),还有可能对表结构的增删改(数据结构层面DDL),不论是哪种加锁方法，一个库被全局锁上以后，里面任何一个表做加字段操作，都是会被锁住的。

不同点：一是，在有些系统中，第二种方法的readonly值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。

二是，在异常处理机制上有差异。如果执行第一种命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

场景：做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。以前有一种做法，是通过 Flush tables with read lock 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。
这里让整库都只读，有2点需要注意的：
1.如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
2.如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

10.索引优化(explain)

(1)通常我们要查询的字段，还有where和order by的字段，需要对其加索引，这样查询速度更快，索引的类型级别有7种system>const>eq_ref>ref>range>index>all。一般我们优化到range(between,>,<)就可以，尽可能到ref(非唯一性索引)。

(2)要保证查询的列在索引列中，就是索引覆盖优化(出现using index),索引覆盖一般用复合索引，就是查询的列在索引表中都能找到，不需要回原表查询；尽量使用索引覆盖（using index）,索引优化是100%，不会出现SQL优化的概率事件(服务层SQL优化器改变我们的优化)。

(3)对于复合索引：我们要遵循最佳左前缀原则，where和order by 的拼接字段顺序要和索引的顺序一致，否则跨列或无序出现索引失效问题，这就需要我们根据情况调整字段的顺序与索引顺序一致。

(4)复合索引不能使用不等于（!= <>）或 is null (is not null)，否则自身以及右侧所有全部失效。

11.mysql查询一亿数据怎们办

可以分表查询：通过小表驱动大表原则，将小表作为主查询，大表作为子查询，通过左外连接，将使用的字段加索引，最好是索引覆盖，提升性能。另外可以用in或exist关键字，如果主查询的数据集大，则使用In , 效率高。
如果子查询的数据集大，则使用exist, 效率高。本题在子查询中用exist.

12.MYSQL的锁是怎么回事，和事务之间有什么联系

行锁(要有索引)：InnoDB默认使用行锁，行锁是锁住某一行数据。在DML时(update,insert,delete)InnoDB会默认给我们加行锁。当会话0去增删改某条数据时，取消事务自动提交，这时会话1去增删改这条数据，就必须等待会话0去释放写锁(commit/rollback提交数据到库里/回滚到上一个库的数据)，才能继续操作。当会话1去增删改不同的数据时，即使没有事务提交，操作不同数据，也互不干扰。行锁适合高并发操作，不易产生锁冲突，但容易死锁，性能损耗较大。行锁在索引失效的情况下，没有索引，会转化为表锁。

加行锁的方式：1.自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(写锁)；2.对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

共享锁(读锁)：select * from tableName where … + lock in share mode
排他锁(写锁/互斥锁)：select * from tableName where … + for update
1
2

表锁：MyISAM默认支持表锁。通过 locak table 表 read/write加读写锁。或者MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁。

​ 加读锁(共享锁)：会话0给该表A加读锁 ，那么会话0只能对A表读操作(select),不能进行写操作(insert/delete/update);也不能对其他表进行读写操作；其他会话如会话1对A表可以读操作，但是写操作需要等待会话0释放A表读锁，会话1可以对其他会话0的表(除了加锁表)，进行读写操作。

​ 加写锁(排它锁)：会话0给该表A加写锁，那么会话0可以对A表进行读写操作，但是不能对其它表进行读写操作；其他会话如会话1可以对A表进行读写操作，前提是需要会话0去释放A表的写锁；会话1可以对其他会话0的表(除了加锁表)，进行读写操作。

表锁容易产生锁冲突，不适合高并发操作。

页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。锁住的是某一页数据

全局锁：全局锁就是对整个数据库加锁，MySQL 提供了2种加全局读锁的方法，
方法一： Flush tables with read lock (FTWRL)。
方法二：set global readonly=true

相同点：业务的更新不只是增删改数据(DML),还有可能对表结构的增删改(数据结构层面DDL),不论是哪种加锁方法，一个库被全局锁上以后，里面任何一个表做加字段操作，都是会被锁住的。

不同点：一是，在有些系统中，第二种方法的readonly值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。

二是，在异常处理机制上有差异。如果执行第一种命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

场景：做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。以前有一种做法，是通过 Flush tables with read lock 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。
这里让整库都只读，有2点需要注意的：
1.如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
2.如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟

锁和事务的联系：事务的隔离性就是通过锁机制完成的，通常事务的高并发操作是通过行锁来完成。在事务未提交之前需要与其他事务保持隔离性，这就需要通过锁来完成，当对同一数据进行多线程操作时，一个线程必须要等待另一线程提交事务释放锁才能对同一数据进行操作，这个未提交事务不能对同一数据操作的过程就是通过锁来完成。

13.行级锁，互斥锁，共享锁；

行级锁：就是行锁。锁定粒度最小的锁。会话0对某数据加了行级锁，其他会话1可以对不同数据进行操作(DML)，即使不事务解锁(行锁解锁：事务提交回滚和语句解锁)，也互不干扰；其他会话对相同数据进行操作时(DML)，需要等会话0去释放行级锁。行锁的并发度高，不易锁冲突，但是易死锁。

互斥锁：就是写锁，也叫排他锁； 共享锁：读锁

14.mysql的事务的四大特性

ACID：原子性，一致性，隔离性，持久性。

原子性：一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功(commit)，要么全部失败回滚操作(rollback).

一致性：一致性就是事务对数据完整性约束的遵循，这些约束包括主键索引约束、唯一索引约束、外键约束。事务执行前后，数据都是合法的状态，不会违背任何的数据完整性。就拿转账来说，A和B加起来有5000块钱，不管A和B如何转账，转几次账，A和B加起来的钱永远都是5000块。
总之，可以理解为：一致性是为了保证数据的完整性。

隔离性：事务的隔离性要求每个读写事务的数据对其他事务的操作数据能相互分离，即该事务提交前对其他事务不可见，通常使用锁来实现。如果事务提交了，锁也就没用了。

持久性：事务一旦提交，其结果就是永久性的，即使数据库重启，或者其他数据库崩溃恢复后的数据还是之前的。

15.表锁和行锁有什么区别

表锁是锁定粒度最大的锁，行锁是锁定粒度最小的锁。表锁是mySIM默认的锁，行锁是InnoDB默认的锁。其区别间12上面分析。

16.默认的事务隔离性是什么

可重复读。解决了丢失问题、脏读、不可重复读的异常问题。如果对安全性要求高，可以使用这个可重复读级别

17.介绍mySQL的索引，底层是如何实现的

MySQL默认采用InnoDB引擎，InnoDB采用B+树索引作为索引结构。B+树的非叶子节点存放索引键值(主键索引值或非主键索引值)，叶子节点存放索引键值key和data域数据(主键索引：data数据就是表中的各行数据；非主键索引：data数据就是主键值)。

对于主键索引作为B+树索引值的节点的：其叶子节点不仅存放了主键索引值还有表的各行数据，查找某个数据时，直接从根节点到叶子节点，通过指针p和主键的范围查找到对应数据。

非主键索引作为B+树结构索引值的：叶子节点存放非主键索引值和主键值，查找某个数据，在叶子节点上，首先通过非主键索引值检索到主键值，再通过主键值到主键索引中查找相应数据(需要两次B+树查找：一次非主键和一次主键)。

查找某一范围内的数据，因为叶子节点的数据都是按升序排好的双向链表，通过从上到下一轮的磁盘IO次数，根据指针p首先查找某一个数据，之后在通过链表横向查找范围内的数据。

18.聚集索引与非聚集索引，主键索引和非主键索引。

在 MySQL 中，B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

1.聚集索引（聚簇索引） ：InnoDb引擎采用的默认索引。但是它又分为索引键值是主键还是非主键(辅助索引)。

以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建
一个隐式的主键。这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 
B+ 树的叶子节点中，存储了表中所有的数据。这种以主键作为 B+ 树索引的键值而构建的 B+ 树索
引，我们称之为聚集索引。
1
2
3
4

主键索引：

主键索引就是将主键索引值作为键值，InnoDb默认会帮我们创建一个主键值，在叶子节点中存放了键值和表的各行数据。

非主键索引：

非主键索引是将其他的非主键索引值当作树结构的键值，在叶子节点上存储主键值，通过主键值去查找数据：首先检索非主键索引(辅助索引)获得主键(Alice-18,Bob-15)，然后用主键到主键索引中检索获得表数据。每次使用辅助索引检索都要经过两次B+树查找(一次非主键和一次主键索引查找)。

2.非聚集索引（非聚簇索引）： MyISAM默认的索引方式

MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址(物理地址)

这是一个主键索引的非聚集索引，将主键值作为树结构的键值，但是叶子节点存放的是主键值和数据记录地址(data域：0x07),这与InnoDB引擎不同(主键索引：data域存表数据；非主键索引：data域存主键值)，叶子节点直接通过主键值找到data域数据记录地址，然后用数据地址找到相应数据。

非主键索引的非聚集索引：将非主键值作为树结构的键值，叶子节点同样存储非主键值和数据记录地址，结构和上面类似，叶子节点直接通过辅助索引值找到data域数据记录地址，然后用数据地址找到相应数据。

主索引和辅助索引都是B+树，叶子节点data域都存储的是数据记录的地址，索引文件和数据文件是分离的，主索引和辅助索引都不会影响数据文件。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

19.联合索引.

联合索引就是复合索引。就是将多个单值索引联合在一起创建。在where和order by查询字段时，在复合索引中要保持最佳左前缀原则，不能跨列或无序查询，否则可能出现索引失效，要回表查询，或者额外排序查询；要使查询的字段都能在索引列中找到，这样就索引覆盖，只在索引表中查询，效率提高；复合索引中不能使用(!=,>,<,is not null,is null),不然索引失效；尽量使用全索引匹配。

20.回表问题

回表查询就是要查询的字段不在索引表中(或者是B+树的节点中)。

通常引发回表查询的情况：(1)对select 查询的字段不在索引表中，必须回原表查询。(2)对于某些字段索引失效，那么就是该字段不存在索引，要查询必须回原表，比如select name,age.....where name or age ,name和age都各自加了索引，or都可能索引失效,查询该字段必须回原表。

解决办法：(1)尽量查询用索引覆盖(单个索引列或者复合索引的情况)，只用在索引列表中查询(将复合索引的键值key放入B+树节点)；(2)select避免用*，查询多少字段就写多少字段，以防有的字段需要回表；(3)尽量采用主键索引值查询(即使复合索引也要包括主键id),因为B+树叶子节点根据主键id直接查到该行data数据；但是非主键索引，需要2次查询，第一次通过非主键索引检索到主键索引(叶子节点存放data数据就是主键id),第二次根据主键id去原表检索该行记录(回到主键索引或者说聚集索引中查询)。

21.数据库分页查询，如何分页，怎么实现？

(1)数据库分页用到的语句就是 limit Offset(当前页的起始行数) Pagesize(每页的数据量)；但是我们通常都是只知道当前页码数current,通过Offset=(current-1)*Pagesize来计算当前页的起始行数。

(2)我们可以封装一个分页功能的page类：主要有三个属性，其他属性都可以通过这三个属性计算出来

​ 1.current:当前的页数，即第几页，可以有默认值；

​ 2.Pagesize：每页显示的数据量，上限值，可以设默认值；

​ 3.rows:数据库表的总的数据数，不可以设默认值，必须从表中查询(可以通过count(id)计算出表中的全部数据)。

(3)通过这三个属性计算出我们需要的分页属性，比如起始行数Offset，总的页数，起始的页码和结束的页码。

(4)如果我们查询的表数据量很大，那么我们就需要做一些分页优化：执行顺序是先select再limit，而不是先limit再select，要逐行查找。如果我们分页的数据是limit 200000 10,在200000的起始行查询10条数据，但是首先会select之前的200000数据逐行查找，到200000的时候才limit分页，效率太低。

方法：1.通过索引下推，过滤掉一部分数据，如select id,name from tb where id>10000 limit 200000,10; 先where再select；2.尽量先查询索引覆盖列id，避免然后再去查找对应的name，防止200000条数据都回表查询，把之前索引覆盖查出的10条数据作为子查询表，之后再查询其他字段select t.id，t.name form tb t inner join (select id from tb limit 200000,10) as tmp where t.id = tmp.id；

22.四种隔离级别，虚读和幻读什么意思发生在哪？

见上面：虚读就是脏读：读未提交；幻读：发生在串行化读隔离级别前的级别

23.Mvcc详细说一下

24.幻读举个例子，怎么解决的

比如事务1查询了id=1和id=2的数据，然后事务自动提交了，之后事务2把id=1和2的数据都给删除了，过一会儿事务1再次查询这2行数据时，发现表中数据的行数前后不一致了(数据少了)，这就是幻读现象(查询前后，行数不一致)。

解决方法：

1. 多版本并发控制（MVCC）（快照读/一致性读）：普通的 select 就是快照读。select * from T where number = 1;解决原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见，这样当前事务查询的是历史快照，所以前后行还是一致的

​ 2.next-key 锁 （当前读）：next-key 锁包含两部分：记录锁（行锁）、间隙锁，记录锁是加在索引上的锁，间 隙锁是加在索引之间的。select * from T where number = 1 for update;select * from T where number = 1 lock in share mode;解决原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。

25.间隙锁

(1)间隙锁是行锁的一种特殊情况，表示存在于某范围内，但是值却不存在。比如：创建表时，id是自增的从1到10，但是中途删除了id=7这个数据，所以就不包含这个id=7这行数据了。

(2)select * from temp t where id>1 and id<10即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。Mysql会自动给间隙加索 ->间隙锁。即本题会自动给id=7的数据加间隙锁（行锁）。行锁：如果有where，则实际加锁的范围 就是where后面的范围（不是实际的值）

(3)间隙锁（Gap Lock）是Innodb在[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7geGZqoC-1636290655710)(https://math.jianshu.com/math?formula=%5Ccolor%7Bred%7D%7B%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%7D)]提交下为了解决幻读问题时引入的锁机制,就是将当前行与上一条数据和下一条数据之间的间隙锁定，保证此范围内的数据是一致的。

26.怎么知道mysql索引有没有生效呢

通过SQL语句分析，在select关键词前加上explain,那么生成的表中某些属性就是来辨别索引是否生效的。type:索引类型。possible_keys：可能用到的索引；**key：表示实际使用的索引，如果为NUll就是没有使用索引。**key_len：使用到的索引字段的长度

27.mysql in查询内部是怎么实现的

通常如果主查询的数据集大，则使用In , 效率高。如果子查询的数据集大，则使用exist, 效率高。

in里面没有子查询表，不能用explain_id字段，通常子查询表在where、 from 、select里面

select * from table1 where id in ( select uid from table2 );通常in里面的子查询都是根据外面的表的查询情况来的，比如上面的就是先执行外面的查询，再执行括号的查询，每次从table1里面查一个id再来和括号里的查询uid比较,这样就是多次查询麻烦。

优化：select * from table1 where id in ( 1，2，3 );如果还使用in查询，先把数据集小的select uid from table2 查询出来，得出结果1，2，3，再次查询主查询。

第二种就是子查询转化成连接查询join on。select t1.* from table1 t1 join table2 t2 on t1.id=t2.uid;因为执行顺序是from …on…join…select,先是查询对应相等的id和uid,避免全表扫描。

28.mysql 如何选择字段使用varchar与char的类型？,char(8) “abc” 几个字符,varchar(8) “abc” 几个字符

CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65535之间的值。VARCHAR的最大有效长度由最大行大小和使用的字符集确定。在MySQL 4.1之前的版本，VARCHAR(50)的“50”指的是50字节（bytes）。如果存放UTF8汉字时，那么最多只能存放16个（每个汉字3字节）。从MySQL 4.1版本开始，VARCHAR(50)的“50”指的是50字符（character），无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放50个。

CHAR和VARCHAR类型声明的长度表示保存的最大字符数。例如，CHAR(30)可以占用30个字符。对于MyISAM表，推荐CHAR类型；对于InnoDB表，推荐VARCHAR类型。另外，在进行检索的时候，若列值的尾部含有空格，则CHAR列会删除其尾部的空格，而VARCHAR则会保留空格。
1
2
3
4
5

（1）在UFT-8，InnoDB下：MySQL 4.1版本之后，括号里面就是字符数(数字和字母的字节数还是字符数)

varchar: varchar是可变的字符，通常一个字符是3个字节，加上null是一个字节，可变长占2个字节,额外存储长度信息。如varchar(20)就是可以存20个字符空间：总共可以存20*3+1(null)+2(可变长)=63字节；

char:固定字符。通常一个字符3个字节，加一个null字节；char(20)就是存储20个字符空间：总共可以存20*3+1(null)=61字节；

（2）使用场景：

varchar适用的场景：

字符串列的最大长度比平均长度要大很多；
字符串列的更新很少时，因为没有或很少有内存碎片问题；
使用了UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储；
char适用的场景：

列的长度为定值时适合适用，比如：MD5密文数据
1
2
3
4
5
6
7
8

（3）char(8)和varchar(8)都是可以存储8个字符。char(8) “abc” ：表示3个字符空间存abc字符(字母字节就是字符)，5个存空字符，总共8个空间，abc占字节数为3(检索时，尾部空格删除)，char是固定长度。varchar(8) “abc”：表示3个字符空间存abc,1个字符空间存长度，varchar是可变长度。 超过8的部分，都会被截断。

29.mysql 索引类型都有哪些

system>const>eq_ref>ref>range>index>all ，要对type进行优化的前提：有索引

其中：system,const只是理想情况；实际优化只能达到 ref>range

system（忽略）: 只有一条数据的系统表 ；或 衍生表只有一条数据的主查询

type 是索引类型，是较为重要的一个指标，可取值为：

30.sum() count() group by order by asc desc 含义

sum()和count()是分组函数：输入多行数据，最后输出一行数据，sum()求和，count()计数，一般统计表的数据量；group by是分组，order by 排序 ，asc升序，desc降序。

31.乐观锁和悲观锁

**无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。**比如在并发编成中，将Syncinoized锁称为悲观锁，而CAS+volilate称为乐观锁。

1.mysql概念：

悲观锁：当某个事务对一个数据库中的一条数据进行修改的时候，为了避免同时被其它事务修改，最好的办法就是直接对该数据进行加锁以防止并发。悲观思想：在事务对数据进行修改前，就已经加锁了。

悲观锁效率：效率较低，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会另外，还会降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完释放锁(commit/rollback)才可以处理那行数据。

乐观锁：乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。乐观思想：在事务对数据修改后进行提交的时候，才会去进行检测。一般乐观锁不使用数据库锁机制，而是通过添加版本号和时间戳来进行验证。

乐观锁效率：效率较高，但是安全性不高，容易发生数据更改，但是我们假设数据一般情况下不会造成冲突。

2.实现方式：

悲观锁实现方式(自己试验，需要关闭事务自动提交)
悲观锁的实现，往往依靠数据库提供的锁机制。在数据库中，悲观锁的流程如下：

在对记录进行修改前(DML-InnoDB默认给我们加行锁；select需要手动for update)，先尝试为该记录加上排他锁（exclusive locking）/写锁。
如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住
1
2
3
4
5
6
7
8

乐观锁实现方式
使用乐观锁就不需要借助数据库的锁机制了。
乐观锁的概念中其实已经阐述了他的具体实现细节：主要就是两个步骤：冲突检测和数据更新。其实现方式有一种比较典型的就是Compare and Swap(CAS)。
CAS是项乐观锁技术，当多个线程尝试使用CAS同时更新同一个变量时，只有其中一个线程能更新变量的值，而其它线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试。

比如：我们在更新之前，先查询一下库存表中当前库存数（quantity），然后在做update的时候，以库存数作为一个修改条件。当我们提交更新的时候，判断数据库表对应记录的当前库存数与第一次取出来的库存数进行比对，如果数据库表当前库存数与第一次取出来的库存数相等，则予以更新，否则认为是过期数据。
1
2
3
4
5
6

3.乐观锁解决ABA问题

问题：比如说一个线程one从数据库中取出库存数3，这时候另一个线程two也从数据库中库存数3，并且two进行了一些操作变成了2，然后two又将库存数变成3，这时候线程one进行CAS操作发现数据库中仍然是3，然后one操作成功。尽管线程one的CAS操作成功，但是不代表这个过程就是没有问题的。

解决办法：(以后实现乐观锁方式都加上)

(1)添加版本号：乐观锁每次在执行数据的修改操作时，都会带上一个版本号，一旦版本号和数据的版本号一致就可以执行修改操作并对版本号执行+1操作，否则就执行失败。因为每次操作的版本号都会随之增加，所以不会出现ABA问题，因为版本号只会增加不会减少。

(2)或者添加时间戳：时间也是增加不会减少的。添加时间戳也可以检测是否有其他线程对数据的修改(ABA)

32.mysql 函数都有哪些,连接都有什么

内连接：inner join on ;外连接：left out join on; right out join on;

33.索引 的数据结构都知道哪些

B树、B+树、哈希表、详细讲解见上面

34.mysql对应关系都有哪些

一对一、一对多、多对多。

一对一设计的方案：主键共享和外键唯一。

一对多：第三范式：所有的非主键字段直接依赖主键，不能产生传递依赖。一对多，两张表，多的表加外键。

多对多：第二范式：所有非主键字段完全依赖主键，不能产生部分依赖。多对多，三张表，关系表两个外键。

35.数据库常见的调优方法（MySQL）

1.创建索引时：避免全表扫描，要索引覆盖，首先考虑在where和order by涉及的列上建立索引；

2.避免索引失效原则

3.使用预编译查询：程序中通常是根据用户的输入来动态执行SQL，这时应该尽量使用参数化SQL,这样不仅可以避免SQL注入漏洞攻击，最重要数据库会对这些参数化SQL进行预编译，这样第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译，这样以后再执行这个SQL的时候就直接使用预编译的结果，这样可以大大提高执行的速度。

4.满足索引全匹配原则：使得where和order by拼接字段的顺序与复合索引一致，或者单索引时，where哪些字段就order by哪些字段。

5.尽量将多条SQL语句压缩到一句SQL中；

6.用where字句替换HAVING字句

7.使用表的别名

8.考虑使用“临时表”暂存中间结果：避免程序中多次扫描主表

9.使用join on连接查询代替子查询，提高查询速率。

36.MySQL 字符排序规则用过几种，对此的了解；

utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写，如果用户名和邮箱用这个就会造成不良后果。

37.建表过程中索引添加的规范

索引设计规范

1. 限制每张表上的索引数量,建议单张表索引不超过 5 个

索引并不是越多越好！索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

2. 禁止给表中的每一列都建立单独的索引

5.6 版本之前，一个 sql 只能使用到一个表中的一个索引，5.6 以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。

3. 每个 Innodb 表必须有个主键

Innodb 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

Innodb 是按照主键索引的顺序来组织表的

•不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）

•不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）

•主键建议使用自增 ID 值

---

4. 常见索引列建议

•出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列

•包含在 ORDER BY、GROUP BY、DISTINCT 中的字段

•并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好

•多表 join 的关联列

---

5.如何选择索引列的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

•区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）

•尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）

•使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

---

6. 避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）

•重复索引示例：primary key(id)、index(id)、unique index(id)

•冗余索引示例：index(a,b,c)、index(a,b)、index(a)

---

7. 对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引

覆盖索引的好处：

•避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。

•可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。

---

8.索引 SET 规范

尽量避免使用外键约束

•不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引

•外键可用于保证数据的参照完整性，但建议在业务端实现

•外键会影响父表和子表的写操作从而降低性能

38.避免mysql索引失效的原则

1、复合索引：

​ a.复合索引，不要跨列或无序使用（最佳左前缀）先a,再b再c ，（a,b,c）

​ b.复合索引，尽量使用全索引匹配 (a,b,c)

2、不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

select ..where A.x = .. ;  --假设A.x是索引
		不要：select ..where A.x*3 = .. ;-- 不然索引失效
		explain select * from book where authorid = 1 and typeid = 2 ;-- 用到了at2个索引
		explain select * from book where authorid = 1 and typeid*2 = 2 ;-- 用到了a1个索引
		explain select * from book where authorid*2 = 1 and typeid*2 = 2 ;-- 用到了0个索引
		-- 用到了0个索引,原因：对于复合索引，如果左边失效，右侧全部失效。最佳左前缀，一定要保证左边有效
		-- (a,b,c)，例如如果 a失效，则b c同时失效。
		explain select * from book where authorid*2 = 1 and typeid = 2 ;
	
 		drop index idx_atb on book ; 
		alter table book add index idx_authroid (authorid) ;
		alter table book add index idx_typeid (typeid) ;
		-- 对于独立索引，authorid,typeid没有最佳左前缀原则，即使最左边失效，也不影响后边索引
		explain select * from book where authorid*2 = 1 and typeid = 2 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3、复合索引不能使用不等于（!= <>）或 is null (is not null)，否则自身以及右侧所有全部失效。

​ 比如：复合索引中如果有>，则自身和右侧索引全部失效

说明：SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。

比如：本来应该使用authoried和typeid两个索引，但是由于SQL优化器对SQL的修改，导致只有一个索引生效

体验SQL优化的概率事件：因为SQL优化底层中的服务层有一个SQL优化器，优化器会对我们的SQL语句产生干扰

	drop index idx_typeid on book;
	drop index idx_authroid on book;
	
	alter table book add index idx_book_at (authorid,typeid);-- 用复合索引
	
	-- 复合索引at全部使用
	explain select * from book where authorid = 1 and typeid =2 ;
	-- 复合索引中如果有>，则自身和右侧索引全部失效。
	explain select * from book where authorid > 1 and typeid =2 ; 
	-- 复合索引at全部使用,个别情况
	explain select * from book where authorid = 1 and typeid >2 ;
	
	----明显的概率问题---
	-- 复合索引at只用到了1个索引
	explain select * from book where authorid < 1 and typeid =2 ;
	-- 复合索引全部失效
	explain select * from book where authorid < 4 and typeid =2 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

结论：我们学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因 该结论不是100%正确。
一般而言， 范围查询（> < in），之后的索引失效。

4、补救：尽量使用索引覆盖（using index）,索引优化是100%，不会出现SQL优化的概率事件
（a,b,c） -------- select a,b,c from xx… where a= … and b =… ;

5、like尽量以“常量”开头，不要以’%'开头，否则索引失效（模糊查询）；

​ 如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。

	select * from xx where name like '%x%' ; -- name索引失效
	
	explain select * from teacher  where tname like '%x%'; -- tname索引失效

	explain select * from teacher  where tname like 'x%';-- tname不会失效
 
	-- 如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。
	explain select tname from teacher  where tname like '%x%'; 
1
2
3
4
5
6
7
8

6、尽量不要使用类型转换（显示、隐式），否则索引失效

explain select * from teacher where tname = 'abc' ;

-- 程序底层将 123 -> '123'，即进行了类型转换，因此索引失效
explain select * from teacher where tname = 123 ;
1
2
3
4

7、多个单值索引尽量不要使用or，否则索引失效

explain select * from teacher where tname ='' or tcid >1 ; -- 将or左侧的tname 失效。
1

39.复合索引生效问题

对于复合索引：不能使用不等于（!= <>）或 is null (is not null)，否则自身以及右侧索引全部失效。不要对索引进行操作(计算、函数、类型转换)，否则索引失效。不要跨列或无序使用索引，否则索引失效。要保证索引最佳左前缀左边有效，如果左边失效，则右侧全部失效。

40.MySQL删除数据有几种方法

drop/truncate/delete

1、drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。

   drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。

   注意:truncate 不能删除行数据,要删就要把表清空。

3、delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存

   以便进行进行回滚操作。

   truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）

4、truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。

   如果要删除表定义及其数据，请使用 drop table 语句。  
5、对于由foreign key约束引用的表，不能使用truncate table ，而应使用不带where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

41.一条SQL语句在MySQL中如何执行的

1.1 MySQL 基本架构概览

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。

先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图，在 1.2 节中会详细介绍到这些组件的作用。

•连接器： 身份认证和权限相关(登录 MySQL 的时候)。•查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。•分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。•优化器： 按照 MySQL 认为最优的方案去执行。•执行器: 执行语句，然后从存储引擎返回数据。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

•Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。•存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

1.2 Server 层基本组件介绍

1. 连接器

连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

2. 查询缓存(MySQL 8.0 版本后移除)

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

3. 分析器

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

4. 优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

5. 执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

二 语句分析

2.1 查询语句

说了以上这么多，那么究竟一条 sql 语句是如何执行的呢？其实我们的 sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

select * from tb_student  A where A.age='18' and A.name=' 张三 ';
1

结合上面的说明，我们分析下这个语句的执行流程：

• 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

• 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

• 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

  a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。  b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。
1

​ 那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

2.2 更新语句

以上就是一条查询 sql 的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql 语句如下：

update tb_student A set A.age='19' where A.name=' 张三 ';
1

我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块式 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

•先查询到张三这一条数据，如果有缓存，也是会用到缓存。•然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。•执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。•更新完成。

这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

•先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。•先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

•判断 redo log 是否完整，如果判断是完整的，就立即提交。•如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。

三 总结

•MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。•引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。•SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎•对于更新等语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit

42.一条SQL语句执行得很慢的原因有哪些？

一个 SQL 执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因

(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。

(2)、执行的时候，遇到锁，如表锁、行锁。

2、这条 SQL 语句一直执行的很慢，则有如下原因。

(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

(2)、数据库选错了索引。

43.MySQL高性能优化规范建议

1.所有表必须使用 Innodb 存储引擎

没有特殊要求（即 Innodb 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用 Innodb 存储引擎（MySQL5.5 之前默认使用 Myisam，5.6 以后默认的为 Innodb）。Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

2.数据库和表的字符集统一使用 UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。

3.见上面索引规范

4.数据库 SQL 开发规范

4.1. 建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划，减少 SQL 编译所需要的时间，还可以解决动态 SQL 所带来的 SQL 注入的问题。

只传参数，比传递 SQL 语句更高效。

相同语句可以一次解析，多次使用，提高处理效率。

4.2避免数据类型的隐式转换

隐式转换会导致索引失效如:

select name,phone from customer where id = '111';
1

​ 4.3充分利用表上已经存在的索引

避免使用双%号的查询条件。如：a like '%123%'，（如果无前置%,只有后置%，是可以用到列上的索引的）

一个 SQL 只能利用到复合索引中的一列进行范围查询。如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到。

在定义联合索引时，如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧，使用 left join 或 not exists 来优化 not in 操作，因为 not in 也通常会使用索引失效。

​ 4.4数据库设计时，应该要对以后扩展进行考虑

​ 4.5程序连接不同的数据库使用不同的账号，进制跨库查询

•为数据库迁移和分库分表留出余地•降低业务耦合度•避免权限过大而产生的安全风险

​ 4.6禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

原因：

•消耗更多的 CPU 和 IO 以网络带宽资源•无法使用覆盖索引•可减少表结构变更带来的影响

​ 4.7禁止使用不含字段列表的 INSERT 语句

如：

insert into values ('a','b','c');
1

应使用：

insert into t(c1,c2,c3) values ('a','b','c');
1

​ 4.8避免使用子查询，可以把子查询优化为 join 操作

通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

​ 4.9避免使用 JOIN 关联太多的表

对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由 join_buffer_size 参数进行设置。

在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说，会产生临时表操作，影响查询效率，MySQL 最多允许关联 61 个表，建议不超过 5 个。

​ 4.10 减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

​ 4.11对应同一列进行 or 判断时，使用 in 代替 or

in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

​ 4.12禁止使用 order by rand() 进行随机排序

order by rand() 会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源。

推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

​ 4.13WHERE 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

不推荐：

where date(create_time)='20190101'
1

推荐：

where create_time >= '20190101' and create_time < '20190102'
1

​ 4.14 在明显不会有重复值时使用 UNION ALL 而不是 UNION

•UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作•UNION ALL 不会再对结果集进行去重操作

​ 4.15 拆分复杂的大 SQL 为多个小 SQL

•大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL•MySQL 中，一个 SQL 只能使用一个 CPU 进行计算•SQL 拆分后可以通过并行执行来提高处理效率