锐客网

  1. 首页 > 睿知 > it技术 > >

索引mysql怎么选 mysql索引的选择

索引IT技术

mysql为啥建选不了普通索引mysql数据库索引是一种能够让mysql数据查询更加快速的数据结构，我们在新建数据库的时候，如果设置了某个字段的Primary key主键 ， 那么数据库会默认为我们的主键字段创建一个唯一索引(Unique Index)的东西 ， 所以你就不需要再为此字段创建mysql数据库索引了，当然了，如果你想设置别的字段索引，那么就要额外加入该字段的数据库索引了 。
【索引mysql怎么选 mysql索引的选择】一：Mysql数据库索引的创建
1)如何为我们的mysql数据库添加索引呢？很简单，请看下面的创建mysql普通索引格式：
CREATE INDEX [index name] ON [table name]([column name]);
参数说明：NameDescription
index name索引名称.
table name需要添加索引的表名称.
column name需要添加索引的列名称.
例如我们要创建一个索引 ， 就可以使用如下的mysql命令运行即可：CREATE INDEX myindex ON mytable(aut_id);
2)唯一索引的创建，和mysql普通索引差不多，只需要在INDEX关键词前面加入UNIQUE关键词即可，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id);
我们也可以使用USING BTREE关键字，B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id) USING BTREE;
二：查看mysql数据库索引
我们可以使用以下命令查询刚刚新建的mysql数据库索引，“from mytable”代表查询该数据库表里面有哪些索引，mysql命令如下：show index from mytable;
创建成功之后 ， 会在“key_name”字段中显示刚刚创建的数据库索引名称，如图所示：
三：删除mysql数据库索引
我们也可以使用mysql命令删除某张表的索引，命令如下：drop index english on mytable
四：查询sql语句是否使用了mysql索引
我们上面常见好mysql数据库索引之后 ， 如果想要知道我们在执行sql语句时是否使用了数据库索引，就要在sql语句前面加入“EXPLAIN”关键词，命令如下所示：EXPLAIN SELECT * FROM `allword` where english='America';
如果使用到了我们创建的字段索引，就会看到如图所示的字段都不会是NULL空值，如下：
在有些情况下mysql索引会失效，也就是在执行到sql语句时没有使用到我们创建的数据库表字段索引，可能有以下这些情况：
1：使用了OR关键字查询
2：或者LOWER()，UPPER()函数 ，
3：还有一种就是使用了LIKE关键字查询，像(like '%XX'或者like '%XX%')这样的语句，但是像(like 'XX%')这种情况的mysql表索引是不会失效的 。
Mysql索引建立索引 ， 要使用离散度（选择度）更高索引mysql怎么选的字段 。
我们先来看一个重要的属性列的 离散度，
count(distinct(column_name)) : count(*) -- 列的全部不同值个数：所有数据行行数
数据行数相同的情况下 ， 分子越大，列的离散度就越高 。简单来说，如果列的重复值越多，离散度就越低，重复值越少 ， 离散度就越高 。
当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢 。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引 。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引
create table shop(address varchar(120) not null);
alter table shop add key(address(12));// 截取12个字符作为前缀索引是最优的吗？
问题是，截取多少呢？截取得多索引mysql怎么选了，达不到节省索引存储空间的目的 ， 截取得少了 ， 重复内容太多，字段的散列度（选择性）会降低 。怎么计算不同的长度的选择性呢？
先看一下字段在全部数据中的选择度计算公式：
select count(distinct address) / count(*) from shop;
select count(distinct left(address, n)) / count(*) as subn from shop;
count(distinct left(address,n)) / count(*) 的结果是会随着 n 的变大而变大 。举个例子，现在有两个address（东大街长兴小区，东大街福乐小区），那么 distinct(address ， 2)distinct(address ， 3）
==所以，截取的长度越长就会越接近字段在全部数据中的选择度
==所以，我们要权衡索引大小和查询速度 。
举个例子，通过不同长度去计算，与全表的选择性对比：
SELECTCOUNT(DISTINCT(address))/COUNT(*) sub,-- 字段在全部数据中的选择度
COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,-- 截取前5个字符的选择度
COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,
COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,
COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,-- 截取前10个字符的选择度
COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,
COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,
COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,
COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15
FROM shop;
-------- -------- -------- -------- -------- -------- -------- -------- --------
| sub| sub5| sub7| sub9| sub10| sub11| sub12| sub13| sub15|
-------- -------- -------- -------- -------- -------- -------- -------- --------
| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |
-------- -------- -------- -------- -------- -------- -------- -------- --------
可以看到在截取 11 个字段时 sub11(0.9993) 就已经很接近字段在全部数据中的选择度 sub(0.9958)了，而且长度也相较后面更短一些，综合考虑比较合适 。
ALTER TABLE shop ADD KEY (address(11));
1.索引的个数不要过多（浪费空间，更新变慢）
2.在用于 where 判断 order 排序和 join 的（on）字段上创建索引
3.区分度低的字段 ， 例如性别，不要建索引（离散度太低，导致扫描行数过多）
4.更新频繁的值 ， 不要作为主键或者索引（页分裂）
5.不建议用无序的值作为索引，例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）
6.若在多个字段都要创建索引的情况下，联合索引优于单值索引
7.联合索引把散列性高（区分度高）的值放在前面
如何为MySQL查询优化选择最佳索引说一下不同引擎的优化 ， myisam读的效果好，写的效率差，这和它数据存储格式 ， 索引的指针和锁的策略有关的，它的数据是顺序存储的（innodb数据存储方式是聚簇索引），索引mysql怎么选他的索引btree上的节点是一个指向数据物理位置的指针，所以查找起来很快，（innodb索引节点存的则是数据的主键 ， 所以需要根据主键二次查找）索引mysql怎么?。籱yisam锁是表锁，只有读读之间是并发的，写写之间和读写之间（读和插入之间是可以并发的 ， 去设置concurrent_insert参数，定期执行表优化操作，更新操作就没有办法了）是串行的，所以写起来慢，并且默认的写优先级比读优先级高，高到写操作来了后，可以马上插入到读操作前面去 ， 如果批量写，会导致读请求饿死，所以要设置读写优先级或设置多少写操作后执行读操作的策略;myisam不要使用查询时间太长的sql ， 如果策略使用不当，也会导致写饿死，所以尽量去拆分查询效率低的sql,
索引类型 mysql 用哪个好MySQL索引类型包括：
一、普通索引
这是最基本的索引，它没有任何限制 。有以下几种创建方式：
1.创建索引
代码如下:
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR ， VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同 。
2.修改表结构
代码如下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定 。
CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,INDEX [indexName] (username(length)));
-- 删除索引的语法：
DROP INDEX [indexName] ON mytable;
二、唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一 ， 但允许有空值 。如果是组合索引，则列值的组合必须唯一 。它有以下几种创建方式：
代码如下:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 创建表的时候直接指定
CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName] (username(length)));
三、主键索引
它是一种特殊的唯一索引，不允许有空值 。一般是在建表的时候同时创建主键索引：
代码如下:
CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID));
当然也可以用 ALTER 命令 。记?。阂桓霰碇荒苡幸桓鲋骷?。
四、组合索引
为了形象地对比单列索引和组合索引，为表添加多个字段：
代码如下:
CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,city VARCHAR(50) NOT NULL,age INT NOT NULL);
为了进一步榨取MySQL的效率，就要考虑建立组合索引 。
mysql 复合 索引 怎么选择利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引 。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序 。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处 。
所以说创建复合索引时，应该仔细考虑列的顺序 。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处 。
如：建立 姓名、年龄、性别的复合索引 。
create table test（
a int,
b int,
c int,
KEY a（a,b,c）
）；
优： select * from test where a=10 a
关于索引mysql怎么选和mysql索引的选择的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：如何在联想服务器上配置硬盘阵列？ 联想服务器怎么做阵列硬盘

    下一篇：mysql表的建立 mysql中创建表的约束条件