MySQL index

By Mysql

Summary:

This is an article about MySQL, mainly introducing MySQL index

# #1. Introduction to Indexes

An index is also called a key in MySQL. It is a data structure used by the storage engine to quickly find records.

Index optimization should optimize query performance. Effective means

It is equivalent to the sequence table in the dictionary. If there is no sequence table, you need to check it page by page

##2. Index classification

|Description|Common Index|Unique Index|Full-Text Index|Single Column Index|Multi-Column Index|Spatial Index| |–|–|–|–|–|–|–| |Storage Engine|All Storage Engine Support( The same index, different storage types, different implementations)|Only supported by Memory|Only supported by MyISAM before 5.5 and before||||| | |Multi-column index will only be used when there is the first field in the select statement condition| | |

##3. Test

###Using stored procedures to insert data

 1  2  3  4 < span class="lnt"> 5  6  7  8  9 10 11 12 < /span>
 
mysql> delimiter $ $ --Set the separator to $$ -- Define a stored proceduremysql> create procedure autoinsert1() -> BEGIN -> declare i int default 1;< /span> -> while(i<20000)do -> insert into school.t2 values(i, 'xff'); ->< /span> set i= i+1 -> end while; -> END$$mysql> delimiter ; --Restore the delimiter as; mysql> call autoinsert1(); --Call a stored procedure

To create a stored procedure, select the corresponding database, otherwise an error may be reported.

###Create index

####1. Creating Create index when table

1 2 3 4 5 6 7 
create table table_name(Field name1 < span class="err">Data type [Integrity constraints ],field name2 data type [Integrity constraints],[< /span>UNIQUE|FULLTEXT |SPATIAL ] INDEX|KEY span>[index name]  (Field name[(Length)] [ASC|< span class="k">DESC]) --ASC|DESC means to sort the index--The length after the field name is for varchar type);

####2. Create a multi-column index

Select to use the first The index is only used for one field

 1  2  3  4  5  6  7  8  9 10  11 12 13 
