1、MySQL存储引擎

• MySQL 存储引擎介绍(常用 InnoDB, MyISAM):

  • （1）、InnoDB 存储引擎InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB 是默认的 MySQL引擎。（2）、MyISAM 存储引擎MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事物。（3）、MEMORY 存储引擎MEMORY 存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。（4）、NDB 存储引擎DB 存储引擎是一个集群存储引擎，类似于 Oracle 的 RAC，但它是 Share Nothing 的架构，因此能提供更高级别的高可用性和可扩展性。NDB 的特点是数据全部放在内存中，因此通过主键查找非常快。关于 NDB，有一个问题需要注意，它的连接(join)操作是在 MySQL 数据库层完成，不是在存储引擎层完成，这意味着，复杂的 join 操作需要巨大的网络开销，查询速度会很慢。（5）、Memory (Heap) 存储引擎Memory 存储引擎（之前称为 Heap）将表中数据存放在内存中，如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。（6）、Archive 存储引擎正如其名称所示，Archive 非常适合存储归档数据，如日志信息。它只支持 INSERT 和 SELECT 操作，其设计的主要目的是提供高速的插入和压缩功能。（7）、Federated 存储引擎Federated 存储引擎不存放数据，它至少指向一台远程 MySQL 数据库服务器上的表，非常类似Oracle 的透明网关。（8）、Maria 存储引擎Maria 存储引擎是新开发的引擎，其设计目标是用来取代原有的 MyISAM 存储引擎，从而成为 MySQL 默认的存储引擎。上述引擎中，InnoDB 是事务安全的存储引擎，设计上借鉴了很多 Oracle 的架构思想，一般而言，在 OLTP应用中，InnoDB 应该作为核心应用表的首先存储引擎。InnoDB 是由第三方的 Innobase Oy 公司开发，现已被Oracle 收购，创始人是 Heikki Tuuri，芬兰赫尔辛基人，和著名的 Linux 创始人 Linus 是校友。
    

• 查看引擎

  -- 查看数据库支持的引擎
  show engines;
  mysql> show engines;
  +--------------------+---------+--------------+------------+
  | Engine             | Support | Transactions | Savepoints |
  +--------------------+---------+--------------+------------+
  | InnoDB             | DEFAULT | YES          | YES        |
  | MRG_MYISAM         | YES     | NO           | NO         |
  | MEMORY             | YES     | NO           | NO         |
  | BLACKHOLE          | YES     | NO           | NO         |
  | MyISAM             | YES     | NO           | NO         |
  | CSV                | YES     | NO           | NO         |
  | ARCHIVE            | YES     | NO           | NO         |
  | PERFORMANCE_SCHEMA | YES     | NO           | NO         |
  | FEDERATED          | NO      | NULL         | NULL       |
  +--------------------+---------+--------------+------------+-- 查看表引擎
  show creat table 表名\G
  eg.
  mysql> show create table user\G
  *************************** 1. row ***************************Table: user
  Create Table: CREATE TABLE `user` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',`name` varchar(50) NOT NULL COMMENT '姓名',`phone` varchar(15) NOT NULL DEFAULT '' COMMENT '手机号码',`address` varchar(100) NOT NULL DEFAULT '' COMMENT '详细住址',`gender` enum('M','W','unknown') NOT NULL DEFAULT 'unknown' COMMENT '性别',`age` tinyint(3) DEFAULT NULL COMMENT '年龄',`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=8441511 DEFAULT CHARSET=utf8
  1 row in set (0.01 sec)-- 查看所有表的引擎
  show table status\G
  eg.
  mysql> show table status\G
  *************************** 1. row ***************************Name: userEngine: InnoDBVersion: 10Row_format: DynamicRows: 9075731Avg_row_length: 109Data_length: 991952896
  Max_data_length: 0Index_length: 0Data_free: 7340032Auto_increment: 9149511Create_time: 2020-08-23 12:01:28Update_time: 2020-08-26 13:13:27Check_time: NULLCollation: utf8_general_ciChecksum: NULLCreate_options: Comment: 
  1 row in set (0.02 sec)

