• 【MySQL】索引相关

    "


    目录

    普通索引

    唯一索引

    主键索引

    组合索引

    正确使用索引的情况

    索引的注意事项

    执行计划 axplain

    慢日志记录

    分页性能相关方案

    索引是数据库中专门用于帮助用户快速查找数据的一种数据结构.
    类似于字典中的目录，查找字典内容可以根据目录查找到数据的存放位置，然后直接获取.

    作用：约束和加速查找

    常见的几种索引：

    - 普通索引
    - 唯一索引
    - 主键索引
    - 联合索引（多列）
            -- 联合主键索引
           -- 联合唯一索引
           -- 联合普通索引

    无索引和有索引的区别：

    无索引：从前往后一条一条查询.
    有索引：创建索引的本质，就是创建额外的文件，以某种格式存储，查询的时候，先去额外的文件找，确定了位置，然后再去原始表中直接查询，但是创建的索引越多，越会对硬盘有损耗.

    ———————————

    建立索引的目的：

    1. 额外的文件保存特殊的数据结构
    2. 查询快，但是插入更新删除依旧慢
    3. 创建索引之后，必须命中索引才能有效

    索引的种类：

    hash索引：查询单条快，范围查询慢

    btre类索引：b+树，层数增多，数据量指数级增长（InnoDB默认支持btree索引，这里就使用它）

    索引名词：

    覆盖索引：在索引文件中直接获取数据
    （例如：select name from userinfo where name = 'zyk';)

    索引合并：把多个单列索引合并使用
    （例如：select name from userinfo where name = 'zyk' and sex = 'boy';）

    1. # 查看索引
    2. show index from 表名;
    3.  
    4. # Key_name字段为定义的索引名
    5. # Column_name字段显示的是创建了索引的字段
    6. # 注意：一个表中默认会有一个主键，主键默认为主键索引.

    普通索引

    作用：加速查找

    1. # 创建表 + 普通索引
    2. create table userinfo(
    3.     nid int not null auto_increment primary key,
    4.     name varchar(20) not null,
    5.     index ix_name(name)    # 定义索引
    6.     );
    7.  
    8. # 添加普通索引
    9. create index 索引名 on 表名(列名);
    10.  
    11. # 删除索引
    12. drop index 索引名 on 表名;

    唯一索引

    作用：加速查找和唯一约束（包含null）

    1. # 创建表 + 唯一索引
    2. create table userinfo(
    3.     nid int not null auto_increment primary key,
    4.     name varchar(20) not null,
    5.     unique index ix_name(name)    # 添加索引
    6.     );
    7.  
    8. # 添加唯一索引
    9. create unique index 索引名 on 表名(列名);
    10.  
    11. # 删除索引
    12. drop index 索引名 on 表名;

    主键索引

    作用：加速查找和唯一约束（不包含null）

    1. # 添加表 + 主键索引
    2. # 方法1:
    3. create table userinfo(
    4.     nid int not null auto_increment primary key,    # 添加主键索引
    5.     name varchar(32) not null
    6.     );
    7.  
    8. # 方法2:
    9. create table userinfo(
    10.     nid int not null auto_increment,
    11.     name varchar(32) not null,
    12.     primary key(nid)    # 添加主键索引
    13.     );
    14.  
    15. # 添加主键索引
    16. alter table 表名 add primary key(列名);
    17.  
    18. # 删除主键索引
    19. alter table 表名 modify 列名 int, drop primary key;

    组合索引

    组合索引是将n个列组合成一个索引
    应用场景：频繁的同时使用n列来进行查询（如：where name = 'zyk' and sex = 'boy'）

    1. # 添加联合普通索引
    2. create index 索引名 on 表名(列名1, 列名2);

    正确使用索引的情况

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

    使用索引，我们必须知道：
    (1) 创建索引
    (2) 命中索引
    (3) 正确使用索引

    ——————————————

    准备100万条数据:

    1. #1. 准备表
    2. create table userinfo(
    3.     id int,
    4.     name varchar(20),
    5.     gender char(6),
    6.     email varchar(50)
    7.     );
    8.  
    9.  
    10. # 创建存储过程，实现批量插入记录
    11. delimiter $$    # 声明存储过程的结束符号为$$
    12. create procedure auto_insert1()  # 定义存储过程名为：auto_insert1()
    13. begin
    14.     declare i int default 1;    # 定义i为1
    15.     while (i<1000000) do
    16.         insert into userinfo
    17. values(i, concat('zyk', i), 'male', concat('zyk', i, '@oldboy'));
    18.     set i = i + 1;
    19.     end while;
    20. end $$
    21. delimiter ; 重新声明分号为结束符号
    22.  
    23.  
    24. # 查看存储过程
    25. show create procedure auto_insertG
    26.  
    27. # 调用存储过程
    28. call auto_insert1();

    测试：

    1. - like '%xx'
    2.     select * from userinfo where name like '%al';
    3. - 使用函数
    4.     select * from userinfo where reverse(name) = 'alex333';
    5. - or
    6.     select * from userinfo where id = 1 or email = 'alex122@oldbody';
    7.     特别的：当or条件中有未建立索引的列才失效，以下会走索引
    8.             select * from userinfo where id = 1 or name = 'alex1222';
    9.             select * from userinfo where id = 1 or email = 'alex122@oldbody' and name = 'alex112'
    10. - 类型不一致
    11.     如果列是字符串类型，传入条件是必须用引号引起来，不然...
    12.     select * from userinfo where name = 999;
    13. - !=
    14.     select count(*) from userinfo where name != 'alex'
    15.     特别的：如果是主键，则还是会走索引
    16.         select count(*) from userinfo where id != 123
    17. - >
    18.     select * from userinfo where name > 'alex'
    19.     特别的：如果是主键或索引是整数类型，则还是会走索引
    20.         select * from userinfo where id > 123
    21.         select * from userinfo where num > 123
    22. - order by
    23.     select email from userinfo order by name desc;
    24.     当根据索引排序时候，选择的映射如果不是索引，则不走索引
    25.     特别的：如果对主键排序，则还是走索引：
    26.         select * from userinfo order by nid desc;
    27.  
    28. - 组合索引最左前缀
    29.     如果组合索引为：(name,email)
    30.     name and email       -- 使用索引
    31.     name                 -- 使用索引
    32.     email                -- 不使用索引

    什么是最左前缀：

    1. # 建立组合索引
    2. create index ix_name_email on user info(name, email);
    3.  
    4. # where name：使用索引
    5. mysql> select * from userinfo where name like 'zyk123456%';                     +--------+-----------+--------+------------------+
    6. | id     | name      | gender | email            |
    7. +--------+-----------+--------+------------------+
    8. | 123456 | zyk123456 | male   | zyk123456@oldboy |
    9. +--------+-----------+--------+------------------+
    10. 1 row in set (0.00 sec)    # 0.00
    11.  
    12. # where name and email：使用索引
    13. mysql> select * from userinfo where name like 'zyk123456%' and email like 'zyk123456%';
    14. +--------+-----------+--------+------------------+
    15. | id     | name      | gender | email            |
    16. +--------+-----------+--------+------------------+
    17. | 123456 | zyk123456 | male   | zyk123456@oldboy |
    18. +--------+-----------+--------+------------------+
    19. 1 row in set (0.00 sec)    # 0.00
    20.  
    21. # where email：不使用索引
    22. mysql> select * from userinfo where email like 'zyk123456%';                    +--------+-----------+--------+------------------+
    23. | id     | name      | gender | email            |
    24. +--------+-----------+--------+------------------+
    25. | 123456 | zyk123456 | male   | zyk123456@oldboy |
    26. +--------+-----------+--------+------------------+
    27. 1 row in set (0.60 sec)    # 注意：0.60

    如果使用组合索引如上，name和email组合索引之后，查询：
    （1）name and email        # 使用索引
    （2）name        # 使用索引
    （3）email        # 不使用索引

    对于同时搜索n个条件，组合索引的性能好于多个单列索引.

    索引的注意事项

    1. 不要使用select *        # 对性能的损耗太大
    2. 应使用 count(1) 或 count(列名) 代替 count(*)  后在查资料发现这些现在都没有区别了
    3. 创建表时尽量使用char代替varchar        # 查询速度
    4. 表的字段顺序：固定长度的字段优先
    5. 经常使用多个条件查询时，应建立组合索引代替多个单列索引
    6. 尽量使用短索引（create index ix_title on tb(title(16)); 特殊的数据类型，如text类型）
    7. 使用链接 join 来代替子查询
    8. 连表时注意条件类型需一致
    9. 索引散列（重复少）不适用于建索引，例如：性别不适合建索引

    执行计划 axplain

    explain + 查询SQL：用于显示SQL执行信息参数，根据参考信息可以进行SQL优化.

    1. mysql> explain select * from userinfo;
    2. +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
    3. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
    4. +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
    5. |  1 | SIMPLE      | userinfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997307 |   100.00 | NULL  |
    6. +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
    7. 1 row in set, 1 warning (0.00 sec)
    8.  
    9. mysql> explain select * from (select id,name from userinfo where id < 20) as A;
    10. +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    11. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
    12. +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    13. |  1 | SIMPLE      | userinfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997307 |    33.33 | Using where |
    14. +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    15. 1 row in set, 1 warning (0.00 sec)

    各字段参数说明：

    1. select_type：
    2.                 查询类型
    3.                     SIMPLE          简单查询
    4.                     PRIMARY         最外层查询
    5.                     SUBQUERY        映射为子查询
    6.                     DERIVED         子查询
    7.                     UNION           联合
    8.                     UNION RESULT    使用联合的结果
    9. table：
    10.                 正在访问的表名
    11. type：
    12.                 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    13.                 ALL             全表扫描，对于数据表从头到尾找一遍
    14.                                 select * from userinfo;
    15.                                 特别的：如果有limit限制，则找到之后就不在继续向下扫描
    16.                                        select * from userinfo where email = 'alex112@oldboy'
    17.                                        select * from userinfo where email = 'alex112@oldboy' limit 1;
    18.                                        虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
    19.  
    20. INDEX ：      全索引扫描，对索引从头到尾找一遍
    21.                                 select nid from userinfo;
    22.  
    23. RANGE：        对索引列进行范围查找
    24.                                 select *  from userinfo where name < 'alex';
    25.                                 PS:
    26.                                     between and
    27.                                     in
    28.                                     >   >=  <   <=  操作
    29.                                     注意：!= 和 > 符号
    30.  
    31.  
    32. INDEX_MERGE：  合并索引，使用多个单列索引搜索
    33.                                 select *  from userinfo where name = 'alex' or nid in (11,22,33);
    34.  
    35. REF：       根据索引查找一个或多个值
    36.                                 select *  from userinfo where name = 'alex112';
    37.  
    38. EQ_REF：    连接时使用primary key 或 unique类型
    39.                                 select userinfo2.id,userinfo.name from userinfo2 left join tuserinfo on userinfo2.id = userinfo.id;
    40.  
    41.  
    42.  
    43. CONST：常量
    44.             表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
    45.                  select id from userinfo where id = 2 ;
    46.  
    47. SYSTEM：系统
    48.              表仅有一行(=系统表)。这是const联接类型的一个特例。
    49.                  select * from (select id from userinfo where id = 1) as A;
    50.  
    51.  
    52. possible_keys：可能使用的索引
    53.  
    54. key：真实使用的
    55.  
    56. key_len：　　MySQL中使用索引字节长度
    57.  
    58. rows： mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
    59.  
    60. extra：
    61.                 该列包含MySQL解决查询的详细信息
    62.                 “Using index”
    63.                     此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
    64.                 “Using where”
    65.                     这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
    66.                 “Using temporary”
    67.                     这意味着mysql在对查询结果排序时会使用一个临时表。
    68.                 “Using filesort”
    69.                     这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
    70.                 “Range checked for each record(index map: N)”
    71.                     这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的

    慢日志记录

    开启慢查询日志，可以让MySQL记录查询时长超过指定时间的语句，通过定位分析性能的瓶颈，能更好的优化数据库系统的性能.

    1. (1) 查询是否开启了慢查询日志
    2. mysql> show variables like 'slow_query%';
    3. +---------------------+----------------------------------------+
    4. | Variable_name       | Value                                  |
    5. +---------------------+----------------------------------------+
    6. | slow_query_log      | OFF                                    |
    7. | slow_query_log_file | /usr/local/mysql/data/MacBook-slow.log |
    8. +---------------------+----------------------------------------+
    9. 2 rows in set (0.01 sec)
    10. # 参数解释：
    11.     slow_query_log：慢查询开启状态：OFF为关闭 ON为开启
    12.     slow_query_log_file：慢查询日志存放的位置（注意用户权限）
    13.  
    14. (2) 查看慢查询超时时间
    15. mysql> show variables like 'long%';
    16. +-----------------+-----------+
    17. | Variable_name   | Value     |
    18. +-----------------+-----------+
    19. | long_query_time | 10.000000 |    # 默认为10妙
    20. +-----------------+-----------+
    21. 1 row in set (0.00 sec)
    22.  
    23. (3) 开启慢日志
    24. mysql> set global slow_query_log = 1;    # 1表示开启，0表示关闭
    25. Query OK, 0 rows affected (0.01 sec)
    26.  
    27. (4) 修改查询超时时间
    28. mysql> set long_query_time = 1;    # 直接指定秒
    29. Query OK, 0 rows affected (0.00 sec)

    通过配置文件开启慢日志（推荐）：

    1. [mysqld]
    2. slow_query_log = 1
    3. slow_query_log_file = /usr/local/mysql/data/localhost-slow.log
    4. long_query_time = 1
    5.  
    6. 参数说明：
    7.     slow_query_log：慢查询开启状态 1为开启
    8.     slow_query_log_file：慢查询日志存放的位置
    9.     long_query_time：查询时间超过多少秒才记录，默认10秒，这里修改为1秒

    分页性能相关方案

    (1) 只有上一页和下一页：
    做一个记录：记录当前页的最大id或最小id

    • 下一页：select * from userinfo where id>max_id limit 10;
    • 上一页：select * from userinfo where id<min_id order by id desc limit 10;

    (2) 中间有页码的情况

    1. select * from userinfo where id in(
    2.     select id from 
    3.         (select * from userinfo where id > pre_max_id 
    4.         limit (cur_max_id-pre_max_id)*10) as A 
    5.         order by A.id desc 
    6.         limit 10
    7.     );
    "
  • 相关阅读:
    MVC3 模板页页预留Section
    LINQ表达式总结笔记
    分布式事务管理器(MSDTC)的事务处理异常的排错
    ado。net的事物BeginTransaction demo
    TransactionScope类使用场景和方法介绍
    Linq中使用Left Join
    FullText Search in ASP.NET using Lucene.NET
    EF的BeginTransaction 用法
    mvc4 @Html.Partial,@Html.RenderPartial
    Android监听EditText内容变化
  • 原文地址：https://www.cnblogs.com/zyk01/p/11375950.html
Copyright © 2020-2023  润新知