20191109 mysql面试总结

分库原则： 数据库设计的一个原则就是：一个库里的表越少越好，一张表里的字段越少越好。如果一个页面只查询一张表，不涉及到多表连接，那么无论放在哪个库里都可以，那就建议分库。否则就要跨表跨库查询，那真是噩梦！

1）一般不考虑分库，保持数据库逻辑上的完整。同一个功能的数据表放在一个库里面。

2）如果你同一个表中记录过多，则可以考虑用分表来提高查询效率。

建表原则

1）建数据表使用编码方式utf8mb4，要不然imoji表情存不进去，建表时考虑存储imoji表情？默认字符编码为utf8(或者utf8mb4)，默认存储引擎为INNODB；

2）唯一索引。唯一索引只能存在一个null。如果存在多个null值，不能添加唯一索引，必须要把null值删除。

3）选择varchar类型时，长度的考虑。数据类型必须要使用文本类型才行，要不然存不了500个字符。

4）表、列名必须有注释；

5）命名必须规范，由数字、字母和_组成，不能使用空格，不能使用关键字TYPE、STATUS等；命名长度不超过20；

6）六个保留字段。字段无用，浪费存储空间。存储空间尽可能的占用的比较少。查询时判断当前用户是否有效 delete_flag=1,一定不能忘记。

强制索引  (是否需要回表查询，回表扫描行数不同)

Mysql执行计划不走我想要的那个复合索引，怎么处理？用force index语法，让其强制走某个索引。查询（扫描）了多少行? 走这个索引查询了多少行？ 走哪个索引查询了多少条？强制使用某个索引。

索引建了好几个复合索引，因为还有很多其他查询场景。用该语句查询的条数为几千条，而阿里云上显示的扫描的却有几十万行，用执行计划分析，它并没有走我想要的索引。

就算你建立了复合索引，也不一定会走；查看执行计划，当前sql执行要扫描多少行记录？全表扫描：扫描了所有的行。

单值索引、复合索引、覆盖索引的区别

索引列为一列的情况,即新建索引的语句只实施在一列上；

复合索引: 在多个列上建立索引；复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引; 

为什么要使用联合索引？

1、减少磁盘空间开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

2、提升性能，覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表查询，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

3、效率高。索引列越多，通过索引筛选出的数据越少。如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

多版本并发控制 MVCC

Multi-Version Concurrency Control,翻译为中文即 多版本并发控制。乐观锁的一种实现方式。在Java编程中，如果把乐观锁看成一个接口，MVCC便是这个接口的一个实现类而已。每行都有版本号，保存时根据版本号决定是否成功。

LBCC，即基于锁的并发控制（Lock-Based Concurrent Control），其实就是串行化，是四种隔离级别中级别最高的Serialize隔离级别。为了提出比LBCC更优越的并发性能方法，MVCC便应运而生。

数据库默认隔离级别：RR（Repeatable Read，可重复读），MVCC主要适用于Mysql的RC,RR隔离级别。

对Innodb引擎而言，由于Innodb是事务型的，所以会把load文件的整个操作（批量刷数据的操作）当作一个事务来处理，中途中断load操作，会导致回滚（事务还没提交，所以没有修改）。

事务隔离分为四个级别（隔离级别不同会造成不同的问题）事务与事务之间的关系

1、READ UNCOMMITTED。一个事务可以读另一个事务未提交的数据，是隔离级别最低的，也是基本没有应用的。脏读，不可重复读，幻读都会发生。

2、READ COMMITTED。一个事务只会读取另一个事务提交后的数据，不会再出现脏读，但是会出现不可重复读和幻读。

3、REPEATABLE READ。重复读，在开始读取事务的时候不允许另一个事务进行修改操作。会出现幻读。

4、SERIALIZABLE。序列化。所有事务进行串行处理，不会出现问题但是执行速度会很慢。

大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

脏读。一个事务读取了另一个事务未提交的数据，这个数据有可能会被回滚，是不存在的，工资变化。

不可重复读。一个事务在另一个事务提交前后读取数据时发现两次查询的结果不一样。

幻读。一个事务在另一个事务新增数据提交前后读取的数据条数不一样；两次读取到的工资数额不一样。

InnoDB存储引擎支持多粒度锁定，即表级锁和行级锁“同时”存在。且行级锁的数量不影响开销，因为使用的是位图标记的算法。

