linux系统mysql索引

2023-11-13 来源：哗拓教育

索引一、索引的分类1.根据算法分类1）主键索引

#1.建表时创建create table test(id int primary key);create table test(id int,primary key(id));#2.添加主键索引alter table test add primary key pri_key(id);

2）唯一建索引

#1.建表时创建create table test(id int unique key);#2.添加唯一建索引alter table test add unique key uni_key(id);

3）普通索引

#1.添加普通索引alter table test add index ljp_key(id);

4）全文索引5）查看索引

#1.方式一：mysql> show index from test10;#2.方式二：mysql> desc test10;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id | int(11) | YES | UNI | NULL | |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)PRI：主键索引UNI：唯一建索引MUL：普通索引

6）删除索引

mysql> alter table test drop index index_key;

2.根据配置方法分类1）注意事项

1.创建索引时会将数据重新进行排序2.创建索引会占用磁盘空间，所以索引不是越多越好3.在同一列上避免创建多种索引4.避免在数据很长的字段上创建索引，如果要创建就创建前缀索引

2）前缀索引

#根据前四个字符创建前缀索引mysql> alter table test add index index_key(name(4));

3）联合索引

mysql> create database xiangqing;mysql> create table xiangqin(id int,name varchar(20),gender enum(‘m‘,‘f‘),age tinyint,money int,height int,weight int,looks tinyint);mysql> insert xiangqin values(1,‘qiudao‘,‘m‘,38,-200000,120,130,‘10‘),(2,‘dilireba‘,‘f‘,18,400000,180,100,‘60‘),(3,‘cxk‘,‘m‘,28,100000,170,120,‘440‘),(4,‘fbb‘,‘f‘,18,1000000,165,85,‘90‘);#创建联合索引mysql> alter table xiangqin add index lh_key(money,gender,age,looks);#联合索引使用三种情况1.部分走索引money,gender,age2.全部走索引money,gender,age,looks3.不走索引 gender,age

二、explain的使用1.explain语法

explain + DQL语句mysql> explain select * from city where countrycode =‘CHN‘ or countrycode =‘USA‘;#查询中国和美国的数据mysql> select * from city where countrycode =‘CHN‘ or countrycode =‘USA‘;mysql> select * from city where countrycode in (‘CHN‘,‘USA‘);mysql> select * from city where countrycode = ‘CHN‘ union all select * from city where countrycode = ‘USA‘;Extra（扩展）Using temporary 使用group by大概率出现Using filesort 使用了order by大概率出现Using join buffer 使用join on大概率出现

2.扩展group by

#一般与聚合索引一起使用#建表mysql> create table jixiao(id int,name varchar(20) charset utf8,jixiao int,product varchar(10) charset utf8); Query OK, 0 rows affected (0.03 sec)#插入数据mysql> insert jixiao values(1,‘qiudao‘,‘1000000‘,‘房地产‘),(2,‘niulei‘,‘10000‘,‘房地产‘),(3,‘lijianpeng‘,‘100000‘,‘汽车‘),(4,‘qiandao‘,‘200000‘,‘ 汽车‘);#查询不同行业绩效最高的人mysql> select name,sum(jixiao),product from jixiao group by product;+------------+-------------+-----------+| name | sum(jixiao) | product |+------------+-------------+-----------+| qiudao | 1010000 | 房地产 || lijianpeng | 300000 | 汽车 |+------------+-------------+-----------+2 rows in set (0.00 sec)#查询房地产行业绩效最高的人mysql> select name,sum(jixiao),product from jixiao group by product having product=‘房地产‘;+--------+-------------+-----------+| name | sum(jixiao) | product |+--------+-------------+-----------+| qiudao | 1010000 | 房地产 |+--------+-------------+-----------+1 row in set (0.00 sec)

3.查询数据的方式1）全表扫描

#1.什么是全表扫描查询数据时type类型为ALL#2.什么情况全表扫描1）查询数据库所有数据mysql> explain select * from country2）没有走索引没设置索引索引损坏

2）索引扫描

