mysql

• mysql: 安装 https://www.iteye.com/blog/lobin-2517170

• mysql: 源代码安装 https://www.iteye.com/blog/lobin-2517171

 

初始化

$ ./bin/mysqld --initialize --user=mysql --datadir=/tmp/mysql-5.7.37-linux-glibc2.12-x86_64/data

2023-03-19T15:50:25.064795Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2023-03-19T15:50:25.064882Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.

2023-03-19T15:50:25.944817Z 0 [Warning] InnoDB: New log files created, LSN=45790

2023-03-19T15:50:26.059737Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2023-03-19T15:50:26.125012Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: c40c9ffa-c66d-11ed-9ab2-00163e145f5c.

2023-03-19T15:50:26.125897Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2023-03-19T15:50:27.091304Z 0 [Warning] 

2023-03-19T15:50:27.091351Z 0 [Warning] 

2023-03-19T15:50:27.091982Z 0 [Warning] CA certificate ca.pem is self signed.

 

修改root密码

$ ./bin/mysqladmin --user=root -p password

Enter password: 

New password: 

Confirm new password: 

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

 

mysql配置

 

Default options are read from the following files in the given order:

 

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

 

以上提示信息可以通过./mysqld --verbose --help命令看到。

 

配置文件

 

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove the leading "# " to disable binary logging

# Binary logging captures changes between backups and is enabled by

# default. It's default setting is log_bin=binlog

# disable_log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

#

# Remove leading # to revert to previous value for default_authentication_plugin,

# this will increase compatibility with older clients. For background, see:

# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin

# default-authentication-plugin=mysql_native_password

 

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

 

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

启动

# mysqld --user=mysql &

# ps -ef | grep mysql

mysql     4269  3242 33 15:23 pts/1    00:00:31 mysqld --user=mysql

# netstat -anop | grep 3306

tcp        0      0 :::3306                     :::*                        LISTEN      4269/mysqld         off (0.00/0/0)

tcp        0      0 :::33060                    :::*                        LISTEN      4269/mysqld         off (0.00/0/0)

 

# mysqld_safe --user=mysql &

# ps -ef | grep mysql

root      4342  3242  1 15:25 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --user=mysql

mysql     4471  4342 37 15:26 pts/1    00:00:28 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

 

mysql启动的时候，参数选项还有顺序的限制。如--defaults-file

 

$ sudo ./bin/mysqld_safe --user=mysql --defaults-file=./my.cnf 

Logging to '/usr/local/mysql/data/admindeMacBook-Air.local.err'.

2022-05-24T12:36:47.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

 

2022-05-24T12:36:49.6NZ mysqld_safe mysqld from pid file /usr/local/mysql/data/admindeMacBook-Air.local.pid ended

这里在启动的时候--defaults-file放在最后的话，mysql启动失败：

2022-05-24T12:32:58.323297Z 0 [ERROR] unknown variable 'defaults-file=./my.cnf'

 

2022-05-24T12:32:58.323319Z 0 [ERROR] Aborting

 

将--defaults-file选项放在前面的话，就可以启动起来了：

 

$ sudo ./bin/mysqld_safe --defaults-file=./my.cnf --user=mysql

 

# netstat -anop | grep 3306

tcp        0      0 :::3306                     :::*                        LISTEN      4471/mysqld         off (0.00/0/0)

tcp        0      0 :::33060                    :::*                        LISTEN      4471/mysqld         off (0.00/0/0)

 

连接

客户端连接mysql的时候需要指定正确的字符集，如果字符集不正确的话，将会出现乱码。这在包含中文的时候经常出现乱码。

 

包括在使用MySQL C API连接mysql的时候，尤其需要自己手动指定这个字符集：

 set names utf8

 可以向mysql服务器发送以下请求：

char *sql = "set names utf8";

mysql_query(mysql, sql); 

 

mysql> CREATE USER 'root'@'%' IDENTIFIED BY '**********';

Query OK, 0 rows affected (0.19 sec)

 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Query OK, 0 rows affected (0.33 sec)

 

或者

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'jxcoco1128' WITH GRANT OPTION;

