三夏时光

@三夏时光

一期一会,世当珍惜!

文章 33
朋友 15
来访 52

LATEST POSTS

0-我自己

1-大神们

三夏时光

  • MySQL

MySQL必备核心知识之索引与存储引擎的介绍

简介:各种索引的概述,以及如何创建索引与引擎的介绍

1、mysql的存储引擎介绍

简介:主要来详细介绍MyISAM与InnoDB引擎

(1)什么是数据库存储引擎?

数据库引擎是数据库底层软件组件,不同的存储引擎提供不同的存储机制,索引技巧,锁定水平等功能,使用不同的数据库引擎,可以获得特定的功能。

(2) 如何查看引擎?

如何查看数据库支持的引擎
show engines;

查看当前数据的引擎:
show create table 表名\G

查看当前库所有表的引擎:
show table status\G

(3)建表时指定引擎

create table yingqin (id int,name varchar(20)) engine = 'InnoDB';

(4)修改表的引擎

alter table 表名 engine='MyiSAm';

修改默认引擎:
• vi /etc/my.cnf
• [mysqld]下面
• default-storage-engine=MyIsAM
• 记得保存后重启服务

(5) MyISAM与InnoDB的区别

MyISAM:支持全文索引(full text);不支持事务;表级锁;保存表的具体行数;奔溃恢复不好。

Innodb:支持事务;以前的版本是不支持全文索引,但在5.6之后的版本就开始支持这个功能了;行级锁(并非绝对,当执行 sql语句时不能确定范围时,也会进行锁全表,例如: update table set id=3 where name like 'a%';);不保存表的具体行数;奔溃恢复好 。

(6)总结:什么时候选择什么引擎比较好

MyISAM:
• 一般来说MyISAM不需要用到事务的时候;
• 做很多count计算。
InnoDB:
• 可靠性要求高的,或者要求支持事务;
• 想要用到外键约束的时候(讲外键的时候会讲)。
推荐:
• 推荐用 InnoDB 。

2、mysql必备知识之常用索引精讲

简介:讲解什么是索引,索引的优缺点,以及常见索引有哪些?

  • 什么是索引?

索引是一个单独的,存储在磁盘中上的数据库结构,它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找 出在某列或多列中有特定值的行。

  • 索引的优点:
• 通过创建唯一索引,来保证数据库表中的每一行数据的唯一性;
• 可以加快数据的检索速度;
• 可以保证表数据的完整性与准确性。
  • 索引的缺点:
• 索引需要占用物理空间;
• 对表中的数据进行改动时,索引也需要跟着动态维护,降低了数据的维护速度。
  • 索引的常见类型:
• index:普通索引
• unique:唯一索引
• primary key:主键索引
• foreign key:外键索引
• fulltext: 全文索引
• 组合索引
  • 演示数据:
(1)创建表的sql语句:
create table test (
id int(7) zerofill auto_increment not null,
username varchar(20),
servnumber varchar(30),
password varchar(20),
createtime datetime,
primary key (id)
)DEFAULT CHARSET=utf8;

(2)生成百万甚至千万级别表的sql语句shell脚本:
#!/bin/bash
echo "请输入字段servnumber的值:"
read serber
echo "请输入创建sql语句的数量:"
read number
# char=`head /dev/urandom | tr -dc 0-9 | head -c 11`
for (( i=0;i<$number;i++ )) 
do
pass=`head /dev/urandom | tr -dc a-z | head -c 8` 
let serber=serber+1
echo "insert into test(id,username,servnumber,password,createtime)
values('$i','user${i}','${serber}','$pass',now());" >>sql.txt
done

vi test.sh 
执行shell脚本:sh test.sh 
进行插数操作:source /home/dazhu/sql.txt 

3、mysql必备核心知识之普通索引与唯一索引

简介:介绍普通索引与唯一索引

  • 什么是普通索引?

普通索引(index)顾名思义就是各类索引中最为普通的索引,主要任务就是提高查询速度。其特点是允许出现相同的索引 内容,允许空(null)值 。

  • 什么是唯一索引?

唯一索引:(unique)顾名思义就是不可以出现相同的索引内容,但是可以为空(null)值 。

  • 如何创建普通索引或者唯一索引?

(1)创建表的时候创建

create table test (
id int(7) zerofill auto_increment not null,
username varchar(20),
servnumber varchar(30),
password varchar(20),
createtime datetime,
unique (id)
)DEFAULT CHARSET=utf8; 

(2)直接为表添加索引

语法:
alter table 表名 add index 索引名称 (字段名称);
举例:
alter table test add unique unique_username (username);
注意:假如没有指定索引名称时,会以默认的字段名为索引名称。

(3)直接创建索引

语法:create index 索引 on 表名 (字段名);
举例:create index index_createtime on test (createtime);
  • 查看索引
语法:show index from 表名\G
举例: show index from test\G
  • 如何删除索引
语法:drop index 索引名称 on 表名;
举例:drop index unique_username on test;

语法:alter table 表名 drop index 索引名;
举例 :alter table test drop index createtime;

4、mysql必备核心知识之主键索引

简介:详细讲解主键索引

  • 什么是主键索引?

把主键添加索引就是主键索引,它是一种特殊的唯一索引,不允许有空值,而唯一索引(unique是允许为空值的)。指定 为“PRIMARY KEY” 。

主键:主键是表的某一列,这一列的值是用来标志表中的每一行数据的。
注意:每一张表只能拥有一个主键。
  • 创建主键:
1)创建表的时候创建
2)直接为表添加主键索引
语法:alter table 表名 add primary key (字段名);
举例:alter table test add primary key (id);
  • 删除主键:
语法:
alter table 表名 drop primary key;
举例:
alter table test drop primary key;

注意:在有自增的情况下,必须先删除自增,才可以删除主键。
删除自增:alter table test change id id int(7) unsigned zerofill not null;

5、mysql核心知识之全文索引的使用

简介:介绍什么是全文索引以及使用

  • 什么是全文索引?

全文索引是将存储在数据库中的文章或者句子等任意内容信息查找出来的索引,单位是。全文索引也是目前搜索引擎使用的一种关键技术。指定为 fulltex

  • 创建练习表的sql:
create table command (
id int(5) unsigned primary key auto_increment,
name varchar(10),
instruction varchar(60)
)engine=MyISAM; 
  • 插入数据sql:
insert into command values('1','ls','list directory contents');
insert into command values('2','wc','print newline, word, and byte counts for each file');
insert into command values('3','cut','remove sections from each line of files');
insert into command values('4','sort','sort lines of text files');
insert into command values('5','find','search for files in a directory  ierarchy');
insert into command values('6','cp','复制文件或者文件夹');
insert into command values('7','top','display Linux processes');
insert into command values('8','mv','修改文件名,移动');
insert into command values('9','停止词','is,not,me,yes,no ...'); 
  • 添加全文索引:

(1)创建表的时候创建全文索引。

(2)通过alter添加:alter table command add fulltext(instruction);

  • 使用全文索引:
select * from 表名 where match (字段名) against ('检索内容');
举例:
select * from command where match(instruction) against ('sections');
  • 查看匹配度:
select id,match(instruction) against ('directory')  from command;
  • 停止词:

出现频率很高的词,将会使全文索引失效。 比如 is、no、not、you、me、yes ...

  • in boolean mode 模式:

in boolean mode:意思是指定全文检索模式为布尔全文检索(简单可以理解为是检索方式)。

select * from 表名 where match (字段名) against ('检索内容' in boolean mode); 

注意点:使用通配符 * 时,只能放在词的后边,不能放前边。

select * from command where match(instruction) against ('directory*'  in boolean mode ); 
  • 删除全文索引:
alter table command drop index instruction;
  • 注意点总结:

• 一般情况下创建全文索引的字段数据类型为 char、varchar、text 。其它字段类型不可以;

• 全文索引不针对非常频繁的词做索引。比如 is,no,not,you,me,yes 这些,我们称之为停止词

• 对英文检索时忽略大小写 。

6、mysql核心知识之外键约束剖析

简介:解析什么是外键约束,以及有什么作用。

  • 什么是外键?

外键就是作用于两个表数据之间的链接的一列或多列,用来保证表与表之间的数据的完整性和准确性。

  • 添加外键约束:

语法:foreign key (字段名) references 关联的表名(关联表的字段名)

注意:主键跟外键的字段类型一定要相。

(1) 创建表时创建外键约束 :

CREATE TABLE `employee` (
`empno` int(11) NOT NULL COMMENT '雇员编号',
`ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(30) DEFAULT NULL,
`mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
`hiredate` date DEFAULT NULL COMMENT '雇佣日期',
`sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
`deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`),
foreign key (deptnu) references dept(deptnu)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(2)alter table的方法:

alter table employee add foreign key (deptnu) references dept(deptnu);
  • 删除外键约束:

注意:在干掉外键索引之前必须先把外键约束删除,才能删除索引。

mysql> alter table employee drop index deptnu; 
ERROR 1553 (HY000): Cannot drop index 'deptnu': needed in a foreign key constraint 
 
mysql> alter table employee drop foreign key employee_ibfk_1; 
Query OK, 0 rows affected (0.01 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
mysql> 
mysql> alter table employee drop index deptnu; 
Query OK, 0 rows affected (0.01 sec) 
Records: 0 Duplicates: 0 Warnings: 0  
  • 注意点总结:

(1)俩个表,主键跟外键的字段类型一定要相同;

(2)要使用外键约束表的引擎一定得是InnoDB引擎,MyISAM是不起作用的;

(3)在删除外键索引之前必须先把外键约束删除,才能删除索引。

7、mysql核心知识之联合索引

简介:详细介绍联合索引

  • 什么是联合索引?

联合索引又称组合索引或者复合索引,是建立在俩列或者多列以上的索引。

  • 怎么来创建联合索引?
alter table 表名 add index(字段1,字段2,字段3);
举例:
alter table test add index(username,servnumber,password);
  • 怎么删除联合索引?
alter table 表名 drop index 字段;
  • 为什么要使用联合索引,而不使用多个单列索引?

联合索引的效率远远高于单列索引。

  • 联合索引的最左原则
  • 注意点总结:
• 索引并非越多越好,过多的索引会增加数据的维护速度还有磁盘空间的浪费。
• 当表的数据量很大的时候,可以考虑建立索引。
• 表中经常查数据的字段,可以考虑建立索引。
• 想要保证表中数据的唯一性,可以考虑建立唯一索引。
• 想要保证俩张表中的数据的完整性跟准确性,可以考虑建立外键约束。
• 经常对多列数据进行查询时,可以考虑建立联合索引。

Comments | NOTHING

  游客,你好 修改资料

*邮箱和昵称必须填写