MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的 IO 场景，为了提高基本的 IO 效率， MySQL 进行 IO 的基本单位是 16 KB （后面统一使用 InnoDB 存储引擎讲解)
我们可以通过 "innodb_page_size" 来查查看当前 mysql 的页大小：
mysql> show global status like 'innodb_page_size';+------------------+-------+| Variable_name | Value |+------------------+-------+| Innodb_page_size | 16384 |+------------------+-------+1 row in set (0.01 sec)
也就是说磁盘这个硬件设备的基本单位是 512 B，而 MySQL 中的 InnoDB 引擎使用 16KB 进行 IO 交互。即 MySQL 和磁盘进行数据交互的基本单位是 16 KB 。
这个基本数据单元，在 MySQL 这里叫做 page（注意这和文件系统的 page 是不一样的）
mysql> create table myisam_test( -> id int primary key, -> name varchar(11) not null -> )engine=MyISAM; --使用engine=MyISAM存储引擎Query OK, 0 rows affected (0.00 sec)[root@VM-8-7-centos test]# lltotal 20-rw-r----- 1 mysql mysql 61 Aug 3 20:12 db.opt-rw-r----- 1 mysql mysql 8586 Aug 4 10:15 myisam_test.frm --表结构数据-rw-r----- 1 mysql mysql 0 Aug 4 10:15 myisam_test.MYD --该表对应的数据，当前没有数据，所以是0-rw-r----- 1 mysql mysql 1024 Aug 4 10:15 myisam_test.MYI --该表对应的主键索引数据
如果 InnoDB 没有显式指定主键的话，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整型。
最后我们简单的创建一张 InnoDB 存储引擎的表来看看其文件的类型：
mysql> create table innodb_test( -> id int primary key, -> name varchar(10) not null -> )engine=InnoDB; --使用engine=InnoDBQuery OK, 0 rows affected (0.02 sec)[root@VM-8-7-centos test]# lltotal 112-rw-r----- 1 mysql mysql 61 Aug 3 20:12 db.opt-rw-r----- 1 mysql mysql 8586 Aug 4 10:49 innodb_test.frm --表结构数据-rw-r----- 1 mysql mysql 98304 Aug 4 10:49 innodb_test.ibd --该表对应的主键索引和用户数据，虽然现在一行数据没有，但是该表并不为0，因为有主键索引数据
4、两种存储引擎的区别
区别项
InnoDB
MyISAM
事务
支持
不支持
锁粒度
行锁，适合高并发
表锁，不适合高并发
是否默认
默认
非默认
支持外键
支持外键
不支持
适合场景
读写均衡。适合写大于读、需要事务的场景
适合读多写少、不需要事务的场景
全文索引
可以通过插件实现
支持
其它区别项会在后面的学习中也体现出来！
Ⅵ. 索引的操作
1、索引的创建原则
索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：
需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，B+树为了维护索引有序性，都需要进行动态维护。
所以，索引并不是万能钥匙，它也是根据场景来使用的。
什么时候适用创建索引❓❓❓
字段有唯一性限制的，适用创建索引，比如商品编码。
经常用于 where 查询条件的字段。这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 group by 和 order by 的字段。这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+树中的记录都是排序好的。
什么时候不适合创建索引❓❓❓
表数据太少的时候，不适合创建索引。
where 条件、group by 和 order by 里用不到的字段，不适合创建索引。索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，不适合创建索引。比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
-- 使用删除其它索引的方式一，结果失败mysql> drop index PRIMARY on t; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY on t' at line 1-- 使用删除其它索引的方式二，结果失败mysql> alter table t drop index PRIMARY;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY' at line 1-- 使用删除主键索引特定的方式，结果删除成功！mysql> alter table t drop primary key;Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0
这可能是因为主键是唯一的原因才有这种特定的删除方式吧~
4、创建索引
① 创建主键索引
第一种方式：在创建表的时候，直接在字段名后指定 primary key：
create table user1( id int primary key, name varchar(30));
第二种方式：在创建表的最后，指定某列或某几列为主键索引：
create table user2( id int, name varchar(30), primary key(id));
第三种方式：创建表以后再添加主键：
alter table 表名 add primary key(列名);
主键索引的特点：
一个表中只能有一个主键索引，当然可以使用复合主键
主键索引的效率高
主键值不允许重复，且不能为 null
主键索引的列最好是整型比如 int
主键索引的列最好是自增类型，这样子能最大化的利用好索引的优势
② 创建唯一键索引
第一种方式：在表定义时，在某列后直接指定 unique 唯一属性：
create table user4( id int primary key, name varchar(30) unique);
第二种方式：创建表时，在表的后面指定某列或某几列为unique：
create table user5( id int primary key, name varchar(30), unique(name));
第三种方式：创建表以后再添加主键：
alter table 表名 add unique(列名);
唯一索引的特点：
一个表中，可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证这列不能有重复数据
如果一个唯一索引上指定 not null，等价于主键索引
③ 创建普通索引
第一种方式：在表的定义最后，指定某列为索引：
create table user8( id int primary key, name varchar(20), email varchar(30), index(name) --在表的定义最后，指定某列为索引);
MySQL 提供全文索引机制，但是有要求，要求表的存储引擎必须是 MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用 sphinx 的中文版(coreseek)。
添加全文索引操作
-- 第一种方式add fulltext index 索引名 on 表名 (column1, column2, ...);-- 第二种方式alter table 表名 add fulltext 索引名 on column1, column2, ...;-- 第三种方式create index 索引名 on 表名 (column1, column2, ...);-- 第四种方式：直接在创建表的时候指定fulltext (column1, column2, ...);
下面我们用第四种方式来测试一下：
-- 插入一张MyISAM存储引擎的表CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body))engine=MyISAM;-- 插入数据INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'), ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we will show ...'), ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...');
首先我们使用以前学过的模糊匹配来进行文本筛选：
mysql> select * from articles where body like '%database%';+----+-------------------+------------------------------------------+| id | title | body |+----+-------------------+------------------------------------------+| 1 | MySQL Tutorial | DBMS stands for DataBase ... || 5 | MySQL vs. YourSQL | In the following database comparison ... |+----+-------------------+------------------------------------------+2 rows in set (0.00 sec)
看似我们好像把需要的文本该检索出来了，但是其实用的并不是全文索引，只是普通遍历而已！
这我们可以使用 mysql 提供的 explain 工具分析一下当前的语句，是否用到了索引：
mysql> explain select * from articles where body like '%database%' \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: articles partitions: NULL type: ALLpossible_keys: NULL key: NULL --表示没有用到索引 key_len: NULL ref: NULL rows: 6 filtered: 16.67 Extra: Using where1 row in set, 1 warning (0.00 sec)
查询全文索引操作
既然上面没有用到索引，那么如何使用全文索引呢❓❓❓
这里就需要使用 match 和 against 关键字来配合，其语法如下所示：
select 列名 from 表名 where match(column1, column2, ...) against('search_expression' [in 'search_mode'] );
mysql> select * from articles where match(title, body) against('database');+----+-------------------+------------------------------------------+| id | title | body |+----+-------------------+------------------------------------------+| 5 | MySQL vs. YourSQL | In the following database comparison ... || 1 | MySQL Tutorial | DBMS stands for DataBase ... |+----+-------------------+------------------------------------------+2 rows in set (0.00 sec)-- 使用explain进行分析语句mysql> explain select * from articles where match(title, body) against('database') \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: articles partitions: NULL type: fulltextpossible_keys: title key: title --可以看到，查找方式是通过索引的 key_len: 0 ref: const rows: 1 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)
