mysql索引

1.索引数据结构B+树

2.主键与索引之间的关系，回表

3.覆盖索引

mysql> create table T ( ID int primary key, k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '', index k(k)) engine=InnoDB; insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

explain select ID from T where k between 3 and 10; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | T | NULL | range | k | k | 4 | NULL | 4 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from T where k between 3 and 10; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | T | NULL | ALL | k | NULL | NULL | NULL | 6 | 66.67 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

4.联合索引

CREATE TABLE `tuser` ( `id` int(11) NOT NULL, `id_card` varchar(32) DEFAULT NULL, `name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, `ismale` tinyint(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `id_card` (`id_card`), KEY `name_age` (`name`,`age`) ) ENGINE=InnoDB

name age 联合索引

name	age	Id
张六	20	10
张三	20	20
张三	10	30
张三	10	40
张三	10	50

为什么？

优化查询效率。

最左原则

字段匹配；字符匹配；

索引下推

select * from tuser where name like'%张'  and age = 20; 基于索引下推原则，只需回表三次。

alter table T engine = InnoDB；重建索引，节省空间

5.普通索引和唯一索引的区别 

a. change buffer的概念。（主要是对更新操作的优化）

b.普通索引适合多写少读（更新），利用change buffer技术推迟数据入库时间（数据不在缓存中的情况）

c.唯一索引，适合写读（更新）一致或是读大于写，每次写和读都会涉及磁盘io（数据不在缓存中），性能相对较低

d.change buffer， redo log， 系统表空间，数据表空间？

问题：

通过图 2 你可以看到，change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？change buffer 丢失可不是小事儿，再从磁盘读入数据可就没有了 merge 过程，就等于是数据丢失了。会不会出现这种情况呢？

答：数据每次commit操作，都会涉及二次提交，即使奔溃，和以往分析一般操作奔溃时一样处理。

如果操作未提交，已经core，意味这数据正常core丢失；如果已提交，未写redo，未写bin log，丢失

如果已提交，写入redo，未写bin log，丢失

如果已提交，写入redo，写入bin log，可恢复

标准答案：

这个问题的答案是不会丢失，留言区的很多同学都回答对了。虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。

在评论区有同学问到，merge 的过程是否会把数据直接写回磁盘，这是个好问题。这里，我再为你分析一下。

merge 的执行流程是这样的：

从磁盘读入数据页到内存（老版本的数据页）；
从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

极客时间版权所有: https://time.geekbang.org/column/article/71173

 

选错索引

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

前缀索引

mysql> alter table SUser add index index1(email);
mysql> alter table SUser add index index2(email(6));

控制刷脏页的速度innodb_io_capacity

参数 innodb_file_per_table

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。

我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

 

mysql> begin; /* 启动事务 */

/*这个语法的目的是为了解决重复性，当数据库中存在某个记录时，执行这条语句会更新它，而不存在这条记录时，会插入它。

相当于 先判断一条记录是否存在，存在则update，否则insert。

规则是这样的：如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执

行insert语句而不是更新语句。所以 ON DUPLICATE KEY UPDATE是不能写where条件的，例如如下语法是错误的。其语法是：*/

insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;

select relation_ship from `like` where user_id=A and liker_id=B;
/* 代码中判断返回的 relation_ship，
如果是 1，事务结束，执行 commit
如果是 3，则执行下面这两个语句：
*/

/*INSERT IGNORE会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，

达到在间隙中插入数据的目的。*/

insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;