MySQL索引

  索引的法力就是束缚规范加加速查找的机能,没有索引就是遍历操作速度很慢。

  索引的系列:

  单列索引:

  主键索引:加速查找 + 无法为空 + 无法重新。

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

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

 

  唯一索引:加速查找 + 不可能重复(包含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里索引项目(某种格式存储):

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

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

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

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

   索引中的多少个名词:

  覆盖索引:查找的列就是我们那一个存储的非凡格式的文本里的列,音讯可以一直在目录文件查到而不需要到表里查,比如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';#不走索引

--适用于组合索引

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

图片 3图片 4

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

不能索引的事态

图片 5图片 6

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

目录注意事项

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

  效能排行all (全表扫描)< index < range < index_merge <
ref_or_null < ref < eq_ref <
system/const(索引),仅供查询,不自然准,大部分是如此。

  对于慢日志的询问:

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

图片 7图片 8

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

各字段意思

图片 9图片 10

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;

图片 11图片 12

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

 

网站地图xml地图