MySQL（三）：索引与存储引擎

作者: JBryan | 来源:发表于2020-02-27 22:14 被阅读0次

MySQL和ES的索引对比
MySQL 索引和 SQL 调优
索引问题
从 BAT 面试回来，我总结了这三类 MySQL 高频面试题
MySQL索引实现及优化
总结mysql索引失效的N种情况
mysql自我小结
MySQL（三）：索引与存储引擎
学习的技术栈，技术书籍必看for me
MYSQL索引的概念

1.存储引擎

数据库引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同的数据库引擎，可以获得特定的功能。
查看数据库支持的引擎：
show engines;
查看当前数据的引擎：
show create table 表名
查看当前库所有表的引擎：
show table status
建表时指定引擎：
create table yingqin (id int,name varchar(20)) engine='InnoDB';
修改表的引擎：
alter table 表名 engine='MyiSAm';
MyISAM与InnoDB的区别
MyISAM：支持全文索引（full text）;不支持事务;表级锁;保存表的具体行数;崩溃恢复不好。
Innodb：支持事务;以前的版本是不支持全文索引，但在5.6之后的版本就开始支持这个功能了;行级锁（并非绝对，当执行sql语句时不能确定范围时，也会进行锁全表例如： update table set id=3 where name like 'a%';）;不保存表的具体行数;崩溃恢复好。
使用MyISAM场景：
一般来说MyISAM不需要用到事务的时候。
做很多count计算。
使用InnoDB场景：
可靠性要求高的，或者要求支持事务。
想要用到外键约束的时候。
推荐用InnoDB

2.常用索引

索引是一个单独的，存储在磁盘中的数据库结构，它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找出在某列或多列中有特定值的行。
优点：通过创建唯一索引，来保证数据库表中的每一行数据的唯一性。可以加快数据的检索速度。可以保证表数据的完整性与准确性。
缺点：索引需要占用物理空间。对表中的数据进行改动时，索引也需要跟着动态维护，降低了数据的维护速度。
索引常见类型：

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

3.普通索引与唯一索引

普通索引（index）顾名思义就是各类索引中最为普通的索引，主要任务就是提高查询速度。其特点是允许出现相同的索引内容，允许空（null）值。
唯一索引：（unique）顾名思义就是不可以出现相同的索引内容，但是可以为空（null）值。
3.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;

直接为表添加索引

语法：
     alter table 表名 add index 索引名称 (字段名称);
 eg: 
     alter table test add unique unique_username (username);

查看索引
语法：show index from 表名
eg: show index from test
删除索引
语法：alter table 表名 drop index 索引名;
eg：alter table test drop index createtime;

4.主键索引

把主键添加索引就是主键索引，它是一种特殊的唯一索引，不允许有空值，而唯一索引（unique是允许为空值的）。指定为“PRIMARY KEY”。主键：主键是表的某一列，这一列的值是用来标志表中的每一行数据的。注意：每一张表只能拥有一个主键。
创建主键：
1.创建表的时候创建
2.直接为表添加主键索引
语法：alter table 表名 add primary key (字段名);
eg：alter table test add primary key (id);

5.全文索引

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

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 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('dir' in boolean mode);
注意：
1、一般情况下创建全文索引的字段数据类型为 char、varchar、text 。其它字段类型不可以。
2、全文索引不针对非常频繁的词做索引。比如is，no，not，you，me，yes这些，我们称之为停止词。
3、对英文检索时忽略大小写。

6.外键约束

外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性。
添加外键约束：
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.直接添加外键
删除外键约束：在干掉外键索引之前必须先把外键约束删除，才能删除索引。

mysql> show create table employee;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `empno` int(11) NOT NULL COMMENT '雇员编号',
  `ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
  `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`),
  KEY `deptnu` (`deptnu`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`deptnu`) REFERENCES `dept` (`deptnu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

mysql> alter table drop foreign key employee_ibfk_1;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'drop foreign key employee_ibfk_1' at line 1
mysql> alter table employee drop foreign key employee_ibfk_1;
Query OK, 14 rows affected (0.03 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> alter table employee drop index deptnu;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employee |          0 | PRIMARY  |            1 | empno       | A         |          14 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

注意：
1.两个表，主键跟外键的字段类型一定要相同。
2.要使用外键约束表的引擎一定得是InnoDB引擎，MyISAM是不起作用的。
3.在干掉外键索引之前必须先把外键约束删除，才能删除索引。

7.联合索引

联合索引又称组合索引或者复合索引，是建立在俩列或者多列以上的索引。
创建联合索引：
alter table 表名 add index(字段1,字段2,字段3);
alter table test add index(username,servnumber,password);
索引总结：
1.索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费。
2.当表的数据量很大的时候，可以考虑建立索引。
3.表中经常查数据的字段，可以考虑建立索引。
4.想要保证表中数据的唯一性，可以考虑建立唯一索引。
5.想要保证俩张表中的数据的完整性跟准确性，可以考虑建立外键约束。
6.经常对多列数据进行查询时，可以考虑建立联合索引。

MySQL和ES的索引对比
[toc] MySQL索引实现在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，...
MySQL 索引和 SQL 调优
MySQL索引 MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引...
索引问题
1.索引存储分类索引是在MySQL的存储引擎层实现的，每个存储引擎的索引不一定相同。MySQL提供以下4种索引： ...
从 BAT 面试回来，我总结了这三类 MySQL 高频面试题
一：MySQL存储与索引有关问题 MySQL 有哪些存储引擎啊？都有什么区别？Float、Decimal 存储金额...
MySQL索引实现及优化
MySQL索引实现在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论...
总结mysql索引失效的N种情况
mysql中，索引是存储引擎实现的，不同的存储引擎索引的工作方式不一样，由于mysql默认的存储引擎为InnoDB...
mysql自我小结
MySql索引存储引擎查询mysql支持的引擎：show engines 常用引擎： MyISAM，InnoD...
MySQL（三）：索引与存储引擎
1.存储引擎数据库引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同...
学习的技术栈，技术书籍必看for me
《高性能MySQL》《数据库索引设计与优化》《MySQL技术内幕：InnoDB存储引擎》《数据结构与算法分析...
MYSQL索引的概念
一、什么是索引？ 1、索引是帮助MYSQL高效获取数据的数据结构； 2、索引在存储引擎中实现，每种存储引擎的索引都...