MySQL知识点总结：

     总结MySQL面试中常见的知识点以及常考的sql语句：

 

一、MySQL索引：

 

1.索引分类及创建：

        索引是帮助MySQL高效获取数据的数据结构，可分为：

        1.普通索引（经常出现在WHERE或ORDER BY后的单一列上使用，可以重复，由关键字KEY或INDEX定义的索引）、

CREATE INDEX index_name ON table(column(length))；

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

INDEX(id)   //建表时

    删除：

DROP INDEX index_name ON table

        2.唯一索引  （索引列中的值必须是唯一的，但是允许为空值由关键字UNIQUE定义的索引）、

CREATE UNIQUE INDEX indexName ON mytable(username(length))

        3.主键索引（是一种特殊的唯一索引，不允许有空值，使用PRIMARY，创建表时候建立）、

        4.组合索引（在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合）、

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

        5.全文索引（在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，只有在MyISAM 引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，创建表的时候适合使用）。

       例如：`content` text字段，在建表时添加： FULLTEXT (content)

       优势：①可以保证数据库表中每一行数据的唯一性；②可以大大加快数据的检索速度；③可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；④在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；⑤通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

      劣势：增加了数据库的存储空间；在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

      不需要创建索引的情况：1).表记录太少2).经常增删改的表；

 

2.Btree索引与Hash索引：

        最常见的索引是Btree索引和Hash索引。不同的引擎对于索引有不同的支持：Innodb和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引。

       Btree索引：以B+树为存储结构实现的。Btree索引的存储结构在Innodb和MyISAM中有很大区别。①MyISAM的索引方式为“非聚簇”，叶结点的data域存放的是数据记录的地址，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。②Innodb的索引方式为“聚簇索引”，叶结点的data域保存了完整的数据记录，按照主键进行聚集。

        Btree索引与Hash索引对比：①Hash 索引的查询效率要远高于 B-Tree 索引。Hash 索引的检索可以一次定位， BTree索引需要从根节点到叶子节点这样多次的IO访问。②Hash 索引有很多弊端：1)仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询；2)无法被用来避免数据的排序操作；3)不能利用部分索引键查询；4)在任何时候都不能避免表扫描；5)遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

         索引优化：①最左前缀匹配原理；（如果建立title 和time的组合索引，查询title和time、或者查询title的时候可以使用，但是在查询time的时候会失效；a,b,c索引对于使用a,ab,abc会有效，其他都无效）②表的主键、外键必须有索引；③ 对where、on、group by、order by中出现的列使用索引；④ 尽量选择区分度高的列作为索引；⑤对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键；⑥不要在比较运算符左侧使用函数或进行计算。

         避免在where子句中 进行null值的判断、使用!=或<>操作符、使用or来连接条件、对字段进行函数操作。（https://blog.csdn.net/suifeng3051/article/details/52669644）

         索引失效：1.如果条件中有or，即使其中有条件带索引也不会使用；2.对于多列索引，不是使用的第一部分，则不会使用索引；3.like查询是以%开头；4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；5.is null 索引失效；等

 

3.MySQL存储引擎：

         MySQL有多种存储引擎，常用的有：MyISAM、InnoDB、MERGE、MEMORY。其中，Memory存储引擎使用存在于内存中的内容来创建表。默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。 Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

        MyISAM和InnoDB区别：①InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；②InnoDB支持外键，而MyISAM不支持。③ InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。④Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；5.InnoDB 支持行级别锁定，MyISAM 支持表级别锁定，

 