mysql锁大致分为以下几类：

表级锁（锁表）；页级锁（锁页）；行级锁（锁行）

意向锁（表锁和行锁的兼容）；插入意向锁（insert插入数据时专有锁）

元数据锁（基础锁）；自增锁（主键自增）

表级锁（锁粒度，并发程度）

比如MyISAM只有表级锁。表锁分【排它型X锁】和【共享型S锁】，MyISAM中称为：表共享锁和表独占写锁。表锁性能差，不利于并发更新记录，因此innodb引擎支持的行锁可以大大提高并发，但引入了死锁的问题：不同事务分别持有某一记录的锁不释放，同时申请对方持有的行锁。

表级别：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。

innodb中的锁可能会引发什么问题？

行级锁

1、排它锁X： select … from … where … for update。

同一时刻，只能有一个事务申请到X锁，其他的不管是读还是写，都需要排队，即堵塞其他的事务对X锁和S锁的申请。

2、共享锁S： select … from … where … lock in share mode

允许多个事务并发的申请S锁不堵塞，即可以并发读，不冲突。

3、R锁：记录锁，单个行记录上的锁（Record Lock）：单个单索引记录上的锁。

4、Gap锁：间隙锁

5、Next-key锁：简称NK锁，结合R锁和Gap锁，是innodb默认的行锁模式。

innodb默认有表锁和行锁；增删改会上锁，读不会上锁；表锁与行锁的区别：如果对整张表的数据进行增删改，就会锁表；如果修改指定的记录以及指定区间的记录，会锁行。

DML与DDL:数据操作语言和数据表定义语言(修改),只有在DML中才会开启事务。

比如你当前在navicat中执行的sql，是否会使用锁？

当前sql是否会锁表（行锁还是表锁？）InnoDB默认使用行锁。

InnoDB支持多种锁粒度，默认使用行锁，锁粒度最小，锁冲突发生的概率最低，支持的并发度也最高，但系统消耗成本也相对较高；锁冲突。

共享锁与排他锁是InnoDB实现的两种标准的行锁

---

死锁

死锁是指两个或两个以上的事务在执行的过程中，因争夺锁资源而造成相互等待的现象。若无外力的作用，事务将无法推进下去。死锁出现的概率是非常低的。解决死锁问题最简单的一种方式是超时，即当两个事务相互等待时，当一个等待时间超过某个值时，其中一个事务进行回滚，另一个等待事务就能继续进行。InnoDB可通过参数innodb_lock_wait_timeout来设置超时的时间。

Mysql死锁问题

Lock wait timeout exceeded; try restarting transaction。

锁机制是为了解决事务并发问题才出现的；

1、事务的四种隔离级别；

a.查看当前会话隔离级别 select @@tx_isolation;

b.查看系统当前隔离级别 select @@global.tx_isolation; REPEATABLE-READ； MySQL默认的事务隔离级别就是REPEATABLE-READ。

2、查询数据库线程情况，看是否有执行很慢的SQL？(mysql是多线程的，redis是单线程的)

SHOW FULL PROCESSLIST; 查询当前mysql的线程状况。

3、再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。 当前事务所在的线程id：trx_mysql_thread_id。

现场环境客户要求删数据（界面没法直接操作），于是直接在数据库进行查询删除了，删完发现界面依然能查到删除后的数据，又用sql语句进行删除，发现报了错：Lock wait timeout exceeded; try restarting transaction（锁等待超时）。上网查了一波发现是删除后没有提交事务造成的（当前事务在等待其它事务释放锁资源造成的，没有获得锁就不能访问资源）。所以在此纪录下解决方法：

方法一：在mysql查询中执行以下指令

select * from information_schema.innodb_trx，会查询到正在运行的事务

kill trx_mysql_thread_id，然后在查询中kill掉就行

方法二：修改配置文件

innodb_lock_wait_timeout 锁定等待时间改大，否则就会造成所等待超时。

my.ini文件：

#innodb_lock_wait_timeout = 50 修改为 innodb_lock_wait_timeout = 500

这个错误是在一个事务没有提交的时候，其他事务也操作相同对象导致的，那么找到了问题的原因，我们就可以针对这种情况进行修改了。

多个事务操作同一个对象，对资源进行上锁。

事务----->锁----->共享资源(某一个共享资源)。

重点1、对共享资源上锁；2 事务首先获取到锁，才能操作共享资源。如果不是对同一个资源操作不会出现争抢锁的情况。

