mysql常见问题

一、 mysql引擎分为mysiam和Inodb，他们区别

1．mysiam只支持表级锁，不支持行级锁；Inoodb支持表级锁和行级锁，默认用的是行级锁；
2．Inoodb支持事物，但是他也给Inoodb带来了更大的开销
3．mysiam上锁和提交是一步执行的，Inoodb的上锁为提交可以分开执行，分为两步走
4．InooDB在查询是没有用到索引时，走的是表级别的锁，用到索引走行级别的锁
5．INoodb必须要密集索引

二、Innodb和mysiam适合的场景

1．MyIsam适合的场景：
频繁执行全表count语句
对数据进行增删改的频率不高的，查询很频繁的
没有事物的
2.InnoDB的适用场景：
数据的增删改查都相当频繁的
可靠性要求高，要求支持事务的

四、数据库事务的四大特性

ACID：
原子性（Atomic）：如一个事务提交之后要么全部更新成功，要么都失败。
一致性（Consistency）：如转账，他们之间无论怎么转账几次，转账多少钱，他们的余额之和是不变的。
隔离性（Isolation）：多个事务并发执行时，相互之间没得影响
持久性（Durability）：一旦更新以后永远生效

五、事务并发访问引起的问题以及如何避免

1．更新丢失—mysql所有事物隔离级别在数据库层面上均可避免
如：同时存在取款事物和存款事物

根据业务的需要设置事物级别，不同的事物级别性能消耗不一样，事物级别越高，消耗性能越大，mysql的默认性能级别为：可重复度的

六、mysql语法部分

group by 、having、统计函数
having通常和group by子句一起使用，where过滤行，having过滤组
出现在同一sql的顺序：where 》 group by > having
explain select student_id,count(course_id),sum(score) from score group by student_id;
结果重点关注type和Extra字段

七、为什么要使用索引，哪些情况下建议使用索引？哪些情况不使用索引？

1、使用索引是避免全表查询，提交查询的效率
2、使用索引：
主键自动建立唯一索引
频繁作为查询条件的字段应该建立索引
查询中其他表关联字段，外键关系建立索引
频繁更新的字段不建议建立索引，因为每次更新不单单更新记录还会更新索引
where条件里面用不到的字段不建议建立索引
查询中排序的字段，排序的字段若通过索引去访问将会大大提高排序速度
查询中分组和统计字段
2.哪些情况不需要创建索引
表记录太少、经常增删改的表、如果某个数据列经常增删改，就没有必要建立索引

八、什么样的信息能成为索引？

主键、辨识度高的列可以作为索引、唯一性高的不存在空的列可以作为索引

九、索引的数据结构

索引是一种数据结构。索引本身很大，不可能存储到内存中。因此，索引是存储在磁盘中的。这样的话，索引查找的过程就会产生磁盘I/O的消耗，评价一个索引数据结构的优劣是在查找过程中对磁盘I/O操作的次数。
索引使用到的数据结构有：B tree、B+Tree 、Hash索引，MYSQL使用B+树做索引。
不同的存储引擎使用的索引不一样，MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。主索引和辅助索引都是B+树，叶子节点都存储的是数据记录的地址，索引文件和数据文件是分离的，主索引和辅助索引都不会影响数据文件。虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。第一个重大区别是InnoDB的数据文件本身就是索引文件。第二个与MyISAM索引的不同是 InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

十、密集索引和稀疏索引的区别

密集索引中的每个搜索码都对应一个索引值
稀疏索引中只为索引码的某些建立索引值
MyISAM–》 主键索引，唯一键索引，还是普通索引 —都是 稀疏索引
InnoDB：
若一个主键被定义，则该主键作为密集索引
若没有主键，则该表的第一唯一非空索引作为密集索引
若不满足以上条件，则innodb则会自动生成一个隐藏主键
非主键索引存储相关键位和对应的主键值，包含两次查找。
InnoDB聚簇表分布
myisam在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。
.frm 用于存储表的定义
.MYD 用于存放数据
.MYI 用于存放表索引

十一、如何定位慢查询

1. 根据慢日志定位慢sql
   SHOW VARIABLES LIKE ‘%query%’ 查询慢日志相关信息
   

SHOW STATUS LIKE ‘%slow_queries%’ 查看慢查询状态

Slow_queries 记录的是慢查询数量 当有一条sql执行一次比较慢时，这个vlue就是1 （记录的是本次会话的慢sql条数）
2.使用explain查看该查询语句的相关情况，重点看type字段和extra字段
type：sytem》const〉eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
extra:中出现了以下两项则意味着mysql根本不能使用索引，效率会受到很大影响，应该尽可能对此优化
Using filesort:表示mysql会对结果使用一个外部索引排序，而不是从表里按照索引次序读到相关内容，可能在内存或者磁盘上进行排序，mysql中无法利用索引完成的排序称为“文件排序”
Using temporary:表示mysql在对查询结果排序时使用了临时表，常见于排序order by 和分组查询group by
3.修改该查询语句，用使用含索引的列作为条件查询
可参考：
https://mp.weixin.qq.com/s/q-wFULexFz0gy6zv3RsjZA

