Mysql 的账户管理，索引，存储引擎

目录

一.MySQL的账户管理

1.存放用户信息的表

2.查看当前使用的用户

3.新建用户

4.修改用户名称

5.删除用户

6.修改用户密码

7.破解密码

8. 远程登录

9.用户权限管理

9.1 权限类别

9.2  查看权限

9.3  授予权限

9.4  撤销权限

二.索引

1. 索引管理

1.1  查看索引

1.2  建立索引

1.3  删除索引

2. EXPLAIN  工具

3.使用profile 工具监控

三.存储引擎

1.myisam 与innodb的区别

2.myisam简介

3.innodb简介

4.其它存储引擎

5.管理存储引擎

6.MySQL 中的系统数据库

7.存储过程

---

一.MySQL的账户管理

MySQL账户管理是管理MySQL数据库系统中用户账户和权限的过程。

超级用户（Superuser）：拥有所有权限，比如root用户。

普通用户（Regular User）：具有特定权限的用户，可以是数据库管理员或普通应用程序用户。

1.存放用户信息的表

mysql 的用户 放在mysql数据库中的user表中

可以使用命令查询

select user,host,authentication_string from mysql.user;
#user：用户名 host：主机地址域名  authentication_string：用户密码

2.查看当前使用的用户

(root@localhost) [none]> select user();

3.新建用户

格式：CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];

'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主机上登录，

可使用IP地址、网段、主机名的形式，

本地用户可用localhost，

允许任意主机登录可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
        若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';
        若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）

在使用明文密码时取消密码安全性策略

set global validate_password_policy=0;
#是在 MySQL 中禁用密码验证策略的全局设置。这意味着 MySQL 将不再强制执行对新用户密码的任何密码策略检查，比如最小长度要求或特殊字符的要求。
set global validate_password_length=1;
#在 MySQL 中启用密码长度验证策略的全局设置。这意味着 MySQL 将强制要求新用户密码满足最小长度要求。

create user zxy@'192.168.240.%' identified by '123123';

远程登录主机mysql

mysql -uzxy -h192.168.240.13  -p'123123'

修改用户密码可以通过after命令实现

alter user zxy@'192.168.240.%' identified by  'abc123';

4.修改用户名称

格式： rename  user  ‘旧名称’@‘host’   to  ‘新名称’@‘host’;

示例：

rename user 'zxy'@'192.168.240.%' to 'lisi'@'192.168.240.%';
select user,host,authentication_string from mysql.user;

5.删除用户

格式： drop  user  ‘用户名’@‘host’;

示例：

drop user cxk@'192.168.240.%';
select user,host,authentication_string from mysql.user;

6.修改用户密码

密码有安全性策略可以将策略取消

set global validate_password_policy=0;
#是在 MySQL 中禁用密码验证策略的全局设置。这意味着 MySQL 将不再强制执行对新用户密码的任何密码策略检查，比如最小长度要求或特殊字符的要求。
set global validate_password_length=1;
#在 MySQL 中启用密码长度验证策略的全局设置。这意味着 MySQL 将强制要求新用户密码满足最小长度要求。

格式：set password = password('密码');  #只能改自己

           set password for '用户' = password('密码');  #修改用户密码

示例：

set password = '123123';

修改其他用户密码

set password for 'lisi'@'192.168.240.%' = password('123123');

7.破解密码

修改配置文件

vim /etc/my.cnf
[mysqld]
skip-grant-tables 
#数据库的单用户模式
skip-networkingv
update mysql.user set authentication_string='' where user='root' and host='localhost';
#注意刷新后生效
flush privileges;

#skip-grant-tables：这个选项会让 MySQL 服务器启动时不执行权限验证，
即所有用户都能够以超级用户的权限登录数据库。
这在某些紧急情况下可以帮助恢复丢失了管理员密码的访问，
但同时也存在安全风险，因为任何人都可以访问数据库并执行操作。
skip-networking：这个选项会禁用 MySQL 服务器的网络连接功能，
使其只能本地访问，即只能通过本地的 Unix Socket 或者命名管道进行连接，
而不能通过 TCP/IP 连接。这对于某些安全要求较高的环境来说可能是一个好的选择。
需要注意的是，使用这些选项可能会降低 MySQL 数据库的安全性和可用性，
因此在正常操作中应该避免长期启用它们。在完成特定的维护或紧急恢复任务后，
记得恢复这些选项的默认设置，并确保数据库的安全和正常运行。

