对京东网站建设的总结,网站怎么赚钱的,陕西住房和城乡建设厅官网,《动画造型设计》「前言」文章内容大致是MySQL索引的学习。 「归属专栏」MySQL 「主页链接」个人主页 「笔者」枫叶先生(fy) 目录 一、索引概念二、从硬件角度理解2.1 磁盘2.2 结论 三、从软件角度理解四、共识五、索引的理解5.1 一个现象和结论5.2 对Page进行建模5.3 索引可以采用的数据结构5.… 「前言」文章内容大致是MySQL索引的学习。 「归属专栏」MySQL 「主页链接」个人主页 「笔者」枫叶先生(fy) 目录 一、索引概念二、从硬件角度理解2.1 磁盘2.2 结论 三、从软件角度理解四、共识五、索引的理解5.1 一个现象和结论5.2 对Page进行建模5.3 索引可以采用的数据结构5.4 聚簇索引和非聚簇索引 六、索引操作6.1 创建主键索引6.2 唯一索引的创建6.3 普通索引的创建6.4 查询索引6.5 删除索引6.6 全文索引的创建6.7 索引创建原则 一、索引概念
如果没有索引那么在查询数据时是直接一条条遍历表中的数据那么查询的时间复杂度将会是O(N)如果数据库表有索引就能提高海量数据的检索速度就能大大提高查找的效率索引概念索引是指对数据库中的数据进行结构化的组织和管理以提高数据的检索效率。索引的本质就是一种数据结构
MySQL是一款网络服务器MySQL服务端是一直在内存中MySQL客户端的CURD操作都会交给MySQL的服务端完成即MySQL的所有CURD操作都是在内存中进行的即索引也是在内存中的进行的。
比如假设数据库组织数据的方式是线性的查询的时间复杂度将会是O(N)如果数据库组织数据的方式是以二叉树的那么查询的时间复杂度将会大大降低。
所以索引虽然提高了数据的查询速度但在一定程度上也会降低数据增删改的效率因为这时在对表中的数据进行增删改操作时除了需要进行对应的增删改操作之外可能还需要对底层建立的数据结构进行调整维护维护结构是需要成本的。
即查询速度的提高是以插入、更新、删除的速度为代价的 常见的索引分为 主键索引primary key唯一索引unique普通索引index全文索引fulltext 先见一见索引 使用如下SQL创建一个海量数据表
drop database if exists index_demon;
create database if not exists index_demon default character set utf8;
use index_demon;-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性所以使用存储过程来创建-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ;
declare return_str varchar(255) default ;
declare i int default 0;
while i n do
set return_str concat(return_str,substring(chars_str,floor(1rand()*52),1));
set i i 1;
end while;
return return_str;
end $$
delimiter ;-- 产生随机数字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i floor(10rand()*500);
return i;
end $$
delimiter ;-- 创建存储过程向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit 0;
repeat
set i i 1;
insert into EMP values ((starti)
,rand_string(6),SALESMAN,0001,curdate(),2000,400,rand_num());
until i max_num
end repeat;
commit;
end $$
delimiter ;-- 雇员表
CREATE TABLE EMP (empno int(6) unsigned zerofill NOT NULL COMMENT 雇员编号,ename varchar(10) DEFAULT NULL COMMENT 雇员姓名,job varchar(9) DEFAULT NULL COMMENT 雇员职位,mgr int(4) unsigned zerofill DEFAULT NULL COMMENT 雇员领导编号,hiredate datetime DEFAULT NULL COMMENT 雇佣时间,sal decimal(7,2) DEFAULT NULL COMMENT 工资月薪,comm decimal(7,2) DEFAULT NULL COMMENT 奖金,deptno int(2) unsigned zerofill DEFAULT NULL COMMENT 部门编号
);-- 执行存储过程添加8000000条记录
call insert_emp(100001, 8000000);
上述SQL中创建了一个名为index_demon的数据库在该数据库中创建了一个名为EMP的员工表并向表当中插入了八百万条记录。
将上述的SQL语句保存在一个文件中然后MySQL中使用source命令执行文件中的SQL 等待SQL语句执行完成时间有点久 查看数据库就能看到一个名为index_demon的数据库 在查看EMP表中的数据时可以带上limit子句数据量太大 查询EMP表中指定工号的员工信息该表没有索引每次查询都要花费几秒进行查询在实际项目中如果放在公网中假如同时有1000个人并发查询那很可能就死机 下面给该表创建索引 再查询EMP表中指定工号的员工信息发现查询的效率大大提高了 根据实验结果发现索引能大大提高查询数据的效率这就是索引的价值所在。
二、从硬件角度理解
2.1 磁盘
MySQL给用户提供存储服务存储的数据在磁盘这个外设当中磁盘是计算机中的一个机械设备相比于计算机的其他电子元件磁盘的效率是比较低的而如何提高效率是MySQL的一个重要话题因此我们有必要了解一下磁盘的相关内容
磁盘在该文章有详细介绍文章链接点击传送这里就简单说一下概念 磁盘的整体结构如下物理结构 磁盘的存储结构 磁道Tracker磁盘表面被分为许多同心圆每个同心圆称为一个磁道每个磁道都有自己的编号柱面Cylinder多个磁盘的同一个磁道重叠起来叫做磁柱磁面Head一个盘面每个盘面都有自己的编号扇区Sector磁道的一个扇形区每个扇区都有自己的编号
磁盘的基本读写单位是扇区扇区大小一般是 512字节512byte 每个盘片被分为若干个同心圆每一个同心圆就是一个磁道而每个磁道被划分为若干个扇区每个扇区的大小都是 512字节
注意近三十年来扇区大小一直是512字节但最近几年正在迁移到更大、更高效的4096字节扇区通常称为4K扇区
我们在使用Linux所看到的大部分目录或者文件其实就是保存在硬盘当中的。(当然有一些内存文件系统如 proc sys 之类我们不考虑)
比如数据库文件本质其实就是保存在磁盘的盘片当中就是一个一个的文件
在该目录下存储着mysql的文件 ls /var/lib/mysql新建一个数据库本质上就是在该目录下创建一个目录图中颜色没有高亮普通用户查看 所以最基本的找到一个文件的全部本质就是在磁盘找到所有保存文件的扇区
而我们能够定位任何一个扇区那么便能找到所有扇区因为查找方式是一样的 扇区的定位方式 定位扇区时采用CHS寻址方式
注通过 柱面Cylinder —— 磁头Head —— 扇区Sector进行寻址这种寻址方法为 CHS寻址
说明一下
CHS寻址方式是磁盘定位扇区的方式但实际CHS寻址方式对磁盘以外的设备来说没什么作用因此系统软件在定位磁盘上的数据时采用的是LBALogicalBlock Address逻辑区块地址LBA是描述计算机存储设备上数据所在区块的通用机制LBA和CHS之间可以通过计算公式进行相互转换LBA存在的意义就是对底层逻辑器件进行虚拟化让系统软件可以不用关心底层硬件具体的寻址方式而实际底层硬件采用的还是CHS寻址方式
注以上在磁盘篇章有详细介绍这里就不再谈论。
2.2 结论 操作系统与磁盘交互的基本单位 操作系统与磁盘进行IO交互的基本单位是4KB而不是扇区的大小512字节原因如下
操作系统与磁盘进行IO交互时如果直接以扇区的大小作为IO的基本单位那么这时系统的IO代码和硬件就是强相关的将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码此外以扇区的大小作为IO的基本单位太小了这就意味着读取同样的数据内容需要进行更多次的磁盘访问而磁盘的效率是比较低的这样IO效率就降低了即以下两个点不想让 OS 的代码和硬件强耦合实现硬件和系统的解耦操作系统与磁盘以4KB作为IO交互的基本单位是为了提高IO效率局部性原理 磁盘随机访问(Random Access)与连续访问(Sequential Access) 随机访问本次IO所给出的扇区地址和上次IO给出扇区地址不连续这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。连续访问如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的那磁头就能很快的开始这次IO操作这样的多个IO操作称为连续访问
因此尽管相邻的两次IO操作在同一时刻发出但如果它们的请求的扇区地址相差很大的话也只能称为随机访问而非连续访问
连续访问中的连续指的是访问的扇区地址的连续而不是访问时间的连续由于连续访问不需要过多的定位因此效率比较高
三、从软件角度理解 MySQL与磁盘交互的基本单位 MySQL作为一款应用软件可以想象成是一种特殊的文件系统它有着更高频的IO场景因此为了提高基本的IO效率MySQL与磁盘交互的基本单位是16KB
通过show命令查看系统中的全局变量可以看到InnoDB存储引擎交互的基本单位是16KB
mysql show global status like innodb_page_size;注后面统一使用InnoDB存储引擎进行讲解
也就是说磁盘这个硬件设备的基本单位是512字节而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即 MySQL 和磁盘进行数据交互的基本单位是16KB。这个基本数据单元在 MySQL 这里叫做page注意和系统的page区分 Buffer Pool 实际上MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存这块内存空间叫做Buffer Pool后续磁盘中加载的数据就会保存在Buffer Pool中刷新数据时也就是将Buffer Pool中的数据刷新到磁盘然而OS是有内核缓冲区的。因此MySQL从磁盘读取数据时需要先将数据从磁盘读取到内核缓冲区再将数据从内核缓冲区读取到Buffer PoolMySQL将数据刷新到磁盘时同样需要先将数据从Buffer Pool刷新到内核缓冲区再将数据从内核缓冲区刷新到磁盘。 四、共识
为了方面下面理解我们需要有以下共识
MySQL 中的数据文件是以page为单位保存在磁盘当中的MySQL的pageMySQL 的 CURD 操作都需要通过计算找到对应的插入位置或者找到对应要修改或者查询的数据。而只要涉及计算就需要CPU参与而为了便于CPU参与一定要能够先将数据移动到内存当中所以在特定时间内数据一定是磁盘中有内存中也有。后续操作完内存数据之后以特定的刷新策略刷新到磁盘。而这时就涉及到磁盘和内存的数据交互也就是IO了而此时IO的基本单位就是Page为了更好的进行上面的操作 MySQL 服务器在内存中运行的时候在服务器内部就申请了被称为 Buffer Pool 的的大内存空间来进行各种缓存。其实就是很大的内存空间来和磁盘数据进行IO交互。为了更高的效率一定要尽可能的减少系统和磁盘IO的次数
五、索引的理解
5.1 一个现象和结论
建立一个测试表表当中包含用户的id、年龄和姓名并将用户的id设置成主键
create table if not exists user (
id int primary key, -- 一定要添加主键只有这样才会默认生成主键索引
age int not null,
name varchar(16) not null
);然后插入多条记录并且插入数据时没有按照主键的大小顺序插入即乱序插入
insert into user (id, age, name) values(3, 18, 张三);
insert into user (id, age, name) values(4, 16, 李四);
insert into user (id, age, name) values(2, 26, 王五);
insert into user (id, age, name) values(5, 36, 赵六);
insert into user (id, age, name) values(1, 56, 田七);插入完成后查看表中的数据时却发现显示出来的数据是按照主键进行有序排列的 引入主键之后插入的数据自动按照主键排序这个排序工作是谁做的为什么要这么做
这个排序工作是MySQL自己做的进行排序的目的是为了方便引入目录下面谈 重谈MySQL的pageMySQL与磁盘进行交互时为什么不是按需交互而是以Page为基本单位进行交互16KB 比如上面的5条记录如果MySQL要查找id2的记录第一次加载id1第二次加载id2一次一条记录那么就需要2次IO。如果要找id5那么就需要5次IO但是如果这5条(或者更多)都被保存在一个Page中(16KB能保存很多记录)那么第一次IO查找id2的时候整个Page会被加载到MySQL的Buffer Pool中这里完成了一次IO。往后如果在查找id1,3,4,5等完全不需要进行IO了而是直接在内存中进行了。所以就在单Page里面就可以大大减少IO的次数往往IO效率低下的最主要矛盾不是IO单次数据量的大小而是IO的次数导致IO效率低下 怎么保证用户一定下次找的数据就在这个Page里面 这个是无法保障的但是用户要找的数据会有很大的概率会在这个page里面局部性原理局部性原理当一个数据正在被访问时那么下一次有很大可能会访问其周围的数据如果局部性原理没有起作用那就再把对应的Page加载到内存当中即可
综上MySQL与磁盘进行交互时以Page为基本单位可以减少与磁盘IO交互的次数进而提高IO的效率
5.2 对Page进行建模
MySQL中要管理很多数据文件在运行期间一定有大量的Page需要被换入换出因此MySQL一定需要将内存中大量的Page管理起来要管理MySQL内部的所有的Page就需要对Page进行先描述再组织所以不能简单认为Page就是一个内存块Page内部也必须写入对应的管理信息
对Page进行建模如下
struct page
{struct page *next;struct page *prev;char buffer[NUM];
}
// 一个Page大小16KB不同的Page 在 MySQL 中都是16KB 都是使用prev和next指针构成双向链表从而用链表的形式对Page进行了管理
假设测试表中的记录都在同一个Page当中那么该Page的结构大致如下
每个Page结构体内部的数据会按照主键进行排序从上面的Page内数据记录可以看出数据是有序且彼此关联的。目的是为了优化数据查询的效率只要设置了主键即便向表中插入的数据是乱序的MySQL底层也会自动按照主键对插入的数据进行排序创建了主键便是使用了索引后序谈 单个Page内创建页内目录引入页目录 Page结构体内部存储的数据记录是以单链表的形式组织起来的。当页内部的数据量增多时如果直接进行线性遍历会导致效率低下。为了提高查询效率可以在Page结构体内部引入页内目录页内目录将Page结构体内部存储的数据记录按照主键划分为若干区域并存储了这些区域的最小键值。在查询数据时可以先通过页内目录找到目标数据所在区域的起始记录然后再从该记录开始向后遍历找到目标记录。通过引入页内目录可以减少不必要的线性遍历提高查询效率 什么是页目录 比如我们在看《谭浩强C程序设计》这本书的时候如果我们要看指针章节找到该章节有两种做法
第一种从头逐页的向后翻直到找到目标内容第二种通过书提供的目录发现指针章节在234页(假设)那么我们便直接翻到234页。同时查找目录的方案可以顺序找不过因为目录肯定少所以可以快速提高定位本质上书中的目录是多花了纸张的但是却提高了效率所以目录是一种“空间换时间的做法每个Page结构体内部的数据会按照主键进行排序其实就是为了引入页内目录因为只有数据按照主键排序后引入页内目录才有意义就像书中每一页都是按照页码进行排序的一样如果一本书的页码是乱序的那么它的目录根本就没有意义
上面是理解单个Page下面理解多个Page 多个Page 随着数据量不断增大单个Page中无法存下所有数据这时就需要用多个Page来存储数据这时在查询数据时就需要先遍历Page双链表确定目标数据在哪一个Page然后再在该Page内部找到目标数据 Page之上创建页目录 随着Page的增多遍历Page数量太大本质也是线性遍历也会导致查找效率降低这时可以给各个Page结构体也建立页目录页目录中的每个目录项都指向一个Page而这个目录项存放的就是其指向的Page中存放的最小数据的键值其中每个目录项的构成是键值指针该目录不存储数据只存放页目录着数据量不断增大Page变得越来越多这时一个页目录无法管理所有的Page这时就需要更多个的页目录 注意目录页的本质也是页普通页中存的数据是用户数据而目录页中存的数据是普通页的地址 页目录之上再创建页目录 随着数据量不断增大我们可以不断在页目录之上再创建页目录最终就一定能够得到一个入口页目录这时在查询数据时就可以从入口页目录开始不断查询页目录最终找到目标数据所在的Page然后再在该Page内部找到目标数据 这个矮胖的树实际就是一棵B树这棵B树就是InnoDB的索引结构存储结构查找的时候自定向下找只需要加载部分目录页到内存即可完成算法的整个查找过程大大减少了IO次数 B树中的Page结点无需全部加载到Buffer Pool中 当对MySQL中的某张表进行增删查改操作时不需要将其对应B树的所有结点全量加入到Buffer Pool中甚至在刚开始时只需要将B树的根结点加入到Buffer Pool中当后续访问表中的数据时再将该数据对应路径上的结点加入到Buffer Pool中即可对于其他不需要的结点根本不用加入到Buffer Pool中这一点和操作系统中的页表是很像
5.3 索引可以采用的数据结构
链表查找时是线性遍历效率太低。普通二叉搜索树可能退化成线性结构这时查找还是线性遍历。AVL树和红黑树虽然保证了二叉树是绝对或近似平衡的不会退化成线性结构但AVL树和红黑树都是二叉树结构这就意味着树的层高会比较高而查询数据时都是从根结点开始向下进行查找的这也就意味着在查询过程中需要遍历更多结点如果这些结点还没有被加载到Buffer Pool中这时就需要进行更多次的IO操作哈希表可以官方的索引实现方式中MySQL是支持HASH的只不过InnoDB和MyISAM存储引擎并不支持。哈希表的优点就是它的时间复杂度是 O(1) 的但哈希表也有一个缺点就是不利于进行数据的范围查找
常见的存储引擎与其所支持的索引类型 注这里的BTREE指的是B树 B树 VS B树 B树是B树的一种变形结构
B树中的所有结点中都同时包括索引信息和数据信息由于一个Page的大小是固定的因此非叶子结点中如果包含了数据信息那么这些结点中能够存储的索引信息一定会变少这时这棵树形结构一定会变得更高更瘦当查询数据时就可能需要与磁盘进行更多次的IO操作IO效率降低B树中的各个叶子结点之间没有连接起来这将不利于进行数据的范围查找
B树结构如下
B树的每个节点只包含键值所有的数据记录都存储在叶子节点中并且叶子节点之间通过指针连接成一个有序链表范围查询非常高效节点不存储data这样一个节点就可以存储更多的key可以使得树更矮所以IO操作次数更少
B树结构如下 5.4 聚簇索引和非聚簇索引 MyISAM存储引擎 - 主键索引结构 InnoDB存储引擎采用B树作为索引的基本数据结构MyISAM存储引擎同样采用B树作为索引的基本数据结构与InnoDB存储引擎的B树不同的是MyISAM存储引擎的B树的叶子结点存放的不是数据记录而是数据记录对应的地址非聚簇索引像MyISAM存储引擎这种将数据记录与索引结构分离的索引方案叫做非聚簇索引
注图中Col1列为主键 InnoDB存储引擎 - 主键索引结构 InnoDB是将索引和数据放在一起的聚簇索引像InnoDB存储引擎这种将数据记录与索引结构放在一起的索引方案叫做聚簇索引 聚簇索引 VS 非聚簇索引实验 创建一个是InnoDB为索引的表
mysql create table if not exists test1(- id int unsigned auto_increment primary key,- name varchar(20)- )engineInnoDB;进入该路径找到自己所在的数据库
ls /var/lib/mysql -l进入自己所在的数据库
当采用InnoDB存储引擎创建表时在数据库对应的目录下会新增两个文件
xxx.frm文件该文件中存储的是表结构相关的信息采用InnoDB存储引擎创建表时会生成一个xxx.ibd文件该文件中存储的是索引和数据相关的信息这就是所谓的聚簇索引索引和数据是存储在同一个文件中的 再创建一个test2的表使用MyISAM存储引擎创建表
mysql create table if not exists test1(- id int unsigned auto_increment primary key,- name varchar(20)- )engine MyISAM;当采用MyISAM存储引擎创建表时在数据库对应的目录下会新增三个文件
xxx.frm文件该文件中存储的是表结构相关的信息采用MyISAM存储引擎创建表时会生成一个xxx.MYD文件和一个xxx.MYI文件其中xxx.MYD文件中存储的是数据相关的信息而xxx.MYI文件中存储的是索引相关的信息这就是所谓的非聚簇索引索引和数据是分开存储的 普通索引 MySQL 除了默认会建立主键索引外我们用户也有可能建立按照其他列信息建立的索引一般这 种索引可以叫做辅助普通索引 MyISAM存储引擎 - 普通索引结构 MyISAM存储引擎的普通索引采用的也是B树结构与主键索引唯一不同的地方就是普通索引的B树中的键值可以重复。因此一张表可能会同时存在多个B树结构但由于MyISAM存储引擎的B树叶子结点中存储的是对应的数据记录的地址因此有效数据只会存储一份
对于 MyISAM建立辅助普通索引和主键索引没有差别无非就是主键不能重复而非主键可重复如下图 InnoDB存储引擎 - 普通索引结构 同样 InnoDB除了主键索引用户也会建立辅助普通索引
InnoDB存储引擎的普通索引采用的也是B树结构但普通索引的B树中的键值可以重复并且B树的叶子结点中存储的不是数据记录而是对应数据记录的主键值。当根据普通索引查询数据时会先查找普通索引对应的B树找到目标记录的主键值然后再查找主键索引对应的B树找到目标记录这个过程就叫做回表查询InnoDB存储引擎的普通索引的B树叶子结点中没有保存整条数据记录是为了节省空间因为同一张表可能会创建多个普通索引每个普通索引的B树中都保存一份数据会造成数据冗余所以通过回表查询主键索引对应的B来获取整个数据记录
以上表中的 Col3 建立对应的辅助索引如下图 注意采用InnoDB存储引擎建立的每张表都会有一个主键就算用户没有设置InnoDB也会自动帮你创建一个不可见的主键
六、索引操作
6.1 创建主键索引 创建主键索引方式一 在创建表的时候直接在字段名后指定primary key
mysql create table if not exists user1(- id int primary key,- name varchar(20)- );创建主键索引方式二 在创建表的最后指定某列或某几列为主键索引
mysql create table if not exists user2(- id int,- name varchar(20),- primary key(id)- );创建主键索引方式三 创建表以后再添加主键
alter table user3 add primary key(id);主键索引的特点 一个表中最多有一个主键索引当然可以使符合主键主键索引的效率高主键不可重复创建主键索引的列它的值不能为null且不能重复主键索引的列基本是整型类型
6.2 唯一索引的创建 唯一索引的创建方式一 在表定义时在某列后直接指定unique唯一属性
mysql create table user4(- id int primary key,- name varchar(20) unique- );唯一索引的创建方式二 创建表时在表的后面指定某列或某几列为unique
mysql create table user5(- id int primary key,- name varchar(20),- unique(name)- );唯一索引的创建方式三 创建表以后再添加唯一键
alter table user6 add unique(name);唯一索引的特点 一个表中可以有多个唯一索引查询效率高如果在某一列建立唯一索引必须保证这列不能有重复数据如果一个唯一索引上指定not null等价于主键索引
6.3 普通索引的创建 普通索引的创建方式一 在表的定义最后指定某列为索引
create table user7(id int primary key,
name varchar(20),
email varchar(30),
index(name)
);普通索引的创建方式二 创建完表以后指定某列为普通索引
alter table user8 add index(name);普通索引的创建方式三 创建表后使用create命令给指定字段创建普通索引并指定索引名
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user9(name);普通索引的特点 一个表中可以有多个普通索引普通索引在实际开发中用的比较多如果某列需要创建索引但是该列有重复的值那么我们就应该使用普通索引
6.4 查询索引 查询索引方式一 语法 show keys from 表名;例如查询user1表 部分说明
Table 表示创建索引的表的名称Non_unique 表示该索引是否是唯一索引如果是则为0如果不是则为1Key_name 表示索引的名称索引也有名字Column_name 表示定义索引的列字段Index_type 显示索引使用的类型和方法BTREE、FULLTEXT、HASH、RTREE 第二种方法 语法
show index from 表名;比如查询user4的索引有两行说明有两个索引 第三种方法 语法查出的信息比较简略 desc 表名;比如查询user7表的索引
6.5 删除索引 删除主键索引 语法
alter table 表名 drop primary key;例如删除user1的主键索引 删除非主键索引 语法
alter table 表名 drop index 索引名;注索引名就是show keys from 表名 中的Key_name字段
例如删除user4表的唯一键索引 第三种方法 该语法可以删除指定的非主键索引
drop index 索引名 on 表名;6.6 全文索引的创建
当对文章字段或有大量文字的字段进行检索时会使用到全文索引MySQL提供全文索引机制但是有要求要求表的存储引擎必须是MyISAM而且默认的全文索引支持英文不支持中文如果对中文进行全文检索可以使用sphinx的中文版(coreseek) 全文索引案例 创建一个文章表表当中包含文章的id、文章名称、文章内容并在创建表的最后通过fulltext给title和body列创建全文索引
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关键字我们可以通过模糊匹配进行查找没有使用到全文索引
select * from articles where body like %database%;可以用explain工具看一下是否使用到索引
在SQL语句前面加上explain可以看到key对应的值为NULL表示这条SQL在执行过程中没有用到任何索引 如果要通过全文索引来查询需要使用match against进行搜
SELECT * FROM articles WHERE MATCH (title,body) AGAINST (database);在这条SQL语句前面加上explain可以看到key对应的值为title表示这条SQL在执行过程中用到了索引名为title的索引 注MyISAM存储引擎是支持全文索引的而InnoDB存储引擎是在5.6以后才开始支持全文索引的
6.7 索引创建原则
索引创建的原则如下
比较频繁作为查询条件的字段应该创建索引唯一性太差的字段不适合单独创建索引即使频繁作为查询条件更新非常频繁的字段不适合创建索引不会出现在where子句中的字段不应该创建索引
--------------------- END ----------------------
「 作者 」 枫叶先生
「 更新 」 2023.9.1
「 声明 」 余之才疏学浅故所撰文疏漏难免或有谬误或不准确之处敬请读者批评指正。
