MySQL索引添加主键：
mysql> alter table studeng add primary key (id);
mysql> alter table student add primary key (id);
删除主键：
mysql> alter table student drop primary key;
mysql> alter table student drop key index_name;
查看表中的索引：
mysql> show index from book\G;
mysql> show create table book;
索引是一个单独，存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针
索引是在存储引擎中实现的
索引分类；普通索引，唯一性索引，全文索引，单列索引，多列索引和空间索引
索引有两种存储类型：B型树（BTREE）索引和哈希(HASH)索引,myisam和innodb存储引擎只支持BTREE索引,memory/heap存储引擎可以支持HAST和BTREE索引
主键索引和唯一索引：
主键是一种特殊的唯一性索引，不允许有空值
唯一索引列的值必须唯一，但允许有空值
单列索引和组合索引
单列索引：索引只包含单个列，一个表可以有多个单列索引
组合索引：指在表的多个字段组合上创建的索引
全文索引：
FULLTEXT;在定义索引的列上支持值的全文查找,char,varchar,text可以创建全文索引,只有myisam支持全文索引
空间索引：
是对空间数据类型的字段建立的索引,有4种空间数据类型:GEOMETRY,POINT,LINESTRING,POLYGON
-->oldboy:
表的操作：
mysql>create database oldboy;
QueryOK, 1 row affected (0.05 sec)
mysql>show create database oldboy\G
***************************1. row ***************************
Database: oldboy
CreateDatabase: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */
1 rowin set (0.00 sec)
查看表结构：
mysql>desc student;
mysql>show columns from student;
mysql>show create table student \G
***************************1. row ***************************
Table: student
CreateTable: CREATE TABLE `student` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8
1 rowin set (0.00 sec)
索引
创建主键索引：
AUTO_INCREMENT 自增
primarykey(id) 主键索引
keyindex_name(name)name字段普通索引
优化：在唯一值多的列上建立索引查询效率高
mysql>create table student( id int(4) not null auto_increment, name char(20) notnull, age tinyint(2) not null default '0', dept varchar(16) default null, primarykey(id), key index_name(name));
QueryOK, 0 rows affected (0.09 sec)
mysql>desc student;
+-------+-------------+------+-----+---------+----------------+
|Field | Type | Null | Key |Default | Extra |
+-------+-------------+------+-----+---------+----------------+
|id | int(4) | NO | PRI | NULL | auto_increment |
|name | char(20) | NO | MUL | NULL | |
|age | tinyint(2) | NO | | 0 | |
|dept | varchar(16) | YES | |NULL | |
+-------+-------------+------+-----+---------+----------------+
4rows in set (0.00 sec)
PRI: 主键索引
MUL: 普通索引
altertable student drop primary key
altertable student change id id int primary key auto_increment;
创建普通索引：
keyindex_name(name)
mysql>alter table student add index index_name(name);
QueryOK, 0 rows affected (0.22 sec)
Records:0 Duplicates: 0 Warnings: 0
mysql>alter table student drop index index_name;
QueryOK, 0 rows affected (0.04 sec)
Records:0 Duplicates: 0 Warnings: 0
mysql>dropindex index_name on student;
对字段的前n个字符创建普通索引：
mysql>create index index_dept on student(dept(8));
QueryOK, 0 rows affected (0.05 sec)
Records:0 Duplicates: 0 Warnings: 0
mysql>show index from student\G
创建唯一索引(非主键)
mysql>create unique index uni_ind_name on student(name);
QueryOK, 0 rows affected (0.38 sec)
Records:0 Duplicates: 0 Warnings: 0
mysql>desc student;
+-------+-------------+------+-----+---------+----------------+
|Field | Type | Null | Key |Default | Extra |
+-------+-------------+------+-----+---------+----------------+
|id | int(4) | NO | PRI | NULL | auto_increment |
|name | char(20) | NO | UNI | NULL | |
|age | tinyint(2) | NO | | 0 | |
|dept | varchar(16) | YES | |NULL | |
+-------+-------------+------+-----+---------+----------------+
4rows in set (0.00 sec)
联合索引：(为表的多个字段创建联合索引)
联合索引生效条件：
index(a,b,c)仅a,ab,abc一个查询条件表可以走索引,b,bc,ac,c等就无法使用索引，这是联合索引的前缀特性
mysql>create index ind_name_dept on student(name,age);
mysql>show index student\G;
对联合索引的前N个字符创建索引：
mysql>create index ind_name_dept on student(name(8),dept(10));
QueryOK, 0 rows affected (0.07 sec)
Records:0 Duplicates: 0 Warnings: 0
索引列的创建及生效条件：
索引不但占用系统空间，更新数据库时还需要维护索引数据
更新频繁，读取比较少的表要少创建索引
selectuser,host,from mysql.user where host= 索引一定要创建在条件列,而不是select后的选择数据的列,
要选择在唯一值多的大表上列建立索引
创建主键索引：
altertable student change id id int primary key auto_increment
删除主键索引：
altertable student drop primary key
创建普通索引：
altertable student add index index_dept(dept(8))
根据列的前n个字符创建索引:
createindex index_dept on student(dept(8))
根据多个列创建联合索引：
createindex ind_name_dept on student(name,dept)
根据列的前n个字符创建联合索引:
createindex ind_name_dept on student(name(8),dept(10))
删除普通索引：
altertable student drop index index_dept;
dropindex index_name on student
创建唯一索引:
createunique index index_name on student(name)
创建索引
有三种方法来创建索引；创建表时创建索引，使用create index在已存在的表上创建索引，使用alter table语句来创建索引
1.创建表时创建索引
create table 表名{
[unique|fulltext|spatial] index|key [别名] （属性1[（长度）] |asc|desc）
};
create table table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length])[ASC|DESC]
唯一索引，全文索引，空间索引
创建普通索引：
mysql> create table book (bookid int not null, bookname varchar(255)not null, authors varchar(255) not null, info varchar(255) null, commentvarchar(255) null, year_publication year not null, index(year_publication));
| book | CREATE TABLE `book` (
`bookid` int(11) NOT NULL,
`bookname` varchar(255) NOTNULL,
`authors` varchar(255) NOT NULL,
`info` varchar(255) DEFAULTNULL,
`comment` varchar(255) DEFAULTNULL,
`year_publication` year(4) NOTNULL,
KEY `year_publication`(`year_publication`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
eg:
mysql> create table index1( id int, name varchar(20), sex boolean, index (id) );
查看表结构:
mysql> show create table index1 \G
mysql> show index from t1\G;
explain查看索引是否被使用,只有插入数据时，才会显示出来
mysql> explain select * from index1 where id=1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: index1
type: system
possible_keys: id
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
1 row in set (0.01 sec)
创建唯一性索引：索引列的值必须唯一，但允许有空值,如果是组合索引，则列值的组合必须唯一
mysql> create table index2 (
-> id int unique,
-> name varchar(20),
-> unique indexindex2_id(id asc));
mysql>create table t1(
-> id int not null,
-> name char(30) not null,
-> unique indexrsc(id));
创建全文索引
全文索引只能创建在char,varchar或text字段上,只有myisam存储引擎支持全文索引
mysql>create table index3(
-> id int,
-> info varchar(20),
-> fulltextindex index3_info(info))
-> engine=myisam;
创建单列索引:在表的单个字段上创建索引
mysql>create table index4( id int ,
-> subject varchar(30),
-> indexindex4_st (subject(10)));
创建多列(组合)索引:是在表的多个字段上创建一个索引,只有第一个字段被查询时，索引才会起作用,服从"最左前缀"
mysql>create table index5 (
-> id int,
-> name varchar(20),
-> sex char(4),
-> indexindex5_ns(name,sex));
创建空间索引,使用spatial参数来设置，表的存储引擎必须为myisam,索引字段必须为非空约束
mysql>create table index6 (
-> id int ,
-> space geometry not null,
-> spatial index index6_sp(space));
在已经存在的表的创建索引
create [unique|fulltext|spatial] index 索引名 on 表名 (属性名[(长度)] [asc|desc]);
创建普通索引
mysql>create index example0_id on example0(id);
创建唯一性索引
mysql>create unique index example_1 on example1(stu_id);
创建全文索引
mysql>create fulltext index example2_gradeon example2(grade);
创建单列索引
mysql>create index example3_stu on example3(stu_id);
创建多列索引
mysql>create index example3_stu on example3(stu_id,id);
用alter table语句来创建索引
altertable 表名 add[unique|fulltext|spatial] index 索引名 (属性名[(长度)] [asc|desc]);
altertable talble_name add index index_name(column_list)
altertable table_name add unique(column_list)
altertable table_name add primary key(column_list)
ALTERTABLE <table>
ADDPRIMARY KEY [index-name] (<column>);
ALTERTABLE <table>
ADD[UNIQUE] KEY|INDEX [index-name]
(<column>);
当创建非主码索引时，KEY 和 INDEX 关键字可以互换。但
创建主码索引时只能使用 KEY 关键字。
增加自增功能：
alter table t1 modify id int unsigned not nullauto_increment;
创建普通索引
mysql>alter table example4 add index example4_name(name);
创建唯一性索引
mysql>create unique index example_1 on example1(stu_id);
创建全文索引
mysql>alter table example5 add fulltext index example5_name(name);
创建单列索引
mysql>alter table example6 add index example6_name(name);
创建多列索引
mysql>alter table example6 add index example6_name(name,stu_id);
使用create index创建索引
create[unique|fulltext|spatial]index index_name on table_name(col_name[length],....)[ASC|DESC]
创建普通索引：
createindex bknameidx on book(bookname)
创建唯一索引：
createunique index uniquldx on book(bookid)
创建单列索引:
createindex bkcomldx on book(comment(50));
创建组合索引：
createindex bkauandinfoldx on book(authors(20),info(50))
创建全文索引：
createfulltext index on t6(info);
创建空间索引：
createspatial index spatldx on t7(g);
删除索引
使用DROP INDEX语句删除索引
drop index 索引名 on 表名；
DROPINDEX index_name ON table_name;
eg:
mysql>drop index id on index1;
使用ALTER TABLE删除索引
Alter table table_namedrop index index_name;
altertable table_name drop index index_name
altertable table_name drop primary key
