MYSQL
1,mysql性能的衡量指标?
三大指标：
- TPS：每秒传输事务处理的个数，这是指服务器每秒处理的事务数，支持事务处理的存储引擎是InooDB。
算法：TPS=（事务提交数+事务回滚数）/运行时间- QPS：每秒查询处理数，同时适用于MyISAM和InnoDB。
算法：TPS=查询数/运行时间- 响应时间
2，mysqlslap测试windows系统下的数据库压测工具？
在mysql的bin目录下，执行测试语句例如：
mysqlslap -uroot -proot –concurrency=50 –iterations 3 -a –auto-generate-sql-add-autoincrement –engine=innodb –number-ofqueries=50
mysqlslap -uroot -proot –concurrency=1,50,100,200 –iterations=3 –number-char-cols=5 –number-int-cols=5 –auto-generate-sql -auto-generate-sql-add-autoincrement –engine=myisam,innodb –create-schema=’test1’ –debug-info
两种存储引擎比较：
mysqlslap -uroot -proot –concurrency=500 –iterations=3 –number-char-cols=5 –number-int-cols=5 –auto-generatesql –auto-generate-sql-add-autoincrement –engine=myisam,innodb –create-schema=’enjoytest1’ –debug-info
内存信息：
- ·system time:在内核态下花费的时间
- ·user time:在用户态花费的时间
*·max resident size:占用的大物理内存(驻留内存)- ·Non-physical pagefaults:直接通过回收脏页来解决IO读写而产生的缺页中断的次数
- ·involuntary context switch:线程因为时间片到了或更高优先级的线程抢占导致的切换的次数
- ·voluntary context switch:线程主动让出处理器导致的切换的次数，很可能是等待IO
3，mysql逻辑架构？
分为：
- 连接层：
根据IP,账号，密码进行认证操作，然后连接。- 服务层：
1，当一次sql操作执行的时候，是在（sqlinterface中执行），会首先去找缓存中是否有，没有就先解析查询，再优化，再返回结果；有就直接返回缓存的结果。注意：缓存包括sql语句和sql结果，mysql默认开启缓存sql语句，默认不开启缓存sql语句的结果。
优化过程是mysql在执行sql的时候，会自动的进行优化操作，比如：
select * from xxx where 1==1就等价于select * from xxx，sql会自动的优化，不会去执行1==1的判断。
show variables like ‘%query_cache_type%’ ：显示缓存是否开启
show variables like ‘%query_cache_size%’ ：显示缓存大小
SET GLOBAL query_cache_size = xxx ：设置缓存大小- 引擎层 show ENGINES：显示当前数据库的存储引擎是否支持
- 存储层
4，mysql的存储引擎？
MyISAM：创建的表之后，在mysql的data目录中，会生成三个对应的文件，分别是：xxx.frm，xxx.MYD，xxx.MYI三种类型，其中：
- frm：储存表结构，任何存储引擎都具备
- MYD：数据文件（MyISAM具备）
- MYI：索引文件，也叫非聚集索引（MyISAM具备）
- CHECK table xxx：检查某个表是否有错误
- REPAIR table xxx：修复某个表
特点：
- 1，表级锁，不支持行级锁，不适合高并发
- 2，支持全文索引
- 3，支持数据压缩
命令：myisampack -b -f xxx.MYI
例如：myisampack -b -f E:\cleaning\mysql5.6\mysql-5.6.36-winx64\data\mysqldemo\product_info.MYI，后会解压源文件，生成一个OLD的新文件。- 4，不支持事务，当做gis（地图类的，空间类的）的时候，就必须使用，因为myisam包含空间函数。 >* 5，读操作很快
- 6，支持主键，不支持外键
- 7，只支持缓存索引，不缓存数据
InnoDB（mysql5.5之后默认）：
- show VARIABLES like ‘innodb_file_per_table’：显示是独立表空间还是系统表空间。
- ON：独立表空间，创建的表会生成frm和ibd两个文件，其中ibd是存储了数据和索引的文件。
- OFF：系统表空间，创建的表只有frm，存储的数据和索引被放在了ibdataX系统文件中。
5.6之前默认是系统表空间，5.6之后默认是独立表空间。
独立表空间和系统表空间特点：
1，系统表空间无法简单的收缩文件大小，会影响性能。
2，独立表空间可以通过optimize table收缩系统文件，比如：optimize tablexxx（收缩独立表数据和索引文件大小，当删除了一些数据之 后，使用此命令收缩文件，可以节约空间以及提高性能）
3，系统表空间会产生IO瓶颈。
4，独立表空间可以同时向多个文件刷新数据。
建议使用：独立表空间。
特点：
- 1，支持行级锁，并发程度更高（也支持表锁）
- 2，支持事务
- 3，支持主键，外键
- 4，支持缓存数据和索引
CSV: 数据以文本方式存储在文件中
组成：
- .csv：文件存储内容
- .csm：存储表的元数据和表状态的数据量
- .frm：储存表结构，任何存储引擎都具备
特点：
1，所有列都不能为null
2，不支持索引，不适合大表，不适合在线处理
*3，可以对数据文件直接编辑（文本内容），注意：新增数据结束要加上回车
Archive：以zlib对表数据进行压缩，磁盘I/O更少，数据存储在arz文件中
特点：
- 1，只支持insert和select
- 2，只允许在自增ID列上加索引
适用于日志和数据采集
Memory：
特点：
1，文件系统存储特点，也称为HEAP存储引擎，所以数据保存在内存中
2，支持HASH索引和BTree索引
3，所有字段都是固定长度，vachar（10）=char（10）
4，不支持Blog和Text等大字段类型
5，使用表级锁
6，大大小由max_heap_table_size决定
临时表：create temporary xxx
注意：临时表是session会话级别的，memory表虽然也是存在内存中的，但是是mysql应用级别的 1，超过限制的时候使用Myisam临时表
2，没超过限制使用Memory表
数据易丢失，所以要求数据可再生，适用于：
1，缓存周期性聚合数据的结果表
2，保存数据分析中产生的中间表
- 3，hash索引用于查找或者是映射表（邮编和地区的对应表） show VARIABLES like ‘max_heap_table_size’：显示存储空间大小
Federated： 特点：
1，可以远程连接其他mysql服务器
2，本地不存储数据，数据全部放在远程服务器上
3，本地需要保存表结构和远程服务器的连接信息
使用场景：偶尔的统计分析和手工查询
默认是禁止的，需要在mysql启动时增加federated启动参数。
5，锁？
表级锁：
- 1，开销小，加锁快
- 2，不会出现死锁
- 3，锁的粒度大，发生锁冲突的概率高，并发度低
使用场景：表级锁更适合查询，只有少量按照索引条件更新数据的应用，如OLAP系统
注意：和行锁不一样的是，表锁的事务提交或者回滚是不能释放锁的，只能 是unlock tables，才能释放锁。 有两种模式：
- 1，表共享读锁
给表加上共享锁，语法：lock table 表名 read（这种不支持别名） lock table 表名 as 别名 read（支持别名）加上读锁之后，在一个session中执行更新加锁的表的操作会失败，在不同session中执行更新加锁的表的操作会等待。 在一个session中执行更新和查询其他表的操作会失败，在不同session中执行更新和查询其他表的操作会成功。- 2，表独占写锁
给表加上共享锁，语法：lock table 表名 write 加上写锁之后，在一个session中执行更新和读取加锁的表的操作成功，在不同session中执行更新和查询加锁的表的操作会等待。
在一个session中执行更新和查询其他表的操作会失败，在不同session中执行更新和查询其他表的操作会成功。
行级锁：
- 1，开销大，加锁慢
- 2，会出现死锁
- 3，锁的粒度小，发生锁冲突的概率低，并发度高
使用场景：只有大量按照索引条件并发更新少量数据的应用，同时又有并发查询的应用。比如OLTP（在线事务处理系统）需要先开启事务，然后锁表，再提交事务或者回滚，才能释放锁，或者是开启一个新事务的时候，也会释放表锁。
读锁（共享锁）：
语法：select * from 表名 where 条件 lock in share mode
当一个事务对某几行上读锁之后，允许其他事务对这些行进行读操作，但不允许进行写操作，也不允许其他事务给这些行加排它锁，但可以加读锁。
写锁（排它锁）：
语法： select * from 表名 where 条件 for update
当一个事务对某几行上写锁之后，不允许其他事务写，但允许读，更不允许其他事务加任何锁。
注意：
- 1，两个事务不能锁同一个索引
- 2，insert，update，delete会默认加上排它锁
- 3，行锁必须有索引才能实现，否则会自动锁全表，就变成了表锁了
页面锁：
- 1，开销和加锁时间介于表锁和行锁之间
- 2，会出现死锁
- 3，锁粒度在表锁和行锁之间，并发度一般
面试题：当一个并发量很高的系统，数据量也很大，需要给A表增加一个字段，怎么办？
解决：
- 第一种：
1，需要创建一个字段和A表一样的，并且增加这个新字段的B表。 2，给A表设置一个触发器，然后将A表中的数据全部复制到B表中，并且触发器的条件设置成更新表数据的时候同步更新到B表。
3，当复制完成后，给A表加上表锁，然后修改B表的名称为A表，同时删除之前的A表。- 第二种： 使用工具pt-online-schema-change
例如：
perl E:\cleaning\pt-online-schema-change h=127.0.0.1,p=root,u=root,D=mysqldemo,t=test_numberic –alter “modify c4 varchar(15) not null default ‘’ ” –execute
pt-online-schema-change原理:
- 1、如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。
- 2、创建一个新的表，表结构为修改后的数据表，用于从源数据表向新表中导入数据。
- 3、创建触发器，用于记录从拷贝数据开始之后，对源数据表继续进行数据修改的操作记录下来，用于数据拷贝结束后，执行这些操作，保证数据不会丢失。
- 4、拷贝数据，从源数据表中拷贝数据到新表中。
- 5、修改外键相关的子表，根据修改后的数据，修改外键关联的子表。
- 6、rename源数据表为old表，把新表rename为源表名，并将old表删除。
- 7、删除触发器。
6,事务？
只有InnoDB支持事务。 数据库中执行事务涉及到很多方面，包括如何处理临界资源，如何加锁解锁等等。但是无论事务如何执行，都需要保证以下几个特性（ACID）：
- 原子性：所有的操作是一个逻辑单元，要么都提交成功，要么就都失败；
一致性：只有合法的数据被写入数据库，否则事务回滚到初的状态；
隔离性：允许多个事务同时进行，而不会破坏数据的正确性和完整性；
持久性：事务结束后，已经提交的结果被固化保存。
注意：数据持久性并不是数据库就能完全解决的。
悲观锁：在当前事务执行的时候，不允许其他事务的操作。
悲观锁的几种实现：
共享锁（读锁） 排它锁（写锁） 更新锁（写锁）
锁包括行级锁和表级锁:
- 行级锁:
是一种排他锁，防止其他事务修改此行；在使用以下语句时，Oracle会自动应用行级锁.
表级锁又分为5类：
行共享 (ROW SHARE) – 禁止排他锁定表
行排他(ROW EXCLUSIVE) – 禁止使用排他锁和共享锁
共享锁(SHARE) - 锁定表，对记录只读不写，多个用户可以同时在同一个表上应用此锁
共享行排他(SHARE ROW EXCLUSIVE) – 比共享锁更多的限制，禁止使用共享锁及更高的锁
排他(EXCLUSIVE) – 限制强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表。- 乐观锁：
在表中加时间戳或者版本的字段，在第一次查询的时候将这个字段记录下来，用于在事务结束提交之前比较，如果字段内容相同就提交事务。
如果不考虑事务隔离，会发生的三种情况：
脏读，不可重复读，幻读
四种事务的隔离级别：
Serializable（串行化）：可避免脏读，不可重复读，幻读 Repeatable read（可重复读其实就是读已提交，即是在同一事务中，多次查询同一数据不一样，因为在其他事务更新了这个数据，还未提交事务的时候查询的是之前的值，提交了之后读取了更新后的值，两次查询结果不一样）：可避免脏读，不可重复读
Read committed（读已提交，读取其他事务已经提交了的值）：可避免脏读
Read uncommitted（读未提交，读取其他事务更新了的值，就算没有提交事务也可以查询到）：什么都不能避免 事务级别越高，执行效率就越低。
mysql的默认隔离级别是可重复读。
show variables like ‘%tx_isolation%’：显示事务的隔离级别。
savepoint：回滚点，可以设置事务回滚到这个点。
脏读：事务A读取了事务B更新的数据，然后B回滚，A就读取了脏数据。
不可重复读：事务A多次读取同一数据，事务B在事务A读取过程中，修改了这个数据，并且提交事务，这个时候事务A就读取了修改后的数据，导致事务A多次读取的数据不一致。
幻读：事务A将数据库的成绩字段由具体分数改为了ABCD等级，但是这个时候事务B又提交了一条之前的显示具体分数的记录，事务A修改完了之后发现了这条记录，就发现是一条 以前的数据，感觉像幻觉一样，就是幻读。
注意：不可重复读和幻读容易混淆，不可重复读侧重于修改，幻读侧重于新增或者删除，解决不可重复读的问题只需要锁住满足条件的行，解决幻读需要锁表。 事务级别是可重 复读的时候，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间，行锁，页锁的问题，从而锁住一些行，如果没有索引，更新数据就会锁住整个表。 所以，当没有索引的时候，会出现行锁升级为表锁的情况。
7种事务传播行为：（常用的两种）
- 1、PROPAGATION_REQUIRED：如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是常用的设置。
2、PROPAGATION_SUPPORTS：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。
在spring中配置事务：
1，配置事务源，绑定数据源
2，开启事务源的注解驱动
3，设置事务源的传播行为
4，设置切面，设置被代理的目标类是否为类或者接口以确定aop的实现方式，指定事务源为切点
7，数据库范式和反范式？
范式：
- 第一范式：表中的字段是单一属性。
第二范式：表中只具有一个业务主键，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系。
第三范式：每一个非非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上相处了非主键对主键的传递依赖。
优点： 可以尽量得减少数据冗余 范式化的更新操作比反范式化更快 范式化的表通常比反范式化的表更小 缺点： 对于查询需要对多个表进行关联 更难进行索引优化
反范式：
- 反范式化是针对范式化而言得，所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反，允许存在少量得冗余，换句话来说反范式化就是使用 空间来换取时间。
优点： 可以减少表的关联 可以更好的进行索引优化
缺点： 存在数据冗余及数据维护异常 对数据的修改需要更多的成本
8，字段设计？
当一个列可以选择多种数据类型时：
优先考虑数字类型 其次是日期、时间类型 后是字符类型 对于相同级别的数据类型，应该优先选择占用空间小的数据类型
- 浮点类型：
FlOAT：4个字节，不是精确类型
DOUBLE：8个字节，不是精确类型 DECIMAL：每4个字节存9个数字，小数点占1个字节，是精确类型（设计财务类的字段时候，优先考虑）
时间类型：
datetime在5.5及其以前是8个字节，5.6以后是5个字节，timestamp是4个字节。
timestamp和时区有关，而datetime无关，所以优先使用timestamp。
注意：timestamp因为本质存的是int类型的时间戳，所有有大小区间，默认是从1970开始到2037年结束，超过就存不了了。
9，慢查询？
慢查询日志：顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，
首先要开启慢查询日志功能。
常用配置：
- slow_query_log 启动停止技术慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
log_queries_not_using_indexes 是否记录未使用索引的SQL log_output
日志存放的地方【TABLE】【FILE】【FILE,TABLE】，默认是存放在文件中。（注意：在开发中，不要设置为TABLE）
记录符合条件得SQL：
*
查询语句 数据修改语句 已经回滚得SQL
show VARIABLES like ‘slow_query_log’：显示数据库是否启动慢查询日志（默认关闭）。
set GLOBAL slow_query_log = 1：启动慢查询日志。
set GLOBAL long_query_time = 1：设置慢查询阈值为1S。
10，慢查询工具？
1，mysqldumpslow
作用：汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。
语法：
mysqldumpslow -s r -t 10 slow-mysql.log -s order (c,t,l,r,at,al,ar)
c:总次数 t:总时间 l:锁的时间 r:总数据行 at,al,ar :t,l,r平均数
【例如：at = 总时间/总次数】 -t top 指定取前面几天作为结果输出
例如：mysqldumpslow.pl -s t -t 10 E:\cleaning\mysql5.6\mysql-5.6.36-winx64\data\SWNMOZSRILYL6H0-slow.log
（打印本地mysql慢查询日志中的前 十条信息）
缺点：必须要有登录mysql的权限才可以使用。2，pt_query_digest 可以更显示更详细的慢sql日志信息，比如执行计划等，并且可以远程连接其他数据库。
例如：perl .\pt-query-digest –explain h=127.0.0.1,u=root,p=root
E:\cleaning\mysql5.6\mysql-5.6.36-winx64\data\SWNMOZSRILYL6H0slow.log
11，索引？
MySQL官方对索引的定义为：
索引（Index）是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。
索引的实现的数据结构是：树，mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引。 B+树索引是B+树在数据库中的一种实现，是常见也是数据库中使用为频繁的一种索引。
分类：
- 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值 复合索引：即一个索引包含多个列
聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术 上来说是B+Tree)和数据行。
非聚簇索引：不是聚簇索引，就是非聚簇索引
查看索引： SHOW INDEX FROM table_name
创建索引： CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
删除索引： DROP INDEX [indexName] ON mytable;
关于建立索引的几个准则：
- 1、合理的建立索引能够加速数据读取效率，不合理的建立索引反而会拖慢数据库的响应速度。
2、索引越多，更新数据的速度越慢。
3、不要在选择的栏位上放置索引，这是无意义的。应该在条件选择的语句上合理的放置索引，比如where，order by。
4、把常用的，筛选数据多的字段放在左侧。
5、单个索引需要注意的事项，组合索引全部通用。比如索引列不要参与计算啊、or的两侧要么都索引列，要么都不是索引列啊、模糊匹配的时候%不要在头部啦等等。
6、左匹配原则。(A,B,C) 这样3列，mysql会首先匹配A，然后再B，C，如果用(B,C)这样的数据来检索的话，就会找不到A使得索引失效。如果使用(A,C)这样的数据来检索的话，就会先找到所有A的值然后匹配C，此时联合索引是失效的。
适合建索引的条件：
- 1，某个字段相对唯一
2，经常用来查询显示的列
3，作为条件或者是关联条件的列
12,数据库的横向分表和纵向分表？
- 主库： 对于经常变化处理的数据和新数据，在主库中处理。
从库： 对于不经常变化，只是查询的一些旧数据，在从库中处理。
横向分表： 将一个数据量很大的表，将其中的数据分割到其他相同结构的分表中去，再利用算法在查询的时候，查询具体的表数据。
纵向分表： 分析表的结构字段，例如创建时间，作者，标题这样的不经常变化的数据，称为冷数据，这样的数据存储在从库中，而对于经常变化的数据称为活跃数据，如浏览量等，这些数据 存储在主库中， 利用横向分表的形式分表处理。对于冷数据存储引擎用Myisam，查询速度快，对于活跃数据存储引擎用Inoodb，更新速度快。
13,数据库n+1问题？
1+n是执行一次查询获取n条主数据后，由于关联引起的执行n次查询从数据,它带来了性能问题； 一般来说，通过懒加载可以部分缓解1+n带来的性能问题
tql