{"id":8969,"date":"2021-06-29T14:38:15","date_gmt":"2021-06-29T05:38:15","guid":{"rendered":"https:\/\/www.fenet.jp\/infla\/column\/?p=8969"},"modified":"2021-10-18T10:52:57","modified_gmt":"2021-10-18T01:52:57","slug":"mysql-index-%e7%a2%ba%e8%aa%8d%e3%81%a8%e3%81%af%ef%bc%9finformation_schema-statistics%e3%82%92%e7%a2%ba%e8%aa%8d","status":"publish","type":"post","link":"https:\/\/www.fenet.jp\/infla\/column\/network\/mysql-index-%e7%a2%ba%e8%aa%8d%e3%81%a8%e3%81%af%ef%bc%9finformation_schema-statistics%e3%82%92%e7%a2%ba%e8%aa%8d\/","title":{"rendered":"MySQL Index \u78ba\u8a8d\u3068\u306f\uff1fINFORMATION_SCHEMA.STATISTICS\u3092\u78ba\u8a8d"},"content":{"rendered":"<div class=\"gzfmd622974f36ba56\" ><!--HubSpot Call-to-Action Code --><span class=\"hs-cta-wrapper\" id=\"hs-cta-wrapper-04abd3c8-8f3b-4c56-8d11-1804732a9998\"><span class=\"hs-cta-node hs-cta-04abd3c8-8f3b-4c56-8d11-1804732a9998\" id=\"hs-cta-04abd3c8-8f3b-4c56-8d11-1804732a9998\"><!--[if lte IE 8]><div id=\"hs-cta-ie-element\"><\/div><![endif]--><a href=\"https:\/\/cta-redirect.hubspot.com\/cta\/redirect\/20723875\/04abd3c8-8f3b-4c56-8d11-1804732a9998\" target=\"_blank\" rel=\"noopener\"><img class=\"hs-cta-img\" id=\"hs-cta-img-04abd3c8-8f3b-4c56-8d11-1804732a9998\" style=\"border-width:0px;\" src=\"https:\/\/no-cache.hubspot.com\/cta\/default\/20723875\/04abd3c8-8f3b-4c56-8d11-1804732a9998.png\"  alt=\"\u57fa\u672c\u60c5\u5831\u6280\u8853\u8005\u8a66\u9a13\u306e\u8a66\u9a13\u5bfe\u7b56\u306f\u3053\u3061\u3089>>\"\/><\/a><\/span><script charset=\"utf-8\" src=\"https:\/\/js.hscta.net\/cta\/current.js\"><\/script><script type=\"text\/javascript\"> hbspt.cta.load(20723875, '04abd3c8-8f3b-4c56-8d11-1804732a9998', {\"useNewLoader\":\"true\",\"region\":\"na1\"}); <\/script><\/span><!-- end HubSpot Call-to-Action Code --><\/div><style type=\"text\/css\">\r\n@media screen and (min-width: 1201px) {\r\n.gzfmd622974f36ba56 {\r\ndisplay: block;\r\n}\r\n}\r\n@media screen and (min-width: 993px) and (max-width: 1200px) {\r\n.gzfmd622974f36ba56 {\r\ndisplay: block;\r\n}\r\n}\r\n@media screen and (min-width: 769px) and (max-width: 992px) {\r\n.gzfmd622974f36ba56 {\r\ndisplay: block;\r\n}\r\n}\r\n@media screen and (min-width: 768px) and (max-width: 768px) {\r\n.gzfmd622974f36ba56 {\r\ndisplay: block;\r\n}\r\n}\r\n@media screen and (max-width: 767px) {\r\n.gzfmd622974f36ba56 {\r\ndisplay: block;\r\n}\r\n}\r\n<\/style>\r\n<div class=\"plnst622974f36ba0d\" ><!--HubSpot Call-to-Action Code --><span class=\"hs-cta-wrapper\" id=\"hs-cta-wrapper-7a78b0b1-92d6-4b3e-80cf-58e8796e7a76\"><span class=\"hs-cta-node hs-cta-7a78b0b1-92d6-4b3e-80cf-58e8796e7a76\" id=\"hs-cta-7a78b0b1-92d6-4b3e-80cf-58e8796e7a76\"><!--[if lte IE 8]><div id=\"hs-cta-ie-element\"><\/div><![endif]--><a href=\"https:\/\/cta-redirect.hubspot.com\/cta\/redirect\/20723875\/7a78b0b1-92d6-4b3e-80cf-58e8796e7a76\" ><img class=\"hs-cta-img\" id=\"hs-cta-img-7a78b0b1-92d6-4b3e-80cf-58e8796e7a76\" style=\"border-width:0px;\" src=\"https:\/\/no-cache.hubspot.com\/cta\/default\/20723875\/7a78b0b1-92d6-4b3e-80cf-58e8796e7a76.png\"  alt=\"\u6700\u65b0\u60c5\u5831\u3084\u95a2\u5fc3\u306e\u3042\u308b\u60c5\u5831\u3092\u6bce\u65e5\u304a\u5c4a\u3051\u3002  FEnet\u30a4\u30f3\u30d5\u30e9\u30e1\u30fc\u30eb\u30de\u30ac\u30b8\u30f3    \u30e1\u30eb\u30de\u30ac\u767b\u9332\u306f\u3053\u3061\u3089\u304b\u3089>>\"\/><\/a><\/span><script charset=\"utf-8\" src=\"https:\/\/js.hscta.net\/cta\/current.js\"><\/script><script type=\"text\/javascript\"> hbspt.cta.load(20723875, '7a78b0b1-92d6-4b3e-80cf-58e8796e7a76', {\"useNewLoader\":\"true\",\"region\":\"na1\"}); <\/script><\/span><!-- end HubSpot Call-to-Action Code --><\/div><style type=\"text\/css\">\r\n@media screen and (min-width: 1201px) {\r\n.plnst622974f36ba0d {\r\ndisplay: block;\r\n}\r\n}\r\n@media screen and (min-width: 993px) and (max-width: 1200px) {\r\n.plnst622974f36ba0d {\r\ndisplay: block;\r\n}\r\n}\r\n@media screen and (min-width: 769px) and (max-width: 992px) {\r\n.plnst622974f36ba0d {\r\ndisplay: block;\r\n}\r\n}\r\n@media screen and (min-width: 768px) and (max-width: 768px) {\r\n.plnst622974f36ba0d {\r\ndisplay: block;\r\n}\r\n}\r\n@media screen and (max-width: 767px) {\r\n.plnst622974f36ba0d {\r\ndisplay: block;\r\n}\r\n}\r\n<\/style>\r\n<p><!--HubSpot Call-to-Action Code --><span class=\"hs-cta-wrapper\" id=\"hs-cta-wrapper-fa98f1b0-ed6d-41fa-b741-6ddc1c7be6ec\"><span class=\"hs-cta-node hs-cta-fa98f1b0-ed6d-41fa-b741-6ddc1c7be6ec\" id=\"hs-cta-fa98f1b0-ed6d-41fa-b741-6ddc1c7be6ec\"><!--[if lte IE 8]>\n\n<div id=\"hs-cta-ie-element\"><\/div>\n\n<![endif]--><a href=\"https:\/\/cta-redirect.hubspot.com\/cta\/redirect\/20723875\/fa98f1b0-ed6d-41fa-b741-6ddc1c7be6ec\" ><img class=\"hs-cta-img\" id=\"hs-cta-img-fa98f1b0-ed6d-41fa-b741-6ddc1c7be6ec\" style=\"border-width:0px;\" src=\"https:\/\/no-cache.hubspot.com\/cta\/default\/20723875\/fa98f1b0-ed6d-41fa-b741-6ddc1c7be6ec.png\"  alt=\"MySQL\u30a8\u30f3\u30b8\u30cb\u30a2 \u6848\u4ef6\u30fb\u6c42\u4eba\u4e00\u89a7\u306f\u3053\u3061\u3089\"\/><\/a><\/span><script charset=\"utf-8\" src=\"https:\/\/js.hscta.net\/cta\/current.js\"><\/script><script type=\"text\/javascript\"> hbspt.cta.load(20723875, 'fa98f1b0-ed6d-41fa-b741-6ddc1c7be6ec', {\"useNewLoader\":\"true\",\"region\":\"na1\"}); <\/script><\/span><!-- end HubSpot Call-to-Action Code --><\/p>\n<div id=\"toc_container\" class=\"no_bullets\"><p class=\"toc_title\">\u3053\u306e\u8a18\u4e8b\u3067\u308f\u304b\u308b\u3053\u3068<\/p><ul class=\"toc_list\"><li><a href=\"#MySQL_Index\"><span class=\"toc_number toc_depth_1\">1<\/span> MySQL Index \u78ba\u8a8d\u3068\u306f\uff1f<\/a><\/li><li><a href=\"#MySQLIndex\"><span class=\"toc_number toc_depth_1\">2<\/span> MySQL\u306eIndex\u3092\u8a66\u3057\u3066\u307f\u3088\u3046<\/a><\/li><\/ul><\/div>\n<h2><span id=\"MySQL_Index\">MySQL Index \u78ba\u8a8d\u3068\u306f\uff1f<\/span><\/h2>\n<p><img src='https:\/\/b-engineer-media-cms.s3.amazonaws.com\/item\/image\/user\/5e9e289364bb3ddfa01b2e7a2998e19f5b1eaf36\/065316f3-57ed-4e3d-9ee4-7d5f7ba7eedf.jpg'><br \/>\nMySQL\u3067\u306fPRIMARY KEY\uff08PK\uff09\u3001FOREIGN KEY\uff08FK\uff09\u3001UNIQUE\u306fINFORMATION_SCHEMA.TABLE_CONSTRAINTS\u3067\u53c2\u7167\u3057\u307e\u3059\u304c\u3001\u5358\u4e00\u30ab\u30e9\u30e0Index\u3084\u30de\u30eb\u30c1\u30ab\u30e9\u30e0Index\u306fINFORMATION_SCHEMA.STATISTICS\u3067\u53c2\u7167\u3059\u308b\u3053\u3068\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<p>\u3053\u3053\u3067\u306fSQL\u6587\u3092\u767a\u884c\u3057\u306a\u304c\u3089\u5358\u4e00\u30ab\u30e9\u30e0Index\u3084\u30de\u30eb\u30c1\u30ab\u30e9\u30e0Index\u306e\u8ffd\u52a0\u3084\u524a\u9664\u3092\u884c\u3044\u305d\u306e\u7d50\u679c\u3092\u78ba\u8a8d\u3057\u3066\u3044\u304d\u307e\u3059\u3002<\/p>\n<h3>\u4f7f\u7528\u3057\u305fMySQL\u306e\u30d0\u30fc\u30b8\u30e7\u30f3\u3068\u30c7\u30fc\u30bf<\/h3>\n<p>MySQL\u306f\u3001mysql-installer-community-8.0.24.0.msi\u3092DeveloperDefault\u30e2\u30fc\u30c9\u3067\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3057\u3066\u4f7f\u7528\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>\u4f7f\u7528\u3057\u305f\u30c7\u30fc\u30bf\u306f<a href=\"https:\/\/www.mariadbtutorial.com\/getting-started\/mariadb-sample-database\/\">MariaDB Sample Database<\/a>\u3088\u308anation.zip\u3092\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u5f8c\u89e3\u51cd\u3057\u3066\u4f7f\u7528\u3057\u3066\u3044\u307e\u3059\u3002\u89e3\u51cd\u5f8cWindows PowerShell\u3092\u8d77\u52d5\u3057\u3066\u4ee5\u4e0b\u306e\u624b\u9806\u3067MySQL\u306b\u53d6\u8fbc\u3093\u3067\u304f\u3060\u3055\u3044\u3002<\/p>\n<pre>\r\nPS C:\\> mysql -u root -p\r\nEnter password:\r\n\uff5e\r\nmysql> source \/path\/to\/nation.sql\r\n\r\n\u4ee5\u4e0b\u306e\u69d8\u306b\u8868\u793a\u3055\u308c\u307e\u3059\r\nmysql> use nation;\r\nDatabase changed\r\n\r\nmysql> SHOW TABLES;\r\n+-------------------+\r\n| Tables_in_nation  |\r\n+-------------------+\r\n| continents        |\r\n| countries         |\r\n| country_languages |\r\n| country_stats     |\r\n| guests            |\r\n| languages         |\r\n| region_areas      |\r\n| regions           |\r\n| vips              |\r\n+-------------------+\r\n<\/pre>\n<h3>\u30c6\u30b9\u30c8\u7528\u30c7\u30fc\u30bf\u3092\u4f5c\u6210\u3059\u308b<\/h3>\n<p>nation\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u306fIndex\u3092\u6301\u3064\u30c6\u30fc\u30d6\u30eb\u304c\u5b58\u5728\u3057\u306a\u3044\u306e\u3067\u3001\u30c6\u30b9\u30c8\u7528\u306b\u4e0b\u8a18\u306e\u3088\u3046\u306b\u8ffd\u52a0\u3057\u307e\u3059\u3002<\/p>\n<pre>\r\nDROP TABLE IF EXISTS `vips_idx`;\r\nSET character_set_client = utf8mb4 ;\r\nCREATE TABLE `vips_idx` (\r\n  `vip_id` int(11) NOT NULL,\r\n  `name` varchar(100) NOT NULL\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\r\nLOCK TABLES `vips_idx` WRITE;\r\nINSERT INTO `vips_idx` VALUES (1,'Jane'),(2,'Charles'),(3,'John'),(4,'Wolverine'),(5,'Rogue');\r\nUNLOCK TABLES;\r\n\r\nDROP TABLE IF EXISTS `regions_mlt`;\r\n SET character_set_client = utf8mb4 ;\r\nCREATE TABLE `regions_mlt` (\r\n  `region_id` int(11) NOT NULL,\r\n  `name` varchar(100) NOT NULL,\r\n  `continent_id` int(11) NOT NULL\r\n) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;\r\n\r\nLOCK TABLES `regions_mlt` WRITE;\r\nINSERT INTO `regions_mlt` VALUES (1,'Caribbean',1),(2,'Southern and Central Asia',2),(3,'Central Africa',3);\r\nUNLOCK TABLES;\r\n<\/pre>\n<p>\u6210\u529f\u3059\u308c\u3070\u4ee5\u4e0b\u306e\u69d8\u306b\u8868\u793a\u3055\u308c\u307e\u3059\u3002<\/p>\n<pre>\r\nmysql> SHOW TABLES;\r\n+-------------------+\r\n| Tables_in_nation  |\r\n+-------------------+\r\n| continents        |\r\n| countries         |\r\n| country_languages |\r\n| country_stats     |\r\n| guests            |\r\n| languages         |\r\n| region_areas      |\r\n| regions           |\r\n| regions_mlt       |\r\n| vips              |\r\n| vips_idx          |\r\n+-------------------+\r\n<\/pre>\n<h3>Index\u3092\u8ffd\u52a0\u3059\u308b<\/h3>\n<p>vips_idx\u30c6\u30fc\u30d6\u30eb\u306b\u5358\u4e00\u30ab\u30e9\u30e0Index\u3068\u3057\u3066idx_vip_id\u3092\u8ffd\u52a0\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<p>MySQL\u3067\u306f\u4ee5\u4e0b\u306eSQL\u6587\u3092\u5b9f\u884c\u3057\u307e\u3059\u3002<br \/>\nALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d ADD INDEX Index\u540d(\u30ab\u30e9\u30e0\u540d);<\/p>\n<pre>\r\nmysql> ALTER TABLE vips_idx ADD INDEX idx_vip_id(vip_id);\r\nmysql> SHOW INDEX FROM vips_idx\\G\r\n*************************** 1. row ***************************\r\n        Table: vips_idx\r\n   Non_unique: 1\r\n     Key_name: idx_vip_id\r\n Seq_in_index: 1\r\n  Column_name: vip_id\r\n    Collation: A\r\n  Cardinality: 5\r\n     Sub_part: NULL\r\n       Packed: NULL\r\n         Null:\r\n   Index_type: BTREE\r\n      Comment:\r\nIndex_comment:\r\n      Visible: YES\r\n   Expression: NULL\r\n<\/pre>\n<p>Table:vips_idx\u306eColumn_name:vip_id\u306bKey_name:idx_vip_id\u306e\u8ffd\u52a0\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n<p>\u6b21\u306bregions_mlt\u306b\u30de\u30eb\u30c1\u30ab\u30e9\u30e0Index\u3068\u3057\u3066idx_multi\u3092\u8ffd\u52a0\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<p>MySQL\u3067\u306f\u4ee5\u4e0b\u306eSQL\u6587\u3092\u5b9f\u884c\u3057\u307e\u3059\u3002<br \/>\nALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d ADD INDEX Index\u540d(\u30ab\u30e9\u30e0\u540d1, \u30ab\u30e9\u30e0\u540d2, \u30fb\u30fb\u30fb);<\/p>\n<pre>\r\nmysql> ALTER TABLE regions_mlt ADD INDEX idx_multi(region_id, continent_id);\r\nmysql> SHOW INDEX FROM regions_mlt\\G\r\n*************************** 1. row ***************************\r\n        Table: regions_mlt\r\n   Non_unique: 1\r\n     Key_name: idx_multi\r\n Seq_in_index: 1\r\n  Column_name: region_id\r\n    Collation: A\r\n  Cardinality: 3\r\n     Sub_part: NULL\r\n       Packed: NULL\r\n         Null:\r\n   Index_type: BTREE\r\n      Comment:\r\nIndex_comment:\r\n      Visible: YES\r\n   Expression: NULL\r\n*************************** 2. row ***************************\r\n        Table: regions_mlt\r\n   Non_unique: 1\r\n     Key_name: idx_multi\r\n Seq_in_index: 2\r\n  Column_name: continent_id\r\n    Collation: A\r\n  Cardinality: 3\r\n     Sub_part: NULL\r\n       Packed: NULL\r\n         Null:\r\n   Index_type: BTREE\r\n      Comment:\r\nIndex_comment:\r\n      Visible: YES\r\n   Expression: NULL\r\n<\/pre>\n<p>Table:regions_mlt\u306eColumn_name:region_id\u3068Column_name:continent_id\u306bKey_name:idx_multi\u306e\u8ffd\u52a0\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n<h3>INFORMATION_SCHEMA.STATISTICS\u3092\u78ba\u8a8d\u3059\u308b<\/h3>\n<p>\u305d\u308c\u3067\u306fMySQL\u306e\u30c6\u30fc\u30d6\u30eb\u306eIndex\u60c5\u5831\u3092\u63d0\u4f9b\u3059\u308bINFORMATION_SCHEMA.STATISTICS\u3092\u78ba\u8a8d\u3057\u307e\u3059\u3002<\/p>\n<pre>\r\nmysql> SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_SCHEMA, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME\r\n    -> FROM INFORMATION_SCHEMA.STATISTICS\r\n    -> WHERE TABLE_SCHEMA = 'nation' AND INDEX_NAME <>'PRIMARY' AND INDEX_NAME <> COLUMN_NAME ;\r\n+--------------+-------------+------------+--------------+------------+--------------+--------------+\r\n| TABLE_SCHEMA | TABLE_NAME  | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME  |\r\n+--------------+-------------+------------+--------------+------------+--------------+--------------+\r\n| nation       | vips_idx    |          1 | nation       | idx_vip_id |            1 | vip_id       |\r\n| nation       | regions_mlt |          1 | nation       | idx_multi  |            1 | region_id    |\r\n| nation       | regions_mlt |          1 | nation       | idx_multi  |            2 | continent_id |\r\n+--------------+-------------+------------+--------------+------------+--------------+--------------+\r\n<\/pre>\n<p>\u8ffd\u52a0\u3057\u305fIndex\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n<p>\u8868\u793a\u3059\u308b\u9805\u76ee\u306f\u4ee5\u4e0b\u304b\u3089\u9069\u5b9c\u9078\u629e\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n<pre>\r\nmysql> desc INFORMATION_SCHEMA.STATISTICS;\r\n+---------------+---------------+------+-----+---------+-------+\r\n| Field         | Type          | Null | Key | Default | Extra |\r\n+---------------+---------------+------+-----+---------+-------+\r\n| TABLE_CATALOG | varchar(64)   | YES  |     | NULL    |       |\r\n| TABLE_SCHEMA  | varchar(64)   | YES  |     | NULL    |       |\r\n| TABLE_NAME    | varchar(64)   | YES  |     | NULL    |       |\r\n| NON_UNIQUE    | int           | NO   |     | 0       |       |\r\n| INDEX_SCHEMA  | varchar(64)   | YES  |     | NULL    |       |\r\n| INDEX_NAME    | varchar(64)   | YES  |     | NULL    |       |\r\n| SEQ_IN_INDEX  | int unsigned  | NO   |     | NULL    |       |\r\n| COLUMN_NAME   | varchar(64)   | YES  |     | NULL    |       |\r\n| COLLATION     | varchar(1)    | YES  |     | NULL    |       |\r\n| CARDINALITY   | bigint        | YES  |     | NULL    |       |\r\n| SUB_PART      | bigint        | YES  |     | NULL    |       |\r\n| PACKED        | binary(0)     | YES  |     | NULL    |       |\r\n| NULLABLE      | varchar(3)    | NO   |     |         |       |\r\n| INDEX_TYPE    | varchar(11)   | NO   |     |         |       |\r\n| COMMENT       | varchar(8)    | NO   |     |         |       |\r\n| INDEX_COMMENT | varchar(2048) | NO   |     | NULL    |       |\r\n| IS_VISIBLE    | varchar(3)    | NO   |     |         |       |\r\n| EXPRESSION    | longtext      | YES  |     | NULL    |       |\r\n+---------------+---------------+------+-----+---------+-------+\r\n<\/pre>\n<h3>EXPLAIN\u306e\u51fa\u529b<\/h3>\n<p>EXPLAIN\u3092\u4f7f\u7528\u3057\u3066\u3001SELECT\u304c\u4f7f\u7528\u3059\u308b\u30c6\u30fc\u30d6\u30eb\u306e\u60c5\u5831\u3092\u78ba\u8a8d\u3057\u307e\u3059\u3002<\/p>\n<p>possible_keys:idx_vip_id<br \/>\nkey:idx_vip_id<br \/>\nExtra: Using index<\/p>\n<p>\u3053\u306e\u3088\u3046\u306b\u3001Index\u304c\u4f7f\u7528\u3055\u308c\u3066\u3044\u308b\u3053\u3068\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n<pre>\r\nmysql> EXPLAIN SELECT vip_id FROM vips_idx GROUP BY vip_id HAVING COUNT(vip_id) = 1\r\n*************************** 1. row ***************************\r\n           id: 1\r\n  select_type: SIMPLE\r\n        table: vips_idx\r\n   partitions: NULL\r\n         type: index\r\npossible_keys: idx_vip_id\r\n          key: idx_vip_id\r\n      key_len: 4\r\n          ref: NULL\r\n         rows: 5\r\n     filtered: 100.00\r\n        Extra: Using index\r\n<\/pre>\n<p>\u540c\u69d8\u306bEXPLAIN\u3092\u4f7f\u7528\u3057\u3066\u3001SELECT\u304c\u4f7f\u7528\u3059\u308b\u30c6\u30fc\u30d6\u30eb\u306e\u60c5\u5831\u3092\u78ba\u8a8d\u3057\u307e\u3059\u3002<\/p>\n<p>possible_keys:idx_multi<br \/>\nkey:idx_multi<br \/>\nExtra: Using index<br \/>\n\u3053\u306e\u3088\u3046\u306b\u3001Index\u304c\u4f7f\u7528\u3055\u308c\u3066\u3044\u308b\u3053\u3068\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n<pre>\r\nmysql> EXPLAIN SELECT region_id, continent_id FROM regions_mlt GROUP BY region_id, continent_id HAVING COUNT(region_id) = 1\\G\r\n*************************** 1. row ***************************\r\n           id: 1\r\n  select_type: SIMPLE\r\n        table: regions_mlt\r\n   partitions: NULL\r\n         type: index\r\npossible_keys: idx_multi\r\n          key: idx_multi\r\n      key_len: 8\r\n          ref: NULL\r\n         rows: 3\r\n     filtered: 100.00\r\n        Extra: Using index\r\n<\/pre>\n<h3>INFORMATION_SCHEMA.TABLE_CONSTRAINTS\u3092\u78ba\u8a8d\u3059\u308b<\/h3>\n<p>MySQL\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u3042\u308b\u5236\u7d04\u306e\u60c5\u5831\u3092\u63d0\u4f9b\u3059\u308bINFORMATION_SCHEMA.TABLE_CONSTRAINTS\u3092\u78ba\u8a8d\u3057\u307e\u3059\u3002<\/p>\n<pre>\r\nmysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'nation';\r\n+--------------------+-------------------+--------------------------+--------------+-------------------+-----------------+----------+\r\n| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME          | TABLE_SCHEMA | TABLE_NAME        | CONSTRAINT_TYPE | ENFORCED |\r\n+--------------------+-------------------+--------------------------+--------------+-------------------+-----------------+----------+\r\n| def                | nation            | PRIMARY                  | nation       | continents        | PRIMARY KEY     | YES      |\r\n| def                | nation            | country_code2            | nation       | countries         | UNIQUE          | YES      |\r\n| def                | nation            | country_code3            | nation       | countries         | UNIQUE          | YES      |\r\n| def                | nation            | PRIMARY                  | nation       | countries         | PRIMARY KEY     | YES      |\r\n| def                | nation            | countries_ibfk_1         | nation       | countries         | FOREIGN KEY     | YES      |\r\n| def                | nation            | PRIMARY                  | nation       | country_languages | PRIMARY KEY     | YES      |\r\n| def                | nation            | country_languages_ibfk_1 | nation       | country_languages | FOREIGN KEY     | YES      |\r\n| def                | nation            | country_languages_ibfk_2 | nation       | country_languages | FOREIGN KEY     | YES      |\r\n| def                | nation            | PRIMARY                  | nation       | country_stats     | PRIMARY KEY     | YES      |\r\n| def                | nation            | country_stats_ibfk_1     | nation       | country_stats     | FOREIGN KEY     | YES      |\r\n| def                | nation            | PRIMARY                  | nation       | guests            | PRIMARY KEY     | YES      |\r\n| def                | nation            | PRIMARY                  | nation       | languages         | PRIMARY KEY     | YES      |\r\n| def                | nation            | PRIMARY                  | nation       | region_areas      | PRIMARY KEY     | YES      |\r\n| def                | nation            | PRIMARY                  | nation       | regions           | PRIMARY KEY     | YES      |\r\n| def                | nation            | regions_ibfk_1           | nation       | regions           | FOREIGN KEY     | YES      |\r\n| def                | nation            | PRIMARY                  | nation       | vips              | PRIMARY KEY     | YES      |\r\n+--------------------+-------------------+--------------------------+--------------+-------------------+-----------------+----------+\r\n<\/pre>\n<p>Index\u306f\u3053\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u542b\u307e\u308c\u3066\u3044\u306a\u3044\u4e8b\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n<h3>Index\u3092\u524a\u9664\u3059\u308b<\/h3>\n<p>MySQL\u3067\u306f\u4ee5\u4e0b\u306eSQL\u6587\u3092\u5b9f\u884c\u3057\u3066Index\u3092\u524a\u9664\u3057\u307e\u3059\u3002<\/p>\n<p>ALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d DROP INDEX Index\u540d;<\/p>\n<pre>\r\nmysql> ALTER TABLE vips_idx DROP INDEX idx_vip_id;\r\nmysql> ALTER TABLE regions_mlt DROP INDEX idx_multi;\r\n<\/pre>\n<p>INFORMATION_SCHEMA.STATISTICS\u3092\u78ba\u8a8d\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<pre>\r\nmysql> SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_SCHEMA, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME\r\n    -> FROM INFORMATION_SCHEMA.STATISTICS\r\n    -> WHERE TABLE_SCHEMA = 'nation' AND INDEX_NAME <>'PRIMARY' AND INDEX_NAME <> COLUMN_NAME ;\r\nEmpty set (0.11 sec)\r\n<\/pre>\n<p>Index\u304c\u524a\u9664\u3055\u308c\u305f\u3053\u3068\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n<h2><span id=\"MySQLIndex\">MySQL\u306eIndex\u3092\u8a66\u3057\u3066\u307f\u3088\u3046<\/span><\/h2>\n<p>\u3044\u304b\u304c\u3067\u3057\u305f\u3067\u3057\u3087\u3046\u304b\u3002MySQL\u3067\u5358\u4e00\u30ab\u30e9\u30e0Index\u3084\u30de\u30eb\u30c1\u30ab\u30e9\u30e0Index\u306e\u8ffd\u52a0\u3084\u524a\u9664\u3092\u884c\u3044\u305d\u306e\u7d50\u679c\u3092INFORMATION_SCHEMA.STATISTICS\u3067\u78ba\u8a8d\u3057\u307e\u3057\u305f\u3002<\/p>\n<p>\u305c\u3072\u3054\u81ea\u8eab\u3067\u5b9f\u969b\u306bSQL\u3092\u5b9f\u884c\u3057\u3066\u7406\u89e3\u3092\u6df1\u3081\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n<p><!--HubSpot Call-to-Action Code --><span class=\"hs-cta-wrapper\" id=\"hs-cta-wrapper-ff38b228-4db6-497b-a9c4-64102e76bc0c\"><span class=\"hs-cta-node hs-cta-ff38b228-4db6-497b-a9c4-64102e76bc0c\" id=\"hs-cta-ff38b228-4db6-497b-a9c4-64102e76bc0c\"><!--[if lte IE 8]>\n\n<div id=\"hs-cta-ie-element\"><\/div>\n\n<![endif]--><a href=\"https:\/\/cta-redirect.hubspot.com\/cta\/redirect\/20723875\/ff38b228-4db6-497b-a9c4-64102e76bc0c\" ><img class=\"hs-cta-img\" id=\"hs-cta-img-ff38b228-4db6-497b-a9c4-64102e76bc0c\" style=\"border-width:0px;\" src=\"https:\/\/no-cache.hubspot.com\/cta\/default\/20723875\/ff38b228-4db6-497b-a9c4-64102e76bc0c.png\"  alt=\"MySQL\u30a8\u30f3\u30b8\u30cb\u30a2 \u6848\u4ef6\u30fb\u6c42\u4eba\u4e00\u89a7\u306f\u3053\u3061\u3089\"\/><\/a><\/span><script charset=\"utf-8\" src=\"https:\/\/js.hscta.net\/cta\/current.js\"><\/script><script type=\"text\/javascript\"> hbspt.cta.load(20723875, 'ff38b228-4db6-497b-a9c4-64102e76bc0c', {\"useNewLoader\":\"true\",\"region\":\"na1\"}); <\/script><\/span><!-- end HubSpot Call-to-Action Code --><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3053\u306e\u8a18\u4e8b\u3067\u308f\u304b\u308b\u3053\u30681 MySQL Index \u78ba\u8a8d\u3068\u306f\uff1f2 MySQL\u306eIndex\u3092\u8a66\u3057\u3066\u307f\u3088\u3046 MySQL Index \u78ba\u8a8d\u3068\u306f\uff1f MySQL\u3067\u306fPRIMARY KEY\uff08PK\uff09\u3001FOREIGN KEY\uff08FK\uff09\u3001UNI<a class=\"read-more\" href=\"https:\/\/www.fenet.jp\/infla\/column\/network\/mysql-index-%e7%a2%ba%e8%aa%8d%e3%81%a8%e3%81%af%ef%bc%9finformation_schema-statistics%e3%82%92%e7%a2%ba%e8%aa%8d\/\"> &#8230;\u3082\u3063\u3068\u8aad\u3080<\/a><\/p>\n","protected":false},"author":17,"featured_media":4658,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false},"categories":[16],"tags":[173],"_links":{"self":[{"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/posts\/8969"}],"collection":[{"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/comments?post=8969"}],"version-history":[{"count":5,"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/posts\/8969\/revisions"}],"predecessor-version":[{"id":9389,"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/posts\/8969\/revisions\/9389"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/media\/4658"}],"wp:attachment":[{"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/media?parent=8969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/categories?post=8969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fenet.jp\/infla\/column\/wp-json\/wp\/v2\/tags?post=8969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}