为什么出现死锁问题？ 当前事务没提交，一直没有释放锁资源，其他事务获取不到当前资源的锁导致的。

---

索引失效

1.单列索引无法储null值，复合索引无法储全为null的值。

2.查询时，采用is null条件时( is null 或者 is not null )，不能利用到索引，只能全表扫描。

原因：为什么索引列无法存储Null值？索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。将索引列值进行建树，其中必然涉及到诸多的比较操作，null 值是不确定值，无法比较，无法确定null出现在索引树的叶子节点位置。

3.使用不等于（！= 或者<>）不能使用索引.

4.like查询是以%开头.表达式、函数计算、子查询等都会导致索引失效

在if判断时使用了这么一句：“start_times ！= ''”和“end_times != '' ”，这两个判断使mapper试着去将start_times和end_times转换成String类型然后与“ ''”进行比较，然后报类型转换出错。 转化成字符串进行比较，但是本身是时间类型。

-- 删除字段名

ALTER TABLE 表名DROP 字段名 

-- 修改字段名

ALTER TABLE 表名 CHANGE 旧字段名  新字段名

-- 修改字段类型

ALTER TABLE 表名 MODIFY 字段名 字段类型(字段长度)

-- 如果表存在就删除表；

drop table if exists

删除索引：DROP INDEX index_name ON talbe_name

商品SKU表怎么设计？

1.同一商品不同SKU库存和售价不同.

2.不同类型的商品具有不同的属性名和属性值(如汽车和服饰),所以属性需要支持后期添加和维护.

3.在某个商品分类下通过属性筛选商品.

商品表在分类表的下面：男装是一个分类，裤子这种商品属于男装这个分类。商品表中会存储商品分类编号。

SKU表挂在商品表下面，sku表中包含商品编号，SKU编号，商品库存数量，SKU属性等信息。

属性名称表:属性名编号, 属性名, 商品分类编号, 父属性编号.

属性值表: (属性值编号, 属性值, 属性名编号)

商品和属性关系表：一个商品可以对应多个属性。

自增编号, 商品编号, 属性名编号, 属性值编号。

商品信息表，商品扩展表；

1、去掉商品服务中的销售总数字段；如果需要显示销售总数，通过库存表进行计算。
2、增加商品图片表，并且能够对图片进行排序，满足多张商品图片显示的需要。
3、商品表：针对优惠价设置过期时间字段。
4、增加库存变更记录表：记录商品库存的变化。
5、商品表：设置是否是组合商品属性；添加组合商品表，建立组合商品和独立商品的关联。
6、商品记录表：设置哪些字段不能被修改。

如何在mysql上安装数据库，如何查看以及修改数据库的配置文件？

max_binlog_cache_size：日志文件能够使用的最大cache内存大小。

当执行多语句事务时，max_binlog_cache_size如果不够大，系统可能会报出“Multi-statement；

Data too long for column 'mobile_phone' at row 24668 列的长度太短，导致数据插入不进来；修改列的长度即可。

插入不成功的原因是什么？长度超过限制了，把sql拷贝出来执行一遍看看报什么错？ 解决问题思路。

刷数据的时候数据脚本，尽量通过字符串占位符自动生成。

分批进行更新数据，共两百万条数据，每次处理50万条。

如何批量插入？insert into 表名(字段名)...select...

1) 表后面的字段要和select后面的字段一一对应；

2) id自增，所以不能全量插入。

3) 判断查询的字段是否为空，如果为空怎么处理,IFNULL(字段，默认值)

4) 批量执行过程中是否会报max_binlog_cache_size溢出问题？如果报了是否需要分批插入（一次性插入几十万条数据）。

5) 按照特征批量刷数据。比如电话号码最后一位进行刷数据。

6) 删除账户id ,如何清空数据 update p_doctor set acct_id=null,把某一列的值全部设置为null.

7) select * from p_doctor表是否会导致客户端OOM ?不是数据库内存溢出。

8) 刷账户id的时候必须进行排序。否则范围内的数据可能不准。

9) between...and...在某个范围之间处理数据

10) where子句应该写在set子句后面

11) 批量刷数据，主从之间数据延迟怎么处理？可能会出生产事故. 从库大量的脚本要执行。

如何获取最后一条记录的id？ 通过limit实现

limit n，检索多少行记录？ 从1开始

