关于MySQL数据库索引和优化的研究_浩视达数据恢
日期：2014-07-13 / 人气： / 来源：网络
摘要：
第一部分：基础知识：
索引
官方介绍索引是帮助MySQL高效获取数据的数据结构。笔者理解索引相当于一本书的目录，通过目录就知道要的资料在哪里，不用一页一页查阅找出需要的资料。关键字index
-------------------------------------------------------------
唯一索引
强调唯一，就是索引值必须唯一，关键字unique index
创建索引：
1、create unique index 索引名 on 表名(列名);
2、alter table 表名 add unique index 索引名 (列名);
删除索引：
1、drop index 索引名 on 表名;
2、alter table 表名 drop index 索引名;
-------------------------------------------------------------
主键
主键就是唯一索引的一种，主键要求建表时指定，一般用auto_increatment列，关键字是primary key
主键创建：
creat table test2 (id int not null primary key auto_increment);
-------------------------------------------------------------
全文索引
InnoDB不支持，Myisam支持性能比较好，一般在 CHAR、VARCHAR 或 TEXT 列上创建。
Create table 表名( id int not null primary anto_increment,title
varchar(100), FULLTEXT(title))type= myisam
------------------------------
单列索引与多列索引
索引可以是单列索引也可以是多列索引(也叫复合索引)。按照上面形式创建出来的索引是单列索引，现在先看看创建多列索引：
create table test3 (id int not null primary key auto_increment,uname char
(8) not null default '',password char(12) not null, INDEX(uname,password))type
=myisam;
注意：INDEX(a, b, c)可以当做a或(a, b)的索引来使用，但和b、c或(b,c)的索引来使用这是一个 最左前缀的优化方法，在后面会有详细的介绍，你只要知道有这样两个概念
-------------------------------------------------------------
聚集索引
一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 聚集索引确定表中数据的物理顺序。Mysql中myisam表是没有聚集索引的，innodb有(主键就是聚集索引)，聚集索引在下面介绍innodb结构的时有详细介绍。
-------------------------------------------------------------
查看表的索引
通过命令：Show index from 表名
如：
mysql> show index from test3;
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ----
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ----
| test3 | 0 | PRIMARY | 1 | id | A | 0 | NULL |
NULL | | BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
Table：表名
Key_name：什么类型索引(这了是主键)
Column_name：索引列的字段名
Cardinality：索引基数，很关键的一个参数，平均数值组=索引基数/表总数据行，平均数值组越接近1就越有可能利用索引
Index_type：如果索引是全文索引，则是fulltext,这里是b tree索引，b tre也是这篇文章研究的重点之一
其他的就不详细介绍，更多：
第二部分： MYISAM 和 INNODB 索引结构
1、 简单介绍 B-tree B tree 树
B-tree结构视图
一棵m阶的B-tree树，则有以下性质
(1)Ki表示关键字值，上图中，k1<k2<…<ki<k0<Kn(可以看出，一个节点的左子节点关键字值<该关键字值<右子节点关键字值)
(2)Pi表示指向子节点的指针，左指针指向左子节点，右指针指向右子节点。即是：p1[指向值]<k1<p2[指向值]<k2……
(3)所有关键字必须唯一值(这也是创建myisam 和innodb表必须要主键的原因)，每个节点包含一个说明该节点多少个关键字，如上图第二行的i和n
(4)节点：
l每个节点最可以有m个子节点。
l根节点若非叶子节点，至少2个子节点，最多m个子节点
l每个非根，非叶子节点至少[m/2]子节点或叫子树([]表示向上取整)，最多m个子节点
(5)关键字：
l根节点的关键字个数1~m-1
l非根非叶子节点的关键字个数[m/2]-1~m-1,如m=3，则该类节点关键字个数：2-1~2
(6)关键字数k和指向子节点个数指针p的关系：
lk 1=p ，注意根据储存数据的具体需求，左右指针为空时要有标志位表示没有
B tree结构示意图如下：
B 树是B-树的变体，也是一种多路搜索树：
l非叶子结点的子树指针与关键字个数相同
l为所有叶子结点增加一个链指针(红点标志的箭头)
B 树是B-树的变体，也是一种多路搜索树：
l非叶子结点的子树指针与关键字个数相同
l为所有叶子结点增加一个链指针(红点标志的箭头)
字符数是255(不是字节)，不管什么编码，超过此值则自动截取255个字符保存并没有报错。
注意当一些英文或数据的时候，最好用每个字符用字节少的类型，如latin1
(4) 整型、整形优先原则
Tinyint、smallint、mediumint、int、bigint，分别需要8、16、24、32、64。
值域范围：-2^(n-1)~ 2^(n-1)-1
很多程序员在设计数据表的时候很习惯的用int，压根不考虑这个问题
笔者建议：能用tinyint的绝不用smallint
误区：int(1) 和int(11)是一样的，唯一区别是mysql客户端显示的时候显示多少位。
整形优先原则：能用整形的不用其他类型替换，如ip可以转换成整形保存，如商品价格‘50.00元’则保存成50
(5)精确度与空间的转换。在存储相同数值范围的数据时，浮点数类型通常都会比DECIMAL类型使用更少的空间。FLOAT字段使用4字节存储
数据。DOUBLE类型需要8 个字节并拥有更高的精确度和更大的数值范围，DECIMAL类型的数据将会转换成DOUBLE类型。
2、 sql 语句优化
mysql> create table one (
id smallint(10) not null auto_increment primary key,
username char(8) not null,
password char(4) not null,
`level` tinyint (1) default 0,
last_login char(15) not null,
index(username,password,last_login))engine=innodb;
这是test表，其中id是主键，多列索引(username,password,last_login),里面有10000多条数据.
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
| one | 0 | PRIMARY | 1 | id | A |20242 | NULL | NULL | |
BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
| one | 1 | username | 1 | username | A |10121 | NULL | NULL | |
BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
| one | 1 | username | 2 | password | A |10121 | NULL | NULL | YES |
BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
| one | 1 | username | 3 | last_login | A |20242 | NULL | NULL | |
BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
(1) 最左前缀原则
定义：最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。
举例说明：上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如(username,password,last_login)、(username,password)、(username)才能用到索引，如下面几个顺序(password,last_login)、(passwrod)、(last_login)---这三者不从username开始，(username,last_login)---断层，少了password，都无法利用到索引。
因为B tree多列索引保存的顺序是按照索引创建的顺序，检索索引时按照此顺序检索
测试：以下测试不精确，这里只是说明如何才能正确按照最左前缀原则使用索引。还有的是以下的测试用的时间0.00sec看不出什么时间区别，因为数据量只有20003条，加上没有在实体机上运行，很多未可预知的影响因素都没考虑进去。当在大数据量，高并发的时候，最左前缀原则对与提高性能方面是不可否认的。
Ps：最左前缀原则中where字句有or出现还是会遍历全表
(1.1) 能正确的利用索引
lWhere子句表达式 顺序是(username)
mysql> explain select * from one where username='abgvwfnt';
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
1 row in set (0.00 sec)
lWhere子句表达式 顺序是(username,password)
mysql> explain select * from one where username='abgvwfnt' and password='123456';
---- ------------- ------- ------ --------------- ---------- --------- ------------- ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------- ------ --------------- ---------- --------- ------------- ------ -------------
| 1 | SIMPLE | one | ref | username | username | 43 | const,const | 1 | Using where |
---- ------------- ------- ------ --------------- ---------- --------- ------------- ------ -------------
1 row in set (0.00 sec)
lWhere子句表达式 顺序是(username,password, last_login)
mysql> explain select * from one where username='abgvwfnt' and password='123456'and last_login='1338251170';
---- ------------- ------- ------ --------------- ---------- --------- ------------------- ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra |
---- ------------- ------- ------ --------------- ---------- --------- ------------------- ------ -------------
| 1 | SIMPLE | one | ref | username | username | 83 | const,const,const | 1 | Using where |
---- ------------- ------- ------ --------------- ---------- --------- ------------------- ------ -------------
1 row in set (0.00 sec)
上面可以看出type=ref 是多列索引，key_len分别是24、43、83，这说明用到的索引分别是(username), (username,password), (username,password, last_login );row分别是5、1、1检索的数据行都很少，因为这三个查询都按照索引前缀原则，可以利用到索引。
(1.2) 不能正确的利用索引
lWhere子句表达式 顺序是(password, last_login)
mysql> explain select * from one where password='123456'and last_login='1338251170';
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 20146 | Using where |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
1 row in set (0.00 sec)
lWhere 子句表达式顺序是(last_login)
mysql> explain select * from one where last_login='1338252525';
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 20146 | Using where |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
1 row in set (0.00 sec)
以上的两条语句都不是以username开始，这样是用不了索引，通过type=all(全表扫描)，key_len=null，rows都很大20146
Ps：one表里只有20003条数据，为什么出现20146，这是优化器对表的一个估算值，不精确的。
lWhere 子句表达式虽然顺序是(username,password, last_login)或(username,password)但第一个是有范围’<’、’>’，’<=’，’>=’等出现
mysql> explain select * from one where username>'abgvwfnt' and password ='123456'and last_login='1338251170';
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| 1 | SIMPLE | one | ALL | username | NULL | NULL | NULL | 20146 | Using where |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
1 row in set (0.00 sec)
这个查询很明显是遍历所有表，一个索引都没用到，非第一列出现范围(password列或last_login列)，则能利用索引到首先出现范围的一列，也就是“where username='abgvwfnt' and password >'123456'and last_login='1338251170';”或则“where username='abgvwfnt' and password >'123456'and last_login<'1338251170';”索引长度ref_len=43,索引检索到password列，所以考虑多列索引的时候把那些查询语句用的比较的列放在最后(或非第一位)。
l断层，即是where顺序(username, last_login)
mysql> explain select * from one where username='abgvwfnt' and last_login='1338252525';
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
1 row in set (0.00 sec)
注意这里的key_len=24=8*3(8是username的长度，3是utf8编码)，rows=5，和下面一条sql语句搜索出来一样
mysql> select * from one where username='abgvwfnt';
------- ---------- ---------- ------- ------------
| id | username | password | level | last_login |
------- ---------- ---------- ------- ------------
| 3597 | abgvwfnt | 234567 | 0 | 1338251420 |
| 7693 | abgvwfnt | 456789 | 0 | 1338251717 |
| 11789 | abgvwfnt | 456789 | 0 | 1338251992 |
| 15885 | abgvwfnt | 456789 | 0 | 1338252258 |
| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |
------- ---------- ---------- ------- ------------
5 rows in set (0.00 sec)
mysql> select * from one where username='abgvwfnt' and last_login='1338252525';
------- ---------- ---------- ------- ------------
| id | username | password | level | last_login |
------- ---------- ---------- ------- ------------
| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |
------- ---------- ---------- ------- ------------
1 row in set (0.00 sec)
这个就是要的返回结果，所以可以知道断层(username,last_login)，这样只用到username索引，把用到索引的数据再重新检查last_login条件，这个相对全表查询来说还是有性能上优化，这也是很多sql优化文章中提到的where 范围查询要放在最后(这不绝对，但可以利用一部分索引)
(1.3) 如果一个查询 where 子句中确实不需要 password 列，那就用“补洞”。
mysql> select distinct(password) from one;
----------
| password |
----------
| 234567 |
| 345678 |
| 456789 |
| 123456 |
----------
4 rows in set (0.08 sec)
可以看出password列中只有这几个值，当然在现实中不可能密码有这么多一样的，再说数据也可能不断更新，这里只是举例说明补洞的方法
mysql> explain select * from one where username='abgvwfnt' and password in('123456','234567','345678','456789')
and last_login='1338251170';
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
| 1 | SIMPLE | one | range | username | username| 83 | NULL |4 | Using where |
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
1 row in set (0.00 sec)
可以看出ref=83 所有的索引都用到了，type=range是因为用了in子句。
这个被“补洞”列中的值应该是有限的，可预知的，如性别，其值只有男和女(加多一个不男不女也无妨)。
“补洞”方法也有瓶颈，当很多列，且需要补洞的相应列(可以多列)的值虽有限但很多(如中国城市)的时候，优化器在优化时组合起来的数量是很大，这样的话就要做好基准测试和性能分析，权衡得失，取得一个合理的优化方法。
(1.4)like
mysql> explain select * from one where username like 'abgvwfnt%';
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
| 1 | SIMPLE | one | range | username | username | 24 | NULL |
5 | Using where |
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
1 row in set (0.00 sec)
mysql> explain select * from one where username like '
| 第一部分：基础知识 第二部分：MYISAM和INNODB索引结构 1、 简单介绍B-tree B tree树 2、 MyisAM索引结构 3、 Annode索引结构 4、 MyisAM索引与InnoDB索引相比较 第三部分：MYSQL优化 1、表数据类型选择 2、sql语句优化 (1) 最左前缀原则 (1.1)能正确的利用索引 (1.2)不能正确的利用索引 (1.3)如果一个查询where子句中确实不需要password列，那就用“补洞”。 (1.4)like (2) Order by 优化 (2.1) filesort优化算法. (2.2) 单独order by 用不了索引，索引考虑加where 或加limit (2.3) where orerby 类型，where满足最左前缀原则，且orderby的列和where子句用到的索引的列的子集。即是(a,b,c)索引，where满足最左前缀原则且order by中列a、b、c的任意组合 (2.4) where orerby limit (2.5) 如何考虑order by来建索引 (3) 隔离列 (4) OR、IN、UNION ALL，可以尝试用UNION ALL (4.1) or会遍历表就算有索引 (4.2)关于in (4.2) UNION All (5) 范索引选择性 (6) 重复或多余索引 3、系统配置与维护优化 (1) 重要的一些变量 (2) Fds optimize、Analyze、check、repair维护操作 (3) 表结构的更新与维护 第四部分：图说mysql查询执行流程 |
索引
官方介绍索引是帮助MySQL高效获取数据的数据结构。笔者理解索引相当于一本书的目录，通过目录就知道要的资料在哪里，不用一页一页查阅找出需要的资料。关键字index
-------------------------------------------------------------
唯一索引
强调唯一，就是索引值必须唯一，关键字unique index
创建索引：
1、create unique index 索引名 on 表名(列名);
2、alter table 表名 add unique index 索引名 (列名);
删除索引：
1、drop index 索引名 on 表名;
2、alter table 表名 drop index 索引名;
-------------------------------------------------------------
主键
主键就是唯一索引的一种，主键要求建表时指定，一般用auto_increatment列，关键字是primary key
主键创建：
creat table test2 (id int not null primary key auto_increment);
-------------------------------------------------------------
全文索引
InnoDB不支持，Myisam支持性能比较好，一般在 CHAR、VARCHAR 或 TEXT 列上创建。
Create table 表名( id int not null primary anto_increment,title
varchar(100), FULLTEXT(title))type= myisam
------------------------------
单列索引与多列索引
索引可以是单列索引也可以是多列索引(也叫复合索引)。按照上面形式创建出来的索引是单列索引，现在先看看创建多列索引：
create table test3 (id int not null primary key auto_increment,uname char
(8) not null default '',password char(12) not null, INDEX(uname,password))type
=myisam;
注意：INDEX(a, b, c)可以当做a或(a, b)的索引来使用，但和b、c或(b,c)的索引来使用这是一个 最左前缀的优化方法，在后面会有详细的介绍，你只要知道有这样两个概念
-------------------------------------------------------------
聚集索引
一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 聚集索引确定表中数据的物理顺序。Mysql中myisam表是没有聚集索引的，innodb有(主键就是聚集索引)，聚集索引在下面介绍innodb结构的时有详细介绍。
-------------------------------------------------------------
查看表的索引
通过命令：Show index from 表名
如：
mysql> show index from test3;
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ----
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ----
| test3 | 0 | PRIMARY | 1 | id | A | 0 | NULL |
NULL | | BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
Table：表名
Key_name：什么类型索引(这了是主键)
Column_name：索引列的字段名
Cardinality：索引基数，很关键的一个参数，平均数值组=索引基数/表总数据行，平均数值组越接近1就越有可能利用索引
Index_type：如果索引是全文索引，则是fulltext,这里是b tree索引，b tre也是这篇文章研究的重点之一
其他的就不详细介绍，更多：
第二部分： MYISAM 和 INNODB 索引结构
1、 简单介绍 B-tree B tree 树
B-tree结构视图
一棵m阶的B-tree树，则有以下性质
(1)Ki表示关键字值，上图中，k1<k2<…<ki<k0<Kn(可以看出，一个节点的左子节点关键字值<该关键字值<右子节点关键字值)
(2)Pi表示指向子节点的指针，左指针指向左子节点，右指针指向右子节点。即是：p1[指向值]<k1<p2[指向值]<k2……
(3)所有关键字必须唯一值(这也是创建myisam 和innodb表必须要主键的原因)，每个节点包含一个说明该节点多少个关键字，如上图第二行的i和n
(4)节点：
l每个节点最可以有m个子节点。
l根节点若非叶子节点，至少2个子节点，最多m个子节点
l每个非根，非叶子节点至少[m/2]子节点或叫子树([]表示向上取整)，最多m个子节点
(5)关键字：
l根节点的关键字个数1~m-1
l非根非叶子节点的关键字个数[m/2]-1~m-1,如m=3，则该类节点关键字个数：2-1~2
(6)关键字数k和指向子节点个数指针p的关系：
lk 1=p ，注意根据储存数据的具体需求，左右指针为空时要有标志位表示没有
B tree结构示意图如下：
B 树是B-树的变体，也是一种多路搜索树：
l非叶子结点的子树指针与关键字个数相同
l为所有叶子结点增加一个链指针(红点标志的箭头)
B 树是B-树的变体，也是一种多路搜索树：
l非叶子结点的子树指针与关键字个数相同
l为所有叶子结点增加一个链指针(红点标志的箭头)
字符数是255(不是字节)，不管什么编码，超过此值则自动截取255个字符保存并没有报错。
| |
65535个字节，开始两位存储长度，超过255个字符，用2位储存长度，否则1位，具体字符长度根据编码来确定，如utf8 则字符最长是21845个 |
|
| 如何处理字符串末尾空格 |
去掉末尾空格，取值出来比较的时候自动加上进行比较 |
Version<=4.1，字符串末尾空格被删掉，version>5.0则保留 |
| 储存空间 |
固定空间，比喻char(10)不管字符串是否有10个字符都分配10个字符的空间 |
Varchar内节约空间，但更新可能发生变化，若varchar(10),开始若储存5个字符，当update成7个时有myisam可能把行拆开，innodb可能分页，这样开销就增大 |
| 适用场合 |
适用于存储很短或固定或长度相似字符，如MD5加密的密码char(33)、昵称char(8)等 |
当最大长度远大于平均长度并且发生更新的时候。 |
(4) 整型、整形优先原则
Tinyint、smallint、mediumint、int、bigint，分别需要8、16、24、32、64。
值域范围：-2^(n-1)~ 2^(n-1)-1
很多程序员在设计数据表的时候很习惯的用int，压根不考虑这个问题
笔者建议：能用tinyint的绝不用smallint
误区：int(1) 和int(11)是一样的，唯一区别是mysql客户端显示的时候显示多少位。
整形优先原则：能用整形的不用其他类型替换，如ip可以转换成整形保存，如商品价格‘50.00元’则保存成50
(5)精确度与空间的转换。在存储相同数值范围的数据时，浮点数类型通常都会比DECIMAL类型使用更少的空间。FLOAT字段使用4字节存储
数据。DOUBLE类型需要8 个字节并拥有更高的精确度和更大的数值范围，DECIMAL类型的数据将会转换成DOUBLE类型。
2、 sql 语句优化
mysql> create table one (
id smallint(10) not null auto_increment primary key,
username char(8) not null,
password char(4) not null,
`level` tinyint (1) default 0,
last_login char(15) not null,
index(username,password,last_login))engine=innodb;
这是test表，其中id是主键，多列索引(username,password,last_login),里面有10000多条数据.
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
| one | 0 | PRIMARY | 1 | id | A |20242 | NULL | NULL | |
BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
| one | 1 | username | 1 | username | A |10121 | NULL | NULL | |
BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
| one | 1 | username | 2 | password | A |10121 | NULL | NULL | YES |
BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
| one | 1 | username | 3 | last_login | A |20242 | NULL | NULL | |
BTREE | |
------- ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------
(1) 最左前缀原则
定义：最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。
举例说明：上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如(username,password,last_login)、(username,password)、(username)才能用到索引，如下面几个顺序(password,last_login)、(passwrod)、(last_login)---这三者不从username开始，(username,last_login)---断层，少了password，都无法利用到索引。
因为B tree多列索引保存的顺序是按照索引创建的顺序，检索索引时按照此顺序检索
测试：以下测试不精确，这里只是说明如何才能正确按照最左前缀原则使用索引。还有的是以下的测试用的时间0.00sec看不出什么时间区别，因为数据量只有20003条，加上没有在实体机上运行，很多未可预知的影响因素都没考虑进去。当在大数据量，高并发的时候，最左前缀原则对与提高性能方面是不可否认的。
Ps：最左前缀原则中where字句有or出现还是会遍历全表
(1.1) 能正确的利用索引
lWhere子句表达式 顺序是(username)
mysql> explain select * from one where username='abgvwfnt';
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
1 row in set (0.00 sec)
lWhere子句表达式 顺序是(username,password)
mysql> explain select * from one where username='abgvwfnt' and password='123456';
---- ------------- ------- ------ --------------- ---------- --------- ------------- ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------- ------ --------------- ---------- --------- ------------- ------ -------------
| 1 | SIMPLE | one | ref | username | username | 43 | const,const | 1 | Using where |
---- ------------- ------- ------ --------------- ---------- --------- ------------- ------ -------------
1 row in set (0.00 sec)
lWhere子句表达式 顺序是(username,password, last_login)
mysql> explain select * from one where username='abgvwfnt' and password='123456'and last_login='1338251170';
---- ------------- ------- ------ --------------- ---------- --------- ------------------- ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra |
---- ------------- ------- ------ --------------- ---------- --------- ------------------- ------ -------------
| 1 | SIMPLE | one | ref | username | username | 83 | const,const,const | 1 | Using where |
---- ------------- ------- ------ --------------- ---------- --------- ------------------- ------ -------------
1 row in set (0.00 sec)
上面可以看出type=ref 是多列索引，key_len分别是24、43、83，这说明用到的索引分别是(username), (username,password), (username,password, last_login );row分别是5、1、1检索的数据行都很少，因为这三个查询都按照索引前缀原则，可以利用到索引。
(1.2) 不能正确的利用索引
lWhere子句表达式 顺序是(password, last_login)
mysql> explain select * from one where password='123456'and last_login='1338251170';
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 20146 | Using where |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
1 row in set (0.00 sec)
lWhere 子句表达式顺序是(last_login)
mysql> explain select * from one where last_login='1338252525';
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 20146 | Using where |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
1 row in set (0.00 sec)
以上的两条语句都不是以username开始，这样是用不了索引，通过type=all(全表扫描)，key_len=null，rows都很大20146
Ps：one表里只有20003条数据，为什么出现20146，这是优化器对表的一个估算值，不精确的。
lWhere 子句表达式虽然顺序是(username,password, last_login)或(username,password)但第一个是有范围’<’、’>’，’<=’，’>=’等出现
mysql> explain select * from one where username>'abgvwfnt' and password ='123456'and last_login='1338251170';
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
| 1 | SIMPLE | one | ALL | username | NULL | NULL | NULL | 20146 | Using where |
---- ------------- ------- ------ --------------- ------ --------- ------ ------- -------------
1 row in set (0.00 sec)
这个查询很明显是遍历所有表，一个索引都没用到，非第一列出现范围(password列或last_login列)，则能利用索引到首先出现范围的一列，也就是“where username='abgvwfnt' and password >'123456'and last_login='1338251170';”或则“where username='abgvwfnt' and password >'123456'and last_login<'1338251170';”索引长度ref_len=43,索引检索到password列，所以考虑多列索引的时候把那些查询语句用的比较的列放在最后(或非第一位)。
l断层，即是where顺序(username, last_login)
mysql> explain select * from one where username='abgvwfnt' and last_login='1338252525';
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |
---- ------------- ------- ------ --------------- ---------- --------- ------- ------ -------------
1 row in set (0.00 sec)
注意这里的key_len=24=8*3(8是username的长度，3是utf8编码)，rows=5，和下面一条sql语句搜索出来一样
mysql> select * from one where username='abgvwfnt';
------- ---------- ---------- ------- ------------
| id | username | password | level | last_login |
------- ---------- ---------- ------- ------------
| 3597 | abgvwfnt | 234567 | 0 | 1338251420 |
| 7693 | abgvwfnt | 456789 | 0 | 1338251717 |
| 11789 | abgvwfnt | 456789 | 0 | 1338251992 |
| 15885 | abgvwfnt | 456789 | 0 | 1338252258 |
| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |
------- ---------- ---------- ------- ------------
5 rows in set (0.00 sec)
mysql> select * from one where username='abgvwfnt' and last_login='1338252525';
------- ---------- ---------- ------- ------------
| id | username | password | level | last_login |
------- ---------- ---------- ------- ------------
| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |
------- ---------- ---------- ------- ------------
1 row in set (0.00 sec)
这个就是要的返回结果，所以可以知道断层(username,last_login)，这样只用到username索引，把用到索引的数据再重新检查last_login条件，这个相对全表查询来说还是有性能上优化，这也是很多sql优化文章中提到的where 范围查询要放在最后(这不绝对，但可以利用一部分索引)
(1.3) 如果一个查询 where 子句中确实不需要 password 列，那就用“补洞”。
mysql> select distinct(password) from one;
----------
| password |
----------
| 234567 |
| 345678 |
| 456789 |
| 123456 |
----------
4 rows in set (0.08 sec)
可以看出password列中只有这几个值，当然在现实中不可能密码有这么多一样的，再说数据也可能不断更新，这里只是举例说明补洞的方法
mysql> explain select * from one where username='abgvwfnt' and password in('123456','234567','345678','456789')
and last_login='1338251170';
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
| 1 | SIMPLE | one | range | username | username| 83 | NULL |4 | Using where |
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
1 row in set (0.00 sec)
可以看出ref=83 所有的索引都用到了，type=range是因为用了in子句。
这个被“补洞”列中的值应该是有限的，可预知的，如性别，其值只有男和女(加多一个不男不女也无妨)。
“补洞”方法也有瓶颈，当很多列，且需要补洞的相应列(可以多列)的值虽有限但很多(如中国城市)的时候，优化器在优化时组合起来的数量是很大，这样的话就要做好基准测试和性能分析，权衡得失，取得一个合理的优化方法。
(1.4)like
mysql> explain select * from one where username like 'abgvwfnt%';
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
| 1 | SIMPLE | one | range | username | username | 24 | NULL |
5 | Using where |
---- ------------- ------- ------- --------------- ---------- --------- ------ ------ -------------
1 row in set (0.00 sec)
mysql> explain select * from one where username like '
作者：管理员
推荐内容 Recommended
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19
相关内容 Related
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19