mysql总结

MySQL 是一种关系型数据库，可用来持久化存储。
本次总结主要分为以下几个方面。
一、引擎
innodb 引擎
默认事务型引擎，最重要最广泛的存储引擎，性能非常优秀 数据存储在共享表空间，可通过配置分开 对主键查询的性能高于其他类型的存储引擎 内部做了很多优化，从磁盘读取数据时自动在内存构建hash 索引，插入数据时自动构建插入缓冲区 通过一些机制和工具支持真正的热备份 支持崩溃后的安全恢复 支持行级锁 支持外键

MyISAM 引擎
5.1 版本前是默认引擎 拥有全文索引、压缩、空间函数 不支持事务和行级锁，不支持奔溃后安全恢复 表存储在两个文件，MYD 和MYI 设计简单，某些场景下性能很好

其他引擎：
Archive、Blackhole、CSV、Memory

二、MySQL 锁机制
当多个查询同一时刻进行数据修改时，会产生并发控制的问题
共享锁（读锁）
排他锁（写锁）
表锁 系统性能开销最小，会锁定整张表，myisam 使用表锁
行锁 最大程度的支持并发处理，但是也带来了最大的锁开销，innodb 实现行级锁

三、索引类型
普通索引、主键索引、唯一索引、组合索引、外键索引、全文索引
索引创建原则
最适合索引的列是出现在where 子句的列，或连接子句中的列，而不是出现在select 的关键字后的 列
索引列的基数越大，索引效果越好
对字符串进行索引，应指定一个前缀长度，可以节省大量的索引空间
根据情况创建复合索引，复合索引可以提高查询效率
避免创建过多索引，索引会额外占用磁盘空间，减低写操作效率
主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用，提高效率
索引的注意事项
复合索引遵循前缀原则
like 查询，%不能在前，可以使用全文索引
column is null 可以使用索引
如果MySQL 估计使用索引比全表扫描更慢，会放弃使用索引

四、事务相关
事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的特性：
原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务的隔离级别：
READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

五、常用操作：
数据库：
#查看所有数据库
SHOW DATABASES ;
#创建库
CREATE DATABASE test_db;
#删除一个数据库
DROP DATABASE test_db;
#使用这个数据库
use test_db;

数据表：

#查看所有的表
SHOW TABLES ;
#创建表
CREATE TABLE test_table(id INT, name VARCHAR(10), PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES n(id), UNIQUE (name));
#新创建一个与原表结构相同的数据表。但不包含其结果。
CREATE TABLE test_table2 LIKE test_table1;
#直接将查询结果导入或复制到新创建的临时表
CREATE TEMPORARY TABLE test_table1 SELECT * FROM test_table2;
#删除表
DROP TABLE test_table;
#查看表的创建语句
show create table test_table;

修改表结构：
#添加字段
ALTER TABLE test_table ADD age VARCHAR(2) ;
#删除字段
ALTER TABLE test_table DROP age;
#更改字段属性和属性
ALTER TABLE test_table CHANGE age a INT;
#只更改字段属性
ALTER TABLE test_table MODIFY age VARCHAR(7) ;