Query OK, 0 rows affected, 1 warning (0.06 sec)

 

查看事务是否自动提交

show variables like 'autocommit';

 

查询事务隔离级别

show variables like 'tx_isolation';

 

查看是否开启General Log(general_log)

show variables like 'general_log';

 

开启General Log(general_log)

set global general_log=on;

 

查看General Log(general_log)日志文件存放路径

show variables like 'general_log_file';

 

建表

 

alter table post character set utf8;

alter table post modify column id varchar(32) character set utf8 not null;

 

插入

 

删除

 

更新

 

查询

 

查询效率

写出查询sql很容易，但要写出高效的查询sql就不是那么容易的。

提升查询效率

1、索引

索引是提升查询sql效率最常见也是最有效的途径

2、sql语句缓存

3、查询缓存

 

一个特殊的查询例子：

以下查询的apn_user表中，username字段是varchar类型，该字段的值可以是数字字符串，但在查询时，如果查询子句的字段值使用的数字部分，也可以查到：

mysql> select * from apn_user where username = 111;

+----+---------------------+---------------+------+----------+--------------+----------+

| id | created_date        | email         | name | password | updated_date | username |

+----+---------------------+---------------+------+----------+--------------+----------+

|  1 | 2012-01-20 12:12:12 | [e-mail] | 111  | 111      | NULL         | 111aaa   |

+----+---------------------+---------------+------+----------+--------------+----------+

1 row in set (0.00 sec)

 

mysql> explain select * from apn_user where username = 111;

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | username      | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

但如果查询子句的字段建了索引， 查询时索引无效。

 

 

索引

 

创建索引

语法：

 

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

 

索引可以创建普通索引，唯一索引，FULLTEXT全文索引，SPATIAL索引

 

创建普通索引

mysql> create index idx_updated_date on apn_user(updated_date);

Query OK, 5 rows affected (0.23 sec)

Records: 5  Duplicates: 0  Warnings: 0

 

mysql> show index from apn_user;

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

| Table    | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

| apn_user |          1 | idx_updated_date |            1 | updated_date | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

 

创建唯一索引

mysql> create unique index idx_updated_date on apn_user(updated_date);

