1.数据库在查询的时候,数据类型会出现隐式转化(如varchar不加单引号的话可能会自动转换为int型,索引会失效),怎么避免隐式转换#sname为varchar类型,用值类型搜索
explain select sno
from s
where sname=123;
#sname为varchar类型,用字符串类型搜索
explain select sno
from s
where sname='123';
上述查询上面不会走索引,下面会走索引。
解决方案:
1.in中全使用字符串类型
2.cast
附:explain字段详解
2.最左匹配原则#建立名为test_user的表
CREATE TABLE `test_user` (
`name` varchar(20) DEFAULT NULL,
`province` int(11) DEFAULT NULL,
`sex` varchar(20) DEFAULT NULL,
`birthday` int(11) DEFAULT NULL,
`phone` double DEFAULT NULL
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
#建立(name,phone,province)联合索引
CREATE INDEX test_user_name_phone_province_index
ON test_user (name, phone, province);
以下查询走索引结果如下图:explain select name
from test_user
where name='张三';
explain select name
from test_user
where name='张三' and phone=32432;
explain select name
from test_user
where name='张三' and phone=32432 and province=2;
explain select name
from test_user
where phone=32432;
explain select name
from test_user
where
3.b+树和b树、红黑树、二叉树的区别
数据结构之树--二叉树/B树/B+树/红黑树及相关算法
浅谈AVL树,红黑树,B树,B+树原理及应用
趣解各种树(很详细)
MYSQL-索引结构介绍、为什么选择B+树而不是B树
深入理解数据库索引采用B树和B+树的原因
B树B-树和B+树的总结
AVL是比红黑树更严格的二叉平衡树,缺点是在极端情况下,会退化成链表;
红黑树有自己的一套规则,根节点必为黑色。
B树是多叉树,高度降低,节点上有待搜索数据。
B+树在B树基础上,非叶子节点并不存储真实数据,只是索引,只有叶子节点存储真实数据,并且叶子节点间也形成链表。
4.覆盖索引
覆盖索引解释(很详细)
覆盖索引总结
首先要知道什么是聚集索引和二级索引。
索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫 做覆盖索引。
聚集索引(主键索引):
聚集索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的即为整张表的记录数据。
聚集索引的叶子节点称为数据页,聚集索引的这个特性决定了索引组辅助索引(二级索引):
非主键索引,叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。
5.drop、truncate、 delete区别,哪个最快,为什么
drop,delete与truncate的区别(删除表的方式,哪个快)
drop、truncate和delete的区别
drop直接删掉表 truncate删除表中数据,再插入时自增长id又从1开始 delete删除表中数据,可以加where字句。
用TRUNCATE替代DELETE
TRUNCATE不记录日志,DELETE记录日志,所以TRUNCATE要快于DELETE。
但是一旦用TRUNCATE进行删除就不能进行恢复, TRUNCATE是删除整张表的数据。不能加where条件。
6.MySQL索引类型
mysql索引类型详解
mysql索引总结
mysql索引简洁口诀
mysql索引精选
从数据结构角度
1、B+树索引(O(log(n))):关于B+树索引,可以参考 MySQL索引背后的数据结构及算法原理
2、hash索引:
a 仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询
b 其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引
c 只有Memory存储引擎显示支持hash索引
3、FULLTEXT索引(现在MyISAM和InnoDB引擎都支持了)
4、R-Tree索引(用于对GIS数据类型创建SPATIAL索引)
从物理存储角度
1、聚集索引(clustered index)
2、非聚集索引(non-clustered index)
从逻辑角度
1、主键索引:主键索引是一种特殊的唯一索引,不允许有空值
2、普通索引或者单列索引
3、多列索引(复合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合
4、唯一索引或者非唯一索引
5、空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]
1、unique|fulltext|spatial为可选参数,分别表示唯一索引、全文索引和空间索引;
2、index和key为同义词,两者作用相同,用来指定创建索引
3、col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择;
4、index_name指定索引的名称,为可选参数,如果不指定,MYSQL默认col_name为索引值;
5、length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;
6、asc或desc指定升序或降序的索引值存储
7.一个Mysql操作很慢,怎样排查原因
反应慢排查示例
mysql数据库慢排查及解决
1.top查看cpu占用
2.查看锁状态
3.查看线程状态processlist
8.悲观锁,乐观锁
悲观锁
总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞直到它拿到锁(共享资源每次只给一个线程使用,其它线程阻塞,用完后再把资源转让给其它线程)。传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。
乐观锁
总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库提供的类似于write_condition机制,其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。
两种锁的使用场景:从上面对两种锁的介绍,我们知道两种锁各有优缺点,不可认为一种好于另一种,像乐观锁适用于写比较少的情况下(多读场景),即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。但如果是多写的情况,一般会经常产生冲突,这就会导致上层应用会不断的进行retry,这样反倒是降低了性能,所以一般多写的场景下用悲观锁就比较合适。
9.索引的优点与缺点,底层怎么实现的?索引的优点:
1.创建唯一性索引,保证数据库表中每一行数据的唯一性。
2.大大加快数据的检索速度(最主要的原因)。
3.加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
5.通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能。
索引的缺点:
1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。
10.mysql引擎有哪些,有啥区别
mysql五种引擎详解
InnoDB Myisam Memory
InnoDB跟Myisam的默认索引是B+tree,Memory的默认索引是hash
区别:
1.InnoDB支持事务,支持外键,支持行锁,写入数据时操作快,MySQL5.6版本以上才支持全文索引。
2.Myisam不支持事务。不支持外键,支持表锁,支持全文索引,读取数据快。
3.Memory所有的数据都保留在内存中,不需要进行磁盘的IO所以读取的速度很快,但是一旦关机的话表的结构会保留但是数据就会丢失,表支持Hash索引,因此查找速度很快。
11.事务的特性
事务及其特性和隔离级别详解
原子性(Atomicity):操作这些指令时,要么全部执行成功,要么全部不执行。只要其中一个指令执行失败,所有的指令都执行失败,数据进行回滚,回到执行指令前的数据状态。
一致性(Consistency):事务的执行使数据从一个状态转换为另一个状态,但是对于整个数据的完整性保持稳定。
隔离性(Isolation):隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。
即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。
持久性(Durability):当事务正确完成后,它对于数据的改变是永久性的。
12.主键跟索引的区别
主键与聚集索引的区别
主键(PRIMARY KEY)
表通常具有包含唯一标示表中每一行的值的一列或者一组列。这样的一列或者多列称为表的主键(PK),用于强制表的尸体完整性。在创建或者修改表时,您可以通过定义PK约束来创建主键。
一个表只能有一个PK约束,并且PK约束中的列不能接受空值。由于PK约束可以保证数据的唯一性,因此经常对标识列定义这种约束。
如果为表指定了PK约束,数据库引擎将通过为主键列创建唯一索引来强制数据的唯一性。当在查询中使用主键时,此索引还可以用来对数据进行快速访问。因此,所选的主键必须遵守创建唯一索引的规则。
创建主键时,数据库引擎会自动创建唯一的索引来强制实施PK约束的唯一性要求。如果表中不存在狙击索引或未显示指定非聚集索引,则将创建唯一的聚集索引以强制实施PK约束。
聚集索引
聚集索引给予数据行的兼职在表内排序和存储这些数据行。每个表只能有一个聚集索引,因为数据行本身只能按一个顺序存储。
每个表几乎都对列定义聚集索引来实现下列功能:
可用于经常使用的查询。
提供高度唯一性。
13.InnoDB引擎主键为什么设成自增?
MySQL的InnoDB存储引擎为什么要用自增的主键?
为什么推荐InnoDB引擎使用自增主键?
在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
InnoDB的辅助索引data域存储相应记录主键的值而不是地址。所以不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。
InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效。
14.默认的隔离级别
15.隔离性分几个级别?每个级别的意思?
事务的隔离级别分为:Read uncommitted(读未提交)
Read Committed(读已提交)
Repeatable Reads(可重复读)
Serializable(串行化)
Read uncommitted
读未提交:隔离级别最低的一种事务级别。在这种隔离级别下,会引发脏读、不可重复读和幻读。
Read Committed
读已提交读到的都是别人提交后的值。这种隔离级别下,会引发不可重复读和幻读,但避免了脏读。
Repeatable Reads
可重复读这种隔离级别下,会引发幻读,但避免了脏读、不可重复读。
Serializable
串行化是最严格的隔离级别。在Serializable隔离级别下,所有事务按照次序依次执行。脏读、不可重复读、幻读都不会出现。
16.关于mysql处理百万级以上的数据时如何提高其查询速度的方法
17.SQL join操作
18.数据库的三范式是什么?
19.数据库MVCC
20.数据库类型(关系型数据库/非关系型数据库)