什么是索引？Mysql目前主要的几种索引类型

--创建普通索引CREATE INDEX index_name ON table_name(col_name);--创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);--创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);--创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

ALTER TABLE table_name ADD INDEX index_name(col_name);

CREATE TABLE table_name (
    ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);

--直接删除索引DROP INDEX index_name ON table_name;--修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;

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

SELECT t.Name  FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;

- 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)- 连表时注意条件类型需一致- 索引散列值（重复多）不适合建索引，例：性别不适合

--第一页SELECT * FROM table_name LIMIT 0,10;--第二页SELECT * FROM table_name LIMIT 10,10;--第三页SELECT * FROM table_name LIMIT 20,10;

--下一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);--上一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);--当前页之后的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (页数差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);--当前页之前的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (页数差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P) ORDER BY id ASC;

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)

--查询配置命令show variables like '%query%';--当前配置参数binlog_rows_query_log_events    OFFft_query_expansion_limit    20have_query_cache    YES--时间限制，超过此时间，则记录long_query_time    10.000000query_alloc_block_size    8192query_cache_limit    1048576query_cache_min_res_unit    4096query_cache_size    1048576query_cache_type    OFFquery_cache_wlock_invalidate    OFFquery_prealloc_size    8192--是否开启慢日志记录slow_query_log    OFF--日志文件slow_query_log_file    D:\Program Files (x86)\mysql-5.7.18-winx64\data\Jack-slow.log--

set global 变量名 = 值;--例如，修改时间限制为20slong_query_time = 20;

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
"""