一、索引

索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。

名词：
     覆盖索引：
      – 在索引文件中直接获取数据
     
     索引合并：
      – 把多个单列索引文件合并使用   

  1.   无索引：从前到后依次查找
  2.   索引：
  3.       id    创建索引文件(某种格式存储)
  4.       name  创建索引文件(某种格式存储)
  5.       email  创建索引文件(某种格式存储)  create index ix_name on userinfo3(email);
  6.       name  email 创建额外文件(某种格式存储)
  7.       查询时先在创建的索引文件内找到数据的定位，然后去原表内定位查找
  8.   索引种类（某种格式存储）：
  9.       hash索引：索引文件表内的数据顺序和表内的数据顺序不一致，是根据hash值排列的，然后对应相应的内存位置，然后在返回原表内查找 
  10.       单值查找速度快
  11.       范围查找速度慢
  12.       btree索引：二叉树的数据结构
  13.       范围查找速度快

MySQL中常见索引有：

  • 普通索引
  • 唯一索引
  • 主键索引
  • 组合索引

1、普通索引

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

  1. create table in1(
  2.     nid int not null auto_increment primary key,
  3.     name varchar(32) not null,
  4.     email varchar(64) not null,
  5.     extra text,
  6.     index ix_name (name)  --普通索引，索引名（索引列）
  7. )

创建表 + 索引

创建索引

create index 索引名称 on 表名(列名)

删除索引

drop index 索引名称 on 表名

查看索引

show index from table_name;

注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

  1. create index ix_extra on in1(extra(32));

View Code

2、唯一索引

唯一索引有两个功能：加速查询 和 唯一约束（可含null）

  1. create table in1(
  2.     nid int not null auto_increment primary key,
  3.     name varchar(32) not null,
  4.     email varchar(64) not null,
  5.     extra text,
  6.     unique ix_name (name)
  7. )

创建表 + 唯一索引

创建：

create unique index 索引名称 on 表名(列名)

删除：

drop unique index 索引名称 on 表名

3、主键索引

主键有两个功能：加速查询 和 唯一约束（不可含null）

  1. create table in1(
  2.     nid int not null auto_increment primary key,
  3.     name varchar(32) not null,
  4.     email varchar(64) not null,
  5.     extra text,
  6.     index ix_name (name)
  7. )
  9. OR
  11. create table in1(
  12.     nid int not null auto_increment,
  13.     name varchar(32) not null,
  14.     email varchar(64) not null,
  15.     extra text,
  16.     primary key(ni1),
  17.     index ix_name (name)
  18. )

创建表 + 创建主键

创建：

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

删除：

alter table 表名 drop primary key; （删除表的主键）

alter table 表名  modify  列名 int, drop primary key;（删除表指定列的主键）

4、组合索引

组合索引是将n个列组合成一个索引

– create unique index 索引名称 on 表名(列名,列名)
– drop unique index 索引名称 on 表名

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = \’alex\’ and n2 = 666。

  1. create table in3(
  2.     nid int not null auto_increment primary key,
  3.     name varchar(32) not null,
  4.     email varchar(64) not null,
  5.     extra text
  6. )

创建表 

  1. create index ix_name_email on in3(name,email);

创建组合索引

如上创建组合索引之后，查询（最左前缀匹配）：(name,email)

  • name and email  — 使用索引
  • name                 — 使用索引
  • email                 — 不使用索引

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

  1. 组合索引    
  2.                             - (name,email)
  3.                                 select  * from userinfo3 where name=\'alex\' and email=\'asdf\';
  4.                                 select  * from userinfo3 where name=\'alex\';
  5.                         索引合并：
  6.                             - name
  7.                             - email
  8.                                 select  * from userinfo3 where name=\'alex\' and email=\'asdf\';
  9.                                 select  * from userinfo3 where name=\'alex\';
  10.                                 select  * from userinfo3 where email=\'alex\';

组合索引和索引合并

