普通索引,没有索引就是遍历操作速度很慢

  索引的出力就是封锁规范加加快查找的效果,没有索引就是遍历操作速度很慢。

  索引的效益就是束缚原则加加速查找的法力,没有索引就是遍历操作速度很慢。

  索引的品类:

  索引的类型:

  单列索引:

  单列索引:

  主键索引:加速查找 + 不可以为空 + 无法再度。

  主键索引:加速查找 + 不可以为空 + 不只怕再一次。

创建表时创建主键索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

创建主键
alter table 表名 add primary key(列名);
删除主键
alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;
创建表时创建主键索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

创建主键
alter table 表名 add primary key(列名);
删除主键
alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

 

 

  普通索引:加快查找。

  普通索引:加快查找。

图片 1图片 2

图片 3图片 4

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

创设表和目录

创制表和目录

只创建索引
create index index_name on table_name(column_name);

删除索引
drop index_name on table_name;

查看索引
show index from table_name;

对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。
create index ix_extra on in1(extra(32));
只创建索引
create index index_name on table_name(column_name);

删除索引
drop index_name on table_name;

查看索引
show index from table_name;

对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。
create index ix_extra on in1(extra(32));

 

 

  唯一索引:加速查找 + 无法重新(包蕴null)。

  唯一索引:加快查找 + 不能够重新(包涵null)。

创建表时创建唯一索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

只创建唯一索引
create unique index 索引名 on 表名(列名)

删除唯一索引
drop unique index 索引名 on 表名
创建表时创建唯一索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

只创建唯一索引
create unique index 索引名 on 表名(列名)

删除唯一索引
drop unique index 索引名 on 表名

 

 

  多列组成二个索引:

  多列组成多少个索引:

  联合索引(多列):联合主键索引、联合唯一索引、联合普通索引。

  联合索引(多列):联合主键索引、联合唯一索引、联合普通索引。

  注:创造索引时会生成二个有关那几个索引值相关的贰个某种格式存储的公文。制造索引的时候会遍历整个表很慢,创制好那一个文件从此,下一回搜索先从这些表里找,找到的话速度就快快,不在这几个文件里依然会遍历整个数据库。

  注:创立索引时会生成贰个关于那么些索引值相关的二个某种格式存储的公文。成立索引的时候会遍历整个表很慢,创立好那个文件从此,下一次搜索先从那些表里找,找到的话速度就快快,不在那么些文件里依旧会遍历整个数据库。

  MySQL里索引项目(某种格式存储):

  MySQL里索引项目(某种格式存储):

  hash索引表:
把你作为目录的键写成hash值,然后把相应的内存地址绑定,hash表中的顺序时乱序的(id>3就很慢),单值快。

  hash索引表:
把您作为目录的键写成hash值,然后把相应的内存地址绑定,hash表中的顺序时乱序的(id>3就很慢),单值快。

  btree索引:二叉树法,查找速度飞快,使用的更广。

  btree索引:二叉树法,查找速度高速,使用的更广。

  建立目录,额外的文本保留特殊的数据结构,查询快(前提要命中询问)插入更新删除慢。

  建立目录,额外的文件保留特殊的数据结构,查询快(前提要命中询问)插入更新删除慢。

select * from userinfo3 where email like 'asdf';--依然很慢。
select * from userinfo3 where email like 'asdf';--依然很慢。

   索引中的多少个名词:

   索引中的多少个名词:

  覆盖索引:查找的列就是大家十三分存储的至极格式的文书里的列,信息方可一贯在目录文件查到而不需求到表里查,比如id时索引,倘使select id直接就在目录表取到id数据,假若要查*那么还要去数据表中查整体。

  覆盖索引:查找的列就是我们格外存储的超常规格式的公文里的列,音信可以直接在目录文件查到而不要求到表里查,比如id时索引,如若select id直接就在目录表取到id数据,即使要查*那么还要去数据表中查全体。

  索引合并:多列都是索引时寻找,把多个单列索引合并使用。

  索引合并:多列都以索引时寻找,把八个单列索引合并使用。

  最左前缀匹配:

  最左前缀匹配:

