MySQL数据库5 索引

索引

索引的目的是加速查找,在MysQL中,指定建立索引,会为这个表的某些列建立额外的索引文件,通过索引文件可以快速查找到内容.

索引文件是一种特殊的数据结构,在MySQL里一般有两种,一种是哈希表,一种是Btree.索引文件建立之后,表如果被改动,索引文件也会被改动,因此提高了查询性能,但降低了修改表的性能.

索引类型
普通索引	加速查询
唯一索引	加速查询而且不能重复,可以为Null
主键索引	加速查询和唯一约束（不可含null）
组合索引	建立索引的时候将n个列组合成一个索引

索引都可以在创建表的时候建立,也可以在之后添加索引,组合索引则必须单独创立:

# 建立索引
CREATE TABLE in1(
    nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  # 主键写在其后表示建立主键索引,也可以单独一行 PRIMARY KEY(nid) 来建立
    name varchar(32) NOT NULL,
    email varchar(64) NOT NULL,
    extra TEXT
    INDEX ix_name (name)  # 建立普通索引
    UNIQUE ix_name (name)  # 建立唯一索引
)
CREATE INDEX index_name ON table_name(column_name);  # 建立普通索引
CREATE INDEX ix_name_email ON in3(name,email);  # 建立组合索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名)  # 建立唯一索引
ALTER TABLE 表名 ADD PRIMARY KEY(列名);  # 主键需要用到ALTER,而不是CREATE命令

# 对于text和blob的索引,一定要加上长度,写法为列名(长度),表示对开头的多少个字符进行索引,如果不加,MySQL会报错,
CREATE INDEX ix_extra ON in1(extra(32));

# 删除索引
drop index_name on table_name;  # 删除普通索引
drop unique index 索引名 on 表名  # 删除唯一索引
alter table 表名 drop primary key  # 删除主键索引

对于组合索引,要注意,索引的列名对于索引命中是有关系的,如果建立的时候是(name,email,age)之类建立组合索引,则遵循左前缀优先的策略才能让索引命中,即按照顺序从左侧搜索起:
例如: 按照 name -> email -> age 的条件语句是可以的,直接用email作为条件,或者email -> age,都不会进行组合索引查找

索引覆盖与索引合并

索引覆盖:直接通过索引文件就可以取到查询结果,而不用再去数据库中寻找,比如查询主键的id

索引合并,指两个列分别建立了索引,然后通过 where con1 and con2 两个条件来查询,这种情况叫做索引合并.索引合并的效率低于组合索引

索引未命中

虽然建立了索引,但一些查询,依然不会使用索引,主要有如下情况:

索引未命中的情况
like ‘%xx’	不会使用索引
使用某些超出索引范围的函数	select * from tb1 where reverse(name) = ‘wupeiqi’;
or	如果or内的任意一列没有编制索引,则失效
类型不一致	类型不一致的时候有时候会自动转换类型,此时无法走索引
!=	判定不等于时不会走索引,但如果是主键还是会走索引
大于或者小于	也不会走索引,但如果是主键而且是整数,还是会走索引
order by	如果排序的列没有编制索引,则不会走索引,即使查询的列有索引也没用.但如果是主键,还是会走索引

一些注意事项

• 避免使用SELECT
• 用count(1)或者count(列)来代替count(*)
• 如果知道长度,用char代替vachar,将变长部分放到最后
• 组合索引代替多个单列索引（经常使用多个条件查询时）
• 尽量让使用索引针对局部创建,比如只针对数据的不同部分
• 使用连接（JOIN）来代替子查询(Sub-Queries)
• 连表时注意条件类型需一致,类型转换效率很低下
• 如果散列值很少,而且大量重复,不适合做索引,比如性别

查看执行方式

MySQL提供了一种方式可以查看语句将如何被解释和执行的,可以用来调优.会以表格的形式展示要如何运行这个SQL语句.详细情况见此:mysql explain执行计划详解

分页性能

limit命令会直接扫描数据库,按行弄过来,所以在大量显示的时候,性能不高,有如下解决方案:

限定数据总数,比如在生成页面的时候,只取数据库的最开始100行,分成10页,如果想要查询,通过其他的方法,这样保证了页面展示不会性能低下

通过索引覆盖,但依然性能低下

解决方案是做一个记录,记录当前页的最大和最小ID,以及分页的大小.注意,不能用between and,因为很可能id不连续.然后将取出来的记录传回来之后,在页面中进行展示.可以配合Python程序,获取用户需要查询的页数,以及每页显示的内容,作为变量传入MySQL,然后用LIMIT进行分页.