mysql最常见面试

一.sql优化

1.系统配置优化：

1）从内存中读取数据，扩大innobd_buffer_pool_size,默认128m。可到4/3，降低磁盘操作。

2）数据预热，通过预热脚本，将磁盘上的全部数据缓存到内存中

3）提高磁盘读写性能，使用ssd或内存磁盘

2.表结构设计优化

1）设计中间表：针对统计或实时性不高要求数据

2）设计冗余字段：减少表之间的关联操作，比如用户，订单，可以在订单表加用户姓名

3）拆表：字段太多或者字段使用较少进行拆分。

4）字段设计：将表字段长度设计的尽量小。因为越小查询越快，最好都喂not null避免null值，查询导致索引失效，能用数值就别用文本，数值效率高，入性别。

5）主键优化：每张表一个主键索引，分布式情况使用雪花，不然自增。

3.sql优化及索引

1）sql尽量不要使用select* 减少cpu内存io网络消耗

2）尽量减少子查询，使用并联查询

3）减少使用in not in使用exists，notsxists或关联查询替换

4）or的查询尽量使用union或者union all替换

5）尽量避免where中使用！=，<>操作符，否则放弃使用索引而进行全表查询

6）尽量避免where语句中使用null判断，否则会放弃使用索引进行全表查询

7）当只需要一条或几条数据的时候使用limit，停止全表扫描

8.不建议%前缀的模糊查询，只有后缀的%模糊查询才会走索引。

9）分段查询，当查询范围过大，查询缓慢，可采用分段查询，通过程序将结果合并

10）避免隐式转换，传入类型与定义类型一致

11）以小表驱动大表，减少循环嵌套次数。

4.explan检查sql语句

1）使用explan检查sql语句，type尽量达到range级别。

2）避免extra出现using filesort，表示引用不满足最左前列。如：where type=1 order by id或使用select*；可采取联合索引或避免使用select *；

3）出现useing temperary，如果order by 子句和group by不是同一个子句。或者order 或 group by 中的字段来自其他的表而非连接顺序中的第一个表的话。就会创建临时表。出现using temporory。所以尽量对第一顺序表使用关联查询。

二.数据库事务：

数据库事务必须具备acid属性：原子性，一致性，隔离性，持久性

1）原子性：事务是一个完整的操作，要么都执行，要么都不执行，每写一个事务，都会修改bufferpool，从而产生相应的redo/undo日志。脏页没有刷新成功数据库挂了，可以通过redo日志将其恢复，脏页刷新成功数据库挂了可以通过undo来实现同步

2）一致性：数据的完整是通过原子性，隔离性，持久性来保证的。而这个特性又是通过redo/undo来保证的

3）隔离性：innodb支持的隔离性有四种，读未提交（脏读，不可重复度，提交覆盖，幻读），读已提交（不可重复度，提交覆盖，幻读），可重复读（幻读），可串行化。锁和多版本控制（mvcc）技术就是用于保障隔离性的

4）持久性：redo log在系统重启的情况时，可以修复数据，从而保障事务的持久性。

多线程数据库产生问题：

脏读：当一个事务正在访问数据库并且对数据进行了修改，但没有提交。这时另外一个事物也访问这个数据，然后使用这个数据，因为这个数据还没有提交，那么另外一个读到的是脏数据。

修改覆盖：指一个事务读取数据时，另外一个事务也访问了该数据，那么第一个事务中修改了数据，第二个事务也修改了这个数据。这样第一个事务内的修改结果就会丢失。

不可重复读：指一个事物内多次读取一个数据，这个事务还没有结束时，另外一个事务也访问该数据。那么第一个事务中两次读数据之间。由于第二个事务的修改导致第一个事务两次读取的数据可能不一样。

幻读：与不可重复读相似，但幻读是由于新增和删除，记录多了或少了。

三.锁

排他锁：或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁

读写锁：区分读操作和写操作，让读和读之间不加锁，读写锁可以让读和读并行，读和写，写和读，写和写之间加排他锁。

操作粒度

表级锁：每次操作锁住整张表，表力度大，发生冲突的概率最高，并发度最低。

行级锁：每次操作锁住一行数据，锁定粒度最小，发生冲突概率最低，并发最高。

页级锁：每次锁定相邻一组数据，锁定粒度介于表级锁和行级锁之间，开销和枷锁时间介于表级锁和行级锁之间，并发一般。

悲观锁：在一条数据修改的时候，为了避免被其它人修改，在修改数据之前先锁定。

乐观锁：一般实现方式是对记录数据版本进行对比，在数据更新提交的时候才会进行冲突检查，如果发生冲突了，则提示错误信息。

死锁：用户a，b都锁定一张表，又相互访问切不释放锁住的表资源，因此对数据库多表操作的时候，尽量按照相同的顺序进行处理。尽量避免同时锁定两个资源。

四.引擎

1.innodb:结构是非叶子节点不存data数据，只存索引，这样能存储更多的索引值。叶子节点包含所有索引值和data。叶子节点使用指针连接，提高区间的访问性能。

2.myisam：索引值和data数据分布在整颗树结构中，每个节点可以存放多个索引值及对应的data数据。树节点中的多个索引值从左到右升序排序。