MySQL 索引详解（mysql索引详解）

25-04-19 8

在这篇文章中，我们将带领您了解MySQL索引详解的全貌，包括mysql索引详解的相关情况。同时，我们还将为您介绍有关Can''tconnecttolocalMySQLserverthroughsock

在这篇文章中，我们将带领您了解MySQL 索引详解的全貌，包括mysql索引详解的相关情况。同时，我们还将为您介绍有关Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)、Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''、CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’、centos7 设置 mysql 自启动的配置文件中 [Service] User=mysql Group=mysql，user 和 group 这边的 mysql 是指的什么？centos 的登录用户名？的知识，以帮助您更好地理解这个主题。

本文目录一览：

MySQL 索引详解（mysql索引详解）
Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)
Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''
CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
centos7 设置 mysql 自启动的配置文件中 [Service] User=mysql Group=mysql，user 和 group 这边的 mysql 是指的什么？centos 的登录用户名？

MySQL 索引详解（mysql索引详解）

MySQL数据库中B+树索引分为聚集索引（clustered index）和非聚集索引（secondary index）。这两种索引的共同点是内部都是B+树，高度都是平衡的，叶节点存放着所有数据。不同点是叶节点是否存放着一整行数据。

聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

1、聚集索引

Innodb存储引擎表是索引组织表，即表中数据按主键顺序存放。而聚集索引就是按每张表的主键构造一颗B+树。并且叶节点存放整张表的行记录数据。每张表只能有一个聚集索引（一个主键）。聚集索引的另一个好处是它对于主键的排序查找和范围的速度非常快。叶节点的数据就是我们要找的数据。

聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的存储引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。

主键排序查找：由于B+树是双项链表，我们可以迅速找到最后一个页，并取出需要的记录；
主键范围查找：如果要通过主键查找某一范围内的数据，通过叶节点的上层中间节点就能得到页的范围，之后直接读取数据页即可；

示例结构：

数字是对应表的主键，内节点只存关键字，叶节点存关键字和数据，叶子页面包含完整的元组，而内节点页面仅包含索引的列(索引的列为整型)InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

（1）MyISAM的数据布局

其布局十分简单，MyISAM按照插入的顺序在磁盘上存储数据

注：

其布局十分简单，MyISAM按照插入的顺序在磁盘上存储数据左边为行号(row number)，从0开始。因为元组的大小固定，所以MyISAM可以很容易的从表的开始位置找到某一字节的位置。

MyISAM不支持聚簇索引，索引中每一个叶子节点仅仅包含索引字段关键字和行号(row number)，且叶子节点按照索引字段关键字的顺序存储

实际上，在MyISAM中，primary key和其它索引没有什么区别。Primary key仅仅只是一个叫做PRIMARY的唯一，非空的索引而已。

（2）InnoDB的数据布局

InnoDB按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同。它存储表的结构大致如下：

注：聚簇索引中的每个叶子节点包含primary key的值，事务ID和回滚指针(rollback pointer)——用于事务和MVCC，和余下的列(如col2)。

相对于MyISAM，二级索引与聚簇索引有很大的不同。InnoDB的二级索引的叶子包含primary key的值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。其结构大致如下：

按primary key的顺序插入行(InnoDB)

如果你用InnoDB，而且不需要特殊的聚簇索引，一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列，这会保证记录按照顺序插入，而且能提高使用primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键，例如，字符串主键就是一个不好的选择，它使得插入操作变得随机。

2、非聚集索引

也称辅助索引。叶级别不包含数据行的全部数据，叶节点包含索引列的键值和指向数据行主键的指针（一个书签bookmark，该书签告诉innodb存储引擎，哪里可以找到与索引对应的数据）辅助索引的存在并不影响数据再聚集索引中的组织，因此一个表可以有多个辅助索引。当通过辅助索引查找数据时，innod会遍历辅助索引并通过叶级别的指针获得数据行的主键。然后再通过主键索引找到一行完整的数据。

3、索引的创建和删除

索引的创建和删除可以用两种方式。一种是alter table,另一种是create/drop index

>A、create index标准语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[index_type]

ON tbl_name (index_col_name,...)

[index_option]

[algorithm_option | lock_option] ...

index_col_name:

col_name [(length)] [ASC | DESC]

index_option:

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT ''string''

index_type:

USING {BTREE | HASH}

algorithm_option:

ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:

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

DROP INDEX index_name ON tbl_name

[algorithm_option | lock_option] ...

algorithm_option:

ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:

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

B、alter table创建删除索引的标准语句

