抱歉，您的浏览器无法访问本站
本页面需要浏览器支持（启用）JavaScript
了解详情 >

MySQL|进阶

MySQL

发布于：2024年4月22日

⭐索引

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节

分类

  • 普通索引和唯一索引

    index(column_name)：普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

    unique index uniquIdx(column_name)：唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。如果是组合索引，则列值的组合必须唯一。

  • 单列索引和组合索引

    index(c1)：单列索引即一个索引只包含单个列，一个表可以有多个单列索引。

    index(c1,c2...)：组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如，由id、name和age 3个字段构成的索引，索引行中按id、name、age的顺序存放，索引可以搜索（id,name, age）、（id, name）或者id字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用索引查询。

  • 全文索引

    fulltext index(xx)全文索引类型为fulltext，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在char、varchar或者text类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。索引总是对整个列进行，不支持局部（前缀）索引。

  • 空间索引

    spatial index(xx)：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是geometry、point、linestring和polygon。MySQL使用spatial关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为not null，空间索引只能在存储引擎为MyISAM的表中创建。

🌟索引的设计原则：

  1. 索引并非越多越好。一张表中如果有大量的索引，不仅占用磁盘空间，还会影响insert / delete / update等语句的性能，因为表数据改动时，索引也会进行调整和更新。
  2. 避免对经常更新的表设置过多的索引，且索引中的列尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
  3. 数据量小的表最好不要使用索引。
  4. 在条件表达式中经常用到的列（且该列不同值较多）上添加索引。
  5. 当唯一性是某种数据本身的特征时，指定唯一索引。
  6. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。如果待排序的列有多个，可以在这些列上建立组合索引。

创建索引

1
create table table_name [col_name data_type] [unique | fulltext | spatial] [index | key] [index_name] (col_name [length]) [ASC | DESC]
  • [unique | fulltext | spatial]，可选参数，分别表示唯一索引、全文索引和空间索引
  • [index | key] 两者作用相同，用来创建索引。
  • [index_name] ，索引名称，可选参数
  • (col_name [length]) ，MySQL默认col_name为索引值；length为索引长度，可选参数。
  • [ASC | DESC]指定升序或者降序的索引值存储。

已有字段添加索引

1
2
alter table table_name　add [unique|fulltext|spatial]　[index|key]
[index_name] (col_name[length],…) [asc | desc]

  1. Table表示创建索引的表。
  2. Non_unique表示索引非唯一。1代表是非唯一索引，0代表唯一索引。
  3. Key_name表示索引的名称。
  4. Seq_in_index表示该字段在索引中的位置。单列索引该值为1，组合索引为每个字段在索引定义中的顺序。
  5. Column_name表示定义索引的列字段。
  6. Sub_part表示索引的长度。
  7. Null表示该字段是否能为空值。
  8. Index_type表示索引类型。
1
2
3
4
# create 方式添加索引
create [unique|fulltext|spatial]　index [index_name] on table_name (col_name[length],..) [asc | desc]
## 例子
create index TextIdx on tb1(b);

删除索引

1
2
3
4
# 1
alter table table_name drop index index_name;
# 2
drop index index_name on table_name;

⚠️删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

🌟explain分析是否命中索引

1
explain <sql语句>

  1. select_type表示查询类型。SIMPLE代表简单的SELECT查询。其他情况有PRIMARY、UNION、SUBQUERY。
  2. table表示数据库读取的数据表的名字，数据库读取的数据表的名字。
  3. type表示本数据表与其他数据表之间的关联关系，可能的取值有system、const、eq_ref、ref、range、index和All。
  4. possible_keys表示MySQL在搜索数据记录时可选用的各个索引。
  5. key表示MySQL实际选用的索引
  6. key_len行给出索引按字节计算的长度，key_len数值越小，表示越快。
  7. ref行给出了关联关系中另一个数据表里的数据列名
  8. rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
  9. Extra行提供了与关联操作有关的信息。

存储过程

todo

视图

todo

触发器

todo

权限

1
2
3
4
# 登录MySQL服务器
mysql [-h [主机ip，默认localhost]] -u<username> -p[password] [-P[post，默认3306]] [database_name] [-e "sql语句"]
# 退出
exit

