Mysql索引与存储引擎

目录

 

mysql存储引擎

索引

普通索引与唯一索引

创建表时创建，

直接为表添加索引

直接创建索引

查看索引

如何删除索引

主键索引

创建主键：

全文索引

添加全文索引：

使用全文索引：

查看匹配度:​

停止词：

in boolean mode 模式：

外键：

添加外键约束

联合索引：

---

mysql存储引擎

什么是？

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

如何查看

如何查看数据库支持的引擎
show engines;
查看当前数据的引擎：
show create table 表名\G
查看当前库所有表的引擎：
show table status\G
 

建表时指定

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

修改：

alter table 表名 engine='MyiSAm';
 

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

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

总结：什么时候选择什么引擎比较好

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

---

索引

什么是？（书的目录  提高检索效率）

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

优点

• 通过创建唯一索引，来保证数据库表中的每一行数据的唯一性。
• 可以加快数据的检索速度。
• 可以保证表数据的完整性与准确性

缺点

• 索引需要占用物理空间。
• 对表中的数据进行改动时，索引也需要跟着动态维护，降低了数据的维护速度。

 

索引的常见类型

• index：普通索引
• unique：唯一索引
• primary key：主键索引
• foreign key：外键索引
• fulltext: 全文索引
• 组合索引

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;

 

生成百万甚至千万级别表的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     （会生成sql.txt文件）
进行插数操作：source /home/dazhu/sql.txt

  

---

普通索引与唯一索引

索引的常见类型

• index：普通索引
• unique：唯一索引
• primary key：主键索引
• foreign key：外键索引
• fulltext: 全文索引
• 组合索引

普通索引：

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

唯一索引：

唯一索引：（unique）顾名思义就是不可以出现相同的索引内容，但是可以为空（null）值

如何创建索引？

 

创建表时创建，

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

直接为表添加索引

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

直接创建索引

语法：create index 索引 on 表名 (字段名);
eg：create index index_createtime on test (createtime);

查看索引

语法：show index from 表名\G
eg: show index from test\G

如何删除索引

语法：drop index 索引名称 on 表名;
eg：drop index unique_username on test;
 

语法：alter table 表名 drop index 索引名;
eg：alter table test drop index createtime;
未加索引（左）

加完索引（右）

---

主键索引

把主键添加索引就是主键索引，它是一种特殊的唯一索引，不允许有空值，而唯一索引（unique是允许为空值的）。

指定为“PRIMARY KEY”

主键：主键是表的某一列，这一列的值是用来标志表中的每一行数据的。
注意：每一张表只能拥有一个主键

创建主键：

1）创建表的时候创建

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

2）直接为表添加主键索引
语法：alter table 表名 add primary key (字段名);
eg：alter table test add primary key (id);

删除主键：
语法：
alter table 表名 drop primary key;
eg：alter table test drop primary key;
注意：在有自增的情况下，必须先删除自增，才可以删除主键
删除自增：alter table test change id id int(7) unsigned zerofill not null;

---

全文索引

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

create table command (
id int(5) unsigned primary key auto_increment,
name varchar(10),
instruction varchar(60)
)engine=MyISAM;

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 hierarchy');
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 ...');

添加全文索引：

创建表的时候创建全文索引
通过alter添加

alter table 表名 add fulltext(字段名);
alter table command add fulltext(instruction);

使用全文索引：

select * from 表名 where match (字段名) against ('检索内容');
select * from command where match(instruction) against ('sections');

用模糊查询，当数据量大了以后，查询的效率会变的很慢。

全文索引就能够解决该问题。

 

查看匹配度:

select * from command where match(instruction) against ('directory');

根据匹配度进行展示的

停止词：

出现频率很高的词，将会使全文索引失效

in boolean mode 模式：

in boolean mode：意思是指定全文检索模式为布尔全文检索（简单可以理解为是检索方式）
select * from 表名 where match (字段名) against ('检索内容' in boolean mode);

注意：

使用通配符*时，只能放在词的后边，不能放前边。

+       表示一定要出现的意思；

“  ”    或的意思

-        一定不能出现的意思

删除全文索引：

alter table command drop index instruction;

注意点总结：

• 1、一般情况下创建全文索引的字段数据类型为 char、varchar、text 。其它字段类型不可以（一个词一个词的英文）
• 2、全文索引不针对非常频繁的词做索引。

      比如is，no，not，you，me，yes这些，我们称之为停止词
• 3、对英文检索时忽略大小写



 

---

外键：

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

两张表将员工建立联系

 

添加外键约束

语法：foreign key (字段名) references 关联的表名(关联表的字段名)
注意：主键跟外键的字段类型一定要相同                                 （一般都是外键关联主键）

create table的方法：
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;

employee关联deptnu

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>
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）在干掉外键索引之前必须先把外键约束删除，才能删除索引
 

谁做了外键约束，谁就是孙子受到爷爷的约束

 

---

联合索引：

联合索引又称组合索引或者复合索引，是建立在俩列或者多列以上的索引。
怎么来创建联合索引？
alter table 表名 add index(字段1,字段2,字段3);
alter table test add index(username,servnumber,password);

删除联合索引？
alter table test drop index username;

为什么要使用联合索引，而不使用多个单列索引？
联合索引的效率远远高于单列索引

mysql会使用最有效的方法进行查询

联合索引的最左原则（使用最左边的索引进行检索）

---

注意点总结：

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

---