MySQL

MySQL基本操作

Posted by Monk on April 26, 2019

数据库的三范式（NF：Normal Form）

  • 1NF. 列的原子性，每个列不可在拆分。如：地址字段，该字段可在拆分为家庭地址和公司地址。
  • 2NF. 一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
  • 3NF. 非主键列是直接依赖于主键，还是直接依赖于非主键列。所有主键列和非主键列不能有传递依赖。

MySQL修改操作

1. 修改表

  1. 修改表名称：alter table src_table rename to target_table;
  2. 修改表注释: alter table tableName comment '修改的注释';

2. 修改字段

  1. 修改字段类型和注释：
    alter table sys_application modify column app_name varchar(20) COMMENT '应用的名称';
  2. 修改字段类型： alter table sys_application modify column app_name varchar(255);
  3. 设置字段允许为空： alter table sys_application modify column description varchar(255) null COMMENT '应用描述';
  4. 增加一个字段，设好数据类型，且不为空，添加注释 alert table sys_application add url varchar(255) not null comment '应用访问地址';
  5. 增加主键 alter table t_app add aid int(5) not null ,add primary key (aid);
  6. 增加自增主键 alter table t_app add aid int(5) not null auto_increment ,add primary key (aid);
  7. 修改为自增主键 alter table t_app modify column aid int(5) auto_increment ;
  8. 修改字段名字(要重新指定该字段的类型) alter table t_app change name app_name varchar(20) not null;
  9. 删除字段 alter table t_app drop aid;
  10. 在某个字段后增加字段（在aid字段后加） alter table t_app add column gateway_id int not null default 0 AFTER aid;

3. 修改索引

  1. 创建索引 
    ALTER TABLE table_name 
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]
  2. 删除索引 
    ALTER TABLE table_name DROP INDEX index_name;
  3. 修改索引（先删除原来的索引，并创建一个同名的索引） 
    ALTER TABLE user DROP INDEX idx_user_username;
CREATE INDEX idx_user_username ON user (username(8));
  4. 查看索引(如果没有使用use databases，那么需要指定DB_NAME) 
    SHOW INDEX FROM table_name [FROM db_name]
SHOW INDEX FROM [db_name.]table_name

MySQL 索引

概念
  • 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
  • 索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
  • 要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
分类
普通索引

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

  • 直接创建索引(length表示使用名称前1ength个字符)
    CREATE INDEX index_name ON table_name(column_name(length))

  • 修改表结构的方式添加索引
    ALTER TABLE table_name ADD INDEX index_name ON (column_name)

  • 创建表的时候同时创建索引
    CREATE TABLE table_name (
    id int(11) NOT NULL AUTO_INCREMENT ,
    title char(255) NOT NULL ,
    PRIMARY KEY (id),
    INDEX index_name (title)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  • 删除索引
    DROP INDEX index_name ON table_name;

  • 建立复合索引 CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);
    使用”表名_字段1名_字段2名”的方式

唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

  • 创建唯一索引:CREATE UNIQUE INDEX index_name ON table_name(column_name)
  • 修改表索引:ALTER TABLE table_name ADD UNIQUE index_name ON (column_name)
  • 创建表的时候直接指定 
    CREATE TABLE `table_name` (  
`id` int(11) NOT NULL AUTO_INCREMENT ,  
`title` char(255) NOT NULL ,  
PRIMARY KEY (`id`),  
UNIQUE index_name (title)  
);
主索引

必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

外键索引

如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

全文索引（FULLTEXT）

fulltext索引仅可用于 MyISAM 表,他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建

单列索引、多列索引

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

组合(复合)索引（最左前缀）

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引： ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。 建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

  • title,time
  • title 为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示

    • 使用到上面的索引
      SELECT * FROM article WHREE title=’测试’ AND time=1234567890;
      SELECT * FROM article WHREE title=’测试’;

    • 不使用上面的索引
      SELECT * FROM article WHREE time=1234567890;

BTree索引

哈希索引

  1. 主键索引：ALTER TABLE table_name ADD PRIMARY KEY (`column`)
  2. 唯一索引：ALTER TABLE table_name ADD UNIQUE ( column )
  3. 普通索引：ALTER TABLE table_name ADD INDEX index_name ( column )
  4. 全文索引：ALTER TABLE table_name ADD FULLTEXT ( column)
  5. 多列索引：ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

QA:

  1. 主键索引和唯一索引的区别：
    • 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
    • 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
    • 唯一性索引列允许空值，而主键列不允许为空值。
    • 主键列在创建时，已经默认为空值 + 唯一索引了。
    • 主键可以被其他表引用为外键，而唯一索引不能。
    • 一个表最多只能创建一个主键，但可以创建多个唯一索引。
    • 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
    • 在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。

MySQL优化

索引优化

  1. 何时使用聚集索引或非聚集索引？
  2. 索引不会包含有NULL值的列 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
  3. 使用短索引 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
  4. 索引列排序 MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
  5. like语句操作 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
  6. 不要在列上进行运算 例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:
    select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。 总结：MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引

优化文章

MySQL性能优化
MySQL的SQL优化技巧

MySQL事务

  1. ACID
    • 原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。
    • 一致性：执行事务前后，数据库从一个一致性状态转换到另一个一致性状态。
    • 隔离性：并发访问数据库时，一个用户的事物不被其他事务所干扰，各并发事务之间数据库是独立的。
    • 持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库 发生故障也不应该对其有任何影响。
  2. 事务隔离级别
    • READ_UNCOMMITTED（未提交读）:最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
    • READ_COMMITTED（提交读）:允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
    • REPEATABLE_READ（可重复读）:对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
    • SERIALIZABLE（串行）:最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。
CATALOG
    FEATURED TAGS
    SpringBoot Flyway MySQL Java8 HashMap 读书笔记 Netty Docker Java Stream gradle Nginx spring Redis JVM Grafana 大数据 Hadoop Kafka MQ
    FRIENDS