9.9 连表查询
【实例】通过例子来熟悉连表查询的概念
# 第一步：建表
# 建立英雄职业分类表格
create table classification(
id int,
name varchar(20)
);
# 英雄属性表格
create table hero(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
cd int, # 技能时间
cha_id int # 英雄职业
);
# 插入数据
insert into classification values
(200,'法师'),
(201,'战士'),
(202,'射手'),
(203,'辅助');
insert into hero(name,sex,cd,cha_id) values
('莫甘娜','female',40,200),
('盖伦','male',50,201),
('赵信','male',48,201),
('女警','female',35,202),
('梦魇','male',30,200),
('阿卡丽','female',28,204)
;
# 第一步：建表
# 建立英雄职业分类表格
create table classification(
id int,
name varchar(20)
);
# 英雄属性表格
create table hero(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
cd int, # 技能时间
cha_id int # 英雄职业
);
# 插入数据
insert into classification values
(200,'法师'),
(201,'战士'),
(202,'射手'),
(203,'辅助');
insert into hero(name,sex,cd,cha_id) values
('莫甘娜','female',40,200),
('盖伦','male',50,201),
('赵信','male',48,201),
('女警','female',35,202),
('梦魇','male',30,200),
('阿卡丽','female',28,204)
;笛卡尔积
交叉连接实例：在没有任何条件连接的情况下联合两张表进行查询，结果会以笛卡尔积的形式显示
mysql> select * from hero,classification;
+----+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+----+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 1 | 莫甘娜 | female | 40 | 200 | 201 | 战士 |
| 1 | 莫甘娜 | female | 40 | 200 | 202 | 射手 |
| 1 | 莫甘娜 | female | 40 | 200 | 203 | 辅助 |
| 2 | 盖伦 | male | 50 | 201 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 2 | 盖伦 | male | 50 | 201 | 202 | 射手 |
| 2 | 盖伦 | male | 50 | 201 | 203 | 辅助 |
| 3 | 赵信 | male | 48 | 201 | 200 | 法师 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 202 | 射手 |
| 3 | 赵信 | male | 48 | 201 | 203 | 辅助 |
| 4 | 女警 | female | 35 | 202 | 200 | 法师 |
| 4 | 女警 | female | 35 | 202 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 4 | 女警 | female | 35 | 202 | 203 | 辅助 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| 5 | 梦魇 | male | 30 | 200 | 201 | 战士 |
| 5 | 梦魇 | male | 30 | 200 | 202 | 射手 |
| 5 | 梦魇 | male | 30 | 200 | 203 | 辅助 |
| 6 | 阿卡丽 | female | 28 | 204 | 200 | 法师 |
| 6 | 阿卡丽 | female | 28 | 204 | 201 | 战士 |
| 6 | 阿卡丽 | female | 28 | 204 | 202 | 射手 |
| 6 | 阿卡丽 | female | 28 | 204 | 203 | 辅助 |
+----+-----------+--------+------+--------+------+--------+
24 rows in set (0.00 sec)
mysql> select * from hero,classification;
+----+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+----+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 1 | 莫甘娜 | female | 40 | 200 | 201 | 战士 |
| 1 | 莫甘娜 | female | 40 | 200 | 202 | 射手 |
| 1 | 莫甘娜 | female | 40 | 200 | 203 | 辅助 |
| 2 | 盖伦 | male | 50 | 201 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 2 | 盖伦 | male | 50 | 201 | 202 | 射手 |
| 2 | 盖伦 | male | 50 | 201 | 203 | 辅助 |
| 3 | 赵信 | male | 48 | 201 | 200 | 法师 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 202 | 射手 |
| 3 | 赵信 | male | 48 | 201 | 203 | 辅助 |
| 4 | 女警 | female | 35 | 202 | 200 | 法师 |
| 4 | 女警 | female | 35 | 202 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 4 | 女警 | female | 35 | 202 | 203 | 辅助 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| 5 | 梦魇 | male | 30 | 200 | 201 | 战士 |
| 5 | 梦魇 | male | 30 | 200 | 202 | 射手 |
| 5 | 梦魇 | male | 30 | 200 | 203 | 辅助 |
| 6 | 阿卡丽 | female | 28 | 204 | 200 | 法师 |
| 6 | 阿卡丽 | female | 28 | 204 | 201 | 战士 |
| 6 | 阿卡丽 | female | 28 | 204 | 202 | 射手 |
| 6 | 阿卡丽 | female | 28 | 204 | 203 | 辅助 |
+----+-----------+--------+------+--------+------+--------+
24 rows in set (0.00 sec)【结论】实际使用时应该是不会希望在笛卡尔积表中查找信息，毕竟有大量的信息冗余。我们希望的是能够通过一种连接关系，将两张有关系的表组合成一张表。
内连接
内连接，实现两张表中相互匹配的行进行连接，就像hero表中的cha_id与classification里的id是相匹配，匹配格式如下：
select * from 表1 inner join 表2 on 条件
select * from 表1，表2，表3 where 表1.字段1=表2.字段1 and 表2.字段2 = 表3.字段1mysql> select * from hero inner join classification on hero.cha_id = classification.id;
# 如果表格的长度比较长不方便操作的话，给他重命名以下
mysql> select * from hero inner join classification as cla on hero.cha_id = cla.id;
+----+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+----+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
+----+-----------+--------+------+--------+------+--------+
mysql> select * from hero inner join classification on hero.cha_id = classification.id;
# 如果表格的长度比较长不方便操作的话，给他重命名以下
mysql> select * from hero inner join classification as cla on hero.cha_id = cla.id;
+----+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+----+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
+----+-----------+--------+------+--------+------+--------+左外连接
连接的两张表中以左侧的表为准，右侧的表匹配左侧的表，优先显示左表全部记录，显示顺序以左侧为主
用法
select * from 表1 left join 表2 on 条件mysql> select * from hero left join classification as cla on hero.cha_id = cla.id;
+----+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+----+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 6 | 阿卡丽 | female | 28 | 204 | NULL | NULL |
+----+-----------+--------+------+--------+------+--------+
mysql> select * from hero left join classification as cla on hero.cha_id = cla.id;
+----+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+----+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 6 | 阿卡丽 | female | 28 | 204 | NULL | NULL |
+----+-----------+--------+------+--------+------+--------+【结论】左侧的表全部显示，右侧匹配左侧，缺失项为NULL
右外连接
连接的两张表中以右侧的表为准，左侧的表匹配右侧的表，优先显示左表全部记录，显示顺序以左侧为主
select * from 表1 right join 表2 on 条件mysql> select * from hero right join classification as cla on hero.cha_id = cla.id;
+------+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+------+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| NULL | NULL | NULL | NULL | NULL | 203 | 辅助 |
+------+-----------+--------+------+--------+------+--------+
mysql> select * from hero right join classification as cla on hero.cha_id = cla.id;
+------+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+------+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| NULL | NULL | NULL | NULL | NULL | 203 | 辅助 |
+------+-----------+--------+------+--------+------+--------+全外连接
根据匹配项连接两张表，显示所有内容，缺失项以NULL填充
select * from 表1 full join 表2 on 条件【注意】目前各个DBMS对外连接的支持星泪不尽相同，MySQL仅仅支持左右两个外连接，并不支持全外连接
在MySQL中实现全外连接
select * from 表1 right join 表2 on 条件
union
select * from 表1 left join 表2 on 条件
+------+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+------+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 6 | 阿卡丽 | female | 28 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 辅助 |
+------+-----------+--------+------+--------+------+--------+
select * from 表1 right join 表2 on 条件
union
select * from 表1 left join 表2 on 条件
+------+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+------+-----------+--------+------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 6 | 阿卡丽 | female | 28 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 辅助 |
+------+-----------+--------+------+--------+------+--------+符合条件的连接查询
# 找出cd时间大于40的英雄名称以及该英雄的职业属性
mysql> select hero.name,cla.name,hero.cd from hero inner join classification as cla on hero.cha_id = cla.id where cd> 40;
+--------+--------+------+
| name | name | cd |
+--------+--------+------+
| 盖伦 | 战士 | 50 |
| 赵信 | 战士 | 48 |
+--------+--------+------+
# 以内连接的方式查询hero和classification表，并且以age字段的升序方式显示
mysql> select * from hero inner join classification as cla on hero.cha_id = cla.id order by cd asc;
+----+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+----+-----------+--------+------+--------+------+--------+
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
+----+-----------+--------+------+--------+------+--------+
# 找出cd时间大于40的英雄名称以及该英雄的职业属性
mysql> select hero.name,cla.name,hero.cd from hero inner join classification as cla on hero.cha_id = cla.id where cd> 40;
+--------+--------+------+
| name | name | cd |
+--------+--------+------+
| 盖伦 | 战士 | 50 |
| 赵信 | 战士 | 48 |
+--------+--------+------+
# 以内连接的方式查询hero和classification表，并且以age字段的升序方式显示
mysql> select * from hero inner join classification as cla on hero.cha_id = cla.id order by cd asc;
+----+-----------+--------+------+--------+------+--------+
| id | name | sex | cd | cha_id | id | name |
+----+-----------+--------+------+--------+------+--------+
| 5 | 梦魇 | male | 30 | 200 | 200 | 法师 |
| 4 | 女警 | female | 35 | 202 | 202 | 射手 |
| 1 | 莫甘娜 | female | 40 | 200 | 200 | 法师 |
| 3 | 赵信 | male | 48 | 201 | 201 | 战士 |
| 2 | 盖伦 | male | 50 | 201 | 201 | 战士 |
+----+-----------+--------+------+--------+------+--------+9.10 子查询
- 将一个查询语句前台到另一个查询语句中；
- 内层查询语句的结果可以作为外层查询语句的条件；
- 子查询中可以包含IN，NOT IN，ANY，ALL，EXISTS和 NOT EXISTS等关键字；EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False，当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
- 还可以包含比较运算符 ＝ ！＝ ＞ ＜
【举例1】
# 带in关键字的子查询
# 查询cd时间在40秒以上的英雄职业有哪些
第一步：通过hero查询cd时间在40秒的职业id
mysql> select cha_id from hero where cd>40;
+--------+
| cha_id |
+--------+
| 201 |
| 201 |
+--------+
第二步：从第一步的查询到的id在classification里找到对应的name
mysql> select name from classification where id in (201);
+--------+
| name |
+--------+
| 战士 |
+--------+
# 将两部结合在一起
mysql> select name from classification where id in (select cha_id from hero where cd>40);
+--------+
| name |
+--------+
| 战士 |
+--------+【举例2】
# 查询平均cd在30以上的英雄职业,平均cd
【方法一】先查平均cd>30的职业id，平均cd，查出结果在之后再连表
mysql> select c.name,avg_cd from classification as c inner join (select avg(cd) as avg_cd ,cha_id from hero group by cha_id having avg(cd) > 30 )as tem on tem.cha_id = c.id;
+--------+---------+
| name | avg_cd |
+--------+---------+
| 法师 | 35.0000 |
| 战士 | 49.0000 |
| 射手 | 35.0000 |
+--------+---------+
3 rows in set (0.00 sec)
【方法二】先查各职业的平均cd，查出结果在之后再连表筛选
mysql> select c.name,avg_cd from classification as c inner join (select avg(cd) as avg_cd,cha_id from hero group by cha_id) as tem on tem.cha_id = c.id where avg_cd > 30;
+--------+---------+
| name | avg_cd |
+--------+---------+
| 法师 | 35.0000 |
| 战士 | 49.0000 |
| 射手 | 35.0000 |
+--------+---------+【注意】
如果最终需要的结果只出现在一张表中,可以用子查询解决问题
如果最终需要的结果出现在两张表中,那么最后用的一定是连表查询
能用连表的时候就用连表，效率比子查询快
【举例三】
# 运算符的使用
# 查看法师职业有谁
mysql> select name from hero where cha_id = (select id from classification where name = '法师');
+-----------+
| name |
+-----------+
| 莫甘娜 |
| 梦魇 |
+-----------+
# 查询大于所有人平均cd的英雄名与cd
mysql> select name,cd from hero where cd > (select avg(cd) from hero);
+-----------+------+
| name | cd |
+-----------+------+
| 莫甘娜 | 40 |
| 盖伦 | 50 |
| 赵信 | 48 |
+-----------+------+
# 扩展练习：查询大于职业平均cd的英雄名、cd
mysql> select hero.name,hero.cd from hero inner join (select cha_id,avg(cd) as avg_cd from hero group by cha_id) as tem on tem.cha_id = hero.cha_id where tem.avg_cd <hero.cd;
+-----------+------+
| name | cd |
+-----------+------+
| 莫甘娜 | 40 |
| 盖伦 | 50 |
+-----------+------+【举例四】
# 带EXISTS关键字的子查询
# EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记# 录。而是返回一个真假值。True或False当返回True时，外层查询语句将进行查询 # 当返回值为False时，外层查询语句不进行查询
# classification表中存在203，True
mysql> select * from hero where exists (select id from classification where id = 203);
+----+-----------+--------+------+--------+
| id | name | sex | cd | cha_id |
+----+-----------+--------+------+--------+
| 1 | 莫甘娜 | female | 40 | 200 |
| 2 | 盖伦 | male | 50 | 201 |
| 3 | 赵信 | male | 48 | 201 |
| 4 | 女警 | female | 35 | 202 |
| 5 | 梦魇 | male | 30 | 200 |
| 6 | 阿卡丽 | female | 28 | 204 |
+----+-----------+--------+------+--------+
# classification表中存在205，False
mysql> select * from hero where exists (select id from classification where id = 205);
Empty set (0.00 sec)总结
select干了什么：首先是根据条件对每行记录进行检索，再根据其他条件进行字段筛选
数据库数据导入
# 准备表、记录
mysql> create database 数据库名;
mysql> use 数据库名;
mysql> source init.sql路径；9.11 检索原理
检索初识
索引在MySQL中是一种“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的检索性能，非常关键，尤其是当表中的数据量越大，索引对于性能的提升越显得重要。
索引优化是对查询性能优化最有效的手段。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，要查某个字，如果不使用音序表，则需要从几百页中逐页去查，这个开销是巨大的。
索引的原理
通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
磁盘IO与预读
磁盘IO是非常高昂的操作，计算机操作系统为此做了一些优化，当一次IO时，不仅读取当前磁盘地址的数据，还会把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理可知，当计算机访问一个地址的数据时，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是读取一页内的数据，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
索引的数据结构
B+树
数据只存储在叶子节点中；索引的区间范围存放在根节点和枝节点当中，每次IO操作可以读取更多的数据
在叶子节点之间接入双向地址连接，更方便在叶子节点之间进行数据的读取
【性质】
- 索引字段尽量要小。硬盘中一个数据页的大小是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低，检索效率就越高。索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高
- 索引的最左匹配原则。b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的。
索引原理
innodb索引
【聚集索引clustered index】
使用主键构建B+树，数据也是索引的一部分；如果没有定义主键，mysql会把第一个 唯一+为空的字段设为主键，也就作为聚集索引；每张表只能有一个聚集索引；
聚集索引的优势：一是利用逐渐的排序和查找速度非常快，叶子结点的数据就是索要查找的数据，又因为B+书的索引是双向链表，所以可以很快的找到最后一个数据页内的数据；二是范围查询，如果要通过主键查找某一范围内的数据，只需要通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。
【辅助索引secondary index 】
除了主键之外所有的索引都是辅助索引，也叫非聚集索引，与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。
辅助索引往往是分两步查询到数据，第一步是找到指向主键索引的主键，第二步通过主键索引来找到一个完整的数据。
聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息
聚集索引
1.纪录的索引顺序与物理顺序相同
因此更适合between and和order by操作
2.叶子结点直接对应数据
从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索引
非聚集索引
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引，需要更多磁盘和内容
多个索引会影响insert和update的速度【回表】
只查询一个索引有时并不能解决查询中的问题，仍需到具体的表中获取真正的数据
myisam索引
辅助索引 除了主键之外所有的索引都是辅助索引
索引的种类
primary key的创建自带索引效果 非空 + 唯一 + 聚集索引
unique 唯一约束的创建也自带索引效果 唯一 + 辅助索引
index 普通的索引 辅助索引
索引的两大类型
#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；索引的创建与删除
#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字;【实例】
#方式一
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index ix_name(name) #index没有key
);
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index(name) #index没有key
);
#方式二
create index ix_age on t1(age);
#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);
#查看
mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
UNIQUE KEY `uni_id` (`id`),
KEY `ix_name` (`name`),
KEY `ix_age` (`age`),
KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1索引的优点：
查找速度快
索引的缺点：
浪费空间，拖慢写入的速度（有可能触发树结构调整，不要创建无用的索引）
【总结】
创建索引之后查询效率大幅度提高
创建索引之后占用的硬盘资源也大幅度提高
正确的使用索引***
没有命中索引的情况，一定要注意：
1.查询的字段不是创建索引的字段；
2.在条件中不能带运算或函数；
3.如果创建索引的列中的数据重复率比较高也不能发挥索引的作用（临界值比例在10:1左右,例如：性别，部门之类的接没必要建立索引）
4.数据对应的范围如果太大，也不能有效率用索引(between and , < , >, != , not in 之类的检索的范围如果差距比较大的话，就很难命中索引)
5.like把%放在前边，直接不能命中索引，而是全表扫描，%放在后面，可以命中索引
6.多条件：and情况下，只要有一个条件列是索引列，就可以命中索引；or的情况下，必须所有的条件列都是索引列才能命中索引
7.联合索引***：where a = XX and b = YYY 情况下：a和b分别创建了索引，正常情况下只能命中树结构比较健康的索引，另一个则不会命中；可以创建联合索引，
# 创建联合索引
create index ind_name on 表名（字段1，字段2）
# 在多个条件相连的情况下，使用联合索引的效率高于单字段索引的连用
# 多个索引连用时，条件中从哪一个字段开始出现了范围，索引就从哪里开始失效，所以创建索引时可以把范围条件的往后放
# 联合索引在使用时遵循最左前缀原则
# 联合索引中只有使用and才能生效，使用or不生效【其他注意事项】
- 避免使用select *
- 使用count(*)
- 创建表时尽量使用 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合两个名词
# 覆盖索引 从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
extra 显示 usingindex
select count(id) from 表;
select id from 表 where id <20;
# 索引合并
单独创建索引，使用时临时合并使用，extra显示 using unionMySQL 神器 explain
# 执行计划
explain select * from 表名 where 条件；
可以查看是否命中了索引，以及命中索引的类型
慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径
配置：
- 内存
show variables like '%query%';
show variables like '%queries%';
set global 变量名 = 值
- 配置文件
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf内容：
slow_query_log = ON
slow_query_log_file = D:/....
注意：修改配置文件之后，需要重启服务慢日志管理
MySQL可以通过配置文件开启慢日志记录；如果数据库在自己手里可以自己开；在DBA手里的话，可以让他开一下
慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径
配置：
- 内存
show variables like '%query%';
show variables like '%queries%';
set global 变量名 = 值
- 配置文件
mysqld --defaults-file='日志文件路径'
my.conf内容：
slow_query_log = ON
slow_query_log_file = 路径
注意：修改配置文件之后，需要重启服务慢查询优化
0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析七表联查速度慢怎么办？***
1.表结构上来说：一是用固定长度的数据类型代替可变长度数据类型；二是把固定长度的字段放在表的前边
2.从数据的角度来说：如果表中的数据越多，查询效率越低；列多进行垂直分表，行多进行水平分表，把经常查询的数据放在一个表里
3.从SQL语句角度来说：一是尽量把条件写的详细一些，where条件多做筛选；二是多变查询尽量使用连表查询代替子查询；三是创建有效的索引，规避无效的索引
4.配置角度来说，开启慢日志查询，确认具体慢的sql语句，进行代码优化
5.数据库搭集群，做数据读写分离，一读多写
导入导出数据
导出：mysqldump -u -p 库名 > 导出位置
导入:进入mysql 切换到要恢复数据的库下面
备份库： mysqldump -u -p --database 导出位置
开启事务
【事务的定义】
- 一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务
- 一个完整业务需要批量的DML语句共同组成
- DML语句才有事务，业务逻辑不同，DML语句的个数也不同
begin; # 开启事务，挂起自动提交
select * from emp where id = 1 for update; # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务，成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
rollback ; # 回滚，失败的结束，将所有的DML语句操作历史记录全部清空【特性】
隔离性：事物之间不会相互影响
稳定性：数据库在事务执行前后状态都必须是稳定的
原子性：构成事务的所有DML语句必须是一个逻辑单元，要么全部执行，要么全不执行
持久性：事务执行成功后必须全部写入磁盘
python连接数据库
安装PyMySQL模块
import pymysql
db = pymysql.connect("数据库ip","用户","密码","数据库" ) # 打开数据库连接
cursor.execute("SELECT VERSION()") # 使用 execute() 方法执行 SQL 查询
cur = db.cursor() # 游标，数据库操作顺序执行
data1 = cur.fetchone() # 使用 fetchone() 方法获取单条记录
data2 = cur.fetchmany(值) # 使用 fetchmany() 方法获取相应数值的记录
data3 = cur.fetchall() # 使用 fetchmall() 方法获取所有的记录
print ("Database version : %s " % data1)
db.commit() # 进行增删改操作时提交数据给数据库
cur.close() # 关闭游标
db.close() # 关闭数据库连接
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
