mysql怎么写外简介 mysql外表
MySQL简单介绍——换个角度认识MySQL
1、InnoDB存储引擎
创新互联公司是一家专注于网站制作、成都网站建设与策划设计,谢家集网站建设哪家好?创新互联公司做网站,专注于网站建设十载,网设计领域的专业建站公司;建站业务涵盖:谢家集等地区。谢家集做网站价格咨询:028-86922220
Mysql版本=5.5 默认的存储引擎,MySQL推荐使用的存储引擎。支持事务,行级锁定,外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。
存储格式 : 数据,索引集中存储,存储于同一个表空间文件中。
InnoDB的行锁模式及其加锁方法: InnoDB中有以下两种类型的行锁:共享锁(读锁: 允许事务对一条行数据进行读取)和 互斥锁(写锁: 允许事务对一条行数据进行删除或更新), 对于update,insert,delete语句,InnoDB会自动给设计的数据集加互斥锁,对于普通的select语句,InnoDB不会加任何锁。
InnoDB行锁的实现方式: InnoDB行锁是通过给索引上的索引项加锁来实现的,如果没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB这种行锁实现特点意味着:如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样。
(1)在不通过索引条件查询时,InnoDB会锁定表中的所有记录。
(2)Mysql的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果使用相同的索引键,是会出现冲突的。
(3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,但都是通过行锁来对数据加锁。
优点:
1、支持事务处理、ACID事务特性;
2、实现了SQL标准的四种隔离级别( 原子性( Atomicity )、一致性( Consistency )、隔离性(Isolation )和持续性(Durability ));
3、支持行级锁和外键约束;
4、可以利用事务日志进行数据恢复。
5、锁级别为行锁,行锁优点是适用于高并发的频繁表修改,高并发是性能优于 MyISAM。缺点是系统消耗较大。
6、索引不仅缓存自身,也缓存数据,相比 MyISAM 需要更大的内存。
缺点:
因为它没有保存表的行数,当使用COUNT统计时会扫描全表。
使用场景:
(1)可靠性要求比较高,或者要求事务;(2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况。
2、 MyISAM存储引擎
MySQL= 5.5 MySQL默认的存储引擎。ISAM:Indexed Sequential Access Method(索引顺序存取方法)的缩写,是一种文件系统。擅长与处理,高速读与写。
功能:
(1)支持数据压缩存储,但压缩后的表变成了只读表,不可写;如果需要更新数据,则需要先解压后更新。
(2)支持表级锁定,不支持高并发;
(3)支持并发插入。写操作中的插入操作,不会阻塞读操作(其他操作);
优点:
1.高性能读取;
2.因为它保存了表的行数,当使用COUNT统计时不会扫描全表;
缺点:
1、锁级别为表锁,表锁优点是开销小,加锁快;缺点是锁粒度大,发生锁冲动概率较高,容纳并发能力低,这个引擎适合查询为主的业务。
2、此引擎不支持事务,也不支持外键。
3、INSERT和UPDATE操作需要锁定整个表;
使用场景:
(1)做很多count 的计算;(2)插入不频繁,查询非常频繁;(3)没有事务。
InnoDB和MyISAM一些细节上的差别:
1、InnoDB不支持FULLTEXT类型的索引,MySQL5.6之后已经支持(实验性)。
2、InnoDB中不保存表的 具体行数,也就是说,执行select count() from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count()语句包含 where条件时,两种表的操作是一样的。
3、对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
4、DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
6、另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。
1.索引概述
利用关键字,就是记录的部分数据(某个字段,某些字段,某个字段的一部分),建立与记录位置的对应关系,就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集,它是提高查询速度最有效的方法。一个没有建立任何索引的表,就相当于一本没有目录的书,在每次查询时就会进行全表扫描,这样会导致查询效率极低、速度也极慢。如果建立索引,那么就好比一本添加的目录,通过目录的指引,迅速翻阅到指定的章节,提升的查询性能,节约了查询资源。
2.索引种类
从索引的定义方式和用途中来看:主键索引,唯一索引,普通索引,全文索引。
无论任何类型,都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。
以上类型的差异:对索引关键字的要求不同。
关键字:记录的部分数据(某个字段,某些字段,某个字段的一部分)。
普通索引,index:对关键字没有要求。
唯一索引,unique index:要求关键字不能重复。同时增加唯一约束。
主键索引,primary key:要求关键字不能重复,也不能为NULL。同时增加主键约束。
全文索引,fulltext key:关键字的来源不是所有字段的数据,而是从字段中提取的特别关键词。
PS:这里主键索引和唯一索引的区别在于:主键索引不能为空值,唯一索引允许空值;主键索引在一张表内只能创建一个,唯一索引可以创建多个。主键索引肯定是唯一索引,但唯一索引不一定是主键索引。
3.索引原则
如果索引不遵循使用原则,则可能导致索引无效。
(1)列独立
如果需要某个字段上使用索引,则需要在字段参与的表达中,保证字段独立在一侧。否则索引不会用到索引, 例如这条sql就不会用到索引:select * from A where id+1=10;
(2)左原则
Like:匹配模式必须要左边确定不能以通配符开头。例如:select * from A where name like '%小明%' ,不会用到索引,而select * from A where name like '小明%' 就可以用到索引(name字段有建立索引),如果业务上需要用到'%小明%'这种方式,有两种方法:1.可以考虑全文索引,但mysql的全文索引不支持中文;2.只查询索引列或主键列,例如:select name from A where name like '%小明%' 或 select id from A where name like '%小明%' 或 select id,name from A where name like '%小明%' 这三种情况都会用到name的索引;
复合索引:一个索引关联多个字段,仅仅针对左边字段有效果,添加复合索引时,第一个字段很重要,只有包含第一个字段作为查询条件的情况才会使用复合索引(必须用到建索引时选择的第一个字段作为查询条件,其他字段的顺序无关),而且查询条件只能出现and拼接,不能用or,否则则无法使用索引.
(3)OR的使用
必须要保证 OR 两端的条件都存在可以用的索引,该查询才可以使用索引。
(4)MySQL智能选择
即使满足了上面说原则,MySQL也能弃用索引,例如:select * from A where id 1;这里弃用索引的主要原因:查询即使使用索引,会导致出现大量的随机IO,相对于从数据记录的第一条遍历到最后一条的顺序IO开销,还要大。
4.索引的使用场景
(1)索引检索:检索数据时使用索引。
(2)索引排序: 如果order by 排序需要的字段上存在索引,则可能使用到索引。
(3)索引覆盖: 索引拥有的关键字内容,覆盖了查询所需要的全部数据,此时,就不需要在数据区获取数据,仅仅在索引区即可。覆盖就是直接在索引区获取内容,而不需要在数据区获取。例如: select name from A where name like '小明%';
建立索引索引时,不能仅仅考虑where检索,同时考虑其他的使用场景。(在所有的where字段上增加索引,就是不合理的)
5.前缀索引
前缀索引是建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时,即使使用字段前部分数据,也可以去识别某些记录。就比如一个班级里,我要找王xx,假如姓王的只有1个人,那么就可以建一个关键字为'王'的前缀索引。语法:Index `index_name` (`index_field`(N))使用index_name前N个字符建立的索引。
6.索引失效
(1) 应尽量避免在 where 子句中使用 != 或 操作符,否则将引擎放弃使用索引而进行全表扫描;
(2) 应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描;
(3) 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描;
(4)应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描;如select id from t where num/2 = 100;
(5) 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描;如:select id from t where substring(name,1,3) = ’abc’ ;
(6)应尽量避免在where子句中对字段进行类型转换,这将导致引擎放弃使用索引而进行全表扫描; 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,如select id from t where id = 1;如果id字段在表设计中是varchar类型,那么即使id列上存的是数字,在查询时也一定要用varchar去匹配,sql应改为select id from t where id = '1';
(7)应尽量避免在where子句中单独引用复合索引里非第一位置的索引;
join 的两种算法:BNL 和 NLJ
NLJ(Nested Loop Join)嵌套循环算法;以如下 SQL 为例:
select * from t1 join t2 on t1.a=t2.a
SQL 执行时内部流程是这样的:
1. 先从 t1(假设这里 t1 被选为驱动表)中取出一行数据 X;
2. 从 X 中取出关联字段 a 值,去 t2 中进行查找,满足条件的行取出;
3. 重复1、2步骤,直到表 t1 最后一行循环结束。
这就是一个嵌套循环的过程,如果在被驱动表上查找数据时可以使用索引,总的对比计算次数等于驱动表满足 where 条件的行数。假设这里 t1、t2都是1万行,则只需要 1万次计算,这里用到的是Index Nested-Loops Join(INLJ,基于索引的嵌套循环联接)。
如果 t1、t2 的 a 字段都没有索引,还按照上述的嵌套循环流程查找数据呢?每次在被驱动表上查找数据时都是一次全表扫描,要做1万次全表扫描,扫描行数等于 1万+1万*1万,这个效率很低,如果表行数更多,扫描行数动辄几百亿,所以优化器肯定不会使用这样的算法,而是选择 BNL 算法;
BNLJ(Block Nested Loop Join)块嵌套循环算法;
1. 把 t1 表(假设这里 t1 被选为驱动表)满足条件的数据全部取出放到线程的 join buffer 中;
2. 每次取 t2 表一行数据,去 joinbuffer 中进行查找,满足条件的行取出,直到表 t2 最后一行循环结束。
这个算法下,执行计划的 Extra 中会出现 Using join buffer(Block Nested Loop),t1、t2 都做了一次全表扫描,总的扫描行数等于 1万+1万。但是由于 joinbuffer 维护的是一个无序数组,每次在 joinbuffer 中查找都要遍历所有行,总的内存计算次数等于1万*1万。另外如果 joinbuffer 不够大放不下驱动表的数据,则要分多次执行上面的流程,会导致被驱动表也做多次全表扫描。
BNLJ相对于NLJ的优点在于,驱动层可以先将部分数据加载进buffer,这种方法的直接影响就是将大大减少内层循环的次数,提高join的效率。
例如:
如果内层循环有100条记录,外层循环也有100条记录,这样的话,每次外层循环先将10条记录放到buffer中,内层循环的100条记录每条与这个buffer中的10条记录进行匹配,只需要匹配内层循环总记录数次即可结束一次循环(在这里,即只需要匹配100次即可结束),然后将匹配成功的记录连接后放入结果集中,接着,外层循环继续向buffer中放入10条记录,同理进行匹配,并将成功的记录连接后放入结果集。后续循环以此类推,直到循环结束,将结果集发给client为止。
可以发现,若用NLJ,则需要100 * 100次才可结束,BNLJ则需要100 / block_size * 100 = 10 * 100次就可结束,大大减少了循环次数。
JOIN 按照功能大致分为如下三类:
JOIN、STRAIGHT_JOIN、INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。
LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。
RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。
注意:mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join。
mysql 多表连接查询方式,因为mysql只支持NLJ算法,所以如果是小表驱动大表则效率更高;反之则效率下降;因此mysql对内连接或等值连接的方式做了一个优化,会去判断join表的数据行大小,然后取数据行小的表为驱动表。
INNER JOIN、JOIN、WHERE等值连接和STRAIGHT_JOIN都能表示内连接,那平时如何选择呢?一般情况下用INNER JOIN、JOIN或者WHERE等值连接,因为MySQL 会按照"小表驱动大表的策略"进行优化。当出现需要排序时,才考虑用STRAIGHT_JOIN指定某张表为驱动表。
两表JOIN优化
a.当无order by条件时,根据实际情况,使用left/right/inner join即可,根据explain优化 ;
b.当有order by条件时,如select * from a inner join b where 1=1 and other condition order by a.col;使用explain解释语句;
1)如果第一行的驱动表为a,则效率会非常高,无需优化;
2)否则,因为只能对驱动表字段直接排序的缘故,会出现using temporary,所以此时需要使用STRAIGHT_JOIN明确a为驱动表,来达到使用a.col上index的优化目的;或者使用left join且Where条件中不含b的过滤条件,此时的结果集为a的全集,而STRAIGHT_JOIN为inner join且使用a作为驱动表。注:使用STRAIGHT_JOIN虽然不会using temporary,但也不是一定就能提高效率,如果a表数据远远超过b表,那么有可能使用STRAIGHT_JOIN时比原来的sql效率更低,所以怎么使用STRAIGHT_JOIN,还是要视情况而定。
在使用left join(或right join)时,应该清楚的知道以下几点:
(1). on与 where的执行顺序
ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。
(2).注意ON 子句和 WHERE 子句的不同
即使右表的数据不满足ON后面的条件,也会在结果集拼接一条为NULL的数据行,但WHERE后面的条件不一样,右表不满足WHERE的条件,左表关联的数据也会被过滤掉。
(3).尽量避免子查询,而用join
往往性能这玩意儿,更多时候体现在数据量比较大的时候,此时,我们应该避免复杂的子查询。
(1)in 和 not in 要慎用,如:select id from t where num in(1,2,3)对于连续的数值,能用 between 就不要用 in:select id from t where num between 1 and 3很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b)用下面的语句替换:select num from a where exists(select 1 from b where num=a.num)
(2)Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。
(3)join语句,MySQL里面的join是用小表去驱动大表,而由于MySQL join实现的原理就是做循环,比如left join就是对左边的数据进行循环去驱动右边的表,左边有m条记录匹配,右边有n条记录那么就是做m次循环,每次扫描n行数据,总扫面行数是m*n行数据。左边返回的结果集的大小就决定了循环的次数,故单纯的用小表去驱动大表不一定的正确的,小表的结果集可能也大于大表的结果集,所以写join的时候尽可能的先估计两张表的可能结果集,用小结果集去驱动大结果集.值得注意的是在使用left/right join的时候,从表的条件应写在on之后,主表应写在where之后.否则MySQL会当作普通的连表查询;
(4)select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的;
(5)select * from t 这种语句要尽量避免,使用具体的字段代替*,更有实际意义,需要什么字段就返回什么字段;
(6)数据量大的情况下,limit要慎用,因为使用limit m,n方式分页时,mysql每次都是查询前m+n条,然后舍弃前m条,所以m越大,偏移量越大,性能就越差。比如:select * from A limit 1000000,20这钟,查询效率就会非常低,当分页的页数大于一定的数量之后,就可以换种方式来分页:select * from A a join (select id from A limit 1000000,20) b on a.id=b.id;
用自己语言描述MySql简介 800字
MySQL一般特制完整的MySQLRDBMS,是一个开源的关系型数据库管理系统(Relational Database Management System),现在属于Oracle公司。随着MySQL功能的不断完善,性能不断提高,又有开源免费的优势,越来越多的企业选择使用MySQL,而放弃商用收费的Oracle。
mysql外键怎么写
mysql添加外键:
为已经添加好的数据表添加外键:
语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id)
//FK_ID是外键的名称
/*
CREATE TABLE `tb_active` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`),
CONSTRAINT `FK_ID` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*/
mysql外键约束怎么写
你好朋友
1.简介
外键表示一个表中的一个字段被另外一个表中的字段应用.外键对相关表中的数据造成了限制,使MySQL 能够保证参照完整性.
在MySQL 中,InnoDB 存储引擎支持外键.在一张表中,可以存在多个外键.
外键的创建可以在创建表的时候创建,也可以在创建表之后增加(考虑数据的完整性问题).
父表:外键所指向的表.
字表:相对于父表,拥有外键的表.
2.语法
create 语法
create table table_name(
column_1,
column_2,
....
constraint constraint_name foreign key (column_name)
references parent_table(column_name)
on delete action
on update action
) engine=InnoDB default charset utf8;
constraint 子句允许为外键定义一个名称,如果不写,MySQL 自动生成一个名称
foreign key 子句指定子表中要应用父表的列.注意:MySQL 会自动创建一个基于外键的索引.
references 子句指定父表中的被引用字段.foreign key 和references 指定的列数必须相同.
on delete: 定义当父表中的记录被删除时,子表的记录应该执行的动作.action包括:
on delete restrict:(默认),父表不能删除一个已经被子表引用的记录.
on delete no action:等同与on delete restrict
on delete cascade: 级联模式,父表删除后,对应子表关联的数据也跟着被删除
on delete set null:置空模式,父表删除后,对应子表关联的外键值被设置为NULL,需要注意的是,如果子表的外键设置not null ,则不能使用这种模式,因为会相互冲突.
on update:定义父表中的记录更新时,子表的记录应该执行的动作.action 包括:
on update restrict:(默认),父表不能更新一个已经被子表引用的记录.
on update no action:等同与on delete restrict
on update cascade: 级联模式,父表更新后,对应子表关联的数据也跟着被更新
on update set null:置空模式,父表更新后,对应子表关联的外键值被设置为NULL,需要注意的是,如果子表的外键设置not null ,则不能使用这种模式.
alter 语法
-- 添加外键
alter table table_name add constraint constraint_name
foreign key column_name
references parent_table(column_name)
on delete action
on update action
-- 删除外键
alter table table_name drop constraint_name;
-- 如果没有显式的定义名字,可以使用如下命令获取
show create table table_name;
3.演示
构造两张表categoryes 和products.每个类别有多种产品,而每个产品只属于一个类别.
-- 设置 类别表 categoryes 和产品表 products
create table categoryes(
c_id int not null auto_increment,
c_name varchar(45) not null,
c_description text,
primary key (c_id)
) engine=InnoDB default charset utf8 comment '类别表';
create table products(
p_id int not null auto_increment,
p_name varchar(45) not null,
p_price decimal(8,4),
c_id int,
primary key (p_id),
constraint fk_products_categoryes
foreign key (c_id)
references categoryes(c_id)
on delete set null
on update cascade
) engine=InnoDB default charset utf8 comment '产品表';
在这两张表的基础上,新生成一张vendors 供应商表,并更新products字段
-- 新生成一张表 供应商 vendors ,并为 products 新添加字段 v_id 外键
-- 引用 vendors.v_id
create table vendors(
v_id int not null auto_increment,
v_name varchar(45),
primary key (v_id)
) engine=InnoDB default charset utf8 comment '供应商';
alter table products add column v_id int not null;
alter table products add
constraint fk_products_vendors foreign key (v_id)
references vendors(v_id)
on delete no action
on update cascade;
望采纳祝你好运
文章标题:mysql怎么写外简介 mysql外表
当前网址:http://pwwzsj.com/article/ddchccg.html