limit m，n; 检索n条记录，从第m条开始。 检索1条记录，从第50万条记录开始。

show full processlist

显示有哪些线程在运行，可以检查当前数据库的运行状态。

id： 一个标识

user： 显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。

host： 显示这个语句是从哪个ip的哪个端口上发出的。

db： 显示这个线程程目前连接的数据库。

command：显示当前连接的执行的命令，一般就是休眠sleep，查询query，连接connect。

time： 此这个状态持续的时间，单位是秒。

state： 显示使用当前连接的sql语句的状态，只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。

info： 显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

State对sql命令的底层执行很有好处(一个sql语句完整的执行过程)。

3、mybatis在Mapper的xml文件中的转义字符的处理

4、对mybatis非空判断的理解：空的就不进行存储，只存储非空字段。不会存储一个空字符串进去

5、mybatis 没有写返回值类型 resultType="java.lang.Long" 导致没有返回值。

6、字段的数量不能匹配值的数量，导致执行sql语句报错。前后列数不等造成的，

Column count doesn't match value count at row 1

7、日志表进行表结构的复制: MySQL完整复制表到另一个新表

1)复制表结构 CREATE TABLE newuser LIKE user; (表名)

2)导入数据 INSERT INTO newauser SELECT * FROM user;

Mysql内置函数

执行截取数据SUBSTRING(索引从1开始)

SELECT SUBSTRING(CardID, -3) FROM users 取CardID字段后三位字符  

SELECT SUBSTRING(CardID, 3) FROM users 从左开始第3位取（包括第三位） 

SELECT SUBSTRING(CardID, 1,8) FROM users  从第1位开始取8位

Mysql中的information_schema数据库

总结：记录大小，数据长度(数据库大小)，索引长度(大小)；

表数据所占用空间大小，表的字段个数，记录条数，索引大小。

information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。

什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

在MySQL中，把【INFORMATION_SCHEMA】 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。

在【INFORMATION_SCHEMA 】中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

information_schema是只读表，不能修改！(只能读不能修改其中的信息)

information_schema数据库表说明:

TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。某一张表的信息。

mysql和oracle中使用commit的区别 

1.Mysql: Mysql中默认在进行DML操作时,是隐式提交事务

2.Oracle: Oracle中在进行DML操作时,需要显示提交事务.

select STR_TO_DATE('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s') str转成date类型,时间可以比较大小；

select DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

select DATE_DIFF(NOW(),NOW()) as result; 取两个时间之间的差值

STR_TO_DATE(str,format)函数是将时间格式的字符串（str），按照所提供的显示格式（format）转换为DATETIME类型的值。

DATE_FORMAT(date,format)函数则是把数据库的日期转换为对应的字符串格式。

动态sql中的标签<where,sql,include,if等等>

动态sql中的where标签，自动去除and或者or；

删除表中的数据

truncate [ˈtrʌŋkeɪt]

删除表信息的方式有两种 :

truncate table table_name;

delete * from table_name;  delete from table_name;

注 : truncate操作中的table可以省略，delete操作中的*可以省略

truncate、delete 清空表数据的区别 :

1> truncate 是整体删除 (速度较快)，delete是逐条删除 (速度较慢)

2> truncate 不写服务器log，delete写服务器log，也就是 truncate 效率比 delete高的原因.

3> truncate 不激活trigger (触发器)，但是会重置Identity (标识列、自增字段)，相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。而 delete 删除以后，identity 依旧是接着被删除的最近的那一条记录ID加1后进行记录。如果只需删除表中的部分记录，只能使用 DELETE语句配合where条件。

1、通过手机号设置账号id，有些手机号多条记录，就会导致一对多的问题产生。

一个手机号对应多个账号id；无论如何一个有效手机号只能对应一条账号id；

一个手机号对应两个账号id； 不允许。

表与表之间的关联关系： 一对一还是一对多，一般情况下是一对多的关系。设计表的时候要考虑。

2、每次在生产环境执行脚本，都要进行保留字段的更新（id，更新时间）；更新无效记录的acct_id就忘记更新保留字段了。

并且要进行校验数据的正确性。select与update分开处理，并且把任务开到tapd上；

待执行的sql需要另外一个人进行评审。

3、创建数据表的时候就创建索引，要不然查询特别慢，不要考虑节省空间的问题。

create index idx_account_info_mobile on account_info(mobile_phone,password);

