What is an index? Mysql currently has several main index types

1. Index

The establishment of MySQL index is very important for the efficient operation of MySQL. The index can greatly improve the retrieval speed of MySQL.

For example, if MySQL that is properly designed and uses indexes is a Lamborghini, then MySQL that is not designed and uses indexes is a human tricycle.

Indexes are divided into single column indexes and combined indexes. A single-column index means that an index only contains a single column. A table can have multiple single-column indexes, but this is not a combined index. Combined index, that is, an index contains multiple columns.

When creating an index, you need to ensure that the index is a condition applied to the SQL query statement (usually as a condition of the WHERE clause).

In fact, the index is also a table, which saves the primary key and index fields and points to the records of the entity table.

The above all talk about the benefits of using indexes, but excessive use of indexes will cause abuse. Therefore, the index will also have its shortcomings: although the index greatly improves the query speed, it will also reduce the speed of updating the table, such as INSERT, UPDATE and DELETE on the table. Because when updating the table, MySQL not only needs to save the data, but also save the index file.

Creating index files will occupy disk space.

2. Index type

Mysql currently mainly has the following index types: FULLTEXT, HASH, BTREE, RTREE.

1. FULLTEXT

is the full-text index, currently only supported by the MyISAM engine. It can be used in CREATE TABLE, ALTER TABLE, and CREATE INDEX, but currently only full-text indexes can be created on CHAR, VARCHAR, and TEXT columns.

Full-text index was not born together with MyISAM. It appeared to solve the problem of low efficiency of fuzzy query for text such as WHERE name LIKE "%word%".

2. HASH

Because HASH is unique (almost 100% unique) and has a key-value pair-like form, it is very suitable as an index.

HASH index can be located once and does not need to be searched layer by layer like a tree index, so it is extremely efficient. However, this efficiency is conditional, that is, it is only efficient under the "=" and "in" conditions, and is still not efficient for range queries, sorting, and combined indexes.

3. BTREE

The BTREE index is a method that stores the index value into a tree-shaped data structure (binary tree) according to a certain algorithm. Each query is started from the entrance of the tree. Starting from the root, traverse the nodes in order to obtain the leaf. This is the default and most commonly used index type in MySQL.

4. RTREE

RTREE is rarely used in MySQL and only supports the geometry data type. The only storage engines that support this type are MyISAM, BDb, InnoDb, NDb, and Archive.

Compared with BTREE, the advantage of RTREE lies in range search.

ps. For details of this paragraph, please see this blog post: The differences and applicability of several Mysql index types

3. Index types

• General Index: Accelerate query only

• Unique index: Accelerate query + column value is unique (can have null)

• Primary key index: Accelerate query + column Unique value (cannot have null) + There is only one

• combined index in the table: multiple column values ​​form an index, specifically used for combined searches, and its efficiency is greater than index merging

• Full text index: segment the text content and search

ps.

Index merging, use multiple single column index combinations to search
Covering index, the selected data column can only be obtained from the index without reading the data row. In other words, the query column must be covered by the built index

4. Index operation

1. Create an index

--创建普通索引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);

2. Create an index by modifying the table structure

ALTER TABLE table_name ADD INDEX index_name(col_name);

3. Directly specify the index when creating the table

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

4. Delete the index

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

5. Other related commands

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

5. The timing of creating an index

So far we have learned to create an index, so under what circumstances do we need to create an index? Generally speaking, columns appearing in WHERE and JOIN need to be indexed, but this is not entirely true because MySQL only indexes <, <=, =, >, >=, BETWEEN, IN, and sometimes LIKE will use the index. For example:

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

At this time, it is necessary to index city and age. Since the userame of the mytable_m table also appears in the JOIN clause, it is also necessary to index it.

I just mentioned that only LIKE needs to be indexed at certain times. Because MySQL will not use the index when making queries starting with wildcard characters % and _.

6. Hit Index

Adding an index to the database table will indeed make the query speed take off, but the premise must be that the index is used correctly for querying. If it is used in the wrong way, even if it is created Indexing will also not work.
Even if the index is created, the index will not take effect:

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

7. Other precautions

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

八、LIMIT分页

若需求是每页显示10条数据，如何建立分页？

我们可以先使用LIMIT尝试：

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

但是这样做有如下弊端：

• 每一条select语句都会从1遍历至当前位置，若跳转到第100页，则会遍历1000条记录

• 若记录的id不连续，则会出错

改善：

若已知每页的max_id和min_id，则可以通过主键索引来快速定位:

--下一页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;

九、执行计划

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/constALL             全表扫描，对于数据表从头到尾找一遍select * from tb1;
                            特别的：如果有limit限制，则找到之后就不再继续向下扫描                                   select * from tb1 where email = &#39;seven@live.com&#39;   select * from tb1 where email = &#39;seven@live.com&#39; limit 1;
                                   虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。INDEX           全索引扫描，对索引从头到尾找一遍select nid from tb1;

            RANGE          对索引列进行范围查找select *  from tb1 where name < &#39;alex&#39;;
                            PS:between andin>   >=  <   <=  操作
                                注意：!= 和 > 符号


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

上表详解

十、慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，MySQLl数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。 

1. 查看慢日志参数：

--查询配置命令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--

2. 修改当前配置

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

ps.也可以直接打开慢日志配置文件进行修改，但必须重启服务才能生效

3. 查看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
"""

 

The above is the detailed content of What is an index? Mysql currently has several main index types. For more information, please follow other related articles on the PHP Chinese website!