8. 远程登录

格式： mysql   -u用户名    -h主机地址域名    -p'密码'    -P端口号

mysql -uzxy -h192.168.240.13  -p'123123'

9.用户权限管理

9.1 权限类别

管理类 、程序类、 数据库级别、 表级别、 字段级别

管理类：

1. CREATE USER：允许用户创建、更改和删除 MySQL 用户账号。

2. FILE：允许用户在服务器上读写文件。

3. SUPER：允许执行多个特权操作，例如启动或停止服务器，设置全局变量等。

4. SHOW DATABASES：允许用户查看所有数据库的列表。

5. RELOAD：允许用户重新加载服务器配置文件。

6. SHUTDOWN：允许用户关闭服务器。

7. REPLICATION SLAVE：允许用户查询从属服务器状态。

8. REPLICATION CLIENT：允许用户查询主服务器和从属服务器的状态。

9. LOCK TABLES：允许用户锁定表，使得其他用户不能访问该表，用于备份和恢复。

10. PROCESS：允许用户查看当前正在执行的进程。

11. CREATE TEMPORARY TABLES：允许用户创建临时表。临时表在会话结束时自动删除。

数据库级别权限

1. ALTER：允许用户修改数据库的结构，如更改表的结构或删除索引。

2. CREATE：允许用户在数据库中创建新的表或索引。

3. CREATE VIEW：允许用户创建视图，即虚拟表。

4. DROP：允许用户删除数据库中的表或索引。

5. SHOW VIEW：允许用户查看数据库中的视图定义。

6. WITH GRANT OPTION：允许用户将自己拥有的权限授予给其他用户，即可以授权其他用户。

数据库表级别权限

1. ALTER：允许用户修改表结构，如添加、删除或修改列，修改索引等。

2. CREATE：允许用户在数据库中创建新表。

3. CREATE VIEW：允许用户创建视图。

4. DROP：允许用户删除表或视图。

5. INDEX：允许用户创建或删除表的索引。

数据库操作：

1. SELECT：允许用户从表中读取数据，即执行查询操作。

2. INSERT：允许用户向表中插入新数据。

3. DELETE：允许用户从表中删除数据。

4. UPDATE：允许用户更新表中已有的数据。

字段级别：

     1.SELECT(col1,col2,...)

     2.UPDATE(col1,col2,...)

     3.INSERT(col1,col2,...)

所有权限：

ALL PRIVILEGES 或 ALL ：授予用户或角色在指定范围内的所有权限。

9.2  查看权限

SHOW GRANTS FOR 'l用户名'@'%';
#USAGE权限只能用于数据库登陆，不能执行任何操作；USAGE权限不能被回收，即 REVOKE 不能删除用户。

示例：

查看李四用户权限

show  grants for 'lisi'@'192.168.240.%';

这条 MySQL 命令 GRANT USAGE ON *.* TO 'lisi'@'192.168.240.%'; 的含义是授予用户 'lisi' 在匹配 '192.168.240.%' 的 IP 地址范围内对所有数据库和表的使用权限。

具体来说：

• GRANT USAGE 表示授予用户权限，但没有具体的数据库或表级别的权限。
• ON *.* 表示这个权限适用于所有的数据库（* 表示所有数据库）和表（同样是 * 表示所有表）。
• TO 'lisi'@'192.168.240.%' 表示将权限授予用户名为 'lisi'，并且该用户只能从 IP 地址以 '192.168.240.' 开头的客户端连接时有效。

总结起来，这条命令允许 'lisi' 用户从 '192.168.240.%' 的 IP 地址段访问 MySQL 数据库服务器，但是没有具体到可以访问哪些数据库或表的详细权限。

9.3  授予权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时， GRANT 语句用于修改用户信息。

格式：GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“kgc.*”表示授权操作的对象为 kgc数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.kgc.com”、“192.168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。

示例：

允许用户lisi 在本地查询主机数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。

grant select on hellodb.students to 'lisi'@'192.168.240.%' identified by '123123';

允许用户 lisi 在所有终端远程连接 mysql ，并拥有所有权限。

grant all  on  *.* to 'lisi'@'192.168.240.%' identified by '123123';
flush privileges;
#刷新