ALTER TABLE tbl_name

[alter_specification [, alter_specification] ...]

[partition_options]

alter_specification:

| ADD {INDEX|KEY} [index_name]

[index_type] (index_col_name,...) [index_option] ...

| ADD [CONSTRAINT [symbol]] PRIMARY KEY

[index_type] (index_col_name,...) [index_option] ...

| ADD [CONSTRAINT [symbol]]

UNIQUE [INDEX|KEY] [index_name]

[index_type] (index_col_name,...) [index_option] ...

| ADD FULLTEXT [INDEX|KEY] [index_name]

(index_col_name,...) [index_option] ...

| ADD SPATIAL [INDEX|KEY] [index_name]

(index_col_name,...) [index_option] ...

| ADD [CONSTRAINT [symbol]]

FOREIGN KEY [index_name] (index_col_name,...)

reference_definition

| DROP {INDEX|KEY} index_name

| DROP PRIMARY KEY

| DROP FOREIGN KEY fk_symbol

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH}

index_option:

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT ''string''

>C、创建表是一块创建索引

drop table if exists people;

CREATE TABLE People (

id int not null AUTO_INCREMENT,

last_name varchar(50) not null,

first_name varchar(50) not null,

dob date not null,

gender enum(''m'', ''f'') not null default ''m'',

primary key(id),

index indx_last_first_name (last_name,first_name),

key indx_dob_gender (dob,gender)

);

mysql> show index from people;

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

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

| people | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |

| people | 1 | indx_dob_gender | 1 | dob | A | 0 | NULL | NULL | | BTREE | | |

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

5 rows in set (0.00 sec)

>d、删除MySQL索引

alter table table_name drop index index_name;

drop index index_name on table_name;

--创建前缀索引

alter table t1 add index idx_col4 ( col4(10));

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

4、各种存储引擎默认的索引类型

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

5、MySQL索引注意的问题：

对于MySQL索引的添加和删除操作，MySQL先是创建一张加好索引的临时表，然后把数据导入临时表，再删除原表，把临时表重命名为原表。

Innodb存储引擎从Innodb Plugin版本开始，支持一种快速创建索引的方法（只限于辅助索引，主键索引仍需要建临时表）。首先对表加S锁，在创建的过程中不需要重建表，但是由于上了S锁，在创建索引的过程中只能进行查询操作，不能更新数据。

6、查看表上的索引信息

SHOW {INDEX | INDEXES | KEYS}

{FROM | IN} tbl_name

[{FROM | IN} db_name]

[WHERE expr]

eg:

mysql> show indexes from actor\G

6、顺序读、随机读与预读取

顺序读是指顺序的读取磁盘上的块，随机读是指访问的块是不连续的，需要磁盘的磁头不断移动。随机读的性能是远远低于顺序读的。

在数据库中，顺序读根据索引的叶节点就能顺序的读取所需的行数据，这个顺序读只是逻辑的顺序读，在磁盘上可能还是随机读。随机读是指访问辅助索引叶节点不能完全得到结果，需要根据辅助索引页节点中的主键去寻找实际数据行。对于一些取表里很大部分数据的查询，正式因为读取是随机读，而随机读的性能会远低于顺序读。所以优化器才会选择全部扫描顺序读，而不使用索引。

innodb存储引擎有两个预读取方法，随机预读取和线性预读取。随机预读取是指当一个区（共64个连续页）中有13个页在缓冲区中并被频繁访问时，innodb存储引擎会将这个区中剩余的页预读到缓冲区。线性预读取基于缓冲池中页的访问方式，而不是数量。如果一个区中有24个页被顺序访问了，则innodb会读取下一个区的所有页到缓冲区。但是innodb预读取经过测试后性能比较差，经过TPCC测试发现禁用预读取比启用预读取提高了10%的性能。在新版本innodb中，mysql禁用了随机预读取，仅保留了线性预读取，并且加入了innodb_read_ahead_threshold参数，当连续访问页超过该值时才启用预读取，默认值为56。

mysql> show variables like ''%read_ahead%'';

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

| Variable_name | Value |

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

| innodb_random_read_ahead | OFF |

| innodb_read_ahead_threshold | 56 |

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

2 rows in set (0.00 sec)

7、innodb存储引擎总是会从辅助索引的叶节点判断是否能得到数据。

create table t ( a int not null, b varchar(20), primary key(a),key(b));

insert into t select 1,''kangaroo'';

insert into t select 2,''dolphin'';

insert into t select 3,''dragon'';

