インデックスとは何ですか? MySQL には現在、いくつかの主要なインデックス タイプがあります。
- PHP中文网オリジナル
- 2017-06-20 14:23:556919ブラウズ
1. インデックス
MySQL インデックスの確立は、MySQL の効率的な動作にとって非常に重要です。インデックスは MySQL の検索速度を大幅に向上させます。
たとえば、適切に設計されインデックスを使用する MySQL がランボルギーニである場合、設計されていないインデックスを使用する MySQL は人間の三輪車です。
インデックスは、単一列インデックスと結合インデックスに分かれています。単一列インデックスは、インデックスに 1 つの列のみが含まれることを意味します。テーブルには複数の単一列インデックスを含めることができますが、これは結合されたインデックスではありません。結合インデックス。つまり、インデックスには複数の列が含まれます。
インデックスを作成するときは、インデックスが SQL クエリ ステートメントに適用される条件 (通常は WHERE 句の条件として) であることを確認する必要があります。
実際、インデックスはテーブルでもあり、主キーとインデックス フィールドを格納し、エンティティ テーブルのレコードを指します。
インデックスを使用する利点は上で説明しましたが、インデックスを過度に使用すると乱用が発生します。したがって、インデックスには欠点もあります。インデックスによりクエリ速度は大幅に向上しますが、テーブルの INSERT、UPDATE、DELETE などのテーブルの更新速度も低下します。テーブルを更新するとき、MySQL はデータを保存するだけでなく、インデックス ファイルも保存する必要があるためです。
インデックスファイルを作成すると、ディスクスペースが占有されます。
2. インデックス タイプ
Mysql には現在、FULLTEXT、HASH、BTREE、RTREE の主要なインデックス タイプがあります。
1. FULLTEXT
はフルテキスト インデックスであり、現在 MyISAM エンジンでのみサポートされています。これは CREATE TABLE、ALTER TABLE、および CREATE INDEX で使用できますが、現時点では CHAR、VARCHAR、および TEXT 列にフルテキスト インデックスのみを作成できます。
全文インデックスは、WHERE name LIKE "%word%" のようなあいまいテキストベースのクエリの効率が悪いという問題を解決するために MyISAM とともに誕生したものではありません。
2. HASH
HASH は一意 (ほぼ 100% 一意) であり、キーと値のペアのような形式であるため、インデックスとして非常に適しています。
HASH インデックスは一度見つけることができ、ツリー インデックスのようにレイヤーごとに検索する必要がないため、非常に効率的です。ただし、この効率性は条件付きです。つまり、「=」および「in」条件下でのみ効率的であり、範囲クエリ、並べ替え、および結合インデックスについては依然として効率的ではありません。
3. BTREE
BTREE インデックスは、インデックス値を特定のアルゴリズムに従ってツリー状のデータ構造 (バイナリ ツリー) に格納する方法で、各クエリはツリーのエントリ ルートから開始され、順番にノードを横断します。 、葉を取得します。これは、MySQL でデフォルトで最も一般的に使用されるインデックス タイプです。
4. RTREE は MySQL ではほとんど使用されず、このタイプをサポートするストレージ エンジンは MyISAM、BDb、InnoDb、NDb のみです。
BTREEと比較して、RTREEの利点は範囲検索にあります。
ps. この段落の詳細については、次のブログ投稿を参照してください: いくつかの MySQL インデックス タイプの違いと適用性
3. インデックス タイプ
- 通常のインデックス: 高速化されたクエリのみ
- ユニークなインデックス: 高速化されたクエリ+ 列 値は一意です (null を含めることができます)
- 主キーインデックス: クエリを高速化します + 列の値は一意です (null を含めることはできません) + テーブルには 1 つだけあります
- 結合インデックス: 複数の列値はインデックスを形成し、特に結合検索に使用されます。その効率はインデックスの結合よりも優れています
- 全文インデックス: テキストコンテンツをセグメント化して検索します
- ps.
インデックスの結合、複数の単一列を使用します検索するインデックスの組み合わせ
カバーインデックス、選択 データ行を読み込まずにインデックスからのみデータ列を取得できます。つまり、クエリ列は構築されたインデックスによってカバーされている必要があります 4. インデックスの操作。
1. インデックスを作成する
--创建普通索引CREATE INDEX index_name ON table_name(col_name);--创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);--创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);--创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
ALTER TABLE table_name ADD INDEX index_name(col_name);
CREATE TABLE table_name (
ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);
--直接删除索引DROP INDEX index_name ON table_name;--修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;
ここでインデックスの作成方法を学習しましたが、どのような状況でインデックスを作成する必要があるのでしょうか?一般に、WHERE および JOIN に出現するカラムにはインデックスを付ける必要がありますが、MySQL では <、<=、=、>、>=、BETWEEN、IN のインデックスのみが使用され、場合によっては LIKE でインデックスが使用されるため、これが完全に当てはまるわけではありません。索引。例:
- 查看表结构 desc table_name; - 查看生成表的SQL show create table table_name; - 查看索引 show index from table_name; - 查看执行时间 set profiling = 1; SQL... show profiles;
このとき、city と age のインデックスを作成する必要があります。mytable_m テーブルの userame も JOIN 句に出現するため、これもインデックスする必要があります。
インデックスを作成する必要があるのは特定の LIKE のみであると先ほど述べました。 MySQL はワイルドカード文字 % と _ で始まるクエリを作成するときにインデックスを使用しないためです。
データベーステーブルにインデックスを追加すると、確かにクエリ速度が向上しますが、前提条件として、インデックスが間違った方法で使用されていたとしても、クエリに正しく使用されなければなりません。インデックスが作成されても機能しません。
インデックスを作成してもインデックスは有効になりません:SELECT t.Name FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;7. その他の注意事項
- like '%xx' select * from tb1 where name like '%cn';- 使用函数 select * from tb1 where reverse(name) = 'wupeiqi';- or select * from tb1 where nid = 1 or email = 'seven@live.com'; 特别的：当or条件中有未建立索引的列才失效，以下会走索引 select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'- 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where name = 999;- != select * from tb1 where name != 'alex' 特别的：如果是主键，则还是会走索引 select * from tb1 where nid != 123- > select * from tb1 where name > 'alex' 特别的：如果是主键或索引是整数类型，则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123- order by select email from tb1 order by name desc; 当根据索引排序时候，选择的映射如果不是索引，则不走索引 特别的：如果对主键排序，则还是走索引： select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引
八、LIMIT分页
若需求是每页显示10条数据，如何建立分页？
我们可以先使用LIMIT尝试：
--第一页SELECT * FROM table_name LIMIT 0,10;--第二页SELECT * FROM table_name LIMIT 10,10;--第三页SELECT * FROM table_name LIMIT 20,10;
但是这样做有如下弊端：
每一条select语句都会从1遍历至当前位置，若跳转到第100页，则会遍历1000条记录
若记录的id不连续，则会出错
改善：
若已知每页的max_id和min_id，则可以通过主键索引来快速定位:
--下一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);--上一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);--当前页之后的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (页数差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);--当前页之前的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (页数差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P) ORDER BY id ASC;
九、执行计划
explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化
mysql> explain select * from tb2;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set (0.00 sec)
id查询顺序标识
如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL || 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ 特别的：如果使用union连接其值可能为null
select_type
查询类型
SIMPLE 简单查询PRIMARY 最外层查询
SUBQUERY 映射为子查询
DERIVED 子查询UNION 联合UNION RESULT 使用联合的结果
...table正在访问的表名
type
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/constALL 全表扫描，对于数据表从头到尾找一遍select * from tb1;
特别的：如果有limit限制，则找到之后就不再继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。INDEX 全索引扫描，对索引从头到尾找一遍select nid from tb1;
RANGE 对索引列进行范围查找select * from tb1 where name < 'alex';
PS:between andin> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。select * from (select nid from tb1 where nid = 1) as A;
possible_keys
可能使用的索引key真实使用的
key_len
MySQL中使用索引字节长度
rows
mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值extra
该列包含MySQL解决查询的详细信息
“Using index”
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。十、慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，MySQLl数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
1. 查看慢日志参数：
--查询配置命令show variables like '%query%';--当前配置参数binlog_rows_query_log_events OFFft_query_expansion_limit 20have_query_cache YES--时间限制，超过此时间，则记录long_query_time 10.000000query_alloc_block_size 8192query_cache_limit 1048576query_cache_min_res_unit 4096query_cache_size 1048576query_cache_type OFFquery_cache_wlock_invalidate OFFquery_prealloc_size 8192--是否开启慢日志记录slow_query_log OFF--日志文件slow_query_log_file D:\Program Files (x86)\mysql-5.7.18-winx64\data\Jack-slow.log--
2. 修改当前配置
set global 变量名 = 值;--例如，修改时间限制为20slong_query_time = 20;
ps.也可以直接打开慢日志配置文件进行修改，但必须重启服务才能生效
3. 查看MySQL慢日志
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
"""--verbose 版本--debug 调试--help 帮助 -v 版本-d 调试模式-s ORDER 排序方式 what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time-r 反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)-t NUM 显示前N条just show the top n queries-a 不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'-n NUM abstract numbers with at least n digits within names -g PATTERN 正则匹配；grep: only consider stmts that include this string -h HOSTNAME mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l 总时间中不减去锁定时间；don't subtract lock time from total time """
以上がインデックスとは何ですか? MySQL には現在、いくつかの主要なインデックス タイプがあります。の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。