MySQL-Optimierungsindex-spezifische Codeanalyse
- 黄舟Original
- 2017-03-10 10:27:201266Durchsuche
MySQL-Optimierung – Indexspezifische Codeanalyse:
Indizes werden in Speicher-Engines implementiert, sodass die Indizes jeder Speicher-Engine nicht unbedingt genau gleich sind, und nicht bei allen Die Speicher-Engine unterstützt alle Indextypen.
Definiert die maximale Anzahl von Indizes und die maximale Indexlänge für jede Tabelle gemäß der Speicher-Engine. Alle Speicher-Engines unterstützen mindestens 16 Indizes pro Tabelle mit einer Gesamtindexlänge von mindestens 256 Byte.
Die meisten Speicher-Engines haben höhere Grenzwerte. Es gibt zwei Speichertypen von Indizes in MYSQL: BTREE und HASH, die sich speziell auf die Speicher-Engine der Tabelle beziehen.
MYISAM- und InnoDB-Speicher-Engines unterstützen nur BTREE-Indizes und HEAP-Speicher-Engines können HASH unterstützen und BTREE-Indizes
Vorteile des Index:
1. Stellen Sie die Eindeutigkeit jeder Datenzeile in der Datenbanktabelle sicher, indem Sie einen eindeutigen Index erstellen
2 Abfrage
3. Bei Verwendung von Gruppierung und Sortierung für die Datenabfrage kann die Zeit für die Gruppierung und Sortierung in der Abfrage erheblich verkürzt werden
Nachteile des Index:
1 Der Index verbraucht Datenbankressourcen
2. Indizes benötigen Speicherplatz und Indexdateien erreichen möglicherweise schneller die maximale Dateigröße als Datendateien
3 Beim Hinzufügen, Löschen oder Ändern von Tabellendaten Die Geschwindigkeit wird durch die Notwendigkeit beeinträchtigt, den Index
zu verwalten. Gewöhnlicher Index und eindeutiger Index
Der Primärschlüsselindex ist ein spezieller eindeutiger Index, es sind keine Nullwerte zulässig
Einspaltiger Index und zusammengesetzter Index
Einspaltiger Index enthält nur eine einzelne SpalteZusammengesetzter Index bezieht sich auf einen Index, der für mehrere Felder erstellt wurde. Der Index wird nur verwendet, wenn das erste Feld beim Erstellen des Index in den Abfragebedingungen verwendet wird . Wenn Sie einen zusammengesetzten Index verwenden, befolgen Sie den Präfixsatz ganz links
3. Volltextindex
Der Volltextindextyp ist FULLTEXT. Dies wird für die Spalte unterstützt, die den Index definiert. Die Volltextsuche von Werten ermöglicht das Einfügen von Duplikat- und Nullwerten in diese indizierten Spalten. Volltextindizes können für Spalten vom Typ CHAR, VARCHAR und TEXT erstellt werden. MYSQL unterstützt nur den Volltextindex der MYISAM-Speicher-Engine
4. Der räumliche Index ist ein Index, der für Felder räumlicher Daten erstellt wurde Typ. MYSQL Es gibt 4 räumliche Datentypen in
: GEOMETRY, POINT, LINESTRING und POLYGON. MYSQL wird um das Schlüsselwort SPATIAL erweitert und ermöglicht die Erstellung räumlicher Indizes mithilfe der Syntax, die zum Erstellen regulärer Indextypen verwendet wird. Spalten, die zum Erstellen räumlicher Indizes verwendet werden, müssen als NOT NULL deklariert werden. Räumliche Indizes können nur in Tabellen erstellt werden, deren Speicher-Engine ist SQLSERVER
unterstützt
unique|fulltext|spatial als optionale Parameter, die jeweils einen eindeutigen Index, einen Volltextindex und einen räumlichen Index darstellen Synonyme, beide haben die gleiche Funktion und werden verwendet, um den zu erstellenden Index anzugeben. col_name ist die Feldspalte, für die der Index erstellt werden muss. Diese Spalte muss aus mehreren in definierten Spalten ausgewählt werden Datentabelle; index_name gibt den Namen des Index an. Wenn er nicht angegeben ist, verwendet MYSQL standardmäßig col_name als Indexwert.length ist ein optionaler Parameter der Index. Nur Felder vom Typ „String“ können die Indexlänge angeben; >
Verwenden Sie SHOW CREATE TABLE, um die Tabellenstruktur zu betrachtenCREATE TABLE table_name[col_name data type] [unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]und stellen Sie fest, dass das Feld „year_publication“ der Buchtabelle erfolgreich indiziert wurde und sein Index Name ist Jahr_Veröffentlichung Wir fügen ein Datenelement in die Tabelle ein und überprüfen dann mit der EXPLAIN-Anweisung, ob der Index verwendet wird.
Da die Anweisung relativ einfach ist, ist die Das System bestimmt, dass die Indizierung oder das Volltextscannen verwendet werden kann
EXPLAIN Die Erklärung jeder Zeile des Anweisungsausgabeergebnisses lautet wie folgt: select_type: stellt den Typ jeder Select-Klausel in der Abfrage dar (einfach oder komplex)
CREATE TABLE book ( bookid INT NOT NULL, bookname VARCHAR (255) NOT NULL, AUTHORS VARCHAR (255) NOT NULL, info VARCHAR (255) NULL, COMMENT VARCHAR (255) NULL, year_publication YEAR NOT NULL, INDEX (year_publication) ) ;
type:
stellt die Art und Weise dar, in der MySQL die erforderlichen Zeilen findet Die Tabelle wird auch als „Zugriffstyp“ bezeichnet. Übliche Typen sind wie folgt: (von oben nach unten wird der Effekt in der Reihenfolge besser)CREATE TABLE `book` ( `bookid` INT(11) NOT NULL, `bookname` VARCHAR(255) NOT NULL, `authors` VARCHAR(255) NOT NULL, `info` VARCHAR(255) DEFAULT NULL, `comment` VARCHAR(255) DEFAULT NULL, `year_publication` YEAR(4) NOT NULL, KEY `year_publication` (`year_publication`) ) ENGINE=MYISAM DEFAULT CHARSET=latin1possible_keys
: Gibt an, welchen Index MySQL verwenden kann um Zeilen in der Tabelle zu finden. Wenn es einen Index für das an der Abfrage beteiligte Feld gibt, wird der Index aufgelistet, aber er darf von der Abfrage nicht verwendet werden Der von MySQL tatsächlich in der Abfrage verwendete Index wird als NULL angezeigt
NSERT INTO BOOK VALUES(12,'NIHAO','NIHAO','文学','henhao',1990) EXPLAIN SELECT * FROM book WHERE year_publication=1990
key_len
: Gibt die Anzahl der im Index verwendeten Bytes an, die durch berechnet werden können Diese Spalte. Die Länge des Index 
ref
: Gibt die Verbindungsübereinstimmungsbedingungen der obigen Tabelle an, dh welche Spalten oder Konstanten verwendet werden, um den Wert in der Indexspalte
Zeilen
: Gibt an, dass MySQL die Anzahl der Zeilen schätzt, die gelesen werden müssen, um die erforderlichen Datensätze basierend auf Tabellenstatistiken und Indexauswahl zu finden 🎜>: Enthält keine für andere Spalten geeignete Anzeige, aber sehr wichtige Zusatzinformationen wie Verwendung wo, Verwendung Index唯一索引
唯一索引列的值必须唯一，但允许有空值。如果是复合索引则列值的组合必须唯一
建表
CREATE TABLE t1 ( id INT NOT NULL, NAME CHAR(30) NOT NULL, UNIQUE INDEX UniqIdx(id) ）
SHOW CREATE TABLE t1 查看表结构
SHOW CREATE TABLE t1
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8可以看到id字段上已经成功建立了一个名为UniqIdx的唯一索引
创建复合索引
CREATE TABLE t3 ( id INT NOT NULL, NAME CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR (255), INDEX MultiIdx (id, NAME, age (100)) )
SHOW CREATE TABLE t3
CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`NAME` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
KEY `MultiIdx` (`id`,`NAME`,`age`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8由结果可以看到id,name,age字段上已经成功建立了一个名为MultiIdx的复合索引
我们向表插入两条数据
INSERT INTO t3(id ,NAME,age,info) VALUES(1,'小明',12,'nihao'),(2,'小芳',16,'nihao')
使用EXPLAIN语句查看索引使用情况
EXPLAIN SELECT * FROM t3 WHERE id=1 AND NAME='小芳'
可以看到 possible_keys和 key 为MultiIdx证明使用了复合索引
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- -------- ------- ----------- ------ -----------
1 SIMPLE t3 ref MultiIdx MultiIdx 94 const,const 1 Using where如果我们只指定name而不指定id
EXPLAIN SELECT * FROM t3 WHERE NAME='小芳'
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ -----------
1 SIMPLE t3 ALL (NULL) (NULL) (NULL) (NULL) 2 Using where结果跟SQLSERVER一样，也是不走索引， possible_keys和key都为NULL
全文索引
FULLTEXT索引可以用于全文搜索。只有MYISAM存储引擎支持FULLTEXT索引，并且只支持CHAR、VARCHAR和TEXT类型
全文索引不支持过滤索引。
CREATE TABLE t4 ( id INT NOT NULL, NAME CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR (255), FULLTEXT INDEX FulltxtIdx (info) ) ENGINE = MYISAM
由于MYSQL5.6默认存储引擎为InnoDB，这里创建表的时候要修改表的存储引擎为MYISAM，不然创建索引会出错
SHOW CREATE TABLE t4
Table Create Table
------ ------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------
t4 CREATE TABLE `t4` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
FULLTEXT KEY `FulltxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8由结果可以看到，info字段上已经成功建立名为FulltxtIdx的FULLTEXT索引。
全文索引非常适合大型数据集合
空间索引
空间索引必须在 MYISAM类型的表中创建，而且空间类型的字段必须为非空
建表t5
CREATE TABLE t5 (g GEOMETRY NOT NULL ,SPATIAL INDEX spatIdx(g))ENGINE=MYISAM
SHOW CREATE TABLE t5
TABLE CREATE TABLE
------ ---------------------------------------------------------------------------------------------------------------
t5 CREATE TABLE `t5` (
`g` GEOMETRY NOT NULL,
SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8可以看到，t5表的g字段上创建了名称为spatIdx的空间索引。注意创建时指定空间类型字段值的非空约束
并且表的存储引擎为MYISAM
已经存在的表上创建索引
在已经存在的表中创建索引，可以使用ALTER TABLE或者CREATE INDEX语句
1、使用ALTER TABLE语句创建索引，语法如下
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY] [index_name](col_name[length],...)[ASC|DESC]
与创建表时创建索引的语法不同，在这里使用了ALTER TABLE和ADD关键字，ADD表示向表中添加索引
在t1表中的name字段上建立NameIdx普通索引
ALTER TABLE t1 ADD INDEX NameIdx(NAME)
添加索引之后，使用SHOW INDEX语句查看指定表中创建的索引
SHOW INDEX FROM t1 TABLE Non_unique Key_name Seq_in_index Column_name COLLATION Cardinality Sub_part Packed NULL Index_type COMMENT Index_comment ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- ------------- t1 0 UniqIdx 1 id A 0 (NULL) (NULL) BTREE t1 1 NameIdx 1 NAME A (NULL) (NULL) (NULL) BTREE
各个参数的含义
1、TABLE：要创建索引的表
2、Non_unique：索引非唯一，1代表是非唯一索引，0代表唯一索引
3、Key_name：索引的名称
4、Seq_in_index：该字段在索引中的位置，单列索引该值为1，复合索引为每个字段在索引定义中的顺序
5、Column_name：定义索引的列字段
6、Sub_part：索引的长度
7、NULL：该字段是否能为空值
8、Index_type：索引类型
可以看到，t1表已经存在了一个唯一索引
在t3表的age和info字段上创建复合索引
ALTER TABLE t3 ADD INDEX t3AgeAndInfo(age,info)
使用SHOW INDEX查看表中的索引
SHOW INDEX FROM t3
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ------ ---------- ------------ ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- ------------- t3 1 MultiIdx 1 id A (NULL) (NULL) (NULL) BTREE t3 1 MultiIdx 2 NAME A (NULL) (NULL) (NULL) BTREE t3 1 MultiIdx 3 age A (NULL) (NULL) (NULL) BTREE t3 1 t3AgeAndInfo 1 age A (NULL) (NULL) (NULL) BTREE t3 1 t3AgeAndInfo 2 info A (NULL) (NULL) (NULL) YES BTREE
可以看到表中的字段的顺序，第一个位置是age，第二个位置是info，info字段是可空字段
创建表t6，在t6表上创建全文索引
CREATE TABLE t6 ( id INT NOT NULL, info CHAR(255) )ENGINE= MYISAM;
注意修改ENGINE参数为MYISAM，MYSQL默认引擎InnoDB不支持全文索引
使用ALTER TABLE语句在info字段上创建全文索引
ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx(info)
使用SHOW INDEX查看索引情况
SHOW INDEX FROM t6
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ------ ---------- --------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- ------------- t6 1 infoFTIdx 1 info (NULL) (NULL) (NULL) (NULL) YES FULLTEXT
创建表t7，并在空间数据类型字段g上创建名称为spatIdx的空间索引
CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM;
使用ALTER TABLE在表t7的g字段建立空间索引
ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g)
使用SHOW INDEX查看索引情况
SHOW INDEX FROM t7
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- ------------- t7 1 spatIdx 1 g A (NULL) 32 (NULL) SPATIAL
2、使用CREATE INDEX语句创建索引，语法如下
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...) [ASC|DESC]
可以看到CREATE INDEX语句和ALTER INDEX语句的基本语法一样，只是关键字不同。
我们建立一个book表
CREATE TABLE book ( bookid INT NOT NULL, bookname VARCHAR (255) NOT NULL, AUTHORS VARCHAR (255) NOT NULL, info VARCHAR (255) NULL, COMMENT VARCHAR (255) NULL, year_publication YEAR NOT NULL )
建立普通索引
CREATE INDEX BkNameIdx ON book(bookname)
建立唯一索引
CREATE UNIQUE INDEX UniqidIdx ON book(bookId)
建立复合索引
CREATE INDEX BkAuAndInfoIdx ON book(AUTHORS(20),info(50))
建立全文索引，我们drop掉t6表，重新建立t6表
DROP TABLE IF EXISTS t6 CREATE TABLE t6 ( id INT NOT NULL, info CHAR(255) )ENGINE= MYISAM; CREATE FULLTEXT INDEX infoFTIdx ON t6(info);
建立空间索引，我们drop掉t7表，重新建立t7表
DROP TABLE IF EXISTS t7 CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM; CREATE SPATIAL INDEX spatIdx ON t7(g)
删除索引
MYSQL中使用ALTER TABLE或者DROP INDEX语句来删除索引，两者实现相同功能
1、使用ALTER TABLE删除索引
语法
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE book DROP INDEX UniqidIdx
SHOW CREATE TABLE book
Table Create Table
------ ----------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
book CREATE TABLE `book` (
`bookid` int(11) NOT NULL,
`bookname` varchar(255) NOT NULL,
`authors` varchar(255) NOT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) NOT NULL,
KEY `BkNameIdx` (`bookname`),
KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))
) ENGINE=MyISAM DEFAULT CHARSET=utf8可以看到，book表中已经没有名为UniqidIdx的唯一索引，删除索引成功
注意：AUTO_INCREMENT约束字段的唯一索引不能被删除！！
2、使用DROP INDEX 语句删除索引
DROP INDEX index_name ON table_name
DROP INDEX BkAuAndInfoIdx ON book
SHOW CREATE TABLE book;
Table Create Table
------
------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------
book CREATE TABLE `book` (
`bookid` int(11) NOT NULL,
`bookname` varchar(255) NOT NULL,
`authors` varchar(255) NOT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) NOT NULL,
KEY `BkNameIdx` (`bookname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8可以看到，复合索引BkAuAndInfoIdx已经被删除了
提示：删除表中的某列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。
如果索引中的所有列都被删除，则整个索引将被删除！！
Das obige ist der detaillierte Inhalt vonMySQL-Optimierungsindex-spezifische Codeanalyse. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!