mysql面试_myisam适用于报表分析吗-优快云博客

1.优化索引，sql语句，分析慢查询
2.设计表的时候严格按照数据库设计规范来设计数据库
3.使用缓存，把京城访问并且不需要经常变化的数据放在缓存中，能够节约磁盘IO
4.优化硬件，采用ssd，使用磁盘队列技术（RAID0， RAID1，RAID5）
5.采用mysql自带的表分区技术，把数据分析分成不同文件，能够磁盘的读写效率
6.垂直分表，把一些不经常读的数据放在一张表当中，节约磁盘IO
7.主从分离读写，采用主从复制把数据库的读操作和写操作分离开来
8.分库分表分机器，数据量特别大的时候，主要的原理是数据路由
9.选择合适的表引擎，参数上的优化
10进行架构级别的缓存，静态化和分布式
11不采用全文检索
12.采用更快的存储方恨少，例如nosql存储经常访问的数据

表优化

1.优化表结构
* 尽量将表字段定义为NOT NULL约束（即使可以为空，补空字符串），这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
* 对于只包含特定类型的字段，可以使用enum、set 等数据类型。
* 数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如IP地址可以使用int类型。
* 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED。但对整数类型指定宽度，比如INT(11)，没有任何用，因为指定的类型标识范围已经确定。
* VARCHAR的长度只分配真正需要的空间
* 尽量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
* 单表不要有太多字段，建议在20以内
* 合理的加入冗余字段可以提高查询速度。
* 列名不要过长，控制20个字符以内，不要中文字段名
2. 表拆分
2.1 垂直拆分
   垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。
   插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作。但是我们可以在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。
   查询单表时，效率大大提升
2.2 水平拆分
   水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。
   有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。
3.表分区
   分区适用于例如日志记录，查询少。一般用于后台的数据报表分析。对于这些数据汇总需求，需要很多日志表去做数据聚合，我们能够容忍1s到2s的延迟，只要数据准确能够满足需求就可以。
   MySQL主要支持4种模式的分区：range分区、list预定义列表分区，hash 分区，key键值分区。
4.读写分离
   大型网站会有大量的并发访问，如果还是传统的数据存储方案，只是靠一台服务器处理，如此多的数据库连接、读写操作，数据库必然会崩溃，数据丢失的话，后果更是不堪设想。这时候，我们需要考虑如何降低单台服务器的使用压力，提升整个数据库服务的承载能力。
   我们发现一般情况对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，这样分析可以采用数据库集群的方案。其中一个是主库，负责写入数据，我们称为写库；其它都是从库，负责读取数据，我们称为读库。这样可以缓解一台服务器的访问压力。
   MySql自带主从复制功能，我们可以使用主从复制的主库作为写库，从库和主库进行数据同步，那么可以使用多个从库作为读库，已完成读写分离的效果。

SQL怎么优化

1.建索引
2.减少表之间的关联
3.优化 sql，尽量让 sql 很快定位数据，不要让sql 做全表查询，应该走索引,把数据量大的表排在前面
4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据
5.尽量用PreparedStatement 来查询，不要用 Statement
从sql上优化
不要在列上使用函数和进行运算
不要在列上使用函数，这将导致索引失效而进行全表扫描。
尽量避免使用 != 或 not in或 <> 等否定操作符
尽量避免使用 or 来连接条件
多个单列索引并不是最佳选择，复合索引的最左前缀原则
查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。
索引不会包含有NULL值的列
当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。
like 语句的索引失效问题
做过mysql的查询优化?
1.select子句中尽量避免使用*
2.where子句比较符号左侧避免函数
3.尽量避免使用in和not in,如用between
4.尽量避免使用or,用union连接
5.使用limit子句限制返回的数据行数
6.尽可能不使用子查询,使用连接（JOIN）来代替子查询(Sub-Queries)
7.LIKE双百分号无法使用到索引,组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
8.update语句如果只改一两个字段，不要update全部字段，否则频繁调用，会引起明显的性能消耗，同时带来的大量的日志

Mysql中一条SQL的执行过程

mysql索引的好处

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

b.增加索引也有许多不利的方面:
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

c.应该在这些列上创建索引：
1.在经常需要搜索的列上，可以加快搜索的速度；
2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6.在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

哪些情况会出现索引失效

1.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
2. 存储引擎不能使用索引中范围条件右边的列
3. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
4. is not null 也无法使用索引,但是is null是可以使用索引的
5. like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
6. 字符串不加单引号索引失效
7. 少用or,用它来连接时会索引失效（只针对mysql）
8. not in操作也会使索引失效

索引的原理

a.为什么要给表加上主键？
如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。
没错，再说一遍，整个表变成了一个索引，也就是所谓的「聚集索引」。这就是为什么一个表只能有一个主键，一个表只能有一个「聚集索引」
b.为什么加索引后会使查询变快？
如果把这张表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据，速度以指数级别提升
c.为什么加索引后会使写入、修改、删除变慢？
索引能让数据库查询数据的速度上升，而使写入数据的速度下降，原因很简单的，因为平衡树这个结构必须一直维持在一个正确的状态，
增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构，
因此，在每次数据改变时，DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。
B+Tree索引
是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。B-Tree需要获取所有节点，相比之下B+Tree效率更高。B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问;

hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
红黑树：树的高度随着数据量增加而增加，IO代价高。
为什么说B±tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？
B+tree的磁盘读写代价更低，B+tree的查询效率更加稳定
数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
B+树的特点：
（1）所有关键字都出现在叶子结点的链表中(稠密索引)，且链表中的关键字恰好是有序的;
（2）不可能在非叶子结点命中;
（3）非叶子结点相当于是叶子结点的索引(稀疏索引)，叶子结点相当于是存储(关键字)数据的数据层;

创建索引时需要注意什么
1.非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
2.取值离散大的字段：变量各个取值之间的差异程度大的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
3.索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。
4.唯一、不为空、经常被查询的字段的字段适合建索引

MYISAM和InnoDB区别

1.MyISAM不支持事务，而InnoDB支持事务。
2.MyISAM支持表级锁，而InnoDB支持行级锁。
3.MyISAM不支持外键，而InnoDB支持外键
4.MyISAM在查询效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
5.InnoDB表比MyISAM表更安全（数据）。
适用场景：
MyISAM适合：插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择，没有事务。
InnoDB适合：可靠性要求比较高，或者要求事务；表更新和查询都相当的频繁，大量的INSERT或UPDATE

分布式ID

1）UUID
2）数据库自增ID
3）数据库多主模式
4）号段模式
5）Redis
6）雪花算法（SnowFlake）
7）滴滴出品（TinyID）
8）百度（Uidgenerator）
9）美团（Leaf）

聚集索引与非聚集索引的区别

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。聚集索引（innodb）的叶节点就是数据节点，而非聚集索引(myisAM)的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。（结合上文）
所谓聚集索引是物理地址连续存放的索引，在取区间的时候，查找速度非常快，但同样的，插入的速度也会受到影响而降低。聚集索引的物理位置使用链表来进行存储。

何时使用聚集索引或非聚集索引

事务的四大特性

1.原子性:事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2.一致性:事务前后数据的完整性必须保持一致
3.隔离性:是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰, 多个并发事务之间数据要相互隔离，不能相互影响。
4.持久性:指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

事务的并发问题

1.脏读:一个事务读取到了另一个事务中尚未提交的数据
2.不可重复读:一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题
3.幻读:一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是insert或delete时引发的问题

事务的隔离级别

1.读未提交一个事务在执行过程中，既可以访问其他事务未提交的新插入的数据，又可以访问未提交的修改数据。如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据。此隔离级别可防止丢失更新。
2.读已提交一个事务在执行过程中，既可以访问其他事务成功提交的新插入的数据，又可以访问成功修改的数据。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。此隔离级别可有效防止脏读。
3.可重复读一个事务在执行过程中，可以访问其他事务成功提交的新插入的数据，但不可以访问成功修改的数据。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。此隔离级别可有效防止不可重复读和脏读。
4.串行化提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。此隔离级别可有效防止脏读、不可重复读和幻读。但这个级别可能导致大量的超时现象和锁竞争，在实际应用中很少使用。

MySQL的默认隔离级别：可重复读
事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持.
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。
Myql中的事务回滚机制,持久性，隔离级别的实现
恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。当事务已经被提交之后，就无法再次回滚了回滚日志作用：
1)能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息
2) 在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。
MySQL 使用重做日志（redo log）实现事务的持久性在数据库中，这两种日志经常都是一起工作的隔离级别的实现
数据库对于隔离级别的实现就是使用并发控制机制对在同一时间执行的事务进行控制，限制不同的事务对于同一资源的访问和更新
时间戳(乐观锁):
使用时间戳实现事务的隔离性时，往往都会使用乐观锁，先对数据进行修改，在写回时再去判断当前值，也就是时间戳是否改变过，如果没有改变过，就写入，否则，生成一个新的时间戳并再次更新数据

数据库三范式

1）所有字段值都是不可分解的原子值。
2）在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
3）数据表中的每一列数据都和主键直接相关，而不能间接相关。
第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。
上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。
第二范式(确保表中的每列都和主键相关)
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说
在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。
第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

mvcc

什么是MVCC？
mvcc，也就是多版本并发控制，是为了在读取数据时不加锁来提高读取效率和并发性的一种手段。

数据库并发有以下几种场景：

读-读：不存在任何问题。
读-写：有线程安全问题，可能出现脏读、幻读、不可重复读。
写-写：有线程安全问题，可能存在更新丢失等。
mvcc解决的就是读写时的线程安全问题，线程不用去争抢读写锁。

mvcc所提到的读是快照读，也就是普通的select语句。快照读在读写时不用加锁，不过可能会读到历史数据。

还有一种读取数据的方式是当前读，是一种悲观锁的操作。它会对当前读取的数据进行加锁，所以读到的数据都是最新的。主要包括以下几种操作：

select lock in share mode（共享锁）
select for update（排他锁）
update（排他锁）
insert（排他锁）
delete（排他锁）
MVCC的实现
1.回顾事务的特性

原子性：通过undolog实现。
持久性：通过redolog实现。
隔离性：通过加锁（当前读）&MVCC（快照读）实现。
一致性：通过undolog、redolog、隔离性共同实现。
2.回顾事务的隔离级别

读未提交：允许读取尚未提交的数据变更。可能会导致脏读、幻读或不可重复读。
读已提交：允许读取已经提交的数据。可能会导致幻读和不可重复读。
可重复读：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改。可能会导致幻读。
可串行化：最高隔离级别。
在读已提交和可重复读隔离级别下的快照读，都是基于MVCC实现的！