--Create table--The first field here is dept_name  mysql> show create table dept4 span>G***************************  1. row *************************** Table: dept4Create Table: CREATE TABLE `< span class ="n">dept4` ( `< span class="n">dept_id` int(11) NOT NULL< /span>, `dept_name ` varchar(30) DEFAULT NULL, `comment` varchar(50) DEFAULT NULL, PRIMARY KEY span> (`dept_id span>`), KEY  `index_dept_name` (`dept_name`,`comment`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 span>
 

 1  2  3  4  5  6  7  8  9 10 11 12 13 14 < span class="lnt">15 16 17 18 19 20 21 22 < /span>23 24 25  26 27 28 29

td> 

-- index is used-- explain how to explain this sentence Executed, but not actually executedmysql> < span class="k">explain select * from dept4 where dept_name='sale' span>G*************** ************ 1.  row *************************** id: 1 select_type: SIMPLE table: dept4 type: ref possible_keys: index_dept_name --this The sentence indicates the name of the index used key: index_dept_name key_len : 33 ref: const rows: 1 -- How many rows were queried in total Extra:< /span> Using where;  Using index1 row in set (0.01 sec)mysql> explain select * from dept4 where span> comment='sale001'G span>*************************** 1 . row ************ *************** id: 1 select_type: SIMPLE table: dept4 type< span class="p">: indexpossible_keys: NULL -- This sentence means that no index is used key: index_dept_name key_len: 86 ref: NULL rows : 4 -- How many rows were queried in total Extra: Using where; Using index1 row in set (0.00 span> sec)

####3. Create an index on an existing table

1 2 < span class="lnt">3 4 
create [UNIQUE|FULLTEXT|SPATIAL] INDEX Index name ON Table name( Field name[(length)] [ASC|DESC]);alter table span> table name ADD [ UNIQUE|FULLTEXT|SPATIAL] INDEX Index name(Field name[( Length)] [ASC< /span>|DESC]);

td>

Copy table

1 2 < /span>3 4 5  6 
-- copied the content and structure of the table, but did not copy Table key (constraint) mysql> create table t3 select * from t2;-- The following two copy the structure of the table, but there is no content and no Copy table key (constraint) mysql> < span class="k">create table t4 select * from t2 where< /span> 1=2 ;mysql> create table t5 like t2;

##4. Manage index

View index show create table table name G

Test exampleexplain select * from t2 where id = 1; View query optimizer Which operations

DROP INDEX index name ON table name delete index

##5. Extension

Sphinx is a full-text search engine, But Chinese is not supported. Coreseek is a Chinese full-text search engine that can be used by enterprises and is based on Sphinx (which can run independently of the original version of Sphinx)

Original: Big Column MySQL Index

< div class="chroma"> 

 1   2  3  4  5  4  5  span> 6  7  8  9 10 11 12 
mysql> delimiter $$ --Set the delimiter to $$ -- Define a stored proceduremysql> create procedure autoinsert1() < span class="o">-> BEGIN -> declare i< /span> int default 1 ; -> while(i<20000)do -> insert into school.t2 values span>(i, ' xff'); -> set i=i+< span class="mi">1 -> end while; -> END< span class="err">$$mysql> delimiter span> ; --Restore the separator to; mysql> call autoinsert1(); --Call a stored procedure
 

 1  2  3  4  5  6  7  8  9 10 11 12 
mysql> delimiter $$ --Set the separator to $$ -- Define a stored proceduremysql> create procedure< /span> autoinsert1() -> BEGIN -> declare i int default 1; < span class="o">-> while(i<20000)do  -> insert into school.t2 values(i,< /span> 'xff'); -> set i=i+1 -> end while; -> END $$mysql> delimiter ; --Restore the delimiter to; mysql> call autoinsert1< /span>(); --Call a stored procedure
 

1 2 3 4 5 6 7  
create table table_name(field name1< /span> Data type [Integrity constraints],field name2 data type [Integrity constraints],[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY[ Index name] (Field name [(Length)] [ASC|DESC]) --ASC|DESC means to sort the index--The length after the field name is for varchar Type);
 

 1 2 3 4 5 6 7
< pre class="chroma">create table table_name(Field name1 Data type [Integrity constraints ],field name2 data type [Integrity constraints],[< /span>UNIQUE|FULLTEXT |SPATIAL] INDEX|KEY[index name] (Field name[(Length)] [ASC |DESC]) –ASC|DESC means to sort the index–The length after the field name is for the varchar type );
< tbody> 

 1  2  3  4  5  6 < span class="lnt"> 7  8  9 10 11 12 13 
--Create table--The first field here is dept_name mysql> show span> create table dept4< /span>G************************** * 1. row *************************** Table: dept4Create span> Table: CREATE TABLE  `dept4` ( `dept_id` int(11) NOT NULL, `< span class="n">dept_name` varchar(30) DEFAULT NULL< /span>, `comment` varchar(50 ) DEFAULT NULL, span> PRIMARY KEY (` dept_id`), KEY `index_dept_name` (`dept_name`,`comment` )) ENGINE= span>InnoDB DEFAULT CHARSET= latin1
 

 1  2  3  4  5  6  7  8  9 10 11 12 13 
--Create table --The first field here is dept_name mysql> show create table dept4G****** ********************* 1. row ***************************< /s pan> Table: dept4Create  Table: CREATE TABLE `dept4` ( `dept_id` int(11) < span class="k">NOT NULL, `dept_name` varchar(< /span>30) DEFAULT  NULL, `comment` varchar(50) DEFAULT NULL ,  PRIMARY KEY (`dept_id`),  KEY `index_dept_name` (`dept_name `,`comment`)) ENGINE =InnoDB DEFAULT CHARSET=latin1
 
 1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 
-- 使用了索引 -- explain 解释这句如何执行，但是并不实际执行 mysql> explain select * from dept4 where dept_name='sale'G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: dept4         type: refpossible_keys: index_dept_name   --这句表示使用了的索引的名字           key: index_dept_name      key_len: 33          ref: const         rows: 1                -- 一共查询了多少行         Extra: Using where; Using index1 row in set (0.01 sec)mysql> explain select * from dept4 where comment='sale001'G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: dept4         type: indexpossible_keys: NULL           -- 这句表示没有使用索引           key: index_dept_name      key_len: 86          ref: NULL         rows: 4              -- 一共查询了多少行         Extra: Using where; Using index1 row in set (0.00 sec)
 

 1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 
-- 使用了索引 -- explain 解释这句如何执行，但是并不实际执行 mysql> explain select * from dept4 where dept_name='sale'G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: dept4         type: refpossible_keys: index_dept_n ame  --这句表示使用了的索引的名字           key: index_dept_name      key_len: 33          ref: const         rows: 1                -- 一共查询了多少行         Extra: Using where; Using index1 row in set (0.01 sec)mysql> explain select * from dept4 where comment='sale001'G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: dept4         type: indexpossible_keys: NULL           -- 这句表示没有使用索引 < span class="c1">          key: index_dept_name      key_len: 86          ref: NULL         rows: 4              -- 一共查询了多少行         Extra: Using where; Using index1 row in set (0.00 sec)
 
1 2 3 4 
create [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名    ON 表名(字段名[(长度)] [ASC|DESC]);alter table 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名(字段名[(长度)] [ASC|DESC]);
 

1 2 3 4 
create [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名    ON 表名(字段名[(长度)] [ASC|DESC]);alter table 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名(字段名[(长度)] [ASC|DESC]);
 
1 2 3 4 5 6 
-- 复制了表的内容和结构，但是没有复制表的key(约束) mysql> create table t3 select * from t2;-- 下面两条，复制了表的结构，但是没有内容，也没有复制表的key(约束) mysql> create table t4 select * from t2 where 1=2;mysql> create table t5 like t2;
 

1 2 3 4 5 6 
-- 复制了表的内容和结构，但是没有复制表的key(约束) mysql> create table t3 select * from t2;-- 下面两条，复制了表的结构，但是没有内容，也没有复制表的key(约束) mysql> create table t4 select * from t2 where 1=2;mysql> create table t5 like t2;

,

Leave a Comment

Your email address will not be published.