电商项目-查询语句和sqlalchemy用法

概念

聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引（这里不懂先放着，一会举例），每张表只能有一个聚集索引，聚集索引的叶子节点存储了整个行数据。
辅助索引：辅助索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，还存储了一个指向改行数据的聚集索引建的书签。

一.查询语句

1.想到查询所有商品最近的一次分享记录，并且当没有最近的分享时候能生成空记录

利用先排序后分组的思路，避免子查询的排序失效，用了limit来保证;然后子查询里面放置

sql = '''select wcate.id, any_value(wcate.supplier_id), any_value(wcate.category_id),any_value(a.create_at), any_value(a.app_name), any_value(a.id)
        from wcate 
        left join (select * from user_cate_share  where user_cate_share.reseller_id=%s order by user_cate_share.id desc limit 1000) a
        on wcate.id = a.wcate_id
        where wcate.id in %s
        GROUP BY wcate.id'''

cursor.execute(sql, (reseller_id, wcate_id_list))
row = cursor.fetchone()
catalog_id, supplier_id = row['id'], row['any_value(wcate.supplier_id)']

2.查询时间为+5.5并且按照天分组，计算每天结果去重后的数量

select DATE_FORMAT(DATE_ADD(wcate.create_at, INTERVAL 5.5 hour),'%Y%m%d') as wcate_create_at, count(DISTINCT wcate.wcate_code), count(DISTINCT wsku.product_code), count(DISTINCT wsku.id)
from wsku
join wcate on wsku.wcate_id=wcate.id
GROUP BY  wcate_create_at
ORDER BY wcate_create_at desc

3.缓存池超过使用的范围，连接超时