1.index#全索引扫描mysql> explain select Name from city;2.range#范围查询mysql> explain select * from city where countrycode =‘CHN‘ or countrycode =‘USA‘;#有限制查询到的数据在总数据的20%以内，超过则走全文扫描，所以在查询是可以使用limit限制mysql> explain select * from city where countrycode != ‘CHN‘ limit 500;3.ref#精确查询mysql> explain select * from city where countrycode =‘CHN‘;4.eq_ref#使用join on时偶尔会出现5.const#查询条件是唯一索引或主键索引mysql> explain select * from city where id=1;6.system#查询级别与const一样，当数据很少时为该级别7.null#不需要读取数据，只需要获取最大值或者最小值mysql> explain select max(population) from city;

三、索引的建立1.索引的建立原则

1.能创建唯一索引就创建唯一索引2.为经常需要排序、分组和联合操作的字段建立索引3.为常作为查询条件的字段建立索引如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。4.尽量使用前缀来索引如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索，会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。5.限制索引的数目索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。6.删除不再使用或者很少使用的索引表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

2.总结什么时候不走索引1）没有查询条件，或者查询条件没有索引

#没有查询条件mysql> explain select * from city;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set (0.00 sec)#查询条件没有索引mysql> explain select District from city;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set (0.00 sec)

2）查询的结果占总数据的20%左右

#占总数据的18%，没走索引mysql> explain select * from city where population > 400000;#占总数据的15%，走了索引mysql> explain select * from city where population > 450000;#如果数据量查询就是表中大部分数据，可以用limit做限制mysql> explain select * from city where population > 400000 limit 100;

3）索引损坏4）查询条件带了特使符号(+，-)

#在=号左侧有特殊符号，不走索引mysql> explain select * from city where id-1=1;#在=号右侧有特殊符号，走索引mysql> explain select * from city where id=3-1;

5）隐式转换

#建表mysql> create table test (id int ,name varchar(20),telnum varchar(10));Query OK, 0 rows affected (0.04 sec)#插入数据mysql> insert into test values(1,‘zs‘,‘110‘),(2,‘l4‘,120),(3,‘w5‘,119),(4,‘z4‘,112);Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0#建立索引mysql> desc phonenum;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(10) | YES | | NULL | || phone | varchar(10) | YES | UNI | NULL | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)#查询语句级别全文扫描mysql> explain select * from phonenum where phone=6666666;+----+-------------+----------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | phonenum | ALL | uni_key | NULL | NULL | NULL | 3 | Using where |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)#当给字符加上引号，查询为索引扫描mysql> explain select * from phonenum where phone=‘6666666‘;+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+| 1 | SIMPLE | phonenum | const | uni_key | uni_key | 13 | const | 1 | NULL |+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)

6）like "%_" 百分号在最前面不走

#走range索引扫描EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘31%‘;#不走索引EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘%110‘;

7）联合索引查询不按照顺序有可能不走索引

%linux%类的搜索需求，可以使用Elasticsearch -------> ELK单独引用联合索引里非第一位置的索引列

CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM(‘m‘,‘f‘),money INT);ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);DESC t1SHOW INDEX FROM t1#走索引的情况测试EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex=‘m‘;#部分走索引EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex=‘m‘; #不走索引EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex=‘m‘;EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex=‘m‘;

linux系统mysql索引

标签：buffer 提高查看 extra 隐式转换主键索引数据量超过 class

小编还为您整理了以下内容，可能对您也有帮助：

linux下怎么连接mysql数据库

1、首先确保linux下mysql安装好，服务启动完之后，就可以开始连接数据库了。在终端输入“mysql -hlocalhost -uroot -p”，其中h参数是主机名，这里是本地就写localhost，也可以省略：

2、输入mysql的密码之后就登录了，也可以不输入密码登录，比如这里的root密码是mysql123456，可以直接输入“mysql -hlocalhost -uroot -pmysql123456”，此时就没有提示输入密码，直接进入mysql的命令行中了：

3、此时显示的就是mysql的命令行界面了，若要退出输入quit就可以退出了。以上就是linux中连接数据库的操作：

linux下，我安装的mysql没有innodb引擎，怎么加上?