十二、联合索引的最左匹配原则的成因

在mysql中，如果设置一个联合索引包含a和b字段，在where中a=？ and b=？这个是会使用联合索引的。如果只where a=？也会使用该联合索引，但是如果只使用where b=？这个是不会使用该联合索引的

如mysql创建复合索引的规则是首先会对复合索引的最左边，也就是索引中的第一个字段进行排序，在第一个字段排序的基础上，在对索引上第二个字段进行排序，其实就像是实现类似order by 字段1，字段2这样的排序规则，那么第一个字段是绝对有序的，而第二个字段就是无序的了，因此一般情况下直接只用第二个字段判断是用不到索引的，这就是为什么mysql要强调联合索引最左匹配原则的原因。

最左前缀匹配原则是非常重要的原则。Mysql一直会向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如where a=3 and b=4 and c>5 and d=6,建立的（abcd）的索引，则d是用不到索引的，，如果建立（abdc）则都可以使用，abd的顺序可以任意调整
=和in 可以看成时乱序，比如a=1 and b=2 and c=3，建立（abc）的索引可以是乱序，mysql的查询优化器可以帮你优化为索引可以识别的顺序

十三、索引是建立的越多越好吗？

不是的：
1.数据量小的表不需要建立索引，建立会增加额外的索引开销
2.不经常引用的列不要建立索引，因为不常用，即使建立了索引也没有多大意义。对经常用于查询的字段应该创建索引。
3.经常频繁更新的列不要建立索引，因为肯定会影响插入或更新的效率
4.数据重复且分布平均的字段，因此他建立索引就没有太大的效果（例如性别字段，是否上架等等，不适合建立索引）
5.数据变更需要维护索引，意味着索引越多维护成本越高。当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。
6.更多的索引也需要更多的存储空间，一个表中很够创建多个索引，这些索引度会被存放到一个索引文件中(专门存放索引的地方)。

#show variables like ‘autocommit’;
set automommit=0;关闭自动提交
select * from person_info_large where id=3;
commit;
update person_tabel set id=4 lock in share mode; #加共享锁

#创建一个表：
create table student(
‘id’int(7) not null AUTO_INCREMENT,
‘account’varchar(10) DEFAULT NULL,
‘name’varchar(20) DEFAULT NULL,
‘title’varchar(20) DEAULT NULL,
PRIMARY key (‘id’),
UNIQUE KEY ‘account’(‘account’),
KEY ‘index_area_title’(‘area’,’title’),
KEY ‘index_name’(‘name’)
)ENGINE=InnoDB AUTO_INCREMENT=2312991 DEFAULT CHARSET=utf8;

十四、请你写一些mysql的删除语句

1）drop可以删除数据库和表：
drop table student
drop database db
2）delete可以删除表中字段：delete from student where id=1；它支持全表删除和按照where条件删除，delete删除是可以进行事物的回滚的。因此删除时需要记录一些信息，删除的速度表truncate慢
3）truncate用来删除表中所有的字段，只支持全表的删除，不支持按照条件删除，它删除后时不允许回滚的，删除了就是永久删除了，因此效率比delete要高
truncate table student

十五、请说一下数据库mysql中char和varchar的区别

char(n)类型：char类型时定长的类型，如果定义char(10),输入的是“abc”三个字符时，他们占用的空间也是一样的10个字节，包括7个空字节。当输入的字符数超过指定的长度时会自动截取掉超出的字符。而且，当输入的字符串包含空格时会自动截取掉输入字符串末尾的空格。Char适合存储很短的，一般固定长度的字符串。如：存储密码的md5，因为这是一个定长的值
varchar(n)类型用于存储可变长的，如定义varchar(10),输入‘abc’三个字符时，那么实际存储大小为3个字节。

十六、请你说一下sql左连接以及场景

左连接：left join ，如 table_a left join table_b
使用场景：为了保持左表完整加入另一表中的数据

十七、某个表格中有10条一摸一样的数据，现在要删除其中的9条，请写一下sql语句

delete from table_name limit 9;
delete from table_name where id in(
select a.m
from (select count(name) ad n,min(id) as m from table_name group by name having n>1) a );
5. exists和in，以及not exists和not in的使用场景和区别
exists和in ，如果子表小，外表大，则使用in；如果外表小，子表大，怎使用exists；exists 和in在外表中都可以用到索引
not exists 和not in：not exists的效率高于not in，not in的内外表都需要全表扫描，没有用到索引，而not exists的子查询依然能用到表上的索引
6. 请说一下数据库事务、主键与外键的区别？
数据库事务：即用户定义的一个数据库操作序列，这些操作要么全做、要么全不做，是一个不可分割的工作单元，它具有四大特性，A-原子性，C
-一致性，I-隔离性，D-持续性。
主键和外键的区别：主键时能确定一条记录的唯一标识，比如，一条记录包含身份证号、姓名、性别，身份证号就是主键；外键用于与另一张表的关联，能确认另一张表记录的字段，用于保持数据的一致性。