MySQL索引管理及执行计划

terte

MySQL索引管理及执行计划
第1章 索引介绍:

索引是对数据库表中一列或者多了的值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息,如果想按特定职员的姓名来查找,则与他在表中搜索所有的行相比,索引有助于更快的获取信息

索引的一个主要目的就是加快检索表中的数据的方法,既能协助信息搜索者尽快找到符合限制条件的记录ID的辅助数据结构

1.1 索引的类型介绍:btree:B+树索引    最为常用
hash:hash索引
fulltest:全文索引
rtree:r数索引
第2章 索引管理2.1 B树索引的分类:

主键索引:

数据库表经常有一列或多列组合,其值唯一标识表中的每一行,该列称为表的主键,在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型

唯一索引:

是不允许期中任何两行具有相同索引值的索引,当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存,数据库还可能防止添加将在表中创建重复键值的新数据

普通索引:

2.2 mysql中的约束索引:2.2.1 主键索引:只能有一个主键主键索引,列的内容是唯一值,高中学号
表创建的时候至少要有一个主键索引,最好和业务无关
2.2.2 普通索引:加快查询速度,工作中优化数据库的关键
在合适的列上建立索引,让数据查询更加高效
2.2.3 唯一索引:内容唯一,但不是关键
2.3 添加索引:

1 2 3

mysql> alter table people add index index_name(id); mysql> alter table stu add index ind_mul(id); mysql> create index inx on stu(gender);

2.4 删除索引:

1	mysql> alter table stu drop index ind_mul;

2.5 查询索引:

1 2	mysql> desc people; mysql> show index from people;

2.6 主键索引的设置:

1.      建表时就设置

2.      建表没有指定,后期修改

2.6.1 唯一键索引的创建:

1	mysql> alter table stu add unique key ind(name);

2.6.2 联合索引的创建:

1	mysql> alter table stu add index ind_id_name(id,name);

联合索引的特点:a;ab;abc   可以走索引
               b;ac;bc;c  不走索引
原则是把最常用来作为条件查询的列放在前面
2.6.3 前缀索引的创建:

1 2	create index ind_name on test(name(8)); alter table test add index ind_name(name(8));

2.6.4 什么情况下推荐创建索引呢?

1	where   order by   group by   join  on

2.7 索引的企业应用场景:2.7.1 企业优化思路:

1.      把一个大的不使用索引的sql语句安好功能进行拆分

2.      长的sql语句无法使用索引,能不能变成两条端的sql语句,让它分别使用上索引

3.      对sql语句功能的拆分和修改

4.      减少烂sql

5.      由运维和开发交流确认,共同确定如何改,最终由dba执行

6.      制定开发流程

2.7.2 不适合建立索引的场景:

1.      唯一值少的裂伤不适合建立索引或者建立索引会导致效率低,例如性别列

2.      小表可以不创建索引,100条记录

3.      对于数据仓库,大量全表扫描的情况,建立索引范围会慢

第3章 执行计划获取及分析3.1 explain   调取执行计划查看是否走了索引:

1 2 3 4 5 6 7 8 9 10 11 12

mysql> explain select name from stu; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | stu   | index | NULL          | ind  | 63      | NULL |    2 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ mysql> explain select id from stu where id=1; +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       | +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ |  1 | SIMPLE      | stu   | ref  | ind_mul       | ind_mul | 5       | const |    1 | Using index | +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

all和index的类型,我们通常认为,索引的创建不够合理,起码达到range级别以上
3.2 type   访问类型的种类:3.2.1 ALL:

mysql将遍历全表以扎到匹配的行

3.2.2 index:

索引范围扫描,index与ALL类型之遍历索引树

3.2.3 range:

索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,这种索引范围扫描是带有between或者where子句里带有<>查询,

3.2.4 ref:

使用非唯一索引扫描或者唯一索引的前缀扫描,返回匹配某个单独值的记录行

3.2.5 eq_ref:

类似ref区别就在于使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者unique key 作为关联条件

3.2.6 const/system:

当mysql对查询某部分进行优化,并转换为一个常亮时,使用这些类似访问,如将主键置于where列表中,mysql就能将该查询转换为一个常量

3.2.7 null:

mysql在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列表里选取最小值可以通过单独索引查找完成

type类型从上到下,性能由差到好
3.3 查看表中唯一值的数量:

1 2 3 4 5 6 7

mysql> select count(distinct user,host) from mysql.user; +---------------------------+ | count(distinct user,host) | +---------------------------+ |                         5 | +---------------------------+ 1 row in set (0.00 sec)

第4章 数据库索引设计的原则:为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引
4.1 索引设计重要原则:4.1.1 选择唯一性索引

唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录

例如,学生表中的学生号是具有唯一性的字段,为该字段建立唯一性索引可以很快的确定某个学生的信息,如果使用姓名的话,又可能会存在同名的现象,从而降低查询速度

主键索引和唯一键索引,在查询中是效率最高的

4.1.2 为经常需要排序,分组和联合操作的字段建立索引

经常需要order by  group by distinct和untion等操作的字段,排序操作会浪费很多时间,如果为其建立索引,可以有效的避免排序操作

4.1.3 为经常查询条件的字段建立索引

如果某个字段上经常用来做查询条件,那么该字段的查询速度回影响整个表的查询速度,因此,为这样的字段建立索引,可以提高整个表的查询速度

