1. 인덱스
MySQL 인덱스의 구축은 MySQL의 효율적인 운영을 위해 매우 중요합니다. 인덱스는 MySQL의 검색 속도를 크게 향상시킬 수 있습니다.
예를 들어 제대로 설계되어 인덱스를 사용하는 MySQL이 람보르기니라면, 설계되지 않고 인덱스를 사용하는 MySQL은 인간 세발자전거입니다.
인덱스는 단일 열 인덱스와 결합 인덱스로 구분됩니다. 단일 열 인덱스는 인덱스가 단일 열만 포함한다는 의미입니다. 테이블은 여러 개의 단일 열 인덱스를 가질 수 있지만 이는 결합된 인덱스가 아닙니다. 결합 인덱스, 즉 인덱스에는 여러 열이 포함됩니다.
인덱스를 생성할 때 인덱스가 SQL 쿼리 문에 적용되는 조건(일반적으로 WHERE 절의 조건)인지 확인해야 합니다.
사실 인덱스는 기본 키와 인덱스 필드를 저장하고 엔터티 테이블의 레코드를 가리키는 테이블이기도 합니다.
위에서 인덱스를 사용하면 얻을 수 있는 이점이 있지만 인덱스를 과도하게 사용하면 악용이 발생할 수 있습니다. 따라서 인덱스에도 단점이 있습니다. 인덱스는 쿼리 속도를 크게 향상시키지만 테이블에 대한 INSERT, UPDATE 및 DELETE와 같은 테이블 업데이트 속도도 감소시킵니다. 테이블을 업데이트할 때 MySQL은 데이터를 저장할 뿐만 아니라 인덱스 파일도 저장해야 하기 때문입니다.
인덱스 파일을 생성하면 디스크 공간을 차지하게 됩니다.
Mysql에는 현재 FULLTEXT, HASH, BTREE, RTREE와 같은 주요 인덱스 유형이 있습니다.
는 현재 MyISAM 엔진에서만 지원되는 전체 텍스트 인덱스입니다. CREATE TABLE, ALTER TABLE, CREATE INDEX에서 사용할 수 있지만 현재는 CHAR, VARCHAR, TEXT 열에 대해 전체 텍스트 인덱스만 생성할 수 있습니다.
전체 텍스트 인덱스는 MyISAM과 함께 탄생하지 않았습니다. 이는 WHERE 이름 LIKE "%word%"와 같은 퍼지 텍스트 기반 쿼리의 효율성이 낮은 문제를 해결하기 위해 나타났습니다.
HASH는 고유성(거의 100% 고유성)을 가지며 키-값 쌍 형태를 가지므로 인덱스로 매우 적합합니다.
HASH 인덱스는 한 번만 찾을 수 있고 트리 인덱스처럼 레이어별로 검색할 필요가 없어 매우 효율적입니다. 그러나 이 효율성은 조건부입니다. 즉, "=" 및 "in" 조건에서만 효율적이며 범위 쿼리, 정렬 및 결합 인덱스에는 여전히 효율적이지 않습니다.
BTREE 인덱스는 특정 알고리즘에 따라 인덱스 값을 트리 형태의 데이터 구조(바이너리 트리)에 저장하는 방법입니다. 각 쿼리는 트리의 항목 루트에서 시작하여 순차적으로 노드를 통과합니다. , 그리고 잎을 얻습니다. 이는 MySQL에서 기본이자 가장 일반적으로 사용되는 인덱스 유형입니다.
RTREE는 MySQL에서 거의 사용되지 않으며 이 유형을 지원하는 유일한 스토리지 엔진은 MyISAM, BDb, InnoDb, NDb 및 Archive입니다.
BTREE에 비해 RTREE의 장점은 범위 검색에 있습니다.
ps. 이 단락에 대한 자세한 내용은 다음 블로그 게시물을 참조하세요. 여러 MySQL 인덱스 유형의 차이점 및 적용 가능성
일반 인덱스: 가속 쿼리만
고유 인덱스: 가속 쿼리 + 열 고유 값(null을 가질 수 있음)
기본 키 인덱스: 쿼리 속도 향상 + 고유 열 값(null을 가질 수 없음) + 테이블에 하나만
결합 인덱스: 여러 열 값이 인덱스를 형성함 , 특별히 결합 검색에 사용되며 색인 병합보다 효율성이 더 높습니다
전체 텍스트 색인: 텍스트 내용을 분할하여 검색합니다.
ps.
색인 병합, 여러 단일 열 색인 조합을 사용하여 검색
커버링 인덱스, 선택 데이터 행을 읽지 않고 인덱스에서만 데이터 열을 가져올 수 있습니다. 즉, 쿼리 열은 내장된 인덱스로 커버되어야 합니다.
--创建普通索引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;
- 查看表结构 desc table_name; - 查看生成表的SQL show create table table_name; - 查看索引 show index from table_name; - 查看执行时间 set profiling = 1; SQL... show profiles;
이때 mytable_m 테이블의 userame도 JOIN 절에 나타나므로 index도 필요합니다.
6. 인덱스 조회
데이터베이스 테이블에 인덱스를 추가하면 쿼리 속도가 확실히 빨라지지만, 잘못된 방법으로 사용하더라도 인덱스를 올바르게 쿼리에 사용해야 한다는 전제가 있습니다. 인덱스가 생성되면 작동하지 않습니다.
인덱스가 생성되더라도 인덱스가 적용되지 않습니다: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 -- 不使用索引
若需求是每页显示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数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
--查询配置命令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--
set global 变量名 = 值;--例如，修改时间限制为20slong_query_time = 20;
ps.也可以直接打开慢日志配置文件进行修改，但必须重启服务才能生效
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 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