用户管理

创建普通用户

  1. create user语句创建新用户

    1
    create user 'userName'@'localhost' [identified by [PASSWORD] 'password'] | [identified with auth_plugin [as 'auth_string']]
    • identified by 表示用来设置用户的密码；PASSWORD表示使用哈希值设置密码，该参数可选；password表示用户登录时使用的普通明文密码。不需要密码就不用这段。
    • identified with语句为用户指定一个身份验证插件；auth_plugin是插件的名称，插件的名称可以是一个带单引号的字符串或者带双引号的字符串；auth_string是可选的字符串参数，该参数将传递给身份验证插件，由该插件解释该参数的意义。

  2. 直接操作user表 不推荐使用，因为user表中有很多没有默认值的字段，缺失无法添加。

    1
    insert into user(host,user,authentication_string) valuses('host','userName',MD5('passward'));

删除普通用户

  1. drop user语句删除用户

    1
    2
    3
    4
    # 删除某个用户
    drop user 'username'@'host';
    # 删除来自所有授权表的帐户权限记录
    drop user;

  2. 直接操作user表

    1
    delete from user where host='localhost' and user='customer1';

修改密码

  1. root用户修改自己的密码

    1
    update user set authentication_string = MD5('password') where user = 'root' and host='localhost';

  2. root用户修改普通用户密码

    1
    2
    set password for 'username'@'localhost' = 'password';
    update user set authentication_string=MD5('password') where user='userName' and host='hostName';

权限管理

权限 user表中对应的列 权限的范围
create create_priv 数据库、表、索引
drop drop_priv 数据库、表、索引
grant option grant_priv 数据库、表、存储过程
references references_priv 数据库、表
event event_priv 数据库
alter alter_priv 数据库
delete delete_priv 表
index index_priv 表
insert insert_priv 表
select select_priv 表、列
update update_priv 表、列
create temporary tables create_tmp_table_priv 表
lock tables lock_tables_priv 表
trigger tigger_priv 表
create view create_view_priv 视图
show view show_view_priv 视图
alter routine alter_routine_priv 存储过程、函数
create routine create_routine_priv 存储过程、函数
execute execute_priv 存储过程、函数
file file_priv 访问服务器上的文件
create tablespace create_tablespace_priv 服务器管理
create uesr create_user_priv 服务器管理
process process_priv 存储过程、函数
reload reload_priv 访问服务器上的文件
replication client repl_client_priv 服务器管理
replication slave repl_slave_priv 服务器管理
show databases show_db_priv 服务器管理
shutdown shutdown_priv 服务器管理
super super_priv 服务器管理

  1. reload命令告诉服务器将授权表重新读入内存；

    flush-hosts , flush-logs , flush-privileges , flush-status , flush-tables , flush-threads , refresh , reload

    flush-privileges是reload的同义词；refresh命令清空所有表并关闭/打开记录文件；其他flush-xxx命令执行类似refresh的功能，但是范围更有限，并且在某些情况下可能更好用。例如，如果只是想清空记录文件，flush-logs是比refresh更好的选择。

  2. shutdown命令关掉服务器。只能从MySQLadmin发出命令。

  3. process权限拥有processlist命令显示在服务器内执行的线程的信息（其他账户相关的客户端执行的语句）。kill命令杀死服务器线程。用户总是能显示或杀死自己的线程，但是需要PROCESS权限来显示或杀死其他用户和SUPER权限启动的线程。

  4. spuer权限拥有kill命令能用来终止其他用户或更改服务器的操作方式。

授权

授权权限分级：

  • 全局层级

    全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。grant all on *.*和revoke all on *.*只授予和撤销全局权限。

  • 数据库层级

    数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。grant all on db_name.和revoke all on db_name.*只授予和撤销数据库权限。

  • 表层级

    表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。grant all on db_name.tbl_name和revoke all on db_name.tbl_name只授予和撤销表权限。

  • 列层级

    列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用revoke时，必须指定与被授权列相同的列。

  • 子程序层级

    create routine、alter routine、execute、grant权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了create routine外，这些权限可以被授予子程序层级，并存储在mysql.procs_priv表中。