create index ix_name_email on userinfo3(name,email,)--两个索引
select  * from userinfo3 where name='alex';
select  * from userinfo3 where name='alex' and email='asdf';
select  * from userinfo3 where email='alex@qq.com';#不走索引

--适用于组合索引
create index ix_name_email on userinfo3(name,email,)--两个索引
select  * from userinfo3 where name='alex';
select  * from userinfo3 where name='alex' and email='asdf';
select  * from userinfo3 where email='alex@qq.com';#不走索引

--适用于组合索引

  组合索引的效能要当先索引合并。

  组合索引的效能要大于索引合并。

图片 5图片 6

图片 7图片 8

- like '%xx'
    select * from tb1 where email like '%cn';


- 使用函数
    select * from tb1 where reverse(email) = 'wupeiqi';


- or后面有非索引也不会命中
    select * from tb1 where nid = 1 or name = 'seven@live.com';


    特别的:当or条件中有未建立索引的列才失效,以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'走索引


- 类型不一致
    如果列是字符串类型,传入条件是必须用引号引起来,不然会很慢
    select * from tb1 where email = 999;


- !=
    select * from tb1 where email != 'alex'

    特别的:如果是主键,则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where email > 'alex'


    特别的:如果是主键或索引是整数类型,则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123


- order by
    select name from tb1 order by email desc;

    当根据索引排序时候,选择的映射如果不是索引,则不走索引
    特别的:如果对主键排序,则还是走索引:
        select * from tb1 order by nid desc;

