30

        10                          40

   5         15               35          66

1    6    11   19          21   39     55    100

#1. 索引的功能就是加速查找

#2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

普通索引INDEX：加速查找

唯一索引：

    -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）

    -唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：

    -PRIMARY KEY(id,name):联合主键索引

    -UNIQUE(id,name):联合唯一索引

    -INDEX(id,name):联合普通索引

#我们可以在创建上述索引的时候，为其指定索引类型，分两类

hash类型的索引：查询单条快，范围查询慢

btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
        

#方法一：创建表时

    　　CREATE TABLE 表名 (

                字段名1  数据类型 [完整性约束条件…],

                字段名2  数据类型 [完整性约束条件…],

                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY

                [索引名]  (字段名[(长度)]  [ASC |DESC])

                );

#方法二：CREATE在已存在的表上创建索引

        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名

                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

#方法三：ALTER TABLE在已存在的表上创建索引

        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX

                             索引名 (字段名[(长度)]  [ASC |DESC]) ;

#删除索引：DROP INDEX 索引名 ON 表名字;

#无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢

mysql> select * from s1 where id=333333333;

Empty set (0.33 sec)

#1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快

比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。

建完以后，再查询就会很快了。

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。

因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

#1、and与or的逻辑

    条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立

    条件1 or 条件2:只要有一个条件成立则最终结果就成立

#2、and的工作原理

    条件：

        a = 10 and b = 'xxx' and c > 3 and d =4

    索引：

        制作联合索引(d,a,b,c)

    工作原理:

        对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

#3、or的工作原理

    条件：

        a = 10 or b = 'xxx' or c > 3 or d =4

    索引：

        制作联合索引(d,a,b,c)

    工作原理:

        对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

- 避免使用select *

- count(1)或count(列) 代替 count(*)

- 创建表时尽量时 char 代替 varchar

- 表的字段顺序固定长度的字段优先

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

- 尽量使用短索引

- 使用连接（JOIN）来代替子查询(Sub-Queries)

- 连表时注意条件类型需一致

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

#覆盖索引：

    - 所有字段（条件的，查询结果的等）都是索引字段

    http://blog.itpub.net/22664653/viewspace-774667/

#分析

select age from s1 where id=123 and name = 'egon'; #id字段有索引，但是name字段没有索引

该sql命中了索引，但未覆盖全部。

利用id=123到索引的数据结构中定位到了id字段，但是仍要判断name字段，但是name字段没有索引，而且查询结果的字段age也没有索引
最牛逼的情况是，索引字段覆盖了所有，那全程通过索引来加速查询以及获取结果就ok了

#索引合并：把多个单列索引合并使用

#分析：

组合索引能做到的事情，我们都可以用索引合并去解决，比如

create index ne on s1(name,email);#组合索引

我们完全可以单独为name和email创建索引，然后按照where name='xxx' and email='xxx'使用 #索引合并

组合索引可以命中：

select * from s1 where name='egon' ;

select * from s1 where name='egon' and email='adf';

索引合并可以命中：

select * from s1 where name='egon' ;

select * from s1 where email='adf';

select * from s1 where name='egon' and email='adf';

乍一看好像索引合并更好了：可以命中更多的情况，但其实要分情况去看，如果是name='egon' and email='adf',那么组合索引的效率要高于索引合并，如果是单条件查，那么还是用索引合并比较合理

执行计划：让mysql预估执行操作(一般正确)

    all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

    id,email

    慢：

        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(走索引)

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE

1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

3.order by limit 形式的sql语句让排序的表优先查

4.了解业务方使用场景

5.加索引时参照建索引的几大原则

6.观察结果，不符合预期继续从0分析

        慢日志

            - 执行时间 > 10

            - 未命中索引

            - 日志文件路径

        配置：

            - 内存

                show variables like '%query%';

                show variables like '%queries%';

                set global 变量名 = 值

            - 配置文件

                mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'

                my.conf内容：

                    slow_query_log = ON

                    slow_query_log_file = D:/....

                注意：修改配置文件之后，需要重启服务

MySQL日志管理

========================================================

错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息

二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作

查询日志: 记录查询的信息

慢查询日志: 记录执行时间超过指定时间的操作

中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放

通用日志： 审计哪个账号、在哪个时段、做了哪些事件

事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等

========================================================

一、bin-log

1. 启用

# vim /etc/my.cnf

[mysqld]

log-bin[=dir\[filename]]

# service mysqld restart

2. 暂停

//仅当前会话

SET SQL_LOG_BIN=0;

SET SQL_LOG_BIN=1;

3. 查看

查看全部：

# mysqlbinlog mysql.000002

按时间：

# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"

# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"

# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 

按字节数：

# mysqlbinlog mysql.000002 --start-position=260

# mysqlbinlog mysql.000002 --stop-position=260

# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930

4. 截断bin-log（产生新的bin-log文件）

a. 重启mysql服务器

b. # mysql -uroot -p123 -e 'flush logs'

5. 删除bin-log文件

# mysql -uroot -p123 -e 'reset master' 

二、查询日志

启用通用查询日志

# vim /etc/my.cnf

[mysqld]

log[=dir\[filename]]

# service mysqld restart

三、慢查询日志

启用慢查询日志

# vim /etc/my.cnf

[mysqld]

log-slow-queries[=dir\[filename]]

long_query_time=n

# service mysqld restart

MySQL 5.6:

slow-query-log=1

slow-query-log-file=slow.log

long_query_time=3

查看慢查询日志

测试:BENCHMARK(count,expr)

SELECT BENCHMARK(50000000,2*3);