MySQL知识点巩固

什么是索引

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。
假设我们创建了一个名为people的表:
create table people (peopleid SMALLINT NOT NULL,name CHAR(50) NOT NULL);
然后，我们完全随机把1000个不同name值插入到people表。
如果我们创建了name列的索引，MySQL将在索引中排序name列：
对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此，如果我们要查找name等于“Mike”记录的peopleid（SQL命令为“SELECT peopleid FROM people WHERE name=’Mike’;”），MySQL能够在name的索引中查找“Mike”值，然后直接转到数据文件中相应的行，准确地返回该行的peopleid（999）。在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，需要MySQL处理的记录数量越少，则它完成任务的速度就越快。
普通索引:仅加速查询
唯一索引:加速查询+列值唯一(可以有null)
主键索引:加速查询+列值唯一表中只有一个(不可以有null)
组合索引:多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引:对文本的内容进行分词，进行搜索。
索引合并:使用多个单列索引组合搜索
覆盖索引:select的数据列只用从索引中就能够取得,不必读取数据行，换句话说查询列要被所建的索引覆盖。

如何创建索引

创建表的时候创建索引

create table tbl name(
字段名陈 字段类型 [完整性约束条件],
,,,,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名称]  (字段名称[长度])[ASC|DESC]
);

在已经存在的表上创建索引:

1、create [unique|fulltext|spatial] index 索引名称 on 表名{字段名称[(长度)]  [ASC|DESC]}
2、alter table ta1_name add [unique|fulltext|spatial] index 索引名称(字段名陈[(长度)]  [ASC|DESC])

删除索引?

drop index 索引名称 on tb1_name;

普通索引

普通索引仅有一个功能:加速查询
创建表+索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

创建索引

create index index_name on table_name(column_name)

删除索引

drop index_name on table_name;

查看索引

show index from table_name;

注意:对于创建索引时如果是BLOB和TEXT类型，必须指定length。

create index ix_extra on in1(extra(32));

唯一性索引

唯一索引有两个功能:加速查询和唯一约束(可含null)
创建表+唯一索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

创建唯一索引

create unique index 索引名 on 表名(列名)

删除索引

drop unique index 索引名 on 表名

主键索引

主键有两个功能:加速查询和唯一约束(不可含null)
创建表+创建主键

create table in1(
	nid int not null auto_increment primary key,
	name varchar(32) not null,
	email varchar(64) not null,
	extra text,
	index ix_name(name)
)
or
create table in1(
	nid int not null auto_increment,
	name varchar(32) not null,
	email varchar(64) not null,
	extra text,
	primary key(nid),
	index ix_name(name)
)

创建主键

alter table 表名 add primary key(列名);

删除主键

alter table 表名 drop primary key;
alter table 表名 modify 列名 int,drop primary key;

组合索引

组合索引是将n个列组合成一个索引
其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = ‘alex’ and n2 = 666。
创建表

create table in3(
	nid int not null auto_increment primary key,
	name varchar(32) not null,
	email varchar(64) not null,
	extra text
)

创建组合索引

create index ix_name_email on in3(name,email);

如上创建组合索引之后，查询:
name and email –使用索引
name –使用索引
email –不使用索引
注意:对于同时搜索n个条件时，组合索引的性能好于多个单列索引合并。
相关命令

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

使用索引和不使用索引

由于索引时专门用于加速搜索而生，所以加上索引之后，查询效率会快到飞起来。
# 有索引
mysql > select * from tb1 where name = 'wupeiqi-888';
+-----+-------------+---------------------+----------------------------------+---------------------+
| nid | name        | email               | radom                            | ctime               |
+-----+-------------+---------------------+----------------------------------+---------------------+
| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |
+-----+-------------+---------------------+----------------------------------+---------------------+
1 row in set (0.00 sec)

# 无索引
mysql> select * from tb1 where email = 'wupeiqi888@live.com';
+-----+-------------+---------------------+----------------------------------+---------------------+
| nid | name        | email               | radom                            | ctime               |
+-----+-------------+---------------------+----------------------------------+---------------------+
| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |
+-----+-------------+---------------------+----------------------------------+---------------------+
1 row in set (1.23 sec)

正确使用索引
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效：

- 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                -- 不使用索引

其他注意事项

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

limit 分页
无论是否有索引，limit分页是一个值得关注的问题

每页显示10条：
当前 118 120， 125

倒序：
            大      小
            980    970  7 6  6 5  54  43  32

21 19 98     
下一页：

    select 
        * 
    from 
        tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1)  
    order by 
        nid desc 
    limit 10;

    select 
        * 
    from 
        tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid < 970  order by nid desc limit 40) A order by A.nid asc limit 1)  
    order by 
        nid desc 
    limit 10;

上一页：

    select 
        * 
    from 
        tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1)  
    order by 
        nid desc 
    limit 10;

    select 
        * 
    from 
        tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1)  
    order by 
        nid desc 
    limit 10;

执行计划
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)

执行计划

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列中索引的位图，并且是冗余的。

慢日志查询
a、配置MySQL自动记录慢日志

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

注：查看当前配置信息：
　　 show variables like ‘%query%’
修改当前配置：
　　　　set global 变量名 = 值
b、查看mysql慢日志
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
参数详解

--verbose    版本
--debug      调试
--help       帮助

-v           版本
-d           调试模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前N条just show the top n queries
-a           不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配；grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间；don't subtract lock time from total time

全文索引

创建全文索引(FullText index)
旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。
不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引，所以具体信息要随时关注官网，
创建表的同时创建全文索引

create table article(
	id int auto_increment not null primary key,
	title varchar(200),
	body text,
	fulltext(title,body)
)type=myisam;

