索引
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。 若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。
索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。
一、索引的原理
- 索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。
- 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件。
二、索引的数据结构
每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。B+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。
1、B+树性质
- 索引字段要尽量的小:如果数据项占的空间越小，数据项的数量越多，树的高度越低。比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
- 索引的最左匹配特性:当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
三、 MySQL索引管理
1、功能
- 索引的功能就是加速查找
2、MySQL常用的索引
- 普通索引：仅加速查询
- 唯一索引UNIQUE：加速查询 + 列值唯一（可以有null）
- 主键索引PRIMARY KEY：加速查询 + 列值唯一 + 表中只有一个（不可以有null）
- 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
- PRIMARY KEY(id,name):联合主键索引
- UNIQUE(id,name):联合唯一索引
- INDEX(id,name):联合普通索引
- 全文索引：对文本的内容进行分词，进行搜索
索引合并，使用多个单列索引组合搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
- 查看表结构 desc 表名 - 查看生成表的SQL show create table 表名 - 查看索引 show index from 表名 - 查看执行时间 set profiling = 1; SQL... show profiles;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
举个例子来说，比如你在为某商场做一个会员卡的系统。这个系统有一个会员表有下列字段：会员编号 INT会员姓名 VARCHAR(10)会员身份证号码 VARCHAR(18)会员电话 VARCHAR(10)会员住址 VARCHAR(50)会员备注信息 TEXT那么这个 会员编号，作为主键，使用 PRIMARY会员姓名 如果要建索引的话，那么就是普通的 INDEX会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）#除此之外还有全文索引，即FULLTEXT会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。#其他的如空间索引SPATIAL，了解即可，几乎不用 |
3、索引的两大类型hash与btree
|
1
2
3
4
5
6
7
8
9
10
|
#我们可以在创建上述索引的时候，为其指定索引类型，分两类hash类型的索引：查询单条快，范围查询慢btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）#不同的存储引擎支持的索引类型也不一样InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引； |
4、创建/删除索引的语法
【创建索引】 --创建表时 --语法： CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); -------------------------------- --创建普通索引示例： CREATE TABLE emp1 ( id INT, name VARCHAR(30) , resume VARCHAR(50), INDEX index_emp_name (name) --KEY index_dept_name (dept_name) ); --创建唯一索引示例： CREATE TABLE emp2 ( id INT, name VARCHAR(30) , bank_num CHAR(18) UNIQUE , resume VARCHAR(50), UNIQUE INDEX index_emp_name (name) ); --创建全文索引示例： CREATE TABLE emp3 ( id INT, name VARCHAR(30) , resume VARCHAR(50), FULLTEXT INDEX index_resume (resume) ); --创建多列索引示例： CREATE TABLE emp4 ( id INT, name VARCHAR(30) , resume VARCHAR(50), INDEX index_name_resume (name,resume) ); --------------------------------- 【添加和删除索引】 ---添加索引 ---CREATE在已存在的表上创建索引 CREATE [UNIQUE] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; ---ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; CREATE INDEX index_emp_name on emp1(name); ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num); -- 删除索引 语法：DROP INDEX 索引名 on 表名 DROP INDEX index_emp_name on emp1; DROP INDEX bank_num on emp2;
# 方法一：创建表时 CREATE TABLE 表名( 字段名1 数据类型[完整性约束条件…], 字段名2 数据类型[完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名](字段名[(长度)][ASC | DESC]) ); # 方法二：CREATE在已存在的表上创建索引 CREATE[UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名[(长度)][ASC | DESC]); # 方法三：ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD[UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名(字段名[(长度)][ASC | DESC]); # 删除索引：DROP INDEX 索引名 ON 表名字; #方式一 create table t1( id int, name char, age int, sex enum('male','female'), unique key uni_id(id), index ix_name(name) #index没有key ); #方式二 create index ix_age on t1(age); #方式三 alter table t1 add index ix_sex(sex); #查看 mysql> show create table t1; | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` char(1) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` enum('male','female') DEFAULT NULL, UNIQUE KEY `uni_id` (`id`), KEY `ix_name` (`name`), KEY `ix_age` (`age`), KEY `ix_sex` (`sex`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
5、总结
- 1、一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引
- 2、在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
- 3、需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI
MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。 因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.
四、正确使用索引
并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须注意以下问题
1、使用索引和不使用索引
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会快到飞起来。# 有索引mysql> select * from tb1 where name = 'wupeiqi-888';+-----+-------------+---------------------+----------------------------------+---------------------+| nid | name | email | radom | ctime |+-----+-------------+---------------------+----------------------------------+---------------------+| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |+-----+-------------+---------------------+----------------------------------+---------------------+1 row in set (0.00 sec)# 无索引mysql> select * from tb1 where email = 'wupeiqi888@live.com';+-----+-------------+---------------------+----------------------------------+---------------------+| nid | name | email | radom | ctime |+-----+-------------+---------------------+----------------------------------+---------------------+| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |+-----+-------------+---------------------+----------------------------------+---------------------+1 row in set (1.23 sec) |
2、索引未命中
|
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
|
- 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)、范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、
- 2)、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
- 3)、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
- 4)、索引列不能参与计算，保持列“干净”。比如为id加了索引，但是查的时候where id*3+10 ,索引字段参与了计算，无法拿到一个明确的值去索引树中查找，每次都得临时计算一下。
- 5)、and/or 在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），会依次往右找到一个区分度高的索引字段，加速查询
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
#1、and与or的逻辑 条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立 条件1 or 条件2:只要有一个条件成立则最终结果就成立#2、and的工作原理 条件： a = 10 and b = 'xxx' and c > 3 and d =4 索引： 制作联合索引(d,a,b,c) 工作原理: 对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序#3、or的工作原理 条件： a = 10 or b = 'xxx' or c > 3 or d =4 索引： 制作联合索引(d,a,b,c) 工作原理: 对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d |
- 6）、最左前缀匹配原则（详见第八小节），非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- 7）、其他情况
|
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
|
- 使用函数 select * from tb1 where reverse(email) = 'egon'; - 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where email = 999; #排序条件为索引，则select字段必须也是索引字段，否则无法命中- order by select name from s1 order by email desc; 当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢 select email from s1 order by email desc; 特别的：如果对主键排序，则还是速度很快： select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为：(name,email) name and email -- 命中索引 name -- 命中索引 email -- 未命中索引- count(1)或count(列)代替count(*)在mysql中没有差别了- create index xxxx on tb(title(19)) #text类型，必须制定长度 |
3、其他注意事项
- - 避免使用select *
- - count(1)或count(列) 代替 count(*)
- - 创建表时尽量时 char 代替 varchar
- - 表的字段顺序固定长度的字段优先
- - 组合索引代替多个单列索引（经常使用多个条件查询时）
- - 尽量使用短索引
- - 使用连接（JOIN）来代替子查询(Sub-Queries)
- - 连表时注意条件类型需一致
- - 索引散列值（重复少）不适合建索引，例：性别不适合
五、联合索引
联合索引时指对表上的多个列合起来做一个索引。
- 创建方法:key idx_a_b(a,b)
- 联合索引的好处是在第一个键相同的情况下，已经对第二个键进行了排序处理
|
1
2
3
4
5
6
7
8
9
|
#对于联合索引（a,b）,下述语句可以直接使用该索引，无需二次排序select ... from table where a=xxx order by b;#然后对于联合索引(a,b,c)来首，下列语句同样可以直接通过索引得到结果select ... from table where a=xxx order by b;select ... from table where a=xxx and b=xxx order by c;#但是对于联合索引(a,b,c)，下列语句不能通过索引直接得到结果，还需要自己执行一次filesort操作，因为索引（a，c)并未排序select ... from table where a=xxx order by c; |
六、慢查询优化的基本步骤
- - 0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
- - 1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
- - 2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
- - 3.order by limit 形式的sql语句让排序的表优先查
- - 4.了解业务方使用场景
- - 5.加索引时参照建索引的几大原则
- - 6.观察结果，不符合预期继续从0分析
七、执行计划
explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化
|
1
2
3
4
5
6
7
|
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/const ALL 全表扫描，对于数据表从头到尾找一遍 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 and in > >= < <= 操作 注意：!= 和 > 符号 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列中索引的位图，并且是冗余的。 详细
更多参见：
http://www.cnblogs.com/xiaoboluo768/p/5400990.html
http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_system
八、limit分页
每页显示10条： 当前 118 120， 125 倒序： 大 小 980 970 7 6 6 5 54 43 32 21 19 98 下一页： select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 970 order by nid desc limit 40) A order by A.nid asc limit 1) order by nid desc limit 10; 上一页： select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1) order by nid desc limit 10;
9、慢日志查询
a、配置MySQL自动记录慢日志
slow_query_log = OFF 是否开启慢日志记录
long_query_time = 2 时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log 日志文件
log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
注：查看当前配置信息：
show variables like '%query%'
修改当前配置：
set global 变量名 = 值
b、查看MySQL慢日志
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
14
15
16
17
18
19
20
21
22
23
24
25
26
|
"""--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""" |