索引

索引

1. 思考

在图书馆中是如何找到一本书的？

一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作)，

而且插入操作和更新操作很少出现性能问题，

遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重

2. 解决办法

当数据库中数据量很大时，查找数据会变得很慢

优化方案：索引

3. 索引是什么

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

4. 索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

5. 索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。

7178f37egw1err37xke42j20hc08caax

作用

• 约束

• 加速查找

索引种类：

• 普通索引：加速查找

• 主键索引：加速查找＋不能为空＋不能重复

• 唯一索引：加速查找＋不能重复

• 联合索引（多列）： 　　　　－　联合主键索引 　　　　－　联合唯一索引 　　　　－　联合普通索引

主键索引： 　 - 创建主键时就创建了主键索引

普通索引：
    - create index 索引名称 on 表名(列名,)
    - drop index 索引名称 on 表名
唯一索引：
    - create unique index 索引名称 on 表名(列名)
    - drop unique index 索引名称 on 表名

组合索引(最左前缀匹配)：
    - create unique index 索引名称 on 表名(列名,列名)
    - drop unique index 索引名称 on 表名

    - create index ix_name_email on userinfo3(name,email,)
    - 最左前缀匹配
    组合索引效率 > 索引合并 
        组合索引:

        索引合并：

    名词：
        覆盖索引：
            - 在索引文件中直接获取数据

        索引合并：
            - 把多个单列索引合并使用

无索引：从前到后依次查找

索引：
    索引 => 创建额外文件(某种格式存储)
   create index 索引名称 on 表名(字段名);

索引种类（某种格式存储）：

索引是在MYSQL的存储引擎层中实现的根据搜索引擎分类：

    B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。
    HASH 索引：只有Memory引擎支持，使用场景简单。
    R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。
    Full-text (全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。
    hash索引： 
        单值快
        范围(慢)
    btree索引： btree索引
        二叉树　(一种算法实现的)

6. 索引的使用

• 查看索引

show index from 表名;

• 创建索引

1. 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
2. 字段类型如果不是字符串，可以不填写长度部分

• 创建表时指定索引

CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );

• 创建表后创建索引

CREATE INDEX 索引名称 ON 表名(字段名称(长度))

• 删除索引：

DROP INDEX 索引名称 ON 表名;

7. 索引demo

7.1. 创建测试表testindex

create table test_index(title varchar(10));

7.2 使用python程序（ipython也可以）通过pymsql模块 向表中加入十万条数据

from pymysql import connect

def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交数据
    conn.commit()

if __name__ == "__main__":
    main()

7.3. 查询

• 开启运行时间监测：

set profiling=1;

• 查找第1万条数据ha-99999

select * from test_index where title='ha-99999';

• 查看执行的时间：

show profiles;

• 为表title_index的title列创建索引：

create index title_index on test_index(title(10));

• 执行查询语句：

select * from test_index where title='ha-99999';

• 再次查看执行的时间

show profiles;

8. 注意：

要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。 建立索引会占用磁盘空间

• a. 额外的文件保存特殊的数据结构、

• b. 查询快；插入更新删除慢

• c. 命中索引　（创建索引要正确的使用索引）

  • like '%xx'

    select * from tb1 where email like '%cn';

  • 使用函数

    select * from tb1 where reverse(email) = 'wupeiqi';

  • or

    select * from tb1 where nid = 1 or name = 'seven@live.com';

    特别的：当or条件中有未建立索引的列才失效，以下会走索引 select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'

  • 类型不一致

    如果列是字符串类型，传入条件是必须用引号引起来，不然...

    select * from tb1 where email = 999;

  • !=

    select * from tb1 where email != 'alex'

    特别的：如果是主键，则还是会走索引 select * from tb1 where nid != 123

  • select * from tb1 where email > 'alex'

    特别的：如果是主键或索引是整数类型，则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123

  • order by

    select name from tb1 order by email desc;

    当根据索引排序时候，选择的映射如果不是索引，则不走索引 特别的：如果对主键排序，则还是走索引： select * from tb1 order by nid desc;

  • 组合索引最左前缀

    如果组合索引为：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引

索引选择原则

1. 较频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在 WHERE 子句中的字段不该创建索引

性能优化过程中，选择在哪个列上创建索引是最非常重要的。可以考虑使用索引的主要有 两种类型的列：在where子句中出现的列，在join子句中出现的列，而不是在SELECT关键字后选择列表的列；

一般两种情况下不建议建索引： 表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了;

至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以5000作为分界线，记录数不超过 5000可以考虑不建索引，超过5000条可以酌情考虑索引。

最后再次强调： 不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长

总结一些其他注意事项： 其他注意事项

• 避免使用select *

• count(1)或count(列) 代替 count(*)

• 创建表时尽量时 char 代替 varchar

• 表的字段顺序固定长度的字段优先

• 组合索引代替多个单列索引（经常使用多个条件查询时）

• 尽量使用短索引

• 使用连接（JOIN）来代替子查询(Sub-Queries)

• 连表时注意条件类型需一致

• 索引散列值（重复少的列做索引）不适合建索引，例：性别不适合

    # -*- coding:utf8 -*-
    import pymysql as c
    from faker import Faker
    import random
    import sys
    import datetime
  
    #创建一个用户表
    #create table usersinfo( use_id int auto_increment, name varchar(20) not null, gender char(5) not null, age int, brithday char(10), id_type varchar(15) default '身份证', id_card char(18), phone char(11), email varchar(30), native_place varchar(60), address varchar(255), join_time char(10), hobby text, primary key(use_id) );
  
    mysqlConn = c.connect(user='root',password="ljh1314",database='class1804')
    cursor = mysqlConn.cursor()
  
    fake = Faker("zh_CN")
  
    def get_native_place(address, key="县市"):
  
        return [address[:address.index(k)+1] for k in key if k in address][0]
  
    def gen_stu_obj():
        #随机生产一个地址
        address = fake.address()
        #从地址里面截取
        native_place = get_native_place(address)
        #随机生成一个出生日期
        brithday = fake.date_of_birth(tzinfo=None, minimum_age=20, maximum_age=30)
        #随机生成一个邮箱
        email = fake.ascii_free_email()
        #随机生辰一个姓名
        name = fake.name()
        #随机生成一个电话号码
        phone = fake.phone_number()
        id_type = "身份证"
        #随机生成一个年龄
        age = random.randint(20,30)
        #随机产生一个身份证号
        id_card = fake.ssn(min_age=20, max_age=30)
        #产生一个时间
        join_time = fake.date_between(start_date="-2y", end_date="today")
        #生成一个座右铭
        hobby = fake.sentence(nb_words=6, variable_nb_words=True, ext_word_list=None)
        #随机筛选男或者女
        gender = random.choice(["男","女"])
        # print(address,native_place,birthday,email,name,phone,age,id_code,join_time,hobby,sex)
  
        sql = 'insert into usersinfo(use_id,name,gender,age,brithday,id_type,id_card,phone,email,' \
        'native_place,address,join_time,hobby) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        cursor.execute(sql, (
        None, name, gender, age, brithday, id_type, id_card, phone, email, native_place, address, join_time, hobby,))
  
        mysqlConn.commit()
  
    if __name__ == "__main__":
  
        a=datetime.datetime.now()
        gen_stu_obj()
        # for _ in range(100000):
            # gen_stu_obj()
        b=datetime.datetime.now()
        # print(b-a)