show index from account_info;

比如通过手机号查询用户信息，手机号没创建索引，就特别慢；以及登录操作。

4、删除项目里面的账户id，重新生成； 不能这样搞，导致acct_id找不到；进而程序报错。

账号id和原来的不一样了，但是缓存中使用的还是原来的。生产环境：无论如何一定不能删除数据。

5、select * from account_info ORDER BY id desc limit 1000 排序，先是id列，后面是排序规则。

6、为什么更新数据sql执行的特别慢，不仅要更新数据，还要维护索引，所以特别慢。

7、EXPLAIN SELECT * FROM p_doctor LIMIT 10 不走索引查询id的前十条

EXPLAIN SELECT NAME FROM p_doctor LIMIT 10 走了索引的前十条记录  哪怕name字段的名称为空。

8、在什么情况下使用内连接，两张表之间存在主外键关系的时候；使用内连接查询。

9、不能在同一表中查询的数据作为同一表的更新数据。

SELECT * from (SELECT * FROM A ORDER BY time) a GROUP BY a.id;

在这里，我们首先对 A 表进行按照时间的顺序排序，这样我们可以把每个用户最后一次购买记

录排在最上面，排序之后再嵌套一层查询，这一层查询使用  GROUP BY 语句。在使用GROUP 

BY 语句的时候，他会按照分组将你排过序的数据的第一条取出来。

TIMESTAMP与DATETIME对比:

今天遇到一个问题，明明数据有更新，update_time字段却还停留在创建数据的时候。按常理来说这个字段应该是自动更新的才对。

1) 默认值：DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

这个区别就解释了为什么平时我们都不用可以管这个字段就能自动更新了，因为多数时候用的是timestamp；而此处用的是datetime，不会有自动更新当前时间的机制，所以需要在上层手动更新该字段。

2) 存储空间：DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。

3) 两者所能存储的时间范围不一样 

4) 表现形式:一个是时分秒，比较直观；一个是毫秒值，不太好转换。

数据库存储空间的理解：

1）数据库不断变大，导致磁盘可用存储空间不足。

2）查询慢，重建索引也比较慢。 尽量使用小的数据类型表示

3）如何节省数据库的存储空间？

为什么要使用连接池？

1、传统的数据库连接方式：一个连接对象对应一个物理连接，每次操作都打开一个物理连接，使用完都关闭连接，造成系统性能低下。

2、连接池技术：客户程序得到的连接对象是连接池中物理连接的一个句柄，调用连接对象的close()方法,物理连接并没有关闭,数据源的实现只是删除了客户程序中的连接对象和池中的连接对象之间的联系.数据库连接的建立及关闭是耗费系统资源的操作，在大型应用中对系统的性能影响尤为明显。为了能重复利用数据库连接对象,缩短请求的响应时间和提高服务器的性能,支持更多的客户，应采用连接池技术.

mysql中的null和空字符串判断，两者不一样；

没查到数据，要么数据不存在，要么sql有问题，特别是过滤条件。

当你在一个可以为NULL的列上使用IN或者NOT IN 子查询时，可以好好想想使用EXISTS或NOT EXISTS代替。

MySQL:索引工作原理，二分查找。

为什么需要索引（Why is it needed）?

但是对于一个有序字段，可以运用二分查找（Binary Search），这样只要访问log2 (N)的数据块。这就是为什么性能能得到本质上的提高。

mybatis中如何向sql中传递多个参数？使用Map集合或者pojo传递。

连接查询

1、当条件放在on后面时，无论on条件的真假，都会返回左表的值(左连接查询)；

2、当条件放在where后面时，只有满足条件的记录会返回。先过滤记录在返回。

update SET delete_flag=2,modified_id=1580052, modified_time=now() where id in (select id from (select ai.id from account_info ai 
INNER JOIN (select MIN(id) as id,mobile_phone from account_info where delete_flag=1 AND mobile_phone is not NULL group by mobile_phone having count(*) >1) inn 
on ai.mobile_phone=inn.mobile_phone 
and ai.id <> inn.id) ttt
)

Lost connection to MySQL server during query。

在查询过程中丢失连接到MySQL服务器。

造成这样的原因一般是sql操作的时间过长，或者是传送的数据太大(例如使用insert ... values的语句过长， 这种情况可以通过修改max_allowed_packed的配置参数来避免，也可以在程序中将数据分批插入)。