Query OK, 5 rows affected (0.25 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> show index from apn_user;

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

| Table    | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

| apn_user |          0 | idx_updated_date |            1 | updated_date | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

 

mysql> alter table apn_user add unique idx_updated_date(updated_date);

Query OK, 5 rows affected (0.20 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> show index from apn_user;

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

| Table    | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

| apn_user |          0 | idx_updated_date |            1 | updated_date | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

 

 

查看索引

mysql> show index from apn_user;

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

| Table    | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

| apn_user |          0 | PRIMARY          |            1 | id           | A         |           2 |     NULL | NULL   |      | BTREE      |         |

| apn_user |          0 | username         |            1 | username     | A         |           2 |     NULL | NULL   |      | BTREE      |         |

| apn_user |          0 | idx_updated_date |            1 | updated_date | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |

+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

3 rows in set (0.00 sec)

 

 

删除索引

mysql> drop index idx_updated_date on apn_user;

Query OK, 9 rows affected (0.27 sec)

Records: 9  Duplicates: 0  Warnings: 0

 

mysql> alter table apn_user drop key idx_updated_date;

Query OK, 5 rows affected (0.25 sec)

Records: 5  Duplicates: 0  Warnings: 0

 

索引类型

1、B树索引

BTREE

2、哈希索引

HASH

 

 

使用索引

索引不是直接用的，而是在检索数据的时候间接使用索引。

 

 

 

 

 

当然，即使在查询时在查询子句上对应的字段上建好了索引，在查询时也不一定会使用到索引，如果确定使用对应索引的确能提升查询的效率，可以强制使用索引。

 

强制使用索引

select * from <table> force index(<index name>, ...) where ...;

 

在使用强制使用索引时，得在有可能使用到索引的情况下才能使用索引。是否有可能使用到索引，可执行查询计划，如果查询计划结果中有“possible_keys”，就表示有可能使用到索引，可强制使用索引。

 

如下，强制使用索引是没用的：

mysql> select * from apn_user where username like '%aaa';

+----+---------------------+------------------+------+----------+--------------+----------+

| id | created_date        | email            | name | password | updated_date | username |

+----+---------------------+------------------+------+----------+--------------+----------+

|  1 | 2012-01-20 12:12:12 | [e-mail]    | 111  | 111      | NULL         | 111aaa   |

|  2 | 2012-01-21 12:12:12 | [e-mail] | 222  | 222      | NULL         | 222aaa   |

|  3 | 2012-02-08 18:01:43 | NULL             | NULL | 333      | NULL         | 333aaa   |

+----+---------------------+------------------+------+----------+--------------+----------+

3 rows in set (0.00 sec)

 

mysql> explain select * from apn_user where username like '%aaa';

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.01 sec)

从这里看，没有使用到索引，也没有可能使用到的索引“possible_keys”。

 

如果强制使用索引的话，也是无效的：

mysql> explain select * from apn_user force index(username) where username like '%aaa';

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

 

 

SQL执行计划

explain <查询>

 

SQL执行计划结果

Column        JSON NameMeaning

id        select_idThe SELECT identifier

select_typeNone        The SELECT type

table        table_nameThe table for the output row

partitionspartitionsThe matching partitions

type        access_typeThe join type

possible_keyspossible_keysThe possible indexes to choose

key        key        The index actually chosen

key_len        key_lengthThe length of the chosen key

ref        ref        The columns compared to the index

rows        rows        Estimate of rows to be examined

filteredfilteredPercentage of rows filtered by table condition

Extra        None        Additional information

 

查询sql中有几次查询，在SQL执行计划结果中就有几行。

 

列说明

5、type

 

system

 

The table has only one row (= system table). This is a special case of the const join type.

 

 

const

 

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

 

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as a const table:

 

SELECT * FROM tbl_name WHERE primary_key=1;

 

SELECT * FROM tbl_name

  WHERE primary_key_part1=1 AND primary_key_part2=2;

 

 

eq_ref

 

One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

 

eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an eq_ref join to process ref_table:

 

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

 

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

  AND ref_table.key_column_part2=1;

 

 

ref

 

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

 

ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can use a ref join to process ref_table:

 

SELECT * FROM ref_table WHERE key_column=expr;

 

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

 

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

  AND ref_table.key_column_part2=1;

 

 

fulltext

 

The join is performed using a FULLTEXT index.

 

 

ref_or_null

 

This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use a ref_or_null join to process ref_table:

 

SELECT * FROM ref_table

  WHERE key_column=expr OR key_column IS NULL;

See Section 8.2.1.14, “IS NULL Optimization”.

 

 

index_merge

 

This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used. For more information, see Section 8.2.1.3, “Index Merge Optimization”.

 

 

unique_subquery

 

This type replaces eq_ref for some IN subqueries of the following form:

 

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

 

 

index_subquery

 

This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:

 

value IN (SELECT key_column FROM single_table WHERE some_expr)

 

 

range

 

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

 

range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:

 

SELECT * FROM tbl_name

  WHERE key_column = 10;

 

SELECT * FROM tbl_name

  WHERE key_column BETWEEN 10 and 20;

 

SELECT * FROM tbl_name

  WHERE key_column IN (10,20,30);

 

SELECT * FROM tbl_name

  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

 

 

index

 

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

 

If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.

 

A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

 

MySQL can use this join type when the query uses only columns that are part of a single index.

 

 

ALL

 

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

 

 

 

mysql> explain select * from apn_user a left join apn_user_info b on a.username = b.username;

+----+-------------+-------+--------+---------------+-----------+---------+----------------------+------+-------+

| id | select_type | table | type   | possible_keys | key       | key_len | ref                  | rows | Extra |

+----+-------------+-------+--------+---------------+-----------+---------+----------------------+------+-------+

|  1 | SIMPLE      | a     | ALL    | NULL          | NULL      | NULL    | NULL                 |    9 |       |

|  1 | SIMPLE      | b     | eq_ref | NewIndex1     | NewIndex1 | 98      | androidpn.a.username |    1 |       |

+----+-------------+-------+--------+---------------+-----------+---------+----------------------+------+-------+

2 rows in set (0.00 sec)

 

mysql> explain select * from apn_user a left join apn_user_info b on a.username = b.username where a.updated_date is not null;

+----+-------------+-------+--------+------------------+------------------+---------+----------------------+------+-------------+

| id | select_type | table | type   | possible_keys    | key              | key_len | ref                  | rows | Extra       |

+----+-------------+-------+--------+------------------+------------------+---------+----------------------+------+-------------+

|  1 | SIMPLE      | a     | range  | idx_updated_date | idx_updated_date | 9       | NULL                 |    3 | Using where |

|  1 | SIMPLE      | b     | eq_ref | NewIndex1        | NewIndex1        | 98      | androidpn.a.username |    1 |             |

+----+-------------+-------+--------+------------------+------------------+---------+----------------------+------+-------------+

2 rows in set (0.00 sec)

 

 

mysql> explain select * from apn_user a, apn_user_info b where a.username = b.username and a.updated_date is not null;

+----+-------------+-------+--------+---------------------------+------------------+---------+----------------------+------+-------------+

| id | select_type | table | type   | possible_keys             | key              | key_len | ref                  | rows | Extra       |

+----+-------------+-------+--------+---------------------------+------------------+---------+----------------------+------+-------------+

|  1 | SIMPLE      | a     | range  | username,idx_updated_date | idx_updated_date | 9       | NULL                 |    3 | Using where |

|  1 | SIMPLE      | b     | eq_ref | NewIndex1                 | NewIndex1        | 98      | androidpn.a.username |    1 | Using where |

+----+-------------+-------+--------+---------------------------+------------------+---------+----------------------+------+-------------+

2 rows in set (0.00 sec)

 

 

 

查询与索引

查询使用索引，是为了提升查询效率的。

 

以apn_user为例，其中有5条记录，除了主键，我们在username字段上建了索引。

mysql> select * from apn_user;

+----+---------------------+------------------+------+----------+---------------------+----------+

| id | created_date        | email            | name | password | updated_date        | username |

+----+---------------------+------------------+------+----------+---------------------+----------+

|  1 | 2012-01-20 12:12:12 | [e-mail]    | 111  | 111      | NULL                | 111aaa   |

|  2 | 2012-01-21 12:12:12 | [e-mail] | 222  | 222      | NULL                | 222aaa   |

|  3 | 2012-02-08 18:01:43 | NULL             | NULL | 333      | NULL                | 333aaa   |

|  4 | 2012-02-08 18:24:39 | NULL             | NULL | 444      | NULL                | 444      |

|  5 | 2012-02-08 18:24:39 | [e-mail]      | 555  | 555      | 2012-02-08 18:24:39 | 555      |

+----+---------------------+------------------+------+----------+---------------------+----------+

5 rows in set (0.00 sec)

 

mysql> show index from apn_user;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| apn_user |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |

| apn_user |          0 | username |            1 | username    | A         |           5 |     NULL | NULL   |      | BTREE      |         |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.00 sec)

 

 