通过alter table的方式来添加
alter table ‘student’ add fulltext index ft_stu_name(‘name’)#ft_stu_name是索引名，可以随便起
或者:alter table ‘student’ add fulltext ft_stu_name(‘name’)
直接通过create index的方式
create fulltext index ft_email_name on ‘student’(‘name’)
也可以在创建索引的时候指定索引的长度:
create fulltext index ft_email_name on ‘student’(‘name’(20))
删除全文索引
直接使用drop index(注意：没有 drop fulltext index 这种用法)
drop index full_idx_name on tommy.girl;
使用alter table的方式
alter table tommy.girl drop index ft_email_abck;
使用全文索引
跟普通索引稍有不同
使用全文索引的格式： MATCH (columnName) AGAINST (‘string’)
eg:
select from ‘student’ where match(‘name’) against(‘聪’);
当查询多列数据时:
建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。
select from ‘student’ where match(‘name’,’address’) AGAINST(‘聪 广东’);
使用全文索引需要注意的是:(基本单位是词)
分词，全文索引以词为基础，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符
MySQL中与全文索引相关的几个变量：
使用命令：mysql> SHOW VARIABLES LIKE ‘ft%’; #ft就是FullText的简写
ft_boolean_syntax + -><()~:””&| #改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引
ft_min_word_len 4 #最短的索引字符串，默认值为4，(通常改为1)修改后必须重建索引文件
重新建立索引命令：repair table tablename quick
ft_max_word_len 84 #最长的索引字符串，默认值为84，修改后必须重建索引文件
ft_query_expansion_limit 20 #查询括展时取最相关的几个值用作二次查询
ft_stopword_file (built-in) #全文索引的过滤词文件，具体可以参考：MySQL全文检索中不进行全文索引默认过滤词
特别注意：50%的门坎限制(当查询结果很多，几乎所有记录都有，或者极少的数据，都有可能会返回非所期望的结果)
–>可用IN BOOLEAN MODE即可以避开50%的限制。
此时使用全文索引的格式就变成了： SELECT FROM student WHERE MATCH(name) AGAINST(‘聪’ IN BOOLEAN MODE)
ft_boolean_syntax (+ -><()~:””&|)使用的例子：
4.1 + : 用在词的前面，表示一定要包含该词，并且必须在开始位置。
eg: +Apple 匹配：Apple123, “tommy, Apple”
4.2 - : 不包含该词，所以不能只用「-yoursql」这样是查不到任何row的，必须搭配其他语法使用。
eg: MATCH (girl_name) AGAINST (‘-林志玲 +张筱雨’)匹配到： 所有不包含林志玲，但包含张筱雨的记录
4.3. 空(也就是默认情况)，表示可选的，包含该词的顺序较高。
例子：apple banana 找至少包含上面词中的一个的记录行
+apple +juice 两个词均在被包含
+apple macintosh 包含词 “apple”，但是如果同时包含 “macintosh”，它的排列将更高一些
+apple -macintosh 包含 “apple” 但不包含 “macintosh”
4.4. > :提高该字的相关性，查询的结果会排在比较靠前的位置。
4.5.< :降低相关性，查询的结果会排在比较靠后的位置。
例子：4.5.1.先不使用 ><
select from tommy.girl where match(girl_name) against(‘张欣婷’ in boolean mode);
可以看到完全匹配的排的比较靠前
4.5.2. 单独使用 >
select from tommy.girl where match(girl_name) against(‘张欣婷 >李秀琴’ in boolean mode);
使用了>的李秀琴马上就排到最前面了
4.5.3. 单独使用 <
select from tommy.girl where match(girl_name) against(‘张欣婷 <不是人’ in boolean mode);
看到没，不是人也排到最前面了，这里使用的可是 < 哦，说好的降低相关性呢，往下看吧。
4.5.4.同时使用><
select * from tommy.girl where match(girl_name) against(‘张欣婷 >李秀琴 <练习册 <不是人>是个鬼’ in boolean mode);
到这里终于有答案了，只要使用了 ><的都会往前排，而且>的总是排在<的前面
小结一下：1. 只要使用 ><的总比没用的 靠前；

2. 使用 >的一定比 <的排的靠前 (这就符合相关性提高和降低)；
3. 使用同一类的，使用的越早，排的越前。
   4.6. ( )：可以通过括号来使用字条件。
   eg: +aaa +(>bbb < ccc) // 找到有aaa和bbb和ccc，aaa和bbb，或者aaa和ccc(因为bbb，ccc前面没有+，所以表示可有可无),然后 aaa&bbb > aaa&bbb&ccc > aaa&ccc
   4.7. ~ :将其相关性由正转负，表示拥有该字会降低相关性，但不像「-」将之排除，只是排在较后面。
   eg: +apple ~macintosh 先匹配apple，但如果同时包含macintosh，就排名会靠后。
   4.8. :通配符，这个只能接在字符串后面。
   MATCH (girl_name) AGAINST (‘+ABC‘) #错误，不能放前面
   MATCH (girl_name) AGAINST (‘+张筱雨‘) #正确
   4.9. “ “ :整体匹配，用双引号将一段句子包起来表示要完全相符，不可拆字。
   eg: “tommy huang” 可以匹配 tommy huang xxxxx 但是不能匹配 tommy is huang。
   5.补充：Windows下无法修改 ft_min_word_len的情况，
4. 1. 使用cmd打开 services.msc，
      找到你的 MySQL服务，右键Properties,找到你的my.ini所在的路径
      
      5.2. 停止MySQL,在my.ini中增加 ft_min_word_len = 1,重启MySQL,
      然后使用命令 show variables like ‘ft_min_word_len’; 查看是否生效了

      单列索引与多列索引