- 优化sql(sql语句优化、索引优化、表结构或设计优化、数据库访问性能优化)
1) SQL语句优化。(1)查询语句中尽量不要使用*,(2)用关联查询代替子查询,(3)用exists代替in,(4)调整where子句顺序,将过滤掉最多数据记录的条件写在where子句最末尾。
2) 索引优化。对查询字段建立索引,并且主要查询语句不要让索引失效。比如(1)使用like时,%不应该放在最前面,(2)联合索引注意最左前缀原则,(3)where子句使用!=和<>或导致索引失效,(4)where子句应避免表达式操作、函数操作。
3) 表结构或设计优化。比如(1)能用数字或枚举就不用其他类型,(2)字段尽可能使用NOT NULL,(3)固定长度的表会更快,(4)长度越小的列越快。 还有优化数据库设计。
4) 数据库访问性能优化。
5) 硬件和网络优化。 - 数据库查询,更新,删除语句:Select查询,insert插入,update更新,delete删除
语法顺序:SELECT[DISTINCT] FROM JOIN ON WHERE GROUP BY HAVING UNION ORDER BY LIMIT
执行顺序:
FROM:将数据从硬盘加载到数据缓冲区,方便对接下来的数据进行操作。
WHERE:从基表或视图中选择满足条件的元组。(不能使用聚合函数)
JOIN(如right left 右连接-------从右边表中读取某个元组,并且找到该元组在左边表中对应的元组或元组集)
ON:join on实现多表连接查询,推荐该种方式进行多表查询,不使用子查询。
GROUP BY:分组,一般和聚合函数一起使用。
HAVING:在元组的基础上进行筛选,选出符合条件的元组。(一般与GROUP BY进行连用)
SELECT:查询到得所有元组需要罗列的哪些列。
DISTINCT:去重的功能。
UNION:将多个查询结果合并(默认去掉重复的记录)。
ORDER BY:进行相应的排序。
LIMIT 1:显示输出一条数据记录(元组) - Inner join,left join,right join区别【2】:left以 left join 左侧的表为主表;right 以 right join 右侧表为主表;inner join 查找的数据是左右两张表共有的
- 索引原理;主键和索引;倒排索引,聚集索引;mysql 主键、唯一索引、普通索引、外键的区别;数据库索引为什么快?
- 索引的使用场景【2】,联合索引
索引作用:提高数据的查询速度
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
- 可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。
- 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
- 通过使用索引,可以在查询 的过程中,使用优化隐藏器,提高系统的性能。
应用场景 - 在经常需要搜索的列上,可以加快搜索的速度;
- 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构; ,
- 在经常用在连接的列上,这 些列主要是一些外键,可以加快连接的速度;
- 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
- 在经常需要排序的列上创 建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
- 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
使用索引有诸多优点:
(1) 可以加快数据查询速度,这也是创建索引的主要原因。
(2) 唯一性索引可以保证列所有值的唯一性。
(3) 可以加快表与表之间的连接。
(4) 可以显著减少查询子句中分组和排序的时间。
(5) 通过使用索引,可以在查询过程中使用优化隐藏器,提高系统性能。
使用索引也有诸多缺点:(索引并非越多越好)
(1) 创建和维护索引需要耗费时间,特别是数据量少时反而会浪费时间。
(2) 索引需要占用物理空间
(3) 索引只适合查询,会降低表的增删改效率,因为每次增删改都要对索引进行动态维护。
索引的创建一般有3种方式(1)直接创建索引,使用CREATE INDEX;(2)创建表时创建索引CREATE TABLE tablename(…, INDEX );(3)修改表时增加索引,使用ALTER TABLE tablename ADD INDEX 。创建索引时均需要指明索引名和索引列。相应的删除索引有两种方式:(1)直接删除索引DROP INDEX 索引名 ON 表名(2)修改表时删除索引,ALTER TABLE 表名 DROP INDEX 索引名。
联合索引规则
1) 需要加索引的字段,需要在where条件中
2) 数据量少的字段不需要索引
3) 如果where条件中是or条件,加索引不起作用
4) 符合最左原则:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找
联合索引的作用:减少开销(针对大量数据的表),覆盖索引(减少了随机io),效率高(索引列越多,通过索引筛选出的数据越少),引申
- 数据库的慢查询和索引,数据库查询特别慢,如何判断是慢查询的问题还是索引的问题
偶尔慢:刷新“脏”页(刷新“脏”页时,系统会暂停其他的操作,全身心的将数据存到磁盘中,就会导致平常正常执行的mysql语句变慢),数据被锁住(可以用show processlist命令查看一下语句执行的状态,查看要查询的数据是否被锁住)
一直很慢:查询的数据量太大(查看是否查询了不必要的行与列,避免用select * from table这样的语句),没有用到索引(未建立索引,索引失效,系统错选索引【将全表扫描与用索引要扫描的行数进行比较,若是觉得运用索引反而要复杂,则系统就会放弃索引采用全表扫描的方式】)
可能导致索引失效的原因:在索引列上用了内置函数或者其他±*/运算:用通配符开头;多列索引违背最佳最匹配原则;or操作符容器造成索引失效,除非or的每个操作列都有索引;字符串不加单引号 - 数据库搜索引擎:MySQL三种主要引擎为MyISAM、InnoDB(5.1之后的默认存储引擎)和Memory。
1) InnoDB是一个事务型的存储引擎,还提供了行级锁和外键约束。事务型主要包括两点:(1)提供了对数据库ACID事务的支持,(2)实现了SQL标准的四种隔离级别。但是InnoDB不支持全文索引。(事务型、行级锁、外键)
2) MyISAM不支持事务、不支持行级锁和外键。当插入或更新数据时,及时写操作需要锁定整个表,效率会低一些,但是强调快速读取。(快速读取)
3) Memory引擎使用存储在内存中的内容来创建表,每个Memory表只实际对应一个磁盘文件,默认使用Hash索引。(表对应磁盘文件)
使用场景:MyISAM(表损坏后不能恢复,不支持事务和外键)【插入不频繁,查询频繁,没有事务,做很多count计算】;InnoDB(支持事务和外键,会占用更多的磁盘空间保留数据和索引)【可靠性要求高,要求事务,表更新和查询都很频繁,高并发(行级锁)】
将原有的表格从一个引擎移动到另一个引擎:ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB - 数据库表怎么设计,用几范式,数据库范式,如何设计表(场景题)
设计数据库表
1) 由具体的业务展开,保证表格的列的唯一性,每行数据的唯一性,每个表格涉及的内容的单一性。对于有关联字段,以减少查询表的数量为目标考虑是否要进行冗余处理,不一定要严格满足第三范式。
2) 选择具体的数据类型。整数类型tinyint,smallint,mediumint,int,bigint 分别使用的 8,16,24,32,64位存储空间,mysql可以为整形类型指定宽度,规定客户端显示的范围;unsigned表示字段不允许负数,范围是0-255;字符类型包括varchar(可变字符串)和char(固定长字符串),varchar在进行存储时,要使用额外的 1到2个字节进行长度的记录;BLOB 和 TEXT类型;ENUM 类型;日期和时间类型。 - 数据库drop,delete,truncate的区别(delete<truncate<drop)
- delete是DML语言,truncate和drop是DDL语言,即delete处理后可以回滚,事务提交后生效,如果有相应的tigger,执行时会被触发,但后两种不能。
- delete和truncate都是执行数据维度删除,delete可以通过where指定删除行,drop删除数据和表结构。
- 速度上,drop>truncate>delete
-
数据库的备份如何实现的(mysql)(按照数据库的状态,分为冷备份【数据库处于关闭状态】,热备份【数据库处于运行状态】,逻辑备份【使用软件将数据写到一个文件上】,按照备份恢复的方式分为逻辑备份【备份sql语句,速度慢,占用空间小】和物理备份【备份文件,速度快,占用空间大】)
1) 完全备份。备份整个数据库,包括表,索引,视图,存储过程等
2) 事务日志备份。使用事务日志,只备份上次备份以来对数据库做的改变。
3) 差异备份。不使用事务日志,备份改变的数据
4) 文件备份。使用文件存储备份数据
常见的备份工具:
1) mysqldump工具:支持基于innodb的热备份,属于逻辑备份,慢,可以实现时间点的恢复,不需要对数据加锁,加选项【–single-transaction】即可;对于myisam存储引擎的表,只能温备份,备份前加读写锁,防止数据的写入【–lock-all-tables】
2) 基于LVM快照备份;tar包备份;percona提供的xtrabackup工具;平台 p8net mysql管理工具MyAdmin v1.0 (mysql 定时备份工具) -
数据库很多的数据怎样提高检索效率(数据库设计,SQL语句):出发点为避免全表扫描
-
数据库事务,事务的特性,具体原理
事务的特性:原子性【事务要么全部执行,要么全部回滚,执行失败】,一致性【事务提交后,数据库必须从一个状态转变为另一个一致性的状态,完整性约束没被破坏】,持久性【事务一旦提交,不会再回滚】,隔离性【两个事务在各自提交之前不可见,事务并发独立】。
具体原理
1) 原子性:事务开始前,会先将旧数据保存一份到undo log中,如果在这中间环节出现故障,那么,会通过之前版本的undo log 恢复到之前版本数据库
2) 一致性: 和原子性一样,原子性是实现一致性的保证
3) 持久性:undo log保存旧数据,redo log保存新数据。undo log保存旧数据,redo log保存新数据,在事务提交时只需将redo log 持久化到磁盘上。
4) 隔离性:加锁实现,四种隔离级别,每种的加锁方式不同,实现的隔离性能也不同
事务隔离的级别
级别 说明 脏读 不可重复读 幻读
Read uncommitted “有事务未提交时也能读” √ √ √
Read committed 理解为“所有事务提交后才能读”,所以解决了脏读问题 × √ √
Repeatable read “可重复读”,解决了脏读和不可重复读 × × √
Serializable “序列化”,解决了脏读、不可重复读、幻读 × × × -
数据库视图(view):优点【简化复杂查询,限制用户对数据库的访问,可以启用计算列,向后兼容】,缺点【基于视图查询慢,修改数据需要维护视图】
常见操作:
不允许更新视图的情况:字段来自库函数或者字段表达式或常数;由两个以上的基表导出;定义中有嵌套查询,DISTINCT任选项,GROUP BY子句或者聚集函数;不允许更新的视图定义的视图
更新的情况:由一个基表定义的视图,只含有基表的主键或候补键,并且视图中没有用表达式或函数定义的属性。 -
关系数据库有哪些:Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL
-
数据库CRUD(create 添加数据read读取数据 update 修改数据delete删除数据)
-
数据库挂了,用户就不能访问了吗,一 个地区的消息队列机子全挂了怎么办
-
你的数据库一会又500个连接数,一会有10个,你分析一下情况
-
如果从数据库挂掉了,会发生什么
-
数据库表相同元素的查询
-
内连接的查询语句
-
Sql中的like通配符,去重
-
Java连接数据库,并在数据库插入一行数据
-
Sql,查询班级男女生人数
-
统计各班人数和降序排列
-
数据库如果索引是 sno,name,aga,查询只是 sno,age 能命中吗
-
mysql创建一个学生表,包含id(int)和name(string),主键的创建,mysql建立索引
-
sql如何查找某段时间内发表过动态的ip数目
-
写一个sql,把userid去重,保留id比较小的数据(select id,userid from table group by userid;)
-
学生表,有学生id,班级,科目,成绩 实现查找每门科目的第一名
-
存在两个数据库表A(a1,a2,a3)和B(b1,b2,b3),写出A表中a3字段和B表中b1字段相等,且按照A表a1字段递减排序的记录(A.a1,A.a2,A.a3,B.b2,B.b3)输出第10-20条记录
-
数据库如何查看自己想查看的排名前10的数据
-
查询第5条到第10条数据
1)查询前十条数据:Select * from serv_history_517 where rownum<11;
2)查询后十条数据:select * from (select rownum no ,serv_id from serv_history_517 ) where no>10
3)查询第五条到第十条的数据(有六条数据):select * from (select rownum no ,serv_id from serv_history_517 ) where no>4 and no <11; -
数据库分页,limit3 5的含义
数据库分页:需要查询几条数据,而不是全部的查询出结果,比如sql的limit
select*from user limit 2,3 --注意是第2条数据之后,不包含第2条数据。3代表总共查询3条记录 如果是11到15的话就是limt 10,5 -
Sql,手写去重