insert into t select 4,''anteloge'';

mysql> select * from t;

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

| a | b |

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

| 4 | anteloge |

| 2 | dolphin |

| 3 | dragon |

| 1 | kangaroo |

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

4 rows in set (0.00 sec)

mysql> explain select * from t\G

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

id: 1

select_type: SIMPLE

table: t

type: index

possible_keys: NULL

key: b

key_len: 63

ref: NULL

rows: 4

Extra: Using index

1 row in set (0.00 sec)

mysql> explain select * from t order by a\G

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

id: 1

select_type: SIMPLE

table: t

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 4

Extra: NULL

1 row in set (0.00 sec)

mysql> select * from t force index(PRIMARY);

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

| a | b |

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

| 1 | kangaroo |

| 2 | dolphin |

| 3 | dragon |

| 4 | anteloge |

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

4 rows in set (0.00 sec)

8、复合索引

复合索引是指对表上的多个列做索引,索引还是一个B+树，不同的是复合索引键值的数量不是1，而是大于等于2，可以对第二键值进行排序。

MySQL创建复合索引时，如果没有指定索引的名称，会默认用索引列的第一个字段作为索引名称

create table buy_log(userid int unsigned not null, buy_date date);

insert into buy_log values(1,''2013-01-01'');

insert into buy_log values(2,''2013-01-01'');

insert into buy_log values(3,''2013-01-01'');

insert into buy_log values(1,''2013-02-01'');

insert into buy_log values(3,''2013-02-01'');

insert into buy_log values(1,''2013-03-01'');

insert into buy_log values(1,''2013-04-01'');

alter table buy_log add key(userid);

alter table buy_log add key(userid,buy_date);

mysql> explain select * from buy_log where userid=2\G

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

id: 1

select_type: SIMPLE

table: buy_log

type: ref

possible_keys: userid,userid_2

key: userid

key_len: 4

ref: const

rows: 1

Extra: NULL

1 row in set (0.00 sec)

可以看到possible_keys里面两个索引都可以使用，分别是单个的userid索引和userid,buy_date的联合索引。但是优化器最终选择的是userid，因为该叶节点包含单个键值，因此一个页存放的记录应该更多。

mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3\G

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

id: 1

select_type: SIMPLE

table: buy_log

type: ref

possible_keys: userid,userid_2

key: userid_2

key_len: 4

ref: const

rows: 4

Extra: Using where; Using index

1 row in set (0.00 sec)

mysql> explain select * from buy_log force index(userid) where userid=1 order by buy_date desc limit 3\G

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

id: 1

select_type: SIMPLE

table: buy_log

type: ref

possible_keys: userid

key: userid

key_len: 4

ref: const

rows: 4

Extra: Using where; Using filesort

1 row in set (0.00 sec)

同样对于上述SQL，两个索引都可使用，但是查询优化器使用了userid和buy_date组成的联合索引userid_2.因为这个联合索引中buy_date已经排序好了，可以减少一次排序操作。如果我们强制使用user_id单个索引，可以看到在Extra这里可以看到Using filesort，Using filesort指排序，但不一定是在文件中完成。

9、覆盖索引（Covering Indexes）

如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：

(1)索引项通常比记录要小，所以MySQL访问更少的数据；

(2)索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；

