【mysql数据库】汇总

1.数据库在查询的时候，数据类型会出现隐式转化（如varchar不加单引号的话可能会自动转换为int型，索引会失效），怎么避免隐式转换

Mysql的隐式转换示例讲解
Mysql隐式转换的问题
MySQL隐式转换解决方案
mysql 隐式转换问题（案例一）
谈谈MySQL隐式类型转换
MySQL隐式转化整理
如下表：

#sname为varchar类型，用值类型搜索
explain select sno
from s
where sname=123;
#sname为varchar类型，用字符串类型搜索
explain select sno
from s
where sname='123';

上述查询上面不会走索引，下面会走索引。

因为等号两侧类型不一致，因此会发生隐式转换，cast(index_filed as signed)，然后和123进行比较。因为'123'，'123abc'都会转化成123，故MySQL无法使用索引只能进行全表扫描，造成了慢查询的产生。

解决方案：
1.in中全使用字符串类型
2.cast
附：explain字段详解

2.最左匹配原则

Mysql最左匹配原则语句详解
Mysql最左匹配原则通过示例详解（这个很清楚）
为什么使用联合索引
示例详解：

#建立名为test_user的表
CREATE TABLE `test_user` (
                             `name`     varchar(20) DEFAULT NULL,
                             `province` int(11)     DEFAULT NULL,
                             `sex`      varchar(20) DEFAULT NULL,
                             `birthday` int(11)     DEFAULT NULL,
                             `phone`    double      DEFAULT NULL
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;
#建立(name,phone,province)联合索引
CREATE INDEX test_user_name_phone_province_index
    ON test_user (name, phone, province);

以下查询走索引结果如下图：

explain select name
        from test_user
        where name='张三';

explain select name
        from test_user
        where name='张三' and phone=32432;

explain select name
        from test_user
        where name='张三' and phone=32432 and province=2;

explain select name
        from test_user
        where phone=32432;

explain select name
        from test_user
        where

3.b+树和b树、红黑树、二叉树的区别

数据结构之树--二叉树/B树/B+树/红黑树及相关算法
浅谈AVL树,红黑树,B树,B+树原理及应用
趣解各种树（很详细）
MYSQL-索引结构介绍、为什么选择B+树而不是B树
深入理解数据库索引采用B树和B+树的原因
B树B-树和B+树的总结
AVL是比红黑树更严格的二叉平衡树，缺点是在极端情况下，会退化成链表；
红黑树有自己的一套规则，根节点必为黑色。
B树是多叉树，高度降低，节点上有待搜索数据。
B+树在B树基础上，非叶子节点并不存储真实数据，只是索引，只有叶子节点存储真实数据，并且叶子节点间也形成链表。

4.覆盖索引

覆盖索引解释（很详细）
覆盖索引总结
首先要知道什么是聚集索引和二级索引。
索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
聚集索引（主键索引）：
聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据。
聚集索引的叶子节点称为数据页，聚集索引的这个特性决定了索引组辅助索引（二级索引）：
非主键索引，叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。

5.drop、truncate、 delete区别，哪个最快，为什么

drop,delete与truncate的区别(删除表的方式，哪个快)
drop、truncate和delete的区别
drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。
用TRUNCATE替代DELETE
TRUNCATE不记录日志，DELETE记录日志，所以TRUNCATE要快于DELETE。
但是一旦用TRUNCATE进行删除就不能进行恢复, TRUNCATE是删除整张表的数据。不能加where条件。

6.MySQL索引类型

mysql索引类型详解
mysql索引总结
mysql索引简洁口诀
mysql索引精选
从数据结构角度
1、B+树索引(O(log(n)))：关于B+树索引，可以参考 MySQL索引背后的数据结构及算法原理
2、hash索引：
a 仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询
b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
c 只有Memory存储引擎显示支持hash索引
3、FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）
4、R-Tree索引（用于对GIS数据类型创建SPATIAL索引）

从物理存储角度
1、聚集索引（clustered index）
2、非聚集索引（non-clustered index）

从逻辑角度
1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值
2、普通索引或者单列索引
3、多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
4、唯一索引或者非唯一索引
5、空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

1、unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；
2、index和key为同义词，两者作用相同，用来指定创建索引
3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；
4、index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；
5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
6、asc或desc指定升序或降序的索引值存储

7.一个Mysql操作很慢，怎样排查原因

反应慢排查示例
mysql数据库慢排查及解决
1.top查看cpu占用
2.查看锁状态
3.查看线程状态processlist

8.悲观锁，乐观锁

悲观锁、乐观锁简要解释
趣解数据库中的悲观锁、乐观锁
数据库中的乐观锁与悲观锁
数据库中的悲观锁和乐观锁的总结

悲观锁

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。
两种锁的使用场景:从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

9.索引的优点与缺点，底层怎么实现的？

优缺点总结

索引的优点：
1.创建唯一性索引，保证数据库表中每一行数据的唯一性。
2.大大加快数据的检索速度（最主要的原因）。
3.加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。
索引的缺点：
　1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

　　2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
　　3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

10.mysql引擎有哪些，有啥区别

mysql五种引擎详解
InnoDB Myisam Memory
InnoDB跟Myisam的默认索引是B+tree，Memory的默认索引是hash
区别：
1.InnoDB支持事务，支持外键，支持行锁，写入数据时操作快，MySQL5.6版本以上才支持全文索引。
2.Myisam不支持事务。不支持外键，支持表锁，支持全文索引，读取数据快。
3.Memory所有的数据都保留在内存中,不需要进行磁盘的IO所以读取的速度很快,但是一旦关机的话表的结构会保留但是数据就会丢失,表支持Hash索引，因此查找速度很快。

11.事务的特性

事务及其特性和隔离级别详解
原子性（Atomicity）：操作这些指令时，要么全部执行成功，要么全部不执行。只要其中一个指令执行失败，所有的指令都执行失败，数据进行回滚，回到执行指令前的数据状态。
一致性（Consistency）：事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定。
隔离性（Isolation）：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
持久性（Durability）：当事务正确完成后，它对于数据的改变是永久性的。

12.主键跟索引的区别

主键与聚集索引的区别
主键（PRIMARY KEY）
表通常具有包含唯一标示表中每一行的值的一列或者一组列。这样的一列或者多列称为表的主键（PK），用于强制表的尸体完整性。在创建或者修改表时，您可以通过定义PK约束来创建主键。
一个表只能有一个PK约束，并且PK约束中的列不能接受空值。由于PK约束可以保证数据的唯一性，因此经常对标识列定义这种约束。
如果为表指定了PK约束，数据库引擎将通过为主键列创建唯一索引来强制数据的唯一性。当在查询中使用主键时，此索引还可以用来对数据进行快速访问。因此，所选的主键必须遵守创建唯一索引的规则。
创建主键时，数据库引擎会自动创建唯一的索引来强制实施PK约束的唯一性要求。如果表中不存在狙击索引或未显示指定非聚集索引，则将创建唯一的聚集索引以强制实施PK约束。
聚集索引
聚集索引给予数据行的兼职在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。
每个表几乎都对列定义聚集索引来实现下列功能：
可用于经常使用的查询。
提供高度唯一性。

13.InnoDB引擎主键为什么设成自增？

MySQL的InnoDB存储引擎为什么要用自增的主键？
为什么推荐InnoDB引擎使用自增主键？
在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。所以不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效。

14.默认的隔离级别

mysql的默认隔离级别
主流数据库的默认隔离级别

15.隔离性分几个级别？每个级别的意思？

5分钟带你读懂事务隔离性与隔离级别
4种事务的隔离级别

事务的隔离级别分为：

• Read uncommitted(读未提交)
• Read Committed(读已提交)
• Repeatable Reads(可重复读)
• Serializable(串行化)

Read uncommitted

读未提交：隔离级别最低的一种事务级别。在这种隔离级别下，会引发脏读、不可重复读和幻读。

Read Committed

读已提交读到的都是别人提交后的值。这种隔离级别下，会引发不可重复读和幻读，但避免了脏读。

Repeatable Reads

可重复读这种隔离级别下，会引发幻读，但避免了脏读、不可重复读。

Serializable

串行化是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行。脏读、不可重复读、幻读都不会出现。

16.关于mysql处理百万级以上的数据时如何提高其查询速度的方法

关于mysql处理百万级以上的数据时如何提高其查询速度的方法

17.SQL join操作

SQL的各种连接Join详解
Mysql数据库演示内连接、左连接、右连接

18.数据库的三范式是什么？

数据库三范式
数据库三大范式详解（通俗易懂）

19.数据库MVCC

数据库MVCC 隔离级别

20.数据库类型（关系型数据库/非关系型数据库)

数据库的简介与类型