java中与MySQL相关知识_JAVA知识点梳理-Mysql

五、MySQL 篇

\49. 简单说说在 MySQL 中执行依据查询 SQL 是如何执行的？

sql语句->连接器->缓存查询->解析器->优化器->执行器->搜索引擎。

\50. MySQL 有哪些存储引擎？

MyISAM: 优势 – 查询速度快 – 数据和索引压缩问题 – 表级锁 – 数据丢失

InnoDB: 优势 – 行级锁 – 事务支持 – 数据安全问题 – 数据文件庞大 – 启动慢 – 不支持FULLTEXT索引

**MEMORY **存储引擎将表中的数据存储在内存中，表级锁、只支持固定大小的行、不支持TEXT、BLOB字段、服务器重启后数据会丢失、内存资源成本昂贵

\51. MySQL 中 varchar 与 char 的区别？varchar(30) 中的 30 代表的涵义？

char 与varchar都是用来存储字符串的，只是他们的保存方式不一样，char有固定的长度，而varchar属于可变长的字符类型。

varchar(30)中30的涵义：最大存储30个字符

\52. int(11) 中的 11 代表什么涵义？

int(20)中20的涵义： 20表示最大显示宽度为20

\53. 为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢？

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了

innoDB是行级锁，MyISAM是表级锁

\54. 说说数据库的三范式和反模式

第一范式(1NF)：数据库表的每一列都是不可分割的原子项

第二范式(2NF)：每个表必须有且仅有一个数据元素为主键(Primary key),其他属性需完全依赖于主键

第二范式需建立在满足第一范式的基础之上

第三范式(3NF)：数据表中的每一列都和主键直接相关，而不能间接相关

同样，第三范式也需要建立在第二范式的基础之上

使用反三大范式的目的是为了，实现效率的提高，在字段类型中使用json的方式存贮多条数据，实现对数据的处理。

\55. 在设计数据库表的时候，字段用于存储金额、余额时，选择什么类型比较好？

Decimal为专门为财务相关问题设计的数据类型。

DECIMAL从MySQL 5.1引入，列的声明语法是DECIMAL(M,D)。在MySQL 5.1中，参量的取值范围如下：

·M是数字的最大数(精度)。其范围为1～65(在较旧的MySQL版本中，允许的范围是1～254)，M 的默认 值是10。

·D是小数点右侧数字的数目(标度)。其范围是0～30，但不得超过M。

\56. 大概说说 InnoDB 与 MyISAM 有什么区别？

\1. 事务安全(MyISAM不支持事务，INNODB支持事务)

\2. 外键 MyISAM 不支持外键， INNODB支持外键.

\3. 锁机制(MyISAM时表锁，innodb是行锁)

\4. 查询和添加速度(MyISAM批量插入速度快)

\5. 支持全文索引(MyISAM支持全文索引，INNODB不支持全文索引)

6.MyISAM内存空间使用率比InnoDB低

\57. 什么是索引？

索引就类似书本的目录，作用就是方便我们更加快速的查找到想要的数据。

\58. 索引有什么优缺点？

使用索引的优点：

提高数据的搜索速度

加快表与表之间的连接速度

在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。

使用索引的缺点:

在我们建立数据库的时候，需要花费的时间去建立和维护索引，而且随着数据量的增加，需要维护它的时间也会增加。

在创建索引的时候会占用存储空间。

在我们需要修改表中的数据时，索引还需要进行动态的维护，所以对数据库的维护带来了一定的麻烦。

\59. MySQL 索引类型有哪些？

FULLTEXT，HASH，BTREE，RTREE。

\60. 什么时候不要使用索引？

不需要创建索引的情况：

1．如果需要取到表中所有记录，则没必要创建索引

2．对非唯一有大量重复值的字段，没必要创建索引，如性别

3．经常进行修改、删除等操作的字段，没必要创建索引

4．记录比较少的表，没必要创建索引\

