本文详细介绍了MySQL中的主键概念，包括主键的特性、主键自增的使用，以及表字段的注释方法。同时，文章深入探讨了数据库事务，包括事务的四大特性、事务隔离级别及其影响，并提供了实际例子来说明事务并发不进行事务隔离可能导致的问题。此外，还讲解了数据库的表设计原则和MySQL中管理事务的命令。
MySQL主键与事务
一、MySQL主键和表字段的注释
二、表设计
三、数据库事务概述
四、别名和去重
五、where语句
六、MySQL常用运算符
七、MySQL索引原理以及查询优化
玩游戏：用户名 密码
京东、淘宝账号：用户名 密码
此用户已被使用、此用户已存在
| 用户名 | 密码 | 余额 |
| 张飞 | 123123 | 10000 |
| 李四 | 123123 | 20000 |
| 用户名(主键) | 密码 | 余额 |
| 张飞 | 123123 | 10000 |
| 张飞(报错) | 123123 | 10000 |
当把某一列设置成主键的时候，那么这一列中的数据是不可以重复的
主键及自增
每一张表通常会有一个且只有一个主键 来表示每条数据的唯一性
主键其实是对某一个字段的 约束
-主键特性：1.值不能重复2.非空(不能赋值为null)
-主键约束写法：
create table student (id int primary key,age int);
-主键+自增的写法：
create table student (id int primary key auto_increment,age int);
-非空约束 not null
create table student (id int primary key auto_increment,age int not null)
表字段的注释
-表字段的注释关键字：comment
create table user(
id int comment ‘用户的id’
age int comment ‘用户的年龄’
)；
表设计
-在设计表字段的时候，如果某个字段有可能出现大量的重复(称为数据冗余)，需要把该字段单独提出创建一张新表，把可能重复的放到新表内，在原表只需要使用表的id即可
数据库:关系型数据库(支持事务) 非关系型数据库(不支持)
数据库事务概述;
什么是事务(transaction)
1.保证成批操作要么完全执行，要么完全不执行，维护数据的完整性，也就是要么成功要么失败。一个事务中包含多条sql语句，而且这些sql语句之间存在一定的关系
2.事务可以是n条sql语句(n>=0)
3.不是所有数据引擎支持事务，InnoDB引擎支持事务处理
数据库事务特性
1.原子性(Atomic):事务的所有所有操作要么全部执行，要么全部不执行。如果中途出现错误不会停止，而是回滚到事务前的状态
2.一致性(Consistency):如果事务执行前是一致的那么执行后也是一致的，不能被破坏关系数据的完整性以及业务逻辑上的一致性，事务按照预期实现。
3.隔离性(Isolation):隔离性可以防止多个事务并发时导致数据的不一致
4.持久性(Durability):事务执行成功后对数据库的修改是永久的
事务并发不进行事务隔离会发生什么
1.脏读:事务A读到未提交事务B修改的数据，如果此时事务B中途执行失败回滚，那么此时事务A读取到的就是脏数据。比如事务A对money进行修改，此时事务B读取到事务A的更新结果，但是如果后面事务A回滚，那么事务B读取到的就是脏数据了。
2.不可重复读:同一个事务中，对同一份数据读取的结果不一致。事务A在事务B对数据更新前进行读取，然后事务B更新提交，事务A再次读取，这时候两次读取的数据不同。
3.幻读:同一个事务中，同一个查询多次返回的结果不一样。事务B查询表的记录数，然后事务A对插入一条记录，接着事务B再次查询发现记录数不同。
区别
1.脏读和不可重复读：脏读是失误读取了还未提交失误的更新数据。不可重复读是同一个事务中，几次读取的数据不同。
2.不可重复读和幻读的区别：都是在同一个事务中，前者是几次读取数据不同，后者是几次读取数据整体不同。
隔离级别
隔离级别 作用
Serializable(串行化) 避免脏读、不可重复读、幻读
Repeatable(可重复读) 默认为此 避免脏读、不可重复读
Read committed(读已提交) 避免脏读
Read uncommitted(读未提交) none
Mysql支持上面4种隔离级别，默认为可重复读
select @@global.tx_isolation; 可以查看当前隔离级别
set global tx_isolation=’serializable’; 更改隔离级别
MySQL数据库管理事务
begin:开始事务，后边有多条数据库操作语句开始执行
commit:开始提交一个事务，对应前边的begin操作，将事务处理的结果保存到数据文件中;
rollback:开始回滚一个事务，在begin和commit之间，将事务中的全部语句撤回，恢复到执行begin之前的数据状态；
set autocommit;：禁用或开启自动提交，自动提交为退出mysql连接程序时，或者执行下一条DML语句;
别名
select ename as ‘姓名’ ,sal as ‘工资’ , mgr as ‘领导编号’ from emp;
select ename ‘姓名’ ,sal ‘工资’, mgr ‘领导编号’ from emp;
select ename 姓名, sal 工资 , mgr 领导编号 from emp;
去重
查询emp表中所有员工的职位
select job from emp;
select distinct job from emp;
where
= < > >= <= != (<>)
1.案例:查询工资高于2000的所有员工的编号，姓名，职位，工资
select empno,ename,job,sal from emp where sal>2000;
2.案例:查询工资小于等于1600的所有员工编号,姓名,工资
select empno,ename,sal from emp where sal<=1600;
3.案例:查询部门编号是20的所有员工的姓名，职位以及所属部门的编号
select ename,job,deptno from emp where deptno=20;
4.案例:查询职位是manager的所有员工的姓名,职位
select job,deptno from emp where job=’manager’;
5.案例:查询不是10号部门的所有员工的编号,姓名,以及所属部门的编号(2种方法)
select empno,ename,deptno from emp where deptno!=10;
select empno,ename,deptno from emp where deptno<>10;
6.案例:查询单价等于23的商品
select * from t_item where price=23;
7.案例:查询单价不等于8443的商品
select title from t_item where price !=8443;
在查询语句最后添加\G让每条数据纵向排列
AND 和 OR运算符
and和java中的&&效果相同,or和java中的||效果相同
1.案例:查询不是10号部门的并且工资小于3000的所有员工的编号，姓名，一击所属部门的编号
select empno,ename,deptno from emp where deptno!=10 and sal<3000;
2.案例查询部门编号是30或者上级领导为7698的所有员工的姓名,职位以及所属部门的编号
select ename,job,deptno from emp where deptno=30 or mgr=7698;
In
如果查询某个字段的值为多个的时候可以使用in关键字
select * from person where age in (68,18,19)
select * from person where age not in (68,18,19)
Between and
-在某两个数值之间 包含 and 两边的数
select * from person where age between 22 and 28 .
sql link 操作符
_:代表单个未知字符
%:代表多个未知字符
a开头 a%
b结尾 %b
包含c %c%
第二个字母是d _d%
倒数第三个字母是e %e__
163邮箱 %@163.com
任意com的邮箱 %@%.com
select * from user where name like ‘a%’
1.案例:查询标题包含记事本的商品
select title from t_item where title like ‘%笔记本’;
2.案例:查询有赠品的dell产品
select sell_point from t_item where sell_point like ‘%赠%’ and title like ‘%dell%’;
3.查询单价低于100的笔记本
select title from t_item where price<100 and title like ‘%记事本%’;
4.查询价格介于50到200之间的得力商品
where price between 50 and 200 and title like ‘%得力%’
5.查询有图片的得力商品
select title from t_item where image is not null and title like ‘%得力%’;
6.查询分类为238,917的商品
select title from t_item where category_id in (238,917);
7.查询分类不是238,917的商品
select title from t_item where category_id not in (238,917);
SQL ORDER BY 子句
order by 排序 by后面添加排序规则的字段
asc:升序
desc:降序
省略asc/desc时默认按升序排列
1.案例:查询所有带燃字的商品，按单价升序排列
select title,price from t_item where title like ‘%燃%’ order by asc;
2.案例:查询所有Dell商品,按单价降序排列
select title,price from t_item where titlie like ‘%dell%’ prder by price desc;
3.案例:查询所有Dell商品,按分类升序单价降序排列
select title ,category_id,price from t_item where title like ‘%dell%’ order by category_id,price desc;
limit
limit begin,count 分页语句begin从0开始需求:每页显示5条查询第三页数据limit 10,5
MySQL索引原理及查询优化
1、介绍：
• 一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了
• 索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
• 非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要
• 索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级
• 索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查
2、索引原理
索引目的：提高查询效率，相似与：查字典，查火车车次，飞机航班等
本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景
3、磁盘IO与预读
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助
4、索引的数据结构
任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生
如图：是一颗b+树，关于b+树的定义可以参见 B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中
b+树的查找过程
如图：如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高
b+树性质
1）、索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表
2）、索引的最左匹配特性（即从左往右匹配）：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性
MySQL索引管理
作用
1）、索引的功能就是加速查找
2）、mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
注意：默认一个表可以设置16个索引
MySQL常用的索引
普通索引
index 加速查找
唯一索引
主键索引 primary key 加速查找+约束（不为空且唯一）
唯一索引 unique 加速查找+约束 （唯一）
联合索引
-primary key(id,name) 联合主键索引
-unique(id,name) 联合唯一索引
-index(id,name) 联合普通索引
全文索引
fulltext 用于搜索很长一篇文章的时候，效果最好
空间索引
spatial 了解就好，几乎不用
举个例子，比如你在某商场做一个会员卡的系统
这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT
那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）
除此之外还有全文索引，即FULLTEXT
会员备注信息 ， 如果需要建索引的话，可以选择全文搜索
用于搜索很长一篇文章的时候，效果最好
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索
索引的两大类型hash和btree
在创建上述索引的时候，为其指定索引类型，分两类
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 表名字;
在创建表时就创建索引
例：
mysql> create table SS(ID int,NAME char(10),ZJ int ,email varchar(25),index(ID));
Query OK, 0 rows affected (0.00 sec)
# 可以写成 ：(ID int primary key，
# 或者写在 index(ID) 的前面：,primary key(ID),index(ID));
在创建时需要注意：不可以这样加 ID int index 索引,因为index只是索引，不是约束；不想主键，有唯一约束，在定义字段的时候不能加索引查看索引
mysql> show index from SS;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| SS | 1 | ID | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)在创建表后添加索引
添加普通索引
mysql> create index NAME on SS(NAME);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from SS;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| SS | 1 | ID | 1 | ID | A | NULL | NULL | NULL | YES | BTREE | | |
| SS | 1 | NAME | 1 | NAME | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)添加唯一索引
例：
mysql> alter table SS add unique index(`email`);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from SS;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| SS | 0 | email | 1 | email | A | NULL | NULL | NULL | YES | BTREE | | |
| SS | 1 | ID | 1 | ID | A | NULL | NULL | NULL | YES | BTREE | | |
| SS | 1 | NAME | 1 | NAME | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)添加住建索引，也就是给字段增加一个主键约束
例：
mysql> alter table SS add primary key(ID);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from SS;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| SS | 0 | PRIMARY | 1 | ID | A | 0 | NULL | NULL | | BTREE | | |
| SS | 0 | email | 1 | email | A | NULL | NULL | NULL | YES | BTREE | | |
| SS | 1 | ID | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
| SS | 1 | NAME | 1 | NAME | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)添加普通联合索引
例：
mysql> create index C on SS(ID,NAME);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from SS;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| SS | 0 | PRIMARY | 1 | ID | A | 0 | NULL | NULL | | BTREE | | |
| SS | 0 | email | 1 | email | A | NULL | NULL | NULL | YES | BTREE | | |
| SS | 1 | ID | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
| SS | 1 | NAME | 1 | NAME | A | NULL | NULL | NULL | YES | BTREE | | |
| SS | 1 | C | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
| SS | 1 | C | 2 | NAME | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)删除索引
删除普通索引
例：
mysql> drop index NAME on SS;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from SS;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| SS | 0 | PRIMARY | 1 | ID | A | 0 | NULL | NULL | | BTREE | | |
| SS | 0 | email | 1 | email | A | NULL | NULL | NULL | YES | BTREE | | |
| SS | 1 | ID | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
| SS | 1 | C | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
| SS | 1 | C | 2 | NAME | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)删除唯一索引
例：
mysql> drop index email on SS;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from SS;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| SS | 0 | PRIMARY | 1 | ID | A | 0 | NULL | NULL | | BTREE | | |
| SS | 1 | ID | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
| SS | 1 | C | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
| SS | 1 | C | 2 | NAME | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)删除主键(因为它添加的时候是按照alter来增加的，所以删除也用alter)
例：
mysql> alter table SS drop primary key;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from SS;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| SS | 1 | ID | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
| SS | 1 | C | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
| SS | 1 | C | 2 | NAME | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)删除联合索引
例：
mysql> drop index C on SS;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from SS;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| SS | 1 | ID | 1 | ID | A | NULL | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)测试索引
准备表
mysql> create table ST(id int,name varchar(10),NPC char(6),dh varchar(70));
Query OK, 0 rows affected (0.00 sec)
mysql> alter table ST engine=InnoDB;创建存储过程，实现批量插入记录
函数：可以理解为代码的封装，方便它人调用
存储过程：可以理解为shell函数，里面包含多条SQL语句
$$：名声存储过程的结束符合为$$
mysql> delimiter $$
mysql> create procedure auto_insert1()
-> BEGIN
-> declare o int default 1;
-> while(o<3000000)do
-> insert into ST values(o,'egon','male',concat('egon',o,'@bj.com'));
-> set o=o+1;
-> end while;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
======#####====== 解释
delimiter $$
create procedure auto_insert1()
BEGIN
declare o int default 1; #类似于shell脚本中的 o=1
while(o<3000000)do
insert into ST values(o,'egon','male',concat('egon',o,'@bj.com'));
set o=o+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号查看存储过程
mysql> show create procedure auto_insert1\G;
*************************** 1. row ***************************
Procedure: auto_insert1
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `auto_insert1`()
BEGIN
declare o int default 1;
while(o<4000000)do
insert into ST values(o,'egon','male',concat('egon',o,'@bj.com'));
set o=o+1;
end while;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
ERROR:
No query specified调用存储过程
mysql> call auto_insert1();
Query OK, 1 row affected (32.15 sec) #比较慢删除存储过程
mysql> drop procedure auto_insert1;
Query OK, 0 rows affected (0.00 sec)测试没有索引进行查询
mysql> select * from ST where id=333;
+------+------+------+----------------+
| id | name | NPC | dh |
+------+------+------+----------------+
| 333 | egon | male | egon333@bj.com |
+------+------+------+----------------+
1 row in set (0.42 sec)
mysql> select * from ST where dh='egon451254@bj.com';
+--------+------+------+-------------------+
| id | name | NPC | dh |
+--------+------+------+-------------------+
| 451254 | egon | male | egon451254@bj.com |
+--------+------+------+-------------------+
1 row in set (0.46 sec)使用索引进行查询
1、必须是给搜索条件的字段创建索引，如： select * from ST where id=333; 需要给 "id" 加上索引
2、在表中已经存在大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快 如：create index id on ST(id); 会扫描表中所有的数据，然后以 id 为数据项，创建索引结构，存放于硬盘的表中；建完后，查询速度会变的很快
mysql> create index id on ST(id);
Query OK, 2999999 rows affected (4.36 sec)
Records: 2999999 Duplicates: 0 Warnings: 0
mysql> select * from ST where id=333;
+------+------+------+----------------+
| id | name | NPC | dh |
+------+------+------+----------------+
| 333 | egon | male | egon333@bj.com |
+------+------+------+----------------+
1 row in set (0.00 sec)注意：innodb表的索引会存放于ST.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI
[root@C7-15 ~]# cd /usr/local/mysql/data/gs
[root@C7-15 gs]# ls
db.opt ST.frm ST.ibd yg.frm yg.ibd总结：MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