- 组合索引最左前缀
    如果组合索引为:(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引
- like '%xx'
    select * from tb1 where email like '%cn';


- 使用函数
    select * from tb1 where reverse(email) = 'wupeiqi';


- or后面有非索引也不会命中
    select * from tb1 where nid = 1 or name = 'seven@live.com';


    特别的:当or条件中有未建立索引的列才失效,以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'走索引


- 类型不一致
    如果列是字符串类型,传入条件是必须用引号引起来,不然会很慢
    select * from tb1 where email = 999;


- !=
    select * from tb1 where email != 'alex'

    特别的:如果是主键,则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where email > 'alex'


    特别的:如果是主键或索引是整数类型,则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123


- order by
    select name from tb1 order by email desc;

    当根据索引排序时候,选择的映射如果不是索引,则不走索引
    特别的:如果对主键排序,则还是走索引:
        select * from tb1 order by nid desc;

- 组合索引最左前缀
    如果组合索引为:(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

心中无数索引的景况

没辙索引的情状

图片 9图片 10

图片 11图片 12

 避免使用select *
 count(1)或count(列) 代替 count(*)
 创建表时尽量时 char 代替 varchar
 表的字段顺序固定长度的字段优先
 组合索引代替多个单列索引(经常使用多个条件查询时)
 尽量使用短索引
 使用连接(JOIN)来代替子查询(Sub-Queries)
 连表时注意条件类型需一致
 索引散列值(重复少)不适合建索引,例:性别不适合
 避免使用select *
 count(1)或count(列) 代替 count(*)
 创建表时尽量时 char 代替 varchar
 表的字段顺序固定长度的字段优先
 组合索引代替多个单列索引(经常使用多个条件查询时)
 尽量使用短索引
 使用连接(JOIN)来代替子查询(Sub-Queries)
 连表时注意条件类型需一致
 索引散列值(重复少)不适合建索引,例:性别不适合

目录注意事项

目录注意事项

  通过EXPLAIN可以预估SQL的实施时间:

  通过EXPLAIN可以预估SQL的举行时间:

mysql> explain select * from t1 where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t1 where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

  功能排行all (全表扫描)< index < range < index_merge <
ref_or_null < ref < eq_ref <
system/const(索引),仅供查询,不肯定准,大部分是那般。

  功效名次all (全表扫描)< index < range < index_merge <
ref_or_null < ref < eq_ref <
system/const(索引),仅供查询,不必然准,大多数是如此。

  对于慢日志的查询:

  对于慢日志的查询:

show variables like '%query%';--基于内存
show variables like '%query%';--基于内存

图片 13图片 14

图片 15图片 16

slow_query_log = OFF                            是否开启慢日志记录
long_query_time = 2                              时间限制,超过此时间,则记录
slow_query_log_file = /usr/slow.log        日志文件
log_queries_not_using_indexes = OFF     为使用索引的搜索是否记录
slow_query_log = OFF                            是否开启慢日志记录
long_query_time = 2                              时间限制,超过此时间,则记录
slow_query_log_file = /usr/slow.log        日志文件
log_queries_not_using_indexes = OFF     为使用索引的搜索是否记录

各字段意思

各字段意思

图片 17图片 18

图片 19图片 20

set global 变量名 = 值
set global 变量名 = 值

修改配置字段

修改配置字段

  还足以找到配置文件一直改动。

  还足以找到配置文件直接修改。

优化分页

  对江小鱼常的select * from userinfo3 limit
20,10;那种分页,当页数很多时功能是卓殊低的,所以一言以蔽之二种缓解方案。

  1.只好翻上下页。

【下一页】:select * from table_name where id > max_id limit 10;
【上一页】:select * from table_name where id < min_id order by id desc limit 10;

图片 21图片 22

下一页:
mysql> select * from t1 where id >0 limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
|  1 | test1  | test1@qq.com  |    1 |    2 |
|  2 | test2  | test2@qq.com  |    1 |    2 |
|  3 | test3  | test3@qq.com  |    1 |    2 |
|  4 | test4  | test4@qq.com  |    1 |    2 |
|  5 | test5  | test5@qq.com  |    1 |    2 |
|  6 | test6  | test6@qq.com  |    1 |    2 |
|  7 | test7  | test7@qq.com  |    1 |    2 |
|  8 | test8  | test8@qq.com  |    1 |    2 |
|  9 | test9  | test9@qq.com  |    1 |    2 |
| 10 | test10 | test10@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

mysql> select * from t1 where id >10 limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 11 | test11 | test11@qq.com |    1 |    2 |
| 12 | test12 | test12@qq.com |    1 |    2 |
| 13 | test13 | test13@qq.com |    1 |    2 |
| 14 | test14 | test14@qq.com |    1 |    2 |
| 15 | test15 | test15@qq.com |    1 |    2 |
| 16 | test16 | test16@qq.com |    1 |    2 |
| 17 | test17 | test17@qq.com |    1 |    2 |
| 18 | test18 | test18@qq.com |    1 |    2 |
| 19 | test19 | test19@qq.com |    1 |    2 |
| 20 | test20 | test20@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

mysql> select * from t1 where id >30 limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 31 | test31 | test31@qq.com |    1 |    2 |
| 32 | test32 | test32@qq.com |    1 |    2 |
| 33 | test33 | test33@qq.com |    1 |    2 |
| 34 | test34 | test34@qq.com |    1 |    2 |
| 35 | test35 | test35@qq.com |    1 |    2 |
| 36 | test36 | test36@qq.com |    1 |    2 |
| 37 | test37 | test37@qq.com |    1 |    2 |
| 38 | test38 | test38@qq.com |    1 |    2 |
| 39 | test39 | test39@qq.com |    1 |    2 |
| 40 | test40 | test40@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

上一页:
mysql> select * from t1 where id < 41 order by id desc limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 40 | test40 | test40@qq.com |    1 |    2 |
| 39 | test39 | test39@qq.com |    1 |    2 |
| 38 | test38 | test38@qq.com |    1 |    2 |
| 37 | test37 | test37@qq.com |    1 |    2 |
| 36 | test36 | test36@qq.com |    1 |    2 |
| 35 | test35 | test35@qq.com |    1 |    2 |
| 34 | test34 | test34@qq.com |    1 |    2 |
| 33 | test33 | test33@qq.com |    1 |    2 |
| 32 | test32 | test32@qq.com |    1 |    2 |
| 31 | test31 | test31@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

mysql> select * from t1 where id < 31 order by id desc limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 30 | test30 | test30@qq.com |    1 |    2 |
| 29 | test29 | test29@qq.com |    1 |    2 |
| 28 | test28 | test28@qq.com |    1 |    2 |
| 27 | test27 | test27@qq.com |    1 |    2 |
| 26 | test26 | test26@qq.com |    1 |    2 |
| 25 | test25 | test25@qq.com |    1 |    2 |
| 24 | test24 | test24@qq.com |    1 |    2 |
| 23 | test23 | test23@qq.com |    1 |    2 |
| 22 | test22 | test22@qq.com |    1 |    2 |
| 21 | test21 | test21@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

示例

  2.上一页 192 193  [196]  197  198  199 下一页,从当前页196到199页

select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10)

 

优化分页

  对于健康的select * from userinfo3 limit
20,10;这种分页,当页数很多时效用是分外低的,所以简而言之三种缓解方案。

  1.只好翻上下页。

【下一页】:select * from table_name where id > max_id limit 10;
【上一页】:select * from table_name where id < min_id order by id desc limit 10;

图片 23图片 24

下一页:
mysql> select * from t1 where id >0 limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
|  1 | test1  | test1@qq.com  |    1 |    2 |
|  2 | test2  | test2@qq.com  |    1 |    2 |
|  3 | test3  | test3@qq.com  |    1 |    2 |
|  4 | test4  | test4@qq.com  |    1 |    2 |
|  5 | test5  | test5@qq.com  |    1 |    2 |
|  6 | test6  | test6@qq.com  |    1 |    2 |
|  7 | test7  | test7@qq.com  |    1 |    2 |
|  8 | test8  | test8@qq.com  |    1 |    2 |
|  9 | test9  | test9@qq.com  |    1 |    2 |
| 10 | test10 | test10@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

mysql> select * from t1 where id >10 limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 11 | test11 | test11@qq.com |    1 |    2 |
| 12 | test12 | test12@qq.com |    1 |    2 |
| 13 | test13 | test13@qq.com |    1 |    2 |
| 14 | test14 | test14@qq.com |    1 |    2 |
| 15 | test15 | test15@qq.com |    1 |    2 |
| 16 | test16 | test16@qq.com |    1 |    2 |
| 17 | test17 | test17@qq.com |    1 |    2 |
| 18 | test18 | test18@qq.com |    1 |    2 |
| 19 | test19 | test19@qq.com |    1 |    2 |
| 20 | test20 | test20@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

mysql> select * from t1 where id >30 limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 31 | test31 | test31@qq.com |    1 |    2 |
| 32 | test32 | test32@qq.com |    1 |    2 |
| 33 | test33 | test33@qq.com |    1 |    2 |
| 34 | test34 | test34@qq.com |    1 |    2 |
| 35 | test35 | test35@qq.com |    1 |    2 |
| 36 | test36 | test36@qq.com |    1 |    2 |
| 37 | test37 | test37@qq.com |    1 |    2 |
| 38 | test38 | test38@qq.com |    1 |    2 |
| 39 | test39 | test39@qq.com |    1 |    2 |
| 40 | test40 | test40@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

上一页:
mysql> select * from t1 where id < 41 order by id desc limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 40 | test40 | test40@qq.com |    1 |    2 |
| 39 | test39 | test39@qq.com |    1 |    2 |
| 38 | test38 | test38@qq.com |    1 |    2 |
| 37 | test37 | test37@qq.com |    1 |    2 |
| 36 | test36 | test36@qq.com |    1 |    2 |
| 35 | test35 | test35@qq.com |    1 |    2 |
| 34 | test34 | test34@qq.com |    1 |    2 |
| 33 | test33 | test33@qq.com |    1 |    2 |
| 32 | test32 | test32@qq.com |    1 |    2 |
| 31 | test31 | test31@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

mysql> select * from t1 where id < 31 order by id desc limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 30 | test30 | test30@qq.com |    1 |    2 |
| 29 | test29 | test29@qq.com |    1 |    2 |
| 28 | test28 | test28@qq.com |    1 |    2 |
| 27 | test27 | test27@qq.com |    1 |    2 |
| 26 | test26 | test26@qq.com |    1 |    2 |
| 25 | test25 | test25@qq.com |    1 |    2 |
| 24 | test24 | test24@qq.com |    1 |    2 |
| 23 | test23 | test23@qq.com |    1 |    2 |
| 22 | test22 | test22@qq.com |    1 |    2 |
| 21 | test21 | test21@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

示例

  2.上一页 192 193  [196]  197  198  199 下一页,从当前页196到199页

select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10)

 

相关文章