61. 使用 MySQL 的索引应该注意些什么？

尽量避免这些不走索引的sql：

-- 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因

-- 字符串与数字比较不使用索引;

-- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

1.索引不会包含有NULL的列

只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。

2.使用短索引

对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

mysql一张表查询只能用到一个索引。因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。这一点是很多程序猿容易忽略的，如where子句的字段建了索引，排序的字段建了索引，但是分开建的，以为会走索引，其实这样的话排序的字段不会使用索引的，除非建复合索引，切记。

4.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。

5.不要在列上进行运算

6.不使用NOT IN 、<>、！=操作，但,>=,BETWEEN,IN是可以用到索引的

7.索引要建立在经常进行select操作的字段上。

这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

8.索引要建立在值比较唯一的字段上。

9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

10.在where和join中出现的列需要建立索引。

11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。

12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。

13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。

14.在进行联表查询时，建立关联的表的字段类型最好一样且长度一致，这样能更好的发挥索引的作用。

15.组合索引时切记此条约束：组合索引中有多个字段，其中一个字段是有范围判断，则需将此字段在最后面。如

ALTER TABLE USER_DEMO ADD INDEX name_age (NAME,AGE); 因为age会有范围判断，则建组合索引时将AGE字段放在后面。

16.字符集字段比较，UTF8与UTF-BIN联合查询是不能走索引的。

如某张表的order_no字段类型为varchar(50),另一张表的order_no字段类型为varchar(50) COLLATE utf8_BIN。则此时联合查询时不能走索引的，切记。

18.给表创建主键，对于没有主键的表，在查询和索引定义上有一定的影响。

19.避免表字段为null，建议设置默认值(如int类型设置默认值为0)，这样在索引查询上，效率会高很多。

\62. 怎么知道一条查询语句是否用到了索引，用了什么类型的索引？

前加explain

explain SELECT * from ....

\63. 说说什么是 MVCC？

MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

\64. MVCC 可以为数据库解决什么问题？

并发访问(读或写)数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题。

大家都应该知道,锁机制可以控制并发操作,但是其系统开销较大，而MVCC可以在大多数情况下代替行级锁,使用MVCC，能降低其系统开销。

\65. 说说 MVCC 的实现原理

MVCC是通过保存数据在某个时间点的快照来实现的。不同存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。当我们创建表完成后，mysql会自动为每个表添加 数据版本号(最后更新数据的事务id)db_trx_id 删除版本号 db_roll_pt (数据删除的事务id) 事务id由mysql数据库自动生成，且递增。

\66. 什么是死锁？

死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去

\67. MySQL 事务隔离级别？

读未提交

读已提交

可重复读

可串行化

\69. 请说说 MySQL 数据库的锁？

行锁：

共享锁(Ｓ)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁(X)：SELECT * FROM table_name WHERE ... FOR UPDATE

表锁：

\70. 说说什么是锁升级？

锁升级，就是将众多细粒度锁转换为较少的粗粒度的锁以削减系统开销的进程。

假如行锁是有开销的，那对1行加锁可能没问题，但对1万行加锁。那对系统开销就非常大了，此时。数据库就会将其升级为表锁，以降低开销。

\71. 说说悲观锁和乐观锁

悲观锁

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

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。

乐观锁常见的两种实现方式

乐观锁一般会使用版本号机制或CAS算法实现。

1.版本号机制(MVCC?)

一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

2. CAS算法

即compare and swap(比较与交换)，是一种有名的无锁算法。无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步(Non-blocking Synchronization)。CAS算法涉及到三个操作数

需要读写的内存值 V

进行比较的值 A

拟写入的新值 B

\72. 怎样尽量避免死锁的出现？

加锁顺序(线程按照一定的顺序加锁)

加锁时限(线程尝试获取锁的时候加上一定的时限，超过时限则放弃对该锁的请求，并释放自己占有的锁)

死锁检测