mysql> explain select * from apn_user where username = '111aaa';

+----+-------------+----------+-------+---------------+----------+---------+-------+------+-------+

| id | select_type | table    | type  | possible_keys | key      | key_len | ref   | rows | Extra |

+----+-------------+----------+-------+---------------+----------+---------+-------+------+-------+

|  1 | SIMPLE      | apn_user | const | username      | username | 194     | const |    1 |       |

+----+-------------+----------+-------+---------------+----------+---------+-------+------+-------+

1 row in set (0.00 sec)

从上面的查询优化看，是用上了索引，索引是生效的。

但是看下面的查询，和上面的查询相比，仅仅是将=换成了!=，但是，看下面的查询计划

mysql> explain select * from apn_user where username != '111aaa';

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | username      | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

发现索引无效！

 

这里username字段是varchar类型，该字段的值可以是数字字符串，但在查询时，如果查询子句的字段值使用的数字，而没有像字符串那样使用引号（单引号或双引号），那么不使用索引。

mysql> select * from apn_user where username = 444;

+----+---------------------+-------+------+----------+--------------+----------+

| id | created_date        | email | name | password | updated_date | username |

+----+---------------------+-------+------+----------+--------------+----------+

|  4 | 2012-02-08 18:24:39 | NULL  | NULL | 444      | NULL         | 444      |