4. B树、B+树、B*树：

      ①B树（B-tree）：为了提高磁盘或外部存储设备查找效率而产生的一种多路平衡查找树，一个m阶的B树满足：树中每个结点至多有m棵子树；除根结点外，每个分支结点至少有ceil(m/2)个子树；根结点至少有两颗子树；有J个孩子的非叶子结点恰好有J-1个关键码，关键码递增；对于一颗含有N个总关键字的m阶B树最大高度为log_ceil(m/2)[(N+1)/2]+1，如下：

        ②B+树为B树的变形结构，用于大多数数据库或文件系统的存储而设计。为叶子结点增加链表指针；每一个父节点的元素都出现在子节点中，是子节点的最大或者最小元素，故而所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；【优势：单一节点存储更多的元素，使得查询的IO次数减少；所有查询都要查到叶子节点，查询性能更稳定；所有叶子节点形成有序链表，便于范围查询】 。图解详见：https://www.sohu.com/a/156886901_479559

         ③B*树在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3。

 

5.事务：

        事务的ACID特性:①原子性（Atomicity）事务是一个原子操作，由一系列动作组成。事务的原子性确保动作要么全部完成，要么完全不起作用。②一致性（Consistency）事务在完成时，所有的数据都保持一致状态。③隔离性（Isolation）并发事务执行之间无影响，在一个事务内部的操作对其他事务是不产生影响。④持久性（Durability）一旦事务完成，数据库的改变必须是持久化的。

        数据库管理系统采用日志来保证事务的原子性、一致性、持久性。【将所有对数据的更新操作都写入日志，如果一个事务中的一部分操作已经成功，但以后的操作，由于断电/系统崩溃/其它的软硬件错误而无法继续，则通过回溯日志，将已经执行成功的操作撤销，从而达到“全部操作失败”的目的。】采用锁机制来实现事务的隔离性。【采用悲观锁，即当前事务将所有涉及操作的对象加锁，操作完成后释放给其他对象使用；使用乐观锁，使得不同事务可以同时看到同一对象的不同历史版本】

          事务并发所可能存在的问题：1.脏读：一个事务读到另一个事务未提交的更新数据。2.不可重复读：一个事务两次读同一行数据，但读到的数据不一样。3.幻读：一个事务执行两次查询，但第二次比第一次查询多出一些数据行。4.丢失更新：撤消一个事务时，把其它事务已提交的更新的数据覆盖了。

          事务隔离级别：①TransactionDefinition.ISOLATION_DEFAULT：这是默认值，表示使用底层数据库的默认隔离级别②.ISOLATION_READ_UNCOMMITTED：一个事务可以读取另一个事务修改但还没有提交的数据。该级别不能防止脏读和不可重复读。③.ISOLATION_READ_COMMITTED：一个事务只能读取另一个事务已经提交的数据。可以防止脏读，大多数情况的推荐值。（oracle数据库支持）④.ISOLATION_REPEATABLE_READ：一个事务在整个过程中可多次重复执行某个查询，并且每次返回的记录都相同。即使在多次查询之间有新增的数据满足该查询，这些新增的记录也会被忽略。可以防止脏读和不可重复读。（mysql默认的事务处理级别）⑤.ISOLATION_SERIALIZABLE：所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，该级别可以防止脏读、不可重复读以及幻读。

           事务的传播行为：如果在开始当前事务之前，一个事务上下文已经存在，此时有若干选项可以指定一个事务性方法的执行行为。（7种）1. PROPAGATION_REQUIRED--支持当前事务，如果当前没有事务，就新建一个事务。这是最常见的选择。2.PROPAGATION_SUPPORTS--支持当前事务，如果当前没有事务，就以非事务方式执行。 3.PROPAGATION_MANDATORY--支持当前事务，如果当前没有事务，就抛出异常。 4.PROPAGATION_REQUIRES_NEW--新建事务，如果当前存在事务，把当前事务挂起。5.PROPAGATION_NOT_SUPPORTED--以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。 6.PROPAGATION_NEVER--以非事务方式执行，如果当前存在事务，则抛出异常。7.PROPAGATION_NESTED：如果当前存在事务，则创建一个事务作为当前事务的嵌套事务来运行；如果当前没有事务，则该取值等价于.PROPAGATION_REQUIRED。

 