(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。

(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra一列中看到“Using index”。

表上的索引情况：

mysql> show index from inventory;

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

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

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

4 rows in set (0.00 sec)

查询的字段是索引字段的一部分，查询字段的顺序无关

mysql> explain select store_id,film_id from inventory\G

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

id: 1

select_type: SIMPLE

table: inventory

type: index

possible_keys: NULL

key: idx_store_id_film_id

key_len: 3

ref: NULL

rows: 4581

Extra: Using index

1 row in set (0.00 sec)

查询全部字段时未用到覆盖索引

mysql> explain select * from inventory\G

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

id: 1

select_type: SIMPLE

table: inventory

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4581

Extra: NULL

1 row in set (0.00 sec)

mysql> explain select * from inventory where store_id=1 and film_id=1\G

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

id: 1

select_type: SIMPLE

table: inventory

type: ref

possible_keys: idx_fk_film_id,idx_store_id_film_id

key: idx_store_id_film_id

key_len: 3

ref: const,const

rows: 4

Extra: NULL

1 row in set (0.00 sec)

InnoDB的二级索引在叶子节点中存储了primary key的值，所以覆盖于二级索引和主键的查询也属于覆盖索引查询

mysql> explain select inventory_id,store_id,film_id from inventory\G

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

id: 1

select_type: SIMPLE

table: inventory

type: index

possible_keys: NULL

key: idx_store_id_film_id

key_len: 3

ref: NULL

rows: 4581

Extra: Using index

1 row in set (0.00 sec)

10、索引排序

MySQL中，有两种方式生成有序结果集：一是使用filesort，二是按索引顺序扫描。利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort？

mysql> explain select inventory_id,film_id from inventory force index(idx_store_id_film_id) order by film_id;

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

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

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

1 row in set (0.00 sec)

mysql> explain select inventory_id,film_id from inventory use index(idx_store_id_film_id) order by film_id;

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

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

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

1 row in set (0.00 sec)

mysql> explain select inventory_id,film_id from inventory order by film_id;

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

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

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

1 row in set (0.00 sec)

mysql> explain select inventory_id,film_id from inventory ignore index(idx_fk_film_id) order by film_id;

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

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

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

单独order by 用不了索引，索引考虑加where 或加limit，开始没limit查询是遍历表的，加了limit后，索引可以使用，看key_len 和key，where满足最左前缀且order by中的列是该多列索引的子集时(也就是说orerby中没最左前缀原则限制)，不管是否有asc ,desc混合出现，都能用索引来满足order by。

mysql> show index from dept_emp;

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

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

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

4 rows in set (0.00 sec)

mysql> explain select * from dept_emp order by dept_no desc;

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

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

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

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from dept_emp order by dept_no desc limit 10;

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

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

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

1 row in set, 1 warning (0.00 sec)

当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。对于filesort，MySQL有两种排序算法。

(1)两遍扫描算法(Two passes):实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。

注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。

(3)一次扫描算法(single pass):该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。

注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。

mysql> explain select a.film_id,a.title,a.language_id,b.actor_id from film a inner join film_actor b on a.film_id=b.film_id order by a.film_id,a.title\G

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

id: 1

select_type: SIMPLE

table: a

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: b

type: ref

possible_keys: idx_fk_film_id

key: idx_fk_film_id

key_len: 2

ref: sakila.a.film_id

rows: 2

Extra: Using index

2 rows in set (0.00 sec)

mysql> explain select a.film_id,a.title,a.language_id,b.actor_id from film a inner join film_actor b on a.film_id=b.film_id order by b.film_id,a.title\G

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

id: 1

select_type: SIMPLE

table: a

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using temporary; Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: b

type: ref

possible_keys: idx_fk_film_id

key: idx_fk_film_id

key_len: 2

ref: sakila.a.film_id

rows: 2

Extra: Using index

2 rows in set (0.00 sec)

11、索引与加锁

索引对于InnoDB非常重要，因为它可以让查询锁更少的元组。这点十分重要，因为MySQL 5.0中，InnoDB直到事务提交时才会解锁。有两个方面的原因：首先，即使InnoDB行级锁的开销非常高效，内存开销也较小，但不管怎么样，还是存在开销。其次，对不需要的元组的加锁，会增加锁的开销，降低并发性。

InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是，只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了：InnoDB已经锁住那些元组，服务器无法解锁了。

create table tbl_actor(

actor_id int unsigned NOT NULL AUTO_INCREMENT,

name varchar(16) NOT NULL DEFAULT '''',

password varchar(16) NOT NULL DEFAULT '''',

PRIMARY KEY(actor_id),

KEY (name)

) ENGINE=InnoDB;

insert into tbl_actor(name,password) values(''cat01'',''1234567'');

insert into tbl_actor(name,password) values(''cat02'',''1234567'');

insert into tbl_actor(name,password) values(''ddddd'',''1234567'');

insert into tbl_actor(name,password) values(''aaaaa'',''1234567'');

mysql> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT actor_id FROM tbl_actor WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE;

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

| actor_id |

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

| 2 |

| 3 |

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

2 rows in set (0.00 sec)

该查询仅仅返回2,3的数据，实际已经对1-3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询（而没有进行过滤操作，WHERE中第二个条件已经无法使用索引了）

mysql> EXPLAIN SELECT actor_id FROM tbl_actor WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G

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

id: 1

select_type: SIMPLE

table: tbl_actor

type: index

possible_keys: PRIMARY

key: name

key_len: 50

ref: NULL

rows: 4

Extra: Using where; Using index

1 row in set (0.00 sec)

表明存储引擎从索引的起始处开始，获取所有的行，直到actor_id<4为假，服务器无法告诉InnoDB去掉元组1。

mysql> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

该查询会被挂起，直到第一个连接的事务提交释放锁时，才会执行（这种行为对于基于语句的复制(statement-based replication)是必要的）。如上所示，当使用索引时，InnoDB会锁住它不需要的元组。更糟糕的是，如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。

唯一索引:普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）

全文索引（FULLTEXT）:MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列

单列索引、多列索引

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

建索引的几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

慢查询优化基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE

1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

3.order by limit 形式的sql语句让排序的表优先查

4.了解业务方使用场景

5.加索引时参照建索引的几大原则

6.观察结果，不符合预期继续从0分析

第一：不要指望所有语句都能通过SQL优化，

第二：不要过于自信，只针对具体case来优化，而忽略了更复杂的情况。

任何数据库层面的优化都抵不上应用系统的优化，同样是MySQL，可以用来支撑Google/FaceBook/Taobao应用，但可能连你的个人网站都撑不住。套用最近比较流行的话：“查询容易，优化不易，且写且珍惜！”

hash函数

SELECT MD5(''http://www.mysql.com/''),RIGHT(MD5(''http://www.mysql.com/''),16),CONV(RIGHT(MD5(''http://www.mysql.com/''),16),16,10),CRC32(''http://www.mysql.com/'');

参考资料

http://www.ruzuojun.com/topic/420.html

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

http://blog.jobbole.com/86594/

Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)

ERROR 2002 (HY000): Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)

原因：系统盘满了

[root@localhost opt]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
18G 17G 0 100% /
tmpfs 504M 0 504M 0% /dev/shm
/dev/sda1 477M 80M 372M 18% /boot
[root@localhost opt]#

解决：

删除大文件后，重启系统解决

[root@localhost mysql]# /opt/lampp/lampp status
Version: XAMPP for Linux 1.8.3-3
Apache is not running.
MySQL is not running.
ProFTPD is running.

df: 未处理文件系统
[root@localhost opt]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
18G 17G 0 100% /
tmpfs 504M 0 504M 0% /dev/shm
/dev/sda1 477M 80M 372M 18% /boot
[root@localhost opt]#

[root@localhost ~]# /opt/lampp/lampp status
Version: XAMPP for Linux 1.8.3-3
Apache is not running.
MySQL is running.
ProFTPD is running.

转

xampp 无法启动mysql 找不到mysql.sock

(2016-02-24 23:21:24)

转载▼

分类：技术

出现的问题：
如果xampp中的mysql启动不了，出现ERROR 2002 (HY000): Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)报错，
停止xampp的时候报：
-bash-4.1# /opt/lampp/lampp stop
Stopping XAMPP for Linux 1.8.2-6...
XAMPP: Stopping Apache...ok.
XAMPP: Stopping MySQL...ok.
XAMPP: Stopping ProFTPD...kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
fail.