+----+---------------------+-------+------+----------+--------------+----------+

1 row in set (0.00 sec)

 

mysql> explain select * from apn_user where username = 444;

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | username      | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

这里看，索引无效。

 

mysql> explain select * from apn_user where username = '111';

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

1 row in set (0.00 sec)

 

关于“Impossible WHERE noticed after reading const tables”

执行查询优化，如果看到有这个，表示sql在实际查询时会执行全表扫描。即便我们在查询子句上建了索引。

如果在查询子句上建了索引，索引无效。

这在查询时，如果没有符合查询子句的记录，会出现这种情况。

 

mysql> explain select * from apn_user where username like '%aaa';

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

从上面的查询优化看，没有用上索引，索引无效。

 

mysql> explain select * from apn_user where username like '_11aaa';

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

从上面的查询优化看，没有用上索引，索引无效。

 

 

mysql> explain select * from apn_user where username like '111%';

+----+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

| id | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra       |

+----+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | range | username      | username | 194     | NULL |    1 | Using where |

+----+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

1 row in set (0.00 sec)

这里使用了索引。

 

查询子句使用like时，如果查询字段上建了索引，'%aaa'，'_11aaa'会使索引失效， 即like时是以通配符%，_开头的都会使索引无效，'111%'这种才有效。

 

 

 

mysql> explain select * from apn_user where username in ('111aaa', '222aaa');

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | username      | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

从上面的查询优化看，没有用上索引，索引无效。

 

mysql> explain select * from apn_user force index(username) where username in ('111aaa', '222aaa');

+----+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

| id | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra       |

+----+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | range | username      | username | 194     | NULL |    2 | Using where |

+----+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

1 row in set (0.02 sec)

这里强制使用索引后，就可以发现使用了索引。

 

mysql> explain select * from apn_user where username = '111aaa' or username = '222aaa';

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | username      | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

这个等效于上面的in用法，但这里也没有用到索引。

 

mysql> explain select * from apn_user where id = 1 or name = '111';

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | PRIMARY       | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

这里也没有用到索引。

 

mysql> explain select * from apn_user force index(username) where username = '111aaa' or username = '222aaa';

+----+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

| id | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra       |

+----+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | range | username      | username | 194     | NULL |    2 | Using where |

+----+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

1 row in set (0.00 sec)

这里强制使用索引后，就可以发现使用了索引。

 

mysql> explain select * from apn_user where username = '111aaa' and username = '222aaa';

+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |

+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |

+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

1 row in set (0.00 sec)

 

可见在执行查询优化，如果看到“Impossible WHERE”字样，都不是什么好情况！

 

 

 

 

mysql> explain select * from apn_user where day(updated_date) = 20;

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | apn_user | ALL  | NULL          | NULL | NULL    | NULL |    9 | Using where |

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

 

1 row in set (0.00 sec)

使用了聚集函数，索引无效

 

 

 

 

 

 

 

 

 

 

exists和in的比较

mysql> select * from apn_user A where username in (select username from apn_user_info B);

+----+---------------------+------------------+------+----------+---------------------+----------+

| id | created_date        | email            | name | password | updated_date        | username |

+----+---------------------+------------------+------+----------+---------------------+----------+

|  1 | 2012-01-20 12:12:12 | [e-mail]    | 111  | 111      | 2012-01-20 12:12:12 | 111aaa   |

|  2 | 2012-01-21 12:12:12 | [e-mail] | 222  | 222      | 2012-01-21 12:12:12 | 222aaa   |

|  3 | 2012-02-08 18:01:43 | NULL             | NULL | 333      | NULL                | 333aaa   |

|  4 | 2012-02-08 18:24:39 | NULL             | NULL | 444      | NULL                | 444      |

|  5 | 2012-02-08 18:24:39 | [e-mail]      | 555  | 555      | 2012-02-08 18:24:39 | 555      |