9.4  撤销权限

格式：REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址;

示例：

撤销用户lisi的权限

revoke all on *.* from 'lisi'@'192.168.240.%';
撤销lisi用户的所有权限

二.索引

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现

索引的概念：

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。

使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。

索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。

索引是表中一列或者若干列值排序的方法。

建立索引的目的是加快对表中记录的查找或排序。

索引的作用优点

加快查询速度，提高数据库性能

设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。

当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。避免排序和使用临时表

可以降低数据库的IO成本（减少io次数），并且索引还可以降低数据库的排序成本。将随机I/O转为顺序I/O

通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。

可以加快表与表之间的连接。

在使用分组和排序时，可大大减少分组和排序的时间。

建立索引在搜索和恢复数据库中的数据时能显著提高性能

缺点：

占用额外的磁盘空间，影响插入速度 占用磁盘空间

创建索引的原则依据

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是主表的主键，查询时可以快速定位。 

记录数超过300行的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能。

经常与其他表进行连接的表，在连接字段上应该建立索引。

唯一性太差的字段不适合建立索引。

更新太频繁地字段不适合创建索引。

经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。

在经常进行 GROUP BY、ORDER BY 的字段上建立索引；

索引应该建在选择性高的字段上。

索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

索引优化

• 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）

• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度

• 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引

• 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

• 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

• 对于经常在where子句使用的列，最好设置索引

• 对于有多个列where或者order by子句，应该建立复合索引

• 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引

• 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高

• 不要使用RLIKE正则表达式会导致索引失效

• 查询时，能不要就不用，尽量写全字段名，比如:select id,name,age from students;

• 大部分情况连接效率远大于子查询

• 在有大量记录的表分页时使用limit

• 对于经常使用的查询，可以开启查询缓存

• 多使用explain和profile分析查询语句

• 查看慢查询日志，找出执行时间长的sql语句优化

索引类型：

• B+ TREE、HASH、R TREE、FULL TEXT

• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起

• 主键索引、二级（辅助）索引

• 稠密索引、稀疏索引：是否索引了每一个数据项

• 简单索引、组合索引: 是否是多个字段的索引

• 左前缀索引：取前面的字符做索引

• 覆盖索引：从索引中即可取出要查询的数据，性能高

索引结构：

二叉树结构

B-Tree  索引

缺点：

1.连续范围查找都要从头开始，效率不稳定，快的很快，慢的就比较慢

2.所存数据量越大，查找次数越多

B+Tree  索引

B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

B+Tree

查找效率不统一

高度一般为2-3层，它就能满足千万级的数据存储，解决了btree 需要7-8层才能达到千万级别

