myIsam 和innodb的区别
表锁 行锁
不支持外键 支持
支持大量select insert,update性能更佳
非聚集索引 聚集索引 dr
不支持 支持事务 锁
事务的ACID属性
:atomicity(原子性),consistent(一致性),isolation(隔离性),durable(持久性)。
隔离级别
未提交读(read uncommitted) 会导致脏读
提交读(read committed) 不可重复读
重复读(repeatable read) 幻读
序列化(serializable)
union和unionall的区别
union 不允许重复
unionall允许重复
explain使用
explain select * from t_table;
-
id列
id列的编号是 select 的序列号,有几个 select 就有几个id,并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类:简单子查询、派生表(from语句中的子查询)、union 查询。 -
select_type列
1)simple:简单查询。查询不包含子查询和union
2)primary:复杂查询中最外层的 select
3)subquery:包含在 select 中的子查询(不在 from 子句中)
4)derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)
3.table列
- type列
这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行。
依次从最优到最差分别为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- possible_keys列
这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表查询。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能,然后用 explain 查看效果。
- key列
这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引,则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引,在查询中使用 force index、ignore index。
- key_len列
这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。
举例来说,film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成,并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列:film_id列来执行索引查找。
-
ref列
这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),func,NULL,字段名(例:film.id) -
rows列
这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。 -
Extra列
这一列展示的是额外信息。常见的重要值如下:
distinct: 一旦mysql找到了与行相联合匹配的行,就不再搜索了
存储过程
DELIMITER // 要先定义结束标签,不然结束不了
CREATE PROCEDURE just_test(OUT bb INT,IN aa INT) //输入输出
BEGIN
SELECT bb;
SET bb=aa;
SELECT bb;
END //
SET @bb=23;
CALL just_test(@bb,232);//输出null 只是给bb赋值
SELECT @bb
DROP PROCEDURE just_test //删除
表操作
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
create database db2 charset utf8;
SHOW DATABASES;
USE database_name;
DROP DATABASE database_name;
SHOW TABLES;
DESCRIBE t_table //查看表结构
DROP TABLE table_name
ALTER TABLE tab_student RENAME tab_student1;
ALTER TABLE table_name CHANGE Old_att_name New_att_name Old_data_type;
增删改查
insert into a1 values
(1,‘mjj’,18),
(2,‘wusir’,28);
select * from a1;
update t_table set name1=""
delete from t_table where id=1
索引
ALTER TABLE table_name
ADD PRIMARY KEY ( column
) 主键索引
ALTER TABLE table_name
ADD UNIQUE ( column
) 唯一索引
ALTER TABLE table_name
ADD INDEX index_name ( column
) 普通索引
ALTER TABLE table_name
ADD INDEX index_name ( column1
, column2
, column3
) 多列索引
mysql>ALTER TABLE table_name
ADD FULLTEXT ( column
) 全文索引
DROP INDEX index_name ON table 删除索引
普通索引的结构主要以B+树和哈希索引为主,用于实现对字段中数据的精确查找,比如查找某个字段值等于给定值的记录,A=10这种查询,因此适合数值型字段和短文本字段。
全文索引是用于检索字段中是否包含或不包含指定的关键字,有点像搜索引擎的功能,其内部的索引结构采用的是与搜索引擎相同的倒排索引结构,其原理是对字段中的文本进行分词,然后为每一个出现的单词记录一个索引项,这个索引项中保存了所有出现过该单词的记录的信息,也就是说在索引中找到这个单词后,就知道哪些记录的字段中包含这个单词了。因此适合用大文本字段的查找。
密集索引和稀疏索引
密集索引的定义:叶子节点保存的不只是键值,还保存了位于同一行记录里的其他列的信息,由于密集索引决定了表的物理排列顺序,一个表只有一个物理排列顺序,所以一个表只能创建一个密集索引
稀疏索引:叶子节点仅保存了键位信息以及该行数据的地址,有的稀疏索引只保存了键位信息机器主键