创建表过程中遇到了哪些问题？

1、搜索条件是模糊查询，所以在名称列不需要创建索引（索引失效）

2、表与表之间的关联关系，虚拟组表与虚拟组成员表之间的关系；

3、drop table if exists ecosystems_member; 如果表存在就删除表。

4、p_constants 项目中的常量表

搜索条件:手机号或者名称是.一个where条件即可解决。where (mobile=str or name=str)

如何查询某个列中重复出现的字段， 通过分组解决。通过名称进行分组获取重复的字段。

-- 创建表字段的时候，就需要考虑到是否需要唯一性约束，主键一般都具备唯一约束。

CREATE UNIQUE INDEX index_name ON table_name (column_list)

-- 字段长度大于某个值  select * from p_doctor where LENGTH(gaoxueya_password)>0

-- 不等于0  select * from p_doctor where gaoxueya_password = 0

-- 通过名称进行分组去重复

-- 对分组后的数据统计数量

-- 对分组后的数据进行排序，然后取出最大的10条

select name,count(id) from p_hospital group by name ORDER BY COUNT(id) DESC limit 10

count函数，以某个字段进行分组，就对某个字段进行聚合。 对分组的字段进行聚合。

锁和事务之间的关系。

当多个用户并发地存取数据时，在数据库中就可能会产生多个事务同时操作同一行数据的情况，若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据的一致性。

主从复制

生产环境大批量的刷数据，读写分离的情况下，会造成数据延迟么？数据延迟有多高？

从库从主库拉数据，一次性全部拉取完毕，再执行。

解析mysql中的auto_increment的问题

一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 。

答案：

如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。 

如果表的类型是InnoDB，那么是15。 InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。

SQL执行计划

1)是否需要回表查询？

select_type查询类型: 主要是用于区别普通查询、联合查询、子查询等的复杂查询。(查询类型与查询级别)

type查询类型:const级别

可能使用到的索引：和查询条件相关的索引都会被列出来。

实际使用的索引，如果为NULL，则没有使用索引

Using index表示的select操作中使用了覆盖索引（Covering Index），避免回表访问数据，效率不错。（通过索引即可找到对应的数据，避免访问表的数据行）。如果索引中不在，就会在表中查询。(这个理念 我都忘记了)以后查询尽量使用覆盖索引，别回表。

是否需要回表查询？Extra是补充说明

MySQL的架构分成了server层和存储引擎层（storage engine），server层通过调用存储引擎层来返回数据。其中Using index表示覆盖索引即可满足查询要求，因而无需再回表查询，因而效率较高.例如：select id from test where id = 5;其中id为主键。 在Server层再通过where语句对检索结果进行过滤。

Using where本身其实和是否使用索引无关，它表示的是Server层对存储引擎层返回的数据所做的过滤。当然该过滤可能会回表，也可能不会回表，取决于查询字段是否被索引覆盖。

MySQL服务器访问存储引擎，存储引擎访问索引，或者存储引擎访问基表。

怎么避免回表？将需要的字段放在索引中去。查询的时候就能避免回表。

是否需要回表查询？using index，using where

-- 返回所有包含此字符串的记录（较为精确的模糊搜索）

select * from p_doctor where INSTR(name,'王')>0 11.668s

select * from p_doctor where name like '%王%' 11.922s

select * from p_doctor where find_in_set('王',name) 10.803s

这几个函数都不能使用索引，必须要全表扫描。

INSTR()内置函数，可代替传统的like方式查询，并且速度更快。

find_in_set()mysql用find_in_set函数代替like搜索提高性能

查询某个字段中，带某个字符串的记录。

尽量少用like，性能查，查询速度慢。

-- 逗号分隔，COMMENT写错，关键字VARCHAR写错，DATETIME类型。
-- 5who保留字段
-- 关键字全部大写
-- tinyint的使用，使用合适的数据类型，为列选择合适的数据类型。

-- 设计表的时候，就设计好索引
-- 同一纬度的数据，数据的纬度划分
-- 用户登陆日志表应该包含哪些字段？ 网上可以搜得到
-- 要理解业务才能定义模型属性，模型有哪些属性，根据需求文档进行设计。
-- 一张表如何与另外一张表进行关联？
-- 是否要考虑冗余设计(暂时先不用)？

1)数据类型小写，后面的全部大写。