首先确定，在mysql的'plugin_dir'下有ha_innodb_plugin.so和ha_innodb.so两个文件

查询'plugin_dir'的路径可以用以下命令

mysql>

show

variables

'plugin_dir';

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

Variable_name

Value

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

plugin_dir

/usr/local/mysql/lib/mysql/plugin

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

1.row

set

(0.01

sec)

如果没有找到，在您的mysql编译目录里有下面的目录文件(内置的innodb和innodb_plugin)

storage/innobase/.libs/ha_innodb.so

storage/innodb_plugin/.libs/ha_innodb_plugin.so

您需要把它们(ha_innodb_plugin.so和ha_innodb.so)拷贝到mysql的plugin目录中(/usr/local/mysql/lib/mysql/plugin)

接下来就是在mysql命令行安装一下

mysql

Welcome

the

MySQL

monitor.

Commands

end

with

;

\g.

Your

MySQL

connection

18.Server

version:

5.1.36-log

Source

distribution

Type

'help;'

'\h'

for

help.

Type

'\c'

clear

the

current

input

statement.

mysql>

show

engines;

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

Engine

Support

Comment

Transactions

Savepoints

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

MRG_MYISAM

YES

Collection

identical

MyISAM

tables

CSV

YES

CSV

storage

engine

MEMORY

YES

Hash

based,

stored

memory,

useful

for

temporary

tables

MyISAM

DEFAULT

Default

engine

MySQL

3.23

with

great

performance

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

4.rows

set

(0.00

sec)

mysql>

INSTALL

PLUGIN

InnoDB

SONAME

'ha_innodb.so';

Query

OK,

rows

affected

(0.69

sec)

mysql>

show

engines;

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

Engine

Support

Comment

Transactions

Savepoints

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

InnoDB

YES

Supports

transactions,

row-level

locking,

and

foreign

keys

YES

CSV

YES

CSV

storage

engine

MRG_MYISAM

YES

Collection

identical

MyISAM

tables

MyISAM

DEFAULT

Default

engine

MySQL

3.23

with

great

performance

MEMORY

YES

Hash

based,

stored

memory,

useful

for

temporary

tables

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

5.rows

set

(0.00

sec)

OK,大功告成

mysql 数据量大加了索引聚类查询还是慢

可以根据条件去添加索引，

一、

所有mysql索引列类型都可以被索引，对来相关类使用索引可以提高select查询性能，根据mysql索引数，可以是最大索引与最小索引，每种存储引擎对每个表的至少支持16的索引。总索引长度为256字节。

mysim和innodb存储引擎的表默认创建索引都是btree索引，目前mysql还不支持函数索引，但支持前缘索引，对字段前N个字符创建索引

二、mysql创建索引语法

Create [unioun|fulltext|spatial] index indexname[using indextype] on tablename( tablenamecol)

index_col_name:

col_name[ (length)][asc |desc]

如果你创建索引时搞错了，需要修改mysql索引我们可以用alert来修改索引，语法与create index创建索引差不多，我们就不说了，可以查看相关手册。

下面我们来看一个关于mysql创建索引实例教程。

mysql>create index cityname on city(city(2));

Query Ok,600 rows affected (0.26 sec)

Records :600 Duplicates:0 Warings 0:

我们现在来以city为条件进行查询，如下面。

->explain select * from city where city ='www.111cn.net' G

id:1

......

possible_keys:cityname

key:cityname

好了，现在我们来看看mysql删除索引等实例

Drop indexname on tablename

实例，我现在要删除刚才创建city索引

>drop index cityname on city;

Query ok, .....

不过通常对百万级数据的查询或者其他操作，都改换其他的大型的数据库了

linux系统如何查找mysql所在端口

my.ini（Linux系统下是my.cnf），当mysql服务器启动时它会读取这个文件，设置相关的运行环境参数。

此文件中有一个参数是 port;

port = 3306 # 设置mysql客户端连接服务端时默认使用的端口

找到mysql运行目录下的my.ini（Linux系统下是my.cnf），查看此参数即可。

显示全文

全部栏目

linux系统mysql索引