转自：https://www.jianshu.com/p/5748793856e7
1 MySQL索引
MySQL索引分类：普通索引，唯一索引，主键索引
1.1 添加索引
1.1.1 普通索引
创建表时添加索引：
create table table_name (属性名 数据类型,...,属性名 数据类型,
index |key [索引名] (属性名1 [(长度)] [asc|desc],...,
属性名n [(长度)] [asc|desc])
);
参数说明
长度是指定索引长度
asc|desc指定索引是升序还是降序
添加表普通索引：
create index idx_name on 表名(列名1 [(长度)] [asc|desc] ,...,
列名n [(长度)] [asc|desc]);
通过alter table 创建索引
alter table table_name
add index |key [索引名] (属性名 [(长度)] [asc|desc]
,...,
属性名n [(长度)] [asc|desc])
1.1.2 其他类型索引
唯一索引：unique index，把添加普通索引的index替换为unique index即可
全文索引：fulltext index，把添加普通索引的index替换为fulltext index即可
1.2 删除索引
命令如下：
drop index index_name on 表名;
或者如下：
alter table 表名 drop index index_name;
1.3 查看索引执行计划
explain或desc这个命令来查看一个这些SQL语句的执行计划，就是为了分析耗时，是否走索引
查看SQL是否使用索引，前面加上explain或desc即可，在Oracle中是explain plan for命令查看索引执行计划，还得紧接着查询表才有结果select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
explain select * from emp where name = 'Jefabc'
expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra：
概要描述：
id:选择标识符select_type:表示查询的类型。table:输出结果集的表partitions:匹配的分区type:表示表的连接类型possible_keys:表示查询时，可能使用的索引key:表示实际使用的索引key_len:索引字段的长度ref:列与索引的比较rows:扫描出的行数(估算的行数)filtered:按表条件过滤的行百分比Extra:执行情况的描述和说明
type表示表的连接类型，由上至下，效率越来越高
ALL: 全表扫描index: 索引全扫描range: 索引范围扫描，常用语<,<=,>=,between,in等操作ref使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中eq_ref: 类似ref，区别在于使用的是唯一索引，使用主键的关联查询const/system: 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询null:MySQL不访问任何表或索引，直接返回结果
虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为"select * from t where a = 1 and b in (1, 2) order by c";如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2
Extra:执行情况的描述和说明
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化Using index：表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
1.4 索引分类
MySQL索引种类有：普通索引、唯一索引(主键索引、唯一索引)、联合索引、全文索引、空间索引
MySQL中索引类型 :
FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX使用，不过目前只有CHAR、VARCHAR ，TEXT列上可以创建全文索引。HASH：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
只有Memory存储引擎显式支持哈希索引BTREE：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找
1.5 全文索引
1.5.1 创建全文索引(FULLTEXT INDEX)
创建表的同时创建全文索引
CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`title` varchar(255) DEFAULT NULL COMMENT '标题',
`body` text COMMENT '内容',
PRIMARY KEY (`id`),
FULLTEXT KEY `body` (`body`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
通过 ALTER TABLE 的方式来添加
ALTER TABLE `student`
ADD FULLTEXT INDEX ft_stu_name (`name`) #ft_stu_name是索引名，可以随便起
# 或者
ALTER TABLE `student`
ADD FULLTEXT ft_stu_name (`name`)
直接通过CREATE INDEX的方式
CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)
# 也可以在创建索引的时候指定索引的长度
CREATE FULLTEXT INDEX ft_email_name ON `student` (`name` (20))
1.5.2 删除全文索引
直接使用 DROP INDEX(注意：没有 DROP FULLTEXT INDEX 这种用法)
DROP INDEX full_idx_name ON tommy.girl ;
使用 ALTER TABLE的方式
ALTER TABLE tommy.girl DROP INDEX ft_email_abcd;
1.5.3 使用全文索引
跟普通索引稍有不同，使用全文索引的格式： MATCH (columnName) AGAINST (‘string’)
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')
当查询多列数据时：建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。
SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')
使用全文索引需要注意的是：(基本单位是词)
分词，全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符
1.5.4 MYSQL中与全文索引相关的几个变量
必须通过修改MySQL的配置文件来完成。通常修改最小搜索长度的值为2，首先打开MySQL的配置文件
在 [mysqld]的下面追加
innodb_ft_min_token_size = 2
ft_min_word_len = 2
ngram_token_size = 2
使用命令：mysql> SHOW VARIABLES LIKE 'ft%';ft就是FullText的简写
| 变量名字 | 变量值 | 变量说明 |
|---|---|---|
| ft_boolean_syntax | + -><()~*:""&| | 改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引 |
| ft_min_word_len | 4 | 最短的索引字符串，默认值为4，(通常改为1)修改后必须重建索引文件。重新建立索引命令：repair table tablename quick |
| ft_max_word_len | 84 | 最长的索引字符串，默认值为84，修改后必须重建索引文件 |
| ft_query_expansion_limit | 20 | 查询括展时取最相关的几个值用作二次查询 |
| ft_stopword_file | (built-IN) | 全文索引的过滤词文件，具体可以参考：MySQL全文检索中不进行全文索引默认过滤词 |
使用命令：show variables like "%ngram%"
| 变量名字 | 变量值 | 变量说明 |
|---|---|---|
| ngram_token_size | 2 | ngram解析器令牌长度即against()中字符串切分的最小字符长度 |
特别注意：50%的门坎限制(当查询结果很多，几乎所有记录都有，或者极少的数据，都有可能会返回非所期望的结果)：可用IN BOOLEAN MODE即可以避开50%的限制。
此时使用全文索引的格式就变成了：
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪' IN BOOLEAN MODE)
1.5.5 FT_BOOLEAN_SYNTAX (+ -><()~*:”“&|)使用的例子
+: 用在词的前面，表示一定要包含该词，并且必须在开始位置
+Apple匹配：Apple123, “tommy, Apple”-: 不包含该词，所以不能只用-yoursql这样是查不到任何row的，必须搭配其他语法使用
MATCH (girl_name) AGAINST (‘-林志玲 +张筱雨’)
匹配到： 所有不包含林志玲，但包含张筱雨的记录空(也就是默认情况)，表示可选的，包含该词的顺序较高
apple banana 找至少包含上面词中的一个的记录行；- +apple +juice 两个词均在被包含；
- +apple macintosh 包含词 “apple”，但是如果同时包含 “macintosh”，它的排列将更高一些；
- +apple -macintosh 包含 “apple” 但不包含 “macintosh”。
>:提高该字的相关性，查询的结果会排在比较靠前的位置<:降低相关性，查询的结果会排在比较靠后的位置" ": 双引号内作为整体不能拆词*: 通配符，只能接在词后面
先不使用 ><
select * from article where match(body) against('明天晴天 ' in boolean mode);
可以看到完全匹配的排的比较靠前。
单独使用 >
select * from article where match(body) against('明天晴天 > 好好学习 ' in boolean mode);
单独使用 <
select * from article where match(body) against('明天晴天 < 出去放风筝' in boolean mode);
同时使用><
select * from article where match(body) against('明天晴天 > 阴天了吧 <好好学习 > 喝奶茶去 < 出去放风筝' in boolean mode);
1.5.6 ( )：可以通过括号来使用字条件
+aaa +(>bbb <ccc) : 找到有aaa和bbb和ccc，aaa和bbb，或者aaa和ccc(因为bbb，ccc前面没有+，所以表示可有可无)，然后 aaa&bbb > aaa&bbb&ccc > aaa&ccc
1.5.7 ~ :将其相关性由正转负
~ :将其相关性由正转负，表示拥有该字会降低相关性，但不像「-」将之排除，只是排在较后面。
+apple ~macintosh : 先匹配apple，但如果同时包含macintosh，就排名会靠后。
1.5.8 * :通配符只能接在字符串后面
MATCH (girl_name) AGAINST ('+*ABC*') : 错误，不能放前面
MATCH (girl_name) AGAINST ('+张筱雨*') : 正确
1.5.9 " " :整体匹配，用双引号将一段句子包起来表示要完全相符，不可拆字
"tommy huang"可以匹配 tommy huang xxxxx 但是不能匹配 tommy is huang
1.6 索引优缺点
索引优点：
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引缺点：
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；空间方面：索引需要占物理空间，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
1.7 创建索引条件
适合建立索引列：
- 在经常需要搜索的列上，可以加快搜索的速度；
- 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
- 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
不适合建立索引列：
- 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
- 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
- 对于那些定义为
text,image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 - 当修改性能远远大于检索性能时，不应该创建索引。这是因为，
修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
2 MySQL虚拟表
虚拟表，顾名思义，就是实际上并不存在（物理上不存在），但是逻辑上存在的表。这样说很抽象，还是看一些实际的例子吧。
在mysql中，存在三种虚拟表：临时表、内存表和视图
2.1 临时表
2.1.1 定义和操作
临时表 ：是建立在系统临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作。 临时表的数据和表结构都储存在内存之中，退出时，其所占的空间会自动被释放。
创建临时表
定义字段
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL)
直接将查询结果导入临时表
CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name
查询临时表
select * from tmp_table
删除临时表
drop table tmp_table
2.1.2 临时表的应用
当工作在十分大的表上运行时，在实际操作中你可能会需要运行很多的相关查询，来获的一个大量数据的小的子集。较好的办法，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表，然后对这些表运行查询。
- 当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。
- 程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。
- 临时表默认的是
MyISAM，但是可以修改。 - 可以把一些经常访问的数据放到临时表中，这样访问时会快一些,因为数据是在服务器内存中,另外每次查询的时候，数据库都需要生成一些临时数据在临时表里
2.1.3 临时表使用注意事项
- 临时表只在当前连接可见，当这个连接关闭的时候，会自动drop。这就意味着你可以在两个不同的连接里使用相同的临时表名，并且相互不会冲突，或者使用已经存在的表，但不是临时表的表名。(当这个临时表存在的时候，存在的表被隐藏了，如果临时表被drop，存在的表就可见了)。
- 临时表只能用在 memory,myisam,merge,或者innodb引擎。
- 临时表不支持
mysql cluster(簇)。 - 在同一个
query语句中，你只能查找一次临时表。例如：下面的就不可用
SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'
如果在一个存储函数里，你用不同的别名查找一个临时表多次，或者在这个存储函数里用不同的语句查找，这个错误都会发生。
show tables语句不会列举临时表，但是会列出内存表。- 不能用
rename来重命名一个临时表。但是，你可以alter table代替
mysql>ALTER TABLE orig_name RENAME new_name;
2.2 内存表
2.2.1 定义和操作
内存表：表结构建在磁盘里，数据在内存里 ，当停止服务后，表中的数据丢失，而表的结构不会丢失。内存表也可以被看作是临时表的一种。
内存表的建立：
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP
注意： TYPE = HEAP必须要有
2.2.2 内存表的应用
内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库。
heap对所有用户的连接是可见的，这使得它非常适合做缓存。- 一旦服务器重启，所有
heap表数据丢失，但是heap表结构仍然存在，因为heap表结构是存放在实际数据库路径下的，不会自动删除。重启之后，heap将被清空，这时候对heap的查询结果都是空的。 - 如果
heap是复制的某数据表（创建普通用户表这些也会丢失），则复制之后所有主键、索引、自增等格式将不复存在，需要重新添加主键和索引，如果需要的话。 - 对于重启造成的数据丢失，有以下的解决办法：
- 在任何查询之前，执行一次简单的查询，判断
heap表是否存在数据，如果不存在，则把数据重新写入，或者DROP表重新复制某张表。这需要多做一次查询。不过可以写成include文件，在需要用该heap表的页面随时调用，比较方便。 - 对于需要该
heap表的页面，在该页面第一次且仅在第一次查询该表时，对数据集结果进行判断，如果结果为空，则需要重新写入数据。这样可以节省一次查询。 - 更好的办法是在
mysql每次重新启动时自动写入数据到heap，但是需要配置服务器，过程比较复杂，通用性受到限制。
- 在任何查询之前，执行一次简单的查询，判断
2.2.3 内存表的注意事项
heap不允许使用xxxTEXT和xxxBLOB数据类型；只允许使用=和<=>操作符来搜索记录（不允 许& amp; lt;、>、<=或>=）；只允许对非空数据列进行索引（not null）。
注：操作符<=>说明：NULL-safe equal，这个操作符和“=”操作符执行相同的比较操作，不过在两个操作码均为NULL时，其所得值为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。- 内存表可以通过
max_heap_table_size = 2048M来加大使用的内存。 - 内存表必须使用
memory存储引擎
2.3 视图
视图的工作机制：当调用视图的时候，才会执行视图中的sql，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。
视图这样设计有什么好处，节省空间，内容是总是一致的话，那么我们不需要维护视图的内容，维护好真实表的内容，就可以保证视图的完整性了
2.3.1 视图操作SQL
视图创建：
create view view_name as 查询语句;
示例例子
create view view_name as select * from TableName;
视图查看：
show table status [from db_name] [like '条件'];
此处db_name是数据库名字
查看视图定义信息
show create view view_name;
查看视图设计信息
desc | describe view_name;
查看所有视图信息
SELECT * FROM information_schema.`VIEWS`
视图修改：
create or replace view view_name as 查询语句;
alter view view_name as select * from OtherTable;
视图删除：
drop view view_name;
2.3.2 视图和临时表的区别
- 视图只是一条预编译的
SQL语句，并不保存实际数据，临时表是保存在tempdb中的实际的表 - 物理空间的分配不一样，视图不分配空间， 临时表会分配空间
- 视图是一个快照,是一个虚表，临时表是客观存在的表类型对象
Create TEMPORARY table，它们的结构一个是表、一个快照。可以把视图像象成联合表的快捷方式
2.3.3 修改视图和原表关系
如果是单表无论新增还是修改，改了视图会影响原表，改了原表会影响视图，如果是多表联合查询视图，一次只能改一个表的数据，如果多了就报错如下提示：
Can not modify more than one base table through a join view 'test.test_view'
3 游标
游标使用步骤：
- 声明游标
DECLARE cursor_name CURSOR FOR select查询语句; - 打开游标
OPEN cursor_name - 使用游标
FETCH cursor_name INTO var_name [,var_name] … - 关闭游标
CLOSE cursor_name
使用例子
DROP PROCEDURE IF EXISTS emplayee_count;
DELIMITER $
#创建存储过程
CREATE PROCEDURE emplayee_count (OUT NUM INTEGER)
BEGIN
#声明变量
DECLARE emplayee_sal INTEGER;
DECLARE flag INTEGER;
#声明游标
DECLARE cursor_emplayee
CURSOR FOR SELECT sal FROM t_employee;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
#设置结束标志
SET flag=0;SET NUM=0;‘
#打开游标
OPEN cursor_emplayee;
#遍历游标指向的结果集
FETCH cursor_emplayee INTO emplayee_sal;
WHILE flag<>1 DO
IF emplayee_sal >999 THEN
SET num=num+1;
END IF;
FETCH cursor_emplayee INTO emplayee_sal;
END WHILE;
#关闭游标
CLOSE cursor_emplayee;
END$
DELIMITER ;