python开发之MySQL（二）

一、索引基础

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

• 普通索引：仅加速查询

• 唯一索引：加速查询 + 列值唯一（可以有null）

• 主键索引：加速查询 + 列值唯一 +　表中只有一个（不可以有null）

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

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

  索引合并：使用多个单列索引组合搜索
  覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

3、相关命令

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

二、索引种类

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

- 创建表+索引
	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;

2、唯一索引
唯一索引有两个功能：加速查询 和 唯一约束（可含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 表名

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

- 创建表+创建主键
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)
)

- 创建主键
	alter table 表名 add primary key(列名);
- 删除主键
	alter table 表名 drop primary key;
	alter table 表名  modify  列名 int, drop primary key;

4、组合索引
组合索引是将n个列组合成一个索引
其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = ‘alex’ and n2 = 666。

- 创建表
create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

- 创建组合索引
	create index ix_name_email on in3(name,email);
如上创建组合索引之后，查询（遵循最左前缀）：
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引
注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

三、正确使用索引

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

- 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)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

四、limit分页

• 原理：从上往下扫表查到目标便停止查询
• SQL语句中如果有where nid>600, 则表明直接从nid>600开始查，节省了查前600个的时间

查询下一页：
	select * from user_info where nid>%s limit 10
查询上一页，把上面的所有倒序之后取前十就是上一页：
	select * from user_info where nid<%s order by nid desc limit 10

五、执行计划

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化（看type列）

 type
     查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
     
   - ALL       全表扫描，对于数据表从头到尾找一遍
                   select * from tb1;
               特别的：如果有limit限制，则找到之后就不在继续向下扫描
                   select * from tb1 where email = 'seven@live.com'
                   select * from tb1 where email = 'seven@live.com' limit 1;
               虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

   - INDEX     全索引扫描，对索引从头到尾找一遍
                   select nid from tb1;

   - RANGE     对索引列进行范围查找
                   select *  from tb1 where name < 'alex';
                  PS:
                      between and
                      in
                      >   >=  <   <=  操作
                      注意：!= 和 > 符号

   - INDEX_MERGE     合并索引，使用多个单列索引搜索
                         select *  from tb1 where name = 'alex' or nid in (11,22,33);

   - REF             根据索引查找一个或多个值
                         select *  from tb1 where name = 'seven';

   - EQ_REF          连接时使用primary key 或 unique类型
                         select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;

   - CONST           常量
                     表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                         select nid from tb1 where nid = 2 ;

   - SYSTEM          系统
                     表仅有一行(=系统表)。这是const联接类型的一个特例。
                         select * from (select nid from tb1 where nid = 1) as A;

六、慢日志记录

配置MySQL自动记录慢日志

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

七、其他

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

==================================================================
参考文献：
https://www.cnblogs.com/wupeiqi/articles/5716963.html
https://www.cnblogs.com/wupeiqi/articles/5713323.html