mysql学习3
1.索引
索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，
会保存在额外的文件中。
作用：
约束
加速查找
1.1.建立索引
a.额外的文件保存特殊的数据结构
b.查询快，插入更新删除慢
c.命中索引
ps:创建索引时如果是blob和text类型，必须制定length
create index in_extra on in1(extra(32))
1.2索引种类：
主键索引：加速查找+不能为空+不能重复
普通索引：加速查找
创建：
create index 索引名称 on 表明（列名）
删除：
drop index 索引名称 on 表名
唯一索引：加速查找+不能重复
创建：
create unique index 索引名称 on 表名（列名）
删除:
drop unique index 索引名称 on 表名
组合索引（最左前缀匹配）
创建：
create index unique 索引名称 on 表名（列名，列名）
例如：
create index unique in_name_emali on userinfo(name,email)
删除：
drop index unique 索引名称 on 表名
最左前缀匹配：
顾名思义，就是最左优先
select * from userinfo3 where name='alex' ##使用索引
select * from userinfo3 where name='alex' and email='alex@qq.com' ##使用索引
select * from userinfo3 where email='alex@qq.com' ##不使用索引
联合索引（多列）：
联合主键索引
联合唯一索引
联合普通索引
全文索引：对文本的内容进行分词，进行搜索
覆盖索引：在索引文件中直接获取数据
索引合并：使用多个单列索引组合搜索
创建:
create unique index 索引名称 on 表名（列名）
create unique index 索引名称 on 表名（列名）
查看：
select * from userinfo3 where name = 'alex' and email = 'ssdf'
select * from userinfo3 where name = 'alex'
select * from userinfo3 where email='alex'
组合索引效率 > 索引合并
组合索引 -（name,email）
索引合并 -name
2.相关命令
（1）查看表结构
desc 表名
（2）创建索引
create index in_name on 表名（索引键）
（3）查询email为alex87867@qq.com
(4)删除索引后查询查询email为alex87867@qq.com
（4）和（3）相比，使用的时间要多很多，由于索引是专门用于加速搜索而生，所以加上索引以后，查询效率会快到飞起来。
（5）查看生成表的sql
show create table 表名
（6）查看索引
show index from 表名
3.正确使用索引
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，
则即使建立索引也不会奏效。
即使建立索引，索引也不会生效：
- like '%xx'
select * from tb1 where email like '%cn';
- 使用函数
select * from tb1 where reverse(email) = 'wupeiqi';
- or
select * from tb1 where nid = 1 or name = 'seven@live.com';
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'
- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where email = 999;
- !=
select * from tb1 where email != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
- >
select * from tb1 where email > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
- order by
select name from tb1 order by email desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
4.其他注意事项
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
5.执行计划
让mysql预估执行操作（一般正确）
explain + 查询sql语句 用来显示sql执行信息参数，根据参考信息可以进行sql优化
慢：
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(走索引)
6.慢日志查询
（1）配置mysql自动记录慢日志查看当前配信息：
show variables like '%query%'
修改当前配置：
set global 变量名=值
内容：
slow_query_log = OFF 是否开启慢日志记录
long_query_time = 2 时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log 日志文件
log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
注意：
修改配置文件后，需要重启服务
（2）查看mysql慢日志
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
7.limit分页
在查询数据库时，有时候由于要查询很大的数据，所以这时候需要分批去取数据库表中的全部数据
进行处理，最简单的方法就是使用分页查询语句。
limit子句可以被用于强制select语句返回指定的记录数。limit接受一个或两个数字参数。参数必须是一个
整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的
最大数目。
一般来说通过这样来查询指定的数据
select * from tb1 limit 20,10
但是现在的数据达到百万级的，这样写会非常的慢
limit查询优化
（1）通过子查询的方式来提高分页效率
select * from tb1 where nid>(select nid from tb1 limit 1000000,1) limit 10
没优化前是直接全表扫描去取数据，现在是只扫描索引表再去取数据。优化效果不大，只是快了一些。
（2）显示为“上一页 5 6 7 下一页”这种类型
--上一页
select * from tb1 where nid > 当前页最大值 order by nid asc limit 10
-下一页
select * from tb1 where nid < 当前页最小值 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 每页数据 *【当前页-页码】) as 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 desc limit 每页数据 *【页码-当前页】) as A order by A.nid asc limit 1)
order by
nid desc
limit 10;
