【MySQL】14.MySQL索引特性新（3）

文章目录6. 索引操作6.1 创建主键索引6.2 唯一索引的创建6.3 普通索引的创建6.4 全文索引的创建6.5 查询索引6.6 删除索引6.7 索引创建原则6.8 扩展---自行了解6. 索引操作6.1 创建主键索引第一种方式-- 在创建表的时候直接在字段名后指定 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 student_course ( student_id INT, course_id INT, score DOUBLE, -- 指定 student_id 和 course_id 这两列组合成主键 PRIMARY KEY (student_id, course_id) );第三种方式create table user3( id int, name varchar(30) ); -- 创建表以后再添加主键 alter table user3 add primary key(id);主键索引的特点一个表中最多有一个主键索引当然可以使符合主键主键索引的效率高主键不可重复创建主键索引的列它的值不能为null且不能重复主键索引的列基本上是int6.2 唯一索引的创建第一种方式-- 在表定义时在某列后直接指定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 seat_booking ( id INT PRIMARY KEY, user_name VARCHAR(30), seat_no VARCHAR(20), -- 指定 user_name 和 seat_no 这两列组合起来必须唯一 UNIQUE (user_name, seat_no) );第三种方式create table user6( id int primary key, name varchar(30) ); alter table user6 add unique(name);唯一索引的特点一个表中可以有多个唯一索引查询效率高如果在某一列建立唯一索引必须保证这列不能有重复数据如果一个唯一索引上指定not null等价于主键索引但不是主键。6.3 普通索引的创建第一种方式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) ); -- 创建一个索引名为 idx_name 的索引 create index idx_name on user10(name);普通索引的特点一个表中可以有多个普通索引普通索引在实际开发中用的比较多如果某列需要创建索引但是该列有重复的值那么我们就应该使用普通索引6.4 全文索引的创建当对文章字段或有大量文字的字段进行检索时会使用到全文索引。MySQL提供全文索引机制但是有要求要求表的存储引擎必须是MyISAM而且默认的全文索引支持英文不支持中文。如果对中文进行全文检索可以使用sphinx的中文版(coreseek)。CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) # 创建全文索引将 title 和 body 两列组合在一起建立一个全文索引 )engineMyISAM;向刚才创建的articles表中一次性插入 6 篇文章的标题和正文内容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: 1 select_type: SIMPLE table: articles type: ALL possible_keys: NULL key: NULL key为null表示没有用到索引 key_len: NULL ref: NULL rows: 6 Extra: Using where 1 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 ... | -----------------------------------------------------------------通过explain来分析这个sql语句mysql explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST (database)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: articles type: fulltext possible_keys: title key: title key用到了title key_len: 0 ref: rows: 1 Extra: Using where使用全文索引需要使用专门的语法MATCH() AGAINST()SELECT * FROM 表名 WHERE MATCH(列名1, 列名2) AGAINST(要找的词汇);6.5 查询索引第一种方法show keys from表名mysql show keys from goods\G *********** 1. row *********** Table: goods 表名 Non_unique: 0 0表示唯一索引 Key_name: PRIMARY 主键索引 Seq_in_index: 1 Column_name: goods_id 索引在哪列 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE 以二叉树形式的索引 Comment: 1 row in set (0.00 sec)第二种方法:show index from表名。第三种方法信息比较简略desc表名。6.6 删除索引第一种方法-删除主键索引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;6.7 索引创建原则比较频繁作为查询条件的字段应该创建索引唯一性太差的字段不适合单独创建索引即使频繁作为查询条件更新非常频繁的字段不适合作创建索引不会出现在where子句中的字段不该创建索引6.8 扩展—自行了解复合索引索引最左匹配原则索引覆盖