在MySQL中，必须是拥有grant权限的用户才可以进行grant。要使用grant或revoke，必须拥有grant option权限，并且必须用于正在授权或撤销的权限。grant的语法如下：

1
grant priv_type [(coloumns)] [,priv_type [(coloums)]] ... on [object_type] table1,table2... to user [with grant option] object_type = table | function | procedure
  • priv_type表示权限类型；
    [(coloumns)]表示权限作用于那些列上，不指定该参数，表示用于整个表。
  • object_type指定授权作用的对象类型包括table（表）、function（函数）和procedure（存储过程）。当旧版本升级到高版本，要使用该子句，升级权限表。
  • user表示用户帐户，有用户名和主机名构成，形式是'username'@'hostname'
  • identified by表示设置密码
  • with关键字后可以跟随一个或多个with_option参数。
    1. grant option：被授权的用户可以将这些权限赋予别的用户
    2. max_queries_per_hour count：设置每个小时可以执行count次查询。
    3. max_updates_per_hour count：设置每小时可执行count从更新。
    4. max_connections_per_hour count：设置每小时可建立count个连接。
    5. max_user_connections count：设置单个用户可以同时建立count个连接。

取消授权

1
revoke all privileges, grant option from 'user'@'host' [, 'user'@'host' ...]

查看权限

1
show grants for 'user'@'host';

🌟性能优化

原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。
1. 通过优化文件系统，提高磁盘I/O的读写速度。
2. 通过优化操作系统调度策略，提高MySQL在高负荷情况下的负载能力。
3. 优化表结构、索引、查询语句等查询响应更快。

性能参数

1
show status like 'value';

value是要查询的参数值，一些常用的性能参数如下：

  • connections：连接MySQL服务器的次数。
  • uptime：MySQL服务器的上线时间。
  • slow_queries：慢查询的次数。
  • com_select：查询操作的次数。
  • com_insert：插入操作的次数。
  • com_update：更新操作的次数。
  • com_delete：删除操作的次数。

分析查询语句

1
explian select select_options

explain实例

📌分析结果列解析：

  • id：select查询的系列号，表示查询中执行select子句或者操作表中的顺序。
    id的数值越大，优先级越高，越先执行
  • select_type：表示语句的类型。
    1. SIMPLE：表示简单的查询，其中不包括连接查询和子查询。
    2. PRIMARY：表示主查询，或者最外层的查询语句。
    3. UNION：表示连接查询的第2个或者后面的查询语句。
    4. DEPENDENT UNION，连接查询中的第2个或后面的SELECT语句，取决于外面的查询。
    5. UNION RESULT，连接查询的结果。
    6. SUBQUERY，子查询中的第1个SELECT语句。
    7. DEPENDENT SUBQUERY，子查询中的第1个SELECT，取决于外面的查询。
    8. DERIVED，导出表的SELECT（FROM子句的子查询）。
  • table：表示涉及的表
  • type：表示连接类型。
    1. ALL：全表查询
    2. system：该表是仅一行的系统表。这是const连接类型的一个特例
    3. const：数据表最多只能匹配一行数据。出现在主键或者索引的场合
    4. eq_ref：当一个索引的所有部分都在查询中使用并且索引是UNIQUE或PRIMARY KEY时，即可使用这种类型
    5. ref：可以用于使用=或<=>操作符带索引的列
    6. ref_or_null：该连接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该连接类型的优化。
    7. index_merge：该连接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用索引的最长关键元素。
    8. unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
    9. index_subquery：该连接类型类似于unique_subquery，可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引。value IN (SELECT key_column FROM single_table WHERE some_expr)
    10.range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符用常量比较关键字列时，类型为range。
    11. index：该连接类型与ALL相同，除了只扫描索引树。这通常比ALL快，因为索引文件通常比数据文件小。

📌优化索引

  1. 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。
  2. MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。最左匹配原则
  3. 查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引；否则，查询将不使用索引。
  4. 避免使用子查询。子查询会导致MySQL多次扫描表

子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。在MySQL中，可以使用连接（JOIN）查询来替代子查询。

📌优化表

