1)最左前缀匹配原则就是用到创建的联合索引中的最左侧的字段,索引即生效。
2)InnoDB和MyIsam
InnoDB:支持事务,支持数据库崩溃后的自动恢复机制,最主要是非常安全。表空间tablespace被用来存储表的内容。
MyIsam:三个文件表示一张表,格式文件(存储表结构的定义),数据文件(存储表行的内容),索引文件(存储表中的索引),可以被转换为压缩、只读表来节省空间。
3)优化数据库性能的方法:1、选取最适用的字段属性;2、使用连接来代替子查询;3、使用联合来代替手动创建的临时表。
4)如何定位慢查询:一般有3个思考方向 1.根据慢日志定位慢查询sql(
slow_query_log=on)。
2.使用explain等工具分析sql
执行计划。3.修改sql
或者尽量让sql
走索引。
5)mysql中表锁和行锁的区别是:所谓的锁就是同时进行俩个事务,是否允许两个事务同时对数据进行操作。表锁偏向myisam存储引擎,行锁偏向innodb存储引擎;表锁开销小,行锁开销大;表锁的锁粒度大,行锁的锁粒度小。
锁冲突:不允许两个事务同时对同一个数据上锁(上共享锁可以)。
死锁:例如说两个事务,事务A锁住了1~5行,同时事务B锁住了6~10行,此时事务A请求锁住6~10行,就会阻塞直到事务B施放6~10行的锁,而随后事务B又请求锁住1~5行,事务B也阻塞直到事务A释放1~5行的锁。死锁发生时,会产生Deadlock错误。
表锁:不会出现死锁,发生锁冲突机率高,并发低。
行锁:会出现死锁,发生锁冲突机率低,并发高。
行锁分为共享锁和排他锁:
共享锁:也叫读锁。当一个事务对某几行上读锁时,允许其他事务对这几行进行读操作,但不允许其进行写操作,也不允许其他事务给这几行上排它锁,但允许上读锁。
排他锁:写锁。当一个事务对某几个上写锁时,不允许其他事务写,但允许读。更不允许其他事务给这几行上任何锁。包括写锁。
上共享锁的写法:lock in share mode
例如: select math from zje where math>60 lock in share mode;
上排它锁的写法:for update
例如:select math from zje where math >60 for update;
6)什么是索引:索引是在数据库表的字段上添加的,是为了提高查询效率存在的一种机制,缩小扫描范围。
7)查询倒数第二个记录,select * from table_a order by rating desc limit 2, 1;
8)Hash索引和B+树索引区别是什么?哈希索引进行等值查询更快,但不能进行范围查询。哈希索引不支持使用索引进行排序。hash索引不支持模糊查询以及多列索引的最左前缀匹配。hash索引虽然在等值查询上较快,但是不稳定。性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差。而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低。
9)索引的适用场景及其优缺点:
索引的有点:提升效率,增加查询或者检索的速度。
索引的缺点:创建索引和维护索引需要耗费大量的时间。
索引会占用物理空间,数据量越大,所占用的空间越大
索引会降低增删改的效率,因为每次增删改都要对索引进行维护。
添加索引场景: 频繁查询的字段添加索引。 该字段很少DML操作。数据庞大的情况。
10)索引和主键的区别:主键是索引的一种,索引分为主键索引,唯一索引,联合索引。创建方式不同。主键索引一定是唯一性索引,唯一性索引不一定是主键索引。主键索引不可以为空,唯一性索引允许列为空。每个表只能有一个主键。
11)一条SQL语句查询速度很慢,可能的原因有哪些,怎么优化
创建适当的索引,对查询进行优化,对where和order by后面的字段创建索引,数据库的表字段特别多,将大字段剥离。减少跨库查询和多表联合查询。升级硬件,提高网速。
12)解决死锁:控制加锁顺序,不同的线程按照相同的顺序获得锁。加锁时限,在一定的时间请求锁失败,那就放弃请求并且释放自己的锁。死锁检测,未不同的线程设定优先级。
13)delete和drop的区别:delete用于删除数据,drop用于删除表结构。
14)乐观锁和悲观锁:乐观就是进行事物操作数据时,别人都不会操作,不会上锁。悲观锁就是每次操作数据,都有别人进行操作上锁。乐观锁适用于写少读多的场景。悲观锁适合写多的场景。
15)表和视图的区别:视图是已经编辑好的sql语句,表不是。视图没有实际的物理记录,表有。表是内容,视图是窗口。视图类似于函数方法,可以增强sql语句复用,可以引用。
16)数据库的存储过程:方法。存储过程就相当于一个方法,是你自定义的一个方法,里面定义了你需要操作的内容和步骤,当你调用这个存储过程时候就像调用函数一样,可以传递参数进去 然后就能执行事先写好的功能。
17)count(*)和count(1)的区别:两者都是统计表中总数据量。在InnoDB中二者效率差不多。count(*)是按照行统计这个数据非NULL的数量,count(1)是添加一列,列的值都为1,统计1的数量。没有主键count(1)比count(*)快。如果表只有一个字段count(*)最快。MyIsam中count(*)更快,因为里面有计数器。
18)数据库三大范式:
第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分。
第二范式:建立在第一范式基础上,要求所有非主键字段完全依赖主键,不要产生部分依赖。
第三范式:建立在第二范式基础上,要求所有非主键字段直接依赖主键,不产生传递依赖。
19)事务特性:
原子性:事务是最小的工作单元不可再分。
隔离性:A事务和B事务之间具有一定的隔离性。
一致性:所有事务要求在同一个事务中,所有操作必须同时成功,或者同时失败。
持久性:事务最终结束的一个保障。事务提交,就相当于将没有保存到硬盘上的数据保存到硬盘上。
20)事务的隔离性
读未提交(脏读):read uncommitted 最低的隔离级别
读已提交:read committed 事务A读取到事务B已经提交的数据(不可重复读取数据)。
可重复读(幻读):repeatable read 只要事务不结束读的就是一开始的数据。
序列化:serializable 事务不能并行,需要排队,最高的隔离级别。
21)如何防止SQL注入
1. 过滤输入内容,校验字符串
2. 参数化查询
3. 限制数据库权限