sqlalchemy.exc.TimeoutError: QueuePool limit of size 10 overflow 10 reached, connection timed out, timeout 10 (Background on this error at: http://sqlalche.me/e/3o7r)

出现这个错的时候一般后端的表现为有部分请求非常慢，或者大部分请求都非常慢，如果后端频繁出现这个问题的时候，可以根据这两种情况进行分析：
Flask-SQLAlchemy 默认给你的 session 是一个 scoped_session，是一个 ThreadLocal 的对象，在大部分情况下，一个线程里拿到的都是同一个 session，你可以多次调用 db.session() 观察其 id，当你 db.session.remove() 了一次后，db.session() 才会拿到不一样的 session。

二.sqlalchemy语法

1.批量插入数据sqlalchemy.orm.session

    insert_sql = text("""
        INSERT INTO
            reseller_behavior(`reseller_id`, login_at, session_time, click_times, cart_times, pay_times, 
            search_list,view_times,catalog_id, supplier_id,category_id,last_share_time,which_app)
        VALUES(
            :reseller_id,
            :login_at,
            :session_time,
            :click_times,
            :cart_times,
            :pay_times,
            :search_list,
            :view_times,
            :catalog_id,
            :supplier_id,
            :category_id,
            :last_share_time,
            :which_app
        )
    """) 

    wemore_oms_session.execute(insert_sql, insert_list)  #insert_list是一个字典的列表

2.返回值

select = db.session.query(Wcate).first()

<Wcate 4>  Wcate对象

select = db.session.query(Wcate.id).first()

(1001,)  需要解包

3.execute() 函数本身有接受sql语句参数位的，可以通过python自身的函数处理sql注入问题。

#写法1
args = (id, type)
cur.execute('select id, type ,name from xl_bugs where id = %s and type = %s', args )

#写法2
# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

from sqlalchemy import text
sql = text('SELECT * from user_t WHERE username = :username;')
data = session.execute(sql, {'username':'test'}).fetchall()

用session(orm)执行，第二个参数是字典，用cursor(pymysql)执行，第二个参数是元组

4.orderby和limit同时使用，如果你需要确保无论带不带LIMIT都要以相同的顺序返回，那么你可以在ORDER BY中包含附加列，如id（自带索引）

5. 如果你将LIMIT row_count子句与ORDER BY子句组合在一起使用的话，MySQL会在找到排序结果的第一个row_count行后立即停止排序，而不是对整个结果进行排序。     所以同时用的时候，order by字段不用索引。

6.limit 2000, 20。 1.扫描2020行，丢掉2000行，减少查询的量，尽量通过主键覆盖的的字段先排完序后，再查询其他数据;2.通过记录上次查询的主键的最后一位id，先判断id，只取20条， where MemberID >= (select MemberID from Member limit 100000,1)

7.Cursor，以元组的方式保存，DictCursor是以字典的形式保存
SSCursor和SSDictCursor流式游标，这类游标不会像上面使用的Cursor和DictCursor那样，一次性返回所有的数据，流式游标会陆陆续续一条一条得返回查询数据，所以这类游标适用于内存低、网络带宽小、数据量大的应用场景中。要配合cursor.fetchone()

8.多个表一起查询，右链接不生效

select wcate.id, max(IFNULL(t.is_notify, 1)) from wcate
left join wsku on wsku.wcate_id = wcate.id
left join offer on offer.wsku_id = wsku.id
left join (SELECT reseller_notify.offer_id, reseller_notify.reseller_id, reseller_notify.is_notify from reseller_notify where reseller_notify.reseller_id = 1001 ) t
on t.offer_id = offer.id
where wcate.id in (1520, 1521)
GROUP BY wcate.id ;

9.Deadlock found when trying to get lock; try restarting transaction

通过查询资料知道，对有索引的字段更新数据会用行级锁，其他的时候是表级锁;此处应该是用到行级锁但是仍然死锁，

ab -c 10 -n 10  -T 'application/json'  -H  'token: eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpYXQiOjE1Njc1MTk5NDEsImV4cCI6MTU3MDExMTk0MSwic291cmNlX3R5cGUiOjMsInVpZCI6MTEyOSwiZGV2aWNlX2lkIjo0NDQxNTUwMX0.nlUrilI99H9FpxDFnLTyu0_JLBzSN29j7p6CUqDIkmE' -s 120 -p /home/test/productcate.txt  https://test.thewemore.com/api/v1.0/product/productcate

通过ab大量跑复现，发现是orm修改和execute两处修改，都未提交导致死锁的。

行级锁并不是直接锁记录，而是锁索引，如果一条SQL语句用到了主键索引，mysql会锁住主键索引；如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。如果操作用到了主键索引会先在主键索引上加锁，然后在其他索引上加锁，否则加锁顺序相反

# case when

select  B.`userId`,SUM(B.`change`), B.`Atype` FROM  (select A.`userId`,A.`change`,
(case
when A.`type` =1  or A.`type`=5  then 1
when A.`type` =2  or A.`type`  then 3
else 2
end
)   `Atype1`
FROM `xc_ebike_user_wallet_record`  A ) B
GROUP BY B.`userId`, B.`Atype`

#多列变成多行

SELECT D.id, D.balance, D.recharge, D.present, C.change1, C.change2, C.change3 FROM xc_ebike_usrs_2 D
JOIN
(select  B.`userId`,SUM(B.`change1`),SUM(B.`change2`),SUM(B.`change3`)  FROM  (select A.`userId`,
IF(A.`type` =1  or A.`type`=5, A.`change`, 0) AS change1,
IF(A.`type` =3 or A.`type`=4 or A.`type`=6 or A.`type`=7 or A.`type`=8, A.`change`, 0) AS change2,
IF(A.`type` =2, A.`change`, 0) AS change3
FROM `xc_ebike_user_wallet_record`  A) B
GROUP BY B.`userId`) C
ON C.userId=D.id

10、
select * from A join B on A.id =B.Aid and B.type=1
select * from A join B on A.id =B.Aid where B.type=1 不一致问题，原因，坏处

11. 创表的时候,让数据库ddl配置字段默认值

    is_vip = Column(Integer, server_default='0')
    # 默认值是创建时间 不自动更新
    create_time = Column(DateTime, nullable=False, server_default=func.now())

 12.orm批量插入或编辑:

users = [{'name': 'name%s' % i, 'age': i % 60, 'gender': i % 2} for i in range(1000)]
session.bulk_insert_mappings(User, users)

ss = [{"id":1, "sort_num":2},{"id":3, "sort_num":4},{"id":5, "sort_num":9},{"id":6, "sort_num":7}]
dao_session.session().bulk_update_mappings(Config, ss)

13.打印连接池的使用情况
logger("pool_size:", len(dao_session.session().bind.engine.pool._pool.queue))
scoped_session
remove() close()