一、索引
MySQL索引的建立對於MySQL的高效運作是很重要的,索引可以大幅提高MySQL的檢索速度。
打個比方,如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話,那麼沒有設計和使用索引的MySQL就是一個人力三輪車。
索引分單列索引和組合索引。單列索引,即一個索引只包含單一資料列,一個表格可以有多個單列索引,但這不是組合索引。組合索引,即一個索引包含多個欄位。
建立索引時,你需要確保索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。
實際上,索引也是一張表,該表保存了主鍵與索引字段,並指向實體表的記錄。
上面都在說使用索引的好處,但過多的使用索引將會造成濫用。因此索引也會有它的缺點:雖然索引大大提高了查詢速度,同時卻會降低更新表的速度,如對錶進行INSERT、UPDATE和DELETE。因為更新表時,MySQL不僅要保存數據,還要保存索引檔。
建立索引會佔用磁碟空間的索引檔案。
二、索引類型
Mysql目前主要有以下幾種索引類型:FULLTEXT,HASH,BTREE,RTREE。
1. FULLTEXT
即為全文索引,目前只有MyISAM引擎支援。其可在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不過目前只有 CHAR、VARCHAR ,TEXT 欄位上可以建立全文索引。
全文索引並不是和MyISAM一起誕生的,它的出現是為了解決WHERE name LIKE “%word%"這類針對文本的模糊查詢效率較低的問題。
2. HASH
由於HASH的唯一(幾乎100%的唯一)及類似鍵值對的形式,很適合作為索引。
HASH索引可以一次定位,不需要像樹狀索引那樣逐層查找,因此具有極高的效率。但是,這種高效是有條件的,即只在「=」和「in」條件下高效,對於範圍查詢、排序及組合索引仍然效率不高。
3. BTREE
BTREE索引就是一種將索引值依一定的演算法,存入一個樹狀的資料結構中(二叉樹),每次查詢都是從樹的入口root開始,依序遍歷node,取得leaf。這是MySQL裡預設和最常用的索引類型。
4. RTREE
RTREE在MySQL很少使用,僅支援geometry資料類型,支援該類型的儲存引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種。
相對於BTREE,RTREE的優點在於範圍查找。
ps. 此段詳細內容請見此片博文:Mysql幾種索引類型的區別及適用情況
三、索引種類
#ps.
索引合併,使用多個單列索引組合搜索
覆寫索引,select的資料列只用從索引就能夠取得,不必讀取資料行,換句話說查詢列要被所建立的索引覆寫
四、操作索引
1. 建立索引
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);
|
登入後複製
2. 透過修改表格結構建立索引
1 | ALTER TABLE table_name ADD INDEX index_name(col_name);
|
登入後複製
3. 建立表格時直接指定索引
1 2 3 | CREATE TABLE table_name (
ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);
|
登入後複製
4. 刪除索引
1 | --直接删除索引DROP INDEX index_name ON table_name;--修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;
|
登入後複製
5. 其它相關命令
#
1 2 3 4 5 6 7 8 9 10 | - 查看表结构
desc table_name;
- 查看生成表的SQL
show create table table_name;
- 查看索引
show index from table_name;
- 查看执行时间
set profiling = 1;
SQL...
show profiles;
|
登入後複製
五、建立索引的時機
到這裡我們已經學會了建立索引,那麼我們需要在什麼情況下建立索引呢?一般來說,在WHERE和JOIN中出現的欄位需要建立索引,但也不完全如此,因為MySQL只對<,<=,=,>,>=,BETWEEN,IN,以及某些時候的LIKE才會使用索引。例如:
1 | SELECT t.Name FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city= '郑州' ;
|
登入後複製
此時就需要對city和age建立索引,由於mytable_m表的userame也出現在了JOIN子句中,也有對它建立索引的必要。
剛才提到只有某些時候的LIKE才需建立索引。因為以通配符%和_開頭作查詢時,MySQL不會使用索引。
六、命中索引
資料庫表中新增索引後確實會讓查詢速度起飛,但前提必須是正確的使用索引來查詢,如果以錯誤的方式使用,則即使建立索引也會不起作用。
即使建立索引,索引也不會生效:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | - 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 -- 不使用索引
|
登入後複製
#七、其它注意事項
1 | - 避免使用select *- count (1)或 count (列) 代替 count (*)- 创建表时尽量时 char 代替 varchar- 表的字段顺序固定长度的字段优先- 组合索引代替多个单列索引(经常使用多个条件查询时)- 尽量使用短索引- 使用连接(JOIN)来代替子查询(Sub-Queries)- 连表时注意条件类型需一致- 索引散列值(重复多)不适合建索引,例:性别不适合
|
登入後複製
八、LIMIT分页
若需求是每页显示10条数据,如何建立分页?
我们可以先使用LIMIT尝试:
1 | --第一页SELECT * FROM table_name LIMIT 0,10;--第二页SELECT * FROM table_name LIMIT 10,10;--第三页SELECT * FROM table_name LIMIT 20,10;
|
登入後複製
但是这样做有如下弊端:
改善:
若已知每页的max_id和min_id,则可以通过主键索引来快速定位:
1 | --下一页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优化
1 | 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)
|
登入後複製


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | 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. 查看慢日志参数:
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. 修改当前配置
1 | set global 变量名 = 值;--例如,修改时间限制为20slong_query_time = 20;
|
登入後複製
ps.也可以直接打开慢日志配置文件进行修改,但必须重启服务才能生效
3. 查看MySQL慢日志
1 | mysqldumpslow -s at -a /usr/local/ var /mysql/MacBook-Pro-3-slow.log
|
登入後複製
1 2 3 4 5 6 7 8 9 10 11 12 13 | "" "--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中文網其他相關文章!