mysql 二进制索引_MySQL索引

ゝ一纸荒年。 2023-01-12 10:29 183阅读 0赞

MySQL基础

单进程多线程：

用户连接：连接线程

MySQL数据文件类型：

数据文件、索引文件

重做日志、撤消日志、二进制日志、错误日志、查询日志、慢查询日志、(中继日志)

DDL & DML:

索引管理：

按特定数据结构存储的数据；

索引类型：

聚集索引、非聚集索引：数据是否与索引存储在一起；

主键索引、辅助索引

稠密索引、稀疏索引：是否索引了每一个数据项；

B+ TREE、HASH、R TREE

简单索引、组合索引

左前缀索引

覆盖索引

管理索引的途径：

创建索引：创建表时指定；CREATE INDEX

创建或删除索引：修改表的命令

删除索引：DROP INDEX

查看表上的索引：

SHOW \{INDEX | INDEXES | KEYS\}

\{FROM | IN\} tbl\_name

\[\{FROM | IN\} db\_name\]

\[WHERE expr\]

索引的创建可以使用ALTER、CREATE命令创建：

mysql> CREATE INDEX name ON student (name);

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW INDEXES FROM student;

\+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

\+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| student | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |

\+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

mysql>

EXPLAIN \#确认是否有使用到索引进行查询。

mysql> EXPLAIN SELECT \* FROM students WHERE id=3;

mysql> select \* from student;

\+----+----------+---------------+------+

| id | name | register\_date | sex |

\+----+----------+---------------+------+

| 1 | ZhanYang | 2018-06-20 | NULL |

| 4 | LiuJia | 2018-05-30 | NULL |

| 8 | JiaLiu | 2018-06-20 | NULL |

| 9 | JiaLiu | 2018-04-20 | NULL |

| 10 | gaoyf | 2018-04-20 | NULL |

| 11 | zhujh | 2018-04-20 | NULL |

| 12 | zhouha | 2018-04-20 | NULL |

| 13 | hanzb | 2018-04-21 | M |

| 16 | ZhanXing | 2018-05-21 | M |

| 17 | XingYan | 2018-05-21 | M |

\+----+----------+---------------+------+

10 rows in set (0.01 sec)

mysql> EXPLAIN SELECT \* FROM student WHERE id=4;

\+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

\+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

\+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

1 row in set (0.01 sec)

mysql> EXPLAIN SELECT \* FROM student WHERE id=4\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type: SIMPLE

table: student

type: const

possible\_keys: PRIMARY

key: PRIMARY

key\_len: 4

ref: const

rows: 1

Extra: NULL

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT \* FROM student WHERE name LIKE '%X%';

\+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

\+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

\+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT \* FROM student WHERE name LIKE '%X%'\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type: SIMPLE

table: student

type: ALL

possible\_keys: NULL

key: NULL

key\_len: NULL

ref: NULL

rows: 10

Extra: Using where

1 row in set (0.01 sec)

DESC命令用于获取数据表结构

mysql 二进制索引_MySQL索引

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql创建索引，mysql索引优化，mysql索引创建删除

相关 mysql longtext 索引_mysql 索引索引长度 fulltext 字符集

相关 mysql 二进制索引_MySQL索引

相关 mysql 的索引代码_mysql索引

相关 mysql索引_MySQL索引

相关【mysql】索引、建立索引

相关 MySQL索引main日志_mysql索引

相关 mysql索引篇_MySQL索引篇

相关 mysql 创建索引命令_mysql 索引-【创建索引命令】

相关 MySQL - 索引