• 建表是指定引擎命令

  -- 语法
  create table 表名() engine=引擎名;
  eg.
  mysql> create table inno (id int auto_increment, PRIMARY KEY (`id`)) engine='InnoDB';
  Query OK, 0 rows affected (1.91 sec)
  eg.
  mysql> create table myisa (id int auto_increment, PRIMARY KEY (`id`)) engine='MyiSAM';
  Query OK, 0 rows affected (0.00 sec)------------------------------
  -- 查询表引擎
  mysql> show table status\G
  *************************** 1. row ***************************Name: innoEngine: InnoDBVersion: 10Row_format: DynamicRows: 0Avg_row_length: 0Data_length: 16384
  Max_data_length: 0Index_length: 0Data_free: 0Auto_increment: 1Create_time: 2020-08-26 13:16:50Update_time: NULLCheck_time: NULLCollation: utf8_general_ciChecksum: NULLCreate_options: Comment: 
  *************************** 2. row ***************************Name: myisaEngine: MyISAMVersion: 10Row_format: FixedRows: 0Avg_row_length: 0Data_length: 0
  Max_data_length: 1970324836974591Index_length: 1024Data_free: 0Auto_increment: 1Create_time: 2020-08-26 13:17:32Update_time: 2020-08-26 13:17:32Check_time: NULLCollation: utf8_general_ciChecksum: NULLCreate_options: Comment:
  

• 修改表引擎

  -- 语法
  alter table 表名 engine=引擎名称
  eg.
  mysql> show create table myisa\G
  *************************** 1. row ***************************Table: myisa
  Create Table: CREATE TABLE `myisa` (`id` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)mysql> alter table myisa engine='InnoDB';
  Query OK, 0 rows affected (1.13 sec)
  Records: 0  Duplicates: 0  Warnings: 0mysql> show create table myisa\G
  *************************** 1. row ***************************Table: myisa
  Create Table: CREATE TABLE `myisa` (`id` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)-- 修改默认引擎
  vim 配置文件
  [mysqld]
  default-storage-engine=MyIsAM
  重启服务即可
  

• Innodb， MyiSAM 选择

  MyISAM 一般来说MyISAM不需要用到事务的时候做很多count计算多查询， 少插入，少修改
  InnoDB： 可靠性要求高的，或者要求支持事务想要用到外键约束的时候插入， 修改较多
  推荐：推荐用InnoDB
  

2、索引

• 什么是索引

  	索引是一个单独的，存储在磁盘中上的数据库结构，它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找出在某列或多列中有特定值的行。索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为：快速查找排好序的一种数据结构。
  

• 索引的优点

  1.索引大大减小了服务器需要扫描的数据量
  2.索引可以帮助服务器避免排序和临时表
  3.索引可以将随机IO变成顺序IO
  4.索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
  5.关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
  

• 索引的缺点

  1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
  2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
  3.如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  4.对于非常小的表，大部分情况下简单的全表扫描更高效；
  5.索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。6.因此应该只为最经常查询和最经常排序的数据列建立索引。7.MySQL里同一个数据表里的索引总数限制为16个。
  

• 常见索引类型

  1.普通索引- 普通索引（index）顾名思义就是各类索引中最为普通的索引，主要任务就是提高查询速度。其特点是允许出现相同的索引内容，允许空（null）值
  2.唯一索引- （unique）顾名思义就是不可以出现相同的索引内容，但是可以为空（null）值
  3.主键索引
  4.组合索引
  5.全文索引
  

