太月星网站建设,深圳关键词优化公司哪家好,wordpress 去掉页头,网站开发去哪里找工作文章目录 1.聚簇索引 VS 非聚簇索引经典问题 2.索引操作创建主键索引唯一索引的创建普通索引的创建全文索引的创建查询索引删除索引索引创建原则 1.聚簇索引 VS 非聚簇索引
之前介绍的将所有的数据都放在叶子节点的这种存储引擎对应的就是 InnoDB 默认存储表数据的存储结构。 … 文章目录 1.聚簇索引 VS 非聚簇索引经典问题 2.索引操作创建主键索引唯一索引的创建普通索引的创建全文索引的创建查询索引删除索引索引创建原则 1.聚簇索引 VS 非聚簇索引
之前介绍的将所有的数据都放在叶子节点的这种存储引擎对应的就是 InnoDB 默认存储表数据的存储结构。
下面我们简单介绍一下 MyISAM 存储引擎-主键索引。MyISAM 引擎同样使用B树作为索引结果叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主索引Col1 为主键。 其中MyISAM 最大的特点是将索引Page和数据Page分离也就是叶子节点没有数据只有对应数据的地址。相较于InnoDB 索引InnoDB 是将索引和数据放在一起的。
--终端A
mysql create database myisam_test; --创建数据库
Query OK, 1 row affected (0.00 sec)
mysql use myisam_test;Database changed
mysql create table mtest(
- id int primary key,
- name varchar(11) not null
- )engineMyISAM; --使用engineMyISAM
Query OK, 0 rows affected (0.01 sec)--终端B
[root-centos mysql]# ls myisam_test/ -al --mysql数据目录下
total 28
drwxr-x--- 2 mysql mysql 4096 Jun 13 13:33 .
drwxr-x--x 13 mysql mysql 4096 Jun 13 13:32 ..
-rw-r----- 1 mysql mysql 61 Jun 13 13:32 db.opt
-rw-r----- 1 mysql mysql 8586 Jun 13 13:33 mtest.frm --表结构数据
-rw-r----- 1 mysql mysql 0 Jun 13 13:33 mtest.MYD --该表对应的数据当前没有数据所以是0
-rw-r----- 1 mysql mysql 1024 Jun 13 13:33 mtest.MYI --该表对应的主键索引数据MyISAM 这种用户数据与索引数据分离的索引方案叫做非聚簇索引
--终端A
mysql create database innodb_test;
Query OK, 1 row affected (0.00 sec)
mysql use innodb_test;Database changed
mysql create table itest(
- id int primary key,
- name varchar(11) not null
- )engineInnoDB; --使用engineInnoDB
Query OK, 0 rows affected (0.02 sec)--创建数据库--终端B
[rootcentos mysql]# ls innodb_test/ -altotal 120
drwxr-x--- 2 mysql mysql 4096 Jun 13 13:39 .
drwxr-x--x 14 mysql mysql 4096 Jun 13 13:38 ..
-rw-r----- 1 mysql mysql 61 Jun 13 13:38 db.opt
-rw-r----- 1 mysql mysql 8586 Jun 13 13:39 itest.frm --表结构数据
-rw-r----- 1 mysql mysql 98304 Jun 13 13:39 itest.ibd --该表对应的主键索引和用户数据虽然现在一行数据没有但是该表并不为0因为有主键索引数据InnoDB 这种用户数据与索引数据在一起索引方案叫做聚索引。当然MySQL除了默认会建立主键索引外我们用户也有可能建立按照其他列信息建立的索引一般这种索引可以叫做辅助(普通)索引。 对于 MyISAM,建立辅助(普通)索引和主键索引没有差别无非就是主键不能重复而非主键可重复。下图就是基于 MyISAM 的 col2建立的索引和主键索引没有差别。 InnoDB 除了主键索引用户也会建立辅助普通索引我们以上表中的Col3 建立对应的辅助索引如下图
InnoDB 的非主键索引中叶子节点并没有数据而只有对应记录的 key 值。
通过辅助普通索引找到目标记录需要两遍索引首先检索辅助索引获得主键然后用主键到主索引中检索获得记录。这种过程就叫做回表查询这种多个索引的就是一个表可以对应多个 B树 为何InnoDB 针对这种辅助普通索引的场景不给叶子节点也附上数据呢原因就是太浪费空间了。【没必要再搞一份重复的数据!】索引的本质B树
经典问题 mysql默认创建一列带有主键的列那为什么插入无主键表时是无序的显示表仍然无序不是有默认主键吗 mysql默认的主键肯定不会按着你的想法来他有自己的设置你插入顺序怎样显示就怎样。 那为什么查询还那么慢 假设有abc三个字段你没有设置主键假设mysql默认使得d为隐藏主键你用a去查就无法使用d的索引结构只能线性遍历所以很慢。【经过了解一棵树可能有多个索引一个索引本质就是一个数据结构实例主键索引/普通索引】为了讲解清楚通常把整颗b树呈现给你但实际上查询数据时是按需加载即用哪个加载哪个结点 叶子节点全部用链表级联起来 a.首先这是b的特点 b.我们比较希望进行范围查找 是mysql用了B结构B结构是非叶子节点不相连叶子节点相连 一段数据/一张page中数据连续。 理解curd 对mysql内部的数据等做操作(CURD)的时候本质其实就是操作文件内容。
文件必须先被打开对文件内容做任何操作都不是直接在磁盘设备上做操作的任何磁盘数据在进程中要进行操作本质都必须在内存中进行如果数据不在内存中呢?换入换出【磁盘到内存内存到磁盘】mysql内部一定要有自己的内存管理。mysql自己启动的时候要预先申请一批内存空间。 索引覆盖 复合索引通常用于{ab}用a找b即不再通过ab找到主键key普通索引存的不是数据而是主键继而通过key找b覆盖了主键索引。不能拿b找a因为匹配时都是1从左向右索引最左匹配原则
2.索引操作
创建主键索引 第一种方式-- 在创建表的时候直接在字段名后指定 primary key create table user1(id int primary key, name varchar(30));第二种方式-- 在创建表的最后指定某列或某几列为主键索引 create table user2(id int, name varchar(30), primary key(id)); 第三种方式-- 创建表以后再添加主键 create table user3(id int, name varchar(30));
alter table user3 add primary key(id);主键索引的特点 一个表中最多有一个主键索引当然可以有复合主键复合主键也是一个主键只不过主键由两个字段构成。 主键索引的效率高主键不可重复。 创建主键索引的列它的值不能为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));第三种方式 create table user6(id int primary key, name varchar(30)
alter table user6 add unique(name);唯一索引的特点 一个表中可以有多个唯一索引 查询效率高 如果在某一列建立唯一索引必须保证这列不能有重复数据 如果一个唯一索引上指定not null等价于主键索引
普通索引的创建 第一种方式 create table user8(id int primary key,name varchar(20),email varchar(30),index(name) --在表的定义最后指定某列为索引
);第二种方式 create table user9(
id int primary key,
name varchar(20),
email varchar(30)
);
alter table user9 add index(name); --创建完表以后指定某列为普通索引第三种方式 create table user10(id int primary key, name varchar(20), email varchar(30)
);
create index idx_name on user10(name);-- 创建一个索引名为 idx_name 的索引 普通索引的特点 一个表中可以有多个普通索引普通索引在实际开发中用的比较多 如果某列需要创建索引但是该列有重复的值那么我们就应该使用普通索引
全文索引的创建
当对文章字段或有大量文字的字段进行检索时会使用到全文索引。MySQL提供全文索引机制但是有要求要求表的存储引擎必须是MyISAM而且默认的全文索引支持英文不支持中文。如果对中文进行全文检索可以使用sphinx的中文版(coreseek)。
CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body))engineMyISAM;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 ...);查询有没有database数据 如果使用如下查询方式虽然查询出数据但是没有使用到全文索引
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 ... |-----------------------------------------------------------------可以用explain工具看一下是否使用到索引 mysql explain select * from articles where body like %database%\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: articlestype: ALLpossible_keys: NULLkey: NULL key为null表示没有用到索引
key_len: NULLref: NULLrows: 6Extra: Using where1 row in set (0.00 sec)如何使用全文索引呢 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 ... |-----------------------------------------------------------------mysql explain - SELECT * FROM articles WHERE MATCH (title,body) AGAINST (database)\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: articlestype: fulltextpossible_keys: titlekey: title key用到了titlekey_len: 0ref:
rows: 1Extra: Using where查询索引
show keys from 表名
show index from 表名;
desc 表名删除索引 第一种方法-删除主键索引: alter table 表名 drop primary key;第二种方法-其他索引的删除: alter table 表名 drop index 索引名; --索引名就是show keys from 表名中的 Key _name 字段mysql alter table user10 drop index idx name;第三种方法方法: drop index 索引名 on 表名mysql drop index name on user8;索引创建原则
比较频繁作为查询条件的字段应该创建索引 唯一性太差的字段不适合单独创建索引即使频繁作为查询条件 更新非常频繁的字段不适合作创建索引 不会出现在where子句中的字段不该创建索引