解决办法：
如果网上一些方法不好用的话，可以试试以下方法：
1. 确定系统盘是否满了
#df -h
2. 删除/opt/lampp目录中的pid文件（删掉后xampp重启时会重建，如果不放心，可以先备份lampp目录）
删除mysql相关缓存：
#rm -rf /opt/lampp/var/mysql/VM_*
删除proftp相关缓存：
#rm -rf /opt/lampp/var/proftpd.pid
如果找不到pid文件，可以搜一下：
#find /opt/lampp -name ''*.pid''

Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''

MySQL已经被我移到数据盘了，本地连接数据库会报错：Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''

但是远程是可以连接的，my.cnf设置mysql的根目录也改成了数据盘的地址，还要在加上client的参数，设置如下：

[client]
socket = /home/data/mysql/mysql.sock

之后重启下mysql就可以了

CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

亲，是不是忘记了开MysqL服务，service MysqLd start

centos7 设置 mysql 自启动的配置文件中 [Service] User=mysql Group=mysql，user 和 group 这边的 mysql 是指的什么？centos 的登录用户名？

centos7 设置 mysql 自启动的配置文件中

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
#Restart=on-failure
#RestartPreventExitStatus=1
#PrivateTmp=false

这里的

[Service]

User=mysql

Group=mysql，

user 和 group 这边的 mysql 是指的什么？centos 的登录用户名？还是其他呢？

今天关于MySQL 索引详解和mysql索引详解的介绍到此结束，谢谢您的阅读，有关Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)、Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''、CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’、centos7 设置 mysql 自启动的配置文件中 [Service] User=mysql Group=mysql，user 和 group 这边的 mysql 是指的什么？centos 的登录用户名？等更多相关知识的信息可以在本站进行查询。

本文标签：