目录

索引引入

Principle of indexing mechanism

index command 

 创建索引

 删除索引

修改索引

查询索引

Create an index exercise 

​编辑

小结 

索引引入

It takes a lot of time to query massive amounts of data,To reduce response time,index is used. 

CREATE INDEX 索引名 on 表名（列名）-- Indicates that a column of this table creates an index

After creating the play index,The file size of the table becomes larger：索引本身会占用空间

After creating the index, use the same query statement to query,The processing speed will be significantly faster.

After the index is created, it only affects the query data of the indexed column,Others that are not created should be slow or slow

Principle of indexing mechanism

 For example, there is a student table,Perform a lookup when there is no indexid  = 6 的操作,会对全表进行扫描,逐条对比.

如果创建索引,A binary tree will be generated,如上,Will greatly reduce the number of queries,This is why index query Sudoku is fast 

index command 

-- 查询emp 表有没有索引 

SHOW INDEXES from emp;

 创建索引

创建索引,再次查询

-- 给emp 表的empno添加一个唯一索引
CREATE UNIQUE INDEX empno_index on emp(empno)

There are two ways to add a normal index 

-- Add normal index mode1
CREATE INDEX empno_index on emp(empno)、
-- 添加普通索引方式2
ALTER TABLE emp ADD INDEX empno_index(empno)

添加主键索引 

The first is to add a primary key to a column before,That is to add the primary key index

The second is similar to adding a normal index,只是把index换成 primary key

ALTER TABLE emp ADD PRIMARY KEY empno_index(empno)

 删除索引

-- 删除索引
-- 删除普通索引
DROP INDEX empno_index on emp
-- 删除主键索引
ALTER TABLE emp DROP PRIMARY KEY

修改索引

To modify an index is to delete the index first,Add a new index

查询索引

There are three ways to query the index

-- 查询索引
-- 方法1
SHOW INDEX FROM emp
-- 方法2
SHOW INDEXES FROM emp
-- 方法3 
SHOW KEYS FROM emp

Create an index exercise 

-- 1.创建一张订单表order (id号,数量).要求idnumber is the primary key,请使
-- 用2way to create a primary key.(提示:For practice convenience,可以是order1 , order2 )

-- 方法1 创建表时添加主键
CREATE TABLE order1 (
			id INT PRIMARY KEY,
			nums INT)
-- 方法2 通过alter add添加
CREATE TABLE order2(
			id INT,
			nums INT)
ALTER TABLE order2 ADD PRIMARY KEY id_index(id)

-- Create a menu of specialsmenu (id号,recipe name,厨师,ID card of the orderer,价
-- 格).要求idnumber is the primary key,The ID of the orderer isuniquePlease use two ways to createunique.(提示:For practice convenience,可以是menu1 , menu2、
-- 方法1 通过unique在创建表时添加
CREATE TABLE menu1(
				id INT PRIMARY KEY,
				menu_name VARCHAR(20) NOT NULL DEFAULT '',
				cart_id INT UNIQUE,
				price INT)
-- 方法2 
CREATE TABLE menu2(
				id INT PRIMARY KEY,
				menu_name VARCHAR(20) NOT NULL DEFAULT '',
				cart_id INT,
				price INT)
CREATE UNIQUE INDEX cart_index on menu2(cart_id)

小结