5、全文索引：对文本的内容进行分词，进行搜索

 二、相关命令

  1. - 查看表结构
  2.     desc 表名
  4. - 查看生成表的SQL
  5.     show create table 表名
  7. - 查看索引
  8.     show index from  表名

三、正确使用索引

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

  1.     id，enail创建索引
  3.     
  4.     - like \'%xx\' 没有命中索引   
  5.         select * from tb1 where email like \'%cn\';                
  6.         
  7.     - 使用函数
  8.         select * from tb1 where reverse(email) = \'wupeiqi\';                
  9.         
  10.     - or
  11.         select * from tb1 where nid = 1 or name = \'seven@live.com\';   无法命中索引（name列没有建立索引）                
  12.         
  13.         特别的：当or条件中有未建立索引的列才失效，以下会走索引
  14.                 select * from tb1 where nid = 1 or name = \'seven@live.com\' and email = \'alex\'
  15.                 忽略name列（没有创建索引）
  16.                 
  17.     - 类型不一致
  18.         如果列是字符串类型，传入条件是必须用引号引起来，不然无法命中 
  19.         select * from tb1 where email = 999;
  20.         
  21.         
  22.     - !=
  23.         select * from tb1 where email != \'alex\'
  24.         
  25.         特别的：如果是主键，则还是会走索引
  26.             select * from tb1 where nid != 123
  27.     - >
  28.         select * from tb1 where email > \'alex\'                
  29.         
  30.         特别的：如果是主键或索引是整数类型，则还是会走索引
  31.             select * from tb1 where nid > 123
  32.             select * from tb1 where num > 123
  33.             
  34.             
  35.     - order by
  36.         select name from tb1 order by email desc;    （映射的是name列，所以不走索引）
  37.         
  38.         当根据索引排序时候，选择的映射如果不是索引，则不走索引
  39.         特别的：如果对主键排序，则还是走索引：
  40.             select * from tb1 order by nid desc;
  41.      
  42.     - 组合索引最左前缀
  43.         如果组合索引为：(name,email)
  44.         name and email       -- 使用索引
  45.         name                 -- 使用索引
  46.         email                -- 不使用索引

四、其他注意事项

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

五、limit分页

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

  1. a. select * from userinfo3 limit 20,10;
  2. b.    - 不让看（只能查看200页）
  3.     - 索引表中扫：
  4.         1、select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)  不是最优答案
  5.         2、最优解决方案：
  6.             记录当前页最大或最小ID
  7.             1. 页面只有上一页，下一页
  8.                 # max_id
  9.                 # min_id
  10.                 下一页数据：
  11.                     select * from userinfo3 where id > max_id limit 10;
  12.                 上一页数据：
  13.                     select * from userinfo3 where id < min_id order by id desc limit 10;   id倒序速度很快
  14.             2. 上一页 192 193  [196]  197  198  199 下一页    基于当前的页面，点击其他任一页分页
  15.                 
  16.                 select * from userinfo3 where id in (
  17.                     select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
  18.                 )            
  19.         
  20. c.id不连续，所以无法直接使用id范围进行查找  between   ... and

六、执行计划

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

让mysql预估执行操作(一般正确)

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

 

七、慢日志查询

a、配置MySQL自动记录慢日志

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

 

  1. 配置：
  2.     - 在内存中修改
  3. 　　　　　查看当前配置信息：
            show variables like     \'%query%\'  查看慢日志配置
            show variables like     \'%querise%\' 查看未使用索引的搜索是否记录
    　　　　　修改当前配置信息：
    set global 变量名 = 值
  4.     - 在配置文件中修改
  5.         在终端启动时输入命令启动配置文件
  6.         mysqld --defaults-file=“配置文件路径”        
  7.         创建my.conf配置文件：
  8.             slow_query_log = ON 
  9.             slow_query_log_file = D:/....
  10.             
  11.         注意：修改配置文件之后，先备份，再重启服务

 

 

 

 

　　

 

版权声明：本文为domestique原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/domestique/p/6994863.html