2)最后一个字段是没有逗号分隔的。

3)详细设计里面展示：Navicat生成的DDL可读性更好。

4)设计表的时候创建索引和主键。key (索引名称) 与 primary key (主键列名)

5)bigint(20) 只能输入20位数字。

6)如何设定有符号还是无符号的数据？ 默认是无符号的，有符号的使用unsigned关键字。

7)选择合适的数据类型：tinyint，smallint，int，bigint

8)编码集的设置，数据库引擎的设置，表注释

ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

手机号不能明文存储，存储加密后的秘文。

默认字符串为'' 空 default ''; 是否要给定默认值？

如何评价一个数据库设计的好坏？

良好的数据库设计：能节省数据库的存储空间、能够保证数据的完整性、方便进行数据库应用系统的开发；

糟糕的数据库设计：会产生数据冗余、浪费存储空间、浪费内存空间、可能导致数据更新和插入的异常。

表设计，添加表字段

哪些设计的不合理？

根据业务去设计数据表字段

p_doctor从技术和业务的角度都有问题。 表中数据越来越多，表宽问题，字段越来越多。

Mysql难道没有long类型？

Java中Long型数据类型对应MySQL数据库中哪种类型？

Long型id数据对应MySQL数据库中 bigint 数据类型.

使用Navicat快速生成数据库字典。

数据库字典，数据库表中每个字段的定义以及注释。

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT 
from information_schema.columns 
where TABLE_SCHEMA='awb'

如何记录用户信息变更日志？

用户信息表

用户信息变更记录表

两张表字段都相同，每次更新完用户信息后，取出来插入到用户信息变更记录表中。

如果要拆表，要考虑哪些字段？

哪些表的体量将来会很大，日志表？

1、表名一般以【模块名称_具体表名】来实现，同一个模块的前缀是一样的。

2、表名称不应该取得太长，一般不超过三个英文单词，使用英文是基本的要求。

3、不使用tab或tb作为表前缀（本来就是一个表，为什么还要说明）

4、当系统中有一些少量的，重复出现的值时，使用字典表来节约存储空间和优化查询。如地区、系统中用户类型的代号等。这类值不会在程序的运行期变化，但是需要存储在数据库中。一般数据库中，都有一个数据字典表，用来保存系统所用到的基础数据，大型的字段表如省份城市区域的字典表，统一以Dictionary_作为前缀。数据字典表，比如用户分类。

5、在命名表时，用单数形式表示名称。例如，使用Employee，而不是Employees。

6、数据库中应建立这样一个表，就是数据库本身的字段信息，表的说明，也就是数据库设计文档的一个表，方便查询使用，有什么不明的可以直接从数据库查询，数据库文档丢失，注释丢失，都可以重新起作用。

7、基本表及其字段之间的关系,应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准，适当增加冗余，达到以空间换时间的目的。

8、操作日志表，登录日志表，这是数据库中必备的两个表，这个记录也需要做进一步的保存。这个有两种情形，一是具体到单个字段的操作日志，二是整个表的操作日志。

操作日志表与登录日志表。

/* 操作日志表 */
CREATE TABLE p_operate_log(
 id bigint not null auto_increment COMMENT '日志编号id',
 ol_uid int NOT NULL COMMENT '操作人id',
 ol_name varchar(20) NOT NULL COMMENT '操作人姓名',
 ol_terminal_type VARCHAR(255) DEFAULT '' COMMENT '终端类型：saaa,admin,app,wechat，通过哪种方式进行操作的',
 ol_type tinyint not null COMMENT '操作类型:1表示查询，2表示增加,3表示修改,4表示删除',
 ol_module tinyint not null COMMENT '操作模块:',
 ol_content varchar(255) NOT NULL COMMENT '操作内容，越具体越好',
 ol_time datetime NOT NULL COMMENT '操作时间',
 ol_ip varchar(20) COMMENT '操作人ip',
 ol_remarks varchar(255) COMMENT '备注信息',
 PRIMARY KEY(id),
 KEY index_p_operate_log_uid(ol_uid)
) ENGINE=INNODB default CHARSET=UTF8 COMMENT='操作记录表';

