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

  对于慢日志的查询:

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

  普通索引:加快查找。

 

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';#不走索引

--适用于组合索引

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

  单列索引:

创设表和目录

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

  普通索引:加快查找。

修改配置字段

目录注意事项

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

图片 1图片 2

  主键索引:加速查找 + 不可能为空 + 不可以重复。

set global 变量名 = 值

图片 3图片 4

  对于慢日志的询问:

目录注意事项

  单列索引:

创设表和目录

优化分页

  对于健康的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;

图片 5图片 6

下一页:
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;

图片 7图片 8

下一页:
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)

 

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

set global 变量名 = 值
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';#不走索引

--适用于组合索引

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

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

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

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

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

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

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

  多列组成一个目录:

select * from userinfo3 where email like 'asdf';--依然很慢。
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)

  索引的作用就是约束原则加加快查找的效用,没有索引就是遍历操作速度很慢。

 

  注:成立索引时会生成一个有关那一个索引值相关的一个某种格式存储的文件。成立索引的时候会遍历整个表很慢,创造好那些文件之后,下两次搜索先从那么些表里找,找到的话速度就急忙,不在那么些文件里仍旧会遍历整个数据库。

图片 9图片 10

 

心慌意乱索引的意况

  覆盖索引:查找的列就是大家充足存储的异样格式的文书里的列,音讯可以间接在目录文件查到而不要求到表里查,比如id时索引,借使select id直接就在目录表取到id数据,即使要查*那么还要去数据表中查全部。

图片 11图片 12

只创建索引
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));
slow_query_log = OFF                            是否开启慢日志记录
long_query_time = 2                              时间限制,超过此时间,则记录
slow_query_log_file = /usr/slow.log        日志文件
log_queries_not_using_indexes = OFF     为使用索引的搜索是否记录

  最左前缀匹配:

  覆盖索引:查找的列就是大家这一个存储的例外格式的文本里的列,音信可以直接在目录文件查到而不必要到表里查,比如id时索引,即使select id直接就在目录表取到id数据,倘若要查*那就是说还要去数据表中查全部。

  索引的序列:

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

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%';--基于内存

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

图片 13图片 14

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)
)

  效能排行all (全表扫描)< index < range < index_merge <
ref_or_null < ref < eq_ref <
system/const(索引),仅供查询,不必然准,一大半是这样。

创建表时创建唯一索引
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 表名
- 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                -- 不使用索引
show variables like '%query%';--基于内存

图片 15图片 16

创建表时创建主键索引
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;

  组合索引的频率要超过索引合并。

  注:成立索引时会生成一个有关这些索引值相关的一个某种格式存储的文件。成立索引的时候会遍历整个表很慢,创制好这几个文件之后,下一次搜索先从这一个表里找,找到的话速度就快快,不在这一个文件里照旧会遍历整个数据库。

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

   索引中的多少个名词:

图片 17图片 18

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

不可以索引的图景

- 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                -- 不使用索引

   索引中的多少个名词:

只创建索引
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));

 

图片 19图片 20

  多列组成一个索引:

  功用排行all (全表扫描)< index < range < index_merge <
ref_or_null < ref < eq_ref <
system/const(索引),仅供查询,不自然准,超过一半是这么。

创建表时创建主键索引
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;

  最左前缀匹配:

图片 21图片 22

  唯一索引:加快查找 + 不能够再次(包罗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 表名

修改配置字段

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)

  索引的项目:

各字段意思

 

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

图片 23图片 24

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)
)

各字段意思

 

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

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

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

相关文章