• 创建唯一索引

  -- 创建表的时候创建唯一索引
  create table test_u_index(id int auto_increment NOT null PRIMARY KEY,names varchar(50),age int,unique index(names)
  );eg:
  mysql> show create table test_u_index\G
  *************************** 1. row ***************************Table: test_u_index
  Create Table: CREATE TABLE `test_u_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`names` varchar(50) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `names` (`names`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)------------------------分割线------------------------------- 直接为表添加索引
  语法:alter table 表名 add 索引类型 索引名称 (字段名称);eg.ALTER TABLE test_u_index add UNIQUE index ix_age(age);mysql> show create table test_u_index\G
  *************************** 1. row ***************************Table: test_u_index
  Create Table: CREATE TABLE `test_u_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`nickname` varchar(50) DEFAULT NULL,`names` varchar(50) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `names` (`names`),UNIQUE KEY `ix_age` (`age`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)**注意: ** 如果没有指定索引名称, 会以字段名为默认的索引名称 -----------------------------分割线-------------------------------
  -- 直接创建索引
  语法:create 索引类型 索引名 on table_name(字段名)
  eg.CREATE unique INDEX ix_nickname on test_u_index(nickname);mysql> show create table test_u_index\G
  *************************** 1. row ***************************Table: test_u_index
  Create Table: CREATE TABLE `test_u_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`nickname` varchar(50) DEFAULT NULL,`names` varchar(50) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `names` (`names`),UNIQUE KEY `ix_age` (`age`),UNIQUE KEY `ix_nickname` (`nickname`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

• 查看索引

  -- 语法show index from table_name;
  

• 删除索引

  -- 语法1:drop index index_name on table_name
  eg.mysql> drop index ix_age on test_u_index;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table test_u_index\G*************************** 1. row ***************************Table: test_u_indexCreate Table: CREATE TABLE `test_u_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`nickname` varchar(50) DEFAULT NULL,`names` varchar(50) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `names` (`names`),UNIQUE KEY `ix_nickname` (`nickname`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)------------------分割线--------------------------------
  -- 语法2:alter table 表名 drop index 索引名;eg.mysql> alter table test_u_index drop index names;Query OK, 0 rows affected (0.57 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table test_u_index\G*************************** 1. row ***************************Table: test_u_indexCreate Table: CREATE TABLE `test_u_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`nickname` varchar(50) DEFAULT NULL,`names` varchar(50) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `ix_nickname` (`nickname`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

• 主键索引

  把主键添加索引就是主键索引，它是一种特殊的唯一索引，不允许有空值，而唯一索引（unique是允许为空值的）。指定 为“PRIMARY KEY”

  主键：主键是表的某一列，这一列的值是用来标志表中的每一行数据的。

  注意：每一张表只能拥有一个主键

• 全文索引

  全文索引是将存储在数据库中的文章或者句子等任意内容信息查找出来的索引，单位是词。全文索引也是目前搜索引擎 使用的一种关键技术。指定为 fulltex

• 外键

  ​ 外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性。

  -- 语法:语法：foreign key (字段名) references 关联的表名(关联表的字段名)
  

  注意:

  ​ （1）俩个表，主键跟外键的字段类型一定要相同

  ​ （2）要使用外键约束表的引擎一定得是InnoDB引擎，MyISAM是不起作用的

  ​ （3）在干掉外键索引之前必须先把外键约束删除，才能删除索引

• 联合索引

  联合索引又称组合索引或者复合索引，是建立在俩列或者多列以上的索引。

  -- 语法alter table 表名 add index(字段1,字段2,字段3);
  eg.mysql> create index ix_lh on test_u_index(names, age);Query OK, 0 rows affected (0.11 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table test_u_index\G*************************** 1. row ***************************Table: test_u_indexCreate Table: CREATE TABLE `test_u_index` (`id` int(11) NOT NULL AUTO_INCREMENT,`nickname` varchar(50) DEFAULT NULL,`names` varchar(50) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `ix_nickname` (`nickname`),KEY `ix_lh` (`names`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

  • 优点

    • 在查询条件两个以上的时候， 联合索引的索引体积比单独索引的体积要小，而且只是一个索引树，相比单独列的索引要更加的节省时间复杂度和空间复杂度

  • 注意：

    • 联合索引最左原则
      • Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效

• MySQL 索引失效

  • where 子句 对 null 判断空的时候，会扫描全表， 索引失效
  • where 子句中国使用 !=, <> 的符号， 索引失效
  • 避免在where子句中使用 or, 如果几个字段中有一个没有索引的话， 索引失效
  • where 子句中 = 左边使用表达式或者函数操作
  • where 子句， like 以 % 开头的， 索引失效
  • 联合索引， 没有遵循最左原则， 索引失效
  • 字符串不加引号， 索引失效