假设定义一颗B+树高度为2，即一个根节点和若干叶子节点。那么这棵B+树的存放总行记录数=根节点指针数*
单个叶子记录的行数。这里先计算叶子节点，B+树中的单个叶子节点的大小为16K，假设每一条目为1K，那么
记录数即为16(16k/1K=16)，然后计算非叶子节点能够存放多少个指针，假设主键ID为bigint类型，那么长
度为8字节，而指针大小在InnoDB中是设置为6个字节，这样加起来一共是14个字节。那么通过页大小/(主键
ID大小+指针大小），即16384/14=1170个指针，所以一颗高度为2的B+树能存放16*1170=18720条这样的
记录。根据这个原理就可以算出一颗高度为3的B+树可以存放16*1170*1170=21902400条记录。所以在
InnoDB中B+树高度一般为2-3层，它就能满足千万级的数据存储

1. 索引管理

1.1  查看索引

show index from 表名;
show keys from 表名;

示例：

show  index from students;
show keys from hellodb.students;

1.2  建立索引

格式： CREATE INDEX 索引名 ON 表名 (列名[(length)]);

示例：

未创建索引前没有显示索引

搜索students表中xu xian 查看使用的索引

select * from students where name='xu xian';
explain select * from students where name='xu xian';#查看使用的索引

创建一个名为idx_name的索引，查看索引

使用explain 命令查看 搜索xu xian 使用的索引

create index idx_name on students(name(5));#‘5’索引会只考虑name字段的前5个字符作为索引的一部分。
show index from students;
explain select * from students where name='xu xian';
show create table students\G; #查看索引创建是否成功

create index idx_name on students(name);  #给students 表加 name表
explain select * from students where name like 's%';  #找s开头的人看会使用索引
explain select * from students where name like '%s';  #找s结尾的人不会使用索引
explain select * from students where name like '%s%'; #包含s的不会调用索引
explain select * from students where name like 'x%';  #以x开头的会使用索引

1.3  删除索引

直接删除索引

格式：DROP INDEX 索引名 ON 表名;

示例：

删除建立在students表上的索引  idx_name  

drop index idx_name on students;
show index from students;

2. EXPLAIN  工具

以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

参考资料： MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

语法：EXPLAIN SELECT clause

示例：

查询students表中的xu xian 查看索引是否有效

explain select * from students where name='xu xian';

字段说明

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	简单查询：SIMPLE|复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查询）、UNION（UNION语句的第一个之后的SELECT语句）、UNIONRESUlT（匿名临时表）、SUBQUERY（简单子查询）
table	访问引用哪个表（引用某个查询，如“derived3”）
type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	查询可能会用到的索引
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值
Extra	额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

3.使用profile 工具监控

#打开后，会显示语句执行详细的过程
set profiling = ON;
#查看语句,注意结果中的query_id值
show profiles ;
#显示语句的详细执行步骤和时长
Show profile for query #  
show profile for query 1;

三.存储引擎

InnoDB：默认的存储引擎，支持事务、行级锁定和外键约束，适合处理需要高并发和事务支持的应用。

MEMORY：基于哈希的存储引擎，数据存储在内存中，适合临时表的使用，不支持事务、XA或保存点。

1.myisam 与innodb的区别

1. 存储上限（storage limits ）：

   • MyISAM：支持最大256TB的表空间。
   • InnoDB：理论上支持64TB的表空间，但实际应用中MySQL很少会达到这个极限。

2. 事务支持（transactions ）：

   • MyISAM：不支持事务，这是其主要限制之一。
   • InnoDB：支持事务，实现了ACID（原子性、一致性、隔离性、持久性）属性，适合处理需要数据完整性和并发控制的应用。

3. 锁定粒度（locking granularity ）：

   • MyISAM：使用表级锁定，这意味着在对表进行读写操作时会锁定整个表，可能导致并发访问的性能问题。
   • InnoDB：支持行级锁定，可以显著减少锁定冲突，提高并发性能。

4. 多版本并发控制（MVCC）：

   • InnoDB：实现了MVCC，这是通过在事务读取数据时使用快照来实现的，提高了并发性和数据一致性。
   • MyISAM：不支持多版本并发控制

5. 数据缓存（data caches ）：

   • MyISAM：不支持数据缓存。
   • InnoDB：支持数据缓存，可以通过缓冲池来管理和优化内存中的数据访问。

6. 外键约束（foreign key ）：

   • MyISAM：不支持外键约束，尽管可以定义外键，但MySQL会忽略这些定义。
   • InnoDB：支持外键约束，确保了数据之间的关系完整性和一致性。

2.myisam简介

myisam引擎的特点和限制

• 不支持事务：MyISAM不提供事务支持，因此不能确保数据的原子性、一致性、隔离性和持久性（ACID特性）。
• 表级锁定：在写操作时会锁定整个表，这可能会导致高并发时的性能瓶颈。
• 读写相互阻塞：写操作会阻塞读操作，反之亦然，这在高并发环境下可能导致性能问题。
• 只缓存索引：MyISAM只缓存索引而不是整行数据，这在一些场景下可能会导致额外的磁盘IO操作。
• 不支持外键约束：虽然可以定义外键关系，但MySQL会忽略这些定义，MyISAM本身不会实际实施外键约束。
• 不支持聚簇索引：MyISAM不支持显式定义聚簇索引，这对于一些需要按照物理顺序存储数据的场景可能不利。
• 读取数据较快，占用资源较少：在某些情况下，MyISAM可以提供较快的读取速度，并且在资源利用方面相对较低。
• 不支持MVCC：MyISAM不支持MySQL的多版本并发控制（MVCC），这在读写并发较高的情况下可能表现不佳。
• 崩溃恢复性较差：相比InnoDB等支持事务的存储引擎，MyISAM的崩溃恢复性较差，容易出现数据损坏的情况。
• MySQL 5.5.5前默认的数据库引擎：在MySQL 5.5.5及之前的版本中，默认的数据库引擎是MyISAM，但随着MySQL的发展，InnoDB成为了更为推荐的选择。

MyISAM 存储引擎适用场景

1. 读频繁、写较少的应用：由于MyISAM表级锁定的特性，适合于读操作远远多于写操作的应用场景。例如，用于报表生成、数据分析等场景。

2. 空间和性能要求较低的应用：MyISAM对系统资源的占用相对较少，适合于资源有限的环境或者对性能要求不是特别高的应用。

3. 不需要事务支持的应用：如果应用不需要事务处理的特性，可以考虑使用MyISAM，例如一些日志记录、数据仓库等。

4. 需要全文搜索功能的应用：MyISAM支持全文索引，对于需要进行全文搜索的应用场景比较适合。

5. 简单的数据结构：MyISAM对于简单的数据结构和查询效率较高，可以在这些场景下提供较好的性能表现。

6. 对数据完整性要求不高的应用：MyISAM不支持外键约束和事务，如果应用对数据的完整性要求不高（例如，不需要强制的引用完整性约束），可以考虑使用MyISAM。

MyISAM存储引擎表的常见文件类型

• tbl_name.frm：这是存储表结构定义的文件，包含了表的字段信息、索引信息以及其他表的元数据。它是MySQL中所有存储引擎共有的部分，用于定义表的结构。

• tbl_name.MYD：这是MyISAM存储引擎的数据文件，用于存储实际的数据记录。每个MyISAM表都有一个对应的.MYD文件，其中包含了表中的数据内容。

• tbl_name.MYI：这是MyISAM存储引擎的索引文件。它存储了表的索引信息，用于加速数据检索操作。每个MyISAM表都有一个对应的.MYI文件。

3.innodb简介

innodb引擎特点

1. 行级锁：InnoDB支持行级锁定，可以最大程度地减少多个会话之间的锁冲突，提高并发性能。

2. 支持事务，适合处理大量短期事务：InnoDB是MySQL中唯一支持事务的存储引擎，适合处理需要事务支持的应用，特别是大量短期事务。

3. 读写阻塞与事务隔离级别相关：事务的隔离级别设置会影响到InnoDB的读写阻塞情况，可以根据应用的需求选择合适的隔离级别以平衡一致性和性能。

4. 可缓存数据和索引：InnoDB会将数据和索引缓存在内存中，加速对数据的访问。

5. 支持聚簇索引：InnoDB的主键索引是聚簇索引，数据行的物理存储顺序与主键的逻辑顺序一致，提高了主键查找的性能。

6. 崩溃恢复性更好：InnoDB具有更好的崩溃恢复性，通过事务日志（redo log）和回滚日志（undo log）确保事务的持久性和一致性。

7. 支持MVCC高并发：多版本并发控制（MVCC）是InnoDB实现高并发访问的重要机制，可以在读操作和写操作之间提供高度的并发性。

8. 从MySQL 5.5后支持全文索引：自MySQL 5.6.4版本开始，InnoDB开始支持全文索引，提供了更多的搜索和分析功能。

9. 从MySQL 5.5.5开始为默认的数据库引擎：自MySQL 5.5.5版本起，InnoDB成为MySQL的默认存储引擎，反映了其在性能和功能上的优势。

4.其它存储引擎

• Performance_Schema：Performance_Schema数据库使用

• Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎

• MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库

• Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区

• Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境

• BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性

• Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性

• CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换

• BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储

• example："stub"引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

5.管理存储引擎

查看mysql支持的存储引擎

show engines;

show table status like 'user'\G   #这张表用的是 myisam

查看当前默认的存储引擎

show variables like '%storage_engine%';

设置默认的存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine = InnoDB

查看库中所有表使用的存储引擎

 格式： show table status from db_name;
 show table status from hellodb;

查看库中指定表的存储引擎

show table status like  'students';
show create table students;

设置表的存储引擎

CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

6.MySQL 中的系统数据库

• mysql 数据库

是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

• information_schema 数据库

MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与"数据字典"，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

• performance_schema 数据库

MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

• sys 数据库

MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

7.存储过程

存储过程：多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中

存储过程优势

存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程，提高了运行速度，同时降低网络数据传输量

delimiter//
create procedure  name(IN id int)
begin
 select * from  students where stuid = id;
END//
delimiter;

call  name(2);