4.1.4 尽量使用前缀来索引

如果索引字段的值很长,最好使用值的前缀来索引,例如:test和blog类型的字段,进行全文检索会很浪费时间,如果只检索字段的前面的若干个字符,这样可以提高检索速度

4.1.5 限制索引的数目

索引的数目并不是越多越好,每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大,修改表时,对索引的重构和更新很麻烦,越多的索引,会使更新表变得很浪费时间

4.1.6 尽量使用数据量少的索引

如果索引的值很长,那么查询的速度必然会受到影响,例如:对弈char(100)类型的字段进行全文检索需要的时间肯定要比对char(10)类型的字段需要的时间更多

4.1.7 删除不在使用或者很少使用的索引

表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不在需要,数据库管理员应当定期找出这些索引,并删除,从而减少索引对更新操作的影响

4.2 索引开发的规范:不走索引的情况:
4.2.1 没有查询条件,或者查询条件没有建立索引

在业务数据库中,数据量大的是没有必要进行全表扫描的,一定程度上讲,全表扫描对用户体验就非常痛苦的,同时对服务器也是毁灭性的;又或者索引建立的但是查询条件没有where等条件

4.2.2 查询结果集是原表中的大部分数据,达到了30%以上查询结果集超过了总行数的30%,就没有必要走索引了

1 2	例如 :select * from test where id>500000;

如果业务允许的情况下,可以加上limit进行控制,如果因为业务的原因没有办法改写方案,就可以放到redis中
4.2.3 索引本身失效,统计数据不真实索引有自我维护的能力,对于表的变化内容比较频繁的情况下,可能会出现索引失效
4.2.4 查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+ - * /等)尽量不要在查询条件上进行运算,算好了在假如查询条件中
4.2.5 隐式转换导致索引失效,这一点应当引起重视,也是开发中经常遇到的错误由于表的字段name定义为varchar(20),但是在查询时把该字段作为number类型以where条件传给数据库,这样会导致索引失效
例如:在查询name的字段信息时,因为是字符串的类型,where就需要加上单引号,但是sql语句忘记加上,就会导致索引失效

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 55 56 57 58 59 60 61 62 63 64

select * from test where tu_mdn=13333333333; 正确的写法: select * from test where tu_mdn=’13333333333’; 案例演示: mysql> alter table tab add index inx_tel(telnum); Query OK, 0 rows affected (0.03 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> desc tab; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id     | int(11)     | YES  |     | NULL    |       | | name   | varchar(20) | YES  |     | NULL    |       | | telnum | varchar(20) | YES  | MUL | NULL    |       | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> select * from tab where telnum='1333333'; +------+------+---------+ | id   | name | telnum  | +------+------+---------+ |    1 | a    | 1333333 | +------+------+---------+ 1 row in set (0.00 sec) mysql> select * from tab where telnum=1333333; +------+------+---------+ | id   | name | telnum  | +------+------+---------+ |    1 | a    | 1333333 | +------+------+---------+ 1 row in set (0.00 sec) mysql> explain  select * from tab where telnum='1333333'; +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ |  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql> explain  select * from tab where telnum=1333333; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain  select * from tab where telnum=1555555; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain  select * from tab where telnum='1555555'; +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ |  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec)

4.2.6 <>,not in 不走索引

<> 的作用是不等于

1 2 3 4 5 6 7 8 9 10 11

mysql> select * from stu; +--------+------+------+------+------+---------+--------+------+-------+ | stu_id | id   | name | QQ   | age  | tel_num | gender | addr | state | +--------+------+------+------+------+---------+--------+------+-------+ |   NULL |    7 | NULL | NULL | NULL |    NULL | NULL   | NULL |     1 | |   NULL | NULL | xiao | NULL | NULL |    NULL | NULL   | NULL |     1 | +--------+------+------+------+------+---------+--------+------+-------+ 2 rows in set (0.00 sec) mysql> select * from stu where id <> '7'; Empty set (0.00 sec)

单独的>,<,in 有可能走,也有可能不走,和结果集有关,尽量结合业务添加limit
or或者in  尽量改写成union

1	EXPLAIN  SELECT * FROM teltab WHERE telnum   IN ('110','119');

改写成：

1 2 3

EXPLAIN SELECT * FROM teltab WHERE telnum='110' UNION ALL SELECT * FROM teltab WHERE telnum='119'

4.2.7  like “%_” 百分号在前面,不走索引

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> explain select * from stu where name like '%xiao'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | stu   | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from stu where name like 'xiao%'; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ |  1 | SIMPLE      | stu   | range | ind           | ind  | 63      | NULL |    1 | Using index condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)

带%号一类的搜索请求,可以使用elasticsearch
4.2.8 单独引用联合索引里非第一位置的索引列联合索引案例:

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT); ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex); DESC t1 SHOW INDEX FROM t1 (a,b,c) a   ab  abc ac bc b  c -----------------------------

走索引:

1 2 3

EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m'; EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  ; EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m';    ----部分走索引

不走索引:

1 2 3

EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE  age=20 EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE  age=30  AND sex='m'; EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE   sex='m';

4.2.9 blob和text类型的列只能创建前缀索引4.2.10 mysql目前不支持函数索引4.2.11 join语句中join条件字段类型不一致的时候mysql无法使用索引