Mysql索引步骤及优化
79 浏览量
2020-12-14
23:37:56
上传
评论
收藏 105KB PDF 举报
Mysql索引步骤及优化索引步骤及优化
MySQL索引的概念
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引不一样了；聚簇索引
能提高多行检索的速度，而非聚簇索引对于单行的检索很快
要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和
插入的表格，没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建
立另外的索引。
1. 普通索引 普通索引(由关键字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名"的方式
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)
);
3.主索引 在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引是所谓的"主索引"。主索引与索引的
区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。
4.外键索引 如果为某个外键字段定义了一个外键约束条件，MySQL会定义一个内部索引来帮助自己以有效率的方式
去管理和使用外键约束条件。
5. 全文索引（FULLTEXT） MySQL从3.23.23版开始支持全文索引和全文检索，fulltext索引仅可用于 MyISAM 表；他们
可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE
INDEX被添加。////对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输
入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的
做法。
文本字段上的普通索引只能加快对出现在字段内容前面的字符串(也是字段内容开头的字符)进行检索操作。如果字段里存
放的是由几个、甚至是多个单词构成的较大段文字，普通索引没什么作用了。这种检索往往以LIKE %word%的形式出现，这
对MySQL来说很复杂，如果需要处理的数据量很大，响应时间会很长。
这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有
单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有
必要时再使用下面这条命令添加：
ALTER TABLE table_name ADD FULLTEXT(column1， column2)
有了全文索引，可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基
本语法：
SELECT * FROM table_name
WHERE MATCH(column1， column2) AGAINST('word1'， 'word2'， 'word3')
评论0
最新资源