MySQL索引
索引的作用就是约束条件加加速查找的功能，没有索引就是遍历操作速度很慢。
索引的种类：
单列索引：
主键索引：加速查找 + 不能为空 + 不能重复。
创建表时创建主键索引 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(ni1), index ix_name (name) )
创建主键 alter table 表名 add primary key(列名); 删除主键 alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key;
普通索引：加速查找。
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 表名
多列组成一个索引：
联合索引（多列）：联合主键索引、联合唯一索引、联合普通索引。
注：创建索引时会生成一个关于这个索引值相关的一个某种格式存储的文件。创建索引的时候会遍历整个表很慢，创建好这个文件以后，下一次查找先从这个表里找，找到的话速度就很快，不在这个文件里还是会遍历整个数据库。
MySQL里索引种类（某种格式存储）：
hash索引表： 把你作为索引的键写成hash值，然后把对应的内存地址绑定，hash表中的顺序时乱序的（id>3就很慢），单值快。
btree索引：二叉树法，查找速度很快，使用的更广。
建立索引，额外的文件保存特殊的数据结构，查询快（前提要命中查询）插入更新删除慢。
select * from userinfo3 where email like 'asdf';--依然很慢。
索引中的几个名词：
覆盖索引：查找的列就是我们那个存储的特殊格式的文件里的列，信息可以直接在索引文件查到而不需要到表里查，比如id时索引，如果select id直接就在索引表取到id数据，如果要查*那么还要去数据表中查全部。
索引合并：多列都是索引时查找，把多个单列索引合并使用。
最左前缀匹配：
create index ix_name_email on userinfo3(name,email,)--两个索引 select * from userinfo3 where name='alex'; select * from userinfo3 where name='alex' and email='asdf'; select * from userinfo3 where email='alex@qq.com';#不走索引 --适用于组合索引
组合索引的效率要大于索引合并。
- like '%xx' select * from tb1 where email like '%cn'; - 使用函数 select * from tb1 where reverse(email) = 'wupeiqi'; - or后面有非索引也不会命中 select * from tb1 where nid = 1 or name = 'seven@live.com'; 特别的：当or条件中有未建立索引的列才失效，以下会走索引 select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'走索引 - 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然会很慢 select * from tb1 where email = 999; - != select * from tb1 where email != 'alex' 特别的：如果是主键，则还是会走索引 select * from tb1 where nid != 123 - > select * from tb1 where email > 'alex' 特别的：如果是主键或索引是整数类型，则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123 - order by select name from tb1 order by email 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) 连表时注意条件类型需一致 索引散列值（重复少）不适合建索引，例：性别不适合
通过EXPLAIN可以预估SQL的执行时间：
mysql> explain select * from t1 where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
效率排名all （全表扫描）< index < range < index_merge < ref_or_null < ref < eq_ref < system/const（索引），仅供查询，不一定准，大部分是这样。
对于慢日志的查询：
show variables like '%query%';--基于内存
slow_query_log = OFF 是否开启慢日志记录 long_query_time = 2 时间限制，超过此时间，则记录 slow_query_log_file = /usr/slow.log 日志文件 log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
set global 变量名 = 值
还可以找到配置文件直接修改。
优化分页
对于常规的select * from userinfo3 limit 20,10;这种分页，当页数很多时效率是非常低的，所以简单的说两种解决方案。
1.只能翻上下页。
【下一页】：select * from table_name where id > max_id limit 10; 【上一页】：select * from table_name where id < min_id order by id desc limit 10;
下一页： mysql> select * from t1 where id >0 limit 10; +----+--------+---------------+------+------+ | id | name | email | type | dep | +----+--------+---------------+------+------+ | 1 | test1 | test1@qq.com | 1 | 2 | | 2 | test2 | test2@qq.com | 1 | 2 | | 3 | test3 | test3@qq.com | 1 | 2 | | 4 | test4 | test4@qq.com | 1 | 2 | | 5 | test5 | test5@qq.com | 1 | 2 | | 6 | test6 | test6@qq.com | 1 | 2 | | 7 | test7 | test7@qq.com | 1 | 2 | | 8 | test8 | test8@qq.com | 1 | 2 | | 9 | test9 | test9@qq.com | 1 | 2 | | 10 | test10 | test10@qq.com | 1 | 2 | +----+--------+---------------+------+------+ 10 rows in set (0.00 sec) mysql> select * from t1 where id >10 limit 10; +----+--------+---------------+------+------+ | id | name | email | type | dep | +----+--------+---------------+------+------+ | 11 | test11 | test11@qq.com | 1 | 2 | | 12 | test12 | test12@qq.com | 1 | 2 | | 13 | test13 | test13@qq.com | 1 | 2 | | 14 | test14 | test14@qq.com | 1 | 2 | | 15 | test15 | test15@qq.com | 1 | 2 | | 16 | test16 | test16@qq.com | 1 | 2 | | 17 | test17 | test17@qq.com | 1 | 2 | | 18 | test18 | test18@qq.com | 1 | 2 | | 19 | test19 | test19@qq.com | 1 | 2 | | 20 | test20 | test20@qq.com | 1 | 2 | +----+--------+---------------+------+------+ 10 rows in set (0.00 sec) mysql> select * from t1 where id >30 limit 10; +----+--------+---------------+------+------+ | id | name | email | type | dep | +----+--------+---------------+------+------+ | 31 | test31 | test31@qq.com | 1 | 2 | | 32 | test32 | test32@qq.com | 1 | 2 | | 33 | test33 | test33@qq.com | 1 | 2 | | 34 | test34 | test34@qq.com | 1 | 2 | | 35 | test35 | test35@qq.com | 1 | 2 | | 36 | test36 | test36@qq.com | 1 | 2 | | 37 | test37 | test37@qq.com | 1 | 2 | | 38 | test38 | test38@qq.com | 1 | 2 | | 39 | test39 | test39@qq.com | 1 | 2 | | 40 | test40 | test40@qq.com | 1 | 2 | +----+--------+---------------+------+------+ 10 rows in set (0.00 sec) 上一页： mysql> select * from t1 where id < 41 order by id desc limit 10; +----+--------+---------------+------+------+ | id | name | email | type | dep | +----+--------+---------------+------+------+ | 40 | test40 | test40@qq.com | 1 | 2 | | 39 | test39 | test39@qq.com | 1 | 2 | | 38 | test38 | test38@qq.com | 1 | 2 | | 37 | test37 | test37@qq.com | 1 | 2 | | 36 | test36 | test36@qq.com | 1 | 2 | | 35 | test35 | test35@qq.com | 1 | 2 | | 34 | test34 | test34@qq.com | 1 | 2 | | 33 | test33 | test33@qq.com | 1 | 2 | | 32 | test32 | test32@qq.com | 1 | 2 | | 31 | test31 | test31@qq.com | 1 | 2 | +----+--------+---------------+------+------+ 10 rows in set (0.00 sec) mysql> select * from t1 where id < 31 order by id desc limit 10; +----+--------+---------------+------+------+ | id | name | email | type | dep | +----+--------+---------------+------+------+ | 30 | test30 | test30@qq.com | 1 | 2 | | 29 | test29 | test29@qq.com | 1 | 2 | | 28 | test28 | test28@qq.com | 1 | 2 | | 27 | test27 | test27@qq.com | 1 | 2 | | 26 | test26 | test26@qq.com | 1 | 2 | | 25 | test25 | test25@qq.com | 1 | 2 | | 24 | test24 | test24@qq.com | 1 | 2 | | 23 | test23 | test23@qq.com | 1 | 2 | | 22 | test22 | test22@qq.com | 1 | 2 | | 21 | test21 | test21@qq.com | 1 | 2 | +----+--------+---------------+------+------+ 10 rows in set (0.00 sec)
2.上一页 192 193 [196] 197 198 199 下一页，从当前页196到199页
select * from userinfo3 where id in ( select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10）