|  6 | NULL                | [e-mail]      | 666  | 666      | NULL                | 666      |

|  7 | NULL                | [e-mail]      | 777  | 777      | NULL                | 777      |

|  8 | NULL                | [e-mail]      | 888  | 888      | NULL                | 888      |

+----+---------------------+------------------+------+----------+---------------------+----------+

8 rows in set (0.00 sec)

 

mysql> explain select * from apn_user A where username in (select username from apn_user_info B);

+----+--------------------+-------+-----------------+---------------+-----------+---------+------+------+--------------------------+

| id | select_type        | table | type            | possible_keys | key       | key_len | ref  | rows | Extra                    |

+----+--------------------+-------+-----------------+---------------+-----------+---------+------+------+--------------------------+

|  1 | PRIMARY            | A     | ALL             | NULL          | NULL      | NULL    | NULL |    9 | Using where              |

|  2 | DEPENDENT SUBQUERY | B     | unique_subquery | NewIndex1     | NewIndex1 | 98      | func |    1 | Using index; Using where |

+----+--------------------+-------+-----------------+---------------+-----------+---------+------+------+--------------------------+

2 rows in set (0.00 sec)

 

mysql> select * from apn_user A where exists (select * from apn_user_info B where B.username = A.username);

+----+---------------------+------------------+------+----------+---------------------+----------+

| id | created_date        | email            | name | password | updated_date        | username |

+----+---------------------+------------------+------+----------+---------------------+----------+

|  1 | 2012-01-20 12:12:12 | [e-mail]    | 111  | 111      | 2012-01-20 12:12:12 | 111aaa   |

|  2 | 2012-01-21 12:12:12 | [e-mail] | 222  | 222      | 2012-01-21 12:12:12 | 222aaa   |

|  3 | 2012-02-08 18:01:43 | NULL             | NULL | 333      | NULL                | 333aaa   |

|  4 | 2012-02-08 18:24:39 | NULL             | NULL | 444      | NULL                | 444      |

|  5 | 2012-02-08 18:24:39 | [e-mail]      | 555  | 555      | 2012-02-08 18:24:39 | 555      |

|  6 | NULL                | [e-mail]      | 666  | 666      | NULL                | 666      |

|  7 | NULL                | [e-mail]      | 777  | 777      | NULL                | 777      |

|  8 | NULL                | [e-mail]      | 888  | 888      | NULL                | 888      |

+----+---------------------+------------------+------+----------+---------------------+----------+

8 rows in set (0.00 sec)

 

mysql> explain select * from apn_user A where exists (select * from apn_user_info B where B.username = A.username);

+----+--------------------+-------+--------+---------------+-----------+---------+----------------------+------+--------------------------+

| id | select_type        | table | type   | possible_keys | key       | key_len | ref                  | rows | Extra                    |

+----+--------------------+-------+--------+---------------+-----------+---------+----------------------+------+--------------------------+

|  1 | PRIMARY            | A     | ALL    | NULL          | NULL      | NULL    | NULL                 |    9 | Using where              |

|  2 | DEPENDENT SUBQUERY | B     | eq_ref | NewIndex1     | NewIndex1 | 98      | androidpn.A.username |    1 | Using where; Using index |

+----+--------------------+-------+--------+---------------+-----------+---------+----------------------+------+--------------------------+

 

2 rows in set (0.00 sec)

对于上面的exists和in的两个查询，如果：

1、apn_user的username没建索引，apn_user_info的username也没建索引，exists和in两种情况都无所谓使用索引

2、apn_user的username建了索引，apn_user_info的username也没建索引，exists和in两种情况都不会使用索引

3、apn_user的username没建索引，apn_user_info的username建了索引，exists和in两种情况都会用到索引，使用的是建在apn_user_info的username字段的索引

3、apn_user的username建了索引，apn_user_info的username也建了索引，exists和in两种情况都会用到索引，使用的是建在apn_user_info的username字段的索引

 

查看连接

show processlist;

 

如果需要查看所有连接的话：

show full processlist;

 

 

MySQL C API

 

libmysqlclient