索引补充（索引种类，正确使用索引,其他注意事项,慢日志查询）

1.索引

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

2.索引种类

• 普通索引：仅加速查询
• 唯一索引：加速查询 + 列值唯一（可以有null）
• 主键索引：加速查询 + 列值唯一 +　表中只有一个（不可以有null）
• 组合索引：多列值组成一个索引，
                专门用于组合搜索，其效率大于索引合并
• 全文索引：对文本的内容进行分词，进行搜索 

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

3.相关命令

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

5.正确使用索引（以下均是错误示范）   面试中提问：创建了索引为什么命中不了索引，下面就是解决方法

- 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';      #nid有索引，email没有索引，使用or这种情况就不会用到索引了，查询时间变长
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'      #这种用and后面连接name索引
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    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                -- 不使用索引

6.其他注意事项

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

7.执行计划 （让mysql预估执行操作，不会真实的执行语句）

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行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)　　

type是ALL是全表扫描

8.慢日志查询

a、配置MySQL自动记录慢日志，直接修改服务器的配置

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

注意：服务端查看当前配置信息，执行下面命令：
　　   show variables like '%query%';
     修改当前配置，执行下面命令：
　　　　set global 变量名 = 值

b.自定义mysql慢日志配置文件

mysqld  --default -file='配置文件路径'

配置文件就是上一步的内容，配置完之后需要重启服务

c.查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

9.limit分页

select * from tb1 limit 10,10;     #从第十一条数据开始查询10条，第一个数字越大查询速度越慢

解决方法：

a.博客园的分页解决方法：

　　限制观看页数，首页最多看200页

b.不去数据表中查询，去索引表    （这种方式速度也不会很快）

select * from tb1 where id in(select id from tb1 limit 20000,10);    #limit先去id索引表中查询

c.记录当前页的最大或最小id  (当前最好方法)

select * from tb1 where id >20 limit 10;   #先根据索引id查询大于20的，再获取十条数据，这样速度更快

反取十条数据

select * from tb1 where id <20 order by id desc limit 10;    #查询小于20的数据，倒序排列，取十条数据