6.数据库锁：处理并发问题

         1) 读锁（S锁）：执行select操作时使用的锁机制，多个事务可封锁一个共享页，任何事物都不能修改该页，通常是该页被读取完毕，共享锁立即被释放；

         2) 写锁（X锁）：执行update，delete等对数据有修改操作时使用的锁，仅允许一个事务封锁此页，其他任何事务必须等到锁被释放才能对该页进行访问；

        3) 更新锁：在修改操作的初始化阶段用来锁定可能要被修改的资源，避免使用共享锁造成的死锁现象【使用共享锁更改数据时，首先获得一个共享锁，读取数据，然后将共享锁升级为排它锁，再执行修改操作，可能会出现在修改数据时事务都不会释放共享锁而是一直等待对方释放，造成死锁】

         乐观锁与悲观锁是为了保持事务的隔离性以及数据库的一致性的一种手段,并不属于数据库锁机制，但是悲观锁是依赖于数据库的锁机制实现的，乐观锁是基于数据版本记录机制来实现的。

         乐观锁（OOC）：在表中添加一个版本号version字段，当有一个事务对该数据进行操作时会取得版本号，当事务对数据更新时会判断数据库中的version是否跟自己取得version一致，一致则说明该数据未被修改过，否则说明数据被修改过，更新操作失败。更新成功则对version字段加一。适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

         悲观锁（PCC）：行锁、表锁、读锁、写锁等，都是在操作之前先上锁。

 

7.数据库主键,外键的作用

       主键作用:能保证设置主键的列非空且唯一.另外,在定义主键时,如果这列之前没有索引,系统会为其创建唯一性索引

  外键作用: 主要是用来控制数据库中的数据完整性的，当对一个表的数据进行操作时，和他有关联的一个表或多个表的数据能够同时发生改变　

 

8.数据库三范式：

         第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式

        第二范式（2NF）：要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。第二范式就是非主属性非部分依赖于主关键字。

        第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

 

9.数据库安全问题：

         sql注入：比较常见的网络攻击方式之一，针对程序员编程时的疏忽，通过SQL语句，实现无帐号登录，甚至篡改数据库。例如：可以这样输入实现免帐号登录：

       用户名： ‘or 1 = 1 --

       密 码：

       当输入了上面的用户名和密码，上面的SQL语句变成：

                SELECT * FROM user_table WHERE username='’or 1 = 1 -- and password='’

       条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功；然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都能正确执行，用户轻易骗过系统，获取合法身份。

       应对方法：

        1. 不要使用动态拼装SQL，可以使用参数化的SQL或者直接使用存储过程进行数据查询存取,通过setInt，setString，setBoolean传入参数

       2. 永远不要信任用户的输入，要对用户的输入进行校验，可以通过正则表达式，或限制长度，对单引号和双"-"进行转换等。

       3. 不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

       4. 不要把机密信息明文存放，请加密或者hash掉密码和敏感的信息。

       5. sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检查工具 jsky (可进行网站漏洞扫描),网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT_IPS可以有效的防御SQL注入，XSS攻击等。

 

10.慢查询：

    MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句：

        1，slow_query_log：这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

        2，long_query_time：当SQL语句执行时间超过此数值时，就会被记录到日 志中，建议设置为1或者更短。

        3，slow_query_log_file：记录日志的文件名。

  检测mysql中sql语句的效率的方法：

        1. 通过查询日志：Windows下开启MySQL慢查询，MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上：

        log-slow-queries = F:/MySQL/log/mysqlslowquery.log

        long_query_time = 2

      2.show processlist 命令：显示哪些线程正在运行

       查询到效率低的 SQL 语句 后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序，

 

11.MySql的Delete、Truncate、Drop分析

https://www.cnblogs.com/zjfjava/p/5991613.html

相同点：

　　truncate 和不带 where 子句的 delete，以及 drop 都会删除表内的数据

不同点:

