mysql性能优化

最新推荐文章于 2024-11-01 11:00:05 发布

爱喝嘉多宝

最新推荐文章于 2024-11-01 11:00:05 发布

阅读量732

点赞数 19

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/jdb824/article/details/139339079

版权

数据库索引

是一个排序的列表，存储这索引值所对应的物理地址
无需对整个表进行扫描，通过物理地址就可以找到所需的数据
是表中一列或者若干列值排序的方法
需要额外的磁盘空间

索引的作用

数据库利用各种快速定位技术，能够大大加快查询速率
当表很大或查询涉及到多个表时，可以成千上万倍的提高查询速度
可以降低数据库IO成本，并且还可以降低数据库的排序成本
通过创建唯一索引保证数据表数据的唯一性
可以加快表与表之间的连接
在使用分组和排序时，可大大减少分组和排序时间

创建索引

create index a1 on info(id)
alter table info add index a2(name)

创建表的时候直接创建索引

CREATE TABLE `kc01` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
        `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        PRIMARY KEY (`id`),
        INDEX index_name (title)
        )；
//此表中id为主键 PRIMARY KEY (`id`)   //索引为title INDEX index_name (title)

唯一索引

create unique index b1 on info(id);            //在名为info表中创建索引b1为id
alter table info add unique b2(id);            //修改已经存在的info表创建索引b2为id


 CREATE TABLE `kc02` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
        `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        PRIMARY KEY (`id`),
        unique b3 (id)
        )；
//此表中唯一索引，主键为id

主键索引

是一种特殊的唯一索引，指定为"primary key"
一个表只能有一个主键，不允许有空值

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    -- 其他字段和约束...
);

-- 假设已有 employees 表，但未定义主键
ALTER TABLE employees
ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST;

-- 或者，如果已有 id 字段但未设为主键
ALTER TABLE employees
MODIFY COLUMN id INT AUTO_INCREMENT,
ADD PRIMARY KEY (id);

组合索引（单列索引与多列索引）

可以时断裂上创建的索引，也可以时在多列上创建的索引
最左原则，从左到右依次执行

全文索引

索引类型为fulltext
可以在char，varchar或者text类型的列上创建

创建索引的原则依据

表的主键，外键必须有索引
记录数超过300行的表应该有索引
经常与其他表进行连接的表，在连接字段上应该建立索引
唯一型性太差的字段不适合建立索引
更新太频繁的字段不适合创建索引
经常出现在where子句中的字段，特别时大表的时段，应该建立索引
索引应该建在选择性高的字段上
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

查看索引

show index from info;        //查询info表中的索引    Non_unique为0不能重复为1可以重复

删除索引

drop index b1 on info;            //删除info表中名为b1的索引
alter table info drop index b2;            //修改已存在info表中删除名为b2的索引

事物的概念

是一种机制，一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行
是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元
适用于多用户同时操作的数据库系统的场景，如银行，保险公司及证券交易系统等等
通过事务的整体性以保证数据的一致性

事务的ACID特点

原子性（Atomicity）
- 事务是一个完整的操作，事务的各元素是不可分的
- 事务中的所有元素必须作为一个整体提交或回滚
- 如果事务中的任何元素失败，则整个事务将失败
一致性（Consistency）

隔离性（lsolation）
- 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

持久性（Durability）
- 指不管系统是否发生故障，事务处理的结果都是永久的
- 一旦事务被提交，事务的效果会被永久地保留在数据库中

手动对事务的控制方法

begin：开始一个事务
commit：提交一个事务
rollback：回滚一个事务

使用set命令进行控制

set autocommit=0：禁止自动提交
set autocommit=1：开启自动提交

存储引擎

MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制，索引技巧锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎
存储引擎是MySQL键数据存储在文件系统中的存储方式或者存储格式
MySQL常用的存储引擎
- MyISAM
  - 不支持事务，也不支持外键
  - 访问速度快
  - 对事物完整性没有要求
  - MyISAM在磁盘上存储成三个文件
    - .frm文件存储表定义
    - 数据文件的拓展名为.MYD(mydata)
    - 索引文件的扩展名是.MYL(MYLndex)
- InnoDB
  - 支持4个事物隔离级别
  - 行级锁定，但是全表扫描仍然是表级锁定
  - 读写阻塞与事物隔离级别相关
  - 能非常高效的缓存索引和数据
  - 表与主键以簇的方式存储
  - 支持分区，表空间，类似oracle数据库
  - 支持外键约束，5.5前不支持全文索引
  - 对硬件资源要求还是比较高的场合