mysql小结篇3 索引、分页、执行计划--（17.6.28）
一、索引
1.介绍
索引，类似字典的目录，查询时可以快速获取想要的数据。
2.常见的索引
2.1普通索引（key）：
创建
key ix_tb1 (name) 或者 index ix_tb1 (name) 这两种方法都可以；
CREATE TABLE `tb1` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`name` char(32) DEFAULT NULL,
`email` char(32) DEFAULT NULL,
PRIMARY KEY (`nid`),
KEY `ix_tb1` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
添加
create index ix_tb1 on tb1(name)
删除
alter table tb1 drop key ix_tb1
2.2唯一索引（unique）
创建 ： unique(列名)
CREATE unique index un_123 on tb3(name)
删除：
alter table tb3 drop index name
2.3主键索引（primary key）
创建：
一个表只能有一个主键，
primary key (nid)
添加：
在没有主键的情况下可以添加
alter table tb4 add primary key(name)
删除：
alter table tb4 drop primary key
2.4组合索引
其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。
创建：
create index ix_name on tb4(name,email)
删除：
alter table tb4 drop key ix_name
3.相关命令
查看表结构 desc 表名
查看创建表的sql 语句 show create table 表名；
查看索引 show index from 表名；
4.正确使用索引
- 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;
- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
5.其它的注意事项
1- 避免使用select * 2- count(1)或count(列) 代替 count(*) 3- 创建表时尽量时 char 代替 varchar 4- 表的字段顺序固定长度的字段优先 5- 组合索引代替多个单列索引（经常使用多个条件查询时） 6- 尽量使用短索引 7- 使用连接（JOIN）来代替子查询(Sub-Queries) 8- 连表时注意条件类型需一致 9- 索引散列值（重复少）不适合建索引，例：性别不适合
二、分页思路
1.方法一
2.方法二
三、执行计划（explain）
1.干嘛的
答：检查sql语句是什么索引，只能帮你select 如：explain select * from tb2;
执行的结果：
a. type = ALL 表示sql语句是全局扫描，没走索引；
mysql> explain select * from tb2; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
2.使用方法
a.当type = all 或 index 表示搜索效率不高，需要改进；
id 查询顺序标识 如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B; +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL | | 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ 特别的：如果使用union连接气值可能为null select_type 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果 ... table 正在访问的表名 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; possible_keys 可能使用的索引 key 真实使用的 key_len MySQL中使用索引字节长度 rows mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
四、慢日志
1.作用
记录sql语句执行的时间
2.使用方法
a. 开启 慢日志 记录 及相关设置
查看 慢日志状态 ： show variables like '%query%'
设置慢日志 ： set global slow_query_long = on 即为开启慢日志； 其他操作雷同；
查看日志： mysqldumpslow 没能运行起来，先暂停。
slow_query_log = OFF 是否开启慢日志记录 long_query_time = 2 时间限制，超过此时间，则记录 slow_query_log_file = /usr/slow.log 日志文件 log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