　　1. truncate 和 delete 只删除数据不删除表的结构(定义)

　　drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

　　2. delete 语句是数据库操作语言(dml)，这操作会放到rollback segement 中，事务提交之后才生效;如果有相应的 trigger，执行的时候将被触发。truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

　　3. delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动，显然 drop 语句将表所占用的空间全部释放。truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage;truncate 会将高水线复位(回到最开始)。

　　4. 速度，一般来说: drop> truncate > delete

 

12.group by的使用:

    Group by根据一个或多个列对结果集进行分组。https://blog.csdn.net/omelon1/article/details/78813541

   （1）Group by语法可以根据给定数据列的每个成员对查询结果进行分组，这里的分组就是将一个“数据集合”划分成若干个“小块”，然后对这些“小块”进行数据处理。最终得到按一个分组汇总的结果表。一般和sum、max、avg等聚合函数一起使用。

     person表中含有name、dept、salary、delevel等列：（查找各部门工资的总和）

select dept,sum(salary) from person group by dept;

    （2）Group by子句和HAVING子句联合使用， 获得满足条件的分组返回结果

    （查找平均工资大于7000的部门的最高工资）

select dept,max(salary) from person group by dept having avg(salary) > 7000;

 

13.数据库join使用:

        数据库中join语句是指将多个表中的数据并联在一起，然后根据要求找出需要的数据。

        left join:即使右表中没有匹配数据，也要从左表返回所有行数据

        right join:即使左表没有关联语句，也要返回右表数据

        inner join :内连接，与join相同，关键字在表中存在至少一条数据，则返回所有行。

        full join 全连接：关键字在任何表中存在任一一条数据时，都会返回所有行

 

14.常考sql语句：

      其他见：https://www.cnblogs.com/qixuejia/p/3637735.html

1. 表格中有A, B两列，分别是日期和比赛的胜／负情况，要求展示每个日期的胜负个数。

//拆成两个表，t1记录胜的场数，t2记录负的场数：
	
select t1.date, t1.win, t2.lose from
(select A as 'date', count(B) as 'win' from T where B='win' group by A) as t1,
(select A as 'date', count(B) as 'lose' from T where B='lose' group by A) as t2
where t1.date=t2.date;

2. 学生表（t1,id），课程表（t2），成绩表(t3，id，cource，score)，一条sql写出每门课程的及格和不及格的学生数量。

r1记录每门课程几个的学生数，r2记录不及格的学生数

select r1.cource, r2.cource from

(select count(t3.id) from t3 where t3.score >'60' group by t3.cource) as r1 ,

(select count(t3.id) from t3 where t3.score <'60' group by t3.cource) as r2

where r1.cource=r2.cource

3.餐馆菜单输出最贵的五道菜 ：

select * from restaurant order by food desc limit 5;

4.学校中有一个年级表，有两个核心字段，student_id和class_id，把年级中大于30人的班级查出来？

select class_id from table group by class_id having count(student_id)>30;

5.有个表，计算班级每个科目的平均分:

select 班级,avg(语文),avg(数学) from 表名 group by 班级

6. 获取每个用户当天的订单量 :

select 用户id,count(1) as 订单数量 from 订单表 group by 用户id

7.一个成绩表，其中存放了学生信息和成绩信息，三列 id，subject，grade其中subject就是课程的id，如何将学生按总分排序？

select id，subject，sum(grade) from table group by id order by sum(grade) desc;

8.学生成绩表，求每个学科的最多最少分数和平均分:

select cource,max(grade), min(grade),avg(grade) from table group by cource ;

9. 订单表结构为 (用户id，商品id)，写一个sql语句，查询购买商品数最多的前十个用户；

SELECT user_id,COUNT(product_id) FROM order_list GROUP BY user_id ORDER BY product_id LIMIT 0,10

10. 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

select distinct salary from salaries where to_date='9999-01-01' order by salary desc;