MySQL之索引原理
一、索引原理
1，什么是索引？
索引在MySQL中也叫‘键’或者‘key’，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少IO次数，加快查询。
2，索引的数据结构：b+树
上图就是一个b+树的数据结构，我们的InnoDB索引的数据就是以这种结构存放的。比如说我们要查找29，首先会把磁盘块1加载到内存，发生一次IO，发现29在17和35 中间，就锁定磁盘块中的p2指向的磁盘模块3，然后把磁盘块3加载到内存，发生第二次IO，发现29在26和30之间，就锁定磁盘块3中p2指向的磁盘块8，然后把磁盘块8加载到内存，发生第三次IO，此时和29相比，有一值等于29，从而就找到了29。这样的b+树可以表示上百万的数据，如果上百万的数据查找也只需要三次IO，那么性能提高是非常大的，如果没有索引，每次数据项都要发生一次IO，总共需要上百万次的IO，得花多少时间。
3，b+树的性质
3.1 索引字段要尽量的小
通过上面的例子来看，基本上是b+树的层级高度决定了IO的次数，也决定了查询的效率，所以，要提高效率就应该减少b+树的高度。对于每个磁盘块所能存放的数据量是有限的。假设当前的所有数据大小为N，每个磁盘能存放某个数据的个数为m=磁盘块的大小/每个数据的大小，则高度为h=log（m+1）N，当数据一定时，m越大，h就越小。说明每个数据越小，高度越低，IO次数就越少，效率就更高。所以我们在建立索引的时候喜欢用id字段，而不是name字段，数字类型肯定比char类型占的空间小嘛。
3.2 索引的最左匹配原则特性
查询数据是从数据块的左边开始匹配，再匹配右边的。
二、聚集索引与辅助索引
数据库中的b+树索引可以分为聚集索引和辅助索引
相同点：其内部都是b+树的形式，叶子节点都存放着数据
不同点：聚集索引存放的是一整行所有数据，而辅助索引只存放索引字段的数据+主键
1，聚集索引
#InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。
同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。 #如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。 #如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。 #由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。
它对主键的排序和范围查找熟读非常的快，叶子节点的数据就是用户所要查询的数据，如用户需要查找一张表，查询最后的10位用户信息，由于b+树索引是双向链表，所以用户可以快速查找到最后一个数据夜，并取出10条数据。
#参照第六小结测试索引的准备阶段来创建出表s1 mysql> desc s1; #最开始没有主键 +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(6) | YES | | NULL | | | email | varchar(50) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set (0.00 sec) mysql> explain select * from s1 order by id desc limit 10; #Using filesort，需要二次排序 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2633472 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+ row in set, 1 warning (0.11 sec) mysql> alter table s1 add primary key(id); #添加主键 Query OK, 0 rows affected (13.37 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from s1 order by id desc limit 10; #基于主键的聚集索引在创建完毕后就已经完成了排序，无需二次排序 +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ row in set, 1 warning (0.04 sec)
范围查找，即如果查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据既可。
mysql> alter table s1 drop primary key; Query OK, 2699998 rows affected (24.23 sec) Records: 2699998 Duplicates: 0 Warnings: 0 mysql> desc s1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(6) | YES | | NULL | | | email | varchar(50) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set (0.12 sec) mysql> explain select * from s1 where id > 1 and id < 1000000; #没有聚集索引，预估需要检索的rows数如下，explain就是预估一下你的sql的执行效率 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2690100 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ row in set, 1 warning (0.00 sec) mysql> alter table s1 add primary key(id); Query OK, 0 rows affected (16.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from s1 where id > 1 and id < 1000000; #有聚集索引，预估需要检索的rows数如下 +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1343355 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ row in set, 1 warning (0.09 sec)
2，辅助索引
当我们把id作为主键，但我们查找的时候，并不是以id为条件，而写的是where name=‘dd’时，就没法用到主键索引，此时查找的效率就会很慢，从而我们就需要引入辅助索引，给name添加一个辅助索引。
聚集索引的叶子节点上存放的是一整行的数据，但辅助索引就不是，它的叶子节点只存放了对应字段的数据加上主键。如果我们用辅助索引拿到对应字段的数据，如select name from t1 where name=‘kk’这种我们称为覆盖索引。如果通过辅助索引拿得不是对应字段的数据，如select sex from t1 where name= ‘kk’，我们通过辅助索引是不能直接拿到sex，但我们可以通过辅助索引拿到对应的主键id，再通过聚集索引找到一整行数据，再从一整行数据中拿到sex值，种操作叫回表，
三、MySQL索引管理
1，功能
索引的功能就是加快查找，mysql中primary key，unique ，联合唯一都是索引，这些索引除了加速查找之外，还有约束的功能。
2，MySQL常用的索引
普通索引INDEX：加速查找 唯一索引： -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复） -唯一索引UNIQUE:加速查找+约束（不能重复） 联合索引： -PRIMARY KEY(id,name):联合主键索引 -UNIQUE(id,name):联合唯一索引 -INDEX(id,name):联合普通索引
3，创建/删除索引的语法
#方法一：创建表时 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 表名字;
示范代码
#方式一 create table t1( id int, name char, age int, sex enum('male','female'), unique key uni_id(id), index ix_name(name) #index没有key ); #方式二 create index ix_age on t1(age); #方式三 alter table t1 add index ix_sex(sex); #查看 mysql> show create table t1; | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` char(1) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` enum('male','female') DEFAULT NULL, UNIQUE KEY `uni_id` (`id`), KEY `ix_name` (`name`), KEY `ix_age` (`age`), KEY `ix_sex` (`sex`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
四、正确使用索引
首先，我们得有一个认识，建立索引确实可以帮助我们加速查询，而且可以建立辅助索引，但并不是说我们把每个字段都给加上辅助索引然后就会很方便，其实在索引存在很多的时候，会让应用程序的性能受到影响，在建立多少索引这问题上，应该找到一个平衡点。
其次，在我们添加了索引以后并不是一定查找速度就很快，这和我们如何去查找有关，比如下面几种情况：
1，范围问题、或者条件不明确，条件中出现这些符号或关键字：>,<,>=,<=,!=,between and
出现上面的情况相当于不是精确查找，而是给的一个范围或者是模糊查找，这导致查找的时候还得去一一进行比较，所以效率也不高，当我们查找的范围越大时，肯定是越慢的，当查找范围越小时，肯定是越快的
2，like
当查找条件中出现like时，若没有%，_符号时，如‘xx’就是精确查找，速度很快的；当有符号时，当符号在前时，如‘%xx’,根据最左匹配原则，肯定先匹配%，由于%代表任意字符，所以会导致把所有的数据都要匹配一下，这效率没有提高；但当符号在后就不一样了，如‘xx%’，根据最左匹配原则，先匹配上xx，此时就可以把很多给直接排除，导致速度也很快。
3，尽量选择区分度高的字段作为索引，比如我们选择性别作为索引，不是男的就是女的，在查询的时候回得到很多数据，这样也会很慢，而且没有实际意义
我们编写存储过程为表s1批量添加记录，name字段的值均为egon，也就是说name这个字段的区分度很低（gender字段也是一样的，我们稍后再搭理它） 回忆b+树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即左1<左2<左3<... 而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b+树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。本例中就是这种极端的情况，
name字段所有的值均为'egon' #现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？ #1：如果条件是name='xxxx',那么肯定是可以第一时间判断出'xxxx'是不在索引树中的（因为树中所有的值均为'egon’,看第一条的时候就知道你不在索引树里面了），所以查询速度很快 #2：如果条件正好是name='egon',查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的IO次数没有多大区别，所以速度很慢
4，索引最好不要参与计算，比如把条件写成where id*3=3000,这样相当于每次都要把id拿来计算一下才比，把所有数据都过一遍，很慢的，但我们写成where id = 3000/3，条件是一样的，但销量就高很多
5，and，or
#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) 工作原理: #如果是你找的话，你会怎么找，是不是从左到右一个一个的比较啊，首先你不能确定a这个字段是不是有索引，即便是有索引，也不一定能确保命中索引了（所谓命中索引，就是应用上了索引），mysql不会这么笨的，看下面mysql是怎么找的： 索引的本质原理就是先不断的把查找范围缩小下来，然后再进行处理，对于连续多个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
五、联合索引与覆盖索引
1，联合索引
联合索引就是把表的多个字段合起来作为一个索引。
mysql> create table t( -> a int, -> b int, -> primary key(a), -> key idx_a_b(a,b) -> ); Query OK, 0 rows affected (0.11 sec)
上图就是一个联合索引的数据结构图，是按照（a，b）的顺序进行了存放。
select * from t1 where a=2 and b=1;这是可以使用联合索引加速查询的
select * from t1 where a=3；这也可以用联合索引加速查询的，可以看出是按a排序的
但是，select * from t1 where b=2；这是不能使用联合索引加速查询的，因为不是按b排序，从上到下，从左到右，b的值都是乱的
注意：建立联合索引时，把区分度高的放在前面，即最左边，依次排下去，范围查询放在后面
联合索引第二好处：第一个字段的值相同时，就已经对第二个字段的值进行了排序，上图中的a为1时，b的值从左往右增大的，排好序了的
create table buy_log( userid int unsigned not null, buy_date date ); insert into buy_log values (1,'2009-01-01'), (2,'2009-01-01'), (3,'2009-01-01'), (1,'2009-02-01'), (3,'2009-02-01'), (1,'2009-03-01'), (1,'2009-04-01'); alter table buy_log add key(userid); alter table buy_log add key(userid,buy_date); #===========验证============== mysql> show create table buy_log; | buy_log | CREATE TABLE `buy_log` ( `userid` int(10) unsigned NOT NULL, `buy_date` date DEFAULT NULL, KEY `userid` (`userid`), KEY `userid_2` (`userid`,`buy_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | #可以看到possible_keys在这里有两个索引可以用，分别是单个索引userid与联合索引userid_2,但是优化器最终选择了使用的key是userid因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多 mysql> explain select * from buy_log where userid=2; +----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+ | 1 | SIMPLE | buy_log | ref | userid,userid_2 | userid | 4 | const | 1 | | +----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+ row in set (0.00 sec) #接着假定要取出userid为1的最近3次的购买记录，用的就是联合索引userid_2了，因为在这个索引中，在userid=1的情况下，buy_date都已经排序好了 mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3; +----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+ | 1 | SIMPLE | buy_log | ref | userid,userid_2 | userid_2 | 4 | const | 4 | Using where; Using index | +----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+ row in set (0.00 sec) #ps：如果extra的排序显示是Using filesort，则意味着在查出数据后需要二次排序(如下查询语句，没有先用where userid=3先定位范围，于是即便命中索引也没用，需要二次排序) mysql> explain select * from buy_log order by buy_date desc limit 3; +----+-------------+---------+-------+---------------+----------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+----------+---------+------+------+-----------------------------+ | 1 | SIMPLE | buy_log | index | NULL | userid_2 | 8 | NULL | 7 | Using index; Using filesort | +----+-------------+---------+-------+---------------+----------+---------+------+------+-----------------------------+ #对于联合索引（a,b）,下述语句可以直接使用该索引，无需二次排序 select ... from table where a=xxx order by b; #然后对于联合索引(a,b,c)来首，下列语句同样可以直接通过索引得到结果 select ... from table where a=xxx order by b; select ... from table where a=xxx and b=xxx order by c; #但是对于联合索引(a,b,c)，下列语句不能通过索引直接得到结果，还需要自己执行一次filesort操作，因为索引（a，c)并未排序 select ... from table where a=xxx order by c;
2，覆盖索引
上面讲了，利用辅助索引拿到辅助索引对应字段的值，不需要从聚集索引中拿整行数据的操作叫覆盖索引。辅助索引不包含整行数据，其大小远小于聚集索引，因此减少大量的IO操作。
2.1对于统计问题而言，相较于聚集索引，还是会选择辅助索引，这是优化器的操作结果
mysql> explain select count(*) from buy_log; +----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+ | 1 | SIMPLE | buy_log | index | NULL | userid | 4 | NULL | 7 | Using index | +----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+ row in set (0.00 sec)
2.2对于（a，b）形式的联合索引，一般不可以选择b的查询条件，相当于说查询条件中没有a，只有b时是用不到联合索引的，但是做统计操作时，优化器还是会选择联合索引的
#联合索引userid_2（userid,buy_date）,一般情况，我们按照buy_date是无法使用该索引的，但特殊情况下：查询语句是统计操作，且是覆盖索引，则按照buy_date当做查询条件时，也可以使用该联合索引 mysql> explain select count(*) from buy_log where buy_date >= '2011-01-01' and buy_date < '2011-02-01'; +----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+ | 1 | SIMPLE | buy_log | index | NULL | userid_2 | 8 | NULL | 7 | Using where; Using index | +----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+ row in set (0.00 sec)
六、查询优化器-explain
优化器中，key表示采用的索引是哪一个，rows是核心指标，表示查询过程总共需要查看几条数据，当rows很小的语句执行一定很快的，所以优化语句基本上都是在优化rows。
使用方法：在select语句前加上explain就行了
具体查看http://www.cnblogs.com/yycc/p/7338894.html，关于优化器的详细讲解
七、查询优化的基本步骤
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);
