mysql内存中的索引_mysql中的索引以及相关优化

索引：

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。在做性能测试的时候，索引也是优化的一个方面。

为什么要使用索引：

如果表中有大量数据，那么没有索引的情况下，会扫描整个表，随着表内数据的日益增长，那么效率会越来越低。如果有了索引，回创建额外的文件，按照某种格式存储数据的位置。所以有了索引之后，回先找额外的文件，拿到数据位置。

索引的优缺点：

优点：所有的Mysql的字段类型都可以被素银，也就是可以给任意字段设置索引，极大的加快数据的查询速度。

缺点：创建索引和维护索引要耗费时间，随着数据量的增加耗费时间也回增加。索引是一个单独的文件，所以也需要占空间，我们知道数据库表中的数据也会有最大上限设置，如果我们有大量的索引，索引文件回比数据文件更快的到达上限。对表中的数据进行变更的时候，索引也需要维护，那么就降低了速度。

MySql中的索引方法：

1 hash索引：

创建索引表，将被索引列，转化成hash值，但是hash表中的没有顺序，所以在查询前几行数据的时候，可能效率较低。所以特点是单值查询快，范围查询慢。msyql的innodb和myisam不支持hash。

alex

98888

数据存储地址

2 btree索引：

也是mysql 的默认索引类型。使用的算法是二叉树。因为插入的时候有顺序，所以范围查找会快。

索引类型：

普通索引：只是加速查找。

唯一索引：加速查找，约束。可为空，不能重复。

主键索引：加速查找，约束。不可为空，不能重复。且每个表中只能有一个主键索引。

联合索引(组合索引)：

联合主键索引：一个主键索引下有多列，也叫复合主键。

联合唯一索引：一个唯一索引下有多列。

联合普通索引：一个普通索引下有多列。

全文索引：对文本内容进行分词，进行搜索。拿出数据的一部分创建索引，如果不指定数据的长度mysql会直接报错。

空间索引：空间索引是针对空间数据类型的字段建立的索引(myisam)

大体上可以分为4类：即单列索引，联合索引，全文索引，空间做引。那么工作中常用到的就是单列索引和普通索引。

mysql中各种索引的关键字：

普通索引：INDEX

唯一索引：UNIQUE

主键索引：PRIMARY KEY

索引的创建和查看：

1 建表时创建索引：

CREATE TABLE index_test1 (

id INT auto_increment PRIMARY KEY,

NAME CHAR ( 128 ),

email VARCHAR ( 255 ),

age int(32),

phone int(14),

addr varchar(255),

hoddy varchar(255)

INDEX ( email ),

UNIQUE(phone),

) ENGINE = INNODB DEFAULT charset = utf8

创建联合索引，只需要在指定索引类型后添加对应字段就可以

CREATE TABLE index_test1 (

id INT auto_increment PRIMARY KEY,

NAME CHAR ( 128 ),

email VARCHAR ( 255 ),

age int(32),

phone int(14),

addr varchar(255),

hoddy varchar(255)

INDEX ( email,age ),

UNIQUE(addr,hoddy),

) ENGINE = INNODB DEFAULT charset = utf8

2 建表后创建索引：

-- 创建普通索引 create index索引名称 on 表名(列)

-- create index ix_name on index_test(email)

-- 创建局部索引 列中的一部分做索引

-- create index ix_name on index_test(email(32))

--

-- 创建 唯一索引

-- create UNIQUE index ix_name on index_test(email)

-- select * from index_test where email='2900001@qq.com'

-- 后期创建主键索引

-- alter table 表名 add primary key (列名)

-- 删除索引

-- drop index ix_name on index_test

建表后创建联合索引：

-- 联合索引(组合索引)

-- 联合普通索引

-- create index索引名称 on 表名(列1，列2)

-- 联合主键索引

-- alter table 表名 add primary key (列名1,列2)

-- 联合唯一索引

-- create UNIQUE index索引名称 on 表名(列1，列2)

联合索引遵循 最左前缀匹配：

-- 最左前缀匹配

-- create index ix_name on index_test(name,email)

-- 命中索引的情况：

-- select * from index_test where name='yuan66666'

-- select * from index_test where name='yuan66666' and email='66666@qq.com'

-- 未命中索引的情况

-- select * from index_test where email='66666@qq.com'

-- 效率：

-- 组合索引>索引合并

使用索引注意事项：

转自老男孩武sir

- 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

注意事项：

避免使用select *

- count(1)或count(列) 代替 count(*)

- 创建表时尽量时 char 代替 varchar

- 表的字段顺序固定长度的字段优先

- 组合索引代替多个单列索引(经常使用多个条件查询时)

- 尽量使用短索引

- 使用连接(JOIN)来代替子查询(Sub-Queries)

- 连表时注意条件类型需一致

- 索引散列值(重复少)不适合建索引，例：性别不适合

分页性能相关：

每页显示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;

执行计划：

执行计划，让mysql预估执行操作时间。explain + SQL

explain的字段：

type各个字段性能：

all  全表扫描

Index 全索引扫描 对索引遍历

Range 对索引进行范围查找(> ,>=,

Index_merge 合并索引 使用多个单列索引搜索

Ref    根据索引赵一个或多个值

Eq_ref 链接时使用primary key或unqiue类型

Const 常量：

System  系统 表仅有一行(=系统表)这是const链接类型的一个特例

Selct * from (select nid from tb1 where nid=1)as A

Const 常量 表最多有一个匹配行，以为仅有一行 在这行的列值 可被优化器剩余部分认为是常数，const表很快，因为他们只被读取一次

效率：All

其他字段：

Possible_keys  可能使用的索引

Key  真实使用的

Key_len  mysql中使用的索引字节长度

Rows mysql 估计为了找到所需要的行而要读取的行数---只是预估值

extra

extra

该列包含MySQL解决查询的详细信息

“Using index” ：此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。

“Using where”：这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Usingwhere”。有时“Usingwhere” 的出现就是一个暗示：查询可受益于不同的索引。

“Using temporary”：这意味着mysql在对查询结果排序时会使用一个临时表。

“Using filesort”：这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。

“Range checked for each record(index map: N)”：这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

Mysql的慢日志：

设定一个时间，将执行时间大于该时间的SQL写入慢日志中。

慢日志配置：

内存中更改：

查看  慢日志配置  show variables like ‘%query%’

慢日志 是否命中索引 show variables like ‘%queries%’

也可以写成 show variables like ‘%quer%’

Long_query_time  超时时间

Show_query_long  慢日志开启关闭

Show_query_long_file 慢日志路径

Long_queris_not_using_indexes 是否命中索引开关

更改   慢日志

Set global Slow_query_long=on

更改mysql配置文件

将Show_query_long等直接写在配置文件

然后在启动mysqld的时候指定默认配置文件

Mysqld --defaults-file=’D:\my.conf’