1、MySQL中有哪些存储引擎？
Mysql 体系架构如下图：
从体系结构图中可以发现，MySQL数据库区别于其他数据库的最重要的⼀个特点就是其插件式的表存储引擎。
插件式存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应⽤建⽴不同存储引擎表。
InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，也是最重要、使用最⼴泛的存储引擎。它被设计⽤来处理⼤量的短期(short-lived)事务，应该优先考虑InnoDB引擎。
MylSAM存储引擎
在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。
但是MyISAM不支持事务和行级锁，而且崩溃后⽆法安全恢复。
同时MyISAM对整张表加锁，很容易因为表锁的问题导致典型的的性能问题。
Memory 引擎
Memory表至少比MyISAM 表要快⼀个数量级，数据⽂件是存储在内存中。
Memory表的结构在重启以后还会保留，但数据会丢失。
Memroy表在很多场景可以发挥好的作⽤:
用于查找(lookup）或者映射(mapping）表，例如将邮编和州名映射的表。
用于缓存周期性聚合数据(periodically aggregated data)的结果。
用于保存数据分析中产⽣的中间数据。
Archive引擎
Archive存储引擎只⽀持INSERT和SELECT操作，会缓存所有的写并利用zlib对插⼊的进行压缩，所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合⽇志和数据采集类应用。
CSV引擎
CSV引擎可以将普通的CSV⽂件(逗号分割值的⽂件）作为MySQL 的表来处理，但这种表不⽀持索引。因此CSV引擎可以作为⼀种数据交换的机制，非常有用。
2、解释索引有哪些好处
Arts and Sciences - Computer Science | myUSF
使用索引能过提高查询效率
索引就像一本书的目录，能够快速定位到某一行的数据，
如果没有使用索引，会全表索引，效率比较低，时间复杂度为O(n),
使用索引时间复杂度为O(logn)
3、InoDB 和 MyISAM 有什么区别？
- InnoDB 支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而 MyISAM就不可以了；
- MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用；
- InnoDB 支持外键，MyISAM 不支持；
- MyISAM 是默认引擎，InnoDB 需要指定；
- InnoDB 不支持 （全文索引）FULLTEXT 类型的索引；
- InnoDB 中不保存表的行数，如 select count(*) from table 时，InnoDB；需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。注意的是，当 count(*)语句包含 where 条件时 MyISAM 也需要扫描整个表；
- 对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM表中可以和其他字段一起建立联合索引；
- 清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表；
- InnoDB 支持行锁（某些情况下还是锁整表，如
update table set a=1 where user like '%lee%'） - InnoDB 是聚集索引，MyISAM 是非聚集索引。
4、应用场景
- MyIASM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量
select操作，应该选择MyIASM InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB
底层索引 B+TREE 存放索引的数据结构
Myisam 和 Innodb 都是B+tree数据结构
- InnoDB 直接通过我们叶子节点，因包含索引文件和数据 ，找到我们相对应的数据（key value）；
- MyIASM key 指向是物理内存的地址，通过物理内存地址先找到索引，在通过索引我们相对应的数据；
5、SQL优化有哪些
- 为查询缓存优化查询
- EXPLAIN 我们的SELECT查询(可以查看执行的行数)
- 当只要一行数据时使用LIMIT 1
- 为搜索字段建立索引
- 在Join表的时候使用相当类型的列，并将其索引
- 千万不要 ORDER BY RAND ()
- 避免SELECT *
- 永远为每张表设置一个ID
- 可以使用ENUM 而不要VARCHAR
- 尽可能的使用NOT NULL
- 固定长度的表会更快
- 垂直分割
- 拆分打的DELETE或INSERT语句
- 越小的列会越快
- 选择正确的存储引擎
- 小心 "永久链接"
6、数据库表设计时，字段你会如何选择？
- 字段类型优先级
整型 > date,time > enum char > varchar > blob,text
选用字段长度最小、优先使用定长型、数值型字段中避免使⽤ “ZEROFILL”。
time : 定⻓运算快，节省时间，考虑时区，写sql不⽅便
enum : 能约束值的⽬的，内部⽤整形来储存，但与char联查时，内部要经历串与值的转化
char : 定⻓，考虑字符集和校对集
varchar : 不定⻓，要考虑字符集的转换与排序时的校对集，速度慢
text,blob : ⽆法使⽤内存临时表(排序操作只能在磁盘上进行)
注意： date，time的选择可以直接选择使⽤时间戳，enum("男","⼥") //内部转成数字来储存，多了⼀个转换的过程，可以使⽤tinyint代替最好使⽤tinyint。
- 可以选整型就不选字符串
整型是定⻓的，没有国家/地区之分，没有字符集差异。例如：tinyint 和 char(1) 从空间上看都是⼀字节,但是 order by 排序 tinyint 快。原因是后者需要考虑字符集与校对集(就是排序优先集)。
- 够用就行不要慷慨
大的字段影响内存影响速度。以年龄为例:tinyint unsigned not null;可以储存255岁，足够了，用int浪费3个字节。以varchar(10)，varchar(300)储存的内容相同，但在表中查询时，varhcar(300)要花用更多内存。
- 4. 尽量避免使用NULL
Null不利于索引，也不利于查询。=null或者!= null都查询不到值，只有使用 is null 或者 is not null 才可以。因此可以在创建字段时候使用 not null default "" 的形式。
- 5. char与varchar选择
char长度固定，处理速度要比varchar快很多，但是相对较费存储空间；所以对存储空间要求不大，但在速度上有要求的可以使用char类型，反之可以⽤varchar类型。
7、Mysql几种数据结构比较优缺点
索引就是为了节省内存，提高query的效率和速度，当然存在硬盘里，用的时候才会在内存中运行。
1、Hash
- 优点：查询效率非常高，底层是数组结构，查询复杂度为O(1)；
- 缺点：但条件查询效率低；
2、平衡二叉树
平衡二叉树查询效率还可以，但是范围查询效率比较低，需要自旋当数据高度越大，查询时间复杂大。
3、B-TREE
地址：B+ Tree Visualization
目录页地址:Data Structure Visualization
一个节点可以存放多个索引，提高了查询效率，但是范围查询效率比较低；
4、B+TREE
B+tree 继承了B tree,新增了叶子节点和非叶子节点，叶子节点包含了 key value,非叶子节点包含了key ,通过叶子节点查询非叶子节点，获取相对应的value,在通过value获取相对应的数据，查询效率非常高；非叶子节点使用链表，有序的排序，提高查询效率；
缺点：会有冗余的节点数据，会占用硬盘大小；
8、MySQL中如何定位慢查询
在默认情况下，mysql是不会记录慢查询sql, mysql默认认为10s才是慢查询
mysql> show status like 'connections'; [查看连接数]
mysql>show status like 'uptime' [mysql运行多长时间]
mysql> show status like 'slow_queries'; 【查看慢查询的次数】
为了能够记录慢查询，我把这个慢查询的默认时间修改成1s
mysql>set long_query_time=1;
mysql>show variables like ' long_query_time'
9、MySQL索引失效有几种原因
1.当字段是字符串类型，查询语句没有使用引号，如注意：user_name是字符串类型
Select * from order where user_name= 123
- like 会导致索引失效；
- or条件也会导致索引失效；
- 为null字段也会导致索引失效；
- Is null 失效 is not null 索引不会失效
- 6.组合索引 没有遵循最左侧，索引也会失效；
10、 MySQL聚簇索引和非聚簇索引的的区别
“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。
聚簇索引
每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC）和余下的列(如col2)。
聚簇索引：将索引与数据存放在B+TREE树中，存放在叶子节点中，通过聚簇索引查找只需要查找一次就可以，而非聚簇索引需要同查找到主键ID,再通过主键id查找数据；
非聚簇索引
非聚簇索引，又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的KEY字段加主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。
ID 最好做自增ID,如果用UUIND,排序分配空间计算
InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引，且MYISAM引擎中可以不设主键。
11、Mysql中VARCHAR(M)最多能存储多少数据？
对于VARCHAR(M)类型的列最多可以定义65535个字节。其中的M代表该类型最多存储的字符数量，但
在实际存储时并不能放这么多。
MySQL对⼀条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的
列（不包括隐藏列和记录头信息）占⽤的字节⻓度加起来不能超过65535个字节。
12、请说下事务的基本特性
事务应该具有4个属性：原⼦性、⼀致性、隔离性、持久性。这四个属性通常称为ACID特性。
原子性：指的是⼀个事务中的操作要么全部成功，要么全部失败。
一致性：指的是数据库总是从⼀个⼀致性的状态转换到另外⼀个⼀致性的状态。⽐如A转账给B100块钱，假设中间sql执⾏过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。
隔离性：指的是⼀个事务的修改在最终提交前，对其他事务是不可⻅的。
持久性：指的是⼀旦事务提交，所做的修改就会永久保存到数据库中。
13、事务并发可能引发什么问题？
脏读
1、在事务A执⾏过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据。
2、由于某些原因，事务A并没有完成提交，发⽣了RollBack操作，则事务B读取的数据就是脏数据。
这种读取到另⼀个事务未提交的数据的现象就是脏读(Dirty Read)。
不可重复读
事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不⼀致。
这种在同⼀个事务中，前后两次读取的数据不⼀致的现象就是不可重复读(Nonrepeatable Read)。
幻读
事务B前后两次读取同⼀个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后⼀次读取到前⼀次查询没有看到的⾏。
幻读和不可重复读有些类似，但是幻读强调的是集合的增减，⽽不是单条数据的更新
14、简单描述下MySQL各种索引？
MySQL索引按字段特性分类可分为：主键索引、普通索引、前缀索引
1. 主键索引
建立在主键上的索引被称为主键索引，⼀张数据表只能有⼀个主键索引，索引列值不允许有空值，通常
在创建表时⼀起创建。
2. 唯一索引
建⽴在UNIQUE字段上的索引被称为唯⼀索引，⼀张表可以有多个唯⼀索引，索引列值允许为空，列值
中出现多个空值不会发⽣重复冲突。
3. 普通索引
建⽴在普通字段上的索引被称为普通索引。
4. 前缀索引
前缀索引是指对字符类型字段的前⼏个字符或对⼆进制类型字段的前几个bytes建⽴的索引，⽽不是在
整个字段上建索引。前缀索引可以建⽴在类型为char、varchar、binary、varbinary的列上，可以⼤⼤
减少索引占⽤的存储空间，也能提升索引的查询效率。
前缀索引是⼀种能使索引更小更快的有效⽅法，但是也包含缺点：mysql无法使用前缀索引做
order by 和 group by
15、什么是三星索引？
对于⼀个查询而言，⼀个三星索引，可能是其最好的索引。
如果查询使⽤三星索引，⼀次查询通常只需要进行⼀次磁盘随机读以及⼀次窄索引片的扫描，因此其相应时间通常比使用⼀个普通索引的响应时间少几个数量级。
⼀个查询相关的索引行是相邻的或者至少相距足够靠近的则获得⼀星；
如果索引中的数据顺序和查找中的排列顺序⼀致则获得⼆星；
如果索引中的列包含了查询中需要的全部列则获得三星。
三星索引在实际的业务中如果无法同时达到，⼀般我们认为第三颗星最重要，第⼀和第⼆颗星重要性差不多，根据业务情况调整这两颗星的优先度。
16、InnoDB⼀棵B+树可以存放多少行数据？
这个问题的其实非常简单：约2千万
计算机在存储数据的时候，有最小存储单元，在计算机中磁盘存储数据最⼩单元是扇区(这就好比我们今天进行现金的流通最小单位是一毛)，⼀个扇区的大小是 512 字节，而文件系统（例如XFS/EXT4）的最
小单元是块，⼀个块的大小是 4k，⽽对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页
（Page），⼀个页的大小是 16K。
Innodb 的所有数据⽂件（后缀为 ibd 的⽂件），他的⼤⼩始终都是 16384（16k）的整数倍。
数据表中的数据都是存储在页中的，所以⼀个页中能存储多少行数据呢？假设⼀页数据的大小是 1k，那么⼀个页可以存放 16 行这样的数据。
对于B+树⽽⾔，只有叶⼦节点存放数据，非叶⼦节点存放的是只保存索引信息和下⼀层节点的指针信息。⼀个⾮叶⼦节点能存放多少指针？
其实这也很好算，我们假设主键 ID 为常⽤的bigint 类型，长度为 8 字节，⽽指针大小在 InnoDB 源码中设置为 6 字节，这样⼀共 14 字节，我们⼀个⻚中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170个。
那么可以算出⼀棵⾼度为2的B+树，存在⼀个根节点和若⼲个叶⼦节点能存放 1170*16=18720 条这样的数据记录。
根据同样的原理我们可以算出⼀个高度为 3 的B+ 树可以存放： 1170*1170*16=21902400 条这样的记录。
17、如何提高insert的性能？
- 合并多条 insert 为⼀条
即： insert into t values(a,b,c), (d,e,f) ,,,
原因分析：主要原因是多条insert合并后⽇志量（MySQL的binlog和innodb的事务⽇志）减少了，降低日志刷盘的数据量和频率，从⽽提⾼效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少⽹络传输的IO。
- 修改参数 bulk_insert_buffer_size，调⼤批量插⼊的缓存；
- 设置 innodb_flush_log_at_trx_commit = 0
相对于 innodb_flush_log_at_trx_commit = 1 可以⼗分明显的提升导⼊速度；
innodb_flush_log_at_trx_commit 参数解释如下：
0：log buffer中的数据将以每秒⼀次的频率写⼊到log file中，且同时会进⾏⽂件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file 的刷新或者⽂件系统到磁盘的刷新操作;
1：在每次事务提交的时候将log buffer 中的数据都会写⼊到log file，同时也会触发⽂件系统到磁盘的同步;
2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘⽂件系统到磁盘的同步。此外，每秒会有⼀次⽂件系统到磁盘同步操作。
- 手动事务提交
因为mysql默认是autocommit的，这样每插⼊⼀条数据，都会进⾏⼀次commit；所以，为了减少创建事务的消耗，我们手动提交事务
即START TRANSACTION;insert ......,insert ......, commit；即执⾏多个insert后再⼀起提交；⼀般1000条insert 提交⼀次。
18、什么是全局锁、共享锁、排它锁？
全局锁就是对整个数据库实例加锁，它的典型使⽤场景就是做全库逻辑备份。这个命令可以使整个库处于只读状态。使⽤该命令之后，数据更新语句、数据定义语句、更新类事务的提交语句等操作都会被阻塞。
共享锁又称读锁 (read lock)，是读取操作创建的锁。其他⽤户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进⾏修改操作，很可能会造成死锁。
排他锁 exclusive lock（也叫 writer lock）又称写锁。
若某个事物对某⼀行加上了排他锁，只能这个事务对其进⾏读写，在此事务结束之前，其他事务不能对其进⾏加任何锁，其他进程可以读取,不能进⾏写操作，需等待其释放。排它锁是悲观锁的⼀种实现。
若事务 1 对数据对象 A 加上 X 锁，事务 1 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到事物 1 释放 A 上的锁。这保证了其他事务在事物 1 释放 A 上的锁之前不能再读取和修改 A。排它锁会阻塞所有的排它锁和共享锁。
19、谈⼀下MySQL中的死锁
死锁是指两个或两个以上的进程在执⾏过程中，因争夺资源⽽造成的⼀种互相等待的现象,若无外力作用清空下、,它们都将⽆法推进下去。此时称系统处于死锁状态或系统产⽣了死锁。
如何查看死锁？
使用命令 show engine innodb status 查看最近的⼀次死锁。
InnoDB Lock Monitor 打开锁监控，每 15s 输出⼀次⽇志。使⽤完毕后建议关闭，否则会影响数据库性能。
对待死锁常见的两种策略：
通过 innodblockwait_timeout 来设置超时时间，⼀直等待直到超时；
发起死锁检测，发现死锁之后，主动回滚死锁中的某⼀个事务，让其它事务继续执行。
20、MySql中隔离级别有哪些？
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
| 读未提交（Read Uncommitted） | 有 | 有 | 有 |
| 读已提交（Read Committed） | 无 | 有 | 有 |
| 可重复读（Repeatable Read） | 无 | 无 | 有 |
| 串行化（Serializable） | 无 | 无 | 无 |
249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