优化数据库结构

  1. 拆分水平宽表
  2. 添加中间表
  3. 合理添加冗余字段。虽然可以避开一些连接查询，但是在数据改动后会造成数据不一致问题，同时浪费里一定的磁盘空间。慎用！慎用！慎用！

优化插入记录的速度

  1. 禁用索引
  2. 禁用唯一性检查
  3. 使用批量插入
  4. 使用load data infile批量导入

分析表

1
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

LOCAL关键字是NO_WRITE_TO_BINLOG关键字的别名，二者都是执行过程不写入二进制日志，tbl_name为分析表的表名，可以有一个或多个。

使用ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB、BDB和MyISAM类型的表。

  • Table：表示分析的表名称
  • Op：表示执行的操作。analyze表示进行分析操作。
  • Msg_type：表示信息类型，其值通常是状态(status)，信息(info)，注意(note)，警告(warning)和错误(error)之一。
  • Msg_text：显示信息。

检查表

1
2
　　CHECK TABLE tbl_name [, tbl_name] ... [option] ...
　　option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
  • QUICK：不扫描行，不检查错误的连接
  • FAST：只检查没有被正确关闭的表
  • MEDIUM：：扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点
  • EXTENDED：对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的，但是化的时间比较长。
  • CHANGED：只检查上次检查后被更改的表和没有被正确关闭的表

option只对MyISAM类型的表有效，对InnoDB类型的表无效。check table语句在执行过程中会给表上只读锁。

优化表

MySQL中使用OPTIMIZE TABLE语句来优化表。该语句对InnoDB和MyISAM类型的表都有效。但是，OPTILMIZE TABLE语句只能优化表中VARCHAR、BLOB或TEXT类型的字段，会给表上只读锁

1
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

📌优化MySQL服务器

优化服务器配件

  • 配置较大的内存。通过增加系统的缓冲区容量使数据在内存停留的时间更长，以减少磁盘I/O。
  • 配置高速磁盘，减少读盘的等待时间，提高响应速度。
  • 合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。
  • 配置多处理器。MySQL是多线程的数据库，多处理器可同时执行多线程。

优化MySQL参数

1
2
# 查看MySQL服务器参数
show variables;

  • 内存缓存

    MySQL的性能受内存的影响很大，因此需要合理地分配内存。

    1. innodb_buffer_pool_size：InnoDB存储引擎的缓存池大小，建议设置为物理内存的70%~80%。
    2. key_buffer_size：MyISAM存储引擎的缓存池大小，建议设置为物理内存的25%。
    3. query_cache_size：查询缓存的大小，建议设置为物理内存的5%~10%。

  • 线程缓存

    MySQL使用线程处理客户端的请求，因此需要设置线程缓存的大小。

    1. thread_cache_size：线程缓存的大小，建议设置为100~200。
    2. max_connections：最大连接数，建议设置为500~1000。

  • 日志

    MySQL的性能受内存的影响很大，因此需要合理地分配内存。

    1. log_show_queries：慢查询日志，可以记录查询时间超过设定阈值的SQL语句。建议设置为1，即开启慢查询日志。
    2. binlog_cache_size：二进制日志缓存大小，建议设置为32M。
    3. innodb_log_file_size：InnoDB日志文件的大小，建议设置为物理内存的10%。

    可以通过MySQL配置文件my.cnf来调整MySQL参数。在修改之前，建议备份。

    1
    2
    3
    4
    # 查找my.cnf 文件的位置
    mysql --help | grep cnf
    # 修改后重启生效
    service mysql resatrt

语句超时处理

1
2
3
# 设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现。
set global max_execution_time=2000;
set session max_execution_time=2000;

全局通用表空间

MySQL 8.0支持创建全局通用表空间，全局表空间可以被所有数据库的表共享，而且相比于独享表空间，手动创建共享表空间可以节约元数据方面的内存。可以在创建表的时候指定属于哪个表空间，也可以对已有表进行表空间修改，具体的信息可以查看官方文档。

1
2
alter table t1 tablespace dxy;
drop tablespace dxy;

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文永久链接是：https://solitaire-12.github.io/2024/04/22/data/mysql-进阶/

更新于：2024年5月10日

MySQL

评论