MySQL学习笔记（五）

倒数第二天！冲冲冲！！！

一、索引

一个表里面可以有多个索引。

1. 索引的作用：约束与加速查找

　　无索引：从前到后依次查找

　　有索引：会为索引列创造一个额外文件（以某种格式存储）。在使用索引进行查找时，会优先在该文件里面进行查找，所以查询时很快。——因此也会占据硬盘的空间。

　　不足：索引查询快，但是对索引进行更新和删除时慢。

　　命中索引：对索引的正确引用才能加速查找。 select * from student where sid = 1 

2. 索引类型

  某种格式——　　　

　　hash索引：索引表。在索引表中将数据以“哈希值”进行存储，同时保存该数据存储地址。需要注意的是，在索引表中，哈希值的排列顺序与原来表中的数据顺序是不一样的。

　　缺点：因此在hash表中如果查找where id > 3 之类的数据，即使id列为索引列，其查找速度在大数据的情况下也会变慢，因为hash中的排列时无序的，不能一顺溜的取出来。简而言之，查找连续的数值很慢。

　　优点：查找单值速度非常快。

　　btree索引（常用）：将值转化为数字，以二叉树的形式进行存储（按顺序存储）。——（在innodb中使用的是btree）

　　a. 普通索引：加速查找（没有唯一性的限制）

　　　创建索引： create index ix_name on tiny_data<email> (ix_name为索引名称)

　　　删除索引： drop index ix_name on tiny_data 

　　　查看索引： show index from tiny_data; 

　　　在创建表时创建索引：

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)
)

　　    对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length： create index ix_extra on in1(extra(32)) 

　　b. 主键索引：加速查找+不能为空+不能重复 （就是创建时的primary key）

　　　在建表之后创建主键索引：  alter table 表名 add primary key(列名)  

　　c. 唯一索引：加速查找+不能重复（可以为空）

　　　创建索引： create unique index 自定义索引名称 on 表名（列名） （就是多加个unique）

　　    删除索引： drop 自定义索引名 on 表名 

　　    在创建表同时创建索引：（注意此时关键词是 unique 不是 unique index）

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)
)

　　d. 组合索引（多列组合成一个索引）：联合普通索引，联合主键索引，联合唯一索引

　　　创建索引：  create unique index 自定义索引名称 on 表名（列名，列名）  

　　    删除索引：  drop 自定义索引名 on 表名  

　　    最左前缀匹配：假设 设立sid 与 sname 都是 student 表的索引。 create index ix_sid_sname on student(sid, name) 

select * from student where sid = 1;                         -- 可以在索引文件中执行，使用索引
select * from student where sid = 1 and sname = "alex";      -- 可以在索引文件中执行
select * from student where sname = "alex";                  -- 不可以在索引文件中执行

3. 索引补充

（1）覆盖索引：（名词解释）在索引文件中可以直接获取数据（指索引值）。如  select sid from student where sid = 1  （在sid为student表的索引的情况下）。

（2）索引合并：（名词解释）把多个单列索引合并使用。如  select sid, sname from student where sid = 1 and sname = 'alex  （在sid与sname都是索引的情况下）。不过虽然“索引合并”使用起来比“联合索引”方便，但是“联合索引”效率更高。

二、命中索引（重点***）

索引内容参考——武沛齐博客

1. 正确使用索引

（1）避免使用“%like%”语句进行查询，因为速度非常慢。

    解决办法：使用第三方工具，搜集分词结果并存储该分词所在ID（索引），在查询时程序会优先在第三方工具的分析结果中进行查询，获取ID后再使用索引查询。

（2）避免使用函数

（3）or：查询条件是（索引列 or 非索引列）的组合时，会导致索引失效。

（4）类型不一致：如果列是字符串类型，传入条件时必须用引号引起来，否则会执行一个类型转换过程，使查询速度变慢。


（5）普通索引使用“!=”进行查询时，无法命中索引，即没有使用索引查询。Especially, 主键除外，如果是对主键使用“!=”进行查询，还是会使用索引进行查询。

    普通索引使用“>”进行查询时，无法命中索引，即没有使用索引查询。Especially, 如果是主键或索引是整数类型，则还是会走索引。

    “order by” 当根据索引排序时候，选择的映射如果不是索引，则不走索引。如 select email from tb1 order by name desc; 。Especially, 如果对主键排序，则还是走索引。

2. 其他注意事项（1,2,3,9最基础）

（1）避免使用select *

（2）count(1)或count(列)代替count(*)


（3）创建表时尽量时 char 代替 varchar

（4）表的字段顺序固定长度的字段优先

（5）组合索引代替多个单列索引（经常使用多个条件查询时）

（6）尽量使用短索引。可以创建局部索引

create index ix_name on tb1(title(16))
-- 表示用title的前16个字符做索引

（7）使用连接（JOIN）来代替子查询(Sub-Queries)——不过这个在MySQL中是一样的，在SQLServer中有差别

（8）连表时注意条件类型需一致

（9）引散列值（重复少）不适合建索引，例：性别

三、MySQL执行计划

执行计划：让MySQL预估执行操作需要的时间。

#输入，假设索引是（id, email）
explain select * from tiny_data; --没有走索引（all）
explain select * from tiny_data where id = 3; -- 主键索引（const）
explain select * from tiny_data where email = "alex@qq.com"; -- 普通索引（ref）

预估时间判断——查看type（仅供参考）

type：all（全表扫描）——没有走索引，速度很慢

　　   const（走了主键索引）——速度很快

  　　 ref（走了索引）——速度很快

 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

四、DBA工作

1. 慢日志：在服务端进行设置

　　　　a.执行时间过慢，如大于10秒

　　　　b.未命中索引

　　　　将以上记录记录到指定的日志文件路径。

2. 配置

　　（1）基于内存

　　查看当前配置信息： show variables like '%query%' 

　　在配置信息的结果中重点关注：

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

　　修改当前配置： set global 变量名 = 值 

　　（2）基于配置文件

　　a. 自建配置文件：在启动服务端的时候设置配置文件路径，mysql慢日志就会使用配置文件中的配置

　　 mysqld --defaults-file='D:\my.conf' 

　　b. 在已有的自带的配置文件下进行更改（my-default.ini）。（我没有找到这个文件，，，）

　　ps：修改配置文件后，需要重启服务。（在修改前记得备份，如果修改后出了问题，可以还原）

五、MySQL分页性能相关方案（重点***）

limit分页时，如（limit 30，10），是先扫描40条数据，再取最后十条，所以越往后，需要扫描的数据量越大，会越慢。

解决方案：

1. 设定允许查询的页数

2. a.从索引表中进行扫描，实行覆盖索引 b. 从覆盖索引中查找 （下面这个mysql用不了）

select * from tiny_data where id in (           -- 利用索引进行查找
    select id from tiny_info limit 20000, 10    -- 覆盖索引
)

3. 最优方案（单纯基于数据库的最优操作）：

　　记录当前页的最大与最小id（假设每页记录条数为10）

　　（1）上一页/下一页

#min_id, max_id
-- 下一页
SELECT * FROM userinfo3 WHERE id > max_id limit 10
-- 上一页
SELECT * FROM userinfo3 WHERE id < min_id ORDER BY id desc limit 10

　　（2）跳转型：上一页 192 193 194 【195】 196 197 198 下一页

SELECT * FROM userinfo3 WHERE id in (
(SELECT * FROM userinfo3 WHERE id > max_id limit 30) as N ORDER BY N.id DESC LIMIT 10
)
-- 这么讲了一通，理解了大致操作，但是没有实操