MySQL系列MySQL进阶_dml语句性能变低-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_54542209/article/details/131474355

本文深入探讨了MySQL的高级主题，包括索引的类型及其优缺点，如主键索引、唯一索引、普通索引和全文索引。详细阐述了事务的ACID特性、隔离级别以及如何控制事务。此外，还讲解了执行计划（EXPLAIN）的关键信息，如type、key_len和rows等，帮助优化查询性能。同时，文章涵盖了视图的基础知识，强调其在数据隔离和权限控制上的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql进阶

一、mysql索引

索引的优缺点
- 优点
  - 提高查询效率
- 缺点
  - 增加存储空间，索引创建需要存文件
  - DML语句效率变低，因为每次变动索引都要重新排序，可以通过读写分离解决此问题
索引分类
- primary key 主键索引
- unique 唯一索引
- index 普通索引
- unique 联合索引
- fulltext 全文索引
索引查询
- show index from <表名称>;

primary key 主键索引

建表创建主键索引

create table test_table(
    id int PRIMARY KEY,
    name VARCHAR(10),
    age VARCHAR(5)
    );
create table test_table(
    id int,
    name VARCHAR(10),
    age VARCHAR(5), 
    PRIMARY KEY(id ));

添加主键索引

#添加主键约束
ALTER TABLE <表名称> ADD PRIMARY KEY(字段名称) 
#修改字段属性并添加主键约束
ALTER TABLE <表名称> MODIFY <字段名称> VARCHAR(20) PRIMARY KEY;

删除主键索引

alter  table  <表名> drop  <索引名称>;
#删除自增主键索引(需要先删除自增属性，再删除索引)
alter table test_table modifly id int;
alter table test_table drop PRIMARY KEY;

unique 唯一索引

建表创建唯一索引

create table test_table(
    id int,
    name VARCHAR(10),
    age VARCHAR(5),
    UNIQUE  indexName (columnName)
    );

创建唯一索引

create  unique  index  <索引名称>  on  <表名>(字段名);

修改唯一索引

alter  table  <表名>  add  unique  <索引名称>(字段名);

删除唯一索引

alter  table  <表名>  drop  index  <索引名称>;

index 普通索引

建表创建普通索引

create table test_table(
    id int,
    name VARCHAR(10),
    age VARCHAR(5),
    index indexName (columnName)
   );

添加普通索引

create  index <索引名称>  on <表名> (字段名);

修改普通索引

alter  table <表名>   add  index <索引名称> (字段名);

删除普通索引

alter  table  <表名>  drop  index  <索引名称>;

联合索引

建表创建联合索引

create table test_table(
    id int,
    name VARCHAR(10),
    age VARCHAR(5),
    unique  index  indexName (id, name)
    );

添加联合索引

create  unique  index  <索引名称>  on <表名> (字段名1，字段名2);

修改联合索引

alter  table <表名>  add unique  index <索引名称> (字段名1，字段名2);

删除联合索引

alter  table  <表名>  drop  index  <索引名称>;

fulltext 全文索引

建表创建全文索引

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

create table table1(
    id int(10),
    title VARCHAR(10),
    txt VARCHAR(5),
    fulltext  title_name (title,txt)
    );

插入测试数据

insert into table1(id,title,txt) values
(1,"a","a"),
(2,"aa","aaa"),
(3,"ba","ab"),
(4,"adsf","aty"),
(5,"abn","ahh");

添加全文索引

create fulltext index <索引名称>  on <表名> (字段1,字段2);

修改全文索引

alter  table  <表名>  add fulltext index  <索引名称>(字段1,字段2);
ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column1,column2,...);

删除全文索引

alter  table  <表名>  drop  index <索引名称>;

全文索引使用

select  * from  <table> where  match(title,txt) against('查询字符串');
#查询无数据
select * from table1 where match(title,txt) against('a');
#查询有1条数据
select * from table1 where match(title,txt) against('aaa');

最小搜索长度和最大搜索长度
- 搜索词小于最小搜索长度和大于最大搜索长度的词语，都不会被搜索到
- 想对一个词语使用全文索引搜索，那么这个词语的长度必须大于最小搜索长度且小于最大搜索长度
查看搜索长度
- show variables like ‘%ft%’;
- MyISAM 引擎下默认
  - ft_min_word_len = 4
  - ft_max_word_len = 84;
- InnoDB 引擎下默认
  - innodb_ft_min_token_size = 3;
  - innodb_ft_max_token_size = 84;
- 修改/etc/my.cnf文件添加
  - ```
  [mysqld]
  innodb_ft_min_token_size = 1
  ft_min_word_len = 1
```
- 重启msyql
- 修复全文索引
  - ```
  repair table table1 quick;
```

二、Mysql事务

什么是事务
- 一组操作要么全部成功，要么全部失败，目的是为了保证数据最终的一致性
事务的特性(ACID)
- 原子性(Atomicity)
  - 当前事务的操作要么同时成功，要么同时失败，原子性由undo log日志来保证比如修改一条数据，会自动生成一条对应的delete语句，当sql执行失败会自动执行delete语句回滚数据
- 一致性(Consistency)
  - 使用事务的最终目的，由业务代码正确逻辑保证
- 隔离性(Isolation)
  - 在事务并发执行时，他们内部操作不能相互干扰
- 持久性(Durability)
  - 一旦提交事务，它对数据库的改变就应该是永久