/* 用户登陆日志表 */
CREATE TABLE p_login_log(
 id int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
 acct_id int(11) NOT NULL COMMENT '账号id',
 acct_name VARCHAR(10) COMMENT '用户名',
 login_time DATETIME COMMENT '用户登录时间',
 login_from tinyint NOT NULL COMMENT '用户登录来源：1表示安卓，2表示ios，3表示web，4表示H5登录',
 login_type tinyint NOT NULL COMMENT '用户登录方式：1表示验证码登录，2表示密码登录，3表示微信登录',
 login_ip VARCHAR(15) COMMENT '用户登录ip',
 login_platform tinyint NOT NULL comment '平台来源:1表示云鹊医产品系，2表示云鹊健康产品系',
 login_status varchar(10) NOT NULL comment '登陆状态:成功,失败',
 create_id INT NOT NULL COMMENT '创建人id',
 create_time datetime NOT NULL COMMENT '创建时间',
 modify_id INT NOT NULL COMMENT '修改人id',
 modify_time datetime NOT NULL COMMENT '修改人id',
 delete_flag tinyint DEFAULT 1 NOT NULL COMMENT '删除标记,1表示有效，2表示删除',
 PRIMARY KEY(id),
 key index_user_name(acct_name),
 KEY index_account_id(acct_id)
) ENGINE=INNODB DEFAULT CHARSET=UTF8 COMMENT '登录日志表';

drop table  p_login_log

/* 用户信息表 */
CREATE TABLE p_user_info(
 id int NOT NULL AUTO_INCREMENT COMMENT 'id',
 acct_id int NOT NULL COMMENT '账户id',
 nickname varchar(255) COMMENT '用户姓名',
 mobile_phone varchar(100) COMMENT '手机号',
 age tinyint unsigned DEFAULT 0 COMMENT '用户年龄',
 sex tinyint default 0 COMMENT '用户性别，0表示用户未输入,1表示男，2表示女',
 village_name varchar(255) COMMENT '村名称',
 village_id bigint(20) DEFAULT 0 COMMENT '村id',
 town_name varchar(255) COMMENT '乡镇名称',
 town_id bigint(20) DEFAULT 0 COMMENT '乡镇',
 county_name varchar(255) COMMENT '区县名称',
 county_id bigint(20) DEFAULT 0 COMMENT '区县',
 city_name varchar(64) COMMENT '市名称',
 city_id bigint(20) DEFAULT 0 COMMENT '市',
 province_name varchar(64) COMMENT '省名称',
 province_id bigint(20) DEFAULT 0 COMMENT '省',
 country bigint(20) DEFAULT 0 COMMENT '国家',
 head_img_url varchar(255) COMMENT '用户头像url',
 address varchar(255) COMMENT '详细地址',
 patient_address varchar(255) COMMENT '现住址拼接字段',
 birthday datetime COMMENT '出生日期',
 telephone varchar(36) COMMENT '固定电话',
 id_no varchar(100) COMMENT '身份证号码',
 email varchar(100) COMMENT '邮箱',
 nation varchar(255) COMMENT '民族',
 create_id int NOT NULL COMMENT '创建人id',
 create_time datetime NOT NULL COMMENT '创建时间',
 modify_id int NOT NULL COMMENT '修改人id',
 modify_time datetime NOT NULL COMMENT '修改人id',
 delete_flag tinyint DEFAULT 1 NOT NULL COMMENT '删除标记,1表示有效，2表示删除',
 PRIMARY KEY(id),
 key idx_p_user_info_acct(acct_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

数据库设计关系到数据的存储性能，还有开发人员对数据的操作都有莫大的关系。

建立科学的，规范的的数据库。

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性。

对第一范式的理解：

1、每一列属性都是不可再分的属性值，确保每一列的原子性。从业务角度考虑，是否有必要分割？

2、两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

地址----->省列、市列、详细地址列。

案例：

1）如果需求知道哪个省哪个市并按其分类，那么显然第一个表格是不容易满足需求的，也不符合第一范式。

2）显然第一个表结构不但不能满足足够多物品的要求，还会在物品少时产生冗余。也是不符合第一范式的。

对第二范式的理解：

数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

身份证号和订单号联系特别弱。 都和人有关系。不能部分依赖于主键。

对第三范式的理解：

确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。像：a-->b-->c  属性之间含有这样的关系，是不符合第三范式的。

比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

这样一个表结构，就存在上述关系。 学号-->所在院校 -->(院校地址，院校电话)这样的表结构，我们应该拆开来，如下：（学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

关系型数据库设计：三大范式的通俗理解 - 景寓6号 - 博客园