MySQL（三）-- 索引

一概述

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段。索引能够轻易将查询性能提高好几个数量级。

索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

对于索引，会保存在额外的文件中。

二常用索引

普通索引INDEX：加速查询

主键索引PRIMARY_KEY：加速查询、列值唯一（不可为空）

唯一索引UNIQUE：加速查询、列值唯一（可为空）

联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• -PRIMARY KEY(id,name)：联合主键索引 
• -UNIQUE(id,name)：联合唯一索引
• -INDEX(id,name)：联合普通索引

2.1 普通索引

普通索引仅有一个功能：加速查找

CREATE TABLE tb1(
    id INT NOT NULL auto_increment PRIMARY KEY,
    name CHAR(32) NOT NULL,
    age INT NOT NULL,
    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_name ON tb1(name(32));

注意点

2.2 主键索引

主键索引有两个功能：加速查找和唯一约束（不可为空）

-- 方式一:
CREATE TABLE tb3(
    id INT NOT NULL auto_increment PRIMARY KEY,
    name CHAR(32) NOT NULL,
    age INT NOT NULL,
)

-- 方式二：
CREATE TABLE tb3(
    id INT NOT NULL auto_increment,
    name CHAR(32) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY(id)
)

创建表时创建主键索引

ALTER TABLE 表名 ADD PRIMARY KEY(列名)；

创建主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;

ALTER TABLE 表名 MODIFY 列名 INT , DROP PRIMARY KEY;

删除主键索引

2.3 唯一索引

唯一索引有两个功能：加速查找和唯一约束（可为空）

CREATE TABLE tb2(
    id INT NOT NULL auto_increment PRIMARY KEY,
    name CHAR(32) NOT NULL,
    age INT NOT NULL,
    UNIQUE unique_name(name)
)

创建表时创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名（列名）

创建唯一索引

DROP UNIQUE INDEX 索引名 ON 表名；

删除唯一索引

2.4 联合（组合）索引

联合索引是将N个列组合成一个索引，其主要应用在：频繁的同时使用N列来进行查询，比如：where id = 123 and name = "joe"。

联合索引有：联合主键索引、联合唯一索引、联合普通索引。

CREATE TABLE test(
    tid INT NOT NULL auto_increment PRIMARY KEY,
    n1 CHAR NOT NULL,
    n2 CHAR NOT NULL,
    INDEX ix_n1_n2(n1,n2)
)

创建表时创建联合索引

CREATE INDEX ix_n1_n2 ON 表名（列名1,列名2...）;

创建联合索引

DROP INDEX 索引名 ON 表名;

删除联合索引

1、如上创建组合索引后，查询时
    n1 and n2   -- 使用索引
    n1              -- 使用索引
    n2              -- 不使用索引

2、对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

注意点

2.5 其他补充

何为覆盖索引？

select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

何为索引合并？

使用多个单列索引组合搜索。

三相关查询命令

- 查看表结构
    desc 表名
 
- 查看生成表的SQL
    show create table 表名
 
- 查看索引
    show index from  表名
 
- 查看执行时间
    set profiling = 1;
    SQL...
    show profiles;

四 B-Tree 索引和 Hash 索引的对比

对于 B-tree 和 hash 数据结构的理解能够有助于预测不同存储引擎下使用不同索引的查询性能的差异，尤其是那些允许你选择 B-tree 或者 hash 索引的内存存储引擎。

4.1 B-Tree 索引的特点

虽然使用术语”B-Tree“，但是实际上底层的存储引擎可能使用不同的存储结构，例如，NDB集群存储引擎内部实际上使用了T-Tree结构，InnoDB则使用的是B+Tree。

B-Tree通常意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同。B-Tree索引适用于全键值、键值范围或者键前缀查找。以索引key(a,b,c)为例：

全值匹配：可以查找a=1 and b=1 and c=1

匹配最最前缀：可以查找a=1

匹配列前缀：可以查找a like 'a%'

匹配范围值：可以查找 a>1 and a<10

4.2 Hash 索引的特点

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。

因为索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然后，哈希索引也有它的限制：

1、哈希索引只包含哈希值和指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。

2、哈希索引数据并不是按照索引数据顺序存储的，所以也就无法用于排序。

3、哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引内的全部内容来计算哈希值的。例如，在数据列（A，B）上建立哈希索引，如果查询只有数据列A，则无法使用该索引。

4、哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price > 100。

5、访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历列表中的所有行指针，逐行进行比较，直到找到所有符合条件的行。

6、如果哈希索冲突很多的话，一些索引的维护操作的代价也会很高。例如，如果再某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

因为这些限制，哈希索引只适用于某些特定场合。而一旦适应哈希索引，则它带来的性能提升将非常显著。

注意点：在MySQL中，索引是在存储引擎中层而不是服务层实现的。

载自：http://blog.sina.com.cn/s/blog_b92fcb510102vvdt.html

五使用索引 VS 不使用索引

由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会快到飞起来。

六正确使用索引

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。

即使建立索引，索引也不会生效：

- 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;
 
- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

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

其他注意事项

七 limit分页

无论是否有索引，limit分页是一个值得关注的问题

每页显示10条：
当前 118 120， 125

倒序：
             大      小
970  7 6  6 5  54  43  32  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;

View Code

八查询优化神器 - explain

关于explain命令具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

执行计划：让mysql预估执行操作(一般正确)
    all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    id,email

    慢：
        select * from userinfo3 where name='alex'

        explain select * from userinfo3 where name='alex'
        type: ALL(全表扫描)
            select * from userinfo3 limit 1;
    快：
        select * from userinfo3 where email='alex'
        type: const(走索引)

View Code

http://www.cnblogs.com/xiaoboluo768/p/5400990.html

http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_system

九慢日志查询

9.1 配置MySQL自动记录慢日志

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%'
      修改当前配置：
         set global 变量名 = 值

9.2 查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

"""
--verbose    版本
--debug      调试
--help       帮助
 
-v           版本
-d           调试模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前N条just show the top n queries
-a           不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配；grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间；don't subtract lock time from total time
"""

http://www.cnblogs.com/linhaifeng/articles/7274563.html

posted @ 2018-08-23 21:27 Joe1991 阅读(162) 评论(0) 收藏举报

刷新页面返回顶部

Joe1991