MySQL 最適化インデックス固有のコード分析
MySQL の最適化 - インデックス固有のコード分析:
インデックスはストレージ エンジンに実装されているため、各ストレージ エンジンのインデックスは必ずしも完全に同じであるとは限りません。また、各ストレージ エンジンがすべてのインデックス タイプをサポートしているとは限りません。
ストレージ エンジンに応じて、各テーブルのインデックスの最大数と最大インデックス長を定義します。すべてのストレージ エンジンは、テーブルごとに少なくとも 16 個のインデックスをサポートし、インデックスの合計長は少なくとも 256 バイトです。
ほとんどのストレージ エンジンには、より高い制限があります。 MYSQL には、BTREE と HASH という 2 つのストレージ タイプがあり、これらは特にテーブルのストレージ エンジンに関連します
MYISAM と InnoDB ストレージ エンジンは BTREE インデックスのみをサポートし、HASH と BTREE インデックスをサポートします。
インデックスの利点:1. 一意のインデックスを作成することで、データベーステーブル内のデータの各行の一意性が保証されます2. データクエリにグループ化と並べ替えを使用する場合、グループ化と並べ替えの時間を大幅に短縮できます インデックスの欠点:1. インデックスの維持にはデータベース リソースが必要です2. インデックスはディスク領域を占有し、インデックス ファイルはデータよりも早く最大ファイル サイズに達する可能性があります。ファイル3. テーブルデータを比較する場合、インデックスを維持する必要があるため、速度に影響します 1.プライマリキーインデックスは、null 値を許可しない特別な一意のインデックスです2. 単一列インデックスと複合インデックス単一列インデックスには単一の列のみが含まれます
複数のフィールドに作成されたインデックス。インデックスを作成するときの最初のフィールドのみがクエリ条件で使用されます。複合インデックスを使用する場合は、左端のプレフィックス セット
に従います。 フルテキスト インデックス
フルテキスト インデックスのタイプは FULLTEXT であり、インデックスを作成し、これらのインデックス列に重複値と null を挿入できるようにします。全文インデックスは、
CHAR、VARCHAR、TEXT 型の列に作成できます。 MYSQL の MYISAM ストレージ エンジンのみがフルテキスト インデックスをサポートします
4. 空間インデックス
空間インデックスは、空間データ型のフィールドに対して確立されたインデックスです。MYSQL には 4 つの空間データ型があります。それぞれ、GEOMETRY、LINESTRING、POLYGON。
MYSQL は SPATIAL キーワードで拡張されており、通常のインデックス タイプの作成に使用される構文を使用して空間インデックスを作成できるようになります。空間インデックスの作成に使用される列は、NOT NULL として宣言する必要があります。空間インデックスは、ストレージ エンジンが MYISAM であるテーブルでのみ作成できます。それぞれ一意のインデックス、フルテキスト インデックス、空間インデックスを表します。
index と key は同義語で、両方とも同じ機能を持ち、インデックスの作成を指定するために使用されます。col_name は作成する必要があるフィールド列です。列はデータテーブルに定義されている複数の列から選択する必要があります。 index_name はインデックスの名前を指定します。これが指定されていない場合、MYSQL はデフォルトで Col_name をインデックス値にします。インデックスの長さを示します。インデックスの長さを指定できるのは文字列型フィールドのみです
asc または desc はインデックス値の格納を昇順または降順で指定します
通常のインデックスCREATE TABLE table_name[col_name data type] [unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]
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) ) ;
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=latin1
EXPLAIN ステートメントの出力結果の各行の説明は次のとおりです。
select_type:
はクエリ内の各選択句のタイプを表します (単純 OR 複合)type: テーブル内の MySQL を表します 必要な行を見つける方法 (「アクセス タイプ」とも呼ばれます)、一般的なタイプは次のとおりです。 (上から下の順に効果が良くなります)
possible_keys: MySQL がテーブル内の行を検索するために使用できるインデックスを指定します。クエリにはフィールドにインデックスがある場合、そのインデックスが含まれます。リストされますが、クエリでは使用されない可能性があります
key:クエリ内で MySQL が実際に使用するインデックスを表示します。インデックスが使用されていない場合は、NULL として表示されます
key_len
: を示します。インデックスで使用されるバイト数、およびクエリで使用されるインデックスの長さは、この列を通じて計算できます
ref
rows: MySQL がテーブル統計とインデックス選択に基づいて、必要なレコードを見つけるために読み取る必要がある行数を推定することを示します。
Extra: そうでない追加のものが含まれます。他の列での表示に適していますが、where の使用、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已经被删除了
提示：删除表中的某列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。
如果索引中的所有列都被删除，则整个索引将被删除！！
以上がMySQL 最適化インデックス固有のコード分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。
ホットAIツール
Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ
AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。
Undress AI Tool
脱衣画像を無料で
Clothoff.io
AI衣類リムーバー
AI Hentai Generator
AIヘンタイを無料で生成します。
人気の記事
ホットツール
メモ帳++7.3.1
使いやすく無料のコードエディター
SublimeText3 中国語版
中国語版、とても使いやすい
ゼンドスタジオ 13.0.1
強力な PHP 統合開発環境
ドリームウィーバー CS6
ビジュアル Web 開発ツール
SublimeText3 Mac版
神レベルのコード編集ソフト（SublimeText3）
ホットトピック
7442
15
1371
52
76
11
9
6
rootとしてmysqlにログインできません
Apr 08, 2025 pm 04:54 PM
ルートとしてMySQLにログインできない主な理由は、許可の問題、構成ファイルエラー、一貫性のないパスワード、ソケットファイルの問題、またはファイアウォール傍受です。解決策には、構成ファイルのBind-Addressパラメーターが正しく構成されているかどうかを確認します。ルートユーザー許可が変更されているか削除されてリセットされているかを確認します。ケースや特殊文字を含むパスワードが正確であることを確認します。ソケットファイルの許可設定とパスを確認します。ファイアウォールがMySQLサーバーへの接続をブロックすることを確認します。
MySQLユーザーとデータベースの関係
Apr 08, 2025 pm 07:15 PM
MySQLデータベースでは、ユーザーとデータベースの関係は、アクセス許可と表によって定義されます。ユーザーには、データベースにアクセスするためのユーザー名とパスワードがあります。許可は助成金コマンドを通じて付与され、テーブルはCreate Tableコマンドによって作成されます。ユーザーとデータベースの関係を確立するには、データベースを作成し、ユーザーを作成してから許可を付与する必要があります。
RDS MySQL Redshift Zero ETLとの統合
Apr 08, 2025 pm 07:06 PM
データ統合の簡素化：AmazonrdsmysqlとRedshiftのゼロETL統合効率的なデータ統合は、データ駆動型組織の中心にあります。従来のETL（抽出、変換、負荷）プロセスは、特にデータベース（AmazonrdsmysQlなど）をデータウェアハウス（Redshiftなど）と統合する場合、複雑で時間がかかります。ただし、AWSは、この状況を完全に変えたゼロETL統合ソリューションを提供し、RDSMYSQLからRedshiftへのデータ移行のための簡略化されたほぼリアルタイムソリューションを提供します。この記事では、RDSMysQl Zero ETLのRedshiftとの統合に飛び込み、それがどのように機能するか、それがデータエンジニアと開発者にもたらす利点を説明します。
MySQLのクエリ最適化は、特に大規模なデータセットを扱う場合、データベースのパフォーマンスを改善するために不可欠です
Apr 08, 2025 pm 07:12 PM
1.正しいインデックスを使用して、データの量を削減してデータ検索をスピードアップしました。テーブルの列を複数回検索する場合は、その列のインデックスを作成します。あなたまたはあなたのアプリが基準に従って複数の列からのデータが必要な場合、複合インデックス2を作成します2。選択した列のみを避けます。必要な列のすべてを選択すると、より多くのサーバーメモリを使用する場合にのみサーバーが遅くなり、たとえばテーブルにはcreated_atやupdated_atやupdated_atなどの列が含まれます。
MySQLテーブルロックテーブルを変更するかどうか
Apr 08, 2025 pm 05:06 PM
MySQLがテーブル構造を変更すると、メタデータロックが通常使用され、テーブルがロックされる可能性があります。ロックの影響を減らすために、次の測定値をとることができます。1。オンラインDDLでテーブルを使用できます。 2。バッチで複雑な変更を実行します。 3.小規模またはオフピーク期間中に操作します。 4. PT-OSCツールを使用して、より細かい制御を実現します。
mysqlは支払う必要がありますか
Apr 08, 2025 pm 05:36 PM
MySQLには、無料のコミュニティバージョンと有料エンタープライズバージョンがあります。コミュニティバージョンは無料で使用および変更できますが、サポートは制限されており、安定性要件が低く、技術的な能力が強いアプリケーションに適しています。 Enterprise Editionは、安定した信頼性の高い高性能データベースを必要とするアプリケーションに対する包括的な商業サポートを提供し、サポートの支払いを喜んでいます。バージョンを選択する際に考慮される要因には、アプリケーションの重要性、予算編成、技術スキルが含まれます。完璧なオプションはなく、最も適切なオプションのみであり、特定の状況に応じて慎重に選択する必要があります。
MySQLはAndroidで実行できますか
Apr 08, 2025 pm 05:03 PM
MySQLはAndroidで直接実行できませんが、次の方法を使用して間接的に実装できます。Androidシステムに構築されたLightWeight Database SQLiteを使用して、別のサーバーを必要とせず、モバイルデバイスアプリケーションに非常に適したリソース使用量が少ない。 MySQLサーバーにリモートで接続し、データの読み取りと書き込みのためにネットワークを介してリモートサーバー上のMySQLデータベースに接続しますが、強力なネットワーク依存関係、セキュリティの問題、サーバーコストなどの短所があります。
高負荷アプリケーションのMySQLパフォーマンスを最適化する方法は？
Apr 08, 2025 pm 06:03 PM
MySQLデータベースパフォーマンス最適化ガイドリソース集約型アプリケーションでは、MySQLデータベースが重要な役割を果たし、大規模なトランザクションの管理を担当しています。ただし、アプリケーションのスケールが拡大すると、データベースパフォーマンスのボトルネックが制約になることがよくあります。この記事では、一連の効果的なMySQLパフォーマンス最適化戦略を検討して、アプリケーションが高負荷の下で効率的で応答性の高いままであることを保証します。実際のケースを組み合わせて、インデックス作成、クエリ最適化、データベース設計、キャッシュなどの詳細な主要なテクノロジーを説明します。 1.データベースアーキテクチャの設計と最適化されたデータベースアーキテクチャは、MySQLパフォーマンスの最適化の基礎です。いくつかのコア原則は次のとおりです。適切なデータ型を選択し、ニーズを満たす最小のデータ型を選択すると、ストレージスペースを節約するだけでなく、データ処理速度を向上させることもできます。
